Python CherryPy CRUD sample using MySQL database

CherryPy is an open-source Python web application development framework. CherryPy allows developers to build web applications in much the same way they would build any other object-oriented Python program. This results in smaller source code developed in less time. CherryPy is proven to be fast and reliable and supports the latest version of Python.

This topic explains all the steps needed to set up a CherryPy web application using MySQL database.
This is a simple application that lists a set of books available in a library. Users can add, edit or delete from this list.
Refer this sample to quickly learn how to set up CherryPy for first time in your machine, how to run a CherryPy web application, basic set up for your first HTML page, database actions like create, read, update and delete from the CherryPy web application.
In this sample, PyMySQL adapter is used to connect to the MySQL database.
Contents
Install Python and setup Virtual Environment

Install PyMySQL

Install CherryPy and Jinja2

Web Application Initial Setup
Setting up First page

MySQL Database Setup

Fetching data from MySQL DB and list

Insert and update an item
Delete an Item

CherryPy CRUD with MySQL - Complete code

Other web app samples
Install Python and setup Virtual Environment

Download and install the latest version of python from Python Website.

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 book_library to create a virtual environment folder book_library. We will be keeping all our code in this folder and the installed package files will also be kept automatically within this folder.

Now, activate the VE using command:

For Windows:

book_library\Scripts\activate.bat

For Linux, macOS:

source book_library /bin/activate

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

For MySQL connection we are going to use the PyMySQL library. For quick reference on PyMySQL use this link PyMySQL basics.
Install it using the command: pip install pymysql or pip3.9 install pymysql (If using 3.9 python version).
Install CherryPy and Jinja2

To install CherryPy, run command pip install CherryPy or pip3.9 install CherryPy
If not working use commands:

For Windows:

py -3.9 -m pip install CherryPy

For Linux, macOS:

python3.9 -m pip install CherryPy

Also install Jinja2 using the command pip install Jinja2. Jinja2 is basically a template engine that helps in serving the html to the user. Using this library along with CherryPy helps in faster development. For example, we are using Jinja2 function get_template to render HTML page from the python code and python codes like for loops are added to the HTML page using the Jinja2 template markup syntax.

Run pip freeze or pip3.9 freeze to check if all packages installed successfully.
Web Application Initial Setup

Let us start with the initial set of code that is required for every CherryPy web applications. Create a file named books.py (you can provide any name) inside book_library folder and copy below code to it. books.py is going to contain your start-up code which is required to run the web app.
Now, from command prompt navigate to book_library folder and execute command py books.py or python books.py or py -3.9 -m books or python3.9 -m books (Based on the Python version used and the Operating System).
```
import cherrypy

class BookLibrary(object):
    @cherrypy.expose
    def index(self):
        return 'Book Library app is starting up'

if __name__ == '__main__':
    cherrypy.quickstart(BookLibrary())
```
This code imports cherrypy module and then a startup class is created and initiated using the quickstart method from the __main__. Startup class should have a method named index that will load the first HTML content.
In command prompt, we will see the below message when execution starts without any issues.
```
	[08/Jun/2022:18:11:50] ENGINE Listening for SIGTERM.
	[08/Jun/2022:18:11:50] ENGINE Bus STARTING
	CherryPy Checker:
	The Application mounted at '' has an empty config.

	[08/Jun/2022:18:11:50] ENGINE Started monitor thread 'Autoreloader'.
	[08/Jun/2022:18:11:50] ENGINE Serving on http://127.0.0.1:8080
	[08/Jun/2022:18:11:50] ENGINE Bus STARTED
```
Copy the URL http://127.0.0.1:8080 to your browser and run. The message Book Library app is starting up will display in the web browser, if the set up is proper and without any issues.
Setting up First page

Now we are going to load an HTML file as the first page of the application.
Create an HTML file bookslist.html inside book_library folder.

Add below code to bookslist.html page.
In books.py, add the line return open('bookslist.html') inside index function to render this HTML as the first page.
Copied

<!DOCTYPE html> <html lang="en-US" dir="ltr"> <head> <title>Books List</title> </head> <body> <h2>Books List</h2> </body>
Copied

import cherrypy class BookLibrary(object): @cherrypy.expose def index(self): return open('bookslist.html') if __name__ == '__main__': cherrypy.quickstart(BookLibrary())
Changes are automatically compiled and on refreshing the web page we can see the HTML page as the output.

Database Setup

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

CREATE TABLE [dbo].[TblBooks](
	[ID] [int] NOT NULL,
	[Name] [varchar](100) NULL,
	[Year] [int] NOT NULL,
	[Price] [float] NOT NULL)

INSERT INTO TblBooks VALUES (1, 'Harry Potter', 2020, 100)
INSERT INTO TblBooks VALUES (2, 'The Lord of the Rings', 2018, 80)
INSERT INTO TblBooks VALUES (3, 'The Alchemist', 2010, 85)
INSERT INTO TblBooks VALUES (4, 'The Da Vinci Code', 2017, 70)
INSERT INTO TblBooks VALUES (5, 'The Twilight Saga', 2015, 90)

Fetching data from MySQL DB and list

bookslist.html is going to show the list of books in the library. Add below code to the body of the HTML and modify books.py main method as below.
In books.py, first, a common method to set up database connection is added. You need to modify this method and provide your database credentials. Then in index() method, data is queried from the DB and passed to the html file using the Jinja2 template function get_template(). We cannot use the open() method because we need to bind the list of items dynamically to the HTML page. So we are using get_template() function instead.

import cherrypy
import pymysql
import os
from jinja2 import Environment, FileSystemLoader

def connection():
    s = '' #Your server(host) name
    prt = 3306 #Your port no
    d = 'BooksDB' #Your database(schema) name
    u = '' #Your login user
    p = '' #Your login password
    conn = pymysql.connect(host=s, port=prt, user=u, password=p, database=d)
    return conn

class BookLibrary(object):
    @cherrypy.expose
    def index(self):
        books = []
        conn = connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM TblBooks")
        for row in cursor.fetchall():
            books.append({"id": row[0], "name": row[1], "year": row[2], "price": row[3]})
        conn.close()
        templenv = Environment(loader=FileSystemLoader(os.getcwd()))
        return templenv.get_template('bookslist.html').render(books=books)


if __name__ == '__main__':
    cherrypy.quickstart(BookLibrary())

<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 book in books %}
            <tr>
                <td>{{ book.id }}</th>
                <td>{{ book.name }}</td>
                <td>{{ book.year }}</td>
                <td>{{ book.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 reload, we will get below output window.

Insert and update an item

Add a new link button in the bookslist.html above the table.

    <a class="btn btn-primary" href="openaddbookpage/0">Add New Book</a>

Add new html file, addbook.html and place the below code.
Add two links for edit and delete in bookslist.html as below.
Add two more route methods, openaddbookpage() and savebookdetails() in books.py below index() method to handle routing to the edit page and saving the data (Double check the pasted contents are properly intended).

    @cherrypy.expose
    def openaddbookpage(self, id=0):
        bk = []
        templenv = Environment(loader=FileSystemLoader(os.getcwd()))
        if int(id) == 0:
            return templenv.get_template('addbook.html').render(book=bk)
        else:
            conn = connection()
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM TblBooks WHERE id = %s", (id))
            for row in cursor.fetchall():
                bk.append({"id": row[0], "name": row[1], "year": row[2], "price": row[3]})
            conn.close()
            return templenv.get_template('addbook.html').render(book=bk[0])
    
    @cherrypy.expose
    def savebookdetails(self, id=0, name='', year=0, price=0):
        templenv = Environment(loader=FileSystemLoader(os.getcwd()))
        idexists = 0
        conn = connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM TblBooks WHERE id = %s", (id))
        for row in cursor.fetchall():
            idexists = 1
            break
        if int(idexists) == 0:
            cursor.execute("INSERT INTO TblBooks (id, name, year, price) VALUES (%s, %s, %s, %s)", (id, name, year, price))
        else:
            cursor.execute("UPDATE TblBooks SET name = %s, year = %s, price = %s WHERE id = %s", (name, year, price, id))
        conn.commit()
        conn.close()
        raise cherrypy.HTTPRedirect("/")

<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Book 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>Book Details</h2>

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

<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 book in books %}
            <tr>
                <td>{{ book.id }}</th>
                <td>{{ book.name }}</td>
                <td>{{ book.year }}</td>
                <td>{{ book.price }}</td>
                <td><a href='openaddbookpage/{{ book.id }}'>Edit</a></td>
                <td><a href='deletebook/{{ book.id }}'>Delete</a></td>
            </tr>
        {% endfor %}
    </tbody>
</table>

Add New Book button in the list page calls openaddbookpage route with ID as 0. So Book Details page opens in Create mode with empty text boxes.
Edit link next to each row also calls openaddbookpage route, but passes ID number to the Book Details page. So the details of the selected book gets fetched from the DB and bind to the corresponding text boxes in the HTML page.

savebookdetails route is called on Submit button click. If the entered ID number doesn't exist in the database, new book gets added. Otherwise, existing record is updated.

Delete an Item

For delete, we will add a new route method deletebook(). And then, just need to provide delete query as below.

    @cherrypy.expose
    def deletebook(self, id=0):
        conn = connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM TblBooks WHERE id = %s", (id))
        conn.commit()
        conn.close()
        raise cherrypy.HTTPRedirect("../")

CherryPy CRUD with MySQL database - Complete code

Complete code is placed below.

import cherrypy
import pymysql
import os
from jinja2 import Environment, FileSystemLoader

def connection():
    s = '' #Your server(host) name
    prt = 3306 #Your port no
    d = 'BooksDB' #Your database(schema) name
    u = '' #Your login user
    p = '' #Your login password
    conn = pymysql.connect(host=s, port=prt, user=u, password=p, database=d)
    return conn

class BookLibrary(object):
    @cherrypy.expose
    def index(self):
        books = []
        conn = connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM TblBooks")
        for row in cursor.fetchall():
            books.append({"id": row[0], "name": row[1], "year": row[2], "price": row[3]})
        conn.close()
        templenv = Environment(loader=FileSystemLoader(os.getcwd()))
        return templenv.get_template('bookslist.html').render(books=books)
    
    @cherrypy.expose
    def openaddbookpage(self, id=0):
        bk = []
        templenv = Environment(loader=FileSystemLoader(os.getcwd()))
        if int(id) == 0:
            return templenv.get_template('addbook.html').render(book=bk)
        else:
            conn = connection()
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM TblBooks WHERE id = %s", (id))
            for row in cursor.fetchall():
                bk.append({"id": row[0], "name": row[1], "year": row[2], "price": row[3]})
            conn.close()
            return templenv.get_template('addbook.html').render(book=bk[0])
    
    @cherrypy.expose
    def savebookdetails(self, id=0, name='', year=0, price=0):
        templenv = Environment(loader=FileSystemLoader(os.getcwd()))
        idexists = 0
        conn = connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM TblBooks WHERE id = %s", (id))
        for row in cursor.fetchall():
            idexists = 1
            break
        if int(idexists) == 0:
            cursor.execute("INSERT INTO TblBooks (id, name, year, price) VALUES (%s, %s, %s, %s)", (id, name, year, price))
        else:
            cursor.execute("UPDATE TblBooks SET name = %s, year = %s, price = %s WHERE id = %s", (name, year, price, id))
        conn.commit()
        conn.close()
        raise cherrypy.HTTPRedirect("/")
    
    @cherrypy.expose
    def deletebook(self, id=0):
        conn = connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM TblBooks WHERE id = %s", (id))
        conn.commit()
        conn.close()
        raise cherrypy.HTTPRedirect("../")


if __name__ == '__main__':
    cherrypy.quickstart(BookLibrary())

<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Books 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>Books List</h2>
    <a class="btn btn-primary" href="openaddbookpage/0">Add New Book</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 book in books %}
                <tr>
                    <td>{{ book.id }}</th>
                    <td>{{ book.name }}</td>
                    <td>{{ book.year }}</td>
                    <td>{{ book.price }}</td>
                    <td><a href='openaddbookpage/{{ book.id }}'>Edit</a></td>
                    <td><a href='deletebook/{{ book.id }}'>Delete</a></td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
</body>

<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Book 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>Book Details</h2>

    <form action="../savebookdetails" method = "POST">
        <div class="form-group row">
          <label for="id">Id</label>
          <input type="text" class="form-control" name="id" value="{{book.id}}">
        </div>
        <div class="form-group row">
            <label for="name">Book Name</label>
            <input type="text" class="form-control" name="name" value="{{book.name}}">
        </div>
        <div class="form-group row">
            <label for="year">Year</label>
            <input type="text" class="form-control" name="year" value="{{book.year}}">
        </div>
        <div class="form-group row">
            <label for="price">Price</label>
            <input type="text" class="form-control" name="price" value="{{book.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

Python CherryPy CRUD sample using MySQL database

Contents

Install Python and setup Virtual Environment

Install PyMySQL

Install CherryPy and Jinja2

Web Application Initial Setup

Setting up First page

Database Setup

Fetching data from MySQL DB and list

Insert and update an item

Delete an Item

CherryPy CRUD with MySQL database - Complete code

Other web app samples

Flask

Django

CherryPy