连接到Flask中的数据库，哪种方法更好?

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

方法一:使用来自http://flask.pocoo.org/docs/tutorial/dbcon/和http://flask.pocoo.org/docs/patterns/sqlite3/的特殊g对象。

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

方法二:使用神秘的_app_ctx_stack，从https://github.com/mitsuhiko/flask/blob/master/examples/flaskr/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?

哪种方法更好?的区别是什么?

4 个解决方案

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

两者之间的区别是，方法一创建了g上的连接。无论您是否需要它，方法2只在应用程序上下文中第一次调用get_db时创建连接。

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.

当你点击/使用@app时，你会看到。在使用_app_context路由时，您获得一个连接，当您调用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).

为了公平起见，您还可以向g添加一个描述符，它将执行相同的惰性连接(或者在现实生活中，从连接池中获取连接)。在这两种情况下，你都可以使用更神奇的方法(werkzeug.local)。本地代理是精确的)创建您自己的自定义线程本地，如g、current_app和请求(包括其他)。

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

然后，在整个应用程序中，始终通过get_request_connection获取您的连接，就像您将get_db函数一样。简单,效率高。基本上，两者都是最好的。

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.

回想起来，我真的不喜欢这些都是全球性的方法，但我认为原因在于Flask的工作方式:它给你“globals”(globals)，实际上指向了thread-local。

#3

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

我推荐Flask-SQLAlchemy，它将SQLAlchemy扩展到Flask中，因此它支持许多不同的数据库。(从Flask-SQLAlchemy文档示例)

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.

现在，您只需导入/使用User类来访问数据库中的用户表。

Create new users:

创建新用户:

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

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'>

#4

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.

方法2可能是为flask扩展集成设计的(例如，应用程序-ctx-stack的例子和说明)。虽然它们可能有非常相似的效果，但方法一应该用于正常情况。