Actually, a list or a tuple will work just fine.
>>> cursor.execute("UPDATE foobartable SET name = %s where name = %s;", ['Ham', 'HAM'])
>>> cursor.execute("UPDATE foobartable SET name = %s where name = %s;", ('Spam', 'SPAM'))
It's string interpolation (which you want to avoid in cursor.execute()) that requires a list.
>>> '%s %s' % ('spam', 'eggs')
'spam eggs'
>>> '%s %s' % ['spam', 'eggs']
Traceback (most recent call last):
File "<console>", line 1, in <module>
TypeError: not enough arguments for format string
You can also use a dictionary.
>>> cursor.execute("UPDATE foobartable SET name = %(new_name)s where name = %(old_name)s;", dict(new_name='Eggs', old_name='EGGS'))
--David
On Fri, Jun 24, 2011 at 2:25 PM, Israel Ben Guilherme Fonseca
<israel.bgf@gmail.com> wrote:
"UPDATE foobartable SET name = 'FooBar' WHERE name in (%s)"
And pass your list as a parameter (it must be a tuple).
execute("UPDATE foobartable SET name = 'FooBar' WHERE name in (%s)", (tuple(yourlist),))
That should work.2011/6/24 Sukhbir Singh
<singheinstein@gmail.com> Hi,
I am using the psycopg2 adapter on Python 2.6.
There was a requirement to automate certain UPDATE queries, so I
designed the program in such a way that the end of the queries are in
a list. I will illustrate with an example.
The query is:
UPDATE foobartable SET name = 'FooBar' WHERE name = %s OR name = %s
And say I have a list:
["Foo", "Bar"]
So, I want to execute the complete query as: substituting element by
element from the list for each %s:
UPDATE foobartable SET name = 'FooBar' WHERE name = 'Foo' OR name = 'Bar'
The question is: how do I pass this list to the query?
I tried using the format method and list unpacking (*list) and it
works. But the docs recommend otherwise: "Warning Never, never, NEVER
use Python string concatenation (+) or string parameters interpolation
(%) to pass variables to a SQL query string. Not even at gunpoint"
This is what I did:
curr.execute("UPDATE foobartable SET name='FooBar' WHERE
name='{0}' or name='{1}';".format(*list))
... which I am certain is wrong.
How can I get this working with something safe and the recommended way
of doing it?
--
Sukhbir.
--
Sent via psycopg mailing list (psycopg@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/psycopg
