Flask AJAX Form for Postgres

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

Introduction

Do you want to modernize your HTML data submission so your users can more easily submit data? All the cool apps are doing it. This article will show you how to create a Flask AJAX Form for Postgres. During this tutorial, we’ll touch on many different functionalities, the primary ones being HTML, JavaScript, JQuery, AJAX, REST, Python, Flask for Python, and Postgres as the database we are using to store submitted data via the SQL UPDATE command.

What is AJAX?

AJAX stands for Asynchronous JavaScript and XML. It encompasses a variety of web development technologies on the client (think browser) side to create asynchronous interactions. In other words, allowing web applications to send and retrieve data from the server asynchronously (in the background) without needing to refresh the HTML page. You’ve probably noticed this kind of behavior becoming more widespread lately when you are on a page, change some data, like maybe your phone number, and as soon as you click off that phone number field, the database is updated with your new number; you didn’t have to press the submit button.

Why make your form RESTful?

If that last sentence above didn’t give you enough of an idea: Ease and convenience for the user are primary reasons. Other reasons include less data exchanged with the database server because only the field we need to change has been updated. THAT benefit depends on how the AJAX is used. There are other benefits that relate to display of data instead of submitting data, not limited by but including the ability for a page to continuously update without the user needing to refresh. Weather forecasting applications, stock tickers, and cryptocurrency tickers come to mind.

The first part of our Python application we will look at is the dynamic HTML the client browser will be sent from Flask’s render_template function.

Build Python HTML template

Name the following file “form.html” and place it in the “/templates” folder.

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
<html>
<head>
    <!-- Jquery library we need for AJAX-type work below -->
    <script src="https://code.jquery.com/jquery-3.4.1.js"></script>
    <!-- Jquery script to create listener -->
    <script language='javascript'>
    $(function() {
        // Add an event listener to btn_submit
        // Three different actions can set this off:
        // (1) clicking submit button, (2) changing contents of first name field, and
        // (3) changing contents of last name field.
        $('#btn_submit').click(function() {
            $.ajax({
                id_user = document.getElementById('id_user').value;
                t_name_first = document.getElementById('t_name_first').value;
                t_name_last = document.getElementById('t_name_last').value;
                url : '/submission?id_user=' + id_user + '&t_name_first=' + t_name_first + '&t_name_last=' + t_name_last
           });
        });
    })
    </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="/contact" id="contact-form" method="GET" enctype="text/plain">
<div class="row">
    <div class="inputfield">
        <!-- hidden field is for storing the user's ID -->
        <input type='hidden' id='id_user' value='{{id_user}}'>
        <!-- HTML for the first name field -->
        <!-- Notice here the "onchange" we are using to activate the event listener for btn_submit. -->
        <!-- We do the same thing with the last name field below -->
        <label for="t_name_first">First name</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_first}}"
           name ="t_name_first" id="t_name_first" pattern="[a-zA-Z]*"
           onchange="document.getElementById('btn_submit').click();" required>
        <span class="helper-text" data-error="Text only, please." data-success=""></span>
    </div>
    <div class="inputfield">
        <label for="t_name_last">Last name</label>
        <input type="text"
           value="{{t_name_last}}"
           name ="t_name_last" id="t_name_last"
           onchange="document.getElementById('btn_submit').click();" required>
    </div>
</div>
<!-- We left a submit button on the page, for event listener but -->
<!-- we could actually remove this and make small changes to the javascript -->
<!-- to listen directly for changes to the first name and last name fields. -->
<!-- OR replace the button with a hidden field that is "listened" to. -->
<button id="btn_submit" type="submit">submit</button>
</form>
</body>
</html>

After building the HTML file and saving it as form.html, we will move on to constructing some of the SQL we will place in functions later.

Update Postgres user table

1
2
3
4
5
6
7
8
9
    s = ""
    s += "UPDATE tbl_users"
    s += " SET"
    s += " t_name_first = '(%t_name_first)'"
    s += ", t_name_last = '(%t_name_last)'"
    s += " WHERE ("
    s += " id_user =(%id_user)"
    s += ")"
    db_cursor.execute(s, [t_name_first, t_name_last, id_user])

Some things to take note of regarding the Python / SQL script above:

  • We are using the UPDATE command from SQL, along with “tbl_users” to tell Postgres we want to change some data in a row. NOTE: Without the WHERE clause, it would be all rows.
  • We have parameterized this query by placing data submitted by the user via AJAX and Flask’s request function into a structure with parenthesis and a percentage symbol and then supplying that data via parameters in the psycopg2 execute function.

Get user data from Postgres

1
2
3
4
5
6
7
8
9
10
11
    s = ""
    s += "SELECT"
    s += " t_name_first"
    s += ", t_name_last"
    s += " FROM"
    s += " tbl_users"
    s += " WHERE ("
    s += " id_user =(%id_user)"
    s += ")"
    db_cursor.execute(s, [id_user])
    return db_cursor.fetchall()

The query we built above is fairly self explanatory. We only needed to feed it “id_user” as a parameter, so Postgres can find the right row via the WHERE clause. The function returns the entire row as an array (list), where the main function can parse it out into t_name_first and t_name_last. Which brings us to…

Parse Python list into variables

1
2
3
data_returned = GetData(id_user)
t_name_first = data_returned[0]
t_name_last = data_returned[1]

Here we see that we are collecting from “return db_cursor.fetchall()” into a list named “data_returned” and then pulling item 1 from data_returned[0] and item 2 from data_returned[1] as t_name_first and t_name_last, respectively.

Now, let’s put it all together into a Python web application!

Source: Creating and deleting tables 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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
# ---------------------------------------------
# Include Python framework/library dependencies
# ---------------------------------------------
from flask import flask
from flask import render_template
from flask import request
import psycopg2
import time

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

app = Flask(__name__)

@app.route("/Main", methods=["GET"])
# ----------------------------
# Application main function
# ----------------------------
def main():
    id_user = 562
    data_returned = GetData(id_user)
    t_name_first = data_returned[0]
    t_name_last = data_returned[1]
    return render_template('form.html', reload = time.time())

# ------------------------
# Update database function
# ------------------------
def SaveData(id_user, t_name_first, t_name_last):
    s = ""
    s += "UPDATE tbl_users"
    s += " SET"
    s += " t_name_first = '(%t_name_first)'"
    s += ", t_name_last = '(%t_name_last)'"
    s += " WHERE ("
    s += " id_user =(%id_user)"
    s += ")"
    db_cursor.execute(s, [t_name_first, t_name_last, id_user])

# ------------------------
# Read user data function
# ------------------------
def GetData(id_user):
    s = ""
    s += "SELECT"
    s += " t_name_first"
    s += ", t_name_last"
    s += " FROM"
    s += " tbl_users"
    s += " WHERE ("
    s += " id_user =(%id_user)"
    s += ")"
    db_cursor.execute(s, [id_user])
    return db_cursor.fetchall()

@app.route("/submission")
def add():
    id_user = request.args.get('id_user', 0)
    t_name_first = request.args.get('t_name_first', "")
    t_name_last = request.args.get('t_name_last', "")
    SaveData(id_user, t_name_first, t_name_last)

Conclusion

In this article we learned how to create a Flask AJAX Form for PostgreSQL. During the article, we touched on many different functionalities, including HTML, JavaScript, JQuery, AJAX, REST, Python, Flask for Python, and PostgreSQL as the database we used to store data submitted via the UPDATE SQL command. In plain terms: Allow a user to update their name without having to press “submit” but they can press “submit” if they want or need to. Code samples are included.

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.