Image Upload with Python and Postgres

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

You will learn in this tutorial how to image upload with Python to Postgres. We’ll build a Python Flask application that uses an HTML page which is called a template in Flask. We will build an HTML form in that HTML template, which will allow the user to click a button, browse their hard drive for the image, and upload the file. Back in Python we will use Flask’s request function to grab the data for that submitted image and insert a record into PostgreSQL with the image data to be stored as a blob.

Before we start building the mini application, let’s look at an alternative method:

Save image file name

A different method to upload and store a file is to store the image in a folder on the server and save the path to that image in a text column in Postgres. A benefit with that method is increased portability because the blob datatype works differently in different database systems and is not even available in some. Also, storing a file – especially images – can quickly bloat the size of a database.

Save file as blob

In this lesson, we are going to store the actual file bytes that define the image into a blob type column in PostgreSQL. Below we will learn how that is done.

HTML Flask template

The first item to script will be an interface; an HTML page that will automatically appear in the user’s default browser which will be called up by the render_template function from Flask.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<html>
<head>
<title>Upload Image to PostgreSQL</title>
<style>
body {background-color: #E9ECCC;}
h1 {font-size: 30pt;}
.div-main {margin: auto; padding-left: 10px; padding-right: 10px;}
.form-fld-caption {text-align: right;}
.form-fld-input {text-align: left;}
.form-btn-submit {text-align: left;}
.frm-btn-submit {width: auto; color: #CCCC00;}
.file-input {width: 58px; color: #FFFFFF; background-color: #000000;}
</style>
</head>
<body style='background-color: #E9ECCC;'>
<div class='div-primary'>
    <h1>Upload Image File</h1>
    <h2>{{msg}}</h2>
    <form action = '/ImageUploader' method = 'POST' enctype = 'multipart/form-data' id='UploadForm'>
    <div class = 'row'>
        <div class = 'form-fld-caption'>Select an image</div>
        <div class = 'form-fld-input'><input type = 'file' class = 'file-input' name = 'file_image' id = 'file_image'></div>
    </div>
    <div class = 'row'>
        <div class = 'form-btn-submit'><button type = 'submit' class = 'frm-btn-submit'>Upload File</button></div>
    </div>
    </form>
</div>
</body>
</html>

Analysis

  • style. This css tag sets up a section in the header where CSS classes for the HTML document define how the user’s browser presents information in terms of color, layout, size, alignment, etc.
  • {{msg}}. This plugs in the dynamic data sent to the template from Flask’s render_template function we will build in a later section of this article.
  • form action = ‘/ImageUploader’ method = ‘POST’ enctype = ‘multipart/form-data’ id=’UploadForm’. What is most important here are two things: (1) “enctype = ‘multipart/form-data'” tells the server the type of data being posted is a file. It also tells the client browser how to handle the situation. (2) “/ImageUploader” is a URL that sends the form submission to the “@app.route” function back in Python.
  • input type = ‘file’ class = ‘file-input’ name = ‘file_image’ id = ‘file_image’. The most important parameter here is “type = ‘file'”, which tells the browser to display a “browse” button so the user can browse their hard drive and choose a file for uploading. This works mostly the same in all browsers.
  • button type = ‘submit’ class = ‘frm-btn-submit’. This places a button for the user to click (or tape) to submit any data submitted to all fields in the form, and which begins uploading the file the user chose in the input field above. So values in any fields between the “open form” and “close form” tags will be sent to the server for Python to “request”.
  • Note We used single-quotes for all tag attributes. We could just as easily used full quotes. Browsers interpret both the same. The nice thing about being able to mix the two, like in Python, is that we can use one within another, which is especially useful when weaving JavaScript into your HTML.

You have now created an HTML page we can render from Python in later sections of this article. Save the file in a folder called “templates\” and name it as “imageUploader.html”.

Now we will begin work on the Python and Postgres application.

Routing in Python

First we set up a route in Python that is navigated to when the user submits the form we built above.

1
2
3
@app.route("/ImageUploader", methods=["GET", "POST"])
def ImageUploader():
    return render_template("imageUploader.html")

Analysis

  • @app.route(“/ImageUploader”, methods=[“GET”, “POST”]). This tells Python which function to go to, as well as the methods available to receive the form POST. Don’t worry. Later we will learn to “request” the data submitted in the form we built. For now we are focused on rendering the HTML page.
  • render_template(“imageUploader.html”). This is a Flask function that pulls up the HTML page for the user to see and use.

Python frameworks needed for upload

1
2
3
4
import psycopg2
from flask import Flask
from flask import render_template
from flask import request

Analysis

These are the Python frameworks needed for the functions we’ll be using in this application. We’ve discussed render_template. Request is what we’ll use to get the data submitted by the user; in this case it is a file. The psycopg2 framework is not YET needed but will be for all of our database operations.

Request POSTed data with Flask

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
def ImageUploader():
    msg = ""
    # Check to be sure the data is a POST
    # rather than a GET.
    if request.method == "POST":
        # IF a file was uploaded.
        if request.files:
            # Retrieve the submitted file data
            # and place that data in a variable
            # called "fileData"
            fileData = request.files["image"]
            # Call function for saving the file
            # data to Postgres.
            SaveFileToPG(fileData)
        else:
            # If no file was uploaded, create a message
            # for the user.
            msg = "No file found. Please choose one from your device."
    # Show the user our HTML page and send a message if one exists.
    return render_template("imageUploader.html", msg)

Analysis

  • def ImageUploader. Creates a function for retrieving the file data submitted and then calling the function we’ll create soon to save the file’s data to the database as a blob type.
  • if request.method. Check to find out if the form was POSTed, not a querystring (GET).
  • if request.files. Making sure a file was submitted. If not, send the user a message.
  • request.files[“image”]. This retrieves the data POSTed and saves that data into the “fileData” variable.
  • SaveFileToPG(fileData). This calls the next feature we will code in Python, which is for saving the file data to a blob type field in Postgres.

Save uploaded blob to database

1
2
3
4
5
6
7
8
9
10
11
def SaveFileToPG(id_image, fileData):
    s = ""
    s += "INSERT INTO tbl_files_images"
    s += "("
    s += "id_image"
    s += ", blob_image_data"
    s += ") VALUES ("
    s += "(%id_image)"
    s += ", '(%fileData)'"
    s += ")"
    db_cursor.execute(s, [id_image, fileData])

Analysis

This “SaveFileToPG(id_image, fileData)” function is for inserting blob file data into our “tbl_files_images” in PostgreSQL. Blob is a data type used for storing large chunks of data that might be an image, a film, a PDF, a song file, etc. We created the function to have two parameters; id_image and blob_image_data. If you study the INSERT command above, you will see id_image is plugged into the part of the SQL’s INSERT clause that defines a column. The blob_image_data parameter does the same, placing the contents of fileData.

That’s it for explaining the disparate parts of our application. Now we will look at a full code listing:

Source code: upload file

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
# -------------------
# Import dependencies
# -------------------
import psycopg2 # For Postgres operations.
from flask import Flask
from flask import render_template # Show HTML page.
from flask import request # Get user input from HTML page, in this case uploaded file data.

# ---------------------
# PostgreSQL connection
# ---------------------
t_host = "Database URL"
t_port = "5432"
t_dbname = "database"
t_name_user = "user name"
t_password = "password"
data_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_name_user, password=t_password)
db_cursor = data_conn.cursor()

# ------------
# Route to use
# ------------
@app.route("/ImageUploader", methods=["GET", "POST"])

# --------------------------
# Get list of files uploaded
# --------------------------
def ImageUploader():
    if request.method == "POST":
        msg = ""
        if request.files:
            fileData = request.files["image"]
            # Make sure you have an item ID.
            #   We used an arbitrary number here.
            id_image = 42
            # Pass both item ID and image file data to a function
            SaveFileToPG(id_image, fileData)
        else:
            msg = "No file picked. Please choose one from your device."
    return render_template("imageUploader.html", msg = t_msg_err)

def SaveFileToPG(id_image, fileData):
    s = ""
    s += "INSERT INTO tbl_files_images"
    s += "("
    s += "id_image"
    s += ", blob_image_data"
    s += ") VALUES ("
    s += "(%id_image)"
    s += ", '(%fileData)'"
    s += ")"
    # --------------
    # Error trapping
    # --------------
    try:
        db_cursor.execute(s, [id_image, fileData])
    except psycopg2.Error as e:
        t_msg_err = "SQL error: " + e + "/n SQL: " + s
        return render_template("imageUploader.html", msg = t_msg_err)

Conclusion

We learned in this tutorial how to upload an image with Python to Postgres. We built a Python Flask application that uses an HTML page which is called a template in Flask. We built a form into that HTML template, which allows users to click a “browse” button, browse their hard drive for the image file, and upload that file. Back in Python we used Flask’s “request” to retrieve the data for that submitted image and insert a record into a PostgreSQL blob column with the image data.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.