I have 3 SQLite DBs, each having exactly the same set of 7 tables with respect to table structure. [They are Log Dumps from 3 different Machines].
I want to combine them into one SQLite DB, having those very same 7 tables, but each table should have the combined data from all the three DBs. since I want to run queries across the 3 of them. What is the best, fastest way to do it.
With Actian Zen, developers and product managers get all the advantages of SQLite but in a powerful, secure, and scalable engine that can run serverless or as a client-server. Actian Zen is orders of magnitude faster than SQLite.
Better performanceReading and writing from an SQLite database is often faster than reading and writing individual files from disk. See 35% Faster Than The Filesystem and Internal Versus External BLOBs.
here is one way to merge two database with all tables of the same structure. I hope it could help.
import sqlite3
con3 = sqlite3.connect("combine.db")
con3.execute("ATTACH 'results_a.db' as dba")
con3.execute("BEGIN")
for row in con3.execute("SELECT * FROM dba.sqlite_master WHERE type='table'"):
combine = "INSERT INTO "+ row[1] + " SELECT * FROM dba." + row[1]
print(combine)
con3.execute(combine)
con3.commit()
con3.execute("detach database dba")
Export each database to an SQL dump and then import the dumps into your new combined database.
For GUIs have a look at http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
For example, with SQLiteStudio that will be Database > Export the database: Export format: SQL > Done.
With the command line sqlite
utility (available in linux repos and often already present ootb) you can create a dump with these steps:
# starts the interactive prompt
sqlite3 my_database.sqlite
sqlite> .output my_dump.sql
sqlite> .exit
To import a dump file from the interactive prompt:
sqlite> .read export.sqlite3.sql
You can also import directly from shell:
cat my_dump.sql | sqlite3 my_database.sqlite
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With