Writing a Flask Application using python-oracledb

November 11, 2022 | 11 minute read
Text Size 100%:

Earlier this year Oracle released python-oracledb, a lightweight Python extension module allowing Python programs to connect to Oracle Database. Python-oracledb is the new name for the popular cx_Oracle driver. The driver will feature prominently in this article, allowing a small Flask application to connect to Oracle XE 21c. Developing and deploying code in containers simplifies many tasks that previously required a strong degree of cooperation between teams, especially with regards to avoiding software version conflicts. Containers solve this packaging and deployment problem by means of abstraction while remaining lightweight in terms of resource usage.

This blog post demonstrates how a Python application can be deployed via a container engine such as Podman. The front-end of the application displays a hit counter, similar in a way that many container tutorials do. The main goal is to show how easily database backends can be changed, and in this case an Oracle Express Edition (XE) version 21c container will be used to store hit counts. The blog post was written using Oracle Linux 8 with Podman 4.1.1 used as the container engine.

Creating the Database Container

The 21c XE database, deployed in a container, is used to store hit counts in a schema. Unlike many tutorials this little Flask demo is multi-user capable. Using session cookies and UUIDs it is possible to track page hits for individual user sessions. Before the database container can be started a few pre-requisites must be met. They include:

•    Creating a Podman Secret to store the password for Oracle's privileged users
•    Provisioning a Volume for persistent data storage
•    Defining a network to enable communication between the front-end container (Flask) and backend (Oracle XE 21c)

Secrets are available with Podman 3.1 and later and allow you to store sensitive information outside of container images or configuration scripts. You create the secret using podman-secret-create (1). In this article the secret name used to store the SYS and SYSTEM password for the 21c XE container is named oracle-system-password. Another secret is required for the application user: flask-user-password. It allows the Flask application to connect to the database. The username is provided to the container as an environment variable, as you can see later.

Volumes allow containers to store data persistently even after they have been destroyed. This is especially important for databases! Volumes are created using podman volume create, in this example the volume name was chosen to be oradata-vol.

Podman networks allow containers to communicate with one another. In this example a network called ora-net has been created. It's important for the network to support DNS, podman network inspect ora-net | jq '.[0].dns_enabled' should return "true" or else the application container won't be able to access the database container.

With all pre-requisites in place, you can start the database container for the first time. Podman will begin by downloading the container image before extracting the database on the (Podman) volume. The database's SYS and SYSTEM passwords will be initialised as specified by the oracle-system-password secret. An application user named FLASK is created at the same time using the password provided by the flask-user-password secret.

podman run --detach \
--secret oracle-system-password \
--env ORACLE_PASSWORD_FILE=/run/secrets/oracle-system-password \
--env APP_USER=flaskdemo \
--secret flask-user-password,type=env,target=APP_USER_PASSWORD \
--volume oradata-vol:/opt/oracle/oradata \
--name oraclexe \
--net oracle-net \
--publish 1521:1521 \
docker.io/gvenzl/oracle-xe:21-slim

The oraclexe container is now ready for use.

Creating the Flask Application

Flask is a great framework for the purpose of this demo: easy to use, and lightweight. The demo application consists of 3 files:

•    hitCounter.py
•    requirements.txt
•    templates/index.html

The application is served by waitress, a popular Python WSGI server. Please note that this isn't an endorsement for waitress, any other supported WSGI will work just fine. For the purpose of this demo no TLS encryption of network traffic was implemented. This is acceptable for this little demo, but not for real-life workloads.

"""
app.py

Martin Bach, 12 Sep 2022

Based on code by Christopher Jones, 10 Sep 2020
Original source: https://blogs.oracle.com/opal/post/how-to-use-python-flask-with-oracle-database

Demo: using Flask with Oracle Database XE 21c

Before running, set these environment variables to connect to the database:

    PYTHON_USERNAME       - your DB username
    PYTHON_PASSWORD       - your DB password
    PYTHON_CONNECTSTRING  - the connection string to the DB, e.g. "example.com/XEPDB1"

"""

import oracledb
import os
import uuid
import socket

from waitress import serve
from flask import Flask, render_template, make_response, request

##########################################################################
#
# start_pool(): create a connection pool
#

def start_pool():

    # Generally a fixed-size pool is recommended, i.e. pool_min=pool_max.
    # Here the pool contains 4 connections, which is fine for 4 conncurrent
    # users and absolutely adequate for this demo.

    pool_min = 4
    pool_max = 4
    pool_inc = 0

    print("Connecting to", os.environ.get("PYTHON_CONNECTSTRING"))

    pool = oracledb.create_pool(
        user=os.environ.get("PYTHON_USERNAME"),
        password=os.environ.get("PYTHON_PASSWORD"),
        dsn=os.environ.get("PYTHON_CONNECTSTRING"),
        min=pool_min,
        max=pool_max,
        increment=pool_inc
    )

    return pool

##########################################################################
#
# create_schema(): drop and create the demo table
#

def create_schema():
    with pool.acquire() as connection:
        with connection.cursor() as cursor:

            try:
                cursor.execute(
                    """
                    CREATE table hit_count (
                        session_id  varchar2(36),
                        hits        number,
                        ts          timestamp not null,
                        constraint pk_hit_count
                        primary key(session_id, hits)
                    )
                    """
                )
            except oracledb.Error as err:
                error_obj, = err.args
                print(f"Error creating hit_count table: {error_obj.message}")

##########################################################################
#
# get_hit_count(): keep track of page hits, similar in a way it is done
# in many tutorials. The code in this example is multi-user capable.
#

def get_hit_count(sessionID):

    print(f"DEBUG: hostname: {socket.gethostname()} session ID: {sessionID}")

    with pool.acquire() as connection:
        with connection.cursor() as cursor:

            cursor.execute(
                """
                INSERT into hit_count (
                    session_id, hits, ts
                )
                SELECT :session_id, nvl((max(hits)+1), 1), systimestamp
                  FROM hit_count
                 WHERE session_id = :session_id
                """,
                session_id=sessionID
            )

            connection.commit()

            maxHitsBySession = "SELECT max(hits) from hit_count WHERE session_id = :session_id"

            numHits = 0
            for row in cursor.execute(maxHitsBySession, session_id=str(sessionID)):
                numHits = row[0]

    return numHits

##########################################################################
#
# Main
#

app = Flask(__name__)

pool = start_pool()

# Try to create the demo table
create_schema()

# Display the hit count

@app.route('/')
def index():
    # get the session ID from the cookie
    myUUID = request.cookies.get("uuid")

    # it must be a new session if there is not session ID found
    # create one in that case and translate it to a string
    if myUUID is None:
        myUUID = str(uuid.uuid4())

    # get hit count by this session
    hits = get_hit_count(myUUID)

    # and send a response to the browser
    response = make_response(render_template('index.html', hits=hits))
    response.set_cookie("uuid", myUUID)
    return response

The application uses the python-oracledb driver to connect to the database and store the hit count per session. The end-user sees the rendered version of the  following template:

<!doctype html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css"
        rel="stylesheet"
        integrity="sha384-Zenh87qX5JnK2Jl0vWa8Ck2rdkQ2Bzep5IDxbcnCeuOxjzrPF/et3URy9Bv1WTRi"
        crossorigin="anonymous">

    <title>Simple Flask Application with an Oracle XE backend</title>
</head>

<body>

    <div class="container px-4" id="app">

        <!-- Header -->
        <div class="row gx-5 no-border">
          <div class="col p-3">
            <h1>Flask powered by Oracle Database</h1>
    
            <p>
                This little application counts how many times this particular page was loaded.
            </p>
          </div>
        </div>
    
    
        <!-- Form -->
        <div class="row gx-2 no-border">
            <div class="col p-3 border">
                <form>
                    <div class="mb-3">
                    <label class="form-label">Number of times this page has been loaded:</label>
                    <input type="text" class="form-control" id="hits" aria-describedby="number of page hits" value="">
                    </div>
                    
                    <button type="submit" class="btn btn-danger">Increase hit count</button>
                </form>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/js/bootstrap.bundle.min.js"
        integrity="sha384-OERcA2EqjJCMA+/3y+gxIOqMEjwtxJY7qPCqsdltbNJuaOe923+mo//f6V8Qbsw3"
        crossorigin="anonymous"></script>
</body>

</html>

The requirements file is the final part of the application. It was current at the time of writing. As with any software project the libraries referred to might get out of date. Please update the requirements file with the current version for each library before use.

cffi==1.15.1
click==8.1.3
cryptography==38.0.3
Flask==2.2.2
importlib-metadata==5.0.0
itsdangerous==2.1.2
Jinja2==3.1.2
MarkupSafe==2.1.1
oracledb==1.1.1
pycparser==2.21
waitress==2.1.2
Werkzeug==2.2.2
zipp==3.10.0

Following the Flask project's directory structure, hitCounter.py should be in the top level directory alongside requirements.txt. The HTML file should be placed into a new directory called templates/.

Creating the Flask Application Container

With the application code ready and the database started it is time is to create the application's container image. The following Dockerfile will create an Oracle Linux 8 container, install Python 3.9 and deploy the application. In the final step it exposes port 8080 and instructs the container to use waitress to serve the application.

FROM ghcr.io/oracle/oraclelinux8-compat:8-slim

WORKDIR /code

RUN dnf -y module disable python36 && \
    dnf -y module enable python39 && \
    dnf -y install python39 python39-pip python39-setuptools python39-wheel && \
    rm -rf /var/cache/dnf

COPY requirements.txt requirements.txt
COPY . .
RUN pip3 install -r requirements.txt --no-cache-dir

EXPOSE 8080
CMD ["waitress-serve", "hitCounter:app"]

The Dockerfile along with the application code allows you to build the container image using a simple one-liner:

podman build -t pydemo:1.0 .

Once that's complete the container image is ready for deployment.

Deploying the Application

The application requires 3 parameters, each passed via the secret mechanism:

•    PYTHON_USERNAME: the database user
•    PYTHON_PASSWORD: the user's database password
•    PYTHON_CONNECTSTRING: the connection string to be used

All of these are going to be passed to the container either as environment variables or Podman secrets. The following command will start the application, solely relying on secrets:

podman run --detach \
--name pydemo \
--net oracle-net \
--env PYTHON_USERNAME=flaskdemo \
--secret flask-user-password,type=env,target=PYTHON_PASSWORD \
--env PYTHON_CONNECTSTRING="oraclexe/xepdb1" \
--publish 8080:8080 \
localhost/pydemo:1.0

If you point your web browser to port 8080 you will be greeted by a small web application. Log information available via podman log pydemo shows each new client connecting and the UUID in use along with the hit count.

Martin Bach

Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.


Previous Post

INTRA CHAT : Oracle Service Cloud Browser UI Inlay for Employee to Employee Collaboration

Sumit Singhal | 6 min read

Next Post


Four Facets of Database Connectivity for Java Applications

Nirmala Sundarappa | 5 min read