pandassqlalchemy

How to migrate pandas read_sql from psycopg2 to sqlalchemy with a tuple as one of the query params

With pandas=1.4.0, it emits a Warning about not using psycopg2 directly within read_sql, but to use sqlalchemy. While attempting to do such a migration, I can not resolve how to pass a tuple as one of the query parameters. For example, this presently works:

import pandas as pd
import psycopg2

read_sql(
    "SELECT * from news where id in %s",
    psycopg2.connect("dbname=mydatabase"),
    params=[(1, 2, 3),],
)

attempting to migrate this to sqlalchemy like so:

import pandas as pd
read_sql(
    "SELECT * from news where id in %s",
    "postgresql://localhost/mydatabase",
    params=[(1, 2, 3),],
)

results in

...snipped...
  File "/opt/miniconda3/envs/prod/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/opt/miniconda3/envs/prod/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
TypeError: not all arguments converted during string formatting

So how do I pass a tuple as a params argument within pandas read_sql?

Solution

  • Wrap your query with a SQLAlchemy text object, use named parameters and pass the parameter values as a dictionary:

    import pandas as pd
from sqlalchemy import text
read_sql(
    text("SELECT * from news where id in :ids"),
    "postgresql://localhost/mydatabase",
    params={'ids': (1, 2, 3),},
)