pythonpostgresqlsqlalchemy

SQLAlchemy: foreign key to multiple tables


Let's consider 3 tables:

Each book has a foreign key to its author, which can either be in the American table, or the British one.

How can I implement such foreign key condition in SQLAlchemy?

I'd like to have a single column to handle the link.


My approach so far was to create an abstract class Author, from which both AmericanAuthor and BritishAuthor inherit, and have the foreign key of Book point to the parent.

class Author(Model):
    __abstract__ = True
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

class AmericanAuthor(Author):
    __tablename__ = 'american_author'
    # some other stuff

class BritishAuthor(Author):
    __tablename__ = 'british_author'
    # some other stuff

class Book(Model):
    __tablename__ = 'book'
    title = db.Column(db.String)
    author_id = db.Column(db.Integer, db.ForeignKey("author.id"))

It fails with the error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'books.author_id' could not find table 'author' with which to generate a foreign key to target column 'id'

Which completely makes sense, considering author is abstract...


Solution

  • Although the @property decorator will work in the application it might be better to use the @hybrid_property from the sqlalchemy.ext.hybrid package. In that way you will be able to filter on that property just like any normal attribute.

    Your Book class would then look like:

    class Book(Model):
        __tablename__ = 'book'
        title = db.Column(db.String)
        american_author_id = db.Column(db.Integer, db.ForeignKey("american_author.id"), nullable=True)
        british_author_id = db.Column(db.Integer, db.ForeignKey("british_author.id"), nullable=True)
    
        @hybrid_property
        def author_id(self):
            return self.american_author_id or self.british_author_id