Package Versions:
I am trying to run a query using pandas' native param substitution, but I can't seem to get it to run without erroring. I tried simplifying the query to:
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
but I get error:
TypeError: dict is not a sequence
when running:
df = pd.read_sql_query(query, self.__engine, params={"test_start_date": "20250101", "test_end_date": "20250131"})
where
self.__engine = create_engine(f'postgresql://{self.user}:{self.password}@{self.host}:{self.port}/{self.database}')
The documentation of read_sql_query says the following:
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’}.
Since you use the psycopg2 driver the parameters should be noted as @JonSG has mentioned. It should be:
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
Hope this works.