python实现sql代码格式化,如何使用Python字符串格式化SQL IN子句

I am trying to create a statement as follows:

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

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 has a method to help with this:

Doc

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.

Usage

# connection: <_mysql.connection open to at>

str_value = connection.string_literal(tuple(provider))

# '(\'provider1\', \'provider2\')'

SQL = "SELECT * FROM table WHERE provider IN %s"

args = (str_value,)

cursor.execute(sql,args)