python - psycopg2.ProgrammingError : syntax error at or near "%"

Question

我正在尝试使用带有 psycopg 的参数化查询来删除一堆行。我的脚本有如下删除语句:

cur.executemany( "WITH remove_rows as (DELETE FROM users WHERE userid = %s RETURNING *), insert_rows as (INSERT INTO old.users SELECT * FROM remove_rows RETURNING *) SELECT count(*) from insert_rows;", (id,))

我得到的错误是:

Traceback (most recent call last):
  File "removal.py", line 17, in <module>
    cur.executemany( "WITH remove_rows as (DELETE FROM .users WHERE userid = %s RETURNING *), insert_rows as (INSERT INTO old.users SELECT * FROM remove_rows RETURNING *) SELECT count(*) from insert_rows;", (id,))
psycopg2.ProgrammingError: syntax error at or near "%"
LINE 1: ...ws as (DELETE FROM users WHERE userid = %s RETURNI...

当我从 userid = %s 中删除空格并使其成为 userid=%s 时，我得到了与消息 column "s"does not存在。

我开始怀疑 psycopg2 参数化是否不处理 CTE？

Answer 1

executemany() 采用参数的嵌套序列集，而不是一个。

要么将您的参数包装到另一个列表中，要么使用 cur.execute() 来只运行一次查询。