如何使用Python字符串格式化SQL IN子句 [英] How to string format SQL IN clause with Python

问题描述

我正在尝试创建如下语句:

I am trying to create a statement as follows:

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

但是，我在使用Django API的字符串格式时遇到了一些麻烦.这是我到目前为止的内容:

However, I'm having some trouble with the string formatting of it from the Django API. Here's what I have so far:

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)')

推荐答案

MySQLdb有一种方法可以帮助解决此问题:

MySQLdb has a method to help with this:

文档

string_literal(...) string_literal(obj)-将对象obj转换为SQL字符串文字. 这意味着，将转义任何特殊的SQL字符，并将其括起来 用单引号引起来.换句话说，它执行:

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) 

这篇关于如何使用Python字符串格式化SQL IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！