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

Answer 1

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

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

https://stackoverflow.com/a/38857840