17

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.

subiet
  • 1,359
  • 1
  • 12
  • 18
  • 1
    http://stackoverflow.com/questions/80801/how-can-i-merge-many-sqlite-databases, http://stackoverflow.com/questions/3689694/merge-sqlite-files-into-one-db-file-and-begin-commit-question, http://stackoverflow.com/questions/3232900/how-to-merge-n-sqlite-database-files-into-one-if-db-has-the-primary-field, http://stackoverflow.com/questions/4913369/how-to-merge-multiple-database-files-in-sqlite, http://stackoverflow.com/questions/9048711/merging-databases, ... Nothing in there or linked questions helped you? – Mat Feb 19 '12 at 13:53
  • 1
    Yeah, I had gone through them, and some more. I am looking for robust way of doing that within my python code, and hence I was looking for a best practice. – subiet Feb 20 '12 at 06:13

2 Answers2

27

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")
kritinsai
  • 23
  • 9
cheng chen
  • 469
  • 4
  • 6
7

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
ccpizza
  • 25,495
  • 13
  • 148
  • 149
  • 1
    Hey, GUI isn't an option, this has to be done withing a program, repeatedly. I will explore the SQL dump method, will it take care of primary key constraint on ID in each table, I am bit doubtful on that. – subiet Feb 20 '12 at 06:14
  • 1
    Why do you need to export the primary keys? Export the data and insert the records, the keys will be generated for you. If you want to do it in Python then have a look at http://mysql-python.sourceforge.net/MySQLdb.html#some-examples and http://www.kitebird.com/articles/pydbapi.html – ccpizza Feb 20 '12 at 12:57
  • In SQLiteStudio, you may simply load two DBs, and drag and drop a table from one DB into another, it will get saved automatically. – Wiktor Stribiżew Jan 18 '21 at 13:59