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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: