How to make a Grocery store Web app with Python, Flask and MySQL (Part-1 How to use MySQL)

In this 3-part article series, I will walk you through the process of making a web app for something as simple as the list of orders, products, suppliers, etc., of a grocery store. I will teach you how to use MySQL to make a database, how to connect it with Python, and how to make the web application.

Why learn this?

This series will be a great way to learn web development. You will learn to make a functioning web app and, more importantly, learn how to use MySQL, a popular tool for making and managing databases, as well as learn to use the Flask module, a popular module for making a web application. By the end of this series, you will have a web application that will manage the records of a grocery store.

In this part, you will learn to use MySQL to make and manage databases.

What is MySQL?

MySQL is a popular database management software that uses the SQL language to function. MySQL is so common due to its strong security and high performance.

How to get MySQL?

MySQL is a paid service, well, commercially at least. Other than that, it's free for the community. You can go to the official MySQL website to download MySQL for Windows, Linux, macOS, etc., or click the link I have provided.
https://www.mysql.com/downloads/

On the website, go to the downloads section and download the program. Then install it directly onto your PC and enter a username and password to set it up.

How to use MySQL

MySQL is a very simple application, mainly because SQL is easily understandable even to someone who doesn't know anything about it. This section will be divided into many steps.

Step 1 Making a database

In your application, simply type CREATE Database {database name};. In my case, I decided to make a simple grocery store web app, though you can still follow the series to make web apps for similar things. That's why I named my database Grocery_store_db, so the command I entered was CREATE database Grocery_store_db;, and I ran the command. To check if your database has been created, you can enter the command SHOW databases;, and you should see a database with the name you entered. In my case, it shows Grocery_store_db.

Step 2 Making tables

Now that we have a database, we want to enter a table in the database. Each table will have columns, and each column will have a datatype, which just means that a column will allow only one type of input. For example, in a column for names you don't want numbers, and in a column for phone numbers you don't want letters, so we can use datatypes and constraints to stop these inputs. There are a lot of datatypes, but the ones we will use are:-

VARCHAR(x):- This datatype allows any input with numbers or letters, with a specified limit of characters written inside the () parentheses instead of the x I have written.

INT :- Allows any integer as a valid input. It doesn't change the input to a string.

Decimal(x,y) :- The decimal datatype is just any number with a decimal in it, like the price of an item, 37.92. The x is the total number of numbers allowed, while y is the number of numbers after the decimal point. For a price in a grocery store, it is typically Decimal(10,2), where numbers like 68373.67 are allowed but 7273.789 isn't allowed because it has 3 Digits after the decimal, so we can use this datatype to bind the decimal numbers.

Some constraints are:-

•AUTO_INCREMENT :- This just counts the number automatically. For example, the first row will have serial no. 1, then 2, and if I don't enter a serial no., it automatically enters 3.

•NOT NULL :- Just says the column can't be empty.

•PRIMARY KEY :- A unique id for each row. For example, two students can have the same name but not the same admission number. So in this case, the admission number column is the PRIMARY KEY. Each table has only one primary key.

•FOREIGN KEY :- A key to join two tables by pointing to the primary key in the joined table.

•UNIQUE:- As the name says, the data in it has to be unique like the primary key, but it can be applied to multiple columns in a table, unlike the primary key.

With all that said, to create a table we will just use the following query.

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    join_date DATE DEFAULT (CURRENT_DATE)
);

By the way, the Default(Current date) part works only on MySQL 8.0.13 or newer versions.

This is the customers table in the Grocery_store_db database. If the table was created in a different database, then you can enter the following query.

USE Grocery_store_db; 

Enter it before running the create table command. With that in mind, here are the general queries for the rest of the tables.

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    description VARCHAR(255)
);

CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT PRIMARY KEY,
    supplier_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20)
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    supplier_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Step 3 Entering values in the table

Now that we have made the tables, we want to add data to them. For that, we will use the following commands:-

Insert Into () The command is quite self-explanatory. It inserts the data specified in the parentheses into the table whose name is written after Into.

In my case, the queries for the data looked something like this.

INSERT INTO categories (category_id, category_name, description) VALUES
(1, 'Vegetables', 'Fresh fruits and vegetables'),
(2, 'Dairy & Eggs', 'Milk, cheese, butter, and eggs'),
(3, 'Bakery', 'Freshly baked bread, muffins, and pastries'),
(4, 'Meat & Seafood', 'Fresh meat, poultry, and fish'),
(5, 'Pantry', 'Canned goods, pasta, sauces');

INSERT INTO suppliers (supplier_id, supplier_name, phone) VALUES
(1, 'Local Farm Dist.', NULL),
(101, 'GreenValley Organics', '555-0192'),
(102, 'DailyDairy Co.', '555-0143'),
(103, 'Sunrise Baking', '555-0177'),
(104, 'Prime Cuts Distributing', '555-0188'),
(105, 'Ocean Catch Seafood', '555-0211'),
(106, 'JOE''S MEAT HOUSE', '627-6969'),
(107, 'Black''s farm', '765-4409');

INSERT INTO customers (customer_id, customer_name, email) VALUES
(501, 'David Miller', '[e-mail]'),
(502, 'Sarah Wilson', '[e-mail]'),
(503, 'Michael Chang', '[e-mail]'),
(504, 'Emma Watson', '[e-mail]'),
(505, 'Ben Carter', '[e-mail]'),
(506, 'Harvey Spectre', '[e-mail]');

INSERT INTO products (product_id, product_name, category_id, supplier_id, price) VALUES
(1, 'Organic Honeycrisp Apples', 1, 101, 2.99),
(2, 'Fresh Spinach (Bag)', 1, 101, 1.99),
(3, 'Whole Milk 1G', 2, 102, 3.49),
(4, 'Cheddar Cheese Block', 2, 102, 4.49),
(5, 'Sourdough Bread', 3, 103, 3.99),
(6, 'Ribeye Steak', 4, 104, 12.99),
(7, 'Atlantic Salmon Fillet', 4, 104, 9.99),
(8, 'Spaghetti Pasta', 5, 101, 1.29),
(11, 'Potatoes', 1, 1, 0.50),
(12, 'Pork Chops 16oz', 4, 104, 19.99),
(13, 'Salmon', 4, 104, 25.00);

INSERT INTO orders (order_id, customer_id) VALUES
(1001, 501), (1002, 502), (1003, 503), (1010, 504), (1011, 505);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1001, 1, 2, 2.99),
(1001, 3, 1, 3.49),
(1001, 5, 1, 3.99),
(1002, 6, 1, 12.99),
(1002, 2, 2, 1.99),
(1002, 8, 3, 1.29),
(1003, 6, 1, 12.99),
(1010, 2, 6, 1.99),
(1011, 6, 9, 12.99);

Step 4 Joining tables

You may have noticed that some tables in the sample database I have provided feel quite empty. For example, the orders table is just one line. Well, the reason is that the other columns, for example the customer ID that the orders table uses, are written in the customers table. It is really inefficient and doesn't really work that well if we were to enter them separately, so we use the join command. By the way, the SELECT function just specifies what to get, and FROM tells the function where to get it from.

JOIN :- This joins two columns from different tables.

When entered, it should look something like this.

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;

Using the JOIN command, we have to enter each column name for specification.

The rest of the code looks something like this.

SELECT o.order_id, p.product_name, oi.quantity, oi.unit_price,
       oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_id;

SELECT o.order_id, c.customer_name, c.email,
       p.product_name, oi.quantity, oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_id;

SELECT p.product_id, p.product_name, c.category_name,
       s.supplier_name, p.price
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN suppliers s ON p.supplier_id = s.supplier_id;

If you are lazy like me and not actually using real data, feel free to just use sample databases like I did.

And that's it for this part.

What you learned

•Basic SQL syntax
•How to make databases
•How to make tables
•Just working with MySQL in general at an amateur level.

In the next part, I will teach you how to connect this database to Python so you can run commands directly from Python.