How do you use the Python Msqldb module? instead of% s for query parameters?

Question

MySqlDb is a fantastic Python module, but one part is incredibly annoying. The request parameters are as follows:

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

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

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

Does this mean that if you want to be portable, do you need to somehow switch between the two notations? and% s, which is really annoying. (Please don't tell me to use the ORM layer - I will choke you).

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

Answer 1

paramstyle, , , , , , wiki paramstyle, , :

paramstyle , - , . , , . ( paramstyles, )

, MySQLdb, , -, , - , .

Answer 2

, , '?' MySQLdb ( )

.

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

1=1

mysql

, ( )

:

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

, , , . ;)

:

from MyNewCursorModule import MyNewCursor

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

( connection.cursor, , ( ))

... - ( ), , :)

Answer 3

I do not recommend doing this, but the easiest solution is the monkeypatch class Cursor:

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