时间戳

首页 > 解决方案 > 如何在具有多个基类的 Python SQLAlchemy 类中正确定义关系？

python - 如何在具有多个基类的 Python SQLAlchemy 类中正确定义关系？

问题描述

我正在尝试使用 SQLAlchemy 和 Flask 重新创建我的数据库。

我已经创建了所有模型，但现在模型之间的关系存在一些问题。插入或更新对象时 Flask 返回以下错误消息：

  File "C:\Users\Lenna\SchoolMi\api-server-v4\venv\lib\site-packages\sqlalchemy\ext\declarative\clsregistry.py", line 326, in __call__
    x = eval(self.arg, globals(), self._dict)
  File "<string>", line 1, in <module>
    # ext/declarative/clsregistry.py
AttributeError: 'Table' object has no attribute 'id'

错误信息引用了profile类中的active_channel关系，表示channel类没有id属性。但是我已经在 ObjectWithDefaultProps 类中定义了这个属性。在 SQL 浏览器中检查 SQL 文件后，id 属性确实存在于 Channel 实体上。

我的第一个假设是外键配置错误，所以我尝试更改外键：

active_channel = db.relationship("Channel", foreign_keys="Channel.id")

代替 

active_channel = db.relationship("Channel", foreign_keys="channel.id")

不幸的是，这没有奏效。错误更改为以下内容：

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

我进一步研究了 SQLAlchemy 的多态性方面，并认为它可能与mapperargs 有关，但无法找出实现这一点的正确方法。

在我的代码中，我有以下类：

我的实体派生自多个表示共享属性或关系的对象。

class Profile(db.Model, ObjectWithDefaultProps, ObjectWithAvatar, ObjectWithNotificationProfile):

    __tablename__ = "profile"

    firebase_uid = db.Column(db.String, unique=True, nullable=False, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)
    firstname = db.Column(db.String, nullable=False)
    lastname = db.Column(db.String, nullable=False)
    about = db.Column(db.String)
    score = db.Column(db.Integer)
    email = db.Column(db.String, nullable=False)

    active_channel_id = db.Column(db.Integer, db.ForeignKey("channel.id"))
    active_channel = db.relationship("Channel", foreign_keys="channel.id")

频道.py

class Channel(db.Model, ObjectBase, ObjectWithAvatar, ObjectWithName, ProfileLinkedObject):

    __tablename__ = "channel"

    description = db.Column(db.String)
    can_add_tags = db.Column(db.Boolean, default=False, nullable=False)
    can_public_join = db.Column(db.Boolean, default=False, nullable=False)



from database.provider import db
from datetime import datetime
class ObjectWithDefaultProps:
    deleted = db.Column(db.Boolean, nullable=False, default=False)
    date_modified = db.Column(db.DateTime, default=datetime.utcnow)
    date_added = db.Column(db.DateTime, onupdate=datetime.utcnow)



from database.provider import db
from database.extensions.object_with_color import ObjectWithColor
class ObjectWithAvatar(ObjectWithColor):
    image_url = db.Column(db.String)


from database.provider import db
class ObjectWithColor:
    color_index = db.Column(db.Integer, default=0)


from database.provider import db
from sqlalchemy.ext.declarative import declared_attr

class ObjectWithNotificationProfile:

    auto_follow_questions = db.Column(db.Integer)
    auto_follow_answers = db.Column(db.Integer)
    auto_follow_comments = db.Column(db.Integer)
    auto_follow_questions_on_comment = db.Column(db.Integer)
    auto_follow_questions_on_answer = db.Column(db.Integer)
    auto_follow_answers_on_comment = db.Column(db.Integer)
    send_new_data_notification = db.Column(db.Boolean)
    send_new_members_notification = db.Column(db.Boolean)


    @declared_attr
    def question_event_preferences_id(cls):
        return db.Column(db.Integer, db.ForeignKey("custom_event_preferences.id"))

    @declared_attr
    def question_event_preferences(cls):
        return db.relationship("CustomEventPreferences", foreign_keys="custom_event_preferences.id")

    @declared_attr
    def answer_event_preferences_id(cls):
        return db.Column(db.Integer, db.ForeignKey("custom_event_preferences.id"))

    @declared_attr
    def answer_event_preferences(cls):
        return db.relationship("CustomEventPreferences", foreign_keys="custom_event_preferences.id")

    @declared_attr
    def comment_event_preferences_id(cls):
        return db.Column(db.Integer, db.ForeignKey("custom_event_preferences.id"))

    @declared_attr
    def comment_event_preferences(cls):
        return db.relationship("CustomEventPreferences", foreign_keys="custom_event_preferences.id")

    @declared_attr
    def question_tagging_preferences_id(cls):
        return db.Column(db.Integer, db.ForeignKey("custom_tagging_preferences.id"))

    @declared_attr
    def question_tagging_preferences(cls):
        return db.relationship("CustomTaggingPreferences", foreign_keys="custom_tagging_preferences.id")

    @declared_attr
    def answer_tagging_preferences_id(cls):
        return db.Column(db.Integer, db.ForeignKey("custom_tagging_preferences.id"))

    @declared_attr
    def answer_tagging_preferences(cls):
        return db.relationship("CustomTaggingPreferences", foreign_keys="custom_tagging_preferences.id")

    @declared_attr
    def comment_tagging_preferences_id(cls):
        return db.Column(db.Integer, db.ForeignKey("custom_tagging_preferences.id"))

    @declared_attr
    def comment_tagging_preferences(cls):
        return db.relationship("CustomTaggingPreferences", foreign_keys="custom_tagging_preferences.id")

标签： pythonsqlalchemy

解决方案

对于那些面临与我的问题中描述的相同或相似问题的人：

通过将 foreign_keys 属性中的字符串变量替换为列变量本身，我设法让我的代码正常工作。

经过测试，我发现使用字符串作为 foreign_keys 属性总是会触发我的错误，但是使用 primaryjoin 和 foreign_keys=cls.foreign_key 的替代方法有效。

@declared_attr
def comment_event_preferences(cls):
    return db.relationship("CustomEventPreferences", foreign_keys="custom_event_preferences.id")

变成：

@declared_attr
def comment_event_preferences(cls):
    return db.relationship("CustomEventPreferences", foreign_keys=cls.comment_event_preferences)

推荐阅读