CODEWITHSUNDEEP
pythonapacheflaskmod-wsgicx-oracle
greenlantern
greenlantern

Reputation: 414

cx_oracle persistent connection on flask+apache+mod_wsgi

I have deployed my flask application on apache+mod_wsgi

I'm using WSGI Daemon mode and have this config in apache httpd.conf:

WSGIDaemonProcess flask_test user=apache group=apache threads=20

For simplicity lets say for each request, I need to execute a query to insert data into Oracle DataBase.

So in my flask application, I have done something like this:

# DB.py

import cx_Oracle
class DB:
    def __init__(self, connection_string):
        self.conn = cx_Oracle.connect(connection_string, threaded=True)
    
    def insert(query):
        cur = self.conn.cursor()
        cur.execute(query)
        cur.close()
        self.conn.commit()

# flask_app.py

from flask import Flask, request, jsonify
from DB import DB

app = Flask(__name__)
db = DB(connection_string)

@app.route("/foo", methods=["POST"])
def foo():
    post_data = request.get_json()
    
    # parse above data
    # create insert query with parsed data values
    
    db.insert(insert_processed_data_QUERY)

    # generate response
    
    return jsonify(response)

When I start the apache+mod_wsgi server, the DB object is created and the DB connection is established. For all incoming requests, the same DB object is used to execute insert query.

So far this works fine for me. However my concern is that if there are no requests for a long period of time, the DB connection might time out, and then my app will not work for a new request when it comes.

I've been monitoring my application and have observed that the DB connection persists for hours and hours. But I'm pretty sure it might timeout if there is no request for 2-3 days(?)

What would be the correct way to ensure that the DB connection will stay open forever? (i.e. as long as the apache server is running)

Upvotes: 0

Views: 466

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

Use a pool instead of a standalone connection. When you acquire a connection from the pool it will check to see if the connection is no longer valid and automatically dispense a new one. So you need something like this:

pool = cx_Oracle.SessionPool(user=user, password=password, dsn=dsn, min=1,
                             max=2, increment=1)

Then in your code you need to do the following:

with pool.acquire() as connection:
    # do what you need to do with the connection

Upvotes: 2

Related Questions