abutremutante abutremutante - 1 month ago 19
Python Question

Python / Pandas / PyMySQL - %s synthax

I'm trying to do the following operation:

import pymsql
import pandas as pd

df:

id height_code name ...
1 34 123123 Jesus
2 84 3421 Heaps
3 23 45243 Ornitorrincus
...

connection=pymysql.connect(host=xxx,user=xxxx,password=xxxx,db=xxxx)

for i in df.index:
df2=pd.read_sql("select business_id,name,color from table123 where business_id=%s;",connection) % df['id'][i]


The idea here is only to access a giant sql and filter table123 using only the rows where
business_id = df['id'][i]


However, I'm getting the following error:

pandas.io.sql.DatabaseError: Execution failed on sql 'select business_id,nome,qualificacao_id from socio_administrador where business_id=%s;': (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 '%s' at line 1")


So, apparently I'm having trouble with the
%s
synthax in pymsql. Tried to have a look at their manual but didn't find a nice explanation about that.

Could anybody help?

DYZ DYZ
Answer Source

You have:

read_sql("... where business_id=%s;",connection) % df['id'][i]

Must be:

read_sql("... where business_id=%s;" % df['id'][i], connection)