Shopping Cart with TimescaleDB

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

Introduction

In this instructional article we’ll learn to set up an HTML screen to offer users a way to modify a web-based shopping cart with TimescaleDB using Python. The Python TimescaleDB application we build here will make use of many methods, types, and functions, including:

  • Python List: a variable type for storing the rowset returned from a select SQL statement into a list (array) for iterating through.
  • Flask request: to get data from an HTML form and URL querystring.
  • for loop: to loop through the records retrieveed from the cart table we’ll create with TimescaleDB.

The first thing to do is set up a database instance. We prefer using Objectrocket because we like their value and efficiency.

Create a TimescaleDB Database Instance on ObjectRocket

(1) Go to kb.objectrocket.com and “Log In”. (2) Use left menu to “Create Instance”. (3) In the “Name” field, add a descriptive name for your instance. (4) Under “Select your service” choose the system you want to use from “CockroachDB, Elasticsearch, PostgreSQL, Redis, TimescaleDB, and MongoDB”. (5) Choose the “Cloud Provider” and “Type” you want. (6) Select “Version” and “Region”. (7) Click the “GO TO STEP 2” button. (8) Make a choice in the “Pick Your Flavor” section. (9) Choose the “Capacity (Storage/Memory)”. Notice how this choice influences your total fee at the bottom right. (10) Click the “GO TO STEP 3” button. (11) Under “Add a Whitelist IP” pick “ALOW ANY IP” or “USE MY IP” or “Add my IPs later”. (12) Choose between “Master” and “Replica” and click the “ADD” button. (13) Now click the “CREATE INSTANCE” button at the bottom.

The next part to get done is to create a database connection to TimescaleDB using the psycopg2 library for Python.

TimescaleDB Connection With Python

1
2
3
4
5
6
7
8
9
10
import psycopg2
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

Analysis

  • import psycopg2: The psycopg2 library supplies Python with the components necessary for connecting to TimescaleDB, executing SQL, and creating cursors.
  • db_conn: Connection to your TimescaleDB server.
  • db_cursor: Cursor for reading and writing from/to TimescaleDB with Python.

The next thing we’ll set up for our Python and TimescaleDB shopping cart instructional article is a table called tblShoppingCart and add some data to the table.

Create Table for Shopping Cart

1
2
3
4
5
6
7
8
9
10
CREATE TABLE tblShoppingCart (
    id serial NOT NULL
    , id_item INT NOT NULL
    , IDuser INT NOT NULL
    , txtNameItem VARCHAR(64) NOT NULL
    , intQty INT NOT NULL
    , curPricePer REAL NOT NULL
    , CONSTRAINT tblShoppingCart_pkey PRIMARY KEY (id)
    );
CREATE UNIQUE INDEX tblShoppingCart_id_idx ON tblShoppingCart USING btree (id);

After we have created tblShoppingCart, we can fill the table with some sample data.

Fill TimescaleDB Table With Data

idid_itemIDusertxtNameItemintQtycurPricePer
100133456568Gumby Coffee Mug24.50
101294567568Pokey Socks13.95
102355678491Bugs Bunny Hat36.75
103416789572Speed Racer Gloves116.95

The next piece we’ll build for our shopping cart application with TimescaleDB back-end is a dynamic web page that we will show the user using Python’s render_template(). This HTML page will show a list of items that exist in the TimescaleDB database as you see in the table above, specifically in tblShoppingCart for that user. So yes, it is important to keep in mind that tblShoppingCart tracks all carts for all users, which is why we need the IDuser field to show only the current user their shopping cart items.

Query Cart With Python

1
2
3
4
5
6
7
8
9
10
11
12
13
14
s = ""
s += "SELECT"
s += " ID"
s += ", txtNameItem"
s += ", intQty"
s += ", curPricePer"
s += " FROM tblShoppingCart"
s += " WHERE"
s += " ("
s += " IDuser = (%IDuser)"
s += " )"
s += " ORDER BY txtNameItem;"
db_cursor.execute(s, [IDuser])
list_cart_contents = db_cursor.fetchall()

Notice we did not include the id_item column because all the operations the user requires can be served via the table’s unique “id” column.

Now that we have an idea of how the shopping cart table looks from two points of view, it will be easier to build our server-generated HTML page which displays the data for the shopper, as well as allow them to change the quantity of any product in their cart, as well as remove the item from their cart by clicking the “X” next to each item.

Here’s what the data that’s in the list_cart_contents array looks like. :

idIDusertxtNameItemintQtycurPricePer
100568Gumby Coffee Mug24.50
101568Pokey Socks13.95
102491Bugs Bunny Hat36.75
103572Speed Racer Gloves116.95

Create a Server-Generated HTML Page

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
<html>
<head>
<title>Upload Image to TimescaleDB</title>
<style>
body {background-color: #FFFFFF;}
h1 {font-size: 32pt;}
.div-main {margin: auto; padding-left: 14px; padding-right: 14px;}
.div-column {margin: auto;}
.form-caption {margin: auto;}
.form-input {text-align: left; width: 55px; color: #000000; background-color: #EEEEEE;}
.form-button {text-align: left; width: auto; color: #CCCCCC;}
</style>
</head>
<body>
<div class='div_tbl'>
    <!-- headings record -->
    <div class='div_record'>
    <div class='div-column'>Item</div>
    <div class='div-column'>Price Each</div>
    <div class='div-column'>Qty</div>
    <div class='div-column'>Total</div>
    <div class='div-column'>Delete</div>
</div>
{%
for i in range(len(list_cart_contents))
    ID = list_cart_contents[0][i]
    txtNameItem = list_cart_contents[2][i]
    intQty = list_cart_contents[3][i]
    curPricePer = list_cart_contents[4][i]
    c_price_record_total = curPricePer * intQty
%}
    <div class='div_record'>
        <form name='{{ ID }}' action='{{ t_url }}?txtWhatxtActionRoute=QtyChange&ID={{ ID }}' method='retrieve'>
        <div class='div-column'>{{ txtNameItem }}</div>
        <div class='div-column'>{{ curPricePer }}</div>
        <div class='div-column'><input name='{{ ID }}' type='text' size='2' value='{{ intQty }}' class='form-input'></a></div>
        <div class='div-column'>{{ c_price_record_total }}</div>
        <div class='div-column'><a href='{{ t_url }}?txtWhatxtActionRoute=DeleteItemFromCart&ID={{ ID }}'>X</a></div>
    </div>
{%
endfor
%}
    <div class='div-column'><input type='submit' value='Update' class='form-button'></div>
    </form>
</div>
</body>
</html>

Analysis

  • for i in range(len(list_cart_contents)): Iterate through each item in the “list_cart_contents” multidimensional array, which was passed to this template from our application, as we will see soon.
  • div: Encapsulates values and HTML so that styles, javascript, and other types of mods can be made to the values between the div and close div tags.
  • {{ t_url }}: We bracketted this code in divs to style the html. Next are the curly brackets; two of them together instructs the server to put values in this spot. Here we’re filling that spot with the contents of “t_url”. t_url is the URL for our Python application.
  • form: We place our fields inside an HTML form so we can use an input text box for the user to both see and change item quantities for each record / item.

Now that you have built an HTML “template” to be called up from the TimescaleDB application we’re building with Python, save the HTML file in a folder “under” the root directory on your server called “templates\”. Save the file as “shopping_cart.html” in that folder.

Now we will pull data the user submitted from the dynamic template HTML page above and call functions within our application to deal with that data.

Request Form Data With Python

First, we will request data from the form using Flask’s request function.

Python Flask Request Syntax

1
2
3
4
# Querystring Using GET.
txtValue_from_get = request.args.get([parameter name], [value if a null is received])
# Form Submission Using POST.
txtValue_from_post = request.form.get(parameter name, [value if a null is received])

Analysis

  • parameter name: This is the name of the field (how it was named in your querystring or form). For example, “txtWhatxtActionRoute” in our Python TimescaleDB application.
  • value if a null is received: In case no values were sent from the form, this is the value your txtValue_from_retrieve variable will receive. This is a way to trap errors. We often put an empty string here such as “”, as you’ll see in the final code listing at the bottom of this tutorial document.

Python Flask Request Example

Here are some examples of how the code might look when used in a project:

1
txtWhatxtActionRoute = request.args.get("txtWhatxtActionRoute", "")

Analysis

  • txtWhatxtActionRoute: This piece of the Python code is like saying to the server, “Study the querystring contained in the URL and find the ‘?’. After that question mark symbol, find ‘some parameter name=’ and put the value that comes after that equal sign symbol into a local variable we named txtWhatxtActionRoute.”

Source Code for Shopping Cart

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
# -----------------------
# Import Libraries Needed
# -----------------------
from flask import Flask
from flask import render_template # for render_template function (render HTML)
from flask import request # to request input from user
import psycopg2 # set up a TimescaleDB connection

# ----------------------
# Connect to TimescaleDB
# ----------------------
import psycopg2
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

# -----------
# Set Routing
# -----------
@app.route("/Main", methods=["GET", "POST"])

# ------------------------------
# Delete a Table Record
# ------------------------------
def deleteItemFromCart(id):
    s = ""
    s += "DELETE * FROM tblShoppingCart"
    s += " WHERE id=(%id)"
    # execute the sql above
    try:
        db_cursor.execute(s, [id])
    except psycopg2.Error as e:
        txtErrMsg = "SQL error: " + e + "/n SQL: " + s
        return render_template("errorpage.html", msg = txtErrMsg)
    # if autocommit is false:
    db_conn.commit()

# ---------------
# Change Quantity
# ---------------
def changeItemQty(id, intQty):
    s = ""
    s += "UPDATE tblShoppingCart SET"
    s += " intQty = (%intQty)"
    s += " WHERE id = (%id)"
    try:
        db_cursor.execute(s, [id, intQty])
    except psycopg2.Error as e:
        txtErrMsg = "SQL error: " + e + "/n SQL: " + s
        return render_template("errorpage.html", msg = txtErrMsg)
    # if autocommit is false:
    db_conn.commit()

# ---------------------------------------------
# Copy Shopping Cart for THIS User Into List
# ---------------------------------------------
    def getAllCartItems(IDuser):
    s = ""
    s += "SELECT"
    s += " ID"
    s += ", txtNameItem"
    s += ", intQty"
    s += ", curPricePer"
    s += " FROM tblShoppingCart"
    s += " WHERE"
    s += " ("
    s += " IDuser = (%IDuser)"
    s += " )"
    s += " ORDER BY txtNameItem;"
    # Pull all the records from the cursor
    db_cursor.execute(s, [IDuser])
    return db_cursor.fetchall()
    db_cursor.close()

# ----------------
# Primary Function
# ----------------
def Main():
    # check querystring value for action to take
    txtWhatxtActionRoute = request.args.get("txtWhatxtActionRoute", "")
    id = request.args.get("id", 0)
    intQty = request.args.get("intQty", 0)

    # if txtWhatxtActionRoute is "DeleteItemFromCart" then delete a record
    if txtWhatxtActionRoute == "DeleteItemFromCart":
        return deleteItemFromCart(id)

    # if txtWhatxtActionRoute is "changeItemQty" then update table with new quantity
    if txtWhatxtActionRoute == "changeItemQty":
        return changeItemQty(id, intQty)

    # Either way: get new tblShoppingCart contents and put it into our array
    IDuser = 1
    list_cart_contents = getAllCartItems(IDuser)
    # Show the dynamic HTML web page to user
    t_url = "[URL to application]" # be sure to fill this in!
    return render_template("shopping_cart.html", list_cart_contents = list_cart_contents, t_url = t_url)

Conclusion

In this instructional article we created a web-based shopping cart system with TimescaleDB and Python. Along the way we seed and used many functions from both TimescaleDB and Python, including multidimensional lists, FOR loops, Python’s render_template, receiving direction from a user via an HTML form, and psycopg2’s fetchall function to pull data from a TimescaleDB query into a List.

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.