Error when trying to use sqlite3

[psychemedia]

Following the sqlite3 docs at https://docs.python.org/3/library/sqlite3.html, an error is raised when trying to query a SQLite database:

# Using the example code from
#https://docs.python.org/3/library/sqlite3.html

import sqlite3
con = sqlite3.connect("tutorial.db")

cur = con.cursor()

cur.execute("CREATE TABLE movie(title, year, score)")

res = cur.execute("SELECT name FROM sqlite_master")

Throws error:

---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
Cell In[2], line 11
      7 cur = con.cursor()
      9 cur.execute("CREATE TABLE movie(title, year, score)")
---> 11 res = cur.execute("SELECT name FROM sqlite_master")

DatabaseError: database disk image is malformed

[rth]

Interesting. I think it because the the current directory (/drive) is not a MEMFS (unlike the rest of the file system), but rather a DriveFS used, from what I understand, for notebook persistance. Probably it's even less POSIX compliant than MEMFS, which becomes an issue for sqlite.

If you write in any other folder say /tmp/ the above example would work.

[psychemedia]

If I create a folder manually (eg via right click into sidebar) and try to reference into that I get the same error. Also, saving the db to /tmp doesn't persist the database file to browser local storage, so the db would need recreating each session as any modifications made to it would not be persisted.

[psychemedia]

I'm guessing this issue is just carried over from when the kernel was part of the core Jupyterlite repo? jupyterlite/jupyterlite#840

[oscarpereztuta]

@psychemedia leaving here two workarounds:

1. As @rth says, create the database in the /tmp/ folder:

import sqlite3

con = sqlite3.connect('/tmp/tutorial.db')

cur = con.cursor()
cur.execute("CREATE TABLE movie(title, year, score)")
res = cur.execute("SELECT name FROM sqlite_master")
res.fetchone()

2. Create a connection to an in-memory database:

import sqlite3

con = sqlite3.connect(':memory:')

cur = con.cursor()
cur.execute("CREATE TABLE movie(title, year, score)")
res = cur.execute("SELECT name FROM sqlite_master")
res.fetchone()

[psychemedia]

@oscarpereztuta Thanks... if pandas can read from/write to the in-memory db, I guess that could persist the tables as CSV.

[oscarpereztuta]

@psychemedia, SQLite databases opened with the sqlite3 module in Python do persist changes to disk by default.

To ensure that changes to the database are persisted to disk, you can set the journal_mode of the database connection to "WAL" (Write-Ahead Logging) mode.

Here's an example of how to set the journal_mode to "WAL" in a sqlite3 connection using the SQLite3 Tutorial:

import sqlite3
import shutil

# create a connection to a SQLite database file in the /tmp directory
con = sqlite3.connect('/tmp/tutorial.db')

# set the journal_mode to WAL (Write-Ahead Logging) for better performance and reliability
con.execute('PRAGMA journal_mode=WAL;')

# create a cursor object to execute SQL commands
cur = con.cursor()

# create a new table named "movie" with three columns: title, year, and score
cur.execute("CREATE TABLE movie(title, year, score)")

# retrieve the names of all tables in the database
res = cur.execute("SELECT name FROM sqlite_master")
res.fetchone()

# insert data into the "movie" table
cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

# commit the changes to the database
con.commit()

# close the cursor and connection
res.close()
con.close()

# move the file from /tmp to /drive JupyterLite directory
shutil.move('/tmp/tutorial.db', '/drive/tutorial.db')

Then you can download the DB file from JupyterLite and see the result on a web application like this one: https://sqliteviewer.app/

[psychemedia]

@oscarpereztuta Thanks for that. The interaction I am trying to simplify is for a non-expert learner in a distance education setting who just wants to run a notebook in JupyterLite and have changes persisted in local browser storage that they can make use of across multiple sessions.

[rth]

If you want to use the home dir, someone would need to figure out what is the missing functionality currently in DriveFS required for Sqlite3 and add it. I don't know enough about this. I see there are different backends for browser storage it could be interesting to try to change the backend and see if the issue persists.

Writing the DB in a manually mounted IDBFS (persistent) folder appears to work but that's not very user friendly from a notebook perspective. DriveFS is also able to use IDBFS from Emscripten as a backend, so this does indeed sound like a bug.