CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Auyer
Auyer

Reputation: 2971

Use Regex in a 'CheckConstraint' from SQLAlchemy

Considering the example below, how can I make the constraint work according to the given regex?

In this case, I am using SQLAlchemy inside a Flask application.

class user(db.Model):
    iduser = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(45), unique=True)
    CheckConstraint("REGEXP_LIKE(email,'^[a-zA-Z][a-zA-Z0-9_\.\-]+@([a-zA-Z0-9-]{2,}\.)+([a-zA-Z]{2,4}|[a-zA-Z]{2}\.[a-zA-Z]{2})$')", name='emailcheck')

(I am not 100% sure about the syntax in the last line)

Upvotes: 7

Views: 2417

Answers (1)

univerio
univerio

Reputation: 20548

CHECK constraints need to be in the table itself, using __table_args__:

class user(db.Model):
    ...
    __table_args__ = (CheckConstraint("regexp_like(email, ...)", name=...),)

You can also put it outside of the class, but SQLAlchemy needs to know what table it's for, so you'll need to write the constraint as an expression instead of a string:

class user(db.Model):
    ...

CheckConstraint(func.regexp_like(user.email, ...), name=...)

Upvotes: 6

Related Questions