首页 / 技术问答 / Python问答列表 / 问答详情

Python SQLAlchemy与；间接的；外键

Context

我正在使用SQLAlChemy(V2)为应用程序设置一个数据库，并try 在我的数据上建立一些关系模型. 这些关系如下图所示.

ER-Diagram

所以我有这些分层的多对一关系，其中Cluster属于SubSubtype，而SubSubtype又属于Subtype. Sample很相似，但它们可能只有Subtype，而不是SubSubtype.

Problem

现在我有一个问题，在我的cluster表中，我只想要一次对subtype_id的引用(最好是在subsubtype.subtype_id上)，类似地，在我的Sample表中，我只想引用subtype_id一次.

当指定alternate join condition或custom FK-relationship时，这应该没有问题，我已经try 过了.

我有一些代码(可以在最下面找到)，可以做到这一点，但当我运行它时，它成功地创建了数据库模式，但插入对象失败. 我得了NoForeignKeysError分，而SQLAlChemy告诉我

没有外键链接[子类型和集群]表.

并向

请确保引用列与ForeignKey或ForeignKeyConstraint相关联，或指定‘PrimiyJoin’表达式.

虽然确实没有直接联系，但我指定了要使用的primaryjoin表达式and和foreign_keys(使用和不使用不同的组合).

此外，当我随后在生成的文件中可视化数据库模式时，我得到了下面的图，这似乎表明我想要的应该可以工作.

DataGrip Diagram

对我来说，这实际上像是SQLAlChemy的错误(至少错误消息不好)，但我了解到，这类事情通常是用户错误，所以我首先来这里征求第二意见.

我已将完整的错误消息(不包括生成的SQL)放在末尾，因为细节标记似乎不适用于此.

我也试着用subtype(和/或subsubtype)的hybrid_propertydecorator ，这可以用Cluster，但不能用Sample，因为我仍然不明白如果没有

Question

如何在不复制引用的情况下在SQLAlChemy中对这些关系进行建模，并且最好使用声明性样式.

如果有必要实现该目标，则可以更改模式.

类似的问题

我看过这些其他问题，虽然有些相似，但它们在这里对我并没有真正的帮助.

我没有找到更多看起来相关的东西，但如果有人给我指给我一个适用的解决方案，我会很高兴的.

Code

下面是一个(多少)可重现的最小示例:

from pathlib import Path
from typing import List, Optional

from sqlalchemy import ForeignKey, String, create_engine, event, Engine
from sqlalchemy.orm import Mapped, mapped_column, relationship, DeclarativeBase, Session


class Base(DeclarativeBase):
    pass


@event.listens_for(Engine, "connect")
def sqlite_pragma_enforce_foreign_keys(dbapi_connection, _):
    """
    This is necessary to enforce foreign key constraints on SQLite.
    Cf. `SQLAlchemy docs <https://docs.sqlalchemy.org/en/20/dialects/sqlite.html#foreign-key-support>`_.

    :param dbapi_connection: The database connection.
    :param _: connection_record?
    """
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()


class Sample(Base):
    __tablename__ = "sample"

    id: Mapped[str] = mapped_column(String(16), primary_key=True)
    """A unique identifier for the sample, aka. the "scount"."""

    sequence: Mapped[str] = mapped_column()
    """The actual genome data/consensus sequences. May only contain valid characters, cf. :meth:`_validate_sequence`."""

    subtype_id: Mapped[str] = mapped_column(ForeignKey("subtype.id"))
    """The ID of the sub- or sub-subtype of this sample."""

    subsubtype_id: Mapped[Optional[str]] = mapped_column(ForeignKey("subsubtype.id"))
    """The ID of the sub-subtype of this sample."""

    subtype: Mapped["Subtype"] = relationship(back_populates="samples")
    """The :class:`Subtype` of this sample."""

    subsubtype: Mapped[Optional["SubSubtype"]] = relationship(back_populates="samples")
    """The :class:`SubSubtype` of this sample."""


class Subtype(Base):
    __tablename__ = "subtype"

    id: Mapped[str] = mapped_column(String(3), primary_key=True)
    """The id of this subtype."""

    subsubtypes: Mapped[List["SubSubtype"]] = relationship()
    """A list of :class:`SubSubtype` clades under this subtype."""

    clusters: Mapped[List["Cluster"]] = relationship()
    """A list of :class:`Cluster` objects under this sub-subtype."""

    samples: Mapped[List[Sample]] = relationship()
    """All :class:`Sample` objects of this subtype."""


class SubSubtype(Base):
    __tablename__ = "subsubtype"

    subtype_id: Mapped[str] = mapped_column(ForeignKey("subtype.id"), primary_key=True)
    """Sub-subtypes belong to a :class:`Subtype`, which is their "parent", this is identified by the ``subtype_id``."""

    id: Mapped[str] = mapped_column(String(16), primary_key=True)
    """The sub-subtype specific part of the id."""

    subtype: Mapped[Subtype] = relationship(back_populates="subsubtypes")
    """Sub-subtypes have a :class:`Subtype` as parent."""

    clusters: Mapped[List["Cluster"]] = relationship()
    """A list of :class:`Cluster` objects under this sub-subtype."""

    samples: Mapped[List[Sample]] = relationship()
    """All :class:`Sample` objects of this subtype."""


class Cluster(Base):
    __tablename__ = "cluster"

    subtype_id: Mapped[str] = mapped_column(ForeignKey("subsubtype.subtype_id"), primary_key=True)
    """The ID of the sub- or sub-subtype of this cluster."""

    subsubtype_id: Mapped[str] = mapped_column(ForeignKey("subsubtype.id"), primary_key=True)
    """The ID of the sub-subtype of this cluster."""

    id: Mapped[str] = mapped_column(String(10), primary_key=True)
    """The cluster specific part of the name/id, e.g., in case of "A1_1", it would be "1"."""

    subtype: Mapped["Subtype"] = relationship(
        primaryjoin=subtype_id == Subtype.id,
        foreign_keys=[subtype_id],
        back_populates="clusters",
    )
    """The :class:`Subtype` of this cluster."""

    subsubtype: Mapped["SubSubtype"] = relationship(back_populates="clusters")
    """The :class:`SubSubtype` of this cluster."""


if __name__ == '__main__':
    engine = create_engine("sqlite:///:memory:", echo=True)
    Base.metadata.create_all(engine)

    subtype = Subtype(id="A")
    subsubtype = SubSubtype(subtype_id="A", id="1")
    cluster = Cluster(subtype_id="A", subsubtype_id="1", id="1")

    with Session(engine) as session:
        session.add_all([subtype, subsubtype, cluster])
        session.commit()

Exception

Full output 
Traceback (most recent call last):
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 2418, in _determine_joins
    self.primaryjoin = join_condition(
                       ^^^^^^^^^^^^^^^
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/sql/util.py", line 123, in join_condition
    return Join._join_condition(
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/sql/selectable.py", line 1358, in _join_condition
    raise exc.NoForeignKeysError(
sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'subtype' and 'cluster'.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/fynn/.config/JetBrains/PyCharm2023.2/scratches/scratch_6.py", line 138, in <module>
    subtype = Subtype(id="A")
              ^^^^^^^^^^^^^^^
  File "<string>", line 4, in __init__
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/state.py", line 561, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
    fn(*args, **kw)
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 4391, in _event_on_init
    instrumenting_mapper._check_configure()
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 2386, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 4199, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 4240, in _do_configure_registries
    mapper._post_configure_properties()
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 2403, in _post_configure_properties
    prop.init()
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/interfaces.py", line 579, in init
    self.do_init()
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 1636, in do_init
    self._setup_join_conditions()
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 1881, in _setup_join_conditions
    self._join_condition = jc = JoinCondition(
                                ^^^^^^^^^^^^^^
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 2305, in __init__
    self._determine_joins()
  File "/home/fynn/Desktop/HIV/HIV-Clustering/venv/lib64/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 2439, in _determine_joins
    raise sa_exc.NoForeignKeysError(
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Subtype.clusters - there are no foreign keys linking these tables.  请确保引用列与ForeignKey或ForeignKeyConstraint相关联，或指定‘PrimiyJoin’表达式.

推荐答案

由于SubSubType具有复合主键，因此Cluster需要复合外键.与其在每列上单独使用ForeignKey()，不如在__table_args__中使用ForeignKeyConstraint()，如下所示:

from sqlalchemy import create_engine, ForeignKey, ForeignKeyConstraint, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

engine = create_engine("sqlite://")


class Base(DeclarativeBase):
    pass


class SubType(Base):
    __tablename__ = "subtype"

    id: Mapped[str] = mapped_column(primary_key=True)


class SubSubType(Base):
    __tablename__ = "subsubtype"

    subtype_id: Mapped[str] = mapped_column(
        ForeignKey("subtype.id"), primary_key=True
    )
    id: Mapped[str] = mapped_column(String(16), primary_key=True)


class Cluster(Base):
    __tablename__ = "cluster"

    subtype_id: Mapped[str] = mapped_column(primary_key=True)
    subsubtype_id: Mapped[str] = mapped_column(primary_key=True)
    id: Mapped[str] = mapped_column(String(10), primary_key=True)

    __table_args__ = (
        ForeignKeyConstraint(
            [subtype_id, subsubtype_id], [SubSubType.subtype_id, SubSubType.id]
        ),
    )


engine.echo = True
Base.metadata.create_all(engine)
"""
CREATE TABLE subtype (
    id VARCHAR NOT NULL, 
    PRIMARY KEY (id)
)

CREATE TABLE subsubtype (
    subtype_id VARCHAR NOT NULL, 
    id VARCHAR(16) NOT NULL, 
    PRIMARY KEY (subtype_id, id), 
    FOREIGN KEY(subtype_id) REFERENCES subtype (id)
)

CREATE TABLE cluster (
    subtype_id VARCHAR NOT NULL, 
    subsubtype_id VARCHAR NOT NULL, 
    id VARCHAR(10) NOT NULL, 
    PRIMARY KEY (subtype_id, subsubtype_id, id), 
    FOREIGN KEY(subtype_id, subsubtype_id) REFERENCES subsubtype (subtype_id, id)
)
"""

以下是来自SQLAlChemy文档中类似情况的example分.

Python相关问答推荐

回归回溯-2D数组中的单词搜索

如何对行使用分段/部分.diff()或.pct_change()？

如何在Power Query中按名称和时间总和进行分组

了解shuffle在NP.random.Generator.choice()中的作用

如何使用PyTest根据self 模拟具有副作用的属性

收件箱转换错误- polars.exceptions. ComputeHelp- pandera(0.19.0b3)带有polars

Pydantic 2.7.0模型接受字符串日期时间或无

将整组数组拆分为最小值与最大值之和的子数组

SQLGory-file包FilField不允许提供自定义文件名，自动将文件保存为未命名

如何在Python中并行化以下搜索？

django禁止直接分配到多对多集合的前端.使用user.set()

ODE集成中如何终止solve_ivp的无限运行

使用密钥字典重新配置嵌套字典密钥名

根据列值添加时区

Django RawSQL注释字段

如何更改groupby作用域以找到满足掩码条件的第一个值？

ConversationalRetrivalChain引发键错误

Gekko中基于时间的间隔约束

如何在Great Table中处理inf和nans

Cython无法识别Numpy类型

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630