pandas read_sql with parameters and wildcard operator

Question

I am trying to retrieve data from sql through python with the code:

query = ("SELECT stuff FROM TABLE WHERE name like %%(this_name)s%")
result = pd.read_sql(query,con=cnx,params={'this_name':some_name})

The code above works perfectly when I don't have to pass the wildcard operator %. However, in this case the code doesn't work. How can I pass in the query the wildcard operator? Thank you.

Possible duplicate of [Pandas read\_sql with parameters](http://stackoverflow.com/questions/24408557/pandas-read-sql-with-parameters) — Mathias711, Sep 14 '16 at 10:42

score 3 · Answer 1 · answered Sep 14 '16 at 12:06

3

Consider concatenating the wildcard operator, %, to passed in value:

query = ("SELECT stuff FROM TABLE WHERE name LIKE %(this_name)s") 
result = pd.read_sql(query,con=cnx, params={'this_name': '%'+ some_name +'%'})

answered Sep 14 '16 at 12:06

Parfait

104,375
17
94
125

score 0 · Answer 2 · answered Jan 13 '20 at 19:08

Try using a docstring and some quotes around the like pattern. It appears you are using the pyformat paramstyle.

query = """SELECT stuff FROM TABLE WHERE name LIKE '%%(this_name)s%'"""
results = pd.read_sql(query, con=cnx, params={'this_name': some_name})

pandas read_sql with parameters and wildcard operator

2 Answers2

Linked