datasette
datasette copied to clipboard
Published
20 hours ago •
simonw
simonw
foreign_keys error 500
Error 500 expected string or bytes-like object
run datasette espial-new.sqlite3 & click on any table other than User
/home/jon/.local/lib/python3.10/site-packages/datasette/app.py:814 in │
│ expand_foreign_keys │
│ │
│ 811 │ │ │ from {other_table} │
│ 812 │ │ │ where {other_column} in ({placeholders}) │
│ 813 │ │ """.format( │
│ ❱ 814 │ │ │ other_column=escape_sqlite(fk["other_column"]), │
│ 815 │ │ │ label_column=escape_sqlite(label_column), │
│ 816 │ │ │ other_table=escape_sqlite(fk["other_table"]), │
│ 817 │ │ │ placeholders=", ".join(["?"] * len(set(values))), │
│ │
│ ╭───────────────────────────── locals ──────────────────────────────╮ │
│ │ column = 'user_id' │ │
│ │ database = 'espial-new' │ │
│ │ db = <Database: espial-new (mutable, size=53248)> │ │
│ │ fk = { │ │
│ │ │ 'column': 'user_id', │ │
│ │ │ 'other_table': 'user', │ │
│ │ │ 'other_column': None │ │
│ │ } │ │
│ │ foreign_keys = [ │ │
│ │ │ { │ │
│ │ │ │ 'column': 'user_id', │ │
│ │ │ │ 'other_table': 'user', │ │
│ │ │ │ 'other_column': None │ │
│ │ │ } │ │
│ │ ] │ │
│ │ label_column = 'name' │ │
│ │ labeled_fks = {} │ │
│ │ self = <datasette.app.Datasette object at 0x7f0f2e77e980> │ │
│ │ table = 'bookmark' │ │
│ │ values = [] │ │
│ ╰───────────────────────────────────────────────────────────────────╯ │
│ │
│ /home/jon/.local/lib/python3.10/site-packages/datasette/utils/__init__.py:346 │
│ in escape_sqlite │
│ │
│ 343 │
│ 344 │
│ 345 def escape_sqlite(s): │
│ ❱ 346 │ if _boring_keyword_re.match(s) and (s.lower() not in reserved_words) │
│ 347 │ │ return s │
│ 348 │ else: │
│ 349 │ │ return f"[{s}]" │
│ │
│ ╭─ locals ─╮ │
│ │ s = None │ │
│ ╰──────────╯ │
╰─────────────────────────────────────────────────────────────────────────────────╯
TypeError: expected string or bytes-like object
Traceback (most recent call last):
File "/home/jon/.local/lib/python3.10/site-packages/datasette/app.py", line 1354, in route_path
response = await view(request, send)
File "/home/jon/.local/lib/python3.10/site-packages/datasette/views/base.py", line 134, in view
return await self.dispatch_request(request)
File "/home/jon/.local/lib/python3.10/site-packages/datasette/views/base.py", line 91, in dispatch_request
return await handler(request)
File "/home/jon/.local/lib/python3.10/site-packages/datasette/views/base.py", line 361, in get
response_or_template_contexts = await self.data(request, **data_kwargs)
File "/home/jon/.local/lib/python3.10/site-packages/datasette/views/table.py", line 158, in data
return await self._data_traced(request, default_labels, _next, _size)
File "/home/jon/.local/lib/python3.10/site-packages/datasette/views/table.py", line 603, in _data_traced
await self.ds.expand_foreign_keys(
File "/home/jon/.local/lib/python3.10/site-packages/datasette/app.py", line 814, in expand_foreign_keys
other_column=escape_sqlite(fk["other_column"]),
File "/home/jon/.local/lib/python3.10/site-packages/datasette/utils/__init__.py", line 346, in escape_sqlite
if _boring_keyword_re.match(s) and (s.lower() not in reserved_words):
TypeError: expected string or bytes-like object
INFO: 127.0.0.1:38574 - "GET /espial-new/bookmark HTTP/1.1" 500 Internal Server Error
INFO: 127.0.0.1:38574 - "GET /-/static/app.css?d59929 HTTP/1.1" 200 OK
Schema:
CREATE TABLE IF NOT EXISTS "user"
(
"id" INTEGER PRIMARY KEY,
"name" VARCHAR NOT NULL,
"password_hash" VARCHAR NOT NULL,
"api_token" VARCHAR NULL,
"private_default" BOOLEAN NOT NULL,
"archive_default" BOOLEAN NOT NULL,
"privacy_lock" BOOLEAN NOT NULL,
CONSTRAINT "unique_user_name" UNIQUE ("name")
);
CREATE TABLE IF NOT EXISTS "bookmark"
(
"id" INTEGER PRIMARY KEY,
"user_id" INTEGER NOT NULL REFERENCES "user" ON DELETE RESTRICT ON UPDATE RESTRICT,
"slug" VARCHAR NOT NULL DEFAULT (Lower(Hex(Randomblob(6)))),
"href" VARCHAR NOT NULL,
"description" VARCHAR NOT NULL,
"extended" VARCHAR NOT NULL,
"time" TIMESTAMP NOT NULL,
"shared" BOOLEAN NOT NULL,
"to_read" BOOLEAN NOT NULL,
"selected" BOOLEAN NOT NULL,
"archive_href" VARCHAR NULL,
CONSTRAINT "unique_user_href" UNIQUE ("user_id", "href"),
CONSTRAINT "unique_user_slug" UNIQUE ("user_id", "slug")
);
CREATE TABLE IF NOT EXISTS "bookmark_tag"
(
"id" INTEGER PRIMARY KEY,
"user_id" INTEGER NOT NULL REFERENCES "user" ON DELETE RESTRICT ON UPDATE RESTRICT,
"tag" VARCHAR NOT NULL,
"bookmark_id" INTEGER NOT NULL REFERENCES "bookmark" ON DELETE RESTRICT ON UPDATE RESTRICT,
"seq" INTEGER NOT NULL,
CONSTRAINT "unique_user_tag_bookmark_id" UNIQUE ("user_id", "tag", "bookmark_id"),
CONSTRAINT "unique_user_bookmark_id_tag_seq" UNIQUE ("user_id", "bookmark_id", "tag", "seq")
);
CREATE TABLE IF NOT EXISTS "note"
(
"id" INTEGER PRIMARY KEY,
"user_id" INTEGER NOT NULL REFERENCES "user" ON DELETE RESTRICT ON UPDATE RESTRICT,
"slug" VARCHAR NOT NULL DEFAULT (Lower(Hex(Randomblob(10)))),
"length" INTEGER NOT NULL,
"title" VARCHAR NOT NULL,
"text" VARCHAR NOT NULL,
"is_markdown" BOOLEAN NOT NULL,
"shared" BOOLEAN NOT NULL DEFAULT false,
"created" TIMESTAMP NOT NULL,
"updated" TIMESTAMP NOT NULL
);
CREATE INDEX idx_bookmark_time ON bookmark (user_id, time DESC);
CREATE INDEX idx_bookmark_tag_bookmark_id ON bookmark_tag (bookmark_id, id, tag, seq);
CREATE INDEX idx_note_user_created ON note (user_id, created DESC);
I just had the same problem. Comparing my DDL with the DDL above, I notice we both use the unqualified REFERENCES syntax:
some_column REFERENCES some_table
instead of
some_column REFERENCES some_table(id)
(sqlite clearly allows both syntaxes.)
When I changed my DDL to the qualified syntax, I stopped getting 500s from datasette. @jonschoning can you try this and confirm if it works for you? If so, it's probably just a bug in how datasette understands sqlite's DDL syntax.
