Explain the validators of SQLAlchemy.

validator.py

#!/usr/bin/env python
import datetime
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.schema import CheckConstraint
from sqlalchemy.orm import validates
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.exc import IntegrityError

Base = declarative_base()

class Event(Base):
    __tablename__ = 'events'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    started_at = Column(DateTime)
    stopped_at = Column(DateTime)

    # Solution 1. Use a Check Constraint on your object
    __table_args__ = (
        CheckConstraint('started_at <= stopped_at'),
    )

    # Solution 2. Use a logical constraing
    # This constraint will check the value of the two datetime fields.
    # At the first call, the started_at field will be filled by the value of
    # field, but not the stopped_at field. This one will be None.

    # At the second call, the key will be for the 'stopped_at' field and the
    # value will contain the value of this field.

    # for this reason, I check the value of the 'key' parameter and I check if
    # the self.started_at field is an instance of 'datetime.datetime'.
    # Then, I can check the values of these two fields and raise an exception if
    # there is an error.
    @validates('started_at', 'stopped_at')
    def validate_dates(self, key, field):
        print "started, stopped: %r %r" %( self.started_at, self.stopped_at)
        print "key, field:  %r %r" % (key, field,)
        if key == 'stopped_at' and isinstance(self.started_at, datetime.datetime):
            if self.started_at > field:
                raise AssertionError("The stopped_at field must be "\
                                     "greater-or-equal than the started_at field")
        return field

engine = create_engine('sqlite:///:memory:', echo=True)

Base.metadata.create_all(engine)

event = Event(name='Event1',
              started_at=datetime.datetime.now(),
              stopped_at=datetime.datetime.now(),
              )

Session = sessionmaker(bind=engine)

try:
    session = Session()
    session.add(event)
    session.commit()
except IntegrityError as ex:
    print ex

For validation, the order of when stopped_at and started_at is non-deterministic. You need to add another if check. So:

@validates('started_at', 'stopped_at')
    def validate_dates(self, key, field):
        print "started, stopped: %r %r" %( self.started_at, self.stopped_at)
        print "key, field:  %r %r" % (key, field,)
        if key == 'stopped_at' and isinstance(self.started_at, datetime.datetime):
            if self.started_at > field:
                raise AssertionError("The stopped_at field must be "\
                                     "greater-or-equal than the started_at field")
        elif key == 'started_at' and isinstance(self.stopped_at, datetime.datetime):
            if self.stopped_at < field:
                raise AssertionError("The stopped_at field must be "\
                                     "greater-or-equal than the started_at field")
        return field

Thank you!

i'm trying to validate but i can't figure out why i'm getting an integrity error

class Meals(db.Model):

__tablename__ = 'meals'
"""
This class represents the meals table
"""
meal_id = db.Column(db.Integer, primary_key=True)
meal_name = db.Column(db.String(50), unique=True, nullable=False)
description = db.Column(db.String(256), nullable=False)
course = db.Column(db.String(20), nullable=False)
price = db.Column(db.String(20), nullable=False)
created_by = db.Column(db.Integer, db.ForeignKey('users.user_id'))

def __init__(self, meal_name, description, course, price, created_by):
    """
    Initialize the meal with meal_name, description , course and price
    """
    self.meal_name = meal_name
    self.description = description
    self.course = course
    self.price = price
    self.created_by = created_by

@validates('meal_name')
def validate_meal_name(self, key, meal_name):
    if not meal_name:
        raise AssertionError('Meal name cannot be blank')
    if Meals.query.filter(Meals.meal_name == meal_name).first():
        raise AssertionError('{} already exits'.format(meal_name))
    if len(meal_name) < 2 or len(meal_name) > 20:
        raise AssertionError('Meal name must be between 2 and 20 characters long')

@validates('description')
def validate_description(self, key, description):
    if not description:
        raise AssertionError('Meal description cannot be blank')
    if len(description) < 2 or len(description) > 256:
        raise AssertionError('Meal description must be between 2 and 256 characters long')

@validates('course')
def validate_course(self, key, course):
    if not course:
        raise AssertionError('Meal course cannot be blank')
    if len(course) < 2 or len(course) > 20:
        raise AssertionError('Meal course must be between 2 and 256 characters long')

@validates('price')
def validate_price(self, key, price):
    if not price:
        raise AssertionError('Meal price cannot be blank')
    if price[0] != '$':
        raise AssertionError('Enter price in dollars')
    if  len(price) > 10:
        raise AssertionError('Enter the correct price')

    
def save(self):
    """Save meal object to database"""
    db.session.add(self)
    db.session.commit()

@Yiga-fred
validate... functions need to return something (field). Otherwise they end up empty, after successful validation.

I have a doubt, there is no need to validate if field is instance of datetime?

    @validates('started_at', 'stopped_at')
    def validate_dates(self, key, field):
        print "started, stopped: %r %r" %( self.started_at, self.stopped_at)
        print "key, field:  %r %r" % (key, field,)
        if not isinstance(field, datetime.datetime):
            raise AssertionError("field must be instance of datetime.datetime")
        if key == 'stopped_at' and self.started_at is not None and self.started_at > field or 
           key == 'started_at' and self.stopped_at is not None and self.stopped_at < field:
            raise AssertionError("The stopped_at field must be "\
                                     "greater-or-equal than the started_at field")
        return field

the code above is correct?

Hi,

If you have the right solution, I can update this gist. Just propose a solution and I will update it asap.

this is what I have in my case

class Sticky(Base):
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid4, server_default=text("uuid_generate_v4()"))
    parent_id = Column(UUID(as_uuid=True), ForeignKey('sticky.id', ondelete='CASCADE'), default=None)

    @validates('id', 'parent_id')
    def validate_self_parenting(self, key, field):
        if field is not None and (
                key == 'id' and field == self.parent_id or
                key == 'parent_id' and field == self.id
        ):
            raise ValidationException('Sticky', 'parent_id', "parent_id can't be equal to the id")
        return field