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

Question

I am trying to use parameterized query with psycopg for deleting a bunch of rows. My script has the deletion statement like:

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,))

And the error I get is:

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...

When I remove the space from userid = %s and made it userid=%s, I got same error with message column "s" does not exist.

I am starting to wonder if psycopg2 parameterization does not handle CTEs?

Answer 1

executemany() takes a nested set of sequences of parameters, not one.

Either wrap your parameters into another list, or use cur.execute() instead to run the query just once.