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
– Martijn Pieters, Commented Dec 23, 2014 at 16:06
I realized that after posting. execute() resolved my issue :) thanks — user1917679
– user1917679, Commented Dec 24, 2014 at 20:32

Martijn Pieters · Accepted Answer · 2014-12-23 16:04:46Z

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.4k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related