flask sqlalchemy column constraint for positive integer

Question

how can i define a column as a positive integer using flask sqlalchemy?

i am hoping the answer would look something like this:

class City(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    population = db.Column(db.Integer, positive=True)
    def __init__(self,population):
        self.population = population

however, this class definition will throw an error b/c sqlalchemy does not know about a 'positive' argument.

i could raise an exception if an object is instantiated with a negative value for the population. but i don't know how to ensure that the population remains positive after an update.

thanks for any help.

Answer 1

unfortunately, on the python side, sqlalchemy does its best to stay out of the way; there's no 'special sqlalchemy' way to express that the instance attribute must satisfy some constraint:

>>> class Foo(Base):
...     __tablename__ = 'foo'
...     id = Column(Integer, primary_key=True)
...     bar = Column(Integer)
...
>>> f = Foo()
>>> f.bar = "not a number!"
>>> f.bar
'not a number!'

If you tried to commit this object, sqlalchey would complain because it doesn't know how to render the supplied python value as SQL for the column type Integer.

If that's not what you're looking for, you just want to make sure that bad data doesn't reach the database, then you need a Check Constraint.

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    bar = Column(Integer)
    __table_args__ = (
        CheckConstraint(bar >= 0, name='check_bar_positive'),
        {})

Answer 2

I know this is old but for what it's worth, my approach was to use marshmallow (de/serialization and data validation library) to validate input data.

Create the schema to your model as such:

from marshmallow import validate, fields, Schema

... 

class CitySchema(Schema):
    population = fields.Integer(validate=validate.Range(min=0, max=<your max value>))

Then use your schema to serialize/deserialize the data when appropriate:

... 
city_data = {...} # your city's data (dict)
city_schema = CitySchema()
deserialized_city, validation_errors = city_schema.load(city_data) # validation done at deserialization
... 

The advantage of using a de/serialization library is that you can enforce all your data integrity rules in one place