Connect to a Database in Flask, Which Approach is better?

Method One: Using special g object from http://flask.pocoo.org/docs/tutorial/dbcon/ and http://flask.pocoo.org/docs/patterns/sqlite3/

import sqlite3
from flask import g

DATABASE = '/path/to/database.db'

def connect_db():
    return sqlite3.connect(DATABASE)

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

@app.teardown_request
def teardown_request(exception):
    if hasattr(g, 'db'):
        g.db.close()

Method Two: Using Mysterious _app_ctx_stack from https://github.com/mitsuhiko/flask/blob/master/examples/flaskr/flaskr.py

from sqlite3 import dbapi2 as sqlite3
from flask import Flask, request, session, g, redirect, url_for, abort, \
     render_template, flash, _app_ctx_stack
def get_db():
    """Opens a new database connection if there is none yet for the
    current application context.
    """
    top = _app_ctx_stack.top
    if not hasattr(top, 'sqlite_db'):
        top.sqlite_db = sqlite3.connect(app.config['DATABASE'])
    return top.sqlite_db


@app.teardown_appcontext
def close_db_connection(exception):
    """Closes the database again at the end of the request."""
    top = _app_ctx_stack.top
    if hasattr(top, 'sqlite_db'):
        top.sqlite_db.close()

Which method is better? What is the difference?

The difference between the two is that method one creates a connection on g.db whether you need it or not while method two only creates the connection when you call get_db for the first time in that application context.

If you compare the two, using this setup:

yourapp = Flask(__name__)

# setup g.db or app_context here
# Add a logging statement (print will do)
# to the get_db or before_request functions
# that simply says "Getting the db connection ..."
# Then access / and /1

@yourapp.route("/")
def index():
    return "No database calls here!"

@yourapp.route("/<int:post_id>")
def show_post(post_id):
    # get a post using g.db or get_db
    return "Went to the DB and got {!r}".format(post)

You'll see that when you hit / using the @app.before_request setup (g.db) you get a connection whether you use it or not, while using the _app_context route you only get a connection when you call get_db.

To be fair, you can also add a descriptor to g that will do the same lazy connecting (or in real life, acquiring a connection from a connection pool). And in both cases you can use a bit more magic (werkzeug.local.LocalProxy to be precise) to create your own custom thread local that acts like g, current_app and request (among others).

The first has the issue of acquiring connections even when they aren't needed. The second has the downside of playing with internals of a third party framework, plus it's pretty unreadable.

Of the two alone, the second is probably the better choice. Not only does it not acquire a connection for routes that don't need one, it doesn't acquire a connection if you go down any code path that doesn't need one, even if other code paths in the route require one. (For example, if you have some form validation, you only need the connection if the validation passes; this won't open one when the validation fails.) You only acquire connections right before you use them with this set up.

However, you can avoid messing with the internals and still get all these benefits. Personally, I created my own little global methods:

import flask
import sqlite3

def request_has_connection():
    return hasattr(flask.g, 'dbconn')

def get_request_connection():
    if not request_has_connection():
        flask.g.dbconn = sqlite3.connect(DATABASE)
        # Do something to make this connection transactional.
        # I'm not familiar enough with SQLite to know what that is.
    return flask.g.dbconn

@app.teardown_request
def close_db_connection(ex):
    if request_has_connection():
        conn = get_request_connection()
        # Rollback
        # Alternatively, you could automatically commit if ex is None
        # and rollback otherwise, but I question the wisdom 
        # of automatically committing.
        conn.close()

Then, throughout the app, always get your connection via get_request_connection, just as you would your get_db function. Straightforward and high efficiency. Basically, the best of both worlds.

Edit:

In retrospect, I really dislike the fact these are global methods, but I think the reason for it is because that's how Flask works: it gives you "globals" that actually point to thread-locals.

I recommend Flask-SQLAlchemy, which extends SQLAlchemy for use in Flask, so it supports many different databases. (Example from Flask-SQLAlchemy documentation)

Setup:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

Now, you can just import/use the User class to access the User table in your database.

Create new users:

>>> from yourapplication import User
>>> admin = User('admin', '[email protected]')
>>> guest = User('guest', '[email protected]')

Add the users to the database:

>>> db.session.add(admin)
>>> db.session.add(guest)
>>> db.session.commit()

Query for users already in database:

>>> users = User.query.all()
[<User u'admin'>, <User u'guest'>]
>>> admin = User.query.filter_by(username='admin').first()
<User u'admin'>

I'd go with method one - more readable and less "hackish".

The method 2 is probably designed for flask extensions integration (example and explanation of app-ctx-stack). Although they probably have very similar effect, method one should be used for normal cases.

To preserve the database connection within the same flask session you may use the application context and assign database connection. If the connection breaks,the context will try to establish the connection back as it polls the connection object continuously.

from flask import Flask
application = Flask(__name__)


def connect_to_database():
  db_handler = SqliteDBConnect("uid={0};""pwd={1}".format(UID, PWD),
                                table_prefix="{}".format(TBL_PRFX))
  return db_handler

fd = {'_database': None}
def get_db():
  db = fd['_database']
  if not isinstance(db, SqliteDBConnect):
    fd['_database'] = connect_to_database()
    db = fd['_database']
return db

with application.app_context():
  #Get DB connection from application's context
  db = LocalProxy(lambda: get_db())