What do I mean by old school developers? These are developers still believing is specializing in one technology and nothing else. They learn all there is to know about a specific technology but nothing beyond that. In this article, I am referring to Oracle data developers. Its also worth noting that this was a corporate setting, as corporate culture is a great influence.

Back story

The team’s environment was simple, an Oracle forms front-end(see why I used old school) and an Oracle database backend. A shared service teams of Unix engineers and DBAs that looked after the shared environment the stack is hosted on. Application integration was mainly implemented via IBM MQ, however, rather than using Oracle Advanced Queuing(DBMS_AQ) java services were used. These services where built and hosted by a separate team, a java team. One service would poll a table on a source DB to check for records that were ready to be published. When ready, the service will build a message off this record and place it in the queue. Thereafter mark the record as published. On the receiving end, a similar service would exist. A service will poll the queue and insert those messages into the target DB table for further processing.

The Java team is actually doing the heavy lifting here and not the source or target developers. They only built source/target tables and sent over processing rules to the Java team. The database developer team was used to get stuff done on their behalf. On demand data access request were provided with a function or view along with database credentials. They were comfortable with working within the database, after all, they were hired as Oracle specialists.

Enter Agile…

Now the above way of working was obviously wouldn’t survive in the current fast-paced application development. The team was not agile because a lot of its services that was outside the team. The team had to plan changes with the java team that was in-demand almost never available. When Agile came in, the feature team had to now be accountable for all of their services. This came with new requirement of building APIs that provided real-time access to data at source without a middle man facilitating the integration.

Enter Python

There were lots of discussions on how a team of Oracle Specialists is now supposed to build an API. One suggestion that was raised the Java team, was to build a generic Spring Boot application that each team would map/with their tables as objects, thereafter an API built from this would be able to perform basic CRUD functions on the mapped tables. I obviously had lots of problems with this.

To address the above, the team needed a scripting language that the developer can simply insert the custom SQL queries to return data and execute existing DB procedures to manipulate data. After identifying the above the answer was obvious. Python + Flask

But How?

Firstly, we need to import cx_Oracle, a Python extension module to enables access to an Oracle Database. Next, we import flask with request and jsonify

import cx_Oracle import flask from flask import request,jsonify

We start by initializing the Flask application. Next, we create a pool of sessions connecting to the database using the cx_Oracle module. The pool will have a minimum of 2 open sessions and can scale up to 5 DB sessions

app=flask.Flask(__name__) userpwd = ". . ." # Obtain password string from a user prompt or environment variable pool = cx_Oracle.SessionPool("hr",userpwd,"dbhost.example.com/orclpdb1", min=2, max=5, increment=1, threaded=True)

The function below is to help us convert a cursor with the query results into a python dictionary for easier processing.

def makeDictFactory(cursor): columnNames = [d[0] for d in cursor.description] def createRow(*args): return dict(zip(columnNames, args)) return createRow

Now here is the fun part, the scaffolding developers get to play with. The @app.route is a Flask function to bind the function definition to the URL path of the API. Here, we are defining a function to search staff details using the staff id. In this function, you can clearly identify the SQL Query. This allows the team in question to come with a prepared SQL query that will use appropriate indexes to fast query the database and also encapsulate the broken normalisation from the API consumers. In the end, we have app.run() to run the Flask application.

@app.route('/search', methods=['GET']) def search(): with pool.acquire() as con: searchSQL = ( "select firstname,lastname,dob " " from hr.staff " " where staffID=:staffID " ) query_parameters = request.args staffID = query_parameters.get('staffID') params = {} if staffID: params.update({'staffID':staffID}) if not params: return {'message': 'Invalid Request'}, 400 cur = con.cursor() cur.execute(searchSQL,params) cur.rowfactory = makeDictFactory(cur) return jsonify(cur.fetchall()) app.run()

Compiling the above into one *.py script and you have API. It was that simple. After I demoed this to the team they were extremely happy about how an SQL query can be easily converted to a JSON response. The team voluntarily started playing around with the code, learning more about the scripting language. Since then, Python has found other uses within the team other than APIs.

Again thank you for readying.

Originally published at loggedon.co.za on February 13, 2021.