Serialize Database Connection Across Session

Question

I am developing a web application that needs to login to the database with credentials that are provided by the end user; the application itself does not have a login to the database.

The problem is how to create one connection per user session.

One approach is:

1. Request user's credentials
2. Check if credentials are valid against db backend
3. Store credentials in session-level variable

Problem with this approach is, on each subsequent request for that session; you would need to create a new connection; and this will quickly exhaust the max connections to the server.

I am using Flask with Oracle.

In Flask, there is a g object, which stores request-scoped objects. This snippet though, does not work:

app = Flask(__name__)
app.config.from_object(__name__)

def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if g.db is None:
            return redirect(url_for('login', next=request.url))
        return f(*args, **kwargs)
     return decorated_function

class LoginForm(Form):
    username = TextField('Username', [validators.Length(min=4, max=25)])
    password = PasswordField('Password', [validators.Required()])

@app.route('/app', methods=['GET','POST'])
@login_required
def index():
    return 'Index'

@app.route('/', methods=['GET','POST'])
def login():
    form = LoginForm(request.form)
    if request.method == 'POST':
        if form.validate():
           try:
               dsn = cx_Oracle.makedsn(app.config['DB_HOST'],
                                       app.config['DB_PORT'], app.config['DB_SID'])
               g.db = cx_Oracle.connect(form.username.data,
                                        form.password.data, dsn)
           except cx_Oracle.DatabaseError as e:
               flash(unicode(e), 'error')
               return render_template('login.html', form=form)
            return redirect(url_for('index'))
        else:
            return render_template('login.html', form=form)
    else:
        return render_template('login.html', form=form)

AttributeError: '_RequestGlobals' object has no attribute 'db'

Answer 1

The reason why this snippet is not working is this line

    if g.db is None:

You are accessing an attribute which does not belong to g. Add these lines of code:

    @app.before_request
    def before_request():
         g.db = None

Functions marked with before_request() are called before a request and passed no arguments.

Regarding Connection Pooling

There is an ORM and SQL Toolkit called SQLAlchemy in Python which does connection pooling automatically for you. It also manages g object of flask and creates prepared SQL statements which are much more secure.

It has two parts:

    1. Core which is a SQL abstraction toolkit.
    2. ORM is an optional package which builds on top of Core.

So if you don't want to use ORM then simply use its Core with all the features intact. Check here.

It supports Oracle via cx_Oracle and it can be set up as mentioned in SQLAlchemy Docs here.

Check this question for some more discussion on connection pooling in python.

Answer 2

Sounds like you need to implement connection pooling. Instead of requesting a new connection for each request (which does not scale at all), you simply check out a connection from the pool with the required attributes. If no suitable connection is available, the pool would create a new one. The pool would also need to handle the opposite case of course in closing connections that haven't been used for some time.

You might want to check if such a pool is available for python. For java oracle supports this with UCP and for OCI with the session pool. If I'm not mistaken a connection pool is even provided for .Net.