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

Issue

I would like to query a SQLite database, downloaded from a remote sever for example via HTTP, in Python, without having ever written it to disk.

I see you can start a blank SQLite database in memory using the magic name :memory: https://docs.python.org/3/library/sqlite3.html. And I see you can copy from a disk-backed SQLite DB to an in-memory one, say using iterdump. But... I would like to populate a :memory: database with the contents of a bytes instance that contains the database, without ever having written those bytes to disk fist.

My reason: it seems unnecessary to have it all in memory, have to write it to disk, and then read it back to memory, so it's a general "keeping the number of steps down" in my process.

Solution

You can use sqlite_deserialize API that is exposed as part of APSW that can accept the serialized bytes of a SQLite database:

import apsw
import httpx

url = "https://data.api.trade.gov.uk/v1/datasets/uk-trade-quotas/versions/v1.0.366/data?format=sqlite"

with apsw.Connection(':memory:') as db:
    # deserialize is used to replace a connected database with an in-memory
    # database. In this case, we replace the "main" database, which is the
    # `:memory:` one above
    db.deserialize('main', httpx.get(url).read())
    cursor = db.cursor()
    cursor.execute('SELECT * FROM quotas;')
    print(cursor.fetchall())

This is a version of the answer at https://stackoverflow.com/a/77731273/1319998

Answered By - Michal Charemza