Click here to Skip to main content
15,313,985 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
cx_oracle persistent caonnection on flask+apache+mod_wsgi

What I have tried:

I have deployed my flask application on apache+mod_wsgi

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

Terminal
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:
Python
# 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()



Python
# 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)
Posted
Comments
Richard MacCutchan 13-Aug-21 10:43am
   
You should not hold open connections to a database as it can tie up resources. You should open the connection, perform your transaction(s), and the close it again.
OrangeLantern 13-Aug-21 21:27pm
   
I understand but this is a client-server application. If I open and close a connection for each request, the response time will increase too much.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900