32

How to copy a disk based sqlite table to a memory database in python? I know the schema of the table.

Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
Clay
  • 323
  • 1
  • 3
  • 4
  • As this post remains highly visible in search engines and the (current) answer isn't here or in the duplicate link, I'm adding the relevant SO answer here: https://stackoverflow.com/questions/23395888/pure-python-backup-of-sqlite3-in-memory-database-to-disk . Short answer: backup API is available in Python 3.7+ : https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.backup – Demitri Jan 05 '20 at 15:21

3 Answers3

38

this code is more general but maybe it can help you:

import sqlite3

new_db = sqlite3.connect(':memory:') # create a memory database

old_db = sqlite3.connect('test.db')

query = "".join(line for line in old_db.iterdump())

# Dump old database in the new one. 
new_db.executescript(query)

EDIT : for getting your specify table you can just change in the for loop like this:

name_table = "test_table"  # name of the table that you want to get.

for line in old_db.iterdump():
    if name_table in line:
        query = line
        break
mouad
  • 67,571
  • 18
  • 114
  • 106
  • 2
    This looks like it works, but how efficient is it? – Clay Oct 26 '10 at 18:57
  • 1
    @Clay: like i wrote in my answer this code is more general and generic, you can use it for dumping all the database or to dump only a given table, and for how efficient it is? the code use iterator for once (less memory) and it also create all the database in a single query, which mean no up and go(less communication). – mouad Oct 26 '10 at 19:10
  • Thanks! This will help me very much. I do like the flexability of it. – Clay Oct 26 '10 at 19:14
  • Is that quadratic? I thought it was better to do query = ''.join([line for line in old_db.iterdump()]) – wisty Aug 31 '11 at 13:19
  • @wisty : it's not quadratic it's linear (loop one time), but i agree with you that using `str.join` will be more compact and faster, editing my answer ... thanks – mouad Aug 31 '11 at 17:16
  • 2
    @mouad, in C this may be quadratic, as you are building a string of length N every step. (Actually, average length is N/2, but that's still O(N)). Python may optimize this away though. – wisty Sep 01 '11 at 11:33
  • Python implementations now typically see that there is only one reference to a string, so they mutate it for append operations. – Paul Draper Apr 08 '13 at 02:42
  • I know this is old I got stuck and wanted to alert the next person: If your app crashes while a memory db is active, you'll get a dump to a file called ':memory'. Next time you run your app, new_db.executescript(query) will raise an exception about tables already existing and such. So you'll want to use something like `dumpfile = os.path.join(os.getcwd(),':memory')` then check with `os.path.isfile(dumpfile)`. If that returns True, then `os.remove(dumpfile)` will solve the problem. [edited - hit enter too soon] – TSeymour Sep 16 '14 at 18:23
  • If you use quote chars or SQL keywords in your column names you may need to patch your dump.py file. Here is what I ended up using: https://github.com/drbailey/GroupShare/blob/master/sqlite3%20patch/dump.py This implements the changes here: http://bugs.python.org/file24429/sqlite3dump.patch – DrBailey Mar 26 '15 at 18:38
  • Fine for smaller databases but @wisty has a point about continually building strings. Write to a TemporaryFile during the dump iteration and then read into executescript() in order to circumvent this? – Tyler Gannon Aug 27 '18 at 16:34
  • `iterdump()` already returns an iterator. You could white `query = "".join(old_db.iterdump())` instead of `query = "".join(line for line in old_db.iterdump())` – Max Mar 21 '19 at 23:29
  • Will `query = "".join(iterator)` convert the lazy `iterator` into an in-memory string object `query` ? This needs two times the memory: first for the string object, second for the SQLite in-memory db itself. – Thamme Gowda May 16 '20 at 00:53
3

Check out the SQLite Backup API. The example is in C, but this should show you how it's done efficiently.

Hollister
  • 3,758
  • 2
  • 20
  • 22
  • Unfortunately, I don't know any C. I did think I heard somewhere that apsw (Another Python Sqlite Wrapper) Had a backup function though. Is this true? – Clay Oct 26 '10 at 18:58
  • useful :) i just need something like this! Great thx – Gelldur Sep 09 '13 at 20:53
  • `sqlite3_backup*` C functions are not available via Python sqlite3 API. Though it should be easy to create a C extension for Python that calls them – jfs Jan 27 '15 at 11:56
1

An alternative, for those using python and sqlalchemy:

http://www.tylerlesmann.com/2009/apr/27/copying-databases-across-platforms-sqlalchemy/

The idea behind is to replicate the metadata from the source database to the target database and then transfer column after column.

chiffa
  • 2,026
  • 3
  • 26
  • 41