CRUD sample using Flask and MySQL Database

This is an article showing how to create python web applications using the Flask Framework. Flask is an easy to use python framework suited for low complexity web applications. This is particularly useful for beginners who are just starting web development using python.

The following sample shows how to implement CRUD operations on a MySQL database from a Flask web application. For MySQL integration to python, PyMySQL library is used. By following this sample, learn Python Flask web application development and also how to do the create, read, update and delete tasks on a MySQL database.
Contents
Initial Setup

Install PyMySQL

Check if pymysql installed successfully

Install Python Flask

Web Application Initial Setup
Setting up default page

MySQL Database Setup

Fetch and List Items

Insert new item
Update an existing item

Delete an Item

Flask CRUD with MySQL - Complete code

Other web app samples
Initial Setup

Download and install the latest version of python from Python Website if not already done.

Keep your favourite Python editor ready. This can be a basic notepad application available by default for your OS or you can download and install any free editor like VS Code.

Setting up virtual environment is not a must, but its is good to keep all your projects within a virtual environment. Here, we will proceed with a virtual environment.

For that, go to the command prompt and cd to the folder where you are going to keep your project.
Run command python -m venv CarSales to create a project folder CarSales and a virtual environment for it.
Activate the VE using command:

For Windows:

CarSales\Scripts\activate.bat

For Linux, macOS:

source CarSales /bin/activate

After VE activation, from cmd type cd CarSales to navigate to the project folder.
Install PyMySQL

For MySQL connection we are going to use the pymysql library. For quick reference on pymysql click pymysql basics.
Install it using the command:
pip install pymysql
Check if pymysql installed successfully

Type pip freeze. If installation successful, we will get below output.
```
PyMySQL==1.0.2
```
Install Flask

To install flask, run below command. This will install flask and associated libraries.
pip install flask

Run pip freeze to check if flask installed successfully. Should give an output like this.
```
click==8.0.3
colorama==0.4.4
Flask==2.0.2
itsdangerous==2.0.1
Jinja2==3.0.3
MarkupSafe==2.0.1
PyMySQL==1.0.2
Werkzeug==2.0.2
```
Web Application Initial Setup

Create a file named carsales.py (you can provide any name) inside CarSales folder and copy below code to it. carsales.py is going to contain your start-up code which is required to run your webapp. Copy below code to the file. From command prompt navigate to CarSales folder and execute command python carsales.py
```
from flask import Flask

carsales = Flask(__name__)

if(__name__ == "__main__"):
    carsales.run()
```
This code imports Flask module and creates an object carsales of Flask class. Last 2 lines serves as the entry point of the app and starts the server. In cmd, we will see the below message.
```
    * Serving Flask app 'carsales' (lazy loading)
    * Environment: production
    WARNING: This is a development server.
	Do not use it in a production deployment.
    Use a production WSGI server instead.
    * Debug mode: off
    * Running on http://127.0.0.1:5000/
	(Press CTRL+C to quit)
```
Copy the url http://127.0.0.1:5000/ to your browser and run. We will get the Not Found message as below.

This means that the Flask has handled the server set up and our application is ready. We will now have to set up our html pages and routing to handle page navigations.
Setting up default page

We are going to keep a list page as the first page of the application.
In Flask, all our HTML should be placed inside a folder named templates and static files like javascript or stylesheet should be placed inside static folder.So, create a folder structure as below within your application folder and add html file carslist.html inside templates folder.

Add below code to carslist.html. To handle routing for this page, modify carsales.py as below. This code imports render_template module and sets up the default route.
Copied

from flask import Flask, render_template carsales = Flask(__name__) @carsales.route("/") #For default route def main(): return render_template("carslist.html") if(__name__ == "__main__"): carsales.run()
Copied

<!DOCTYPE html> <html lang="en-US" dir="ltr"> <head> <title>Cars List</title> </head> <body> <h2>Cars List</h2> </body>
In command prompt. click Ctrl + C to stop the application and run command python carsales.py
This process is to be done each time we make any changes and want to reload the page. Some times, after stopping flask web from command prompt, just reloading the html will not give you the desired output. If any such issues are faced, close the web page and reopen.

MySQL Database Setup

Connect to your MySQL instance and create a database carsales. Set up login for the database (Not covered here). Create a table TblCars with below structure and insert below set of data.

CREATE TABLE TblCars (
    ID int,
    Name varchar(100),
    Year int,
    Price float
);

INSERT INTO TblCars VALUES (1, 'Toyota Camry', 2018, 2000);
INSERT INTO TblCars VALUES (2, 'Honda Civic', 2019, 2200);
INSERT INTO TblCars VALUES (3, 'Chevrolet Silverado', 2017, 1800);
INSERT INTO TblCars VALUES (4, 'Ford F-150', 2020, 2500);
INSERT INTO TblCars VALUES (5, 'Nissan Altima', 2021, 3000);

Fetch and List Items

carslist.html is going to hold the list of cars available for sale. To list the items, add below code in body of html and modify carsales.py main method as below. Also import pymysql. In python file, we are reading data from MySQL database carsales using pymysql built-in functions as shown below and pass it to html as a parameter. In html, we are looping through the items and list it as table rows.


from flask import Flask, render_template
import pymysql

carsales = Flask(__name__)

def connection():
    s = '' #Your server(host) name 
    d = 'carsales' 
    u = '' #Your login user
    p = '' #Your login password
    conn = pymysql.connect(host=s, user=u, password=p, database=d)
    return conn

@carsales.route("/")
def main():
    cars = []
    conn = connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM TblCars")
    for row in cursor.fetchall():
        cars.append({"id": row[0], "name": row[1], "year": row[2], "price": row[3]})
    conn.close()
    return render_template("carslist.html", cars = cars)

if(__name__ == "__main__"):
    carsales.run()


<table class="table">
    <thead>
        <tr>
        <th scope="col">Id</th>
        <th scope="col">Name</th>
        <th scope="col">Year</th>
        <th scope="col">Price</th>
        </tr>
    </thead>
    <tbody>
        {% for car in cars %}
            <tr>
                <td>{{ car.id }}</th>
                <td>{{ car.name }}</td>
                <td>{{ car.year }}</td>
                <td>{{ car.price }}</td>
            </tr>
        {% endfor %}
    </tbody>
</table>

For styling we will use Bootstrap 5. Add below 2 lines inside head of html.


    <link rel="stylesheet" crossorigin="anonymous"
	href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" >
    
	<script crossorigin="anonymous"
	src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" >
	</script>

On rerun, we will get below output window.

Insert new item

Add a new button in the carslist.html above the table.

    <a class="btn btn-primary" href="addcar">Add New Car</a>

Add new html file, addcar.html and place below code. Add below code in caresales.py as the second route below main method.


@carsales.route("/addcar", methods = ['GET','POST'])
def addcar():
    if request.method == 'GET':
        return render_template("addcar.html")
    if request.method == 'POST':
        id = int(request.form["id"])
        name = request.form["name"]
        year = int(request.form["year"])
        price = float(request.form["price"])
        conn = connection()
        cursor = conn.cursor()
        cursor.execute("INSERT INTO TblCars (id, name, year, price) VALUES (%s, %s, %s, %s)", (id, name, year, price))
        conn.commit()
        conn.close()
        return redirect('/')


<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Car Details</title>
    
    <link rel="stylesheet" crossorigin="anonymous"
	href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" >
    
	<script crossorigin="anonymous"
	src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" >
	</script>

</head>

<body>
    <h2>Car Details</h2>

    <form action="" method = "POST">
        <div class="form-group row">
          <label for="id">Id</label>
          <input type="text" class="form-control" name="id" id="id">
        </div>
        <div class="form-group row">
            <label for="name">Maker and Model</label>
            <input type="text" class="form-control" name="name" id="name">
        </div>
        <div class="form-group row">
            <label for="year">Make Year</label>
            <input type="text" class="form-control" name="year" id="year">
        </div>
        <div class="form-group row">
            <label for="price">Sell Price</label>
            <input type="text" class="form-control" name="price" id="price">
        </div>
        <button type="btnAdd" class="btn btn-primary">Submit</button>
        <a href="/" class="btn btn-primary">Cancel</a>
      </form>
</body>

Now we have to import request and redirect. For that, change first line as below.

from flask import Flask, render_template, request, redirect

In the python code, we are defining a new route for addcar. We are using the same method for GET and POST. GET is just rendering addcar.html. In POST, we are reading data from form controls and save data to table and then redirect to list page.

In HTML page, we are defining a form with 4 text boxes. Each has a name provided. This name is used in the python file while reading data from controls.
On rerun, we can navigate to Create page as below and add new Car.

Update an existing item

For update and delete we will add 2 links in the carslist.html page as below.


<table class="table">
    <thead>
        <tr>
        <th scope="col">Id</th>
        <th scope="col">Name</th>
        <th scope="col">Year</th>
        <th scope="col">Price</th>
        <th scope="col">Edit</th>
        <th scope="col">Delete</th>
        </tr>
    </thead>
    <tbody>
        {% for car in cars %}
            <tr>
                <td>{{ car.id }}</th>
                <td>{{ car.name }}</td>
                <td>{{ car.year }}</td>
                <td>{{ car.price }}</td>
                <td><a href='updatecar/{{ car.id }}'>Edit</a></td>
                <td><a href='deletecar/{{ car.id }}'>Delete</a></td>
            </tr>
        {% endfor %}
    </tbody>
</table>

For easier understanding, we are going to keep separate routes for create and update, although we are using the same addcar.html for update also. For update we will provide the route name updatecar.
In addcar.html, in order to populate the values, we have to use the value attribute and bind the car object values as in html provided below.

In carsales.py, add new updatecar route. In GET, we are filtering details based on selected id and using fetchall() function, assigning it to a list variable and pass it to the addcar.html page.
In POST, we are using the update command and commit
Note: We have to disable Id textbox to not make changes to Id. Here we are not doing it and if Id is changed no update will happen.


@carsales.route('/updatecar/<int:id>',methods = ['GET','POST'])
def updatecar(id):
    cr = []
    conn = connection()
    cursor = conn.cursor()
    if request.method == 'GET':
        cursor.execute("SELECT * FROM TblCars WHERE id = %s", (id))
        for row in cursor.fetchall():
            cr.append({"id": row[0], "name": row[1], "year": row[2], "price": row[3]})
        conn.close()
        return render_template("addcar.html", car = cr[0])
    if request.method == 'POST':
        name = str(request.form["name"])
        year = int(request.form["year"])
        price = float(request.form["price"])
        cursor.execute("UPDATE TblCars SET name = %s, year = %s, price = %s WHERE id = %s", (name, year, price, id))
        conn.commit()
        conn.close()
        return redirect('/')


<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Car Details</title>
    
    <link rel="stylesheet" crossorigin="anonymous"
	href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" >
    
	<script crossorigin="anonymous"
	src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" >
	</script>

</head>

<body>
    <h2>Car Details</h2>

    <form action="" method = "POST">
        <div class="form-group row">
          <label for="id">Id</label>
          <input type="text" class="form-control" name="id" id="id" value="{{car.id}}">
        </div>
        <div class="form-group row">
            <label for="name">Maker and Model</label>
            <input type="text" class="form-control" name="name" id="name" value="{{car.name}}">
        </div>
        <div class="form-group row">
            <label for="year">Make Year</label>
            <input type="text" class="form-control" name="year" id="year" value="{{car.year}}">
        </div>
        <div class="form-group row">
            <label for="price">Sell Price</label>
            <input type="text" class="form-control" name="price" id="price" value="{{car.price}}">
        </div>
        <button type="btnAdd" class="btn btn-primary">Submit</button>
        <a href="/" class="btn btn-primary">Cancel</a>
      </form>
</body>

After the above changes, update will work, but create will throw an error. To fix this, change the render_template line of addcar as below.

return render_template("addcar.html", car = {})

We are binding an empty car object to make it working.

Delete an Item

For delete, we will add a new route deletecar. And just need to provide delete query as below. If needed, a confirm popup also can be added.
Copied
```
@carsales.route('/deletecar/<int:id>')
def deletecar(id):
    conn = connection()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM TblCars WHERE id = %s", (id))
    conn.commit()
    conn.close()
    return redirect('/')
```

Flask CRUD with MySQL - Complete code

Complete code is placed below.


from flask import Flask, render_template, request, redirect
import pymysql

carsales = Flask(__name__)

def connection():
    s = '' #Your server(host) name 
    d = 'carsales' 
    u = '' #Your login user
    p = '' #Your login password
    conn = pymysql.connect(host=s, user=u, password=p, database=d)
    return conn

@carsales.route("/")
def main():
    cars = []
    conn = connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM TblCars")
    for row in cursor.fetchall():
        cars.append({"id": row[0], "name": row[1], "year": row[2], "price": row[3]})
    conn.close()
    return render_template("carslist.html", cars = cars)
@carsales.route("/addcar", methods = ['GET','POST'])
def addcar():
    if request.method == 'GET':
        return render_template("addcar.html", car = {})
    if request.method == 'POST':
        id = int(request.form["id"])
        name = request.form["name"]
        year = int(request.form["year"])
        price = float(request.form["price"])
        conn = connection()
        cursor = conn.cursor()
        cursor.execute("INSERT INTO TblCars (id, name, year, price) VALUES (%s, %s, %s, %s)", (id, name, year, price))
        conn.commit()
        conn.close()
        return redirect('/')
@carsales.route('/updatecar/<int:id>',methods = ['GET','POST'])
def updatecar(id):
    cr = []
    conn = connection()
    cursor = conn.cursor()
    if request.method == 'GET':
        cursor.execute("SELECT * FROM TblCars WHERE id = %s", (id))
        for row in cursor.fetchall():
            cr.append({"id": row[0], "name": row[1], "year": row[2], "price": row[3]})
        conn.close()
        return render_template("addcar.html", car = cr[0])
    if request.method == 'POST':
        name = str(request.form["name"])
        year = int(request.form["year"])
        price = float(request.form["price"])
        cursor.execute("UPDATE TblCars SET name = %s, year = %s, price = %s WHERE id = %s", (name, year, price, id))
        conn.commit()
        conn.close()
        return redirect('/')
@carsales.route('/deletecar/<int:id>')
def deletecar(id):
    conn = connection()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM TblCars WHERE id = %s", (id))
    conn.commit()
    conn.close()
    return redirect('/')

if(__name__ == "__main__"):
    carsales.run()


<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Cars List</title>
    
    <link rel="stylesheet" crossorigin="anonymous"
	href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" >
    
	<script crossorigin="anonymous"
	src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" >
	</script>

</head>

<body>
    <h2>Cars List</h2>
    <a class="btn btn-primary" href="addcar">Add New Car</a>
    <table class="table">
        <thead>
            <tr>
            <th scope="col">Id</th>
            <th scope="col">Name</th>
            <th scope="col">Year</th>
            <th scope="col">Price</th>
            <th scope="col">Edit</th>
            <th scope="col">Delete</th>
            </tr>
        </thead>
        <tbody>
            {% for car in cars %}
                <tr>
                    <td>{{ car.id }}</th>
                    <td>{{ car.name }}</td>
                    <td>{{ car.year }}</td>
                    <td>{{ car.price }}</td>
                    <td><a href='updatecar/{{ car.id }}'>Edit</a></td>
                    <td><a href='deletecar/{{ car.id }}'>Delete</a></td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
</body>


<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Car Details</title>
    
    <link rel="stylesheet" crossorigin="anonymous"
	href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" >
    
	<script crossorigin="anonymous"
	src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" >
	</script>

</head>

<body>
    <h2>Car Details</h2>

    <form action="" method = "POST">
        <div class="form-group row">
          <label for="id">Id</label>
          <input type="text" class="form-control" name="id" id="id" value="{{car.id}}">
        </div>
        <div class="form-group row">
            <label for="name">Maker and Model</label>
            <input type="text" class="form-control" name="name" id="name" value="{{car.name}}">
        </div>
        <div class="form-group row">
            <label for="year">Make Year</label>
            <input type="text" class="form-control" name="year" id="year" value="{{car.year}}">
        </div>
        <div class="form-group row">
            <label for="price">Sell Price</label>
            <input type="text" class="form-control" name="price" id="price" value="{{car.price}}">
        </div>
        <button type="btnAdd" class="btn btn-primary">Submit</button>
        <a href="/" class="btn btn-primary">Cancel</a>
      </form>
</body>

Other web app samples

Crud Samples implemented using different frameworks and databases.

Flask

Flask CRUD with JSON

Flask CRUD with SQL Server

Flask CRUD with MySQL

Flask Web App Sample using MongoDB

Flask CRUD with Oracle

Flask CRUD with PostgreSQL

Flask Web App Sample using MariaDB

Flask Web App Sample using Firebase DB

Django

Django CRUD with JSON

Django CRUD with SQL Server

Django CRUD with MySQL

Django Web App Sample using MongoDB

Django CRUD with Oracle

Django CRUD with PostgreSQL

Django Web App Sample using MariaDB

Django Web App Sample using Firebase DB

CherryPy

CherryPy CRUD with SQL Server

CherryPy Web App Sample using MongoDB

CherryPy CRUD with Oracle

CherryPy CRUD with JSON

CherryPy Web App Sample using Firebase DB

CherryPy CRUD with MySQL

CherryPy Web App Sample using MariaDB

CherryPy CRUD with PostgreSQL

CRUD sample using Flask and MySQL Database

Contents

Initial Setup

Install PyMySQL

Check if pymysql installed successfully

Install Flask

Web Application Initial Setup

Setting up default page

MySQL Database Setup

Fetch and List Items

Insert new item

Update an existing item

Delete an Item

Flask CRUD with MySQL - Complete code

Other web app samples

Flask

Django

CherryPy