UPDATE .. RETURNING on older versions of sqlite with python interface?

Question

Here is a statement in Python3 using the sqlite3 built-in library:

self.cursor.execute(
     'UPDATE my_table set val = val+1'
     ' WHERE id=?'
     ' RETURNING val', (self.id,))

My beliefe is that this increments val and then returns it, all within one "transaction" no matter what the isolation level is on the connection.

However, RETURNING is only supported as of sqlite 3.35.0, released March 2021.

How do I support this functionality through the python interface with an older sqlite, especially since this class doesn't control the connection's isolation level?

I thought about something like:

self.cursor.executescript("""
   BEGIN
   UPDATE my_table set val = val+1
   WHERE id=?;
   SELECT val WHERE id=?;
   COMMIT
   """, (self.id, self.id))

Well, executescript doesn't take query parameters.

I could separate it into two statements like this:

self.cursor.execute(
        'UPDATE my_table set val = val+1 WHERE id=?', (self.id,))
self.cursor.execute(
        'SELECT val FROM my_table WHERE id=?', (self.id,))

However, there's no guarantee they'll be in the same transaction.

As I said, this class doesn't control the isolation level of the connection. It gets a connection. It could create a new connection or cursor if that's possible from an existing connection.

I believe [this](https://stackoverflow.com/questions/15856976/transactions-with-python-sqlite3) helps you. — Yevgeniy Kosmak, Jan 26 '22 at 21:10

dfrankow · Answer 1 · 2022-01-28T18:00:01.950

Based on my reading of this answer (suggested in comments), I think issuing a BEGIN will start a transaction regardless of the isolation level of the connection. In that case, the solution is

self.cursor.execute('BEGIN')
try:
    self.cursor.execute(
        'UPDATE my_table set val = val+1 WHERE id=?', (self.id,))
    self.cursor.execute(
        'SELECT val FROM my_table WHERE id=?', (self.id,))
    new_val = self.cursor.fetchone()[0]
    self.cursor.execute('COMMIT')
except sqlite3.Error as err:
    self.cursor.execute('ROLLBACK')
    raise err

NOTE: Although it's possible this solution relies on the connection being in autocommit mode, since sqlite does not allow starting a transaction within another transaction.

UPDATE .. RETURNING on older versions of sqlite with python interface?

1 Answers1