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?
You have:
read_sql("... where business_id=%s;",connection) % df['id'][i]
Must be:
read_sql("... where business_id=%s;" % df['id'][i], connection)