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:

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

van · Accepted Answer · 2015-09-03 06:44:11Z

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

@YaakovBressler: sure, just extract the code you want into a base class, or a mixin class.

czgu · Accepted Answer · 2021-11-09 02:02:46Z

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

C Halsema · Accepted Answer · 2023-10-12 02:59:08Z

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

Collectives™ on Stack Overflow

Truncating too long varchar when inserting to MySQL via SQLAlchemy

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related