Shopping Cart with CockroachDB and Python

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

Introduction

In this tutorial we will learn to set up an HTML screen to give users a way to modify a web-based shopping cart with CockroachDB and Python. The Python CockroachDB application we build here will make use of many methods, types, and functions, including:

  • Python List: a variable type for storing the recordset returned from a select SQL statement into a list (array) for iterating through.
  • Flask request: to retrieve data from an HTML form and URL querystring.
  • for loop: to iterate through the rows pulled from the cart table we will create with CockroachDB.

The first piece to get out of the way is to create a database connection to CockroachDB using the psycopg2 library for Python.

CockroachDB connection with Python

1
2
3
4
5
6
7
8
9
10
11
import psycopg2
t_dbname = "database name"
t_name_user = "database user name"
t_sslmode = "auto"
t_sslrootcert = 'mycerts/ca.crt'
t_sslkey = 'mycerts/client.maxroach.key'
t_sslcert = 'mycerts/client.maxroach.crt'
t_host = "localhost"
t_port = "26256"
db_conn = psycopg2.connect(database=t_dbname, user=t_name_user, sslmode=t_sslmode, sslrootcert=t_sslrootcert, sslkey=t_sslkey, sslcert=t_sslcert, host=t_host, port=t_port)
db_cursor = db_conn.cursor()

Analysis

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

The next thing we will set up for our Python and CockroachDB shopping cart tutorial is a table called tbl_shopping_cart and add some data to the table.

Create table for shopping cart

1
2
3
4
5
6
7
8
9
10
CREATE TABLE tbl_shopping_cart (
    id serial NOT NULL
    , id_item INT NOT NULL
    , id_user INT NOT NULL
    , t_name_item VARCHAR(64) NOT NULL
    , i_qty INT NOT NULL
    , c_price_per REAL NOT NULL
    , CONSTRAINT tbl_shopping_cart_pkey PRIMARY KEY (id)
    );
CREATE UNIQUE INDEX tbl_shopping_cart_id_idx ON tbl_shopping_cart USING btree (id);

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

Fill CockroachDB table with data

idid_itemid_usert_name_itemi_qtyc_price_per
100123456568Gumby Coffee Mug23.50
101234567568Pokey Socks12.95
102345678491Bugs Bunny Hat35.75
103456789572Speed Racer Gloves115.95

The next part we will build for our shopping cart application with CockroachDB back-end is a dynamic web page that we’ll show the user using Python’s render_template(). This HTML page will show a list of items that exist in the CockroachDB database as you see in the table above, specifically in tbl_shopping_cart for that user. So yes, it is important to keep in mind that tbl_shopping_cart tracks all carts for all users, which is why we need the id_user 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 += ", t_name_item"
s += ", i_qty"
s += ", c_price_per"
s += " FROM tbl_shopping_cart"
s += " WHERE"
s += " ("
s += " id_user = (%id_user)"
s += " )"
s += " ORDER BY t_name_item;"
db_cursor.execute(s, [id_user])
list_cart_contents = db_cursor.fetchall()

Notice we left out the id_item column because all the operations the user needs 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 perspectives, it will be easier to build our dynamic 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 is in the list_cart_contents array looks like. :

idid_usert_name_itemi_qtyc_price_per
100568Gumby Coffee Mug23.50
101568Pokey Socks12.95
102491Bugs Bunny Hat35.75
103572Speed Racer Gloves115.95

Create a dynamic 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 CockroachDB</title>
<style>
body {background-color: #F0F0F0;}
h1 {font-size: 34pt;}
.div-main {margin: auto; padding-left: 12px; padding-right: 12px;}
.div-col {margin: auto;}
.form-caption {margin: auto;}
.form-input {text-align: left; width: 58px; color: #000000; background-color: #FFFF00;}
.form-button {text-align: left; width: auto; color: #DDDDDD;}
</style>
</head>
<body>
<div class='div_tbl'>
    <!-- headings row -->
    <div class='div_row'>
    <div class='div-col'>Item</div>
    <div class='div-col'>Price Each</div>
    <div class='div-col'>Qty</div>
    <div class='div-col'>Total</div>
    <div class='div-col'>Delete</div>
</div>
{%
for i in range(len(list_cart_contents))
    ID = list_cart_contents[0][i]
    t_name_item = list_cart_contents[2][i]
    i_qty = list_cart_contents[3][i]
    c_price_per = list_cart_contents[4][i]
    c_price_row_total = c_price_per * i_qty
%}
    <div class='div_row'>
        <form name='{{ ID }}' action='{{ t_url }}?t_what_action_route=QtyChange&ID={{ ID }}' method='get'>
        <div class='div-col'>{{ t_name_item }}</div>
        <div class='div-col'>{{ c_price_per }}</div>
        <div class='div-col'><input name='{{ ID }}' type='text' size='2' value='{{ i_qty }}' class='form-input'></a></div>
        <div class='div-col'>{{ c_price_row_total }}</div>
        <div class='div-col'><a href='{{ t_url }}?t_what_action_route=DeleteItemFromCart&ID={{ ID }}'>X</a></div>
    </div>
{%
endfor
%}
    <div class='div-col'><input type='submit' value='Update' class='form-button'></div>
    </form>
</div>
</body>
</html>

Analysis

  • for i in range(len(list_cart_contents)): Loop through each item in the “list_cart_contents” multidimensional array, which was passed to this template from our application, as we’ll 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 tells the server to put values in this spot. Here we are 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 row / item.

Now that you have created an HTML “template” to be called up from the CockroachDB application we are 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’ll retrieve 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’ll request data from the form using Flask’s request function.

Python Flask Request Syntax

1
2
3
4
# Querystring using GET.
t_value_from_get = request.args.get([parameter name], [value if a null is received])
# Form submission using POST.
t_value_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, “t_what_action_route” in our Python CockroachDB application.
  • value if a null is received: In case no data was sent from the form, this is the value your t_value_from_get variable will receive. This is a way to trap errors. We often put an empty string here such as “”, as you will see in the final code listing at the bottom of this article.

Python Flask Request Example

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

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

Analysis

  • t_what_action_route: This part of the Python code is like saying to the server, “Look at the querystring buried 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 t_what_action_route.”

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
104
# -----------------------
# 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 CockroachDB connection

# ----------------------
# Connect to CockroachDB
# ----------------------
import psycopg2
t_dbname = "database name"
t_name_user = "database user name"
t_sslmode = "auto"
t_sslrootcert = 'mycerts/ca.crt'
t_sslkey = 'mycerts/client.maxroach.key'
t_sslcert = 'mycerts/client.maxroach.crt'
t_host = "localhost"
t_port = "26256"
db_conn = psycopg2.connect(database=t_dbname, user=t_name_user, sslmode=t_sslmode, sslrootcert=t_sslrootcert, sslkey=t_sslkey, sslcert=t_sslcert, host=t_host, port=t_port)
db_cursor = db_conn.cursor()

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

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

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

# ---------------------------------------------
# Copy shopping cart for THIS user into list
# ---------------------------------------------
    def retrieveAllCartItems(id_user):
    s = ""
    s += "SELECT"
    s += " ID"
    s += ", t_name_item"
    s += ", i_qty"
    s += ", c_price_per"
    s += " FROM tbl_shopping_cart"
    s += " WHERE"
    s += " ("
    s += " id_user = (%id_user)"
    s += " )"
    s += " ORDER BY t_name_item;"
    # Retrieve all the rows from the cursor
    db_cursor.execute(s, [id_user])
    return db_cursor.fetchall()
    db_cursor.close()

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

    # if t_what_action_route is "DeleteItemFromCart" then delete a row
    if t_what_action_route == "DeleteItemFromCart":
        return deleteItemFromCart(id)

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

    # Either way: get new tbl_shopping_cart contents and put it into our array
    id_user = 1
    list_cart_contents = retrieveAllCartItems(id_user)
    # Show the dynamic HTML 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 tutorial we created a web-based shopping cart system with CockroachDB and Python. Along the way we learned and used many functions from both CockroachDB 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 retrieve data from a CockroachDB 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.