Connect to the database in a jar, which approach is better?

Question

The first method: using a 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 the method creates a connection on g.db if you need it or not, and the second method creates a connection only when the get_db called the first time in this application context.

If you compare them, use this setting:

 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 will see that when you press / using the @app.before_request ( g.db ) g.db you get a connection whether you use it or not, and when you use the _app_context route _app_context you only get a connection when you call get_db .

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

Answer 2

The first problem is the acquisition of compounds, even if they are not needed. The second one has a drawback in the game with internal components of a third-party structure, plus it is quite unreadable.

Of the two, only one, the second is probably the best choice. Not only does it not get a connection for routes that don't need it, it doesn't get a connection if you go on any code path that it doesn't need, even if other code paths on the route require one. (For example, if you have some form of verification, you only need a connection, if the verification passes, it will not open it when the verification fails.) You only get the connections right before you use them with this setting.

However, you can avoid messing around with the internal elements and get all these benefits. Personally, I created my own 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 application, always get your connection through get_request_connection , just like your get_db function. Simple and high performance. In principle, the best of both worlds.

Edit:

In retrospect, I really don't like the fact that these are global methods, but I think the reason for this is because Flask works this way: it gives you “global” ones that actually point to stream locators.

Answer 3

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 simply import / use the User class to access the User table in your database.

Create new users:

 >>> from yourapplication import User >>> admin = User('admin', '[e-mail]') >>> guest = User('guest', '[e-mail]') 

Add users to the database:

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

The request for users is already in the database:

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

Answer 4

I would go with one method - more readable and less "hacker".

Method 2 is probably intended to integrate flask extensions ( example and explanation of app-ctx-stack ). Although they probably have a very similar effect, the method should be used for normal cases.