Can I parameterise tables using Pandas read_sql function?

Question

I got a database with multiple projects which follows a similar structure to the following:

Schemas for our client Bob: bob_legal, bob_docs, bob_misc
Schemas for our client Jess: jess_legal, jess_docs, jess_misc

I am currently using f-strings to build the query:

query = f"SELECT * FROM {client}_legal.my_table WHERE country = '{country_name}';"
result = pd.read_sql(query, con)

But I want to parameterise building the query. I tried using the params parameter in panda's read_sql function. But it doesn't work as it outputs the table like this:

SELECT * FROM 'bob'_legal.my_table WHERE country = 'canada';"

The table bob now is wrapped in single quotes and it creates a SQL syntax error. Is there a different way to do achieve the job but still using the read_sql function? There does not seem to be an option for parameterising tables.

Timeless · Accepted Answer · 2023-09-27 12:28:52Z

I assume you're using PostgreSQL or any database flavor that supports schemas.

With client = "bob", your code works just fine for me. Alternatively (and highly inspired by @amn34's answer), you can try something like below with Identifiers :

from sqlalchemy import create_engine
from psycopg2.sql import Identifier, SQL
import pandas as pd

d1 = {
    "user": "xxx", "pass": "xxx",
    "host": "xxx", "port": "xxx", "db": "xxx"
}

# SELECT * FROM 'bob'_legal.my_table WHERE country = 'canada';"
countr_name = "canada"

d2 = {
    "schema_name": Identifier("bob_legal"),
    "table_name": Identifier("my_table"),
    "field_name": Identifier("country"),
}

engine = create_engine("postgresql://{user}:{pass}@{host}:{port}/{db}".format(**d1))
query = SQL("SELECT * FROM {schema_name}.{table_name} WHERE {field_name} = %s").format(**d2)

df = pd.read_sql_query(
    query.as_string(engine.raw_connection().cursor()),
    engine, params=(country_name, )
)

Collectives™ on Stack Overflow

Can I parameterise tables using Pandas read_sql function?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related