Data too long for column 'img' where img is a LargeBinary

Question

I have the following model:

from marshmallow import Schema, fields

from server import db, ma, app
from sqlalchemy.ext.hybrid import hybrid_property
from .analysis import AnalysisSchema



class Category(db.Model):
    __tablename__ = 'Categories'
    __table_args__ = {'extend_existing': True}
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), index=True, unique=True)
    description = db.Column(db.Text, nullable=False)
    img = db.Column(db.LargeBinary, nullable=False)
    img_mimetype = db.Column(db.Text, nullable=False)
    img_name = db.Column(db.Text, nullable=False)
    analysis = db.relationship("Analysis", back_populates="category")

    @property
    def img_url(self):
        return "/categories/" + str(self.id)



class CategorySchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        # model = Category
        fields = ("id", "name", "description", "img", "img_url", "analysis")
    analysis = fields.Nested(AnalysisSchema, many=True)


class CreateCategorySchema(Schema):
    name = fields.Str(required=True)
    description = fields.Str(required=True)


class UpdateCategorySchema(Schema):
    name = fields.Str(required=True)
    description = fields.Str(required=False)
    img_name = fields.Str(required=False)
    img_mimetype = fields.Str(required=False)

I create a new category with the following code:

from flask import jsonify

from server import db, app
from ..models.category import (
    Category,
    CategorySchema,
    CreateCategorySchema,
    UpdateCategorySchema
)



def create_category(data):
    app.logger.info('Create category invoked')
    create_category_schema = CreateCategorySchema()
    # errors = create_category_schema.validate(data)
    errors = None
    if errors:
        app.logger.info('Found errors %s', errors)
        return jsonify(errors), 400
    name = Category.query.filter_by(name=data['name']).first()
    app.logger.info('Name is %s', name)
    if not name:
        app.logger.info('Name not present')
        category = Category(
            name=data['name'],
            description=data['description'],
            img=data['img'],
            img_name=data['img_name'],
            img_mimetype=data['img_mimetype']
        )
        _save_category(category)
        category_schema = CategorySchema()
        response = category_schema.dump(category), 201
        #response = jsonify('Category created'), 200
    else:
        response = jsonify('Category already exists'), 409
    return response

def all_categories():
    category_schema = CategorySchema(many=True, exclude=['img'])
    categories = Category.query.all()
    response = category_schema.dump(categories)
    return jsonify(response), 20


def get_category(id):
    return Category.query.filter_by(id=id).first()

def _save_category(category):
    db.session.add(category)
    db.session.commit()

If I try to invoke create_category I got the following error:

sqlalchemy.exc.DataError: (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'img' at row 1")
[SQL: INSERT INTO `Categories` (name, description, img, img_mimetype, img_name) VALUES (%s, %s, %s, %s, %s)]
[parameters: ('test10', 'blah blah blah', b'\xff\xd8\xff\xe0\x00\x10JFIF\x00\x01\x01\x00\x00\x01\x00\x01\x00\x00\xff\xdb\x00\x84\x00\x06\x06\x06\x06\x07\x06\x07\x08\x08\x07\n\x0b\n\x0b\n\x0f\x ... (371140 characters truncated) ... xe4\xd3\xc0\xe3\xcag\xaf\x17\xff\x00\xcc?\xfdD\xfe\x85\xc3\x91\x06\x07\x017#3\x93:\x7fE\xfe\xb3\xff\x00\xe9_\xff\x00\x04r-i\x86\xb7\xd6|#\xbf\xff\xd9', 'image/jpeg', 'WhatsApp_Image_2021-06-04_at_11.14.49_AM.jpeg')]
(Background on this error at: http://sqlalche.me/e/14/9h9h)

I don't understand why is trying to insert an image as a String if it is explicitly defined as a LargeBinary.

`b'\xff\xd8...'` isn't a `str` it's a `bytes`. Storing images directly in the database is generally discouraged, rather storing the image path in the database is preferred. https://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay — Michael Ruth, Jun 22 '21 at 23:43
The issue is that the solution was working and now it is not. I understand the best practice but sqlAlchemy provide the ability to store blob on the db. — Kerby82, Jun 22 '21 at 23:51
How large is WhatsApp_Image_2021-06-04_at_11.14.49_AM.jpeg, and what is the type of the `Categories.img` column in your database? — Michael Ruth, Jun 23 '21 at 00:04
Categories.img is a blob. The image is less than 1MB. I already uploaded the same image. Then I added a relationship to the model and I don't know why I'm getting this error now. — Kerby82, Jun 23 '21 at 00:08
For MySQL, the BLOB type has a max length of 64kB. Try explicitly setting the length of `LargeBinary` as [documented](https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.LargeBinary.__init__), 16MB (MEDIUMBLOB) or 4GB (LONGBLOB). — Michael Ruth, Jun 23 '21 at 00:15

Kerby82 · Accepted Answer · 2021-07-07T19:04:32.673

0

The solution can be found in the question comments. I had to increase the size of the Blob using the length attribute of SQLAlchemy.

edited Jul 07 '21 at 19:04

answered Jun 24 '21 at 20:04

Kerby82

4,934
14
48
74

Data too long for column 'img' where img is a LargeBinary

1 Answers1