Python Flask Upload File Into Database (Simple Example)

Welcome to a tutorial on how to upload and save a file into a database in Python Flask. So you want to “secure” a file in a database, or have some limitations on the server file system? No problem, it is totally possible to save files in a database – Read on for a simple example!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it 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

 

 

 

QUICK SETUP

• Create a virtual environment virtualenv venv and activate it – venv\Scripts\activate (Windows) venv/bin/activate (Linux/Mac)
• Install required libraries – pip install flask werkzeug
• For those who are new, the default Flask folders are –
  • static Public files (JS/CSS/images/videos/audio)
  • templates HTML pages

 

PART 1) THE DATABASE

1A) THE SQL

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

To keep things simple, we will be using SQLite. All we need is a table with a BLOB column to store the “raw file data”. Yes, this will pretty much work for any RDB that supports the storage of binary data.

 

 

1B) CREATE SQL DATABASE

# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error
 
# (B) DATABASE + SQL FILE
DBFILE = "demo.db"
SQLFILE = "1a-database.sql"
 
# (C) DELETE OLD DATABASE IF EXIST
if os.path.exists(DBFILE):
  os.remove(DBFILE)
 
# (D) IMPORT SQL
conn = sqlite3.connect(DBFILE)
with open(SQLFILE) as f:
  conn.executescript(f.read())
conn.commit()
conn.close()
print("Database created!")

Next, run this script to create the actual database file itself.

 

PART 2) HTML FILE UPLOAD FORM

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

Now that the database is in place, we will create an HTML page to accept file uploads… There’s nothing “special” here. Just a good old file input field.

 

 

PART 3) FLASK SERVER

3A) SERVER INIT

# (A) INIT
# (A1) LOAD MODULES
from flask import Flask, render_template, request, make_response
from werkzeug.utils import secure_filename
import sqlite3

# (A2) FLASK SETTINGS + INIT
HOST_NAME = "localhost"
HOST_PORT = 80
app = Flask(__name__)
# app.debug = True
 
...
 
# (C) START
if __name__ == "__main__":
  app.run(HOST_NAME, HOST_PORT)

• (A) The top section of the Flask server script should be self-explanatory. Load the required modules, define settings, and load whatever middleware is required.
• (C) Start the HTTP server. Captain Obvious at your service.

 

3B) UPLOAD TO DATABASE

# (B) ENDPOINTS
# (B1) FILE UPLOAD PAGE
@app.route("/", methods=["GET", "POST"])
def index():
  return render_template("2-upload.html")

# (B2) SAVE UPLOADED FILE
@app.route("/upload", methods = ["POST"])
def saveup():
  # (B2-1) GET FILE INFO
  up = request.files["upload"]
  updata = up.read()
  upname = secure_filename(up.filename)
  upmime = up.content_type

  # (B2-2) SAVE INTO DATABASE
  conn = sqlite3.connect("demo.db")
  cursor = conn.cursor()
  cursor.execute(
    "REPLACE INTO storage (`file_name`, `file_mime`, `file_data`) VALUES (?,?,?)",
    (upname, upmime, updata)
  )
  conn.commit()
  conn.close()
  return "OK"

• / Serve the above HTML file upload form.
• /upload The file upload form will be submitted to this endpoint, we simply save the uploaded file into the database.

 

 

3C) SERVER INIT

# (B3) DOWNLOAD
@app.route("/download", methods=["GET", "POST"])
def dl():
  # (B3-1) GET FILE FROM DATABASE
  conn = sqlite3.connect("demo.db")
  cursor = conn.cursor()
  cursor.execute("SELECT * FROM storage WHERE `file_name`=?", ("vegetarian.png",))
  dbfile = cursor.fetchone()
 
  # (B3-2) FORCE DOWNLOAD
  response = make_response(dbfile[2], 200)
  response.headers["Content-type"] = dbfile[1]
  response.headers["Content-Transfer-Encoding"] = "Binary"
  response.headers["Content-Disposition"] = "attachment; filename=\"%s\"" % dbfile[0]
  return response

Finally, this small section will read a file from the database and do a force download.

 

 

NOT A GOOD PRACTICE

Yes, the above example works, but it is not a good practice to store massive files in the database – Most RDB are not made to work well with massive files. So unless you have very specific reasons to save files in the database, a “secured folder” will make more sense.

 

LINKS & REFERENCES

• Python Flask Documentation
• Upload Multiple Files – Code Boxx