How to Use a Wildcard (%) in Pandas read_sql()

Question

I am trying to run a MySQL query that has a text wildcard in as demonstrated below:

import sqlalchemy
import pandas as pd

#connect to mysql database
engine = sqlalchemy.create_engine('mysql://user:@localhost/db?charset=utf8')
conn = engine.connect()

#read sql into pandas dataframe
mysql_statement = """SELECT * FROM table WHERE field LIKE '%part%'; """
df = pd.read_sql(mysql_statement, con=conn)

When run I get the error as shown below related to formatting.

TypeError: not enough arguments for format string

How can I use a wild card when reading MySQL with Pandas?

Answer 1

from sqlalchemy import create_engine, text
import pandas as pd

mysql_statement = """SELECT * FROM table WHERE field LIKE '%part%'; """
df = pd.read_sql( text(mysql_statement), con=conn)

You can use text() function from sqlalchemy

Answer 2

Under the hood of pandas, it's using whatever sql engine you've given it to parse the statement. In your case that's sqlalchemy, so you need to figure out how it handles %. It might be as easy as escaping it with LIKE '%%part%%'.

In the case of psycopg, you use the params variable like this:

mysql_statement = """SELECT * FROM table WHERE field LIKE %s; """
df = pd.read_sql(mysql_statement, con=conn, params=("%part%",))

Answer 3

you need to use params options in read_sql pandas method.

# string interpolate 
mysql_statement = """SELECT * FROM table WHERE field LIKE %s; """
df = pd.read_sql(mysql_statement, con=conn, params=("%part%",))

# variable interpolate 
# string interpolate 
val = 'part'
mysql_statement = """SELECT * FROM table WHERE field LIKE %s; """
df = pd.read_sql(mysql_statement, con=conn, params=('%' + val + '%',))