Passing table name as a parameter in psycopg2

Question

I have the following code, using pscyopg2:

sql = 'select %s from %s where utctime > %s and utctime < %s order by utctime asc;'
data = (dataItems, voyage, dateRangeLower, dateRangeUpper)
rows = cur.mogrify(sql, data)

This outputs:

select 'waterTemp, airTemp, utctime' from 'ss2012_t02' where utctime > '2012-05-03T17:01:35+00:00'::timestamptz and utctime < '2012-05-01T17:01:35+00:00'::timestamptz order by utctime asc;

When I execute this, it falls over - this is understandable, as the quotes around the table name are illegal.

Is there a way to legally pass the table name as a parameter, or do I need to do a (explicitly warned against) string concatenation, ie:

voyage = 'ss2012_t02'
sql = 'select %s from ' + voyage + ' where utctime > %s and utctime < %s order by utctime asc;'

Cheers for any insights.

Answer 1

According to the official documentation:

If you need to generate dynamically an SQL query (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module.

The sql module is new in psycopg2 version 2.7. It has the following syntax:

from psycopg2 import sql

cur.execute(
    sql.SQL("insert into {table} values (%s, %s)")
        .format(table=sql.Identifier('my_table')),
    [10, 20])

More on: https://www.psycopg.org/docs/sql.html#module-usage

[Update 2017-03-24: AsIs should NOT be used to represent table or fields names, the new sql module should be used instead: https://stackoverflow.com/a/42980069/5285608 ]

Also, according to psycopg2 documentation:

Warning: Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

Answer 2

Per this answer you can do it as so:

import psycopg2
from psycopg2.extensions import AsIs

#Create your connection and cursor...

cursor.execute("SELECT * FROM %(table)s", {"table": AsIs("my_awesome_table")})

Answer 3

The table name cannot be passed as a parameter, but everything else can. Thus, the table name should be hard coded in your app (Don't take inputs or use anything outside of the program as a name). The code you have should work for this.

On the slight chance that you have a legitimate reason to take an outside table name, make sure that you don't allow the user to directly input it. Perhaps an index could be passed to select a table, or the table name could be looked up in some other way. You are right to be wary of doing this, however. This works, because there are relatively few table names around. Find a way to validate the table name, and you should be fine.

It would be possible to do something like this, to see if the table name exists. This is a parameterised version. Just make sure that you do this and verify the output prior to running the SQL code. Part of the idea for this comes from this answer.

SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' and table_name=%s LIMIT 1

Answer 4

This is a workaround I have used in the past

query = "INSERT INTO %s (col_1, col_2) VALUES (%%s, %%s)" % table_name
cur.execute(query, (col_1_var, col_2_var))

Hope it help :)