Pandas + SQLAlchemy + psycopg2: correct %(name)s placeholders to fix read_sql_query TypeError in PostgreSQL

Learn why pandas read_sql_query with SQLAlchemy and psycopg2 raises TypeError 'dict is not a sequence' in PostgreSQL, and how %(name)s placeholders fix it.

When using pandas to run parameterized SQL against PostgreSQL through SQLAlchemy and psycopg2, a subtle mismatch in placeholder syntax can derail the whole query. With SQLAlchemy 2.0.40, pandas 2.2.3, and psycopg2-binary 2.9.10, an attempt to rely on pandas’ native parameter substitution may raise a TypeError if the placeholders do not follow the driver’s expected style.

Reproducing the issue

The query looks harmless, but the placeholder format is the culprit. Executing it ends with TypeError: dict is not a sequence.

qry_bad = """
select *
FROM public.bq_results br 
WHERE cast("eventDate" as date) between 
  TO_DATE('%test_start_date', 'YYYYMMDD') AND TO_DATE('%test_end_date', 'YYYYMMDD')
limit 10000
"""
db_engine = create_engine(f'postgresql://{self.user}:{self.password}@{self.host}:{self.port}/{self.database}')
out_df = pd.read_sql_query(
    qry_bad,
    db_engine,
    params={"test_start_date": "20250101", "test_end_date": "20250131"}
)

What actually goes wrong

The behavior is defined by pandas.read_sql_query and, more specifically, by the underlying DB-API driver. The function defers parameter binding to the driver, which expects placeholders in a driver-specific format. The documentation explicitly points this out and gives the required style for psycopg2.

params : list, tuple or mapping, optional, default: None

List of parameters to pass to execute method. The syntax used to pass parameters is database driver dependent. Check your database driver documentation for which of the five syntax styles, described in PEP 249’s paramstyle, is supported. Eg. for psycopg2, uses %(name)s so use params={‘name’ : ‘value’}.

In this case, the placeholders were written as '%test_start_date' and '%test_end_date', which do not match psycopg2’s %(name)s format. That mismatch is what produces the TypeError during execution.

The fix

Use psycopg2’s %(name)s paramstyle in the SQL string and keep passing a mapping to params. That’s it.

qry_ok = """
select *
FROM public.bq_results br 
WHERE cast("eventDate" as date) between 
  TO_DATE(%(test_start_date)s, 'YYYYMMDD') AND TO_DATE(%(test_end_date)s, 'YYYYMMDD')
limit 10000
"""
db_engine = create_engine(f'postgresql://{self.user}:{self.password}@{self.host}:{self.port}/{self.database}')
out_df = pd.read_sql_query(
    qry_ok,
    db_engine,
    params={"test_start_date": "20250101", "test_end_date": "20250131"}
)

If you are wondering whether '%test_start_date' should be written differently, the answer is yes: it should be %(test_start_date)s, and the same applies to the end date.

Why this matters

read_sql_query does not normalize placeholder styles. It expects you to speak the driver’s dialect. For psycopg2, that means the %(name)s pattern. Using a different style leads to confusing errors and wasted time, even when the params mapping itself is correct.

Takeaways

When wiring pandas to PostgreSQL via psycopg2, always use %(name)s placeholders in the SQL and pass a mapping to params. This alignment with the driver’s paramstyle resolves the TypeError and lets the query run as intended.