How to nest methods to execute a SQLite transaction safely in Python?

Question

I'm working on class creation in Python together with some SQLite tests and I'm wondering if the way I'm handling the connections is right (and most importantly, safe).

Inside class Test, I have two methods: simple_operation, which inserts a new value in a nested set model table, and coupled_operation, which executes two simple_operation in a row to avoid redundancy.

Currently, all other methods involving any insertion or updates on the database open the connection and close it upon completing the operation, like simple_operation is planned originally:

class Test:
    def simple_operation(self, value):
        conn = sqlite3.connect(mydatabase)
        cur = conn.cursor()
        with conn:
            cur.execute('UPDATE nested SET rgt = rgt + 2 WHERE rgt >= ?;', (value,))
            cur.execute('UPDATE nested SET lft = lft + 2 WHERE lft >= ?;', (value,))
            cur.execute('INSERT INTO nested(lft, rgt) VALUES(?, ?);', (value, value+1))

        conn.close()

However, since coupled_operation will execute it twice and its purpose is to pair two simple operations that have something in common, it would have its own transaction leading back to the simple method:

class Test:
    def coupled_operation(self, value):
        connd = sqlite3.connect(mydatabase)
        with connd:
            self.simple_operation(value)
            self.simple_operation(value+2)
        conn.close()

However, since simple_operation implements its own transaction, handling it the same way would commit every simple_operation it makes, defeating the purpose of connd as a context manager. In a practical level, this implementation does 2x conn.commit with 3x cur.execute each, instead of 6x cur.execute and then connd.commit.

My solution is currently passing through leaving the cursor as an optional argument in simple_operation, changing behaviour whether it is provided or not:

class Test:
    def coupled_operation(self, value):
        connd = sqlite3.connect(mydatabase)
        curd = connd.cursor()
        with connd:
            self.simple_operation(value, curd)
            self.simple_operation(value+2, curd) # A

        conn.close()

    def simple_operation(self, value, outer_cursor=None):
        if outer_cursor is None:
            conn = sqlite3.connect(mydatabase)
            cur = conn.cursor()
            with conn:
                cur.execute('UPDATE nested SET rgt = rgt + 2 WHERE rgt >= ?;', (value,))
                cur.execute('UPDATE nested SET lft = lft + 2 WHERE lft >= ?;', (value,))
                cur.execute('INSERT INTO nested(lft, rgt) VALUES(?, ?);', (value, value+1))

            connd.close()
        else:
            # Executes, but doesn't commit, leaves for coupled_operation to integrate into transaction
            outer_cursor.execute('UPDATE nested SET rgt = rgt + 2 WHERE rgt >= ?;', (value,))
            outer_cursor.execute('UPDATE nested SET lft = lft + 2 WHERE lft >= ?;', (value,))
            outer_cursor.execute('INSERT INTO nested(lft, rgt) VALUES(?, ?);', (value, value+1)) # B

I'm not sure, however, if done this way it is successfully contained within the desired transaction, in such a way that should something go wrong with # B when coming from # A the entire operation would be rolled back.

---

Just for curiosity's sake, I'm opening and closing connections every time I handle the database. Previously I set a single connection as a class property and started messing with other methods, but I ended up locking my database when instantiating the class. Would you guys consider this way or the one shown in the examples more efficient?

question from:https://stackoverflow.com/questions/65895593/how-to-nest-methods-to-execute-a-sqlite-transaction-safely-in-python

Answer 1

Surely the simplest solution is to separate concerns of the simple_operation() function into the part that commits the transaction and the part that does the actual work:

class Test:
    def __init__(self, path):
        self.conn = sqlite3.connect(path)

    def coupled_operation(self, value):
        with self.conn:
            cursor = self.conn.cursor()
            self.do_simple_operation(cursor, value)
            self.do_simple_operation(cursor, value+2)

    def simple_operation(self, value):
        with self.conn:
            cursor = self.conn.cursor()
            self.do_simple_operation(cursor, value)

    def do_simple_operation(self, cursor, value):
        cursor.execute('UPDATE nested SET rgt = rgt + 2 WHERE rgt >= ?;', (value,))
        cursor.execute('UPDATE nested SET lft = lft + 2 WHERE lft >= ?;', (value,))
        cursor.execute('INSERT INTO nested(lft, rgt) VALUES(?, ?);', (value, value+1))

That should do the trick. There's no need to keep closing and reopening the database, that will only lead to slowness. I'm not sure why you had locking problems; maybe you were sharing objects of this class between threads? That isn't allowed in SQLite.

You could potentially split out all methods in this way, to be really safe. All methods foo() just open a connection context manager and call do_foo(), while methods starting with do_ could freely call each other.

Here are a few extra notes that might be useful:

• Using the same cursor has no effect on whether statements are considered to be part of the same transaction and no effect on efficiency (there is an internal statement cache in the Python sqlite3 module but it's per connection not per cursor) so you can just make a new cursor each time you execute a statement. There's even an execute() method on the sqlite3.Statement object which is a shortcut for creating a Cursor and calling execute() on it (and the return value is the Cursor object in case you need it).
• Just in case you're not aware (I certainly think it's not obvious!), with conn doesn't start a transaction, it just commits (if no exception) or rolls back (if there is an exception) a transaction that is somehow otherwise started. If no transaction is in progress when you get to the end of a with conn block then it silently does nothing so you'll never know!
• By default, the Python sqlite3 module automatically starts a transaction when certain statements are executed. The rules are complicated, but do include the statements in your example (but do not include e.g. CREATE TABLE ... or SELECT ...). It's probably safest to disable this confusing logic by setting isolation_mode=None and start transactions manually.
• The only slight exception to what I said about cursors and locking above is that if you execute a SELECT statement but don't consume all the resulting rows and don't destroy or call close() on the Cursor object then this can extend the database lock past the end of the transaction (even past a COMMIT, although if the transaction lock is a write lock then it will downgrade it to a read lock). This is rarely a practical problem so long as you don't hang on to Cursor objects for a long time in variables.

With these tweaks, your code ends up looking like this:

class Test:
    def __init__(self, path):
        self.conn = sqlite3.connect(path, isolation_mode=None)

    def coupled_operation(self, value):
        with self.conn:
            self.conn.execute("BEGIN")
            self.do_simple_operation(value)
            self.do_simple_operation(value+2)

    def simple_operation(self, value):
        with self.conn:
            self.conn.execute("BEGIN")
            self.do_simple_operation(value)

    def do_simple_operation(self, value):
        self.conn.execute('UPDATE nested SET rgt = rgt + 2 WHERE rgt >= ?;', (value,))
        self.conn.execute('UPDATE nested SET lft = lft + 2 WHERE lft >= ?;', (value,))
        self.conn.execute('INSERT INTO nested(lft, rgt) VALUES(?, ?);', (value, value+1))