Form to Postgres with REST in Python

Introduction

One way to make data management in your client server applications easier and more efficient for users and possibly, put less load on the server is to build the application so that users can submit the form to Postgres with REST in Python. This kind of operation is often called “AJAX”. The exercises in this lesson will expose you to many related technologies, including but not limited to JavaScript, JQuery, HTML, AJAX, RESTful, psycopg2, Flask, INSERT INTO, and PostgreSQL.

AJAX definition

AJAX, or Asynchronous JavaScript and XML, includes web technologies on the browser (client side) to allow for asynchronous interactions. To put it simpley: AJAX allows web applications to send and receive data between the browser and server in the background without refreshes of the page being required; thus the term “asynchronous”. You have seen this behavior becoming more ubiquitous when you are on a web site, change your email address or user name, and when you click on another element of the form or just out of that user name field, the server is updated with your new user name; you didn’t have to press “submit” or “save”.

REST benefits?

First comes efficiency and ease for the user. More: [usually] less data is exchanged between the client and server because only the field we need to change has been updated, which depends on how the REST is used, as sometimes all columns are still updated. Since reads are typically faster than writes in land of PostgreSQL, we could – before doing the UPDATE – read the table and compare to what the user submitted in order to determine which fields require update. There are other ways, all client-side, that would be even more efficient. On the other side, where we are sending data from the server to the client: a page to continuously update without need for a page refresh. Crypto tickers, stock tickers, and weather applications, for example.

The beginning of our application (built in Python using Flask) we will create is an HTML form the user will be shown via use of the render_template function that is yet another great Flask utility.

HTML RESTful form

Save the following HTML as “restful_form.html” and put it in the folder in the root called “/templates”.

<html>
<head>
    <!-- Jquery needed for the async to work below -->
    <script src="https://code.jquery.com/jquery-3.4.1.js"></script>
    <!-- Jquery for listener setup -->
    <script language='javascript'>
    $(function() {
        // Add an event listener to button_save_or_update
        // Three different actions can set this off:
        // (1) clicking submit button, (2) changing contents of product title field, and
        // (3) changing contents of product price field.
        $('#button_save_or_update').click(function() {
            $.ajax({
                id_item = document.getElementById('id_item').value;
                t_name_product_item = document.getElementById('t_name_product_item').value;
                c_price_product_item = document.getElementById('c_price_product_item').value;
                url : '/submission?id_item=' + id_item + '&t_name_product_item=' + t_name_product_item + '&c_price_product_item=' + c_price_product_item
           });
        });
    })
    </script>
</head>
<body>
<!-- Since we have used JavaScript (JQuery) to bypass the form by going straight to the DOM fields, -->
<!-- the only reason we are using the form tag blow is to specify encryption type as text/plain -->
<form action="/savey" id="form_product_update" method="GET" enctype="text/plain">
<div class="row">
    <div class="inputfield">
        <!-- hidden field is for storing the product's ID -->
        <input type='hidden' id='id_item' value='{{id_item}}'>
        <!-- HTML for the product title field -->
        <!-- Regarding the "onchange" for activation of the event listener for button_save_or_update: -->
        <!-- We do the same with the product price field below -->
        <label for="t_name_product_item">Product name/title</label>
        <!-- Notice for the two input fields below, the "value" tag:  -->
        <!--   this autofills the form with the user's current/old data -->
        <input type="text"
           value="{{t_name_product_item}}"
           name ="t_name_product_item" id="t_name_product_item" pattern="[a-zA-Z]*"
           onchange="document.getElementById('button_save_or_update').click();" required>
        <span class="helper-text" data-error="Text only, please." data-success=""></span>
    </div>
    <div class="inputfield">
        <label for="c_price_product_item">Product price</label>
        <input type="text"
           value="{{c_price_product_item}}"
           name ="c_price_product_item" id="c_price_product_item"
           onchange="document.getElementById('button_save_or_update').click();" required>
    </div>
</div>
<!-- We kept a Save/Update button for the listener -->
<!--   but we *could* remove it and make changes to the javascript -->
<!--   to listen directly to each input field for changes to the product -->
<!--   title and product price fields. -->
<button id="button_save_or_update" type="submit">Save / Update</button>
</form>
</body>
</html>

After saving the HTML file above as restful_form.html, we will do the next step in building our Python RESTful application for submitting data to PostgreSQL asynchronously.

Get item data from PostgreSQL

s = ""
s += "SELECT"
s += " t_name_product_item"
s += ", c_price_product_item"
s += " FROM"
s += " tbl_products_n_items"
s += " WHERE ("
s += " id_item =(%id_item)"
s += ")"
postgres_cursor.execute(s, [id_item])
return postgres_cursor.fetchall()

The SQL above is mostly self-explanatory. It requires “id_item” as a parameter for the WHERE clause, so PostgreSQL can return the record we need, which is data on a specific product / item. The “execute” function retrieves the entire row into an array (list), where later part of our Python application can split that array into t_name_product_item and c_price_product_item discrete values.

Now that we have READ the PostgreSQL database to get product / item data to show the user for potential updating, IF they update the data shown them in the form fields, the following SQL will be required:

Update PostgreSQL user table

s = ""
s += "UPDATE tbl_products_n_items"
s += " SET"
s += " t_name_product_item = '(%t_name_product_item)'"
s += ", c_price_product_item = '(%c_price_product_item)'"
s += " WHERE ("
s += " id_item =(%id_item)"
s += ")"
postgres_cursor.execute(s, [t_name_product_item, c_price_product_item, id_item])

Analysis

  • The SQL UPDATE command, plus “tbl_products_n_items” instructs PostgreSQL to change some data in a row. Without the WHERE clause, it would change all rows, which would not be a good thing.
  • This query is parameterized by putting values submitted by the user via request (from Flask) into parenthesis and a “%” symbol and then supplying that data via parameters in execute (from psycopg2).

Parse Python list into variables

data_from_user = ReadDatabase(id_item)
t_name_product_item = data_from_user[0]
c_price_product_item = data_from_user[1]

Here we see that we are copying values from “return postgres_cursor.fetchall()” to a list we called “data_from_user” and next copying the first value from data_from_user[0] to t_name_product_item and the second value from data_from_user[1] as c_price_product_item.

Now that we have a greater understanding of how the most important parts of our application – the meat – work, we will create the full Python application for (a) reading data for a given product / item, (b) displaying that data on a dynamic HTML “template” form, (c) retrieving changes by the user asynchronously, and (d) sending those changes to PostgreSQL.

Full RESTful Python application

# --------------------
# Include dependencies
# --------------------
from flask import flask
from flask import render_template
from flask import request
import time
import psycopg2

# ---------------
# Data connection
# ---------------
t_host = "DB IP address, URL, or 'localhost'"
t_port = "5432"
t_dbname = "name of the database"
t_name_user = "database user"
t_password = "database user pw"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_name_user, password=t_password)
postgres_cursor = db_conn.cursor()

app = Flask(__name__)

@app.route("/Main", methods=["POST", "GET"])
# ----------------
# Primary function
# ----------------
def main():
    id_item = 28
    data_from_user = ReadDatabase(id_item)
    t_name_product_item = data_from_user[0]
    c_price_product_item = data_from_user[1]
    return render_template('restful_form.html', reload = time.time())

# ---------
# Update DB
# ---------
def UpdateDatabase(id_item, t_name_product_item, c_price_product_item):
    s = ""
    s += "UPDATE tbl_products_n_items"
    s += " SET"
    s += " t_name_product_item = '(%t_name_product_item)'"
    s += ", c_price_product_item = (%c_price_product_item)"
    s += " WHERE ("
    s += " id_item =(%id_item)"
    s += ")"
    postgres_cursor.execute(s, [t_name_product_item, c_price_product_item, id_item])

# -----------------
# Read data from DB
# -----------------
def ReadDatabase(id_item):
    s = ""
    s += "SELECT"
    s += " t_name_product_item"
    s += ", c_price_product_item"
    s += " FROM"
    s += " tbl_products_n_items"
    s += " WHERE ("
    s += " id_item =(%id_item)"
    s += ")"
    postgres_cursor.execute(s, [id_item])
    return postgres_cursor.fetchall()

@app.route("/savey")
def savey():
    id_item = request.args.get('id_item', 0)
    t_name_product_item = request.args.get('t_name_product_item', "")
    c_price_product_item = request.args.get('c_price_product_item', 0)
    UpdateDatabase(id_item, t_name_product_item, c_price_product_item)

Conclusion

In this tutorial we learned to make user-facing forms in your client-server Python-Postgres application easier and more efficient for users and possibly, put less stress on the server is to build the application so that users can submit forms to PostgreSQL using a RESTful model in Python. This is often called “Asynchronous JavaScript and XML” or “AJAX”. The exercises in this lesson also taught some related technologies, including JavaScript, JQuery, HTML, AJAX, RESTful, psycopg2, Flask, INSERT INTO, and PostgreSQL.

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.