Psycopg3: How to use Identifier in executemany()

Question

I'm trying in python to use psycopg (version 3.1.4) to truncate a lot of tables with executemany(), but can't figure out what to use as a placeholder in the query. The documentation doesn't really say anything on this as far as I can see.

My code looks like this:

# Make a list of list of Identifiers ('tables' is the list of tables)
vals = [[sql.Identifier(schemaname, t)] for t in tables] 

# Truncate all tables on the schema
query = sql.SQL("truncate table {};")
cursor.executemany(query=query, params_seq=vals)

This gives me the error: psycopg.ProgrammingError: the query has 0 placeholders but 1 parameters were passed

If I instead use %s as a placeholder I get: psycopg.ProgrammingError: cannot adapt type 'Identifier' using placeholder '%PyFormat.AUTO' (format: AUTO)

Which placeholder will make this work? Or do I need to build the params-seq differently?

Adrian Klaver · Answer 1 · 2023-06-14T14:27:10.763

You are mixing value parameters and dynamic SQL. Your attempt cursor.executemany(query=query, params_seq=vals) does not work because vals are not what executemany is expecting which is a list of values parameters. Where values are data values, either being passed into a table or used in filtering data. What you are trying to do is build dynamic SQL over a list of identifiers, that is a different problem.

A solution:

import psycopg
from psycopg import sql
from psycopg import ClientCursor

con = psycopg.connect("dbname=test host=localhost  user=postgres")
cur = ClientCursor(con)

tbl_list = ['tbl1', 'tbl2', 'tbl3']

for tbl in tbl_list:
    trunc_sql = sql.SQL("truncate table {}").format(sql.Identifier(tbl))
    print(cur.mogrify(trunc_sql))

truncate table "tbl1"
truncate table "tbl2"
truncate table "tbl3"

I used ClientCursor instead of con.cursor() in order to get the mogrify method and use it to display the built query.

Psycopg3: How to use Identifier in executemany()

1 Answers1