How do you make the Python Msqldb module use ? in stead of %s for query parameters?

Question

MySqlDb is a fantastic Python module -- but one part is incredibly annoying. Query parameters look like this

cursor.execute("select * from Books where isbn=%s", (isbn,))

whereas everywhere else in the known universe (oracle, sqlserver, access, sybase...) they look like this

cursor.execute("select * from Books where isbn=?", (isbn,))

This means that if you want to be portable you have to somehow switch between the two notations ? and %s, which is really annoying. (Please don't tell me to use an ORM layer -- I will strangle you).

Supposedly you can convince mysqldb to use the standard syntax, but I haven't yet made it work. Any suggestions?

Answer 1

I found a lot of information out there about paramstyle that seemed to imply it might be what you wanted, but according to this wiki you have to use the paramstyle your library uses, and most of them do not allow you to change it:

paramstyle is specific to the library you use, and informational - you have to use the one it uses. This is probably the most annoying part of this standard. (a few allow you to set different paramstyles, but this isn't standard behavior)

I found some posts that talked about MySQLdb allowing this, but apparently it doesn't as someone indicated it didn't work for them.

Answer 2

I don't recommend doing this, but the simplest solution is to monkeypatch the Cursor class:

from MySQLdb.cursors import Cursor
old_execute = Cursor.execute
def new_execute(self, query, args):
   return old_execute(self, query.replace("?", "%s"), args) 
Cursor.execute = new_execute

Answer 3

From what I can see you cannot use '?' for a parameter marker with MySQLdb (out of box)

you can however use named parameters

i.e.

cursor.execute("%(param1)s = %(param1)s", {'param1':1})

would effectively execute 1=1

in mysql

but sort of like Eli answered (but not hackish)

you could instead do:

MyNewCursorModule.py

import MySQLdb.cursors import Cursor

class MyNewCursor(Cursor):
  def execute(self, query, args=None):
     """This cursor is able to use '?' as a parameter marker"""
     return Cursor.execute(self, query.replace('?', '%s'), args)

  def executemany(self, query, args):
     ...implement...

in this case you would have a custom cursor which would do what you want it to do and it's not a hack. It's just a subclass ;)

and use it with:

from MyNewCursorModule import MyNewCursor

conn = MySQLdb.connect(...connection information...
                       cursorclass=MyNewCursor)

(you can also give the class to the connection.cursor function to create it there if you want to use the normal execute most of the time (a temp override))

...you can also change the simple replacement to something a little more correct (assuming there is a way to escape the question mark), but that is something I'll leave up to you :)