CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Jiri
Jiri

Reputation: 16625

Truncating too long varchar when inserting to MySQL via SQLAlchemy

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?

Upvotes: 6

Views: 4947

Answers (3)

C Halsema
C Halsema

Reputation: 61

Create a TypeDecorator in your base model for an even more generic answer. Then, use this TypeDecorator instead of string.

In base.py

 from sqlalchemy.types import TypeDecorator

  class TruncateString(TypeDecorator):
    """trim spaces from the string"""

    impl = db.String
    cache_ok = True

    def process_bind_param(self, value, dialect):
        # In case you have nullable string fields and pass None
        if value and len(value) > self.impl.length:
            value = value[:self.impl.length]
        return value if value else value

    def copy(self, **kw):
        return TruncateString(self.impl.length)

Now, in your yourModel.py, use this type.

    from flask_app.models.base import *

class CampaignScript(BaseModel):
    __tablename__ = 'campaign_script'

    name = Column(TruncateString(200))
    subject = Column(TruncateString(200))
    comments = Column(TruncateString(200))
    language = Column(TruncateString(200))
    script = Column(NVARCHAR(None))
    user_id = Column(TruncateString)
    account_id = Column(Integer)
    comm_type = Column(TruncateString(45))
    short_name = Column(TruncateString(75))
    released_locked = Column(Boolean())

good luck

Upvotes: 0

czgu
czgu

Reputation: 31

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))

Upvotes: 3

van
van

Reputation: 77082

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

Upvotes: 13

Related Questions