How to make a Grocery store Web app with Python, Flask and MySQL (Part-3 How to use Flask to make the actual webapp)

In this tutorial I will teach you how to make a Webapp that will this tutorial in a way that anyone who knows MySQL and basic Python can follow regardless of the fact that they read the last parts or not. This tutorial will be divided into many parts and so will the code to make it more understandable. I will provide a sample code which is made just for teaching purposes and not an actual web app for any work related tasks. I will begin this tutorial with the knowledge you should already have to follow this tutorial, followed by the skills and knowledge you will gain in this tutorial.

Pre-requisite knowledge

• Basic SQL syntax.
• Knowledge of making a Database and tables on MySQL.
• Basic knowledge of pandas.
• Connecting a database to Python.
• Basic HTML.
If you don't have them there are many amazing tutorials on the internet that teach it in an easy to understand and beautiful way.

What you will learn

• Using Flask to make a Webapp
• Integrating the database in the Webapp.
• How to add functions to the buttons and other widgets.
• How to make a Webapp editable.

With that in mind let us begin this tutorial.

i) The modules

We will first start by importing the required modules

from html import escape

import pandas as pd
from flask import Flask, request, redirect, url_for
import mysql.connector

from database_grocery_store import connecting_database

These are just the Python libraries we will be using. Followed by the connecting_database function. Which is just the server's connection which I have written in a different file. Here's why I have chosen to use these modules.

• from html import escape :- It's for security so the user just can't change the website by typing HTML code.

• pandas :- To manage the data easily and more efficiently.

• Flask :- To make the Webapp.

• mysql.connector :- To connect to the database.

• from database_grocery_store import connecting_database :- This is just the function for the connection code, I have used this line because I have the connection code in a different file to make it look nicer and to make the code more readable. If you don't want to do that then don't use this line.

ii) Starting the Flask application

app = Flask(__name__)

This line just starts the Flask application.

iii) Importing the connection.

def get_html_content():
    connection = connecting_database()

I have created a function that just gets the content in the database table. I start this function with the connection, for which I have written the code as a different function in a different file. This makes the code more readable and understandable. You can replace the connection= connecting_database() line with the connection code if you don't have the connection code in a different file.

iv) Using HTML to put the list of entries in the table in rows.

    try:
        # 1. Products
        df_p = pd.read_sql_query("SELECT * FROM products;", connection)
        for _, row in df_p.iterrows():
            product_rows += (
                f"<tr><td><strong>{escape(str(row.get('product_id', '')))}</strong></td>"
                f"<td>{escape(str(row.get('product_name', '')))}</td>"
                f"<td>{escape(str(row.get('category_id', '')))}</td>"
                f"<td>{escape(str(row.get('supplier_id', '')))}</td>"
                f"<td>${row.get('price', 0.0):.2f}</td></tr>"
            )

•We first run the command to get the list of all entries in the products table. df_p = pd.read_sql_query("SELECT * FROM products;", connection) This is a shorter version of the syntax using pandas. Using pandas creates the cursor and makes it easier to run SQL queries on Python.

• product_rows = ""
for _, row in df_p.iterrows():
product_rows +=
We use a simple for loop to get every single entry in the products table, turn it into a string and concatenate it to the end of the string.

• The last part is the HTML code to put each value in its own table. In my case the products table has the following columns
i) Product_id
ii) Product_name
iii) Category_id
iv) supplier_id
v) price

Now that we know how this works we will do the same with the rest of the tables.

# 2. Orders (orders JOIN order_items)
        query_orders = """
            SELECT o.order_id, oi.product_id, oi.quantity, oi.unit_price
            FROM orders o
            JOIN order_items oi ON o.order_id = oi.order_id;
        """
        df_o = pd.read_sql_query(query_orders, connection)
        for _, row in df_o.iterrows():
            order_rows += (
                f"<tr><td><strong>{escape(str(row.get('order_id', '')))}</strong></td>"
                f"<td>{escape(str(row.get('product_id', '')))}</td>"
                f"<td>{escape(str(row.get('quantity', '')))}</td>"
                f"<td>${row.get('unit_price', 0.0):.2f}</td></tr>"
            )

        # 3. Suppliers
        df_s = pd.read_sql_query("SELECT * FROM suppliers;", connection)
        for _, row in df_s.iterrows():
            supplier_rows += (
                f"<tr><td><strong>{escape(str(row.get('supplier_id', '')))}</strong></td>"
                f"<td>{escape(str(row.get('supplier_name', '')))}</td>"
                f"<td>{escape(str(row.get('phone', '')))}</td></tr>"
            )

        # 4. Customers
        df_c = pd.read_sql_query("SELECT * FROM customers;", connection)
        for _, row in df_c.iterrows():
            customer_rows += (
                f"<tr><td><strong>{escape(str(row.get('customer_id', '')))}</strong></td>"
                f"<td>{escape(str(row.get('customer_name', '')))}</td>"
                f"<td>{escape(str(row.get('email', '')))}</td></tr>"
            )

    except mysql.connector.Error as err:

        print(f"Database Query Error: {err}")

    finally:
        connection.close()


    return f"""<!DOCTYPE html>..."""

We end the connection after finishing all four tables, so the program can run more efficiently.

v) The Webapp layout

return f"""<!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Database Dashboard</title>
        <style>
            body {{ font-family: 'Segoe UI', sans-serif; background-color: #f4f7f6; margin: 0; padding: 20px; color: #333; }}
            .dashboard-container {{ max-width: 1200px; margin: 0 auto; }}
            .oval-container {{ background: #ffffff; border-radius: 30px; padding: 25px; box-shadow: 0 4px 15px rgba(0,0,0,0.05); margin-bottom: 30px; border: 1px solid #e0e0e0; overflow-x: auto; }}
            h2 {{ color: #2c3e50; margin-top: 0; }}
            h3 {{ color: #34495e; border-bottom: 2px solid #ecf0f1; padding-bottom: 5px; }}
            table {{ width: 100%; border-collapse: collapse; margin-top: 10px; margin-bottom: 20px; }}
            th, td {{ padding: 10px 12px; text-align: left; border-bottom: 1px solid #ddd; }}
            th {{ background-color: #f8f9fa; color: #2c3e50; }}
            .forms-grid {{ display: grid; grid-template-columns: repeat(auto-fit, minmax(260px, 1fr)); gap: 20px; margin-top: 20px; }}
            .form-card {{ background: white; padding: 20px; border-radius: 15px; box-shadow: 0 4px 10px rgba(0,0,0,0.05); }}
            .form-group {{ margin-bottom: 12px; }}
            label {{ display: block; margin-bottom: 5px; font-weight: 600; font-size: 0.9em; }}
            input {{ width: 100%; padding: 8px; border: 1px solid #ccc; border-radius: 6px; box-sizing: border-box; }}
            button {{ background-color: #3498db; color: white; border: none; padding: 10px 15px; border-radius: 6px; cursor: pointer; width: 100%; font-weight: bold; }}
            button:hover {{ background-color: #2980b9; }}
        </style>
    </head>
    <body>

    <div class="dashboard-container">
        <div class="oval-container">
            <h2> Live Database System Tables</h2>

            <h3>Products</h3>
            <table>
                <thead><tr><th>Product ID</th><th>Product</th><th>Category ID</th><th>Supplier ID</th><th>Price</th></tr></thead>
                <tbody>{product_rows}</tbody>
            </table>

            <h3>Orders</h3>
            <table>
                <thead><tr><th>Order ID</th><th>Product ID</th><th>Quantity</th><th>Unit Price</th></tr></thead>
                <tbody>{order_rows}</tbody>
            </table>

            <h3>Suppliers</h3>
            <table>
                <thead><tr><th>Supplier ID</th><th>Supplier Name</th><th>Phone Number</th></tr></thead>
                <tbody>{supplier_rows}</tbody>
            </table>

            <h3> Customers</h3>
            <table>
                <thead><tr><th>Customer ID</th><th>Customer Name</th><th>Email</th></tr></thead>
                <tbody>{customer_rows}</tbody>
            </table>
        </div>

        <h2>Add New Records</h2>
        <div class="forms-grid">
            <div class="form-card">
                <h3> Add Product</h3>
                <form action="/add-product" method="POST">
                    <div class="form-group"><label>Product Name</label><input type="text" name="product_name" required></div>
                    <div class="form-group"><label>Category ID</label><input type="number" name="category_id" required></div>
                    <div class="form-group"><label>Supplier ID</label><input type="number" name="supplier_id" required></div>
                    <div class="form-group"><label>Price</label><input type="number" step="0.01" name="price" required></div>
                    <button type="submit">Submit Product</button>
                </form>
            </div>

            <div class="form-card">
                <h3> Add Order</h3>
                <form action="/add-order" method="POST">
                    <div class="form-group"><label>Product ID</label><input type="number" name="product_id" required></div>
                    <div class="form-group"><label>Quantity</label><input type="number" name="quantity" required></div>
                    <button type="submit">Submit Order</button>
                </form>
            </div>

            <div class="form-card">
                <h3> Add Supplier</h3>
                <form action="/add-supplier" method="POST">
                    <div class="form-group"><label>Supplier Name</label><input type="text" name="supplier_name" required></div>
                    <div class="form-group"><label>Phone Number</label><input type="text" name="phone"></div>
                    <button type="submit">Submit Supplier</button>
                </form>
            </div>

            <div class="form-card">
                <h3>Add Customer</h3>
                <form action="/add-customer" method="POST">
                    <div class="form-group"><label>Customer Name</label><input type="text" name="customer_name" required></div>
                    <div class="form-group"><label>Email Address</label><input type="email" name="email"></div>
                    <button type="submit">Submit Customer</button>
                </form>
            </div>
        </div>
    </div>
    </body>
    </html>
    """

I expect you all to know at least this much HTML to follow this tutorial, but I will very briefly define the tag and what each tag does in the above given code:-
Document Structure & Metadata

• <!DOCTYPE html>
• Definition: An informative tag that tells your web browser this document is written in HTML5.
• Application: Informs the web browser to analyze the page layout and style this document properly.
• <html>
• Definition: The root tag where you place all contents in a web page.
• Application: Informs your web browser that everything you see below belongs to the HTML Document (notice the tag lang="en").
• <head>
• Definition: A special set of tags where you can place all non-visible information about your document.
• Application: Keeps your page settings, CSS instructions, and document's title in one place.
• <meta>
• Definition: A set of tags that contain specific information about your page's data.
• Application: Tells the web browser to use standard character encoding (UTF-8) and scale the page's appearance nicely on mobile devices (viewport).
• <title>
• Definition: A tag that specifies the name of the document that appears in the tab of your browser.
• Application: Displays "Database Dashboard" at the top of your browser's tab.
• <style>
• Definition: A tag that contains CSS instructions to design your HTML document's content.
• Application: Holds the instructions to color, space, align, and animate your dashboard's elements.
• <body>
• Definition: A powerful tag that keeps all visible parts of your web page.
• Application: Displays all tables, forms, and text that you see in this dashboard.

Layout & Headings

• <div>
• Definition: A tag that creates a box-like structure to hold all your elements.
• Application: Helps you organize your dashboard's layout (dashboard-container), table cards (oval-container), and forms (form-card).
• <h2>
• Definition: A tag that defines level 2 headings (usually bigger than level 3 headings).
• Application: Displays the general structure of your page (" Live Database System Tables" and "Add New Records").
• <h3>
• Definition: A tag that defines level 3 headings (used to identify unique sections within a bigger category).
• Application: Displays the unique sections inside your database ("Products", "Orders") and categories ("Add Product").

Data Display Tables

• <table>
• Definition: A tag that creates a grid/table structure to hold your data.
• Application: Designs the visual appearance of your tables (Products, Orders, Suppliers, Customers).
• <thead>
• Definition: A tag that encloses all the head content of an HTML table.
• Application: Groups all column headers to display them with a different background color.
• <tbody>
• Definition: A tag that encloses all the data rows of an HTML table.
• Application: Holds the actual values that come from your backend database ({product_rows}).
• <tr>
• Definition: A tag that encloses all the table row cells (both headers and data cells).
• Application: Makes a horizontal row of related column cells (one database record).
• <th>
• Definition: A tag that encloses headings (used to mark column headers).
• Application: Displays your table's column names (e.g., "Product ID", "Price").
• <td>
• Definition: A tag that encloses a standard table cell (holds the actual values).
• Application: Holds the actual values that come from your backend database columns.

Interactive Forms & Inputs

• <form>
• Definition: A tag that holds all interactive elements that help your users send data to your database.
• Application: Keeps your form's elements grouped and tells your web browser where to send the data (action="/add-product", method="POST").
• <label>
• Definition: A tag that provides a caption for a specific input element.
• Application: Tells your users what to type in each form input ("Product Name", "Quantity").
• <input>
• Definition: A tag that lets your users type or select data to insert into your database.
• Application: Lets your users type in strings (type="text") or numbers (type="number").
• <button>
• Definition: A tag that creates a clickable button to let users perform actions.
• Application: Submits your form data into the database when the user clicks the "Add" button (type="submit").

vi) To run the get_html_content function when the website is opened.

@app.route('/')
def index():
    return get_html_content()

This just makes the function run when a user visits the website.

vii) To get add data functions.

@app.route('/add-product', methods=['POST'])
def add_product():
    connection = connecting_database()
    cursor = connection.cursor()
    query = "INSERT INTO products (product_name, category_id, supplier_id, price) VALUES (%s, %s, %s, %s)"
    values = (
        request.form.get('product_name'),
        request.form.get('category_id'),
        request.form.get('supplier_id'),
        request.form.get('price'),
    )
    try:
        cursor.execute(query, values)
        connection.commit()
    except mysql.connector.Error as err:
        connection.rollback()
        return f"Database Error: {err}", 500
    finally:
        cursor.close()
        connection.close()
    return redirect(url_for('index'))

Here is how this code block works :-

• We use the @app.route('/add-product' to start the function.

•methods=['POST']) we use the POST part to make sure it is run only when it is submitted like a form.

• We define the function to add a product.

• We start that function by securing a connection to the database.

• We create a cursor and run the insert values query.

• We request to get the values from the input the user gave.

• Then we try to execute the cursor and finish with committing the connection.

• We also have an exception for if this fails. Which rolls back the changes and returns an error message.

• Finally, whether or not the exception is hit, the finally block closes both cursor and connection. After that, we redirect to the index page, which loads the table again with the updated data.

Now that we know how adding works in one table here is the code to add data in all the other tables as well.

@app.route('/add-order', methods=['POST'])
def add_order():
    connection = connecting_database()
    cursor = connection.cursor()

    try:
        # Step 1: Create the main order record
        cursor.execute("INSERT INTO orders () VALUES ()")
        new_order_id = cursor.lastrowid

        # Step 2: Look up the product's current price
        cursor.execute(
            "SELECT price FROM products WHERE product_id = %s",
            (request.form.get('product_id'),),
        )
        result = cursor.fetchone()
        if result is None:
            connection.rollback()
            return "Product ID not found", 400
        unit_price = result[0]

        # Step 3: Insert the order line with the price
        query_items = (
            "INSERT INTO order_items (order_id, product_id, quantity, unit_price) "
            "VALUES (%s, %s, %s, %s)"
        )
        values = (
            new_order_id,
            request.form.get('product_id'),
            request.form.get('quantity'),
            unit_price,
        )
        cursor.execute(query_items, values)
        connection.commit()
    except mysql.connector.Error as err:
        connection.rollback()
        return f"Database Error: {err}", 500
    finally:
        cursor.close()
        connection.close()

    return redirect(url_for('index'))


@app.route('/add-supplier', methods=['POST'])
def add_supplier():
    connection = connecting_database()
    cursor = connection.cursor()

    query = "INSERT INTO suppliers (supplier_name, phone) VALUES (%s, %s)"
    values = (
        request.form.get('supplier_name'),
        request.form.get('phone'),
    )

    try:
        cursor.execute(query, values)
        connection.commit()
    except mysql.connector.Error as err:
        connection.rollback()
        return f"Database Error: {err}", 500
    finally:
        cursor.close()
        connection.close()

    return redirect(url_for('index'))


@app.route('/add-customer', methods=['POST'])
def add_customer():
    connection = connecting_database()
    cursor = connection.cursor()

    query = "INSERT INTO customers (customer_name, email) VALUES (%s, %s)"
    values = (
        request.form.get('customer_name'),
        request.form.get('email'),
    )

    try:
        cursor.execute(query, values)
        connection.commit()
    except mysql.connector.Error as err:
        connection.rollback()
        return f"Database Error: {err}", 500
    finally:
        cursor.close()
        connection

viii) Making the web app run when executed.

if __name__ == '__main__':
    app.run(debug=True)

This makes the entire code run when executed. app.run makes it so the program run on flask's server. Debugging makes it able to give a detailed traceback when an error occurs as well making the server restart when a change is made.

Summary of achievements:-

Congratulations you have made a functioning web application that is connected to a SQL database. You have also learned how to use basic Flask. To take this to the next level you can add a delete product command as well or make more aesthetic changes on the layout perhaps even try to add pictures.