SQLAlchemy Self Referencing Relationship - NoReferencedTableError

Question

I'm using SA 0.6.6, Declarative style, against Postgres 8.3, to map Python objects to a database. I have a table that is self referencing and I'm trying to make a relationship property for it's children. No matter what I try, I end up with a NoReferencedTableError.

My code looks exactly like the sample code from the SA website for how to do this very thing.

Here's the class.

class FilterFolder(Base):
    __tablename__ = 'FilterFolder'
    id = Column(Integer,primary_key=True)
    name = Column(String)
    isShared = Column(Boolean,default=False)
    isGlobal = Column(Boolean,default=False)
    parentFolderId = Column(Integer,ForeignKey('FilterFolder.id'))
    childFolders = relationship("FilterFolder",
                    backref=backref('parentFolder', remote_side=id)
                    )

Here's the error I get:

NoReferencedTableError: Foreign key assocated with column 'FilterFolder.parentFolderId' could not find table 'FilterFolder' with which to generate a foreign key to target column 'id'

Any ideas what I'm doing wrong here?

score 2 · Accepted Answer · edited Apr 08 '13 at 06:14

2

This was a foolish mistake on my part. I typically specify my FK's by specifying the Entity type, not the string. I am using different schemas, so when defining the FK entity as a string I also need the schema.

Broken:

parentFolderId = Column(Integer,ForeignKey('FilterFolder.id'))

Fixed:

parentFolderId = Column(Integer,ForeignKey('SchemaName.FilterFolder.id'))

edited Apr 08 '13 at 06:14

Sheena

15,590
14
75
113

answered Mar 23 '11 at 14:21

Corey Coogan

1,569
2
17
31

2

For getting back and answering your own question, our village will sacrifice a goat in your honor, Corey Coogan. Unfortunately it will not help me with my problem, but you have pleased our gods. – Roy Prins Dec 29 '13 at 21:18

phooji · Answer 2 · 2011-03-22T21:32:01.723

1

I checked your code with SQLAlchemy 0.6.6 and sqlite. I was able to create the tables, add a parent and child combination, and retrieve them again using a session.query.

As far as I can tell, the exception you mentioned (NoReferencedTableError) is thrown in schema.py (in the SQLAlchemy source) exclusively, and is not database specific.

Some questions: Do you see the same bug if you use an sqlite URL instead of the Postgres one? How are you creating your schema? Mine looks something like this:

engine = create_engine(db_url)
FilterFolder.metadata.create_all(self.dbengine)

edited Mar 22 '11 at 21:32

answered Mar 22 '11 at 21:26

phooji

10,086
2
38
45

We're not creating our schema with SA, but are doing it with Scripts. I will give sqlite a try and see what happens. – Corey Coogan Mar 23 '11 at 12:26

SQLAlchemy Self Referencing Relationship - NoReferencedTableError

2 Answers2