how to safely generate a SQL LIKE statement using python db-api

python sql sql-like python-db-api
laramichaels picture laramichaels · Jan 19, 2010 · Viewed 30.6k times · Source

I am trying to assemble the following SQL statement using python's db-api:

SELECT x FROM myTable WHERE x LIKE 'BEGINNING_OF_STRING%';

where BEGINNING_OF_STRING should be a python var to be safely filled in through the DB-API. I tried

beginningOfString = 'abc'

cursor.execute('SELECT x FROM myTable WHERE x LIKE '%s%', beginningOfString) 
cursor.execute('SELECT x FROM myTable WHERE x LIKE '%s%%', beginningOfString)

I am out of ideas; what is the correct way to do this?

Answer

unutbu picture unutbu · Jan 19, 2010

It's best to separate the parameters from the sql if you can. Then you can let the db module take care of proper quoting of the parameters.

sql='SELECT x FROM myTable WHERE x LIKE %s'
args=[beginningOfString+'%']
cursor.execute(sql,args)

Related questions

Regular expression to match start of filename and filename extension

What is the regular expression to match strings (in this case, file names) that start with 'Run' and have a filename extension of '.py'? The regular expression should match any of the following: RunFoo.py RunBar.py Run42.py …

Read More
Python String Formats with SQL Wildcards and LIKE

I'm having a hard time getting some sql in python to correctly go through MySQLdb. It's pythons string formatting that is killing me. My sql statement is using the LIKE keyword with wildcards. I've tried a number of different things …

Read More
Connecting to Microsoft SQL server using Python

I am trying to connect to SQL through python to run some queries on some SQL databases on Microsoft SQL server. From my research online and on this forum the most promising library seems to be pyodbc. So I have …

Read More