SQLAlchemy--can I map an empty string to null in the DDL? I want a nullable integer column to translate '' to NULL on insert or update

Question

I have a SQLAlchemy model with an integer column being populated from an HTML form (I'm using Flask and WTForms-alchemy and I'm trying to avoid writing custom code in routes). If the user does not enter a value for this integer on the form, the code that populates objects from the form ends up trying to put an empty string in for that column and MySQL complains that this is not an integer value. To help people searching: the error I got started with Incorrect integer value: '' for column ....

I don't want to use the sql_mode='' hack, which people suggest in order to put MySQL back into the old behavior of making a guess whenever you give it incorrect data, because I can't control the MySQL server this will eventually be used for.

What I want is something like the default column specification, except that instead of specifying a default for when nothing is passed in, I want to intercept an attempt to put an empty string in and replace it with None which I think will get translated to a null as it goes to the database.

Is there any way to do this in the model definition? I realize that it may incur a performance hit, but throughput is not a big deal in this application.

Answer 1

I found a way. The validates decorator can change a value on the way in. Here's how I did it:

from sqlalchemy.orm import validates

class Task(Base):
    __tablename__ = 'task'

    id = Column(INTEGER(11), primary_key=True)

    time_per_unit = Column(INTEGER(11))


    @validates('time_per_unit')
    def empty_string_to_null(self, key, value):
        if isinstance(value,str) and value == '':
            return None
        else:
            return value