Parametric table name for pandas read_sql_query on Postgres

user_6752178 0
Viewed 200

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?

pandas psycopg2 postgresql python
1 Answers

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)
user_62113530
Related