Shopping Cart with Python and Postgres

Introduction

In this tutorial we will set up an HTML screen to provide users a method for modifying an online shopping cart with Python and Postgres. The Python Postgres application we build here will make use of many Postgres and Python functions, including:

  • Python List to save all records from a select SQL statement into an array for looping.
  • for loop to loop through all the rows pulled from the cart table we will build in PostgreSQL.

Prerequisites

Following are some Python and Postgres skills you may want to be conversant with in order to most easily follow this tutorial:

  • Python coding: We used Python 3.8, which is the latest version of Python as of the day when we wrote this article. You are not obligated to use this new a version. Either way, you can download Python free here.

  • Writing SQL: Some knowledge of how to write and execute queries for PostgreSQL. In the lessons here, we only need to use fairly simple queries, including use of SELECT, WHERE, and ORDER BY. For managing our Postgres database installation, we prefer dBeaver’s tool for its clear and easy-to-use interface, query debugging, and many other features.

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

Postgres Database connection with Python

import psycopg2
t_host = "Postgres database host URL"
t_port = "5432"
t_dbname = "database name"
t_name_user = "database user name"
t_password = "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()

Analysis

  • import psycopg2: The psycopg2 framework with the components necessary for connecting to Postgres, executing queries, and creating a database cursor.
  • t_host: If your database is on your local computer, use “localhost”, otherwise, a URL or IP address will be needed.
  • t_port: “5432” is the default port for postgreSQL servers.
  • t_dbname: The name of your database.
  • t_name_user: The name you set up to access the Postgres database from Python.
  • t_password: Password for the above user.
  • db_conn: Connection to your PostgreSQL server.
  • db_cursor: Cursor for reading/writing from/to the database with Python.

The next element we will set up for our Python and Postgres shopping cart exercise is a table called tbl_cart and add some arbitrary data to the table.

Create table for shopping cart

CREATE TABLE tbl_cart (
    id serial NOT NULL
    , id_product INT NOT NULL
    , id_user INT NOT NULL
    , t_name_product VARCHAR(64) NOT NULL
    , i_quantity INT NOT NULL
    , c_price_each REAL NOT NULL
    , CONSTRAINT tbl_cart_pkey PRIMARY KEY (id)
    );
CREATE UNIQUE INDEX tbl_cart_id_idx ON tbl_cart USING btree (id);

Once we create tbl_cart, let’s fill it with four rows of sample data.

Fill Postgres table with data

Both PGadmin and DBeaver provide an easy interface for adding data to our new PostgreSQL table.

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

The next item we will build for our Python application for creating a shopping cart with Postgres back-end is the dynamic HTML page that we’ll display to the user with Python’s render_template function. This page will display a list of products that exist in the Postgres database, specifically in tbl_cart for that user. So yes, it is important to keep in mind that tbl_cart tracks all carts for all users, which is why we need the id_user column to show the current user only their cart contents.

Query shopping cart with Python

s = ""
s += "SELECT"
s += " ID"
s += ", t_name_product"
s += ", i_quantity"
s += ", c_price_each"
s += " FROM tbl_cart"
s += " WHERE"
s += " ("
s += " id_user = 568"
s += " )"
s += " ORDER BY t_name_product;"
db_cursor.execute(s)
array_cart = db_cursor.fetchall()

Note that we left out the ID_product field because all the operations the user needs can be served via the unique id column.

Now that we have a visual representation of our shopping cart table from two perspectives, it will be easier to create our dynamic HTML page to display that data to the user, as well as allow them to change the quantity of any given item in their cart, as well as remove the product from their cart by clicking the “X”.

Here’s what the data that is now in the array_cart Python list looks like. :

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

Build dynamic HTML page

<html>
<head></head>
<body>
<div class='div_table'>
{%
for i in range(len(array_cart))
    ID = array_cart[0][i]
    t_name_product = array_cart[2][i]
    i_quantity = array_cart[3][i]
    c_price_each = array_cart[4][i]
    c_price_row_total = c_price_each * i_quantity
%}
    <div class='div_row'>
        <form name='{{ ID }}' action='{{ t_url }}?t_do_what=QuantityChange&ID={{ ID }}' method='get'>
        <div class='div_column'>{{ t_name_product }}</div>
        <div class='div_column'>{{ c_price_each }}</div>
        <div class='div_column'><input name='{{ ID }}' type='text' size='2' value='{{ i_quantity }}'></a></div>
        <div class='div_column'>{{ c_price_row_total }}</div>
        <div class='div_column'><a href='{{ t_url }}?t_do_what=DeleteItem&ID={{ ID }}'>X</a></div>
        <div class='div_column'><input type='submit' value='Update'></div>
        </form>
    </div>
{%
endfor
%}
</div>
</body>
</html>

Analysis

  • for i in range(len(array_cart)): Loop through each item in the “array_cart” multidimensional list (array), which was passed to this template from our application, as we’ll see soon.
  • div: Used in HTML to encapsulate so that javascript, styles, and other modifications can be made to the values between the div and close div tags. We left out the css here required to lay the div contents out in a table-like format.
  • {{ 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 are using a form so that we can use an input text box for the user to both see and change quantity for any given product / row.
  • endfor: Typically with Python we don’t need a “next” or “endfor” kind of statement to set the end of a for loop. Dyanmic HTML templates from Flask require this because we can not rely on indentation for Python to know the “bottom” of the loop.

Now that we have created a dynamic HTML “template” to be summoned from the Postgres application we are building in Python, make sure to save the HTML file in a folder off the root of your server called “templates\”. Save the file as “cart.html” in that folder.

Now we’ll look at how we get data the user sent us from the dynamic template HTML page and enact functions within our Python app accordingly.

Get data from HTML form with Python

First, we’ll learn how to request data from an HTML form using request from Flask. Our application will only be using the GET form of request, so you can ignore the “request.form.get” for POST that you see below, at least for THIS application. If the “METHOD” parameter of our FORM tag had been set to “POST”, we would be using “request.form.get”.

Python Request Syntax

t_value_from_get = request.args.get([field name], [value if null value is received]) # for GET; querystring.
t_value_from_post = request.form.get(field name, [value if null value is received]) # for POST; form submission.

Analysis

  • field name: This is the name of the field (how it was named in your querystring or form). For example, “t_do_what” in our Python Postgres application.
  • value if null received: In case no data was submitted, this is the value your t_value_from_get variable will receive. This is a way to trap 404 errors. We often put an empty string here, like “”, as you will see in the final code listing of our application.

Python Request Example

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

t_do_what = request.args.get("t_do_what", "")

Analysis

  • t_do_what: This part of the Python script is like asking the server, “Look at the querystring buried in the URL, find the ‘?’. After that question mark symbol, look for ‘t_do_what=’ and put the value that comes after that equal sign symbol into a local variable we named t_do_what.”

Source code for Python shopping cart

# -----------------------
# 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 PostgreSQL connection

# ---------------------
# Connect to PostgreSQL
# ---------------------
t_host = "Postgres database address"
t_port = "5432"
t_dbname = "database"
t_name_user = "user name"
t_password = "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.route("/Main", methods=["GET"])

# ------------------------
# Function to delete a row
# ------------------------
def DeleteRow(id):
    # delete table SQL
    s = ""
    s += "DELETE * FROM tbl_cart"
    s += " WHERE id=(%id)"
    # execute the delete row command we built
    db_cursor.execute(s, [id])
    # if autocommit is off:
    db_conn.commit()

# ---------------------------
# Function to change quantity
# ---------------------------
def ChangeQuantity(id, i_quantity):
    # delete table SQL
    s = ""
    s += "UPDATE tbl_cart SET"
    s += " i_quantity=(%i_quantity_)"
    s += " WHERE id=(%id)"
    # execute the command we built above to change quantity of that row
    db_cursor.execute(s, [id, i_quantity])
    # if autocommit is off:
    db_conn.commit()

# ---------------------------------------------
# Function to copy cart for THIS user into list
# ---------------------------------------------
    def GetCartContents(id_user):
    s = ""
    s += "SELECT"
    s += " ID"
    s += ", t_name_product"
    s += ", i_quantity"
    s += ", c_price_each"
    s += " FROM tbl_cart"
    s += " WHERE"
    s += " ("
    s += " id_user = (%id_user)"
    s += " )"
    s += " ORDER BY t_name_product;"
    # Retrieve all the rows from the cursor
    db_cursor.execute(s, [id_user])
    return db_cursor.fetchall()
    db_cursor.close()

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

    # if t_do_what is "DeleteItem" then delete a row
    if t_do_what == "DeleteItem":
        return DeleteRow(id)

    # if t_do_what is "ChangeQuantity" then update table with new quantity
    if t_do_what == "ChangeQuantity":
        return ChangeQuantity(id, i_quantity)

    # Either way: get new tbl_cart contents and put it into our array
    id_user = 508
    array_cart = GetCartContents(id_user)
    # Show the dynamic HTML page to user
    t_url = "[URL to application]" # be sure to fill this in!
    return render_template("cart.html", array_cart = array_cart, t_url = t_url)

Conclusion

In this tutorial we created an online shopping cart with Python and PostgreSQL. We learned each part, step by step, and then put it all together to create a Python application utilizing PostgreSQL as our back end. Along the way we learned and used many other functions from both PostgreSQL and Python, including use of a multidimensional list, how to use a FOR loop, how to render a Python template, how to retrieve data from a user via an HTML form, and how to use fetchall to get data from a PostgreSQL query into a Python 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.