I am running a query using pymysql and passing a string as a parameter for the where clause condition. Code and Error Traceback below:
import pandas as pd
# mysql connection
import pymysql
from sqlalchemy import create_engine
user = 'user'
pwd = 'xxxxx'
host = 'x.com'
port = 3306
database = 'dbname'
engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format(user,pwd,host,database))
con = engine.connect()
c = 'Phoenix, AZ'
query = '''
select * from schema_name.table1
where city = {};
'''.format(c)
df = pd.read_sql(query, con)
Traceback:
File "/Applications/Anaconda/anaconda3/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/Applications/Anaconda/anaconda3/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_query_result
result.read()
File "/Applications/Anaconda/anaconda3/lib/python3.9/site-packages/pymysql/connections.py", line 1156, in read
first_packet = self.connection._read_packet()
File "/Applications/Anaconda/anaconda3/lib/python3.9/site-packages/pymysql/connections.py", line 725, in _read_packet
packet.raise_for_error()
File "/Applications/Anaconda/anaconda3/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/Applications/Anaconda/anaconda3/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', AZ' at line 2")
The value needs to be quoted. It's best to let the database connector do it, to protect against injection attacks:
query = '''
select * from schema_name.table1
where city = %s;'''
df = pd.read_sql(query, con, params=[c])