英文:
Psycopg3: How to use Identifier in executemany()
问题
我在Python中尝试使用psycopg（版本3.1.4）和executemany()来截断许多表，但是无法弄清楚在查询中使用什么作为占位符。根据我所见，文档没有提供明确的信息。
我的代码如下：
# 制作一个标识符列表的列表（'tables'是表的列表）
vals = [[sql.Identifier(schemaname, t)] for t in tables]
# 截断模式下的所有表
query = sql.SQL("truncate table {};")
cursor.executemany(query=query, params_seq=vals)
这会导致错误：psycopg.ProgrammingError: 查询中没有占位符，但传递了1个参数
如果我改用%s作为占位符，我会得到：psycopg.ProgrammingError: 不能使用占位符'%PyFormat.AUTO'（格式：AUTO）来适应类型'Identifier'
哪种占位符会使这个工作？或者我需要以不同的方式构建params-seq？
英文:
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?
答案1
得分: 1
你正在混合使用值参数和动态SQL。你的尝试 cursor.executemany(query=query, params_seq=vals) 不起作用，因为 vals 不是 executemany 预期的参数，它预期的是一个值参数列表。这些值参数是数据值，可以传递到表格中或用于数据过滤。你正在尝试的是在一组标识符上构建动态SQL，这是一个不同的问题。
一个解决方案：
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"
我使用了 ClientCursor 而不是 con.cursor()，以便获取 mogrify 方法，并使用它来显示构建的查询。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论