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?

I focussed on the `cur.executemany()` call, but since I am not familiar with CTEs I am not confident that it'll work even if you were to use `cur.execute()` instead. As such I deleted my answer. — Martijn Pieters, Dec 23 '14 at 16:06
I realized that after posting. execute() resolved my issue :) thanks — user1917679, Dec 24 '14 at 20:32

score 1 · Accepted Answer · answered Dec 23 '14 at 16:04

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 '14 at 16:04

Martijn Pieters

1,048,767
296
4,058
3,343

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

1 Answers1