sqlite3.ProgrammingError: Cannot operate on a closed database. [Python] [sqlite]

Question

I am using a common function to execute all sqlite queries in a class. It all works, until I use a for loop with more than one item in the list.

Here's the common function that executes sqlite queries:

def executeQuery(self, query, params = ()):
        results = {}
        try:
            cur = self.conn.cursor()
            cur.execute(query, params)
            self.conn.commit()
            rows = cur.fetchall()

            results['status'] = 'success'
            result = []
            if rows:
                column = map(lambda x: x[0], cur.description)
                for row in rows:
                    result.append( dict(zip(column, row)) )

            results['results'] = result

        except self.conn.Error, e:
            if self.conn:
                self.conn.rollback()

            print "Error: %s" % e.args[0]
            results['status'] = 'failure'
            results['results'] = e.args[0]

        finally:
            if self.conn:
                self.conn.close()

        return results

And here's the loop that gets me the database closed error:

stages = self.getStageByDate(2000)
        for stage in stages['results']:
            print stage['name']
            additives = self.getStageAdditives(stage['name'])
            print additives
            for additive in additives['results']:
                print additive

Error seems to originate from the getStageAdditives() as it return 4 items, while getStageByDate() return only 1.

It seems to me like the connection to the database is not closed before the second connection is attempted. Why does this happen? It did not happen when used with MySQL database. What are the solutions to this issue?

Answer 1

You write "It seems to me like the connection to the database is not closed before the second connection is attempted" but, in fact, there is no "second connection" to the database. You're using a single connection, which I'm guessing is created in the initializer (__init__) for the not-shown-in-your-example class that contains the method execute_query.

You (again guessing) create the conn in that __init__ method, but you close it immediately after executing any query. Therefore, it will not be available when you execute another query.

Instead, you should not .close() but rather .commit() at the end of your query. Don't do this in finally but rather at the end of the try. That way the transaction will either be committed (if it succeeds) or rolled back (in the except block, if it fails).

Then, add a separate .close() method to your larger class which in turn calls .close() on the connection, and have the calling program call that method when it's finished with all its queries. That call to close would appropriately appear in a finally block inside the larger program.

Answer 2

Why is a business method closing the connection? Surely it should close the cursor instead? Closing the connection would mean that the second time the executeQuery is called, it would fail because the connection is gone.