Ish via Simon Willison, font of a thousand useful and practical hacks, a recipe for downloading a SQLite database from a file into an in-browser JupyterLite environment in-memory file system and querying it, Using the sqlite3 Python module in Pyodide:
# Ish via https://til.simonwillison.net/python/sqlite-in-pyodide from js import fetch async def load_file_into_in_mem_filesystem(url, fn=None): """Load a file from a URL into an in-memory filesystem.""" # Create a filename if required fn = fn if fn is not None else url.split("/")[-1] # Fetch file from URL res = await fetch(url) # Buffer it buffer = await res.arrayBuffer() # Write file to in-memory file system open(fn, "wb").write(bytes(buffer.valueOf().to_py())) return fn
Then call as:
url="https://raw.githubusercontent.com/psychemedia/lang-fairy-books/main/data.db" db_file = await load_file_into_in_mem_filesystem(url)
To then query the database:
import sqlite3 # Open database connection c = sqlite3.connect(db_file) # Show database tables c.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall() """ [('books',), ('books_metadata',), ('books_fts',), ('books_fts_data',), ('books_fts_idx',), ('books_fts_docsize',), ('books_fts_config',)] """
Next step: can we also save the downloaded file into browser storage, and then reload it? Possibly related discussion issue: Uploading files that can be read by python code in a notebook.
Also, is there a way of reading a sqlite database that forms part of a JupyterLite distribution, such as a the
demo.db database in this distribution: https://ouseful-pr.github.io/jupyterlite-demo/lab/index.html