Connect to bz2 sqlite database in Python

Question

I have a bz2 file (I have never worked with such files). When I manually unzip it, I see it's a sqlite db with several tables in it, but I don't know how to connect to it all from python without having to unzip it manually (I have many dbs so it has to be automated in the script). So far, I have tried the following but get an error.

import bz2
import sqlite3

zipfile = bz2.BZ2File("file.sqlite.bz2")
connection = sqlite3.connect(zipfile.read())

query = "SELECT * FROM sqlite_master WHERE type='table';"
cursor = connection.execute(query)
cursor.fetchall()
[]

But, when I do the same query for the unzipped file I do get all the tables.

Answer 1

If you can use apsw instead of the standard python library's sqlite3 module, it's possible to open an in-memory representation of a database (Like the bytes returned by BZ2File.read():

#!/usr/bin/env python3

import bz2
import apsw

zipfile = bz2.BZ2File("file.sqlite.bz2")

db = apsw.Connection(":memory:")
db.deserialize("main", zipfile.read())

query = "SELECT * FROM sqlite_master WHERE type='table';"
cursor = db.cursor()
for row in cursor.execute(query):
    print(row)

Otherwise, since the standard bindings don't support Sqlite3's serialization functions, you'll have to save the decompressed database to a temporary file, and connect to that.