Why do I get "TypeError: not all arguments converted during string formatting" when trying to use a string in a parameterized SQL query?

Question

I have this code:

#! /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")

But I get an error like:

Traceback (most recent call last):
  File "./lookup", line 27, in <module>
    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

What is wrong, and how do I fix it?

---

_{The same problem occurs in sqlitem, reported differently; see sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied for details.}

Answer 1

You can try this code:

cur.execute( "SELECT * FROM records WHERE email LIKE %s", (search,) )

You can see the documentation

Answer 2

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.

Answer 3

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

Answer 4

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).

Answer 5

cur.execute( "SELECT * FROM records WHERE email LIKE %s", (search,) )

I do not why, but this works for me . rather than use '%s'.

Answer 6

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.

Answer 7

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> select * from methods limit 1;
+----------+--------------------+------------+
| MethodID | MethodDescription  | MethodLink |
+----------+--------------------+------------+
|       32 | Autonomous Sensing | NULL       |
+----------+--------------------+------------+
1 row in set (0.01 sec)

mysql> 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.

>>> search = "test"
>>> "SELECT * FROM records WHERE email LIKE '%s'" % con.literal(search)
"SELECT * FROM records WHERE email LIKE ''test''"
>>> 

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:

>>> "SELECT * FROM records WHERE email LIKE %s" % con.literal(search)
"SELECT * FROM records WHERE email LIKE 'test'"
>>> 

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.

Answer 8

'%' 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)