How to format a SQL IN clause using Python

Question

I am trying to create an instruction as follows:

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

However, I am having problems formatting the string from the Django API. Here is what I still have:

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

Doc

string_literal (...) string_literal (obj) - Converts an obj object into an SQL string literal. This means that any special SQL characters are escaped, and they are enclosed in single quotes. In other words, it does:

 "'%s'" % escape_string(str(obj)) Use connection.string_literal(obj), if you use it at all. _mysql.string_literal(obj) cannot handle character sets. 

Using

 # 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

You should probably replace the string before passing it to the cursor object that will be executed:

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

Answer 3

Another answer that I don't particularly like, but will work for your obvious use case:

 providers = tuple[str(item) for item in providers] # ('provider1', 'provider2') # rest of stuff... SQL = 'SELECT * FROM table WHERE provider IN {}'.format(repr(providers)) cursor.execute(SQL) 

Answer 4

 "SELECT * FROM table WHERE provider IN ({0},{1},{2})".format(*args) #where args is list or tuple of arguments. 

Answer 5

So you have string input for the required 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 6

try this .... should work.

 SQL = "SELECT * FROM table WHERE provider IN %s"%(providers) exec 'cursor.execute("%s")'%(SQL)