Foreign keys are not compatible with @cache

[sizhky]

schema = {'department': {'id': int, 'name': str},
 'students': {'columns': {'id': int, 'name': str, 'department_id': str},
  'foreign_keys': ['department_id']},
 'professors': {'columns': {'id': int, 'name': str, 'department_id': str},
  'foreign_keys': ['department_id']},
 'classes': {'columns': {'id': int, 'name': str, 'department_id': str},
  'foreign_keys': ['department_id']}}

db = database(DB)
for t in db.table_names(): db.t[t].drop()

for t,meta in schema.items():
    cs = meta.get('columns', meta)
    fks = meta.get('foreign_keys')
    db.create_table(t, cs, pk='id', foreign_keys=fks)

We get the error

TypeError                                 Traceback (most recent call last)
Cell In[3], line 16
     14 if isinstance(fks, L): fks = list(fks)
     15 print(fks)
---> 16 db.create_table(t, cs, pk='id', foreign_keys=fks)

File /opt/miniconda3/lib/python3.12/site-packages/sqlite_minutils/db.py:976, in Database.create_table(self, name, columns, pk, foreign_keys, column_order, not_null, defaults, hash_id, hash_id_columns, extracts, if_not_exists, replace, ignore, transform, strict)
    974 self.execute(sql)
    975 foreign_keys = self.resolve_foreign_keys(name, foreign_keys) if foreign_keys is not None else None
--> 976 created_table = self.table(
    977     name,
    978     pk=pk,
    979     foreign_keys=foreign_keys,
    980     column_order=column_order,
    981     not_null=not_null,
    982     defaults=defaults,
    983     hash_id=hash_id,
    984     hash_id_columns=hash_id_columns,
    985 )
    986 return cast(Table, created_table)

TypeError: unhashable type: 'list'

because self.table is @cache decorated and foreign keys are a list

[Deufel]

What version of python are you using?

I have had similar issues(skill issue most likely) with fk definitions and while I have not been able to find the root cause of it
I did figure out that these concise fk definitions will work on python 3.10 but not 3.12 [ you can check just run this in a google-colab enviorment which uses python 3.10]

! pip install python-fasthtml
from fasthtml.common import *

schema = {'department': {'id': int, 'name': str},
 'students': {'columns': {'id': int, 'name': str, 'department_id': str},
  'foreign_keys': ['department_id']},
 'professors': {'columns': {'id': int, 'name': str, 'department_id': str},
  'foreign_keys': ['department_id']},
 'classes': {'columns': {'id': int, 'name': str, 'department_id': str},
  'foreign_keys': ['department_id']}}

db = Database(memory=True)
for t in db.table_names(): db.t[t].drop()

for t,meta in schema.items():
    cs = meta.get('columns', meta)
    fks = meta.get('foreign_keys')
    db.create_table(t, cs, pk='id', foreign_keys=fks)


print(f"Python version: {sys.version}")
diagram(db.tables)

When using python 3.12 you need to be more exact with the fk definitions. to get your code to work in 3.12..

schema = {'department': {'id': int, 'name': str},
 'students': {'columns': {'id': int, 'name': str, 'department_id': str},
  'foreign_keys': (("department_id", "department", "id"),)},
 'professors': {'columns': {'id': int, 'name': str, 'department_id': str},
  'foreign_keys': (("department_id", "department", "id"),)},
 'classes': {'columns': {'id': int, 'name': str, 'department_id': str},
  'foreign_keys': (("department_id", "department", "id"),)}}

db = Database(memory=True)
for t in db.table_names(): db.t[t].drop()

for t,meta in schema.items():
    cs = meta.get('columns', meta)
    fks = meta.get('foreign_keys')
    db.create_table(t, cs, pk='id', foreign_keys=fks)


print(f"Python version: {sys.version}")
diagram(db.tables)

[Deufel]

edit looks like Its more just related to the types - must be something todo with immutable/ mutable datatypes ?

'foreign_keys': ['department_id']  # OK in 3.10 Not in 3.12

'foreign_keys': (('department_id',),)  # OK in 3.12