Prevent marshmallow from querying the db

224 views Asked by At

I want to completely prevent marshmallow from querying the db.

Below the explanatory code snippet

from flask_sqlalchemy import SQLAlchemy
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema
from flask_restful import Resource

db = SQLAlchemy()

class FooModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(256))
    picture = db.Column(db.String(256))
    about = db.Column(db.String(256))

class FooSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = FooModel

class FooRessource(Resource):
    def get(self):
        foo = FooModel(name="Mi", picture="pic", about="ab")
        db.session.add(foo)
        db.session.commit()

        created = FooModel.query.options(db.load_only("name")).first()
        created_data = FooSchema().dump(created)
        return {'data': created_data}

I have turned SQLAlchemy logs on with SQLALCHEMY_ECHO = True. Can now see this from logs

INFO     sqlalchemy.engine.Engine:log.py:117 INSERT INTO foo_model (name, picture, about) VALUES (%(name)s, %(picture)s, %(about)s) RETURNING foo
_model.id
INFO     sqlalchemy.engine.Engine:log.py:117 [generated in 0.00040s] {'name': 'Mi', 'picture': 'pic', 'about': 'ab'}
INFO     sqlalchemy.engine.Engine:log.py:117 COMMIT
INFO     sqlalchemy.engine.Engine:log.py:117 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:log.py:117 SELECT foo_model.id AS foo_model_id, foo_model.name AS foo_model_name
FROM foo_model
 LIMIT %(param_1)s
INFO     sqlalchemy.engine.Engine:log.py:117 [generated in 0.00024s] {'param_1': 1}
INFO     sqlalchemy.engine.Engine:log.py:117 SELECT foo_model.picture AS foo_model_picture, foo_model.about AS foo_model_about
FROM foo_model
WHERE foo_model.id = %(pk_1)s
INFO     sqlalchemy.engine.Engine:log.py:117 [generated in 0.00026s] {'pk_1': 1}

As you can see there's two db select queries one from my resource and another one from Marshmallow.

When I update my schema instance like following created_data = FooSchema(only="name",)).dump(created) the query from Marshmallow disappears and that what I want as you can see below.

INFO     sqlalchemy.engine.Engine:log.py:117 INSERT INTO foo_model (name, picture, about) VALUES (%(name)s, %(picture)s, %(about)s) RETURNING foo
_model.id
INFO     sqlalchemy.engine.Engine:log.py:117 [generated in 0.00024s] {'name': 'Mi', 'picture': 'pic', 'about': 'ab'}
INFO     sqlalchemy.engine.Engine:log.py:117 COMMIT
INFO     sqlalchemy.engine.Engine:log.py:117 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:log.py:117 SELECT foo_model.id AS foo_model_id, foo_model.name AS foo_model_name
FROM foo_model
 LIMIT %(param_1)s
INFO     sqlalchemy.engine.Engine:log.py:117 [generated in 0.00021s] {'param_1': 1}

I want to know if there's an elegant way to tell marshmallow to not query the db and do deserialization with available data on the instance. Am working on large project and this is now problematic as Marshmallow can come to query many relationship's unwanted data resulting in super slow requests.

Any suggestion, doc refer or advance will be highly appreciate.

0

There are 0 answers