Python Flask middleware app with InterSystems IRIS as the back end database

irisflask.py

import pyodbc
from flask import Flask
from flask import request
from flask import render_template
import json
app = Flask(__name__)
connection = None

# default route
@app.route('/')
def hello_world():
    return 'Hello, World!'

@app.route('/makeschema', methods=['GET'])
def make_schema():
    try:
        cursor = connection.cursor()
        sql = 'CREATE TABLE FLASK.SAMPLE(user_id VARCHAR(16), firstname VARCHAR(128), lastname VARCHAR(128) )'
        cursor.execute(sql)
        connection.commit()
    except Exception as exp:
        print(exp)
        return render_template('/error.html', err=exp)
    return 'Successfully created FLASK.SAMPLE table'

@app.route('/loaddata', methods=['POST'])
def load_data():
    d = request.get_data(as_text=True)
    personlist = d.split('|')
    try:
        cursor = connection.cursor()
        for person in personlist:
            sql = 'INSERT INTO FLASK.SAMPLE (user_id, firstname, lastname) VALUES ('
            atts = person.split(',')
            sql += "'" + atts[0] + "'" + ","
            sql += "'" + atts[1] + "'" + ","
            sql += "'" + atts[2] + "'" + ")"
            print("Inserting: "+sql)
            cursor.execute(sql)
        connection.commit()
    except Exception as exp:
        print(exp)
        return render_template('/error.html', err=exp)
    return 'Successfully inserted data into FLASK.SAMPLE '

@app.route('/person/<firstname>')
def get_by_firstname(firstname):
    try:
        cursor = connection.cursor()
        sql = "SELECT * FROM FLASK.SAMPLE WHERE firstname LIKE " + "'" + firstname + "'"
        print("SQL: "+sql)
        rows = cursor.execute(sql)
        resp = []
        for row in rows:
            print(row)
            r = {"id": row[0], "firstname": row[1], "lastname": row[2]}
            resp.append(r)
        return json.dumps({ "query": sql, "responses": resp})
    except Exception as exp:
        print(exp)
        return render_template('/error.html', err=exp)
    return 'Successfully inserted data into FLASK.SAMPLE '


# Get connection details from config file
def get_connection_info(file_name):
    # Initial empty dictionary to store connection details
    connections = {}

    # Open config file to get connection info
    with open(file_name) as f:
        lines = f.readlines()
        for line in lines:
            # remove all white space (space, tab, new line)
            line = ''.join(line.split())

            # get connection info
            connection_param, connection_value = line.split(":")
            connections[connection_param] = connection_value
    return connections


def run():
    # Retrieve connection information from configuration file
    connection_detail = get_connection_info("connection.config")

    ip = connection_detail["ip"]
    port = int(connection_detail["port"])
    namespace = connection_detail["namespace"]
    username = connection_detail["username"]
    password = connection_detail["password"]
    driver = "{InterSystems ODBC}"

    # Create connection to InterSystems IRIS
    connection_string = 'DRIVER={};SERVER={};PORT={};DATABASE={};UID={};PWD={}' \
        .format(driver, ip, port, namespace, username, password)
    global connection
    connection = pyodbc.connect(connection_string)
    connection.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
    connection.setencoding(encoding='utf-8')
    print("Connected to InterSystems IRIS")

run()