How to load an existing db file into memory in Python sqlite3?

Question

I have an existing sqlite3 db file on which I need to do some extensive calculations. Computing from a file is very slow, and since the file is small (~ 10 MB ), so there should be no problem loading it into memory.

Is there a way for Pythonic to load an existing file into memory to speed up the calculations?

Answer 1

Here is a snippet that I wrote for my flash application:

 import sqlite3 from StringIO import StringIO def init_sqlite_db(app): # Read database to tempfile con = sqlite3.connect(app.config['SQLITE_DATABASE']) tempfile = StringIO() for line in con.iterdump(): tempfile.write('%s\n' % line) con.close() tempfile.seek(0) # Create a database in memory and import from tempfile app.sqlite = sqlite3.connect(":memory:") app.sqlite.cursor().executescript(tempfile.read()) app.sqlite.commit() app.sqlite.row_factory = sqlite3.Row 

Answer 2

sqlite3.Connection.iterdump "[r] eturns iterator to dump the database in SQL text format. Useful when storing the database in memory for later recovery. This function provides the same capabilities as the .dump command in the sqlite3 shell."

Get such an iterator and unload the disk-based database into memory, and you are ready to calculate. When the calculation is done, just flush the return path back to disk.

Answer 3

First you should try to figure out what causes the slowness that you are observing. Do you write tables? Are your records big enough for you to save unnecessary temporary results on disk? Can you change the entries to go to temporary tables (with pragma temp_store=memory )? Can you live with pragma synchronous=off ?

I do not think this function is displayed in the Python module, but sqlite has a backup API that sounds exactly like you ask for: a way to copy from one database to another (any of which can be nested in the database), which works almost automatically without any user-visible enumeration of tables. (Perhaps APSW reveals this?)

Another option is to create a disk with disks (if you have sufficient control over the environment) and copy the file there.

Answer 4

if we must use the Python shell, then there is no better solution than two-pass reading and writing. but starting with version 3.7.17, SQLite has the ability to directly access the contents of the disk using mapped I / O. sqlite mmap

if you want to use mmap, you must use the C interface, since it does not provide any wrapper.

and there is another hardware solution, a memory disk. Then you have a convenient file I / O and memory speed.

Answer 5

This has already been answered, including code examples in python, how can I fully load the sqlite database into memory before connecting it?

You don't mention the operating system, but one of them is that by default it uses a 10 MB cache, no matter how much memory you have. (This made sense on days when systems shipped with 64 MB, etc.). This post has several links:

http://marc.info/?l=sqlite-users&m=116743785223905&w=2

Answer 6

Here is a relatively easy way to read SQLite db into memory. Depending on your preferences regarding data management, you either use a Pandas dataframe or write your table to the sqlite3 database in memory. Similarly, after manipulating the data, you use the same df.to_sqlite approach to return the results to the db table.

 import sqlite3 as lite from pandas.io.sql import read_sql from sqlalchemy import create_engine engine = create_engine('sqlite://') c = engine.connect() conmem = c.connection con = lite.connect('ait.sqlite', isolation_level=None) #Here is the connection to <ait.sqlite> residing on disk cur = con.cursor() sqlx = 'SELECT * FROM Table' df = read_sql(sqlx, con, coerce_float=True, params=None) #Read SQLite table into a panda dataframe df.to_sql(con=conmem, name='Table', if_exists='replace', flavor='sqlite') 

Answer 7

What about sqlite3.Connection.backup(...) ? "This method backs up the SQLite database even when it is accessed by other clients or at the same time through the same connection." Availability: SQLite 3.6.11 or higher. New in version 3.7.

 import sqlite3 source = sqlite3.connect('existing_db.db') dest = sqlite3.connect(':memory:') source.backup(dest) 

Answer 8

sqlite supports in-memory databases.

In python, the database name is used for this : memory:.

Perhaps you could open two databases (one of the file empty in memory), transfer everything from the file database to memory, and then use the database in memory to perform the calculations.

Answer 9

Thanks to the Cenk Alti solution, I always had a MemoryError error with Python 3.7 when the process reached 500 MB. Only using the sqlite3 backup function (mentioned by thinwybk) was I able to load and save large SQLite databases. You can also do the same with just 3 lines of code, in both directions.