sqlite-utils: Extract columns cannot create foreign key relation: sqlite3.OperationalError: table sqlite_master may not be modified

Thanks for what seems like a truly great suite of libraries. I wanted to try out Datasette, but never got more than half way through your YouTube video with the SF tree dataset. Whenever I try to extract a column, I get a sqlite3.OperationalError: table sqlite_master may not be modified error from Python. This snippet reproduces the error on my system, Python 3.9.1 and sqlite-utils 3.5 on an M1 Macbook Pro running in rosetta mode:

curl "https://data.nasa.gov/resource/y77d-th95.json" | \
    sqlite-utils insert meteorites.db meteorites - --pk=id
sqlite-utils extract meteorites.db meteorites  recclass

I have tried googling the problem, but all I’ve found is that this might be a problem with the sqlite3 database running in defensive mode, but I definitely can’t know for sure. Does the problem seem familiar to you?

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 3
Comments: 18 (8 by maintainers)

Commits related to this issue

Documentation for pysqlite3 and sqlean, refs #235, #559 — committed to simonw/sqlite-utils by simonw a year ago
Release 3.33 Refs #235, #559, #560 Refs https://github.com/simonw/llm/issues/60 Refs https://github.com/nalgeon/sqlean.py/issues/1 — committed to simonw/sqlite-utils by simonw a year ago
Use sqlean if available in environment (#560) Closes #559 Closes #235 Refs https://github.com/simonw/llm/issues/60 - Uses `sqlean` in place of `sqlite3` if `sqlean.py` is installed - Uses `sq... — committed to numist/sqlite-utils by simonw a year ago

Most upvoted comments

This bug affects me as well. Env:

Python 3.8.12
sqlite-utils, version 3.28
sqlite3 3.32.3
MacOS Big Sur 11.6.7
Intel

Similar to @mdrovdahl, I was able to work around this bug by piping the SQL string constructed in add_foreign_keys to the sqlite3 command itself. Specifically, if you’re trying to patch this yourself, replace lines 1026-1039 of db.py in your site packages with something similar to the following:

print("PRAGMA writable_schema = 1;")
for table_name, new_sql in table_sql.items():
    print("UPDATE sqlite_master SET sql = '{}' WHERE name = '{}';".format(
         new_sql, table_name)
    )
print("PRAGMA writable_schema = 0;")
print("VACUUM;")

Then from your terminal: db-to-sqlite "<connection string>" your.db --all > output.sql && sqlite3 your.db < output.sql

If you want to run this with -p, you’ll have to actually open a file in code to write to instead of redirecting the output.

wpears on Aug 23, 2022

https://sqlite-utils.datasette.io/en/stable/changelog.html#v3-33 - upgrading to sqlite-utils>=3.33 and then installing both sqlean.py and sqlite-dump in the same virtual environment as sqlite-utils should fix this issue.

simonw on Jun 26, 2023

I just tested this in a brand new virtual environment using the macOS Python 3:

pipenv shell --python /Applications/Xcode.app/Contents/Developer/usr/bin/python3

Then in that virtual environment I ran:

pip install sqlite-utils
# Confirm the right one is on the path:
which sqlite-utils

curl "https://data.nasa.gov/resource/y77d-th95.json" | \
    sqlite-utils insert meteorites.db meteorites - --pk=id
sqlite-utils extract meteorites.db meteorites  recclass

This threw the same error reported above. Then I did this:

rm meteorites.db
pip install sqlean.py

curl "https://data.nasa.gov/resource/y77d-th95.json" | \
    sqlite-utils insert meteorites.db meteorites - --pk=id
sqlite-utils extract meteorites.db meteorites  recclass

And that second time it worked correctly.

simonw on Jun 26, 2023