A sample Python web application using CherryPy and Oracle database

This is a Python CherryPy web app sample that uses Oracle database.
Adapter used to connect to Oracle DB from python is cx_Oracle, which supports all the latest Oracle versions.
cx_Oracle is fast and easy to use and simple to understand.
Basic create, read, update and delete operations are provided in this sample.
Also helpful to understand Python CherryPy framework and its usages.

This is a simple application that lists a set of books available in a library. Users can add, edit or delete from this list.
Contents
Set up python in your machine

Install CherryPy and Jinja2 Template Engine

Install cx_Oracle

Web Application Initial Setup
Setting up First page

Oracle Database Setup

Read data from Oracle and list

Create or update record
Delete a Record

CherryPy CRUD with Oracle - Complete code

Other web app samples
Set up python in your machine

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 CherryPy and Jinja2 Template Engine

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.
Install cx_Oracle

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

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.

Oracle Database Setup

Connect to your Oracle database. Create a table TblBooks with below structure and insert below set of data and commit.

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

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)
commit;

Read data from Oracle 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 cx_Oracle
import os
from jinja2 import Environment, FileSystemLoader

def connection():
    h = '' #Your host name/ip
    p = '' #Your port number
    sid = '' #Your sid
    u = '' #Your login user name
    pw = '' #Your login password
    d = cx_Oracle.makedsn(h, p, sid=sid)
    conn = cx_Oracle.connect(user=u, password=pw, dsn=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.

Create or update record

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 = :id", [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 = :id", [id])
        for row in cursor.fetchall():
            idexists = 1
            break
        if int(idexists) == 0:
            cursor.execute("INSERT INTO TblBooks VALUES (:id, :name, :year, :price)", [id, name, year, price])
        else:
            cursor.execute("UPDATE TblBooks SET name = :id, year =:year, price = :price WHERE id = :id", [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 a Record

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 = :crid", [id])
        conn.commit()
        conn.close()
        raise cherrypy.HTTPRedirect("../")

CherryPy CRUD with Oracle database - Complete code

Complete code is placed below.

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

def connection():
    h = '' #Your host name/ip
    p = '' #Your port number
    sid = '' #Your sid
    u = '' #Your login user name
    pw = '' #Your login password
    d = cx_Oracle.makedsn(h, p, sid=sid)
    conn = cx_Oracle.connect(user=u, password=pw, dsn=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 = :id", [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 = :id", [id])
        for row in cursor.fetchall():
            idexists = 1
            break
        if int(idexists) == 0:
            cursor.execute("INSERT INTO TblBooks VALUES (:id, :name, :year, :price)", [id, name, year, price])
        else:
            cursor.execute("UPDATE TblBooks SET name = :id, year =:year, price = :price WHERE id = :id", [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 = :crid", [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

A sample Python web application using CherryPy and Oracle database

Contents

Set up python in your machine

Install CherryPy and Jinja2 Template Engine

Install cx_Oracle

Web Application Initial Setup

Setting up First page

Oracle Database Setup

Read data from Oracle and list

Create or update record

Delete a Record

CherryPy CRUD with Oracle database - Complete code

Other web app samples

Flask

Django

CherryPy