CODEWITHSUNDEEP
pythonflasksqlalchemy
mrQWERTY
mrQWERTY

Reputation: 4149

sqlalchemy, using check constraints

I just started on sqlalchemy and I want to put a check constraint on one of my columns. I have a column called startTime and endTime and I want to ensure that endTime > startTime.

from sqlalchemy import Column, Integer, String, ForeignKey, Date
import models.Base

class Session(Base):
    __tablename__ = 'sessions'

    sid = Column(Integer, primary_key=True)
    uid = Column(Integer, ForeignKey('users.uid'), nullable=False)
    startTime= Column(Date, nullable=False)

    #probably won't work
    endTime = Column(Date, CheckConstraint('endTime > startTime'), nullable=False)

Upvotes: 26

Views: 31769

Answers (3)

viraj892
viraj892

Reputation: 351

Apart from the fact that MySQL doesn't support check constraints, I think the issue is that you are trying to refer to multiple columns in a column level check constraint.

Assuming that you used another database, you need to define the constraint at the table level, something like this:

from sqlalchemy import Column, Integer, String, ForeignKey, Date, CheckConstraint
import models.Base

class Session(Base):
    __tablename__ = 'sessions'
    __table_args__ = (
        CheckConstraint('endTime > startTime'),
    )

    sid = Column(Integer, primary_key=True)
    uid = Column(Integer, ForeignKey('users.uid'), nullable=False)
    startTime= Column(Date, nullable=False)
    
    endTime = Column(Date, nullable=False)

Upvotes: 24

MOCKBA
MOCKBA

Reputation: 1790

It's a valid syntax, but in MySQL (I assume you're using MySQL?) this will be ignored. From SQLAlchemy docs:

Check constraints can be named or unnamed and can be created at the Column or Table level, using the CheckConstraint construct. The text of the check constraint is passed directly through to the database, so there is limited “database independent” behavior. Column level check constraints generally should only refer to the column to which they are placed, while table level constraints can refer to any columns in the table. Note that some databases do not actively support check constraints such as MySQL.*

You can create a trigger of course, but then you'd put your biz logic to the DB layer. I'd write an app-level constructor check instead.

Upvotes: 14

deesolie
deesolie

Reputation: 1062

An alternative way to create a check constraint is to use the event.listen event API from SQLAlchemy.

In my use case, I wanted to create a column check on my model where the column couldn't be a negative number.

event.listen(target, identifier, func)

The target in this case is my model ("Post") and my column ("upvotes"). The identifier in this case is "set". The function in this case is a static method created within my model. So it would look like this:

event.listen(Post.upvotes, "set", Post.before_set_upvotes)

My function is as follows:

@staticmethod
def before_set_upvotes(target, value, oldvalue, initiator):
    if value < 0:
        raise ValueError('Upvotes cannot be a negative value')

The target argument is "Post.upvotes" and value is the value we are setting the column attribute to be. NOTE: The function that is used in event.listen requires 4 arguments, I am unsure why so if someone has a reason please comment. Without 4 arguments (and just having target and value as args), I get the error TypeError: before_set_upvotes() takes 2 positional arguments but 4 were given.

Now you can test this by running a function that tried to set and commit a negative number to that column. A value error will be raised, thus creating the constraint we need.

Upvotes: 1

Related Questions