SQLAlchemy how to insert base64 string in a column

Question

I've a problem when try to insert a base64 string in the database of the server. The data is received correctly from the client.

This is the table:

from app import db

class Example(db.Model):
    __tablename__ = 'example'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    image = db.Column(db.LargeBinary)
    description = db.Column(db.String(120))

And when I receive the data I try to insert by this way.

example.image = request.json['image']

But launch this error:

sqlalchemy.exc.StatementError: (builtins.TypeError) memoryview: a bytes-like object is required, not 'str' [SQL: 'INSERT INTO example (image, description) VALUES (?, ?)'] [parameters: [{'image':''iVBORw0KGgoAAAANSUhEUgAAASwAAACWCAYAAABkW7XSAAAgAElEQVR4Xkyd65Ikx5GdI+9ZWVXdMwOQMtPF9EM/dDEtd0GQS4LgLk2m938KmWklApjprlveM/V9XsM1cUURmOmuyozwcD9+/LhH9 ... (131066 characters truncated) ... U+bq1fYzK8CjL+Htr9jmjGZaL/sAccUiPf8/8906GpLwkQngOCuh5nohrdndHTRHii8Q55Rlk2N1B1ZGCCmSIct5gm/IiAAAAEklEQVSsvzbg3YTp3ut72d+/AvI8T5jSrxmzAAAAAElFTkSuQmCC'','description':'Example description'}]]

Any solution for this?

Don't save files in a database. http://stackoverflow.com/a/38829952/267540 — e4c5, Dec 14 '16 at 12:39

score 1 · Answer 1 · answered Mar 18 '21 at 09:28

1

You are using the wrong Column type here. Because Base64 is actually ASCII encoded variable length string, you should use TEXT instead of LargeBinary. This should solve your problem. Happy coding!

answered Mar 18 '21 at 09:28

Jinghui Niu

990
11
28

score 0 · Answer 2 · answered Mar 21 '18 at 10:45

Not storing files in a database avoids this issue. However, to answer the question: I think is that request.json['image'] returns a str that needs to be encoded to bytes first like so:

request.json['image'].encode('utf-8')

given the encoding is of course 'utf-8'

SQLAlchemy how to insert base64 string in a column

2 Answers2