CODEWITHSUNDEEP
pythonamazon-redshiftpsycopg2psql
Thom Rogers
Thom Rogers

Reputation: 1433

psycopg2 query not parsing parameters properly?

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: enter image description here

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';

Upvotes: 3

Views: 1026

Answers (1)

alecxe
alecxe

Reputation: 474003

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.

Upvotes: 3

Related Questions