sqlalchemy 外键关系属性 [英] sqlalchemy foreign key relationship attributes

问题描述

我有一个 User 表和一个 Friend 表.Friend 表包含两个指向我的 User 表的外键以及一个状态字段.我试图能够从我的用户表中调用 Friend 对象上的属性.例如，我希望能够执行诸如friend.name 或friend.email 之类的操作.

I have a User table and a Friend table. The Friend table holds two foreign keys both to my User table as well as a status field. I am trying to be able to call attributes from my User table on a Friend object. For example, I would love to be able to do something like, friend.name, or friend.email.

class User(Base):
    """ Holds user info """

    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(25), unique=True)
    email = Column(String(50), unique=True)
    password = Column(String(25))
    admin = Column(Boolean)

    # relationships
    friends = relationship('Friend', backref='Friend.friend_id',primaryjoin='User.id==Friend.user_id', lazy='dynamic')

class Friend(Base):
    __tablename__ = 'friend'

    user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
    friend_id = Column(Integer, ForeignKey(User.id), primary_key=True)
    request_status = Column(Boolean)

当我获得 friend 对象时，我只有 2 个 user_ids 并且我想显示每个用户的所有属性，以便我可以在表单等中使用该信息.我是 sqlalchemy 的新手 - 仍在尝试学习更多高级功能.这只是一个更大的 Flask 项目的一个片段，这个功能将用于 friend 请求等.我试图查找关联对象等，但我很难做到.

When I get friend objects all I have is the 2 user_ids and i want to display all properties of each user so I can use that information in forms, etc. I am new to sqlalchemy - still trying to learn more advanced features. This is just a snippet from a larger Flask project and this feature is going to be for friend requests, etc. I've tried to look up association objects, etc, but I am having a hard with it.

任何帮助将不胜感激.

推荐答案

首先，如果你使用的是 flask-sqlalchemy，为什么你直接使用 sqlalchemy 而不是 Flask 的 db.型号?

First, if you're using flask-sqlalchemy, why are you using directly sqlalchemy instead of the Flask's db.Model?

我强烈建议使用 flask-sqlalchemy 扩展，因为它利用了会议和其他一些整洁的事情.

I strongly reccomend to use flask-sqlalchemy extension since it leverages the sessions and some other neat things.

创建代理便利对象很简单.只需在 Friend 类中添加与它的关系即可.

Creating a proxy convenience object is straightforward. Just add the relationship with it in the Friend class.

class Friend(Base):
    __tablename__ = 'friend'

    user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
    friend_id = Column(Integer, ForeignKey(User.id), primary_key=True)
    request_status = Column(Boolean)

    user = relationship('User', foreign_keys='Friend.user_id')
    friend = relationship('User', foreign_keys='Friend.friend_id')

SQLAlchemy 会处理剩下的事情，你可以简单地通过以下方式访问用户对象:

SQLAlchemy will take care of the rest and you can access the user object simply by:

name = friend.user.name

如果您打算每次使用 friend 对象时都使用 user 对象，请在 中指定 lazy='joined'关系.这样它就可以在单个查询中加载两个对象.

If you plan to use the user object every time you use the friend object specify lazy='joined' in the relationship. This way it loads both object in a single query.

这篇关于sqlalchemy 外键关系属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！