CODEWITHSUNDEEP
pythonsqlite
mrosa
mrosa

Reputation: 73

Sqlite OperationalError with query returning value

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

Upvotes: 1

Views: 522

Answers (2)

ntraore
ntraore

Reputation: 46

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()

Upvotes: 3

zswqa
zswqa

Reputation: 914

Use lastrowid:

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

print(cursor.lastrowid)

Upvotes: 1

Related Questions