Sqlalchemy error handling and custom validations

Question

I've read several questions about this and I don't know yet what's the best solution for this problem. Suppose we have a Model like this:

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True)
    fullname = Column(String)
    password = Column(String, nullable=False) 

In my controller, I want to create a user, while validating parameters and handling sqlalchemy errors to reply to this request.

validation checks:

1. email uniqeness
2. password can't be null
3. password must be at least 6 characters.

for validation number 3, we can use validates decoraor. for first 2 validation, we can add and commit this user and somehow parse sqlalchemy.exc.SQLAlchemyError error which is raised.

But I really don't like this way. because it's so dirty and not scalable. some validtions like uniqeness needs to query to db. but checking if a value is null, doesn't need to query to db and parse sqlAlchemyError.

What's the best way to handle various types of validation?

What's the best way to parse sqlAlchemyError messages and return proper HTTP status codes to clients?

Answer 1

Depending on what you are planning to do with your application you could use the excellent validation capabilities of wtforms:

from flask_wtf import Form
from wtforms import StringField, PasswordField, SubmitField
from wtforms.fields.html5 import EmailField
from wtforms.validators import Length, Required, Email, EqualTo

class FooForm(Form):
    email = EmailField('Email', validators=[Required(),Email()])
    fullname = StringField('Name', validators=[Required(), Length(64)])
    password = PasswordField('Password', validators=[Required(), EqualTo('password2', message='Passwords must match.'), Length(8)])
    password2 = PasswordField('Password (confirm)', validators=[Required(),Length(8)])
    submit = SubmitField('Submit')

That takes care of your userinput validation - if something doesn't fit the requirements it will catch the errors before any sort of request to the backand is made. For the email uniquenes, i would suggest wrapping the sqla transaction in "try:...except:" to catch the error and flash a respective message to the user. Or you could write your own wtforms validator as described here. You could, for example, shoot an ajax request to the backend and check whether the emailaddress in question is already taken.