Geek Answers Handbook

Sqlalchemy bulb column constraint for positive integer

How can I define a column as a positive integer using sqlalchemy in a bulb?

I hope the answer will 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 cause an error. b / c sqlalchemy is unaware of the "positive" argument.

I could throw an exception if the object was created with a negative value for the aggregate. but I don’t know how to ensure that the population remains positive after the update.

Thanks for any help.

+4
source share
2 answers

Unfortunately, on the python side, sqlalchemy does its best to stay out of the way; there is no special sqlalchemy form to express that an instance attribute must satisfy some restriction:

 >>> 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 will complain because it does not know how to display the provided python value as SQL for the Integer column type.

If this is not what you are looking for, you just want to make sure that bad data does not reach the database, then you need 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'), {})
+12
source

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

Create a schema for 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, if necessary:

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

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

+1
source

More articles:


All Articles