Downloading and Querying a SQLite3 Database From a Remote URL Using JupyterLite

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
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:


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


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:

Author: Tony Hirst

I'm a Senior Lecturer at The Open University, with an interest in #opendata policy and practice, as well as general web tinkering...

%d bloggers like this: