Turn Your Oracle Table into a REST API with Flask

 In my previous tutorial, we built a Flask application that connected to an Oracle Database and displayed employee data in a browser.

Now, let's take it one step further.

Instead of rendering HTML, we'll expose the same Oracle table as a REST API that returns JSON. This is how modern applications communicate with mobile apps, web frontends, Oracle Integration Cloud (OIC), and other external systems.

By the end of this tutorial, you'll have an API that returns employee data directly from Oracle Database.

What We'll Build

We'll expose data from the XXFLASK_EMPLOYEE_INFO table through the following endpoint:

GET /GetEmployee

Instead of HTML, the response will be JSON.

Example:

[

  {

    "name": "Pranay",

    "dept": "IT",

    "salary": 60000,

    "contact_info": "+9195555552367"

  },

  {

    "name": "Anshuman",

    "dept": "Finance",

    "salary": 35000,

    "contact_info": "+9195555552368"

  }

]

Project Structure

We'll continue using the same project.

DataReadDBApp/

│

├── app.py

├── json.config

├── static/

└── templates/

Since the database connection is already stored in json.config, we don't need to change any connection details inside the code.

Step 1: Import Required Libraries

Start by importing the required packages.

from flask import Flask, render_template, request, jsonify

from flask_sqlalchemy import SQLAlchemy

from datetime import datetime

import oracledb

import json

import os

Why jsonify?

Unlike the previous tutorial where Flask returned HTML, we'll now use jsonify().

jsonify() converts Python dictionaries and lists into valid JSON responses while automatically setting the correct HTTP response headers.

Step 2: Read the Configuration File

Load the database connection 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"]

This keeps the application configurable and avoids hardcoding database credentials.

Step 3: Initialize Oracle and SQLAlchemy

oracledb.init_oracle_client(

lib_dir=r"C:\instantclient_21_11"

)

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = dbParams["database_conn_link"]

db = SQLAlchemy(app)

This establishes the Oracle database connection using SQLAlchemy.

Step 4: Map the Oracle Table

Create the ORM model.

class EmployeeInfo(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)

This maps the Oracle table to a Python class.

Now every employee record becomes a Python object.

Step 5: Create the REST API

Here's the complete API.

@app.route('/GetEmployee', methods=['GET'])

def get_employee_info():

rows = EmployeeInfo.query.all()

result = []

for row in rows:

result.append({

"name": row.NAME,

"dept": row.DEPT,

"salary": row.SALARY,

"contact_info": row.CONTACTINFO

})

return jsonify(result)

How It Works

The API performs four simple steps:

1. Receives an HTTP GET request.

2. Reads all employee records from Oracle.

3. Converts each record into a Python dictionary.

4. Returns the entire list as JSON.

Instead of generating HTML, the application now behaves like a backend service.

Step 6: Run the Application

Start Flask.

python app.py

Flask starts on:

http://127.0.0.1:5000

Open:

http://127.0.0.1:5000/GetEmployee

You should see a JSON response similar to this.

Understanding the JSON Response

Each employee record becomes a JSON object.

{

"name": "Pranay",

"dept": "IT",

"salary": 60000,

"contact_info": "+9195555552367"

}

Why JSON?

Because almost every modern application understands JSON.

• Mobile apps

• React applications

• Angular applications

• Oracle Integration Cloud (OIC)

• Postman

• Third-party APIs

Complete Source Code

from flask import Flask,render_template,request,jsonify

from flask_sqlalchemy import SQLAlchemy

from datetime import datetime

import oracledb

import json

import os

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"]

oracledb.init_oracle_client(lib_dir=r"C:\instantclient_21_11")

app=Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] =dbParams["database_conn_link"]

db=SQLAlchemy(app)

class EmployeeInfo(db.Model):

    __tablename__ = "XXFLASK_EMPLOYEE_INFO"   # ✅ correct syntax!

    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)

   

@app.route('/GetEmployee', methods=['GET'])

def get_employee_info():

    rows=EmployeeInfo.query.all()

    result=[]

    for row in rows:

        result.append( {"name":row.NAME,

                        "dept":row.DEPT,

                        "salary":row.SALARY,

                        "contact_info":row.CONTACTINFO

                        }

        )

    return jsonify(result)

if __name__ == '__main__':

    app.run(debug=True)