This post refers to this page for merging SQLite databases.
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
- attach 'b.db' as toM;
- begin; <--
- insert into benchmark select * from toM.benchmark;
- commit; <--
- detach database toM;
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.
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.
- Why can't I run the begin/commit command?
- Is it absolutely necessary to run begin/commit to safely merge the db files? The post says that the purpose of begin/commit is for speedup. Then, what's the difference between using and not using the begin/commit command in terms of speedup?