Executing SELECT WHERE IN using MySQLdb

Question

I m having a problem executing some SQL from within Python  despite similar SQL working fine from the mysql command-line   The table looks like this   mysql gt  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 the mysql command-line  without a problem   mysql gt  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 within Python  I get no rows  while I expected 2 rows   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 should the python code be modified to select those fooids 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 bars where fooid is in  1 3  successfully  I don t understand why one such query  below  works  while the other one  above  doesn t   sql  SELECT bar FROM foo WHERE fooid IN  s  args   1 3   cursor execute sql args  data cursor fetchall   print data       A       C       And just to be absolutely clear  this is how the foo table was created   mysql gt  DROP TABLE IF EXISTS foo  Query OK  0 rows affected  0 00 sec   mysql gt  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 gt  INSERT into foo  bar  values   A     B     C     D    Query OK  4 rows affected  0 00 sec  Records  4  Duplicates  0  Warnings  0     Edit  When I enable 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 looks like too many quotes are being placed around A and C   Thanks to  Amber s comment  I understand better what is going wrong  MySQLdb converts the parametrized argument   A   C   to    A     C       Is there a way to make a parametrized query using the IN SQL syntax  Or must one manually construct the SQL string

User · Answer

Improving on Jo  o s and satru s code  I suggest creating a cursor mixin that can be used to build a cursor with an execute that accepts nested iterables and handles them correctly  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 s still backwards 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

User · Answer

If you have other parameters in the query  beyond the IN list  then the following extension to  JG s answer may be useful   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  join all the params in a linear array  then pass it as a tuple to the execute method

User · Answer

Very simple  Just use the below formation    rules id     quot 9 quot   quot 10 quot    sql2    quot SELECT   FROM attendance rules staff WHERE id in quot  str tuple rules id    note the str tuple rules id

User · Answer

Maybe we can create a function to do what Jo  o proposed   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

User · Answer

Have been trying every variation on Jo  o s solution to get an IN List query to work with Tornado s mysql wrapper  and was still getting the accursed  TypeError  not enough arguments for format string  error  Turns out adding     to the list var   args  did 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

User · Answer

Here is a similar solution which I think is more efficient in building up the list of  s strings in the SQL      Use the 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         That way you avoid having to quote yourself  and avoid all kinds of sql injection       Note that the data  list of ids  is going directly to mysql s driver  as a parameter  not in the query text  so there is no injection  You can leave any chars you want in the string  no need to remove or quote chars

User · Answer

this works for me   myTuple  tuple myList  sql  select fooid from foo where bar in   str myTuple  cursor execute sql

User · Answer

Unfortunately  you need to manually construct the query parameters  because as far as I know  there is no built-in bind method for binding a list to an IN clause  similar to Hibernate s setParameterList    However  you can accomplish 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

User · Answer

args should be tuple    eg   args     A   B    args     A      in case of single

User · Answer

Why not just this in that 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    results in   SELECT fooid FROM foo WHERE bar IN   A    C

[python] Executing "SELECT ... WHERE ... IN ..." using MySQLdb

Examples related to python

Examples related to mysql