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


Question

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?

1
56
5/1/2013 2:36:42 AM

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).

34
5/14/2013 3:40:46 AM

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon