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

Question

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) 

EDIT

I think my Python terminology was wrong when I wrote this. I should be saying "list" not "array".

Answer 1

There is really no good way to make a dynamic query of this kind safe. You should switch to a parametrized query, in which case the solution is:

placeholder = '%s'
param_subs = ','.join((placeholder,) * len(param_list))
sql = 'SELECT col1, col2, . . . FROM Table WHERE Column IN ( %s );' % param_subs
cursor.execute(sql, param_list)

(This assumes you're using MySQL Connector which, unfortunately, uses %s as a placeholder. Other Python libraries tend to use ? as the placeholder.)