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

CodePudding user response：

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