Python MySQLdb TypeError not all arguments converted during string formatting

Question

Upon running this script       usr bin env python import MySQLdb as mdb import sys      class Test      def check self  search           try              con   mdb connect  localhost    root    password    recordsdb                 cur   con cursor               cur execute   SELECT   FROM records WHERE email LIKE   s    search                ver   cur fetchone                print  Output    s     ver          except mdb Error  e               print  Error  d   s     e args 0  e args 1               sys exit 1           finally                  if con                      con close    test   Test   test check  test     I get an error of     lookup  Traceback  most recent call last     File    lookup   line 27  in  lt module gt      test check  test     File    lookup   line 11  in creep     cur execute   SELECT   FROM records WHERE email LIKE   s    search     File   usr local lib python2 7 dist-packages MySQLdb cursors py   line 187  in execute     query   query   tuple  db literal item  for item in args   TypeError  not all arguments converted during string formatting   I have zero idea why  I m trying to do parameterized querys  but it s been nothing but a pain  I m somewhat new to Python  so it s probably an obvious problem

User · Answer

I don t understand the first two answers  I think they must be version-dependent  I cannot reproduce them on MySQLdb 1 2 3  which comes with Ubuntu 14 04LTS  Let s try them  First  we verify that MySQL doesn t accept double-apostrophes   mysql gt  select   from methods limit 1   ---------- -------------------- ------------    MethodID   MethodDescription    MethodLink    ---------- -------------------- ------------          32   Autonomous Sensing   NULL          ---------- -------------------- ------------  1 row in set  0 01 sec   mysql gt  select   from methods where MethodID     32    ERROR 1064  42000   You have an error in your SQL syntax  check the manual that corresponds to your MySQL server version for the right syntax to use near  9999     at line 1   Nope  Let s try the example that Mandatory posted using the query constructor inside  usr lib python2 7 dist-packages MySQLdb cursors py where I opened  con  as a connection to my database    gt  gt  gt  search    test   gt  gt  gt   SELECT   FROM records WHERE email LIKE   s     con literal search   SELECT   FROM records WHERE email LIKE   test     gt  gt  gt     Nope  the double apostrophes cause it to fail  Let s try Mike Graham s first comment  where he suggests leaving off the apostrophes quoting the  s    gt  gt  gt   SELECT   FROM records WHERE email LIKE  s    con literal search   SELECT   FROM records WHERE email LIKE  test    gt  gt  gt     Yep  that will work  but Mike s second comment and the documentation says that the argument to execute  processed by con literal  must be a tuple  search   or a list  search   You can try them  but you ll find no difference from the output above   The best answer is ksg97031 s

User · Answer

keyword is so dangerous because it major cause of  SQL INJECTION ATTACK    So you just using this code   cursor execute  select   from table where example  s    example      or  t    example   cursor execute  select   from table where example  s   t    if you want to try insert into table  try this   name    ksg  age   19 sex    male  t     name  age  sex  cursor execute  insert into table values  s  d  s    t

User · Answer

The accepted answer by  kevinsa5 is correct  but you might be thinking  I swear this code used to work and now it doesn t   and you would be right   There was an API change in the MySQLdb library between 1 2 3 and 1 2 5  The 1 2 3 versions supported  cursor execute  SELECT   FROM foo WHERE bar    s    baz     but the 1 2 5 versions require  cursor execute  SELECT   FROM foo WHERE bar    s     baz      as the other answers state  I can t find the change in the changelogs  and it s possible the earlier behavior was considered a bug   The Ubuntu 14 04 repository has python-mysqldb 1 2 3  but Ubuntu 16 04 and later have python-mysqldb 1 3 7    If you re dealing with a legacy codebase that requires the old behavior but your platform is a newish Ubuntu  install MySQLdb from PyPI instead     pip install MySQL-python  1 2 3

User · Answer

I encountered this error while executing  SELECT   FROM table   I traced the error to cursor py line 195    if args is not None          if isinstance args  dict               nargs                  for key  item in args items                    if isinstance key  unicode                       key   key encode db encoding                  nargs key    db literal item              args   nargs         else              args   tuple map db literal  args           try              query   query   args         except TypeError as m              raise ProgrammingError str m     Given that I am entering any extra parameters  I got rid of all of  if args      branch  Now it works

User · Answer

You can try this code   cur execute   SELECT   FROM records WHERE email LIKE  s    search       You can see the documentation

User · Answer

According PEP8 I prefer to execute SQL in this way   cur   con cursor     There is no need to add single-quota to the surrounding of   s     because the MySQLdb precompile the sql according to the scheme type   of each argument in the arguments list  sql    SELECT   FROM records WHERE email LIKE  s   args    search    cur execute sql  args    In this way  you will recognize that the second argument args of execute method  must be a list of arguments   May this helps you

User · Answer

Instead of this   cur execute   SELECT   FROM records WHERE email LIKE   s    search     Try this   cur execute   SELECT   FROM records WHERE email LIKE  s    search      See the MySQLdb documentation   The reasoning is that execute s second parameter represents a list of the objects to be converted  because you could have an arbitrary number of objects in a parameterized query   In this case  you have only one  but it still needs to be an iterable  a tuple instead of a list would also be fine

User · Answer

cur execute   SELECT   FROM records WHERE email LIKE  s    search       I do not why  but this works for me   rather than use   s

[python] Python MySQLdb TypeError: not all arguments converted during string formatting

Examples related to python

Examples related to python-2.7