Passing table name as a function parameter in Python/Pandas/PostgreSQL

Question

I am trying to call table_name in my SQL query using the 'new' secure way (it is good against SQL injection attacks) as in here: http://initd.org/psycopg/docs/sql.html#module-psycopg2.sql but I can't make it work with my code (table_name is a parameter of my function).

import pandas as pd
import psycopg2 as pg
import pandas.io.sql as psql
from psycopg2 import sql

sql_query = sql.SQL("SELECT * FROM {} limit %d offset %d" % (table_name, chunk_size, offset)).format(sql.Identifier(table_name)) 
df = psql.read_sql_query(sql_query, connection)

Any suggestions?

UPDATE (after a suggested answer):

I tried with

def import_table(self, connection, table_name, chunk_size, offset):
    sql = "SELECT * FROM {} limit %d offset %d" 
    qry = sql.format(**dict(table=table_name)) %(chunk_size, offset)
    df_piece = psql.read_sql_query(qry, connection)

And then calling it with:

df = pd.concat(import_table(pg.connect("dbname=my_db user=my_user"), 'table_name', 100000, 0))

But I am getting an error:

---> 30             qry = sql_ct.format(**dict(table=table_name)) %  (chunk_size, offset)
 31             df_piece = psql.read_sql_query(qry, connection)
 32 

IndexError: tuple index out of range

MaxU - stand with Ukraine · Accepted Answer · 2017-05-10 11:05:05Z

2

Table names/column names can't be parameterized this way.

It can and should be applied to values.

Please read about Prepared statement

Consider the following technique:

# param. for str format:      vvvvv
In [9]: qry = "SELECT * FROM {table} limit %d offset %d"

In [10]: qry.format(**dict(table='my_table')) %(10, 500)
Out[10]: 'SELECT * FROM my_table limit 10 offset 500'

UPDATE:

In [53]: my_table = 'table_name'

In [54]: qry
Out[54]: 'SELECT * FROM {table} limit %d offset %d'

In [55]: qry.format(**dict(table=my_table)) %(10, 500)
Out[55]: 'SELECT * FROM table_name limit 10 offset 500'

edited May 10, 2017 at 11:05

answered May 10, 2017 at 10:23

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

3 Comments

SLack A Over a year ago

I updated my answer with a trial per your suggestion and an error I get.

MaxU - stand with Ukraine Over a year ago

@SLackA, you missed parameter name table in {table} - you used {}. replace "SELECT * FROM {} limit %d offset %d" with "SELECT * FROM {table} limit %d offset %d"

SLack A Over a year ago

I can see what you are saying but we are having a difference in our code. I need to pass my_table as a variable in qry.format(**dict(table='my_table')) %(10, 500), not as a string. I am trying to do qry.format(**dict(table=my_table)) %(10, 500) and getting another error now

28             sql_ct = "SELECT * FROM {table_name} limit %d offset %d" ---> 29             qry = sql_ct.format(**dict(table=table_name)) %(chunk_size, offset)      30             df_piece = psql.read_sql_query(qry, connection)      31   KeyError: 'table_name'

Collectives™ on Stack Overflow

Passing table name as a function parameter in Python/Pandas/PostgreSQL

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related