How I convinced old school developers to use Python for APIs

What do I mean by old-school developers? These are developers still believing in 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 team of Unix engineers and DBAs 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 were 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 of 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 requests 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 were 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 a new requirement of building APIs that provided real-time access to data at the 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 by 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.

• Databases are not normalized correctly: If you have worked on a database that has existed for more than 15 or more years, you know what I am talking about. The normalization on these databases was built to handle data during the ’90s and early ’00s. Now there have been numerous workarounds over the years to adapt new data into these tables and this came at a cost of normalization. This means simply joining tables based on reference constraints was most like return duplicates or on incorrect data, hence mapping tables as objects in the spring boot application was not going to work well. Also, the performance was almost guaranteed to be terrible as indexes were built for specific tuning.

• Java is intimidating: A maven project is intimidating to anyone just starting out with Java. There are just too many files in a simple project. It is even more complicated if you try to understand how the whole thing works. The build process is easy but this introduces a new artifact that you run which is very different from how scripting works. This is the team that has a bunch of PLSQL scripts that they run using sqlplus on a Unix environment. i.e. The team was comfortable with scripting.

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 reading.