Sqlite OperationalError with query returning value

Question

Is it possible to execute a commit on a db right after a query returning a value (if this value is not fetched)? Does a returning statement in the query lock the cursor and prevent commits if not fetched?

Example/How to reproduce

Given the following table products, built as follows:

create table products (id INTEGER, name TEXT, price INTEGER, PRIMARY KEY (id));

The following query to insert an element can be executed without any error:

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
query1 = 'insert into products (id, name, price) values (?,?,?)'
cursor.execute(query1, (1, 'apple', 100))
conn.commit()

Nevertheless, when using a query returning the id of the element such as

query2 = 'insert into products (id, name, price) values (?,?,?) returning id'
cursor.execute(query2, (2, 'cherry', 250))
conn.commit()

the following error is raised

OperationalError: cannot commit transaction - SQL statements in progress

That's just an example. What I would like to understand is the rationale behind this error. Is it due to the cursor locking the db? Postgres is working fine with the same code, so is it due to internals of sqlite? — mrosa
– mrosa, Commented Jul 20, 2021 at 11:50
I've been googling the error message, and it seems to be related to the way Python implicitly commits transactions. I guess this doesn't interact properly with returning id doing both an update and also returning a query set. — Barmar
– Barmar, Commented Jul 20, 2021 at 13:43

ntraore · Accepted Answer · 2023-01-01 18:40:18Z

3

I know it has been a while on this, but just in case it can help someone else.

In order for your code to work with the returning clause, you need to fetch the result first before doing the commit :

query2 = 'insert into products (id, name, price) values (?,?,?) returning id'
id = cursor.execute(query2, (2, 'cherry', 250)).fetchone()  # <--- here

print(id)
conn.commit()

answered Jan 1, 2023 at 18:40

ntraore

465 bronze badges

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

Comments

zswqa · Accepted Answer · 2021-07-20 08:58:01Z

1

Use lastrowid:

query2 = 'insert into products (id, name, price) values (?,?,?)'
cursor.execute(query2, (2, 'cherry', 250))

print(cursor.lastrowid)

answered Jul 20, 2021 at 8:58

zswqa

9347 silver badges15 bronze badges

Collectives™ on Stack Overflow

Sqlite OperationalError with query returning value

Example/How to reproduce

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example/How to reproduce

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related