How can SQLite3 databases be merged?

Question

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:

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).

Answer 1

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.

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()

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.