database 如何合并多个 SQLite 数据库？

How can I merge many SQLite databases?

提问by DavidM

If I have a large number of SQLite databases, all with the same schema, what is the best way to merge them together in order to perform a query on all databases?

如果我有大量 SQLite 数据库，所有数据库都具有相同的架构，那么将它们合并在一起以便对所有数据库执行查询的最佳方法是什么？

I know it is possible to use ATTACHto do this but it has a limitof 32 and 64 databases depending on the memory system on the machine.

我知道可以使用ATTACH来做到这一点，但它有32 和 64 个数据库的限制，具体取决于机器上的内存系统。

采纳答案by DavidM

This would be done on demand, possible several times a day. The way I would see it working is as in http://sqlite.1065341.n5.nabble.com/Attempting-to-merge-large-databases-td39548.htmlwhere the databases are merged into a large DB, the query performed and then the large database deleted.

这将按需进行，一天可能进行多次。我认为它工作的方式是在http://sqlite.1065341.n5.nabble.com/Attempting-to-merge-large-databases-td39548.html中将数据库合并到一个大型数据库中，执行查询然后将大数据库删除。

回答by dfrankow

To summarize from the Nabble postin DavidM's answer:

总结DavidM 回答中的Nabble 帖子：

attach 'c:\test\b.db3' as toMerge;           
BEGIN; 
insert into AuditRecords select * from toMerge.AuditRecords; 
COMMIT; 
detach toMerge;

Repeat as needed.

根据需要重复。

Note: added detach toMerge;as per mike's comment.

注意：detach toMerge;根据迈克的评论添加。

回答by Damilola Olowookere

Although a very old thread, this is still a relevant question in today's programming needs. I am posting this here because none of the answers provided yet is concise, easy, and straight-to-point. This is for sake of Googlers that end up on this page. GUI we go:

尽管这是一个非常古老的线程，但这仍然是当今编程需求中的一个相关问题。我在这里发布这个是因为所提供的答案都不是简洁、简单和直截了当的。这是为了最终出现在此页面上的 Google 员工。GUI我们去：

1. Download Sqlitestudio
2. Add all your database files by using the Ctrl + Okeyboard shortcut
3. Double-click each now-loaded db file to open/activate/expand them all
4. Fun part: simply right-click on each of the tables and click on Copy, and then go to the target database in the list of the loaded database files (or create new one if required) and right-click on the target db and click on Paste

1. 下载Sqlitestudio
2. 使用Ctrl + O键盘快捷键添加所有数据库文件
3. 双击每个现在加载的 db 文件以打开/激活/展开它们
4. 有趣的部分：只需右键单击每个表并单击Copy，然后转到加载的数据库文件列表中的目标数据库（或根据需要创建新的）并右键单击目标数据库并单击Paste

I was wowed to realize that such a daunting task can be solved using the ancient programming skill called: copy-and-paste :)

我很惊讶地意识到这样一项艰巨的任务可以使用称为复制和粘贴的古老编程技巧来解决:)

回答by Espo

If you only need to do this merge operation once (to create a new bigger database), you could create a script/program that will loop all your sqlite databases and then insert the data into your main (big) database.

如果您只需要执行一次合并操作（以创建一个新的更大的数据库），您可以创建一个脚本/程序来循环您的所有 sqlite 数据库，然后将数据插入到您的主（大）数据库中。

回答by CONvid19

Late answer, but you can use:

迟到的答案，但您可以使用：

#!/usr/bin/python

import sys, sqlite3

class sqlMerge(object):
    """Basic python script to merge data of 2 !!!IDENTICAL!!!! SQL tables"""

    def __init__(self, parent=None):
        super(sqlMerge, self).__init__()

        self.db_a = None
        self.db_b = None

    def loadTables(self, file_a, file_b):
        self.db_a = sqlite3.connect(file_a)
        self.db_b = sqlite3.connect(file_b)

        cursor_a = self.db_a.cursor()
        cursor_a.execute("SELECT name FROM sqlite_master WHERE type='table';")

        table_counter = 0
        print("SQL Tables available: \n===================================================\n")
        for table_item in cursor_a.fetchall():
            current_table = table_item[0]
            table_counter += 1
            print("-> " + current_table)
        print("\n===================================================\n")

        if table_counter == 1:
            table_to_merge = current_table
        else:
            table_to_merge = input("Table to Merge: ")

        return table_to_merge

    def merge(self, table_name):
        cursor_a = self.db_a.cursor()
        cursor_b = self.db_b.cursor()

        new_table_name = table_name + "_new"

        try:
            cursor_a.execute("CREATE TABLE IF NOT EXISTS " + new_table_name + " AS SELECT * FROM " + table_name)
            for row in cursor_b.execute("SELECT * FROM " + table_name):
                print(row)
                cursor_a.execute("INSERT INTO " + new_table_name + " VALUES" + str(row) +";")

            cursor_a.execute("DROP TABLE IF EXISTS " + table_name);
            cursor_a.execute("ALTER TABLE " + new_table_name + " RENAME TO " + table_name);
            self.db_a.commit()

            print("\n\nMerge Successful!\n")

        except sqlite3.OperationalError:
            print("ERROR!: Merge Failed")
            cursor_a.execute("DROP TABLE IF EXISTS " + new_table_name);

        finally:
            self.db_a.close()
            self.db_b.close()

        return

    def main(self):
        print("Please enter name of db file")
        file_name_a = input("File Name A:")
        file_name_b = input("File Name B:")

        table_name = self.loadTables(file_name_a, file_name_b)
        self.merge(table_name)

        return

if __name__ == '__main__':
    app = sqlMerge()
    app.main()

---

---

SRC : Tool to merge identical SQLite3 databases

SRC： 合并相同 SQLite3 数据库的工具

回答by Robert Gould

With no offense, just as one developer to another, I'm afraid that your idea seems terribly inefficient. It seems to me that instead of uniting SQLite databases you should probably be storing several tables within the same Database file.

没有冒犯，就像一个开发人员对另一个开发人员一样，我担心你的想法似乎非常低效。在我看来，与其合并 SQLite 数据库，不如将多个表存储在同一个数据库文件中。

However if I'm mistaken I guess you could ATTACH the databases and then use a VIEW to simplify your queries. Or make an in-memory table and copy over all the data (but that's even worse performance wise, especially if you have large databases)

但是，如果我弄错了，我想您可以附加数据库，然后使用 VIEW 来简化您的查询。或者制作一个内存表并复制所有数据（但这在性能方面更差，特别是如果你有大型数据库）