KartikJha-prod

Posted on Oct 13 Edited on Oct 22

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

#python #flask #webdev #mysql

Part	Topic	Link
1	How to use MySQL	Read Part 1
2	How to connect a database to Python	Read Part 2
3	How to use Flask to make the actual web app (you are here)	Part 3

In this tutorial, you will build a Flask web app that connects to a MySQL database, shows the data from four tables on a dashboard, and lets users add new records through forms.

This is the third part of a series, but anyone who knows basic MySQL and Python can follow it, whether or not they read the earlier parts. The code is split into small sections to make it easier to understand. It is sample code made for teaching purposes, not a production web app.

We will begin with the knowledge you should already have, followed by the skills you will gain in this tutorial.

Pre-requisite knowledge

Basic SQL syntax.
Knowledge of making a database and tables in MySQL.
Basic knowledge of pandas.
Connecting a database to Python.
Basic HTML.

If you don't have these, there are many amazing tutorials on the internet that teach them in an easy to understand way.

What you will learn

Using Flask to make a web app.
Integrating the database into the web app.
How to add functions to buttons and other widgets.
How to make a web app editable.

With that in mind, let us begin.

Note: All the code sections below go into one file (for example app.py), in the order they appear.

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 the Python libraries we will be using. Here is why I have chosen each of them:

from html import escape :- It's for security, so a user can't change the website by typing HTML code into a form.
pandas :- To manage the data easily and more efficiently.
Flask :- To make the web app.
mysql.connector :- To connect to the database.
from database_grocery_store import connecting_database :- This is the function that opens the database connection. I keep it in a separate file (from Part 2) to make the code more readable. If you don't want to do that, you can skip this line and write the connection code directly.

If you need a reminder, here is an example of what that file can look like. Yours may differ, so use your own details:

# database_grocery_store.py
import mysql.connector

def connecting_database():
    return mysql.connector.connect(
        host="localhost",
        user="your_username",
        password="your_password",
        database="your_database_name"
    )

ii) Starting the Flask application

app = Flask(__name__)

This line creates the Flask application.

iii) Getting the connection and preparing the rows

def get_html_content():
    connection = connecting_database()

    # Empty strings that will hold the HTML rows of each table
    product_rows = ""
    order_rows = ""
    supplier_rows = ""
    customer_rows = ""

I have created a function that gets the content of the database tables. It starts by opening the connection, which comes from the separate function in the other file. If you don't have that file, replace connecting_database() with your own connection code.

We also create four empty strings. In the next step, each row from the database will be turned into HTML and added to these strings.

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

Everything in this section goes inside get_html_content(), right below the code from the previous section (indented once, with 4 spaces).

    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>"
            )

Here is what this code does:

pd.read_sql_query("SELECT * FROM products;", connection) gets all the entries of the products table. Pandas creates the cursor for us, which makes running SQL queries in Python shorter.
The for loop goes through every row, turns each value into a string, and adds it to the end of product_rows.
escape() makes sure that any HTML typed by a user is shown as plain text instead of being run by the browser.
In my case, the products table has these columns: product_id, product_name, category_id, supplier_id and price.

Note: Depending on your pandas version, you may see a warning saying pandas only officially supports SQLAlchemy connections. It is harmless here, and the code still works.

Now that we know how this works, we will do the same with the rest of the tables. These blocks go inside the same try:, right after the products block.

        # 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()

The orders table is built from a JOIN between orders and order_items, so each row shows one product in one order. We close the connection after reading all four tables (the finally block runs even if an error happens), so the program runs more efficiently.

v) The web app layout

This comes right after the finally block, still inside get_html_content() (indented once). It returns the full HTML page with our rows inserted.

    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>
    """

Why are the CSS curly braces doubled ({{ }})? The page is an f-string, where single braces like {product_rows} insert a Python value. Doubling the braces tells Python to print a literal { or }, which CSS needs.

I expect you to know at least this much HTML to follow this tutorial, but here is a very brief definition of each tag used in the code above.

Document structure and metadata

<!DOCTYPE html>
- Definition: Tells the web browser that the document is written in HTML5.
- Application: Makes the browser display the page layout and style properly.
<html>
- Definition: The root tag that holds all the contents of a web page.
- Application: Everything below it belongs to the HTML document (note the lang="en" attribute).
<head>
- Definition: Holds non-visible information about your document.
- Application: Keeps page settings, CSS and the document's title in one place.
<meta>
- Definition: Holds specific information about your page.
- Application: Sets the character encoding (UTF-8) and scales the page nicely on mobile devices (viewport).
<title>
- Definition: Specifies the name of the document shown in the browser tab.
- Application: Displays "Database Dashboard" in the tab.
<style>
- Definition: Contains the CSS instructions that design your page.
- Application: Holds the instructions for color, spacing and alignment of the dashboard.
<body>
- Definition: Holds all the visible parts of the page.
- Application: Displays all the tables, forms and text of the dashboard.

Layout and headings

<div>
- Definition: Creates a box-like container to group elements.
- Application: Organizes the layout (dashboard-container), the tables card (oval-container) and the forms (form-card).
<h2>
- Definition: A level 2 heading (bigger than level 3).
- Application: Shows the main sections ("Live Database System Tables" and "Add New Records").
<h3>
- Definition: A level 3 heading, used for sub-sections.
- Application: Shows the name of each table ("Products", "Orders") and each form ("Add Product").

Data display tables

<table>
- Definition: Creates a grid to hold data.
- Application: Displays the Products, Orders, Suppliers and Customers tables.
<thead>
- Definition: Groups the header rows of a table.
- Application: Groups the column headers so they can have their own background color.
<tbody>
- Definition: Groups the data rows of a table.
- Application: Holds the values that come from the database (for example {product_rows}).
<tr>
- Definition: A table row.
- Application: One horizontal row of cells (one database record).
<th>
- Definition: A header cell.
- Application: Shows the column names (for example "Product ID", "Price").
<td>
- Definition: A standard data cell.
- Application: Holds one value from a database column.

Interactive forms and inputs

<form>
- Definition: Holds the interactive elements that let users send data.
- Application: Groups the inputs and tells the browser where to send the data (action="/add-product", method="POST").
<label>
- Definition: A caption for an input element.
- Application: Tells users what to type in each box ("Product Name", "Quantity").
<input>
- Definition: Lets users type or select data.
- Application: Accepts text (type="text") or numbers (type="number").
<button>
- Definition: A clickable button.
- Application: Submits the form data when the user clicks it (type="submit").

vi) Running get_html_content() when the website is opened

This code goes at the left edge of the file (not inside the function).

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

This makes the function run whenever a user visits the home page of the website.

vii) Functions to add data

@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:

@app.route('/add-product', methods=['POST']) creates the address that the "Add Product" form sends its data to.
methods=['POST'] makes sure it only runs when a form is submitted.
We define a function to add a product and start it by opening a connection to the database.
We create a cursor and prepare the insert query. The %s placeholders are filled in with the values from the form, which keeps the query safe from SQL injection.
request.form.get(...) reads the values that the user typed in the form.
We try to execute the query and commit the change.
If it fails, the except block rolls back the changes and returns an error message.
The finally block closes both the cursor and the connection, whether or not an error happened.
After that, we redirect to the home page, which loads the tables again with the updated data.

Now that we know how adding works for one table, here is the code to add data to the other tables.

@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.close()

    return redirect(url_for('index'))

A quick note on add_order(): it first creates an empty row in orders (this works because the other columns have default or auto-increment values, as set up in Part 1), then looks up the product's current price, and finally saves the order line with that price in order_items.

viii) Making the web app run when executed

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

This starts the web app when you run the file. app.run starts Flask's built-in server. debug=True shows a detailed traceback when an error occurs and restarts the server whenever you change the code.

Run the file (for example python app.py) and open http://127.0.0.1:5000 in your browser.

Warning: debug=True is only for development on your own computer. Never use it on a public server.

Summary of achievements

Congratulations! You have made a functioning web app 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 command for products.
Make more aesthetic changes to the layout.
Try adding pictures.
Add a login system, since this teaching app lets anyone add records.

DEV Community