Serve image stored in SQLAlchemy LargeBinary column

Question

I want to upload a file and store it in the database. I created a LargeBinary column.

logo = db.Column(db.LargeBinary)

I read the uploaded file and store it in the database.

files = request.files.getlist('file')

if files:
    event.logo = files[0].file.read()

Is this the proper way to store an image as binary in the database? How do I convert the binary data into an image again to display it?

Answer 1

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 }}"/>