CODEWITHSUNDEEP
pythonsql
chrise
chrise

Reputation: 4253

python avoiding %s in sql query

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)

Upvotes: 3

Views: 8551

Answers (4)

Matt Auerbach
Matt Auerbach

Reputation: 201

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

Upvotes: 0

alecxe
alecxe

Reputation: 473783

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

Upvotes: 2

Burhan Khalid
Burhan Khalid

Reputation: 174624

Two things:

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

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

Upvotes: 1

khampson
khampson

Reputation: 15296

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

Upvotes: 0

Related Questions