CODEWITHSUNDEEP
pythondatabaseflaskflask-sqlalchemycx-oracle
Abhi
Abhi

Reputation: 43

Python flask: make database connect more efficient

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.

Upvotes: 1

Views: 3155

Answers (2)

shoaib30
shoaib30

Reputation: 975

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

Upvotes: 2

Pouya Esmaeili
Pouya Esmaeili

Reputation: 1263

Connecting to database for each request is not an efficient solution. You need to establish a pooled connection and use it for all requests (share a pool of connections between threads). So, it is necessary to have thread safety here.

For your database driver define the following snippet globally (outside functions) and use it inside functions:

import cx_Oracle

# Create the session pool
pool = cx_Oracle.SessionPool(user="hr", password=userpwd,
                             dsn="dbhost.example.com/orclpdb1", min=2,
                             max=5, increment=1, encoding="UTF-8")

# Acquire a connection from the pool
connection = pool.acquire()

# your functions here

For details about pooled connection check here

Upvotes: 1

Related Questions