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

我发现了很多信息关于paramstyle似乎暗示这可能是你想要的,但是根据这个wiki使用paramstyle图书馆使用,和大多数人不允许您更改:

paramstyle是特定于您使用图书馆,和信息,你必须使用它使用。这可能是最讨厌这个标准的一部分。(少数允许您设置不同的paramstyles,但不是标准的行为)

我发现了一些文章,谈到MySQLdb允许,但显然它并t作为一个表示,并为他们工作。

Answer 2

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

您可以使用命名参数

即。

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

would effectively execute 1=1

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

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

并使用它:

from MyNewCursorModule import MyNewCursor

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

(你也可以给类的连接。指针函数来创建它如果你想使用正常执行大部分的时间(一个临时覆盖))

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

Answer 3

我不推荐这样做,但是最简单的解决方案是monkeypatch光标类:

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

友情链接