Open a sqlite3 database from an io.BytesIO stream?

Since Python 3.11, sqlite3 has a method called deserialize on the connection object which takes the bytes of a database and deserializes it. You can use it for your purpose like so:

import sqlite3


with open("temp.db", "rb") as openf:
    db_bytes = openf.read()

conn = sqlite3.connect(":memory:")
conn.deserialize(db_bytes)

Note that as mentioned in the documentation:

This method is only available if the underlying SQLite library has the deserialize API.

It's not quite open-ing it so you can run SQL queries, but you can use https://github.com/uktrade/stream-sqlite to get at all the rows in the file.

If you have an io.BytesIO instance, you can use iter to convert it to an iterable of bytes that stream-sqlite needs:

import io

import httpx
from stream_sqlite import stream_sqlite

url = 'https://www.parlgov.org/data/parlgov-development.db'
byte_stream = io.BytesIO(httpx.get(url).read())
bytes_iter = iter(lambda: byte_stream.read(4096), b'')

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

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

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())

The above doesn't use an io.BytesIO instance as in the question, but you can call read on one to get the bytes if you have one:

import io

import apsw
import httpx

# For working example purposes, constructing io.BytesIO from an HTTP response
url = "https://data.api.trade.gov.uk/v1/datasets/uk-trade-quotas/versions/v1.0.366/data?format=sqlite"
byte_stream = io.BytesIO(httpx.get(url).read())

with apsw.Connection(':memory:') as db:
    db.deserialize('main', byte_stream.read())
    cursor = db.cursor()
    cursor.execute('SELECT * FROM quotas;')
    print(cursor.fetchall())