Open a compressed SQLite database in Python

Question

In Python, is there a more or less hacky way to open a compressed SQLite database without having to write a temporary file somewhere?

Something like:

import bz2
import sqlite3

dbfile = bz2.BZ2File("/path/to/file.bz2", "wb")
dbconn = sqlite3.connect(dbfile)

cursor = dbconn.cursor()
...

This of course raises:

ValueError: database parameter must be string or APSW Connection object

Answer 1

The underlying C-library directly uses the filename string. Thus there is no way to transparently work on it from Python.

See the code on Github

Depending on your OS, you might be able to use a RAM-disk to work on the file. If your sqlite-file is bigger than that, it might be time to switch to another DB-system, like Postgres.