Lightweight web application with Python Flask connecting to database using ODBC

 

Simple web app in Python using flask. This app connects to a database and compares the cost of calls, using

1. new connection object each time, vs
2. reusing same connection object

 

HTH

 

from flask import Flask
import pyodbc
import time

app = Flask(__name__)


@app.route('/')
def hello_world():
    return 'Hello, World! <br><br>URL with no caching: <a href="/db_call/cache_no" target="_blank">Click</a><br>' \
           'URL with Caching: <a href="/db_call/cache" target="_blank">Click</a>'


# #################################################################################################################
str_dsn = "DSN=Hive LLAP"
str_query = "SELECT x,y,z from the_table where condition like '%could_be_bad%';"

cache_no_hits = 0
cache_no_hits_avg_time = 0


@app.route('/db_call/cache_no')
def db_call_cache_no():
    global cache_no_hits, cache_no_hits_avg_time
    cache_no_hits += 1
    t = time.time()
    conn = pyodbc.connect(str_dsn, autocommit=True)
    cursor = conn.cursor()
    tq_start = time.time()
    cursor.execute(str_query)
    tq_end = time.time()
    output = ""
    for row in cursor:
        output += str(row) + r"<br/>"

    cursor.close()
    conn.close()
    elapsed_time = time.time() - t

    if cache_no_hits_avg_time == 0:
        cache_no_hits_avg_time = elapsed_time
    else:
        cache_no_hits_avg_time = (cache_no_hits_avg_time * (cache_no_hits - 1) + elapsed_time) / cache_no_hits

    payload = "<b>Creating new connection each time</b><br>Last request time: {} <br>Total elapsed time: {} <br>" \
              "Backend time spent: {} <br>No cache calls: {}<br>Average time: {} <br><br> {}"
    return payload.format(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t)), elapsed_time, (tq_end-tq_start),
                          cache_no_hits, cache_no_hits_avg_time, output)


# #################################################################################################################


conn_global = pyodbc.connect(str_dsn, autocommit=True)
cursor_global = conn_global.cursor()
cache_hits = 0
cache_hits_avg_time = 0


@app.route('/db_call/cache')
def db_call_cache():
    global cache_hits, cache_hits_avg_time
    cache_hits += 1
    t = time.time()
    cursor_global.execute(str_query)
    back_end_time = time.time() - t
    output = ""
    for row in cursor_global:
        output += str(row) + r"<br/>"
    elapsed_time = time.time() - t

    if cache_hits_avg_time == 0:
        cache_hits_avg_time = elapsed_time
    else:
        cache_hits_avg_time = (cache_hits_avg_time * (cache_hits - 1) + elapsed_time) / cache_hits

    payload = "<b>Using same connection object</b><br>Last request time: {} <br>Total elapsed time: {} <br>" \
              "Backend time spent: {} <br>Cache calls: {}<br>Average time: {} <br><br> {}"
    return payload.format(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t)), elapsed_time, back_end_time,
                          cache_hits, cache_hits_avg_time,output)


if __name__ == '__main__':
    app.run()