Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Fastest Way merge two SQLITE Databases

Tags:

sql

sqlite

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.

like image 494
subiet Avatar asked Feb 19 '12 13:02

subiet


People also ask

What is faster than SQLite?

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.

Is SQLite faster than text file?

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.


2 Answers

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")
like image 137
cheng chen Avatar answered Sep 18 '22 13:09

cheng chen


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
like image 21
ccpizza Avatar answered Sep 20 '22 13:09

ccpizza