psycopg2 query not parsing parameters properly?

Question

Using python 2.7.12 with psycopg2 version 2.6.2 and having no success in submitting a generated query (as opposed to a string I just type in). Querying against an AWS RedShift instance.

When I try to run the code what happens is that it fails because an extraneous parenthesis is added (when I use the (%s) construction ... or an extra single quote is added if I use just %s instead). I've tried to follow the docs very carefully and have also searched here and google, but am getting nowhere. Does anybody out there have any advice on how to solve this problem?

I've tried very hard to follow the docs at http://initd.org/psycopg/docs/usage.html#query-parameters:

My code looks like this:

con = psycopg2.connect(dbname=dbname, host=host, port=port, user=user, password=password)
cur = con.cursor()

try3 = "TRUNCATE TABLE (%s);"
values = ("schema_one.tbl_six",)
cur.execute(try3,values)

try4 = "TRUNCATE TABLE %s;"
values = ("schema_four.tbl_four",)
cur.execute(try4,values)

Which produces this output:

$ ./test_qry.py
Traceback (most recent call last):
  File "./test_qry.py", line 23, in <module>
    cur.execute(try3,values)
psycopg2.ProgrammingError: syntax error at or near "("
LINE 1: TRUNCATE TABLE ('schema_one.tbl_six');

$ ./test_qry.py
Traceback (most recent call last):
  File "./test_qry.py", line 28, in <module>
    cur.execute(try4,values)
psycopg2.ProgrammingError: syntax error at or near "'schema_four.tbl_four'"
LINE 1: TRUNCATE TABLE 'schema_four.tbl_four';

The pattern you are using assumes a FIELD/VALUES pair will be modified. Does the pattern work for an INSERT statement with a FIELD/VALUE pair? Thinking maybe you can't use this paradigm for a single statement like you are trying...? — Karen Clark
– Karen Clark, Commented Dec 28, 2016 at 22:56
If you scroll down, you can see sample code that exhibits this paradigm: # Execute a command: this creates a new table >>> cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);") # Pass data to fill a query placeholders and let Psycopg perform # the correct conversion (no more SQL injections!) >>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", ... (100, "abc'def")) — Karen Clark
– Karen Clark, Commented Dec 28, 2016 at 22:58

alecxe · Accepted Answer · 2016-12-28 22:55:40Z

3

You've got an exceptional case that is described later in the docs:

Only variable values should be bound via this method: it shouldn’t be used to set table or field names. For these elements, ordinary string formatting should be used before running execute().

In other words, you cannot parameterize table or column names and have to use string formatting:

query = "TRUNCATE TABLE %s;"
values = ("schema_one.tbl_six",)
cur.execute(query % values)

Or, with str.format():

query = "TRUNCATE TABLE {table_name};"
cur.execute(query.format(table_name="schema_one.tbl_six"))

That said, you should still be careful and validate/escape the table name to prevent SQL injection attacks even if you trust the source.

edited Dec 28, 2016 at 22:55

answered Dec 28, 2016 at 22:50

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

psycopg2 query not parsing parameters properly?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related