SQLite3数据库如何合并? [英] How can SQLite3 databases be merged?

问题描述

我有许多要合并的SQLite3数据库文件.例如，考虑由代理脚本生成的数据库文件，如下所示:

I have many SQLite3 database files that I want merged. As an example, consider database files produced by agent scripts like this:

#!/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()

其中一个数据库的一个表可能看起来像这样:

One table from one of the databases might look like this:

我想要一种通用的方式(也许是一条SQL命令)来合并所有数据库中的所有单个表(可以有多个表).该命令必须适用于不同形式的数据库(例如，不能具有表名或字段名).

I want a generic way (perhaps an SQL command) to merge all of the individual tables (there can be multiple tables) in all of the databases. The command must be applicable to different forms of database (so cannot feature table names or field names, for examples).

推荐答案

考虑SQLite的 附加数据库 命令查询外部数据库，然后遍历附加数据库的每个表，以将数据附加到第一个数据库.

Consider SQLite's ATTACH DATABASE command to query external databases and then walk through every table of the attached database for appending data to the first database.

具体地说，下面首先打开数据库，然后迭代地将每个数据库附加到包含SQLite数据库的目录中.然后在每个数据库中，查询表名，然后循环以追加其每个内容.外循环包装在第一个数据库连接的上下文管理器 with()中，因此不需要最后运行 conn.close().

Specifically, below opens first database and then iteratively attaches each database in a directory that contains the SQLite databases. Then within each database, table names are queried and then looped to append each of their content. Outer loop is wrapped in a context manager, with(), of the first database connection, so there is no need to run conn.close() at end.

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 数据库中的 all表中维护 all 记录.您可能需要在循环之前或之后将仅第一个数据库中的每个表手动附加到 mytable 中.

Be sure to update mypath, myfirstdb, and mytable for actual names. If all runs properly, the first database's mytable will maintain all records across all tables in all databases. You may need to manually append each table within only the first database into mytable prior to or after looping.

这篇关于SQLite3数据库如何合并?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！