[SOLVED] Foreign key to the same table in Python Peewee

Foreign key to the same table in Python Peewee

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?

Solution

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.