psycopg2 - Using SQL object with execute_values

Question

I'm inserting data using execute_values, which takes a sql query. The query is constructed using psycopg2.sql.SQL as recommended in the documentation, but execute_values won't take that object.

Here's the code I have:

import psycopg2 as pg
from psycopg2 import extras
from psycopg2 import sql

config = {
    'host' : 'localhost',
    'user' : 'username',
    'password' : 'password',
    'dbname' : 'myDatabase'
}

connection = pg.connect(**config)
cursor = connection.cursor()

tableName = 'myTable'
dataset = [[1,2],[3,4],[5,6]]

queryText = "INSERT INTO {table} (uid,value) VALUES %s"
query = sql.SQL(queryText).format(table=sql.Identifier(tableName))

extras.execute_values(cursor,query,dataset)

The last line gives the following error:

AttributeError: 'Composed' object has no attribute 'encode'

If the query is specified directly as a string, as below, then the execution runs.

query = """INSERT INTO "myTable" (uid,value) VALUES %s"""

It's possible to insert the table name into the query using string format, but apparently that shouldn't be done, even at gunpoint. How can I safely insert a variable table name into the query and use execute_values? I can't find a built-in way to convert the SQL object to a string.

user_1995738 · Answer

The parameter sql in execute_values(cur, sql, argslist, template=None, page_size=100) is supposed to be a string:

sql – the query to execute. It must contain a single %s placeholder, which will be replaced by a VALUES list. Example: "INSERT INTO mytable (id, f1, f2) VALUES %s".

Use the as_string(context) method:

extras.execute_values(cursor, query.as_string(cursor), dataset)
connection.commit()

user_9537087 · Answer

As execute_values() expect the sql statement to be a string you can simply user:

 queryText = "INSERT INTO {table} (uid,value) VALUES %s".format(table=sql.Identifier(tableName)

psycopg2 - Using SQL object with execute_values

2 Answers