Thread: Unpacking a Python list in a query.

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.

Hi,

> execute("UPDATE foobartable SET name = 'FooBar' WHERE name in (%s)",
> (tuple(yourlist),))
>
> That should work.

I get this error:

    psycopg2.ProgrammingError: arguments of row IN must all be row expressions
    LINE 1: UPDATE listarchives SET name='FooBar' WHERE name in ((E'Foo...

I am passing a tuple only.

Hi,

> Actually, a list or a tuple will work just fine.

Indeed, it does work. I somehow didn't try the most obvious solution
to this, focusing on unpacking all the time :-)

Thank you.

• >>> cur.mogrify("SELECT %s;", ([10, 20, 30], ))
  
  'SELECT ARRAY[10, 20, 30];'
  

Python tuples are converted in a syntax suitable for the SQL IN operator and to represent a composite type:

>>> cur.mogrify("SELECT %s IN %s;", (10, (10, 20, 30)))

'SELECT 10 IN (10, 20, 30);'