search

mcfunley / pugsql

A HugSQL-inspired database library for Python
https://pugsql.org
Apache License 2.0
673 stars 22 forks source link

Using INSERT RETURNING seems to break sqlite3 #54

Open ghallberg opened 2 years ago

ghallberg commented 2 years ago

I'm trying to write an INSERT RETURNING query which works on both Postgres and SQLite:

-- :name test_insert :insert
INSERT INTO table (foo) VALUES (:bar) RETURNING id;

This causes a sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) cannot commit transaction - SQL statements in progress error.

It's possible this is related to https://github.com/sqlalchemy/sqlalchemy/issues/6195

mcfunley commented 2 years ago

Looks like the underlying issue isn't fixed yet (haven't had time to really dig here), but in the meantime you should be able to drop the RETURNING id -- for engines that support lastrowid, insert statements already return the ID of the last row inserted.

https://github.com/mcfunley/pugsql/blob/master/pugsql/statement.py#L94

an-ivanov commented 1 year ago

The same bug has not been fixed yet for the UPDATE RETURNING combination :(