samedi 23 juillet 2022

Flask-SQL Alchemy how to union two different models from different tables

Hello I'm new to flask and I have an application where i am creating different models and schemas for my entities. These two models and schemas are very close to each other except with few differences. I have base classes for my model and schema so i could inherit and re-use the same class. However, i'm having a problem when i need to deserialize them with marshall and return the union result.

I'm using marshmallow,sql-achemy and flaskapi-spec. I am not sure if there's a way to use the marshall_with decorator with multiple schemas since I want to union my results and return the aggregated model.

Here is the endpoint,models and classes I have.

Models;

class BasePublisher(Model):
    __abstract__= True
    id= Column(db.String(80),primary_key=True,nullable=False)
    date = Column(db.DateTime, default=dt.datetime.utcnow, primary_key=True, nullable=False)
    views = Column(db.Numeric)
    clicks = Column(db.Numeric)
    publisher = Column(db.String(80),primary_key=True,nullable=False)

class Facebook(BasePublisher):
    __tablename__='facebook_table'

    def __init__(self, **kwargs):
        db.Model.__init__(self, **kwargs)

class Pinterest(BasePublisher):
    __tablename__='pin_table'
    
    def __init__(self, user, **kwargs):
        db.Model.__init__(self, user=user, **kwargs)

Schemas

class PublisherSchema(Schema):
    date = fields.DateTime(dump_only=True)
    type = fields.DateTime(dump_only=True)
    views = fields.Number(dump_only=True)
    clicks = fields.Number(dump_only=True)
    publisher = fields.Str(dump_only=True)

class FacebookSchema(PublisherSchema):
     @post_dump
     def dump_data(self,data):
           data["type"]="Facebok"

class PinterestSchema(PublisherSchema):
     @post_dump
     def dump_data(self,data):
          data["type"]="Pinterest

"

-View

@blueprint.route('/api/sample/publishers/<id>', methods=('GET',))
@use_kwargs({'type': fields.Str(), 'start_date': fields.Str(),'end_date':fields.Str()},location="query")
@marshal_with(facebook_schema)
def get_data(id, type, start_date=None,end_date=None):
    facebook_data = Facebook.query.filter_by(id=id)
    .filter(Facebook.date.between(start_date,end_date))
    .limit(10).all()

Ideally i would like to do this in my view;

     pinterest_data = Pinterest.query.filter_by(id=id)
    .filter(Pinterest.date.between(start_date,end_date))
    .limit(10).all()
    facebook_data.query.union(pinterest_data)

Union like this throws an error in flask application and also i have slightly different schemas for each publisher and i don't know how i can return both of them when i de-serialize with marshall

something like this maybe?

@marshal_with(facebook_schema,pinterest_schema)
    

Aucun commentaire:

Enregistrer un commentaire