How to copy a disk based sqlite table to a memory database in python? I know the schema of the table.
1
-
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: stackoverflow.com/questions/23395888/… . Short answer: backup API is available in Python 3.7+ : docs.python.org/3/library/…Demitri– Demitri2020-01-05 15:21:44 +00:00Commented Jan 5, 2020 at 15:21
Add a comment
|
3 Answers
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
12 Comments
Clay
This looks like it works, but how efficient is it?
mouad
@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).
Clay
Thanks! This will help me very much. I do like the flexability of it.
wisty
Is that quadratic? I thought it was better to do query = ''.join([line for line in old_db.iterdump()])
wisty
@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.
|
Check out the SQLite Backup API. The example is in C, but this should show you how it's done efficiently.
3 Comments
Clay
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?
Gelldur
useful :) i just need something like this! Great thx
jfs
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 themAn 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.
