From: | Sukhbir Singh <singheinstein(at)gmail(dot)com> |
---|---|
To: | psycopg(at)postgresql(dot)org |
Subject: | Unpacking a Python list in a query. |
Date: | 2011-06-24 19:05:08 |
Message-ID: | BANLkTimSngQwS=uTopoKptur1+JxS8u0+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Israel Ben Guilherme Fonseca | 2011-06-24 19:25:02 | Re: Unpacking a Python list in a query. |
Previous Message | Daniel Lenski | 2011-06-24 18:38:56 | performance of copy_from() vs. raw COPY command |