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?
Ngenator
11.3k4 gold badges44 silver badges47 bronze badges
1 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.
answered Dec 23, 2014 at 16:04
Martijn Pieters
1.1m326 gold badges4.2k silver badges3.5k bronze badges
Sign up to request clarification or add additional context in comments.
Comments
default
cur.executemany()call, but since I am not familiar with CTEs I am not confident that it'll work even if you were to usecur.execute()instead. As such I deleted my answer.