Store & Retrieve Image In Database With Python Flask

Welcome to a tutorial on how to save and load images in a database. So you need to keep an image in a database for some reason? Well, it is not so straightforward, but possible. Read on for the example!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Click here to download

The example code is released under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

VIDEO TUTORIAL

 

PART 1) THE DATABASE

1A) DATABASE TABLE

CREATE TABLE `images` (
  `file_name` TEXT PRIMARY KEY,
  `file_mime` TEXT NOT NULL,
  `file_data` BLOB NOT NULL
);

Let us first start with the database, and 3 fields are all we need.

• file_name The image file name, primary key.
• file_mime The MIME type of the image.
• file_data Raw image data.

 

1B) CREATE DATABASE

import sqlite3
conn = sqlite3.connect("images.db")
with open("S1_images.sql") as f:
  conn.executescript(f.read())
conn.commit()
conn.close()
print("Database created!")

This short snippet should be self-explanatory. Read the above SQL file and create the actual database file itself.

 

 

PART 2) DATABASE LIBRARY

# (A) SQLITE & DATABASE FILE
import sqlite3
DB_FILE = "images.db"
 
# (B) SAVE INTO DATABASE
def save(name, mime, data):
  conn = sqlite3.connect(DB_FILE)
  cursor = conn.cursor()
  cursor.execute(
    "REPLACE INTO `images` (`file_name`, `file_mime`, `file_data`) VALUES (?,?,?)",
    (name, mime, data)
  )
  conn.commit()
  conn.close()
 
# (C) LOAD FROM DATABASE
def load(name):
  conn = sqlite3.connect(DB_FILE)
  cursor = conn.cursor()
  cursor.execute("SELECT * FROM `images` WHERE `file_name`=?", (name,))
  return cursor.fetchone()

Next, we have a “library” to work with the database… Which is just 2 functions.

• save() Save an image file into the database.
• load() Load the given file from the database.

 

 

PART 3) FLASK SERVER

3A) HTTP SERVER

# (A) INIT
# (A1) LOAD MODULES
from flask import Flask, render_template, request, make_response
from werkzeug.utils import secure_filename
import S2_db as dblib
 
# (A2) SETTINGS + INIT
HOST_NAME = "localhost"
HOST_PORT = 80
app = Flask(__name__)
# app.debug = True
 
# (B) UPLOAD ENDPOINTS
# (B1) FILE UPLOAD PAGE
@app.route("/", methods=["GET", "POST"])
def index():
  return render_template("S3_upload.html")
 
# (B2) SAVE UPLOADED FILE
@app.route("/upload", methods = ["POST"])
def dbsave():
  up = request.files["upload"]
  dblib.save(
    secure_filename(up.filename),
    up.content_type,
    up.read()
  )
  return "OK"
 
# (C) DOWNLOAD ENDPOINTS
# (C1) FORCE DOWNLOAD
@app.route("/download", methods=["GET"])
def dbload():
  dbfile = request.args.get("f")
  dbfile = None if dbfile is None else dblib.load(dbfile)
  if dbfile is None:
    return make_response("Not Found", 404)
  else:
    response = make_response(dbfile[2])
    response.headers["Content-type"] = dbfile[1]
    response.headers["Content-Transfer-Encoding"] = "Binary"
    response.headers["Content-Disposition"] = "attachment; filename=\"%s\"" % dbfile[0]
    return response
 
# (C2) SHOW IMAGE FROM DATABASE
@app.route("/show", methods=["GET"])
def dbshow():
  dbfile = request.args.get("f")
  dbfile = None if dbfile is None else dblib.load(dbfile)
  if dbfile is None:
    return make_response("Not Found", 404)
  else:
    response = make_response(dbfile[2])
    response.headers["Content-type"] = dbfile[1]
    return response
 
# (D) START
if __name__ == "__main__":
  app.run(HOST_NAME, HOST_PORT)

With the database and library, we can now build the HTTP server. This may look complicated to some beginners, but keep calm and study slowly:

1. Load the required modules, the library, and some Flask server settings.
2. The image upload endpoints.
   • / An image upload form.
   • /upload The form will be submitted to this endpoint, save the image into the database.
3. The image download endpoints.
   • /download Load an image from the database and force download.
   • /show Load an image from the database and display it.
4. Start the Flask HTTP server. Captain Obvious at your service.

 

 

3B) UPLOAD PAGE

<form method="post" action="/upload" target="_blank" enctype="multipart/form-data">
  <input type="file" name="upload" required accept="image/*">
  <input type="submit" name="submit" value="Upload File">
</form>

Finally, the upload page itself. Just a “regular” HTML file upload form, restricted to images only.

 

 

IT WORKS, BUT IT’S NOT GREAT…

Yes, this example works, but a quick reminder that RDB is generally not great with large files. For example, it is difficult or impossible to do file streaming. We will have to load the entire file into the memory, and then process it.

Not that it is wrong, keeping images in the database can add another layer of security. But then again, we can also encrypt uploaded files, and keep them in private folders that are not publically accessible – You decide if “image in the database is a good idea”.

 

 

LINKS & REFERENCES

• Upload File Into Database With Python Flask – Code Boxx
• Python Flask
• SQLITE3