Truncating too long varchar when inserting to MySQL via SQLAlchemy

Question

I am inserting data to MySQL via SQLAlchemy models. Recently, this app is running against MySQL configured with STRICT_TRANS_TABLES and app fails occasionally because of Data too long for column error.

I know that I can disable strict sql_mode for my session (like here MySQL too long varchar truncation/error setting),

but I was curious if SQLAlchemy can enforce max String() length for column data. Documentation says, the String() length is for CREATE TABLE only. My question:

1. Is it possible to enforce max length (truncate too long strings) in SQLAlchemy?
2. Can I set it for individual columns or for all columns in all tables/database only?

Answer 1

If you would like to enfoce max length by automatically truncating it on the python/sqlalchemy side, I think that using Simple Validators is the easiest way to achieve this:

class MyTable(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    code = Column(String(4))
    name = Column(String(10))

    @validates('code', 'name')
    def validate_code(self, key, value):
        max_len = getattr(self.__class__, key).prop.columns[0].type.length
        if value and len(value) > max_len:
            return value[:max_len]
        return value

Answer 2

Here is a generic solution based on van's answer:

from sqlalchemy.orm import validates

def TruncateString(*fields):
    class TruncateStringMixin:
        @validates(*fields)
        def validate_string_field_length(self, key, value):
            max_len = getattr(self.__class__, key).prop.columns[0].type.length
            if value and len(value) > max_len:
                return value[:max_len]
            return value

    return TruncateStringMixin

Now you can use it with

class MyTable(Base, TruncateString('code', 'name')):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    code = Column(String(4))
    name = Column(String(10))