合并SQLite文件为一个数据库文件，并且关于'开始/提交'的问题

Question

这篇文章提到了一些关于合并SQLite数据库的内容，具体可以参考这个页面。

合并的步骤如下。假设我想把a.db和b.db合并。在命令行中，我会这样做：

• 输入sqlite3 a.db
• 然后输入attach 'b.db' as toM;
• 接着输入begin; <--
• 然后输入insert into benchmark select * from toM.benchmark;
• 再输入commit; <--
• 最后输入detach database toM;

这样做效果很好。不过在提到的网站上，有人问如何加快这个过程，答案是使用'begin'和'commit'命令。

然后，我写了以下的Python代码来做同样的事情。我把SQLite的功能调用抽象成了SQLiteDB，其中一个方法是runCommand()。即使我删除了self.connector.commit()，我还是遇到了同样的错误。

# run command
def runCommand(self, command):
    self.cursor.execute(command)
    self.connector.commit() # same error even though I delete this line

db = SQLiteDB('a.db')
cmd = "attach \"%s\" as toMerge" % "b.db"
print cmd
db.runCommand(cmd)
cmd = "begin"
db.runCommand(cmd)
cmd = "insert into benchmark select * from toMerge.benchmark"
db.runCommand(cmd)
cmd = "commit"
db.runCommand(cmd)
cmd = "detach database toMerge"
db.runCommand(cmd)

但是，我得到了以下错误。

OperationalError: cannot commit - no transaction is active

尽管有错误，结果数据库还是成功合并了。而且如果不使用begin/commit，根本没有错误。

• 为什么我不能运行begin/commit命令？
• 合并数据库文件时，运行begin/commit是绝对必要的吗？文章说begin/commit的目的是为了加快速度。那么，使用和不使用begin/commit命令在速度上有什么区别呢？

Answer 1

看起来，Cursor.execute 不支持 'commit' 命令。它支持 'begin' 命令，但这其实是多余的，因为 sqlite3 会自动为你开始事务：

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> cur.execute('begin')
<sqlite3.Cursor object at 0x0104B020>
>>> cur.execute('CREATE TABLE test (id INTEGER)')
<sqlite3.Cursor object at 0x0104B020>
>>> cur.execute('INSERT INTO test VALUES (1)')
<sqlite3.Cursor object at 0x0104B020>
>>> cur.execute('commit')

Traceback (most recent call last):
  File "<pyshell#10>", line 1, in <module>
    cur.execute('commit')
OperationalError: cannot commit - no transaction is active
>>> 

你只需要在你的 Connection 对象上使用 commit 方法就可以了。

至于你的第二个问题，在合并文件时并不是绝对必要调用 begin/commit：只要确保在这个过程中没有任何磁盘错误、数据库被修改，或者有人用不对的眼光看着电脑就行。所以说，使用 begin/commit 是个好主意。当然，如果原始数据库没有被修改（我老实说没去查），那其实就不需要了。如果出现错误，你可以直接丢掉部分输出，重新开始。

这样做还有个好处，就是每次更改不需要立即写入磁盘。它们可以先存储在内存中，然后一起写入。但是正如前面提到的，sqlite3 会为你处理这些事情。

另外，值得一提的是：

cmd = "attach \"%s\" as toMerge" % "b.db"

这个是错误的，因为它已经过时了。如果你想正确地做错事，那就是：

cmd = 'attach "{0}" as toMerge'.format("b.db") #why not just one string though?

这与更新版本的 Python 兼容，能让代码迁移变得更简单。

如果你想做正确的事情，那就是：

cmd = "attach ? as toMerge"
cursor.execute(cmd, ('b.db', ))

这样可以避免 SQL 注入，而且显然会稍微快一点，所以这是双赢的选择。

你可以这样修改你的 runCommand 方法：

def runCommand(self, sql, params=(), commit=True):
    self.cursor.execute(sql, params)
    if commit:
        self.connector.commit()

现在你可以在不需要提交的情况下，通过传递 commit=False 来避免每个命令后都提交。这保持了事务的概念。