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

[Bruno Desthuilliers]

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)))

# 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
     )


HTH