Serve Image Stored in SQLalchemy Largebinary Column

Serve image stored in SQLAlchemy LargeBinary column

If you absolutely need to store the image in the database, then yes, this is correct. Typically, files are stored in the filesystem and the path is stored in the database. This is the better solution because the web server typically has an efficient method of serving files from the filesystem, as opposed to the application sending large blobs of data dynamically.

---

To serve the image, write a view that gets the image data and sends it as a response.

@app.route('/event/<int:id>/logo')
def event_logo(id):
    event = Event.query.get_or_404(id)
    return app.response_class(event.logo, mimetype='application/octet-stream')

<img src="{{ url_for('event_logo', id=event.id }}"/>

Preferably serve it using the correct mimetype rather than application/octet-stream.

---

You could also embed the image data directly in the html using a data uri. This is sub-optimal, because data uris are sent every time the page is rendered, while an image file can be cached by the client.

from base64 import b64encode

@app.route('/event/<int:id>/logo')
def event_logo(id):
    event = Event.query.get_or_404(id)
    image = b64encode(event.logo)
    return render_template('event.html', event=event, logo=image)

<p>{{ obj.x }}<br/>
{{ obj.y }}</p>
<img src="data:;base64,{{ logo }}"/>

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

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

sqlalchemy store media in the database

I think it is better to store the image on the filesystem and store the path in the database.

database storage is usually more expensive than file system storage

A Great article here on which type to use

Which SQLAlchemy column type should be used for binary data?

When storing binary data, use the LargeBinary type. Despite its name, it is appropriate for any sized binary data.

data = db.Column(db.LargeBinary)

Read the data from the uploaded file in your Flask view.

audio.data = request.files['data'].read()

Rather than storing the data in the database, it's typically better to store files in the filesystem, and store the path to the file in the database.

Since you're presumably serving these files, not just storing them, it is much more efficient to serve the files from the filesystem. See this answer for more discussion on serving data from the database.

---

Related Topics