[FIXED] Python flask: make database connect more efficient

Issue

I developed a basic webapp in flask. On the homepage, it has links to 3 different routes each of which leads to a separate html page displaying separate results. Now the results are fetched from oracle database tables. The code is structured as shown below.

@app.route('/route1')
def func1():
    connection = cx_Oracle.connect("myuser/[email protected]:1521/ora1")
    \\read from database into variable var1
    connection.commit()
    connection.close()
    return render_template('a.html', var = var1)

@app.route('/route2')
def func2():
    connection = cx_Oracle.connect("myuser/[email protected]:1521/ora1")
    \\read from database into variable var2
    connection.commit()
    connection.close()
    return render_template('b.html', var = var2)

@app.route('/route3')
def func3():
    connection = cx_Oracle.connect("myuser/[email protected]:1521/ora1")
    \\read from database into variable var3
    connection.commit()
    connection.close()
    return render_template('c.html', var = var3)

So, I am starting a new connection for every new request. It works fine but is there a more efficient way to implement that? Each user can request one service(route) at a time only. So, ideally there should be one connection to the database per user. How can that be implemented? Or is there any other way to improve it overall? The webapp is supposed to be used by 40-50 people.

Solution

You would want to establish a database connection/pool of connections while creating the Flask App and reuse the same connection/pool in all your routes.

As a single connection

This is not advised as multiple requests would need to wait for the connection.

app = Flask(__name__)

conn =   cx_Oracle.connect(
        'username',
        'password',
        'host',
        encoding="UTF-8")

@app.route("/")
def hello_world():
    # use conn
    return "<p>Hello, World!</p>"

@app.route("/test")
def hello_world():
    # use conn
    return "<p>Hello, World!</p>"

As a connection pool

This is the preferred method of connecting to a database in a web application

app = Flask(__name__)

pool = cx_Oracle.SessionPool(
    user='username',
    password='password',
    dsn='host',
    min=2,
    max=5,
    increment=1,
    encoding="UTF-8")


@app.route("/")
def hello_world():
    conn = pool.acquire()
    # use conn
    pool.release(conn)
    return "<p>Hello, World!</p>"


@app.route("/test")
def hello_world():
    conn = pool.acquire()
    # use conn
    pool.release(conn)
    return "<p>Hello, World!</p>"

Source: https://www.oracletutorial.com/python-oracle/connecting-to-oracle-database-in-python/

---

You may use an ORM to simplify as well as structure your application better. SQLAlchemy does a great job and there is a wrapper to use SQLAlchemy with flask flask-sqlalchemy

Answered By - shoaib30