SQLAlchemy: Single table inheritance with many to many relationship

Question

I have done a reasonable amount of OO python but I am new to SQL, and am struggling to understand which relationships are appropriate for the system I am trying to model.

I am modelling the relationships between a stock market, the stocks on that market and LICs (listed investment companies) which hold stocks on that market. As such, my first table is a market table, which has a foreign key relationship to the 'stock' table:

class Market(Base):
    __tablename__ = 'market'
    code = db.Column(db.String, primary_key=True)
    name = db.Column(db.String)
    api_code = db.Column(db.String)
    timezone = db.Column(TimezoneType(backend='pytz'))
    open = db.Column(db.Time(timezone=True))
    close = db.Column(db.Time(timezone=True))
    base_url = db.Column(db.String)

    # Foreign key relationships
    stocks = relationship('Stock', order_by='Stock.ticker', back_populates='market')

The stock table defines all stocks and has a relationship back to the market table:

class Stock(Base):
    __tablename__ = 'stock'
    ticker = db.Column(db.String, primary_key=True)
    name = db.Column(db.String)
    url = db.Column(db.String)
    price = db.Column(db.Float)
    price_time = db.Column(db.DateTime)
    shares_issued = db.Column(db.Integer)
    sector = db.Column(db.String)
    last_updated = db.Column(db.DateTime)
    type = db.Column(db.String)

    # Link stock data to markets table via market code
    market_code = db.Column(db.String, db.ForeignKey('market.code'))
    market = relationship('Market', back_populates='stocks')

    # Map polymorphism identifier for Single Table Inheritance with LIC table
    # https://docs.sqlalchemy.org/en/13/orm/inheritance.html
    __mapper_args__ = {'polymorphic_identity': 'stock',
                       'polymorphic_on': type}

Since LICs are certain type of stock, I used single table inheritance to define LICs in the stock table, which have some additional attributes:

class LIC(Stock):
    cash = db.Column(db.Integer)
    NTA = db.Column(db.Float)
    NTA_time = db.Column(db.DateTime)

    holding_ticker = db.Column(db.String, db.ForeignKey('stock.ticker'))  # had ForeignKey('holding.holding_ticker') here and produces different error
    holdings = relationship('Stock', foreign_keys=[holding_ticker], secondary='holding')

    # Map polymorphism identifier for Single Table Inheritance with stock table
    __mapper_args__ = {'polymorphic_identity': 'LIC'}

From here I am unsure how to define the relationship a LIC has with the stocks it holds. From reading the SQLAlchemy documentation (on basic relationships and inheritance) it seems that this should be a many to many relationship, like so:

class Holding(Base):
    # Many to many relationship between LIC and Stock tables
    __tablename__ = 'holding'
    LIC_ticker = db.Column(db.String, db.ForeignKey('stock.ticker'), primary_key=True)
    holding_ticker = db.Column(db.String, db.ForeignKey('stock.ticker'), primary_key=True)
    units = db.Column(db.Integer)

However I have not been able to make this relationship work, receiving the following error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship LIC.holdings - there are no foreign keys linking these tables via secondary table 'holding'.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' expressions.

When I change foreign key relationship in LIC to:

holding_ticker = db.Column(db.String, db.ForeignKey('holding.holding_ticker'))

I get:

sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for source column 'holding.holding_ticker'; mapper 'mapped class LIC->stock' does not map this column.  Try using an explicit `foreign_keys` collection which does not include destination column 'stock.holding_ticker' (or use a viewonly=True relation).

My understanding of what's going wrong is because LIC is part of the Stock table I have a kind of circular dependency thing happening, where both my foreign keys point back to the one table. It seems I need a way to use the polymorphic_identity to distinguish between a LIC and the Stock it holds in the Holdingstable..? I have considered that making LIC a separate table may help me define the many to many relationship more clearly, but seeing as how a LIC literally is a subclass of Stock then this doesn't seem like a good solution.

Sorry for the long-winded question, hopefully this is semi-coherent!

Extra problem definition/detail/background: A LIC is a company who, as the name suggests, is listed on a stock exchange and invests in other stocks (in this case) on the same stock exchange. A LIC is therefore a stock and has all the attributes of a stock, plus some new ones. Prime candidate for class inheritance, and so to me it seemed appropriate to use single table inheritance here.

One of the key attributes of a LIC is its NTA, which defines the LIC's value based on the shareholdings it has in other companies. Therefore, for this application, a LIC is composed of other stocks. So I need to link a LIC with the stocks that it holds, along with how many shares in that stock the LIC owns. This seems to me like a many-many relationship, as many LICs can own many Stocks.

SQLAlchemy: Single table inheritance with many to many relationship

0 Answers