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
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
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.
PYTHON FLASK UPLOAD FILE INTO DATABASE
All right, let us now get into the details of how to upload a file and save it into a database with Python Flask.
- Create a virtual environment
virtualenv venvand activate it –
- Install required libraries –
pip install flask werkzeug
- For those who are new, the default Flask folders are –
staticPublic files (JS/CSS/images/videos/audio)
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.
/uploadThe 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, 200) response.headers["Content-type"] = dbfile response.headers["Content-Transfer-Encoding"] = "Binary" response.headers["Content-Disposition"] = "attachment; filename=\"%s\"" % dbfile return response
Finally, this small section will read a file from the database and do a force download.
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
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
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!