psycopg2 interpolate table name in executemany statement

Question

I am trying to insert data into a table. The table is determined in the beging of the program and remains constant throughout. How do I interpolate the table name in an execute many statement like the one below?

tbl = 'table_name'
rows = [{'this':x, 'that': x+1} for x in range(10)]
cur.executemany("""INSERT INTO %(tbl)s 
                  VALUES(
                          %(this)s,
                          %(that)s
                  )""", rows)

http://stackoverflow.com/questions/13793399/passing-table-name-as-a-parameter-in-psycopg2 — Ashalynd, Nov 11 '15 at 22:24
So, the table name cannot be interpolated: http://stackoverflow.com/questions/13793399/passing-table-name-as-a-parameter-in-psycopg2 — mgcdanny, Nov 11 '15 at 23:00

score 0 · Accepted Answer · answered Mar 26 '17 at 09:09

As stated in the official documentation: "Only query values should be bound via this method: it shouldn’t be used to merge table or field names to the query. 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."

It has the following syntax:

from psycopg2 import sql
tbl = 'table_name'
rows = [{'this':x, 'that': x+1} for x in range(10)]
cur.execute(
    sql.SQL("INSERT INTO {} VALUES (%(this)s, %(that)s);"""")
        .format(sql.Identifier(tbl)), rows)

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

psycopg2 interpolate table name in executemany statement

1 Answers1