KeyError / status 500 for table with references

[curiousleo]

Error message

Traceback (most recent call last):
  [...]
  File "/usr/local/lib/python3.9/site-packages/graphql/type/definition.py", line 212, in define_field_map
    field_map = field_map()
  File "/usr/local/lib/python3.9/site-packages/graphene/types/typemap.py", line 275, in construct_fields_for_type
    map = self.reducer(map, field.type)
  File "/usr/local/lib/python3.9/site-packages/graphene/types/field.py", line 119, in type
    return get_type(self._type)
  File "/usr/local/lib/python3.9/site-packages/graphene/types/utils.py", line 45, in get_type
    return _type()
  File "/usr/local/lib/python3.9/site-packages/datasette_graphql/utils.py", line 624, in getter
    return table_classes[table]
KeyError: 'Katalogwerte'
INFO:     172.17.0.1:43848 - "GET /Marktstammdatenregister/EinheitenSolar HTTP/1.1" 500 Internal Server Error

Full stack trace: traceback.txt

SQL schema

This happens when I try to open the page of a table "EinheitenSolar". It relates to the table "Katalogwerte" (mentioned in the traceback) in that the "EinheitenSolar" table contains columns which reference "Katalogwerte":

create table 'EinheitenSolar' (
  -- [...]
  'Land' integer not null references Katalogwerte(Id) deferrable initially deferred,
  'Bundesland' integer references Katalogwerte(Id) deferrable initially deferred,
  -- [...]

Note that without the GraphQL plugin, Datasette recognises the references declarations and displays the label_column as expected. So the tables themselves seem to have a structure that Datasette can generally work with, it's just this plugin that is unhappy.

Full SQL for "EinheitenSolar": EinheitenSolar.sql.txt

Screenshot

Versions

$ pip list | grep -E 'datasette(-graphql)?\s'
datasette                 0.58.1
datasette-graphql         1.5

[simonw]

Thanks for the SQL file. I could replicate this bug locally like so:

wget https://github.com/simonw/datasette-graphql/files/7360568/EinheitenSolar.sql.txt
sqlite3 einheit.db < EinheitenSolar.sql.txt
datasette einheit.db

[simonw]

OK, I see what is happening here. There's only one table in that file, but it's CREATE TABLE refers to other tables which don't currently exist.

I'm a little surprised that Datasette doesn't throw errors there to be honest - good that it doesn't!

I think I can fix this in the introspect_tables() function:

datasette-graphql/datasette_graphql/utils.py

Lines 653 to 680 in 0b107eb

	def introspect_tables(conn, datasette, db_name):
	db = sqlite_utils.Database(conn)
	table_names = db.table_names()
	view_names = db.view_names()
	table_metadata = {}
	table_namer = Namer("t")
	for table in table_names + view_names:
	datasette_table_metadata = datasette.table_metadata(
	table=table, database=db_name
	)
	columns = db[table].columns_dict
	foreign_keys = []
	pks = []
	supports_fts = bool(datasette_table_metadata.get("fts_table"))
	fks_back = []
	if hasattr(db[table], "foreign_keys"):
	# Views don't have this
	foreign_keys = db[table].foreign_keys
	pks = db[table].pks
	supports_fts = bool(db[table].detect_fts()) or supports_fts
	# Gather all foreign keys pointing back here
	collected = []
	for t in db.tables:
	collected.extend(t.foreign_keys)
	fks_back = [f for f in collected if f.other_table == table]