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

[Michael Mabin]

       cursor.execute("""
                   SELECT titem.object_id, titem.tag_id
                   FROM tagging_taggeditem titem
                   WHERE titem.object_id IN (%s)
               """ % ','.join([str(x) for x in [1,5,9]])

On Fri, Sep 26, 2008 at 6:23 AM, Tino Wildenhain <tino at wildenhain.de> wrote:

> 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
>
>
> --
> http://mail.python.org/mailman/listinfo/python-list
>



-- 
| _ | * | _ |
| _ | _ | * |
| *  | * | * |
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20080926/829eebb7/attachment.html>