pymysql error attempting to run query : sqlalchemy.exc.ProgrammingError [duplicate]

Question

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

Tim Roberts · Accepted Answer · 2022-05-18 20:39:47Z

1

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

answered May 18, 2022 at 20:39

Tim Roberts

55.2k4 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pymysql error attempting to run query : sqlalchemy.exc.ProgrammingError [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related