python - 用关系数据库Flask-SQLAlchemy查询单表

Question

我有三个表:用户、角色、部门

我想查询具有以下选定过滤器的所有用户:Department.name、Role.name 和 User.degree

我找不到解决这个问题的方法，任何建议都很好!

这是我简化的模型:

class User(UserMixin, db.Model):

    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    password_hash = db.Column(db.String(128))
    degree = db.Column(db.String(20),default=None)
    department_id = db.Column(db.Integer, db.ForeignKey('departments.id'))
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))


class Department(db.Model):

    __tablename__ = "departments"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(sqlalchemy.types.NVARCHAR(100), unique=True)
    user = db.relationship('User', backref='department',
                                lazy='dynamic')

class Role(db.Model):

    __tablename__ = 'roles'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(sqlalchemy.types.NVARCHAR(100), unique=True)
    users = db.relationship('User', backref='role',
                                lazy='dynamic')

Answer 1

session.query(User).filter(
User.department.has(name='some_name)).filter(
User.role.has(name='some_role')).filter(
User.degree == 'some_degree')