Flask-SQLAlchemy-棉花糖嵌套模式不起作用 [英] Flask-sqlalchemy-Marshmallow nesting Schema not working

问题描述

基本上我想做的是，加入2个表'users'&公司"，并向用户提供其相关公司详细信息.

Basically what i want to do is, join 2 tables 'users' & 'company' and get the users with their relevant company details .

这是用户模型:

class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
firstname = db.Column(db.String(10), nullable=False)
lastname = db.Column(db.String(25), nullable=False)
username = db.Column(db.String(250), nullable=False)
email = db.Column(db.String(100), nullable=False)
password = db.Column(db.String(250), nullable=False)
isPasswordReset = db.Column(db.Boolean, nullable=False)
companyId = db.Column(db.Integer, db.ForeignKey(
    'company.id'), nullable=True)

这是架构

class UserSchema(ma.Schema):
id = fields.Integer()
firstname = fields.String(required=True)
lastname = fields.String(required=True)
username = fields.String(required=True)
email = fields.String(required=True)
password = fields.String(required=True)
isPasswordReset = fields.Boolean(required=True)
companyId = fields.Integer()
company_name = fields.Nested(CompanySchema)

这是公司模式:

class Company(db.Model):
__tablename__ = 'company'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
companyName = db.Column(db.String(100), nullable=True)
companyCode = db.Column(db.String(20), nullable=True)
companyTheme = db.Column(db.String(300), nullable=True)

这是公司架构:

class CompanySchema(ma.Schema):
id = fields.Integer()
companyName = fields.String(required=True)
companyCode = fields.String(required=True)
companyTheme = fields.String(required=True)

这是resource-user.py:

and this is the resource-user.py :

users_schema = UserSchema(many=True)
user_schema = UserSchema()

class UsersResource(Resource):
def get(self):
users = db.session.query(User.firstname, Company.companyName).join(
Company, User.companyId == Company.id).all()
if users:
results = users_schema.dump(users).data
return {'status': 'success', 'message': json.dumps(results, default=str)}, 200

这是我得到的输出:

{"status":成功"，消息":"[{\"名字\:\" abc \}，{\"名字\:\" xyz \}]"}

{ "status": "success", "message": "[{\"firstname\": \"abc\"}, {\"firstname\": \"xyz\"}]" }

仅显示用户表，而不显示公司表.我已经花了几个小时学习了很多教程和内容.但仍然无法解决.我是flask和sqlalchemy的新手.请问有人知道如何纠正这个问题吗?

only user table are shown, not from company table.i have followed a lot of tutorial and stuff for hours. but still couldn't fix it.i'm new to flask and sqlalchemy. please does anyone knows how to correct this?

推荐答案

首先，您需要在sqlalchemy模型中声明关系对象:

At first you need to declare relationship object in your sqlalchemy model:

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    firstname = db.Column(db.String(10), nullable=False)
    lastname = db.Column(db.String(25), nullable=False)
    username = db.Column(db.String(250), nullable=False)
    email = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(250), nullable=False)
    isPasswordReset = db.Column(db.Boolean, nullable=False)
    companyId = db.Column(db.Integer, db.ForeignKey(
                         'company.id'), nullable=True)
    company = db.relationship("Company", backref="parents")  # <--

然后在用户架构的声明字段中代表该对象:

Then in the user schema declare field that represents that object:

class UserSchema(ma.Schema):
    id = fields.Integer()
    firstname = fields.String(required=True)
    lastname = fields.String(required=True)
    username = fields.String(required=True)
    email = fields.String(required=True)
    password = fields.String(required=True)
    isPasswordReset = fields.Boolean(required=True)
    companyId = fields.Integer()
    company = fields.Nested(CompanySchema) # <-- 

请注意，字段名称必须相同(或者您可以使用 attribute 参数).

Please notice, that field names must be the same (or you can use attribute argument).

在查询时，您可以简单地做到:

While querying you could simply do:

user_schema = UserSchema()
users = User.query.all()
results = users_schema.dump(users).data
return {'status': 'success', 'message': json.dumps(results, default=str)}, 200

(或返回users_schema.dump(用户)以进行测试)

毕竟您应该获得如下输出:

After all you should get output like this:

{
id: 'something',
firstname: 'something',
lastname: 'something',
username: 'something',
email: 'something',
password: 'something',
isPasswordReset: 'something',
company : {
           id: 'something',
           companyName: 'something',
           companyCode: 'something',
           companyTheme: 'something',
          }
}

这可以满足您的需求吗?

Does this cover your needs?

这篇关于Flask-SQLAlchemy-棉花糖嵌套模式不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！