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

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?

Answer 1

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

Answer 2

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.