sqlite3.ProgrammingError:无法对关闭的数据库进行操作.[Python] [sqlite] [英] sqlite3.ProgrammingError: Cannot operate on a closed database. [Python] [sqlite]

问题描述

我正在使用一个通用函数来执行一个类中的所有 sqlite 查询.一切正常，直到我使用一个包含列表中多个项目的 for 循环.

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.

这是执行sqlite查询的常用函数:

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

错误似乎源自 getStageAdditives()，因为它返回 4 个项目，而 getStageByDate() 仅返回 1 个.

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

在我看来，在尝试第二个连接之前，与数据库的连接并未关闭.为什么会发生这种情况?与 MySQL 数据库一起使用时不会发生这种情况.这个问题的解决方案是什么?

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?

推荐答案

您写道在我看来，在尝试第二次连接之前，与数据库的连接并未关闭"，但实际上没有第二次连接"到数据库.您使用的是单个连接，我猜它是在包含方法 execute_query__init__) 中创建的代码>.

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.

您(再次猜测)在该 __init__ 方法中创建了 conn，但在执行任何查询后立即关闭它.因此，当您执行另一个查询时，它将不可用.

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.

相反，您不应在查询结束时使用 .close() 而是 .commit().不要在 finally 中执行此操作，而是在 try 的末尾执行此操作.这样，事务将被提交(如果成功)或回滚(在except块中，如果失败).

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

然后，向较大的类添加一个单独的 .close() 方法，该方法又在连接上调用 .close()，并让调用程序调用 那个 方法，当它完成所有查询时.对 close 的调用会适当地出现在较大程序内的 finally 块中.

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.

这篇关于sqlite3.ProgrammingError:无法对关闭的数据库进行操作.[Python] [sqlite]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！