How to Load Existing Db File to Memory in Python SQLite3

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

Here is the snippet that I wrote for my flask application:

import sqlite3
from io 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

Load existing db file to memory Python sqlite?

You could read all the tables into DataFrames with Pandas, though I'm surprised it's slow. sqlite has always been really fast for me.

In python, how can I load a sqlite db completely to memory before connecting to it?

apsw is an alternate wrapper for sqlite, which enables you to backup an on-disk database to memory before doing operations.

From the docs:

###
### Backup to memory
###

# We will copy the disk database into a memory database

memcon=apsw.Connection(":memory:")

# Copy into memory
with memcon.backup("main", connection, "main") as backup:
    backup.step() # copy whole database in one go

# There will be no disk accesses for this query
for row in memcon.cursor().execute("select * from s"):
    pass

connection above is your on-disk db.

Get sqlite3 database from disk, load it to memory, and save it to disk?

All you need to do is connect to the database - you can do anything you want to with it then.

from sqlite3 import connect

conn = connect("/path/to/your/sqlite.db")
# Do what you need to with the database here
# Changes (inserts, updates, etc) will be persisted
# to disk whenever you commit a transaction.

If you need to be able to run a series of commands and roll them all back if one does not succeed you should use a transaction.

If you need to make a copy of the existing database, make alterations to it and then save it somewhere else only if the alterations succeed then you can do one of the following things:

• Create an in-memory database and then use SQLite's ATTATCH DATABASE command to attach the existing database to the new in-memory database. Once you have copied all of the data over from the existing database into the in-memory database and transformed it you can attach a new database and copy everything over again.
• Copy the existing database file, attempt to make alterations to it, and delete the new file if the operations did not succeed.
• Use sqlite3's Connection.iterdump to get the contents of the tables as a SQL script which you can then pass to sqlite3.Cursor.executescript as this answer suggests (using either an in-memory or an on-disk connection string for your new database).

Open sqlite database from http in memory

The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename :memory:. In other words, instead of passing the name of a real disk file pass in the string :memory:. For example:

database = sqlite3.connect(":memory:")

When this is done, no disk file is opened. Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

Note that in order for the special :memory: name to apply and to create a pure in-memory database, there must be no additional text in the filename. Thus, a disk-based database can be created in a file by prepending a pathname, like this: ./:memory:.

See more here: https://www.sqlite.org/inmemorydb.html

Query an existing/downloaded SQLite database in Python in memory, without having to write it to disk first

It doesn't support queries, but you can use https://github.com/uktrade/stream-sqlite to access the contents of a downloaded/downloading SQLite file without having it written to disk.

Taking the example from its README:

from stream_sqlite import stream_sqlite
import httpx

def sqlite_bytes():
    with httpx.stream('GET', 'http://www.parlgov.org/static/stable/2020/parlgov-stable.db') as r:
        yield from r.iter_bytes(chunk_size=65_536)

for table_name, pragma_table_info, rows in stream_sqlite(sqlite_bytes(), max_buffer_size=1_048_576):
    for row in rows:
        print(row)

(Full disclosure: I was heavily involved in the development of stream-sqlite)

Connect to SQLite Memory Database from seperate file (Python)

You opened a named, in-memory database connection with shared cache. Yes, you can share the cache on that database, but only if you use the exact same name. This means you need to use the full URI scheme!

If you connect with db = sqlite3.connect('file:my_db?mode=memory&cache=shared', uri=True), any additional connection within the process can see the same table, provided the original connection is still open, and you don't mind that the table is 'private', in-memory only and not available to other processes or connections that use a different name. When the last connection to the database closes, the table is gone.

So you also need to keep the connection open in the other module for this to work!

For example, if you change the module to use a global connection object:

db = None

def create_a_table():
    global db
    if db is None:
        db = sqlite3.connect('file:my_db?mode=memory&cache=shared', uri=True)

    with db:
        cursor = db.cursor()

        cursor.execute('''
            CREATE TABLE my_table(id INTEGER PRIMARY KEY, some_data TEXT)
        ''')

and then use that module, the table is there:

>>> import test_create
>>> test_create.create_a_table()
>>> import sqlite3
>>> db = sqlite3.connect('file:my_db?mode=memory&cache=shared', uri=True)
>>> with db:
...     cursor = db.cursor()
...     cursor.execute('''INSERT INTO my_table(some_data) VALUES(?)''', ("a bit of data",))
...
<sqlite3.Cursor object at 0x100d36650>
>>> list(db.cursor().execute('select * from my_table'))
[(1, 'a bit of data')]

Another way to achieve this is to open a database connection in the main code before calling the function; that creates a first connection to the in-memory database, and opening and closing additional connections won't cause the changes to be lost.

From the documentation:

When an in-memory database is named in this way, it will only share its cache with another connection that uses exactly the same name.

If you didn't mean for the database to be just in-memory, and you wanted the table to be committed to disk (to be there next time you open the connection), drop the mode=memory component.

---

Related Topics