Not able to get joins in flask Marshmellow and sql alchemy

Question

I want to get the producttype with admin info who added the producttype I am using sqlalchemy with postgresql and marshmallow

This is my Model related info related to Admin

class Admin(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    full_name = db.Column(db.String())
    email = db.Column(db.String(), unique=True)
    mobile = db.Column(db.String(), unique=True)
    password = db.Column(db.String())
    product_types: db.relationship("ProductType", backref="admin", lazy=True)
    created_at = db.Column(DateTime(timezone=True), server_default=func.now())
    updated_at = db.Column(DateTime(timezone=True), onupdate=func.now())

    def __repr__(self):
        return "<Admin %r>" % self.full_name


class AdminSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Admin
      


admin_schema = AdminSchema()
admins_schema = AdminSchema(many=True)

This is my Model related info related to Product_Type

class ProductType(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    descripton = db.Column(db.String())
    added_by_id = db.Column(db.Integer, db.ForeignKey("added_by.id"))
    added_by: db.relationship("Admin", backref=db.backref("admin", lazy="dynamic"))
    created_at = db.Column(DateTime(timezone=True), server_default=func.now())
    updated_at = db.Column(DateTime(timezone=True), onupdate=func.now())

    def __repr__(self):
        return "<ProductType %r>" % self.name


class ProductTypeSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = ProductType

    added_by = ma.Nested(AdminSchema)


product_type_schema = ProductTypeSchema()
product_types_schema = ProductTypeSchema(many=True)

If anyone can suggest something please do

I want to get the producttype with admin info who added the producttype I am using sqlalchemy with postgresql and marshmallow.

The output I am expecting

        product_type:{
            "created_at": "2023-01-21T07:55:14.773346+05:30",
            "descripton": "Product Type",
            "id": 6,
            "name": "dgd",
            "updated_at": null,
            "admin":{
               "full_name":""
               "email":""
               "mobile":""
            }
        }

Answer 1

Unfortunately, your code contains syntax errors as well as an inaccurate definition of the database relationships. For this reason it is difficult to interpret exactly what your database looks like or which renaming the schemas must contain.

As a note on defining relationships:

• The referencing via ForeignKey expects the names of the table and the primary key column separated by a period.
• The backref parameter defines the name of the attribute under which the back reference is accessible.

The following example shows a possible implementation to achieve the output you are aiming for.

class Admin(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    full_name = db.Column(db.String())
    email = db.Column(db.String(), unique=True)
    mobile = db.Column(db.String(), unique=True)
    password = db.Column(db.String())
    created_at = db.Column(db.DateTime(timezone=True), server_default=func.now())
    updated_at = db.Column(db.DateTime(timezone=True), onupdate=func.now())

class AdminSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Admin
        exclude = ('password',)

class ProductType(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    description = db.Column(db.String())
    admin_id = db.Column(db.Integer, db.ForeignKey("admin.id"))
    admin = db.relationship("Admin", backref=db.backref("product_types", lazy="dynamic"))
    created_at = db.Column(db.DateTime(timezone=True), server_default=func.now())
    updated_at = db.Column(db.DateTime(timezone=True), onupdate=func.now())

class ProductTypeSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = ProductType
    admin = ma.Nested(AdminSchema(only=('full_name','mobile', 'email')))