How to pass a list as a param to pandas read_sql_query with sqlalchemy
In my relatively limited experience with python I've found that the only thing that has a worse documentation website than pandas is sqlalchemy. So I guess it shouldn't have actually been all that surprising that it was rather hard to work out how to do this, given that it required combining those two libraries. After a fair bit of docs scrolling and stackoverflow searching I managed to cobble together some answers into the right incantation to get this working.
import pandas as pd
import sqlalchemy as sa
connection_url = sa.engine.URL.create(
drivername="postgresql",
database="database_name",
username="user_name",
password="password,
host="localhost"
)
engine = sa.create_engine(connection_url)
ids = ['123', '456', '789']
df = pd.read_sql_query(
sa.text("""
select
*
from users
where id = any(:ids)
"""),
con=engine,
params={
'ids': ids
}
)
I can't entirely explain why but it only works if you pass in the query after running it through the sa.text() function