If you're an Oracle developer learning Python and Flask, one of the first real-world applications is reading data from an Oracle database and displaying it in a web page.
In this tutorial, we'll build a simple Flask application that connects to an Oracle Database using SQLAlchemy and python-oracledb, retrieves employee records, and displays them in a browser.
Step 1: Create the Project Structure
Create a new folder on your local machine.
Project Location
D:\Flask_Applications\DataReadDBAppCreating folder with name DataReadApp on my local machine
Inside this folder, create two additional folders.
DataReadDBApp/
│
├── static/
├── templates/
└── json.config
Why this structure? This is common and standard folders in every flask app
static stores CSS, JavaScript, and images.
templates stores HTML pages.
app.py contains the Flask application.
json.config stores database connection details separately from the code.
D:\Flask_Applications\DataReadDBApp
Step 2: Create the Oracle Database Table
Create the employee table.
CREATE TABLE xxflask_employee_info (
xxid NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR2(1000),
dept VARCHAR2(1000),
salary NUMBER,
contact_info VARCHAR2(1000),
creation_date DATE DEFAULT SYSDATE
);Insert some sample records.
BEGIN
INSERT INTO xxflask_employee_info(name,dept,salary,contact_info)
VALUES('Pranay','IT',60000,'+9195555552367');
INSERT INTO xxflask_employee_info(name,dept,salary,contact_info)
VALUES('Anshuman','Finance',35000,'+9195555552368');
INSERT INTO xxflask_employee_info(name,dept,salary,contact_info)
VALUES('Devansh','HR',25000,'+9195555552369');
INSERT INTO xxflask_employee_info(name,dept,salary,contact_info)
VALUES('Indrajeet','SCM',60000,'+9195555552389');
INSERT INTO xxflask_employee_info(name,dept,salary,contact_info)
VALUES('Naman','IT',60000,'+9195555552397');
END;
/Verify the data.
SELECT * FROM xxflask_employee_info;Step 3: Create the json.config Configuration File
Create a file named json.config.
{
"params": {
"database_conn_link": "oracle+oracledb://apps:apps@orcl:1522/?service_name=devdb"
}
}Why use a configuration file?
Many beginners hardcode database credentials directly inside Python files.
Instead, storing them in a separate configuration file is considered a better coding practice because:
Connection details can be changed without modifying the application code.
It keeps configuration separate from business logic.
It makes deployments easier across Development, Test, and Production environments.
If your database changes later, you only update json.config.
Step 4: Import Required Libraries
Open main.py and import the required packages.
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import oracledb
import json
import os
What each library does
Library
Purpose
Flask
Creates the web application
Flask-SQLAlchemy
Database ORM
oracledb
Oracle Database connectivity
json
Reads configuration
os
Reads files from the project directory
datetime
Date handling
Step 5: Read the Configuration File
Instead of hardcoding the connection string, load it dynamically.
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
config_path = os.path.join(BASE_DIR, 'json.config')
with open(config_path, 'r') as c:
dbParams = json.load(c)["params"]Why use os.path?
Using:
os.path.abspath(__file__)makes your application portable.
No matter where the project is copied, Flask can still locate the configuration file.
Step 6: Initialize the Oracle Client
Before connecting to Oracle, initialize the Instant Client.
oracledb.init_oracle_client(
lib_dir=r"C:\instantclient_21_11"
)Step 7: Configure Flask and SQLAlchemy
Create the Flask application.
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = dbParams['database_conn_link']
db = SQLAlchemy(app)This reads the connection string directly from the configuration file.
Step 8: Create the ORM Model
Instead of writing SQL every time, SQLAlchemy lets us map database tables to Python classes.
class FlaskEmployeeInfo(db.Model):
__tablename__ = "XXFLASK_EMPLOYEE_INFO"
XXID = db.Column("XXID", db.Integer, primary_key=True)
NAME = db.Column("NAME", db.String)
DEPT = db.Column("DEPT", db.String)
SALARY = db.Column("SALARY", db.Integer)
CONTACTINFO = db.Column("CONTACT_INFO", db.String)
CREATIONDATE = db.Column("CREATION_DATE", db.Date)Why use an ORM?
ORM (Object Relational Mapping) allows you to work with Python objects instead of writing SQL for every operation.
Example:
Instead of:
SELECT * FROM xxflask_employee_info;we can write:
db.select(FlaskEmployeeInfo)This makes the code cleaner and easier to maintain.
Step 9: Create the Flask Route
Create a route that reads employee data.
@app.route('/GetEmployee')
def DBRead():
try:
resultset = db.session.execute(
db.select(FlaskEmployeeInfo)
)
resultset_Text = '<ul>'
for rs in resultset:
row = rs[0]
resultset_Text += (
f'<li>{row.NAME}, '
f'{row.DEPT}, '
f'{row.SALARY}, '
f'{row.CONTACTINFO}</li>'
)
resultset_Text += '</ul>'
return resultset_Text
except Exception as e:
error_text = "<p>The error:<br>" + str(e) + "</p>"
hed = "<h1>Something is broken.</h1>"
return hed + error_textHow this works
The user opens
/GetEmployee.Flask executes the function.
SQLAlchemy fetches all employee records.
Each row is converted into an HTML list item.
Flask returns the HTML to the browser.
Step 10: Run the Application
Add the final block.
if __name__ == '__main__':
app.run(debug=True)Here is the complete code
Expected Output
The browser displays all employee records.
0 Comments