Trouble defining multiple self-referencing foreign keys in a table

I have some code here. I recently added this root_id parameter. The goal of that is to let me determine whether a File belongs to a particular Project without having to add a project_id FK into File (which would result in a model cycle.) Thus, I want to be able to compare Project.directory to File.root. If that is true, File belongs to Project.

However, the File.root attribute is not being autogenerated for File. My understanding is that defining a FK foo_id into table Foo implicit creates a foo attribute to which you can assign a Foo object. Then, upon session flush, foo_id is properly set to the id of the assigned object. In the snippet below that is clearly being done for Project.directory, but why not for File.root?

It definitely seems like it has to do with either 1) the fact that root_id is a self-referential FK or 2) the fact that there are several self-referential FKs in File and SQLAlchemy gets confused.

Things I've tried.

• Trying to define a 'root' relationship() - I think this is wrong, this should not be represented by a join.
• Trying to define a 'root' column_property() - allows read access to an already set root_id property, but when assigning to it, do开发者_Python百科es not get reflected back to root_id

How can I do what I'm trying to do? Thanks!

from sqlalchemy import create_engine, Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship, scoped_session, sessionmaker, column_property

Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = scoped_session(sessionmaker(bind=engine))

class Project(Base):
   __tablename__ = 'projects'
   id = Column(Integer, primary_key=True)
   directory_id = Column(Integer, ForeignKey('files.id'))

class File(Base):
   __tablename__ = 'files'
   id = Column(Integer, primary_key=True)
   path = Column(String)
   parent_id = Column(Integer, ForeignKey('files.id'))
   root_id = Column(Integer, ForeignKey('files.id'))
   children = relationship('File', primaryjoin=id==parent_id, backref=backref('parent', remote_side=id), cascade='all')

Base.metadata.create_all(engine)

p = Project()
root = File()
root.path = ''

p.directory = root
f1 = File()
f1.path = 'test.txt'
f1.parent = root
f1.root = root

Session.add(f1)
Session.add(root)
Session.flush()
# do this otherwise f1 will be returned when calculating rf1
Session.expunge(f1)

rf1 = Session.query(File).filter(File.path == 'test.txt').one()
# this property does not exist
print rf1.root

My understanding is that defining a FK foo_id into table Foo implicit creates a foo attribute to which you can assign a Foo object.

No, it doesn't. In the snippet, it just looks like it is being done for Project.directory, but if you look at the SQL statements being echo'ed, there is no INSERT at all for the projects table.

So, for it to work, you need to add these two relationships:

class Project(Base):
    ...
    directory = relationship('File', backref='projects')

class File(Base):
    ...
    root = relationship('File', primaryjoin='File.id == File.root_id', remote_side=id)