[Python] Relationships are both of the same direction when declaring two-way foreign keys

For my ORM classes Trade, Order and Account (of stock markets) I want Order and Trade linked to each other by a foreign key, while setting the column in order to be nullable (an order may not have a trade). But SQL Alchemy complains about relationships pointing in the same direction.

import sqlite3
from typing import Optional
from sqlalchemy import String,ForeignKey,create_engine
from sqlalchemy.testing.schema import mapped_column
from sqlalchemy.orm import DeclarativeBase, Mapped, relationship, sessionmaker


class Base(DeclarativeBase):
pass


class Order(Base):
__tablename__ = 'order'

order_id: Mapped[int] = mapped_column(primary_key=True)
account_id: Mapped[str] = mapped_column(ForeignKey("account.account_id"))

corresp_trade_id: Mapped[Optional[int]] = mapped_column(ForeignKey("trade.trade_id"))
corresp_trade: Mapped[Optional["Trade"]] = relationship(back_populates='corresp_order', foreign_keys=[corresp_trade_id])

parent_order_id: Mapped[int] = mapped_column(ForeignKey(f"order.order_id"))
parent_order: Mapped["Order"] = relationship(back_populates='parent_order', foreign_keys=[parent_order_id], uselist=True)


class Trade(Base):
__tablename__ = "trade"

trade_id: Mapped[int] = mapped_column(primary_key=True)
account_id: Mapped[str] = mapped_column(ForeignKey("account.account_id"))

corresp_order_id: Mapped[int] = mapped_column(ForeignKey("order.order_id"))
corresp_order: Mapped["Order"] = relationship(back_populates='corresp_trade', foreign_keys=[corresp_order_id])


class Account(Base):
__tablename__ = 'account'

account_id: Mapped[int] = mapped_column(primary_key=True)
account_name: Mapped[str] = mapped_column(String(75))
avl_funds: Mapped[float]


class DB:
def __init__(self):
self.db = sqlite3.connect("<db_file_path>")
self.db_engine = create_engine(f"sqlite:///{<db_file_path>}", echo=True)
Base.metadata.create_all(self.db_engine)
self.Session_factory = sessionmaker(bind=self.db_engine)
self.insert_init_rows()

def insert_init_rows(self):
with self.Session_factory() as session:
first_account=Account(account_id='0', account_name='First', avl_funds=100000.00)
session.add(first_account) ### Exception here
session.commit()


When I try to persist a new account I get:

sqlalchemy.exc.ArgumentError: Trade.corresp_order and back-reference Order.corresp_trade are both of the same direction <RelationshipDirection.MANYTOONE: 2>. Did you mean to set remote_side on the many-to-one side ?
​

If I remove the foreign key from any of the tables it still fails because there are multiple other foreign keys in Trade and Order. SQLAlchemy then explicitly asks to specify a foreign key. I also need to specify back_populates in both tables.

Continue reading...