0

I am using sqlite3 in python and I have multiple database files which I now want to combine into one big file. Each database is associated with a specific topic but they all share the same structure. I'll be querying them separately -- I just want to combine them into one file so that it's easier to move them around.

One dirty way I can think of goes like this:

create_string = 'CREATE TABLE %s_table1 (id INTEGER PRIMARY KEY, doca INTEGER, docb INTEGER)' %topic1 
cur.execute(create_string)

This way, I'll have the topic name prefixed with each table and when I do a query, I'll have to construct a query string with the appropriate topic name. But there should be a better way to solve this. Am I missing something obvious?

How to Merge Multiple Database files in SQLite?

How can I merge many SQLite databases?

I've already looked at these and it seems my problem is easier to solve than theirs, but I still can't find a better solution.

Thanks in advance for any help.

Community
  • 1
  • 1
yasas
  • 1
  • 1
  • 1
  • 1
    I'd use a tool like [SQLite Expert](http://www.sqliteexpert.com/). – Hot Licks Sep 07 '12 at 03:01
  • Thanks for the link but I need to do this from a script. – yasas Sep 07 '12 at 03:07
  • You're going to be doing it repeatedly?? – Hot Licks Sep 07 '12 at 03:16
  • Well, you can always write a SQLite script to do it. But you'd probably have to dump and reload the tables. – Hot Licks Sep 07 '12 at 03:18
  • yes. I need to do the whole process once every week (most probably) – yasas Sep 07 '12 at 03:21
  • @HotLicks Is there a way to partition the database file while creating tables, based on my topics? (is partition the right word for a thing like this??) I would prefer the tables to be divided at creation time itself. That said, sqlite script should work too. I'll look into it. – yasas Sep 07 '12 at 03:28

2 Answers2

1

If you want to move whole tables (as in, not merge them but bring in tables from a different file), you can do so in unix like so:

 sqlite3 'from_this_db.db' '.dump' | sqlite3 -batch 'to_this_db.db'
Andy Chase
  • 1,318
  • 12
  • 23
0

I think you're solution is OK. Note that you can give the "source" cursor as input to the "destination" cursor executemany method. Something like:

src.execute('SELECT * FROM ...')
dest.executemany('INSERT INTO ...', src)
Miki Tebeka
  • 13,428
  • 4
  • 37
  • 49