Parameterized queries using MySQLdb in Python 3
Question: I'm attempting to insert data into a MySQL database through a Python script
using parameterized queries rather than formatting the parameters into a
string and opening the application up to SQL injection.

Here is the Python code:

    
    
    #!/usr/bin/python3
    
    import MySQLdb as mdb
    
    conn = mdb.connect("localhost", "fakeuser", "fakepassword", "testdb")
    c = conn.cursor()
    c.execute("INSERT INTO test VALUES (%s)", ("Test", ))
    conn.commit()
    conn.close()
    

When I run the script, I get the following stack trace:

    
    
    Traceback (most recent call last):
      File "./load.py", line 7, in <module>
        c.execute("INSERT INTO test VALUES (%s)", ("Test", ))
      File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 184, in execute
        self.errorhandler(self, exc, value)
      File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/connections.py", line 37, in defaulterrorhandler
        raise errorvalue
      File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 171, in execute
        r = self._query(query)
      File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 330, in _query
        rowcount = self._do_query(q)
      File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 294, in _do_query
        db.query(q)
    _mysql_exceptions.ProgrammingError: (1064, "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 '%s)' at line 1")
    

However, every bit of documentation I've read suggests `%s` is the correct
placeholder. I've tried to enclose the `%s` in single quotes, but that causes
the statement to insert that string literally rather than substitute the given
value, which is what I'd expect.

Here are the relevant software versions:

  * Python 3.2.3
  * MySQL_python-1.2.3-py3.2
  * MySQL 5.5.40-36.1

Note: I'm using a shared host and cannot upgrade the software.


Answer: The MySQL-Python project does not support Python 3—see [the project web
page](http://sourceforge.net/projects/mysql-python/), which has said "Python 3
support coming soon" for years now, and [the cheese shop
entry](https://pypi.python.org/pypi/MySQL-python/1.2.5), which offers only a
Python 2.7 version and says:

> Python-3.0 will be supported in a future release.

There's no "-py3.2" distribution available that I've heard of (or could find
by searching); your host may have something silly (like
[this](http://stackoverflow.com/a/15859504/2359271), for example) to get the
library to install on Python 3, but being able to install a library does not
guarantee it will actually work.

Here's [an example of someone running into the same problem on a shared
host](http://www.gossamer-threads.com/lists/python/python/1046062):

> Today i switched to Python v3.2.3 on my remote web server at HostGator. My
> cgi-script which it works at python v2.6.6 now produces theses errors:
>  
>  
>     --> -->
>     Traceback (most recent call last):
>     File "/opt/python3/lib/python3.2/site-
> packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line
> 171, in execute
>     r = self._query(query)
>     File "/opt/python3/lib/python3.2/site-
> packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line
> 330, in _query
>     rowcount = self._do_query(q)
>     File "/opt/python3/lib/python3.2/site-
> packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line
> 294, in _do_query
>     db.query(q)
>     _mysql_exceptions.ProgrammingError: (1064, "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 '%s' at line 1")
>  

You're right about the `%s` syntax being correct for the library;
unfortunately, something about it isn't playing nice with Python 3. You could
try using the named parameter style instead, and see if that gets you
anywhere, but the best thing would be to stop using an unsupported library
altogether. If your host also provides [an old version of
Connector/Python](http://dev.mysql.com/doc/connector-python/en/connector-
python-versions.html), for example.

