时间戳

首页 > 解决方案 > SQLAlchemy 使用多对一关系引发 NoForeignKeyError

python - SQLAlchemy 使用多对一关系引发 NoForeignKeyError

问题描述

我有三个不同的类，我想使用SQLAlchemy. 我的关系就像Class2对象是主类，每个类都Class2可以有多个Class1对象，这些对象Class1应该与Class2. 每个Class3都有两个一对一的关系，具有两个不同的 type 类Class1。

import sqlalchemy as db
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class Class1(Base):
    __tablename__ = 'Class1s'
    id = Column(Integer, primary_key=True)
    owner = relationship("Class2", back_populates="Class1s")

    crypto_type = Column(String)
    balance = Column(Integer, primary_key=True, default=0)



class Class2(Base):
    __tablename__ = 'Class2s'

    id = Column(Integer, primary_key=True)



class Class3(Base):
    __tablename__ = 'Class3'

    id = Column(Integer, primary_key=True)
    amount = Column(Integer)

    source = relationship("Class1")
    destination = relationship("Class1")


class Database:
    def __init__(self):
        self.engine = db.create_engine("sqlite:///database.db", echo=True)

        session = sessionmaker()
        session.configure(bind=self.engine)
        Base.metadata.create_all(self.engine)


if __name__ == '__main__':
    db = Database()
    print('Database Initialized Successfully!')

    user = Class2()

这会引发以下错误：

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Class1.owner - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

标签： pythondatabasesqlalchemyorm

解决方案

请阅读基本关系模式

你确实需要定义你的类底层的表是如何链接的，这是使用ForeignKey

class Class1(Base):
    __tablename__ = 'Class1s'
    id = Column(Integer, primary_key=True)
    owner = relationship("Class2", back_populates="Class1s")

    crypto_type = Column(String)
    balance = Column(Integer, primary_key=True, default=0)



class Class2(Base):
    __tablename__ = 'Class2s'

    id = Column(Integer, primary_key=True)
    class1_id = Column(ForeignKey('Class1s.id'))  # <!-- NEW **
    # Class1s = relationship("Class2", back_populates="owner")

在您的情况下，您还需要明确定义 Class3 和 Class1 之间的关系（我猜是 Transaction 和 TransactionLeg）。 有关详细信息，请参阅处理多个连接路径。

class Class3(Base):
    __tablename__ = 'Class3'

    id = Column(Integer, primary_key=True)
    amount = Column(Integer)

    source_id = Column(ForeignKey('Class2s.id'))  # <!-- NEW **
    destination_id = Column(ForeignKey('Class2s.id'))  # <!-- NEW **

    source = relationship("Class1", foreign_keys=[source_id])  # <!-- MODIFIED **
    destination = relationship("Class1",  foreign_keys=[destination_id])  # <!-- MODIFIED **

请注意，以上内容均未经过测试，可能包含拼写错误。

推荐阅读