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.

Papule

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

Papule

I don't think there is a reason to check isinstance(value, str) you can just value == '' and if value is a non-string type it will still be False – Topeka

Answer 2

I ran into a similar issue and created a decorator, that could then be used on a setter for the field. This allows easy reuse but does require using a hybrid property and setter for the field.

# helpers.py
import functools  # stdlib

def none_if_empty_str(func):
    @functools.wraps(func)
    def wrapped_function(*args, **kwargs):
        """convert empty string to `None`
        """
        for arg in args:
            if arg == '':
                arg = None
        for k,v in kwargs.items():
            if v == '':
                kwargs[k] = None
        return func(*args, **kwargs)
    return wrapped_function

# models.py
from helpers import none_if_empty_str

_column_name_ = db.Column(db.String(120))

@hybrid_property
def column_name(self):
    return self._column_name_

@column_name.setter
@none_if_empty_str
def column_name(self, name):
    self._column_name_ = name

Novak