Unique validator in WTForms with SQLAlchemy models

Question

I defined some WTForms forms in an application that uses SQLALchemy to manage database operations.

For example, a form for managing Categories:

class CategoryForm(Form):
    name = TextField(u'name', [validators.Required()])

And here's the corresponding SQLAlchemy model:

class Category(Base):
    __tablename__= 'category'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(255))

    def __repr__(self):
        return '<Category %i>'% self.id

    def __unicode__(self):
        return self.name

I would like to add a unique constraint on the form validation (not on the model itself).

Reading the WTForms documentation, I found a way to do it with a simple class:

class Unique(object):
    """ validator that checks field uniqueness """
    def __init__(self, model, field, message=None):
        self.model = model
        self.field = field
        if not message:
            message = u'this element already exists'
        self.message = message

    def __call__(self, form, field):         
        check = self.model.query.filter(self.field == field.data).first()
        if check:
            raise ValidationError(self.message)

Now I can add that validator to the CategoryForm like this:

name = TextField(u'name', [validators.Required(), Unique(Category, Category.name)])

This check works great when the user tries to add a category that already exists \o/ BUT it won't work when the user tries to update an existing category (without changing the name attribute).

When you want to update an existing category : you'll instantiate the form with the category attribute to edit:

def category_update(category_id):
    """ update the given category """
    category = Category.query.get(category_id)
    form = CategoryForm(request.form, category)

The main problem is I don't know how to access the existing category object in the validator which would let me exclude the edited object from the query.

Is there a way to do it? Thanks.

Antiphon

Answer 1

In the validation phase, you will have access to all the fields. So the trick here is to pass in the primary key into your edit form, e.g.

class CategoryEditForm(CategoryForm):
    id = IntegerField(widget=HiddenInput())

Then, in the Unique validator, change the if-condition to:

check = self.model.query.filter(self.field == field.data).first()
if 'id' in form:
    id = form.id.data
else:
    id = None
if check and (id is None or id != check.id):

Steinberg

It looks as if this is counting on the client (browser) to pass back the correct id value unchanged. Is that right? Or else, the server code should reset the id field in the form after the POST but before the validate? – Morocco

@Morocco that is indeed the case. In my case, I only use the id inclusion in the admin area and on the front-end, compare current_user.email != form.email.data which allows me to cover both examples. But +1 as it's a point people should definitely be aware of. – Sandhurst

Answer 2

Although this is not a direct answer I am adding it because this question is flirting with being an XY Problem. WTForms primary job is to validate that the content of a form submission. While a decent case could be made that verifying that a field's uniqueness could be considered the responsibility of the form validator, a better case could be made that this is the responsibility of the storage engine.

In cases where I have be presented with this problem I have treated uniqueness as an optimistic case, allowed it to pass form submission and fail on a database constraint. I then catch the failure and add the error to the form.

The advantages are several. First it greatly simplifies your WTForms code because you do not have to write complex validation schemes. Secondly, it could improve your application's performance. This is because you do not have to dispatch a SELECT before you attempt to INSERT effectively doubling your database traffic.

Dionysian

Excuse me, could you please provide with a short example of code, preferrably using Flask? Or, if you know where I can look at one, could you please provide a link? – Papule

Answer 3

The unique validator needs to use the new and the old data to compare first before checking if the data is unique.

class Unique(object):
...
def __call__(self, form, field):
    if field.object_data == field.data:
        return
    check = DBSession.query(model).filter(field == data).first()
    if check:
        raise ValidationError(self.message)

Additionally, you may want to squash nulls too. Depending on if your truly unique or unique but allow nulls.

I use WTForms 1.0.5 and SQLAlchemy 0.9.1.

Arthromere

This worked for me and is much simpler to implement than the above. – Influential

Answer 4

Declaration

from wtforms.validators import ValidationError

class Unique(object):

    def __init__(self, model=None, pk="id", get_session=None, message=None,ignoreif=None):
        self.pk = pk
        self.model = model
        self.message = message
        self.get_session = get_session
        self.ignoreif = ignoreif
        if not self.ignoreif:
            self.ignoreif = lambda field: not field.data

    @property
    def query(self):
        self._check_for_session(self.model)
        if self.get_session:
            return self.get_session().query(self.model)
        elif hasattr(self.model, 'query'):
            return getattr(self.model, 'query')
        else:
            raise Exception(
                'Validator requires either get_session or Flask-SQLAlchemy'
                ' styled query parameter'
            )

    def _check_for_session(self, model):
        if not hasattr(model, 'query') and not self.get_session:
            raise Exception('Could not obtain SQLAlchemy session.')

    def __call__(self, form, field):
        if self.ignoreif(field):
            return True

        query = self.query
        query = query.filter(getattr(self.model,field.id)== form[field.id].data)
        if form[self.pk].data:
            query = query.filter(getattr(self.model,self.pk)!=form[self.pk].data)
        obj = query.first()
        if obj:
            if self.message is None:
                self.message = field.gettext(u'Already exists.')
            raise ValidationError(self.message)

To use it

class ProductForm(Form):
    id = HiddenField()
    code = TextField("Code",validators=[DataRequired()],render_kw={"required": "required"})
    name = TextField("Name",validators=[DataRequired()],render_kw={"required": "required"})
    barcode = TextField("Barcode",
                        validators=[Unique(model= Product, get_session=lambda : db)],
                        render_kw={})

Aili

Answer 5

Looks like what you are looking for can easily be achieved with ModelForm which is built to handle forms that are strongly coupled with models (the category model in your case).

To use it:

...
from wtforms_components import Unique
from wtforms_alchemy import ModelForm

class CategoryForm(ModelForm):
    name = TextField(u'name', [validators.Required(), Unique(Category, Category.name)])

It will verify unique values while considering the current value in the model. You can use the original Unique validator with it.

Experience

Answer 6

This worked for me, simple and easy: make sure that every time when a new row is created in the database, it must have unique name in column_name_in_db, otherwise it will not work.

class SomeForm(FlaskForm):
    id = IntegerField(widget=HiddenInput())
    fieldname = StringField('Field name', validators=[DataRequired()])
    ...
    
    def validate_fieldname(self, fieldname):
        names_in_db = dict(Model.query.with_entities(Model.id, 
        Model.column_name_in_db).filter_by(some_filters_if_needed).all())
        if fieldname.data in names_in_db.values() and names_in_db[int(self.id)] != fieldname.data:
            raise ValidationError('Name must be unique')

Biebel