SQLAlchemy присоединиться к 2 таблице

поэтому я решил использовать текстовый запрос SQLalchemy следующим образом

params = {"id":1}
s = text("SELECT student.*, master_edu_level.name_level FROM student left join master_edu_level ON master_edu_level.id = student.edu_level_id WHERE student.id= :id")
users_query= session.execute(s, params ).fetchall()
results = schema.dump(users_query, many=True).data

а затем для схемы учащегося добавьте поля name_level

как это

class StudentSchema(Schema):
    not_blank = validate.Length(min=1, error='Field cannot be blank')
    id = fields.Integer(dump_only=True)
    student_name        = fields.String()  
    edu_level_id        = fields.String()  
    name_level = fields.String()

благодарю вас .

На первом шаге измените схему таким образом:

import marshmallow as mm
from marshmallow_sqlalchemy import ModelSchema

class StudentSchema(ModelSchema):
    id = mm.fields.Integer(dump_only=True)
    student_name = mm.fields.String()
    education = mm.fields.Nested('MasterEdulevelSchema')

    class Meta:
        type_ = 'student'

    @mm.post_dump
    def format_dump(self, data):
        education = data.pop('education')
        data['education'] = education['name_level']
        return data


class MasterEdulevelSchema(ModelSchema):
    name_level = mm.fields.String()  

    class Meta:
        type_ = 'master_edu_level'

Он будет правильно форматировать ваши данные.

На следующем шаге объект базы данных дампа:

student_schema = StudentSchema() 
student = db.session.query(Student).join(MasterEduLevel, Student.edu_level_id == MasterEduLevel.id).first()
student_result = student_schema.dump(student)

Это должно работать, но я не проверял.

Кстати, есть много способов решить эту проблему. Как в случае с SQLAlchemy, так и на стороне Marshmallow.

Попробуй это:

from app import db
from app import session
from sqlalchemy import Table, DateTime,Date, Text, text, Column, Integer, String, MetaData, ForeignKey, select, func
from sqlalchemy.ext.declarative import declarative_base
from marshmallow_jsonapi import Schema, fields
from marshmallow import validate
from marshmallow import ValidationError
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import relationship


class MasterEduLevel(db.Model):
    __tablename__ = 'master_edu_level'
    __table_args__ = {'extend_existing': True} 

    id = db.Column(Integer, primary_key=True, index=True)
    name_level = db.Column(String(20), nullable=False, index=True)


class MasterEdulevelSchema(Schema):
    not_blank = validate.Length(min=1, error='Field cannot be blank')
    id = fields.Integer(dump_only=True)
    name_level        = fields.String()  

    class Meta:
        type_ = 'master_edu_level'

class Student(db.Model):
    __tablename__ = 'student'

    id = db.Column(Integer, primary_key=True, index=True)
    student_name = db.Column(String(20), nullable=False, index=True)
    edu_level_id = db.Column(Integer, db.ForeignKey(MasterEduLevel.id))
    edu_level = db.relationship("MasterEduLevel")

class StudentSchema(Schema):
    not_blank = validate.Length(min=1, error='Field cannot be blank')
    id = fields.Integer(dump_only=True)
    student_name        = fields.String()  
    edu_level_id        = fields.String()

    edu_level           = fields.Function(lambda x: x.edu_level)  

    # You can use Nested for get the entire object of the MaterEduLevel (comment above edu_level)
    #edu_level           = fields.Nested("MasterEdulevelSchema")

    # You can also specify a field of the Nested object using the 'only' attribute (comment above edu_level)
    #edu_level           = fields.Nested("MasterEdulevelSchema", only=('name_level',))  

    class Meta:
        type_ = 'student'

student_schema = StudentSchema()
# You can also use the ONLY attribute here
#student_schema = StudentSchema(only=('student_name', 'edu_level'))

# Many results
users_query = session.query(Student).all()
results = student_schema.dump(users_query, many=True).data

# Many results with filter
users_query = session.query(Student).filter(Student.name.like('jo%')).all()
results = student_schema.dump(users_query, many=True).data

# Many results with filter in child property
users_query = session.query(Student).join(Student.edu_level).filter(MasterEduLevel.name == 'Bachelor').all()
results = student_schema.dump(users_query, many=True).data

# Just one row by PK
user = session.query(Student).get(1) #Student.id == 1
result = student_schema.dump(user).data