Self reference foreign key example. Test to check if mysql and sqlite supported DEFERRED constraint checks, turns out not.

self_reference_fk.py

from sqlalchemy import Column, Integer, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Session

Base = declarative_base()


class Person(Base):
  __tablename__ = 'persons'
  id = Column(Integer, primary_key=True)
  best_friend_id = Column(
    Integer,
    ForeignKey('persons.id', use_alter=True, name='fk_best_friend'))
    #if we wanted to set nullable=False to the foreignkey, we'd need an engine with
    # DEFERRED constraing checks support
  best_friend = relationship(
    'Person',
    primaryjoin='Person.best_friend_id==Person.id',
    post_update=True,
    foreign_keys=[best_friend_id],
    uselist=False
  )


engine = create_engine('sqlite:///')
session = Session(bind=engine)
Base.metadata.create_all(bind=engine)
guy1 = Person()
guy1.best_friend = guy1
session.add(guy1)
session.commit()