BuvinJ BuvinJ - 1 year ago 81
MySQL Question

What's the "best" way to create a dynamic sql "in list" clause in Python from an array (or tuple) of strings?

I'm running a dynamic MySQL query from Python (using MySQLDb) which includes an "in list" clause that contains string values. The function which executes this gets an array of the values. I could make that array into a tuple or any other kind of collection if it helps.

What's the "best" way to insert this list? Keep in mind that single quotes and commas etc are needed. Here's an ugly, but safe manual approach:

inList = ""
for stringValue in someArray:
if inList != "" : inList += ","
inList += "'%s'" % stringValue
querystr = "SELECT * FROM some_tbl WHERE some_column IN( %s );" % (inList)

Alternatively, here's another option. It's shorter, but relies on the array to string representation remaining exactly the same in the future:

inList = str(someArray).replace("[", "").replace("]", "")
querystr = "SELECT * FROM some_tbl WHERE some_column IN( %s );" % (inList)

Answer Source

Larry Lustig's answer is good in concept, but has a critical syntax flaw. Here's a functional solution I derived from his post:

def toSqlListPlaceholder(array):            
    return ",".join( ["%s"] * len(array) )           

querystr = ("SELECT * FROM some_tbl WHERE some_column IN( %s );" %
            (toSqlListPlaceholder( someArray )) ) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download