SQLite3数据库如何合并？

Question

我有很多SQLite3数据库文件需要合并。例如，考虑类似于代理脚本生成的数据库文件：

#!/usr/bin/python
# -*- coding: utf-8 -*-
import random
import time

import folktales

agent_ID = "f67b809e-c38b-465a-9e93-665ab36668f2"

def main():
    while True:
        folktales.insert_state_dictionary_into_database_table(
            entries    = {
                         "value_1" : random.random(),
                         "agent_ID": agent_ID
                         },
            table_name = "measurements",
            filepath   = "database_1.db"
        )
        time.sleep(5)

if __name__ == "__main__":
    main()

其中一个数据库中的一个表可能长这样:

我希望有一种通用的方式（可能是SQL命令）来合并所有数据库中的单个表（可能有多个表）。该命令必须适用于不同形式的数据库（因此不能包含表名或字段名等信息）。

- BlandCorporation

---

@Parfait 抱歉，我应该清楚地表明我不希望出现太多复杂性。将所有表单独合并的联合将是可以的，而无需重置索引。UUID4字段用作每个条目的唯一标识符。 - BlandCorporation

所有数据库中的表是否都保持着相同的列名和相同的列数？ - Parfait

@Parfait 是的，他们做。 - BlandCorporation

而且表名相同吗？如果不是，您是否有可用的代理脚本来了解表名。 - Parfait

Answer 1

考虑使用SQLite的ATTACH DATABASE命令来查询外部数据库，然后遍历附加数据库中的每个表以将数据追加到第一个数据库中。

具体而言，以下内容打开第一个数据库，然后迭代地附加包含SQLite数据库的目录中的每个数据库。然后在每个数据库中查询表名并循环追加它们的内容。外层循环被包装在第一个数据库连接的上下文管理器with()中，因此无需在结尾运行conn.close()。

library(os)
library(sqlite3)

mypath = "/path/to/sqlite/databases"

# OPEN CONNECTION TO FIRST DATABASE
with sqlite3.connect(os.path.join(mypath, "myfirst.db")) as conn:    
    cur = conn.cursor()

    # LOOP THROUGH EACH DB TO ATTACH
    for db in os.listdir(mypath):
        if db != "myfirst.db" and db.endswith(".db"):
            # PASS FULL FILE NAME AS PARAMETER
            cur.execute("ATTACH ? AS curr_db;", os.path.join(mypath, db))

            # GET ALL TABLES IN curr_db
            cur.execute("SELECT name FROM curr_db.sqlite_master WHERE type='table';")
            all_tbls = cur.fetchall()

            # LOOP THROUGH EACH TABLE
            for tbl in all_tbls:
                # APPEND DATA (ASSUMING SAME COLUMNS IN SAME POSITION)
                sql = "INSERT INTO mytable SELECT * FROM curr_db.[{}];".format(tbl[0])
                cur.execute(sql)
                conn.commit() 

            cur.execute("DETACH curr_db;")

    cur.close()

请务必更新mypath、myfirstdb和mytable为实际名称。如果一切正常，第一个数据库的mytable将在所有数据库的all tables中维护all记录。在循环之前或之后，您可能需要手动将第一个数据库中的每个表追加到mytable中。

- Parfait