how to replace and string in a "SELECT ... IN ()"

[Tino Wildenhain]

Hi,

Bruno Desthuilliers wrote:
> bcurtu a écrit :
>> Hi,
>>
>> I have a BIIIIIG problem with the next query:
>>
>>         cursor.execute("""
>>                     SELECT titem.object_id, titem.tag_id
>>                     FROM tagging_taggeditem titem
>>                     WHERE titem.object_id IN (%s)
>>                 """,( eid_list))
>>
>> eid_list is suppossed to be a list of ids = [1,5,9]
>>
>> How can I make it work?
> 
> You have to build your sql statement in three stages:
> 
> # stage 0: the template
> sql_template = """
>     SELECT titem.object_id, titem.tag_id
>     FROM tagging_taggeditem titem
>     WHERE titem.object_id IN (%s)
> """
> 
> # stage 1: build correct place_holders string for the actual number
> # of items in eid_list
> place_holders = ", " .join("%s" for x in xrange(len(eid_list)))

Hm. either ", ".join(["%s"]*len(eid_list))
or ", ".join("%s" for x in eid_list)

should produce the same, wouldn't it? :-)

> # stage 2 : build the effective sql statement
> sql = sql_template % place_holders
> 
> # ok, let's go:
> cursor.execute(sql_template, eid_list)
> 
> 
> NB : you can of course make it in a single statement, but readability 
> will suffer:
> 
> cursor.execute(
>     """
>     SELECT titem.object_id, titem.tag_id
>     FROM tagging_taggeditem titem
>     WHERE titem.object_id IN (%s)
>     """ % ", " .join("%s" for x in xrange(len(eid_list))),
>     eid_list
>     )

I'd think giving the arguments in a form of an array type should
work too. At least in postgres there are references to do so.

Regards
Tino

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3241 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20080926/649f4088/attachment-0001.bin>