Doing "SELECT ... WHERE ... IN ..." using MySQLdb

Question

I had a problem running some SQL from Python, even though a similar SQL works fine from the mysql command line.

The table looks like this:

 mysql> SELECT * FROM foo; +-------+-----+ | fooid | bar | +-------+-----+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | +-------+-----+ 4 rows in set (0.00 sec) 

I can execute the following SQL query from mysql command line without any problems:

 mysql> SELECT fooid FROM foo WHERE bar IN ('A','C'); SELECT fooid FROM foo WHERE bar IN ('A','C'); +-------+ | fooid | +-------+ | 1 | | 3 | +-------+ 2 rows in set (0.00 sec) 

However, when I try to do the same from Python, I don't get the lines, while I was expecting 2 lines:

 import MySQLdb import config connection=MySQLdb.connect( host=config.HOST,user=config.USER,passwd=config.PASS,db='test') cursor=connection.cursor() sql='SELECT fooid FROM foo WHERE bar IN %s' args=[['A','C']] cursor.execute(sql,args) data=cursor.fetchall() print(data) # () 

So the question is: how to change python code to select those fooid where bar is in ('A','C') ?

By the way, I noticed that if I switch the roles of bar and fooid , I can get the code to select those bar where fooid is in (1,3) successfully. I do not understand why one such request (below) works, and another (above) does not work.

 sql='SELECT bar FROM foo WHERE fooid IN %s' args=[[1,3]] cursor.execute(sql,args) data=cursor.fetchall() print(data) # (('A',), ('C',)) 

And to be absolutely clear, here is how the foo table was created:

 mysql> DROP TABLE IF EXISTS foo; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE `foo` ( `fooid` int(11) NOT NULL AUTO_INCREMENT, `bar` varchar(10) NOT NULL, PRIMARY KEY (`fooid`)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT into foo (bar) values ('A'),('B'),('C'),('D'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 

---

Change When I turn on the general query log with mysqld -l /tmp/myquery.log I see

 mysqld, Version: 5.1.37-1ubuntu5.5-log ((Ubuntu)). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument 110101 11:45:41 1 Connect unutbu@localhost on test 1 Query set autocommit=0 1 Query SELECT fooid FROM foo WHERE bar IN ("'A'", "'C'") 1 Query SELECT bar FROM foo WHERE fooid IN ('1', '3') 1 Quit 

Indeed, it seems that too many quotes are placed around A and C

Thanks to @Amber's comment, I better understand what is going wrong. MySQLdb converts the parameterized argument ['A','C'] to ("'A'","'C'") .

Is there a way to make a parameterized query using IN SQL syntax? Or do you need to manually build an SQL string?

Answer 1

Here is a similar solution , which, in my opinion, is more efficient in creating a list of% s strings in SQL:

Use list_of_ids directly:

 format_strings = ','.join(['%s'] * len(list_of_ids)) cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings, tuple(list_of_ids)) 

This way you avoid quoting yourself and avoiding all kinds of SQL injections.

Note that the data ( list_of_ids ) goes directly to the mysql driver as a parameter (not in the request text), so there is no injection. You can leave any characters you want in the string, no need to delete or specify characters.

Answer 2

Unfortunately, you need to manually construct the query parameters, since, as far as I know, there is no built-in bind method to bind list to an IN clause similar to Hibernate setParameterList() , however you can do the same with the following:

Python 3:

 args=['A', 'C'] sql='SELECT fooid FROM foo WHERE bar IN (%s)' in_p=', '.join(list(map(lambda x: '%s', args))) sql = sql % in_p cursor.execute(sql, args) 

Python 2:

 args=['A', 'C'] sql='SELECT fooid FROM foo WHERE bar IN (%s)' in_p=', '.join(map(lambda x: '%s', args)) sql = sql % in_p cursor.execute(sql, args) 

Answer 3

If you have other parameters in the request outside the IN list, the following extension may be useful for JG's answer.

 ids = [1, 5, 7, 213] sql = "select * from person where type=%s and id in (%s)" in_ids = ', '.join(map(lambda x: '%s', ids)) sql = sql % ('%s', in_ids) params = [] params.append(type) params.extend(ids) cursor.execute(sql, tuple(params)) 

That is, attach all the parameters in a linear array, and then pass it as a tuple to the execution method.

Answer 4

this works for me:

 myTuple= tuple(myList) sql="select fooid from foo where bar in "+str(myTuple) cursor.execute(sql) 

Answer 5

Maybe we can create a function to do what João suggested? Something like:

 def cursor_exec(cursor, query, params): expansion_params= [] real_params = [] for p in params: if isinstance(p, (tuple, list)): real_params.extend(p) expansion_params.append( ("%s,"*len(p))[:-1] ) else: real_params.append(p) expansion_params.append("%s") real_query = query % expansion_params cursor.execute(real_query, real_params) 

Answer 6

I tried all the options for João's solution to get an IN List query to work with the Tornado mysql wrapper and still got the damn error "TypeError: not enough arguments for format string". It turns out adding "*" to the var "* args" list does the trick.

 args=['A', 'C'] sql='SELECT fooid FROM foo WHERE bar IN (%s)' in_p=', '.join(list(map(lambda x: '%s', args))) sql = sql % in_p db.query(sql, *args) 

Answer 7

Improving the code for João and satru, I suggest creating a cursor mixer that can be used to build a cursor with execution that accepts nested iterations and correctly processes them. A better name would be nice, though ... For Python3, use str instead of basestring .

 from MySQLdb.cursors import Cursor class BetterExecuteMixin(object): """ This mixin class provides an implementation of the execute method that properly handles sequence arguments for use with IN tests. Examples: execute('SELECT * FROM foo WHERE id IN (%s) AND type=%s', ([1,2,3], 'bar')) # Notice that when the sequence is the only argument, you still need # a surrounding tuple: execute('SELECT * FROM foo WHERE id IN (%s)', ([1,2,3],)) """ def execute(self, query, args=None): if args is not None: try: iter(args) except TypeError: args = (args,) else: if isinstance(args, basestring): args = (args,) real_params = [] placeholders = [] for arg in args: # sequences that we treat as a single argument if isinstance(arg, basestring): real_params.append(arg) placeholders.append('%s') continue try: real_params.extend(arg) placeholders.append(','.join(['%s']*len(arg))) except TypeError: real_params.append(arg) placeholders.append('%s') args = real_params query = query % tuple(placeholders) return super(BetterExecuteMixin, self).execute(query, args) class BetterCursor(BetterExecuteMixin, Cursor): pass 

This can then be used as follows (and it is still backward compatible!):

 import MySQLdb conn = MySQLdb.connect(user='user', passwd='pass', db='dbname', host='host', cursorclass=BetterCursor) cursor = conn.cursor() cursor.execute('SELECT * FROM foo WHERE id IN (%s) AND type=%s', ([1,2,3], 'bar')) cursor.execute('SELECT * FROM foo WHERE id IN (%s)', ([1,2,3],)) cursor.execute('SELECT * FROM foo WHERE type IN (%s)', (['bar', 'moo'],)) cursor.execute('SELECT * FROM foo WHERE type=%s', 'bar') cursor.execute('SELECT * FROM foo WHERE type=%s', ('bar',)) 

Answer 8

Very simple:

Just use the following formula ###

 rules_id = ["9","10"] sql2 = "SELECT * FROM attendance_rules_staff WHERE id in"+str(tuple(rules_id)) 

pay attention to str(tuple(rules_id)) .

Answer 9

Why not just in this case?

 args = ['A', 'C'] sql = 'SELECT fooid FROM foo WHERE bar IN (%s)' in_p =', '.join(list(map(lambda arg: "'%s'" % arg, args))) sql = sql % in_p cursor.execute(sql) 

leads to:

 SELECT fooid FROM foo WHERE bar IN ('A', 'C') 

Answer 10

args must be a tuple.

eg:

 args = ('A','B') args = ('A',) # in case of single