Foreign key to the same table in Python Peewee

Question

I am using ORM peewee for sqlite in Python. I would like to create table Item with field parent_id that will be foreign key to the Item:

from peewee import *

db = SqliteDatabase("data.db")

class Item(Model):
    id = AutoField()
    parent_id = ForeignKeyField(Item, null = True)

    class Meta:
        database = db

db.create_tables([Item])

However, there is error because of circular foreign key:

NameError: free variable 'Item' referenced before assignment in enclosing scope

For this case, there is DeferredForeignKey in peewee:

from peewee import *

db = SqliteDatabase("data.db")

class Item(Model):
    id = AutoField()
    parent_id = DeferredForeignKey("Item", null = True)

    class Meta:
        database = db

db.create_tables([Item])
Item._schema.create_foreign_key(Item.parent_id)

Unfortunately, there is no ADD CONSTRAINT in sqlite, so another error appears:

peewee.OperationalError: near "CONSTRAINT": syntax error

Is there any way to create circural foreign key in sqlite using peewee, or I have to use plain integer instead of foreign key or use native SQL instead of ORM?

Fatso

Answer 1

This is documented very clearly: http://docs.peewee-orm.com/en/latest/peewee/models.html#self-referential-foreign-keys

You just put 'self' as the identifier:

class Item(Model):
    id = AutoField()
    parent = ForeignKeyField('self', backref='children', null=True)

    class Meta:
        database = db

You do not need to mess with any deferred keys or anything.

Prosy

Answer 2

I found solution. Custom constraints can be added in Meta class:

from peewee import *

db = SqliteDatabase("data.db", pragmas = {"foreign_keys": "on"})

class Item(Model):
    id = AutoField()
    parent_id = IntegerField()

    class Meta:
        database = db
        constraints = [
            SQL("FOREIGN KEY(parent_id) REFERENCES items(id)")
        ]

db.create_tables([Item])

Fatso

This was the only solution I could get to work when trying to include the field parameter on ForeignKeyField (with ForeignKeyField('self', ...)). Thanks! – Broeker