SQLAlchemy: What is the best way to validate a model before inserting or updating

Question

I'm trying to validate a SQLAlchemy model before it is inserted or updated, e.g

class MyModel(db.Model):
    foo = db.Column(db.String(255))
    bar = db.Column(db.String(255))

I've tried a few approaches, but none seem to work. One possibility was to listen to before_insert and before_update events, e.g

@event.listens_for(MyModel, 'before_insert')
@event.listens_for(MyModel, 'before_update')
def validate_my_model(mapper, connection, model):
    if not is_valid(model):
        raise Exception("the model isn't valid")

This works okay, but in tests I get this error unless I roll back the session.

This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback()

I could call session.rollback() in the tests, but I that seems incorrect, since the test is just issuing PUT/POST requests and shouldn't really know anything about the session or any SQLAlchemy internals.

Another option was to listen to init events, instead of before_insert and before_update, but that doesn't handle the update case.

I also tried using the @validates, e.g

@validates('foo', 'bar')
def validate(self, key, val):
    ...

However, the validation relies on both properties, while the validate method is called individually for each property. This means that if I try to set both foo and bar on the model, it attempts to validate the intermediate state where one property has been set but the other hasn't.

Answer 1

try flask-marshmallow and marshmallow_sqlalchemy, it is based on marshmallow validation package. it allows you to validate based on your database model. create a schema file

import MyModel
import db
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema

class MyModelSchema(SQLAlchemyAutoSchema):
    class Meta:
        sqla_session = Session
        load_instance = True
        model = MyModel

adding model inside the Meta class will help to validate based on the model and load_instance will let us load the object inside the API as a model instance

from schemas import MyModelSchema

model_schema = MyModelSchema()

# API post route
def post(self):
    model_json = request.get_json()
    model = model_schema.load(model_json)

    db.session(model)
    db.session.commit() 

    return {"message": "model created"}, 201

last to user marshmallow inside flask app and to return a validation error message if the body sent not validated, inside application root file add

from flask_marshmallow import Marshmallow
from marshmallow import ValidationError

# marshmallow config
ma = Marshmallow(app)

# marshamallow global exception handler
@app.errorhandler(ValidationError)
def handle_marshmallow_validation(err):
    return jsonify(err.messages), 400

I hope you find this useful.