将SQLite文件合并到一个数据库文件中，并“开始/提交"问题 [英] Merge SQLite files into one db file, and 'begin/commit' question

问题描述

这篇文章指的是此

This post refers to this page for merging SQLite databases.

顺序如下.假设我要合并a.db和b.db.在命令行中，请执行以下操作.

The sequence is as follows. Let's say I want to merge a.db and b.db. In command line I do the following.

• sqlite3 a.db
• 将'b.db'附加为toM；
• 开始； <-
• 插入基准测试，然后从toM.benchmark中选择*；
• 提交; <-
• 将数据库分离为M;

它运作良好，但是在被引荐网站上，发问者询问是否要加快速度，答案是使用"begin"和"commit"命令.

It works well, but in the referred site, the questioner asks about speeding up, and the answer is to use the 'begin' and 'commit' command.

然后，我想出了以下python代码来做完全相同的事情.我用SQLiteDB抽象了SQLite函数调用，其中之一是runCommand().即使删除self.connector.commit()，我也遇到相同的错误.

Then, I came up with the following python code to do the exactly same thing. I abstract the SQLite function calls with SQLiteDB, and one of it's method is runCommand(). I got the same error even though I delete the 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)

但是，我遇到了以下错误.

But, I got the following error.

OperationalError: cannot commit - no transaction is active

即使发生错误，结果数据库也可以很好地合并.如果没有开始/提交，就不会有任何错误.

Even though the error, the result db is well merged. And without the begin/commit, there's no error at all.

• 为什么我不能运行begin/commit命令?
• 是否绝对有必要运行begin/commit以安全地合并db文件?帖子说开始/提交的目的是为了加快速度.那么，就加速而言，使用和不使用begin/commit命令有什么区别?

推荐答案

显然，Cursor.execute不支持'commit'命令.它确实支持'begin'命令，但这是多余的，因为sqlite3随时为您启动了它们:

Apparently, Cursor.execute doesn't support the 'commit' command. It does support the 'begin' command but this is redundant because sqlite3 begins them for you anway:

>>> 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方法.

just use the commit method on your Connection object.

关于第二个问题，合并文件时并不一定需要调用begin/commit:只需确保绝对没有磁盘错误，对数据库的修改或在计算机上以错误的方式看计算机的人正在发生.因此，开始/提交可能是一个好主意.当然，如果原始数据库没有被修改(老实说，我没有看过)，那么甚至没有必要这样做.如果出现错误，您可以只废弃部分输出并重新开始.

As for your second question, it is not absolutely necessary to call begin/commit when merging the files: just be sure that there is absolutely no disk error, modifications to the db's or people looking at the computer the wrong way while it is happening. So begin/commit is probably a good idea. Of course, if the original db's aren't being modified (I honestly haven't looked) then there is no need for that even. If there is an error, you can just scrap the partial output and start over.

它还提供了加速，因为不必每次更改都将其写入磁盘.它们可以存储在内存中并成批写入.但是如前所述，sqlite3会为您处理此问题.

It also provides a speedup because every change doesn't have to be written to disk as it occurs. They can be stored in memory and written in bulk. But as mentioned sqlite3 handles this for you.

另外，值得一提的是

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

在被贬低的意义上是错误的.如果您想正确地做错事，那就是

is wrong in the sense that it's depracated. If you want to do the wrong thing correctly, it's

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

这与新版本的python向前兼容，这将使移植代码更加容易.

This is forward compatible with newer versions of python which will make porting code easier.

如果您想做 right 事情，那就是

if you want to do the right thing, it's

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

这避免了sql注入，并且显然要快一些，因此是双赢的.

This avoids sql injection and is, apparently, slightly faster so it's win-win.

您可以按以下方式修改runCommand方法:

You could modify your runCommand method as follows:

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

现在，当不需要提交时，不能通过传递commit=False来在每个命令之后提交.这保留了交易的概念.

now you can not commit after every single command by passing commit=False when you don't need a commit. This preserves the notion of transaction.

这篇关于将SQLite文件合并到一个数据库文件中，并“开始/提交"问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！