Upload a File to Postgres with Python 1112

Introduction

In this tutorial, we explore how to upload a file to Postgres with Python. We’ll build a Python app that shows the user an HTML form with a field for uploading their file. On the back end, we’ll “harvest” that file and save it to PostgreSQL using psycopg2, Flask, and PostgreSQL’s blob data type.

Alternate method: Save file path

One way to save a file with Python and Postgres is to write the file to a folder on the server and store the file path text to a column in Postgres defined as the text data type. A benefit to using that method is portability because the “blob” data type works differently in every database system. Since we know we are working with Postgres here, we’ll learn the “save file as blob” method.

Save in Postgres as blob

In this tutorial, we will store the file in Postgres as a data type known as “blob”. We’ll use the INSERT INTO SQL command to “upload” the file contents to a PostgreSQL table.

The first step in building our Python application for uploading a file to PostgreSQL is to create an HTML page as the interface the user will see, which allows the user to choose a file from their hard drive, such as a PDF, avi, mp4, mp3, JPG, PNG, etc., and submit that file for upload to the server via form submission and the Python Flask request function.

Create HTML page for upload

<html>
<head>
<title>Upload File to Postgres</title>
<style>
body {background-color: #EEEEEE;}
h1 {font-size: 32pt;}
.div-primary {margin: auto; padding-left: 20px; padding-right: 20px;}
.frm-fld-label {text-align: right;}
.frm-fld-input {text-align: left;}
.frm-fld-btn-save {text-align: left;}
.frm-bnt-save-file {width: auto; color: #CC0000;}
.div-input-file {width: 60px; background-color: #CCCCCC;}
</style>
</head>
<body style='background-color: #D1D1D1;'>
<div class='div-primary'>
    <h1>Upload a File</h1>
    <form action = '/Upload_File' method = 'POST' enctype = 'multipart/form-data'>
    <div class = 'row'>
        <div class = 'frm-fld-label'>Select a file from your computer</div>
        <div class = 'frm-fld-input'><input type = 'file' class = 'div-input-file' name = 'file_name' id = 'file_name'></div>
    </div>
    <div class = 'row'>
        <div class = 'frm-fld-btn-save'><button type = 'submit' class = 'frm-bnt-save-file'>Upload Your File</button></div>
    </div>
    </form>
</div>
</body>
</html>

Analysis

  • style. This tag encapsulates the section in our HTML document (up in the head area) where we define CSS classes for our page to define the look, alignment, colors, font sizes, and layout of the user’s browser display of the page.
  • form. In this example we do not need to give the form a name or id because we are not using any javascript or other code, client or server, to refer to the form part. That said, two items ARE important: (1) “action = ‘/Upload_File'” points the form to a specific spot in the Python application we will build below. (2) “enctype = ‘multipart/form-data'” is required so Python Flask’s request function understands what kind of data – file – is being uploaded.
  • input type = ‘file’. This field causes the browser to show the user a button for browsing their local computer folders to pick the file the user wants to upload to the server.
  • button type = ‘submit’. This creates a button for the user to press to submit the file for upload that the user had selected from their local hard drive.

You have built a dynamic HTML page to be called up via Python Flask’s render_template function. Save this HTML page as “save-file.html” in a folder called “templates\”.

Next step will be to get into your Python development environment and begin building the pieces that will eventually come together into a full blown Python web database application.

Python route and primary function

@app.route("/Upload_File", methods=["GET", "POST"])
def Upload_File():
    return render_template("save-file.html")

Analysis

  • @app.route(“/Upload_File”, methods=[“GET”, “POST”]). This tells Python which function to go to when called by the user via the HTML form we built in the exercise above.
  • render_template. This function (requires Flask) shows the dynamic HTML page we created above.

Python frameworks for file upload

from flask import Flask
from flask import render_template
from flask import request
from flask import redirect
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. Redirect is exactly what it sounds like. It allows us to redirect the user and program flow to another function.

Request POSTed data with Flask

def Upload_File():
    if request.method == "POST":
        if request.files:
            blob_saved_file = request.files["file_name"]
            Save_File_To_Database(blob_saved_file)

    return render_template("save-file.html")

Analysis

  • def Upload_File. Defining a function. 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 “blob_saved_file” variable. Note: You can call this variable anything you want; it doesn’t have to be called “blob_saved_file”. But if you change that, then change it in the next line of code, too.
  • Save_File_To_Database(blob_saved_file). This calls the next function we will build, which is for saving the file to PostgreSQL.

Save file to Postgres

def Save_File_To_Database(id_item, blob_saved_file):
    s = ""
    s += "INSERT INTO tbl_saved_files"
    s += "("
    s += "id_item"
    s += ", blob_saved_file"
    s += ") VALUES ("
    s += "(%id_item)"
    s += ", '(%blob_saved_file)'"
    s += ")"
    data_cursor.execute(s, [id_item, blob_saved_file])

Analysis

The “Save_File_To_Database” function is for saving blob data into our “tbl_saved_files” PostgreSQL table. Blob is a Postgres data type we use for storing large amounts of data that might comprise something like an file, a PDF, a video, a music file, etc. We gave our function two parameters; id_item and blob_saved_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_saved_file parameter does the same, placing the contents of blob_saved_file.

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

Source Code: Upload file to Postgres with Python

# ---------------------------
# Python frameworks to import
# ---------------------------
from flask import Flask
from flask import render_template
from flask import request # to get input from HTML form
import psycopg2 # for database connection and SQL execution

# ---------------------
# Connect to PostgreSQL
# ---------------------
t_host = "URL to Postgre Database"
t_port = "port number here" #5432 is typically default port
t_dbname = "database"
t_name_user = "database user name"
t_password = "user 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("/Upload_File", methods=["GET", "POST"])

# ----------------------------------------
# Function for retrieving form data (file)
# ----------------------------------------
def Upload_File():
    if request.method == "POST":
        if request.files:
            blob_saved_file = request.files["file_name"]
            # IMPORTANT: Be sure to have an item ID.
            id_item = 52
            # Call function for saving to Postgres, with two parameters
            Save_File_To_Database(id_item, blob_saved_file)

    return render_template("save-file.html")

# ------------------------------------------
# Function for saving the file to PostgreSQL
# ------------------------------------------
def Save_File_To_Database(id_item, blob_saved_file):
    s = ""
    s += "INSERT INTO tbl_saved_files"
    s += "("
    s += "id_item"
    s += ", blob_saved_file"
    s += ") VALUES ("
    s += "(%id_item)"
    s += ", '(%blob_saved_file)'"
    s += ")"
    # We recommend adding TRY here to trap errors.
    data_cursor.execute(s, [id_item, blob_saved_file])
    # Use commit here if you do not have auto-commits turned on.

Conclusion

In this tutorial, we built a Python app to learn how to upload a file to Postgres with Python. The Python Flask application shows the user an HTML form with a control for uploading their file. On the server side, we requested that file and saved it to a blob field in PostgreSQL using psycopg2 and Flask.

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.