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
OUseful.Info, the blog... 