I am trying to use parameters for SQL queries with Pandas:
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('postgresql:///mydb')
conn = engine.connect()
sql = '''
SELECT id, name, age FROM person WHERE name=%{name}s
'''
df = pd.read_sql(sql, conn, params={'name': 'John Doe'})
print(df)
From how I understand the Pandas documentation this should work. But it throws a ‘Type Error: dict is not a sequence’.
The solution is to wrap the SQL in text() from SQLAlchemy and use a different placeholder syntax:
import pandas as pd
from sqlalchemy import create_engine, text
engine = create_engine('postgresql:///mydb')
conn = engine.connect()
sql = '''
SELECT id, name, age FROM person WHERE name=:name
'''
df = pd.read_sql(text(sql), conn, params={'name': 'John Doe'})
print(df)
I am fine using text() because it makes the code also independent of the backend. But what is wrong with the first approach? IMHO, it exactly follows the docs.
>Solution :
It is expecting parenthesis not curly brackets:
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('postgresql:///mydb')
conn = engine.connect()
sql = '''
SELECT id, name, age FROM person WHERE name=%(name)s
'''
df = pd.read_sql(sql, conn, params={'name': 'John Doe'})
print(df)