Parametric table name for pandas read_sql_query on Postgres

Question

I cannot find out, how to use pandas.read_sql_query and correctly (= safely against sql injection) parametrize table names (or other sql identifiers). Using sqlalchemy+psycopg2 to access PostgreSQL database.

Example of what doesn't work:

import os
import pandas
from sqlalchemy import create_engine
db = create_engine(os.getenv(POSTGRES_CONNSTRING))
pandas.read_sql_query(sql='select * from %(schema)s.%(table)s',
                      con = db,
                      params={'schema': 'public', 'table': 'table_name'})

Yields:

SyntaxError: syntax error at or near "'public'"
LINE 1: select * from 'public'.'table_name'

For psycopg2 the correct solution is described here.

import psycopg2
query = psycopg2.sql.SQL('select * from {schema}.{table}') \
                        .format(schema = psycopg2.sql.Identifier('public'),
                                table = psycopg2.sql.Identifier('table_name'))

But the query is now of type psycopg2.sql.Composed, which I can pass to the execute methods in psycopg2 but not to pandas.read_sql_query.

Is there any good solution to this?

There is no way to parametrize table or schema names. You can only parametrize the values of columns — nacho
– nacho, Commented May 19, 2022 at 9:40

amn34 · Accepted Answer · 2022-06-02 08:44:00Z

2

You can use the as_string method to turn the Composed query into a string that you can pass to Pandas (docs).

import pandas as pd
import psycopg2
from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg2://user:pw@host:port/db')
cur = engine.raw_connection().cursor()

query = psycopg2.sql.SQL('select * from {schema}.{table}') \
                        .format(schema = psycopg2.sql.Identifier('public'),
                                table = psycopg2.sql.Identifier('table_name'))
query_string = query.as_string(cur)

pd.read_sql_query(query_string, engine)

answered Jun 2, 2022 at 8:44

amn34

1062 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parametric table name for pandas read_sql_query on Postgres

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