用字符串数组(或元组)在Python中创建动态sql“in list”子句的“最佳”方法是什么?

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.

我正在运行一个Python的动态MySQL查询(使用MySQLDb)，其中包含一个包含字符串值的“in list”子句。执行此操作的函数获取值的数组。如果有用的话，我可以把这个数组做成tuple或者其他的集合。

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".

我认为我写这篇文章的时候Python术语是错误的。我应该说“list”而不是“array”。

1 个解决方案

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

(这假定您正在使用MySQL连接器，不幸的是，该连接器使用%s作为占位符。其他Python库倾向于使用吗?的占位符)。

#1