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




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
    )

NB : you can of course make it in a single statement, but readability will suffer:

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

smime.p7s
Description: S/MIME Cryptographic Signature

--
http://mail.python.org/mailman/listinfo/python-list