如何使Python Msqldb模块使用?而不是%s作为查询参数?

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

MySqlDb是一个很棒的Python模块——但是有一部分非常烦人。查询参数如下所示

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

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

而在已知的宇宙中的其他地方(oracle、sqlserver、access、sybase…)，它们看起来是这样的

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

这意味着如果你想要便携你必须在两个符号之间切换?还有%s，真的很烦人。(请不要告诉我使用ORM层——我会勒死你的)。

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

假设您可以说服mysqldb使用标准语法，但我还没有让它正常工作。有什么建议吗?

3 个解决方案

#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的信息似乎暗示着它可能是你想要的，但是根据这个维基你必须使用你的库使用的paramstyle，而且大多数都不允许你改变它:

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)

paramstyle是特定于你使用的库的，而且是信息性的——你必须使用它使用的库。这可能是这个标准中最烦人的部分。(少数允许您设置不同的参数，但这不是标准行为)

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

我发现一些讨论MySQLdb的文章允许这样做，但显然它不允许，因为有人指出它对他们不起作用。

#2

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

从我所看到的你不能使用的东西?对于带有MySQLdb的参数标记(开箱)

you can however use named parameters

但是，您可以使用命名参数。

i.e.

即。

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

would effectively execute 1=1

能有效执行1 = 1

in mysql

在mysql中

but sort of like Eli answered (but not hackish)

但有点像伊莱回答的(但不是hackish)

you could instead do:

你可以做的:

MyNewCursorModule.py

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

(你也可以给连接一个类。游标函数来创建它，如果您希望在大多数情况下使用普通的execute(临时覆盖)

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

…你也可以把简单的替换改为稍微正确一点的替换(假设有一种方法可以避开问号)，但这是我留给你的东西:

#3

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

我不建议这样做，但最简单的解决方案是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

#1