Upload Image with Python to Postgres

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

Introduction

You will learn in this document how to upload an image with Python to Postgres. We’ll build an application using Python that calls a Flask template (HTML page) up for the user to see and use the form on that page to upload an image. In Python we will request the data for that submitted image and insert a record into PostgreSQL with the image data to be stored as a blob.

Save file name in Postgres

A simple method to store an image is to store the image in a folder on the server and save the path to that image in a text column in PostgreSQL. One benefit with this method is increased portability because the blob datatype is not available or works differently in other database systems.

Save blob in Postgres

In this lesson, we are going to store the actual file data that defines the image in PostgreSQL as a “blob” data type. Below we will learn how to do this.

Build Python Flask template

The first thing to build will be our user interface; an HTML document to display in the user’s browser that will be shown to the user via Flask’s render_template function for Python.

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
<html>
<head>
<title>Upload Image to PostgreSQL</title>
<style>
body {background-color: #EEEEEE;}
h1 {font-size: 32pt;}
.div-main {margin: auto; padding-left: 20px; padding-right: 20px;}
.form-field-caption {text-align: right;}
.form-field-input {text-align: left;}
.form-button-submit {text-align: left;}
.frm-btn-submit {width: auto; color: #CC0000;}
.file-input {width: 60px; background-color: #CCCCCC;}
</style>
</head>
<body style='background-color: #DDDDDD;'>
<div class='div-main'>
    <h1>Upload an Image</h1>
    <form action = '/UploadImage' method = 'POST' enctype = 'multipart/form-data'>
    <div class = 'row'>
        <div class = 'form-field-caption'>Select an image</div>
        <div class = 'form-field-input'><input type = 'file' class = 'file-input' name = 'file_image' id = 'file_image'></div>
    </div>
    <div class = 'row'>
        <div class = 'form-button-submit'><button type = 'submit' class = 'frm-btn-submit'>Upload Image</button></div>
    </div>
    </form>
</div>
</body>
</html>

Analysis

  • style. This tag begins the section in the header of an HTML document where we set up the CSS classes for our document to fine tune what the user’s browser displays in terms of layout, color, alignment, size, etc.
  • form action = ‘/UploadImage’ method = ‘POST’ enctype = ‘multipart/form-data’. In this case we don’t need to give the form an id or name because we are not using any javascript to refer to the form. What is very important are two items: (1) “/UploadImage” is a URL that points the form to the “@app.route” function in our Python application. (2) “enctype = ‘multipart/form-data'” which is necessary so Flask knows what kind of data – image file – is being posted.
  • input type = ‘file’ class = ‘file-input’ name = ‘file_image’ id = ‘file_image’. This field, because type is set to “file”, is where the user is presented with a “browse” button to browse their local drive to pick a file to upload.
  • button type = ‘submit’ class = ‘frm-btn-submit’. This places a button for the user to press to submit the form, which begins the upload process for the file the user picked in the input field above.
  • Note Notice how we used single-quotes (apostrophes) for each tag’s attributes. We could just as easily used full quotes. Browsers interpret both the same.

Congratulations! You have built an HTML page we can later render from Python. Save this file in a folder called “templates\” and name the file “upload-image.html”.

Now let’s begin building the Python application. We like to use Visual Studio Code, which is free. You could even use notepad if you prefer.

Set up a Python route

The first step is to set up the route and define the primary function in our application.

1
2
3
@app.route("/UploadImage", methods=["GET", "POST"])
def UploadImage():
    return render_template("upload-image.html")

Analysis

  • @app.route(“/UploadImage”, methods=[“GET”, “POST”]). This function tells Python which function to go to, as well as the methods available to receive the form POST.
  • render_template(“upload-image.html”). This Flask function displays the HTML page we built earlier.

Python libraries for image upload

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

Analysis

These are the libraries needed for the functions we’ll be using in this application. We’ve talked about render_template. Request is what we’ll use to get the data submitted by the user; in this case it is a file.

Request POSTed data with Flask

1
2
3
4
5
6
7
def UploadImage():
    if request.method == "POST":
        if request.files:
            FileImage = request.files["image"]
            SaveToDatabase(FileImage)

    return render_template("upload-image.html")

Analysis

  • def UploadImage. Definines our function for getting the image data and then calling the function to save the data to the database as a blob. Pretty much everything requires a function.
  • if request.method. Checking to see if form was POSTed.
  • if request.files. Making sure a file was POSTed for upload.
  • request.files[]. This gets the data POSTed and places that data into the “FileImage” variable. Note: You can call this variable anything you want; it doesn’t have to be called “FileImage”. But if you change that, then change it in the next line of code, too.
  • SaveToDatabase(FileImage). This calls the next function we will build, which is for saving the image to PostgreSQL.

Save image to Postgres

1
2
3
4
5
6
7
8
9
10
11
def SaveToDatabase(id_item, FileImage):
    s = ""
    s += "INSERT INTO tbl_goodies"
    s += "("
    s += "id_item"
    s += ", blob_file"
    s += ") VALUES ("
    s += "(%id_item)"
    s += ", '(%FileImage)'"
    s += ")"
    data_cursor.execute(s, [id_item, FileImage])

Analysis

The “SaveToDatabase(id_item, FileImage)” function is for saving blob data into our “tbl_goodies” PostgreSQL table. Blob is a Postgres data type we use for storing large amounts of data that might comprise something like an image, a PDF, a video, a music file, etc. We gave our function two parameters; id_item and blob_file. If you look at the INSERT command, you will see id_item is plugged into the part of the SQL’s INSERT clause that defines a column name. The blob_file parameter does the same, placing the contents of FileImage.

That’s it! Let’s put it all together now:

Source Code: Upload Image to Postgres with Python

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
# ---------------------------
# Include Python dependencies
# ---------------------------
from flask import Flask
from flask import render_template # for render_template
from flask import request # for getting user input from HTML page
import psycopg2 # for database operations

# -------------------
# Database 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)
data_cursor = data_conn.cursor()

@app.route("/UploadImage", methods=["GET", "POST"])

# ----------------------------------------
# Get list of available backups to restore
# ----------------------------------------
def UploadImage():
    if request.method == "POST":
        if request.files:
            FileImage = request.files["image"]
            # Make sure you have an item ID.
            #   We used an arbitrary number here.
            id_item = 52
            # Pass both item ID and image file data to a function
            SaveToDatabase(id_item, FileImage)

    return render_template("upload-image.html")

def SaveToDatabase(id_item, FileImage):
    s = ""
    s += "INSERT INTO tbl_goodies"
    s += "("
    s += "id_item"
    s += ", blob_file"
    s += ") VALUES ("
    s += "(%id_item)"
    s += ", '(%FileImage)'"
    s += ")"
    # We recommend adding TRY here to trap errors.
    data_cursor.execute(s, [id_item, FileImage])
    # Use commit here if you do not have auto-commits turned on in PostgreSQL.

Conclusion

In this tutorial you learned how to upload an image with Python to PostgreSQL. Together, we built a Python application that displays a Flask HTML template for the user to use the form on that page to upload an image to the server. Next, in Python we requested the data for that submitted file data of the image and used the insert SQL command to save a new record into a PostgreSQL table with the image data stored as a blob data type.

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.