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

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?

Asked by: Freddie967 | Posted: 30-11-2021

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.

Answered by: Kimberly820 | Posted: 01-01-2022

Answer 2

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

you can however use named parameters


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:

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

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

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

Answered by: Ada134 | Posted: 01-01-2022

Answer 3

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

Answered by: Brad275 | Posted: 01-01-2022

Similar questions

python - Django: Arbitrary number of unnamed parameters

I have a Django model with a large number of fields and 20000+ table rows. To facilitate human readable URLs and the ability to break down the large list into arbitrary sublists, I would like to have a URL that looks like this: /browse/<name1>/<value1>/<name2>/<value2>/ .... etc .... where 'name' maps to a model attribute and 'value' is the search criteria for that...

python - variables as parameters in field options

I want to create a model, that will set editable=False on creation, and editable=True on editing item. I thought it should be something like this: home = models.ForeignKey(Team, editable=lambda self: True if else False) But it doesn't work. Maybe something with overriding the init can help me, but i don't sure what can do the trick. I know i can check for

c# - How to analyse .exe parameters inside the program?

I have a program that can have a lot of parameters (we have over +30 differents options). Example: myProgram.exe -t alpha 1 -prod 1 2 -sleep 200 This is 3 Commands (from command pattern object at the end) that each contain some parameters. Inside the code we parse all command (start with -) and get a list of string (split all space) for the parameters. So in fact, we have : string-->Collection ...

python - Default parameters to actions with Django

Is there a way to have a default parameter passed to a action in the case where the regex didnt match anything using django? urlpatterns = patterns('',(r'^test/(?P<name>.*)?$','myview.displayName')) def displayName(request,name): # write name to response or something I have tried setting the third parameter in the urlpatterns to a dictionary containing ' and giving...

python - Loop function parameters for sanity check

I have a Python function in which I am doing some sanitisation of the input parameters: def func(param1, param2, param3): param1 = param1 or '' param2 = param2 or '' param3 = param3 or '' This caters for the arguments being passed as None rather than empty strings. Is there an easier/more concise way to loop round the function parameters to apply such an expression to ...

python - How can I pass all the parameters to a decorator?

I tried to trace the execution of some methods using a decorator. Here is the decorator code: def trace(func): def ofunc(*args): func_name = func.__name__ xargs = args print "entering %s with args %s" % (func_name,xargs) ret_val = func(args) print "return value %s" % ret_val print "exiting %s" % (func_nam...

parameters - Python Newbie: Returning Multiple Int/String Results in Python

I have a function that has several outputs, all of which "native", i.e. integers and strings. For example, let's say I have a function that analyzes a string, and finds both the number of words and the average length of a word. In C/C++ I would use @ to pass 2 parameters to the function. In Python I'm not sure what's the right solution, because integers and strings are not passed by reference but by value (at leas...

Print out list of function parameters in Python

Is there a way to print out a function's parameter list? For example: def func(a, b, c): pass print_func_parametes(func) Which will produce something like: ["a", "b", "c"]

python - How to create a decorator that can be used either with or without parameters?

I'd like to create a Python decorator that can be used either with parameters: @redirect_output("somewhere.log") def foo(): .... or without them (for instance to redirect the output to stderr by default): @redirect_output def foo(): .... Is that at all possible? Note that I'm not looking for a different solution to the problem of redirectin...

python - Scope of lambda functions and their parameters?

This question already has answers here:

Still can't find your answer? Check out these communities...

PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python