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 {} values (%s, %s)")
        .format(sql.Identifier('my_table')),
    [10, 20])

More on: http://initd.org/psycopg/docs/sql.html#module-psycopg2.sql

[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.