python avoiding %s in sql query

Question

I am trying to query a mysql db from python but having troubles generating the query ebcasue of the wildcard % and python's %s. As a solution I find using ?, but when I run the following,

query = '''select * from db where name like'Al%' and date = '%s' ''', myDateString

I get an error

cursor.execute(s %'2015_05_21')

ValueError: unsupported format character ''' (0x27) at index 36 (the position of %)

How can i combine python 2.7 string bulding and sql wildcards? (The actual query is a lot longer and involves more variables)

Possibly unrelated, but you'll typically need to use LIKE (not =) to use a wildcard like 'Al%' — StuartLC
– StuartLC, Commented May 27, 2014 at 4:50

alecxe · Accepted Answer · 2014-05-27 04:52:31Z

2

First of all, you need to escape the percent sign near the Al:

'''select * from db where name like 'Al%%' and date = '%s''''

Also, follow the best practices and pass the query parameters in the second argument to execute(). This way your query parameters would be escaped and you would avoid sql injections:

query = """select * from db where name like 'Al%%' and date = %s"""
cursor.execute(query, ('2015_05_21', ))

answered May 27, 2014 at 4:52

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

2 Comments

chrise Over a year ago

thanks, that works for the example. in my query, I need to combine wildcard and string building like select * from table where name like 'A%% %s' and date = %s. seems like sql does not like having %s within quotation marks. How can i work around that?

alecxe Over a year ago

@chrise in that case you can use format() with MySQLdb.escape_string, e.g. query = """select * from db where name like 'Al%% {0}' and date = %s""".format(MySQLdb.escape_string(param)).

Burhan Khalid · Accepted Answer · 2014-05-27 04:52:23Z

1

Two things:

Don't use string formatting ('%s' % some_var) in SQL queries. Instead, pass the string as a sequence (like a list or a tuple) to the execute method.

You can escape your % so Python will not expect a format specifier:

q = 'SELECT foo FROM bar WHERE zoo LIKE 'abc%%' and id = %s'
cursor.execute(q, (some_var,))

answered May 27, 2014 at 4:52

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

Comments

khampson · Accepted Answer · 2014-05-27 04:51:41Z

0

Use the format syntax for Python string building, and %s for SQL interpolation. That way they don't conflict with each other.

answered May 27, 2014 at 4:51

khampson

15.5k4 gold badges45 silver badges44 bronze badges

Comments

Matt Auerbach · Accepted Answer · 2014-05-27 04:52:44Z

0

You are not using the ? correctly.

Here's an example: command = '''SELECT M.name, M.year FROM Movie M, Person P, Director D WHERE M.id = D.movie_id AND P.id = D.director_id AND P.name = ? AND M.year BETWEEN ? AND ?;'''

*Execute the command, replacing the placeholders with the values of the variables in the list [dirName, start, end]. *

cursor.execute(command, [dirName, start, end])

So, you want to try:

cursor.execute(query,'2015_05_21')

answered May 27, 2014 at 4:52

Matt Auerbach

2012 silver badges9 bronze badges

1 Comment

Burhan Khalid Over a year ago

Did you actually try this code? It won't work. .execute expects a sequence, and you are passing it a string.

Collectives™ on Stack Overflow

python avoiding %s in sql query

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related