CODEWITHSUNDEEP
pythonpsycopg2
bneil
bneil

Reputation: 1600

How to pass parameterized statements with values and table names to Psycopg2?

I'm basically trying to accomplish what the answerer pointed out here: psycopg2 cursor.execute() with SQL query parameter causes syntax error

My statement is:

    cursor.execute('SELECT %s FROM %s WHERE %s = %s' % (pkey,table,field, "%s"), (value))

But when I run it I get the error:

---> 89         cur.execute('SELECT %s FROM %s WHERE %s = %s' % (pkey,table,field, "%s"), (value))
     90         res=cur.fetchone()
     91         if res==None:

TypeError: not all arguments converted during string formatting

Upvotes: 2

Views: 4907

Answers (2)

BluesRockAddict
BluesRockAddict

Reputation: 15683

Try the following, db module should take care of the proper quoting:

sql = 'SELECT %s FROM %s WHERE %s' % (pkey, table, field) + ' = %s'
cursor.execute(sql, (value, ))

Upvotes: 4

Irfy
Irfy

Reputation: 9607

Your problem was not in quoting but in Python's tuple syntax.

As you already commented above, the correct way to represent one-element tuple is (value,). In general the tuple syntax is (informally):

()
(value,)
(value, value)
(value, value, value)

and so on for multi-element tuples.

The accepted answer is not really correct and the change suggested therein is purely a matter of taste (having the last '%s' be appended after substitution vs. your attempt).

Keep this in mind: when doing common string substitution in Python, when you have a string with just one %s, you can drop the tuple and simply pass the single substitution string itself:

'foo %s' % 'bar'

This will result in foo bar.

You could try the same with psycopg's cursor.execute, maybe it works (I would test it myself before posting, but I can't).

Upvotes: 4

Related Questions