Skip site navigation (1) Skip section navigation (2)

Re: Unpacking a Python list in a query.

From: David Niergarth <dn(at)hddesign(dot)com>
To: psycopg(at)postgresql(dot)org
Cc: Sukhbir Singh <singheinstein(at)gmail(dot)com>
Subject: Re: Unpacking a Python list in a query.
Date: 2011-06-24 19:36:16
Message-ID: BANLkTinV5UoDcxxwkiNRp3XOJGfcUwPuLg@mail.gmail.com (view raw or flat)
Thread:
Lists: psycopg
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(dot)bgf(at)gmail(dot)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(at)gmail(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/psycopg
>>
>
>

In response to

Responses

psycopg by date

Next:From: Sukhbir SinghDate: 2011-06-24 19:40:23
Subject: Re: Unpacking a Python list in a query.
Previous:From: Sukhbir SinghDate: 2011-06-24 19:35:13
Subject: Re: Unpacking a Python list in a query.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group