Python mysql string substitution not working

Question

I've been using this syntax with great success in python mysql.

search = "O'%"   # find names like O'Brien or O'Connell...

cursor.execute ("""
   select userid
      from usertab
    where name like %s
""" , (search))

But sometimes I need to build my sql string before I execute it like the following, but the substitution technique is different than above and doesn't work in all cases.

search = "O'%"   # find names like O'Brien or O'Connell...

sql = """
   select userid
      from usertab
    where name like '%s'
""" % (search)

cursor.execute(sql)

How can I achieve the same kind of string substitution that works well in the first example, without executing the cursor?

mata · Accepted Answer · 2013-06-13 17:36:51Z

2

MySQLdb uses the connection's literal() method to escape the arguments, so you could use:

sql = """
   select userid
      from usertab
    where name like %s
""" % cursor.connection.literal(search)

answered Jun 13, 2013 at 17:36

mata

69.4k10 gold badges168 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

panofish Over a year ago

that works... I want to sit on this for day to see what others might suggest... I'm curious how others approach the same problem.

panofish Over a year ago

this solution becomes cumbersome when you have several substitutions.

mata Over a year ago

what do you mean with several substitutions? you can just pass a tuple, and each element will be escaped individually: "select ... where a = %s and b = %s and ..." % literal((a, b, ...))

panofish Over a year ago

ooo.... interesting. How can I use the short name literal as in your example instead of cursor.connection.literal ?

mata Over a year ago

I just forgot to add that part, but you could just use literal = cursor.connection.literal to make it available as literal if that makes it easier (or connection.literal, no real need to get it from cursor...)

Collectives™ on Stack Overflow

Python mysql string substitution not working

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related