如何用Python字符串格式SQL-IN子句

Question

我试图创建一个声明如下：

SELECT * FROM table WHERE provider IN ('provider1', 'provider2', ...)

但是，我在Django API中的字符串格式化方面遇到了一些问题。以下是我目前掌握的情况：

profile = request.user.get_profile()
providers = profile.provider.values_list('provider', flat=True) # [u'provider1', u'provider2']
providers = tuple[str(item) for item in providers] # ('provider1', 'provider2')

SQL = "SELECT * FROM table WHERE provider IN %s"
args = (providers,)
cursor.execute(sql,args)

DatabaseError
(1241, 'Operand should contain 1 column(s)')

Answer 1

MySQLdb有一个方法可以帮助您：

文档

string_literal(...) string_literal(obj) -- converts object obj into a SQL string literal. This means, any special SQL characters are escaped, and it is enclosed within single quotes. In other words, it performs:

"'%s'" % escape_string(str(obj))

Use connection.string_literal(obj), if you use it at all.
_mysql.string_literal(obj) cannot handle character sets.

用法

# connection:  <_mysql.connection open to 'localhost' at 1008b2420>

str_value = connection.string_literal(tuple(provider))
# '(\'provider1\', \'provider2\')'

SQL = "SELECT * FROM table WHERE provider IN %s"
args = (str_value,)
cursor.execute(sql,args) 

Answer 2

因此，您有ID必需的字符串输入：

some_vals = '1 3 5 76 5 4 2 5 7 8'.split() # convert to suitable type if required
SomeModel.objects.filter(provider__in=some_vals)

Answer 3

在将字符串传递给cursor对象以执行之前，您可能应该进行字符串替换：

sql = "SELECT * FROM table WHERE provider IN (%s)" % \
        (','.join(str(x) for x in providers))
cursor.execute(sql)