UPDATE statement with RETURNING clause in PostgresSQL and pyodbc

Question

I need to run a query in PostgreSQL to first update a record and then return it. Here's the query:

WITH x AS ( 
  SELECT "ID" FROM JOB WHERE "STATE" = 1 LIMIT 1
)
UPDATE JOB y SET "STATE" = 2 FROM x WHERE x."ID" = y."ID"
RETURNING y.*

It works fine in PostgreSQL server itself. However, running it in Python with pyodbc does not work.

cursor.execute(query)
results = cursor.fetchall()

It says "No results. Previous SQL was not a query."

If you are using postgresql and python, I recommend psycopg2 (or psycopg2-binary), I believe that if you are using pyodbc you must indicate which driver you are using (or at least that was the case when I tried to connect to a Sybase database) — Alex Turner, Feb 04 '22 at 20:42

score 0 · Answer 1 · answered Feb 04 '22 at 20:37

0

Seems like cursor.execute can't handle multiple statements...

Either rewrite your queries and do them one at a time or try splitting by ;

answered Feb 04 '22 at 20:37

רן מורסקי

Putting links are frown upon, quote the most relevant part here. Also, how do I split it so that the functionality is remained untouched? – Hamed Feb 04 '22 at 21:01

1 Answers1