Flask SQLAlchemy 中文文档

Flask-SQLAlchemy 是一个为您的 Flask 应用增加 SQLAlchemy 支持的扩展。它需要 SQLAlchemy 0.6 或者更高的版本。它致力于简化在 Flask 中 SQLAlchemy 的使用，提供了有用的默认值和额外的助手来更简单地完成常见任务。

Flask-SQLAlchemy 使用起来非常有趣，对于基本应用十分容易使用，并且对于大型项目易于扩展。有关完整的指南，请参阅 SQLAlchemy 的 API 文档。

一个最小应用

常见情况下对于只有一个 Flask 应用，所有您需要做的事情就是创建 Flask 应用，选择加载配置接着创建 SQLAlchemy 对象时候把 Flask 应用传递给它作为参数。

一旦创建，这个对象就包含 sqlalchemy 和 sqlalchemy.orm 中的所有函数和助手。此外它还提供一个名为 Model 的类，用于作为声明模型时的 delarative 基类:

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

为了创建初始数据库，只需要从交互式 Python shell 中导入 db 对象并且调用 SQLAlchemy.create_all() 方法来创建表和数据库:

>>> from yourapplication import db
>>> db.create_all()

Boom, 您的数据库已经生成。现在来创建一些用户:

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

但是它们还没有真正地写入到数据库中，因此让我们来确保它们已经写入到数据库中:

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

访问数据库中的数据也是十分简单的:

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

简单的关系

SQLAlchemy 连接到关系型数据库，关系型数据最擅长的东西就是关系。因此，我们将创建一个使用两张相互关联的表的应用作为例子:

from datetime import datetime

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80))
    body = db.Column(db.Text)
    pub_date = db.Column(db.DateTime)

    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category',
        backref=db.backref('posts', lazy='dynamic'))

    def __init__(self, title, body, category, pub_date=None):
        self.title = title
        self.body = body
        if pub_date is None:
            pub_date = datetime.utcnow()
        self.pub_date = pub_date
        self.category = category

    def __repr__(self):
        return '<Post %r>' % self.title

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '<Category %r>' % self.name

首先让我们创建一些对象:

>>> py = Category('Python')
>>> p = Post('Hello Python!', 'Python is pretty cool', py)
>>> db.session.add(py)
>>> db.session.add(p)

现在因为我们在 backref 中声明了 posts 作为动态关系，查询显示为:

>>> py.posts
<sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x1027d37d0>

它的行为像一个普通的查询对象，因此我们可以查询与我们测试的 “Python” 分类相关的所有文章(posts):

>>> py.posts.all()
[<Post 'Hello Python!'>]

启蒙之路

您仅需要知道与普通的 SQLAlchemy 不同之处:

1. SQLAlchemy 允许您访问下面的东西:
   • sqlalchemy 和 sqlalchemy.orm 下所有的函数和类
   • 一个叫做 session 的预配置范围的会话(session)
   • metadata 属性
   • engine 属性
   • SQLAlchemy.create_all() 和 SQLAlchemy.drop_all()，根据模型用来创建以及删除表格的方法
   • 一个 Model 基类，即是一个已配置的声明(declarative)的基础(base)
2. Model 声明基类行为类似一个常规的 Python 类，不过有个 query 属性，可以用来查询模型 (Model 和 BaseQuery)
3. 您必须提交会话，但是没有必要在每个请求后删除它(session)，Flask-SQLAlchemy 会帮您完成删除操作。

如果您计划只使用一个应用程序，您大可跳过这一章节。只需要把您的应用程序传给 SQLAlchemy 构造函数，一般情况下您就设置好了。然而您想要使用不止一个应用或者在一个函数中动态地创建应用的话，您需要仔细阅读。

如果您在一个函数中定义您的应用，但是 SQLAlchemy 对象是全局的，后者如何知道前者了？答案就是 init_app() 函数:

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

db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    db.init_app(app)
    return app

它所做的是准备应用以与 SQLAlchemy 共同工作。然而现在不把 SQLAlchemy 对象绑定到您的应用。为什么不这么做？ 因为那里可能创建不止一个应用。

那么 SQLAlchemy 是如何知道您的应用的？您必须配置一个应用上下文。如果您在一个 Flask 视图函数中进行工作，这会自动实现。但如果您在交互式的 shell 中，您需要手动这么做。(参阅 创建应用上下文 )。

简而言之，像这样做:

>>> from yourapp import create_app
>>> app = create_app()
>>> app.app_context().push()

在脚本里面使用 with 声明都样也有作用:

def my_function():
    with app.app_context():
        user = db.User(...)
        db.session.add(user)
        db.session.commit()

Flask-SQLAlchemy 里的一些函数也可以接受要在其上进行操作的应用作为参数:

>>> from yourapp import db, create_app
>>> db.create_all(app=create_app())

下面是 Flask-SQLAlchemy 中存在的配置值。Flask-SQLAlchemy 从您的 Flask 主配置中加载这些值。 注意其中的一些在引擎创建后不能修改，所以确保尽早配置且不在运行时修改它们。

配置键

Flask-SQLAlchemy 扩展能够识别的配置键的清单:

New in version 0.8: 增加 SQLALCHEMY_NATIVE_UNICODE, SQLALCHEMY_POOL_SIZE, SQLALCHEMY_POOL_TIMEOUT 和 SQLALCHEMY_POOL_RECYCLE 配置键。

New in version 0.12: 增加 SQLALCHEMY_BINDS 配置键。

New in version 0.17: 增加 SQLALCHEMY_MAX_OVERFLOW 配置键。

New in version 2.0: 增加 SQLALCHEMY_TRACK_MODIFICATIONS 配置键。

连接 URI 格式

完整连接 URI 格式列表请跳转到 SQLAlchemy 下面的文档(支持的数据库)。这里展示了一些常见的连接字符串。

SQLAlchemy 把一个引擎的源表示为一个连同设定引擎选项的可选字符串参数的 URI。URI 的形式是:

dialect+driver://username:[email protected]:port/database

该字符串中的许多部分是可选的。如果没有指定驱动器，会选择默认的（确保在这种情况下 不 包含 + ）。

Postgres:

postgresql://scott:[email protected]/mydatabase

MySQL:

mysql://scott:[email protected]/mydatabase

Oracle:

oracle://scott:[email protected]:1521/sidname

SQLite (注意开头的四个斜线):

sqlite:////absolute/path/to/foo.db

使用自定义的元数据和命名约定

你可以使用一个自定义的 MetaData 对象来构造 SQLAlchemy 对象。这允许你指定一个 自定义约束命名约定。这样做对数据库的迁移是很重要的。因为 SQL 没有定义一个标准的命名约定，无法保证数据库之间实现是兼容的。你可以自定义命名约定像 SQLAlchemy 文档建议那样:

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

convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

metadata = MetaData(naming_convention=convention)
db = SQLAlchemy(app, metadata=metadata)

更多关于 MetaData 的信息，请查看官方的文档。

通常下，Flask-SQLAlchemy 的行为就像一个来自 declarative 扩展配置正确的 declarative 基类。因此，我们强烈建议您阅读 SQLAlchemy 文档以获取一个全面的参考。尽管如此，我们这里还是给出了最常用的示例。

需要牢记的事情:

• 您的所有模型的基类叫做 db.Model。它存储在您必须创建的 SQLAlchemy 实例上。 细节请参阅 快速入门。
• 有一些部分在 SQLAlchemy 上是必选的，但是在 Flask-SQLAlchemy 上是可选的。 比如表名是自动地为您设置好的，除非您想要覆盖它。它是从转成小写的类名派生出来的，即 “CamelCase” 转换为 “camel_case”。

简单示例

一个非常简单的例子:

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

用 Column 来定义一列。列名就是您赋值给那个变量的名称。如果您想要在表中使用不同的名称，您可以提供一个想要的列名的字符串作为可选第一个参数。主键用 primary_key=True 标记。可以把多个键标记为主键，此时它们作为复合主键。

列的类型是 Column 的第一个参数。您可以直接提供它们或进一步规定（比如提供一个长度）。下面的类型是最常用的:

一对多(one-to-many)关系

最为常见的关系就是一对多的关系。因为关系在它们建立之前就已经声明，您可以使用 字符串来指代还没有创建的类(例如如果 Person 定义了一个到 Article 的关系，而 Article 在文件的后面才会声明)。

关系使用 relationship() 函数表示。然而外键必须用类 sqlalchemy.schema.ForeignKey 来单独声明:

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    addresses = db.relationship('Address', backref='person',
                                lazy='dynamic')

class Address(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50))
    person_id = db.Column(db.Integer, db.ForeignKey('person.id'))

db.relationship() 做了什么？这个函数返回一个可以做许多事情的新属性。在本案例中，我们让它指向 Address 类并加载多个地址。它如何知道会返回不止一个地址？因为 SQLALchemy 从您的声明中猜测了一个有用的默认值。 如果您想要一对一关系，您可以把 uselist=False 传给 relationship() 。

那么 backref 和 lazy 意味着什么了？backref 是一个在 Address 类上声明新属性的简单方法。您也可以使用 my_address.person 来获取使用该地址(address)的人(person)。lazy 决定了 SQLAlchemy 什么时候从数据库中加载数据:

• 'select' (默认值) 就是说 SQLAlchemy 会使用一个标准的 select 语句必要时一次加载数据。
• 'joined' 告诉 SQLAlchemy 使用 JOIN 语句作为父级在同一查询中来加载关系。
• 'subquery' 类似 'joined' ，但是 SQLAlchemy 会使用子查询。
• 'dynamic' 在有多条数据的时候是特别有用的。不是直接加载这些数据，SQLAlchemy 会返回一个查询对象，在加载数据前您可以过滤（提取）它们。

您如何为反向引用（backrefs）定义惰性（lazy）状态？使用 backref() 函数:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    addresses = db.relationship('Address',
        backref=db.backref('person', lazy='joined'), lazy='dynamic')

多对多(many-to-many)关系

如果您想要用多对多关系，您需要定义一个用于关系的辅助表。对于这个辅助表， 强烈建议 不 使用模型，而是采用一个实际的表:

tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('page_id', db.Integer, db.ForeignKey('page.id'))
)

class Page(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', secondary=tags,
        backref=db.backref('pages', lazy='dynamic'))

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)

这里我们配置 Page.tags 加载后作为标签的列表，因为我们并不期望每页出现太多的标签。而每个 tag 的页面列表（ Tag.pages）是一个动态的反向引用。 正如上面提到的，这意味着您会得到一个可以发起 select 的查询对象。

现在您已经有了 declared models，是时候从数据库中查询数据。我们将会使用 快速入门 章节中定义的数据模型。

插入记录

在查询数据之前我们必须先插入数据。您的所有模型都应该有一个构造函数，如果您 忘记了，请确保加上一个。只有您自己使用这些构造函数而 SQLAlchemy 在内部不会使用它， 所以如何定义这些构造函数完全取决与您。

向数据库插入数据分为三个步骤:

1. 创建 Python 对象
2. 把它添加到会话
3. 提交会话

这里的会话不是 Flask 的会话，而是 Flask-SQLAlchemy 的会话。它本质上是一个 数据库事务的加强版本。它是这样工作的:

>>> from yourapp import User
>>> me = User('admin', '[email protected]')
>>> db.session.add(me)
>>> db.session.commit()

好吧，这不是很难吧。但是在您把对象添加到会话之前， SQLAlchemy 基本不考虑把它加到事务中。这是好事，因为您仍然可以放弃更改。比如想象 在一个页面上创建文章，但是您只想把文章传递给模板来预览渲染，而不是把它存进数据库。

调用 add() 函数会添加对象。它会发出一个 INSERT 语句给数据库，但是由于事务仍然没有提交，您不会立即得到返回的 ID 。如果您提交，您的用户会有一个 ID:

>>> me.id
1

删除记录

删除记录是十分类似的，使用 delete() 代替 add():

>>> db.session.delete(me)
>>> db.session.commit()

查询记录

那么我们怎么从数据库中查询数据？为此，Flask-SQLAlchemy 在您的 Model 类上提供了 query 属性。当您访问它时，您会得到一个新的所有记录的查询对象。在使用 all() 或者 first() 发起查询之前可以使用方法 filter() 来过滤记录。如果您想要用主键查询的话，也可以使用 get()。

下面的查询假设数据库中有如下条目:

通过用户名查询用户:

>>> peter = User.query.filter_by(username='peter').first()
>>> peter.id
1
>>> peter.email
u'[email protected]'

同上但是查询一个不存在的用户名返回 None:

>>> missing = User.query.filter_by(username='missing').first()
>>> missing is None
True

使用更复杂的表达式查询一些用户:

>>> User.query.filter(User.email.endswith('@example.com')).all()
[<User u'admin'>, <User u'guest'>]

按某种规则对用户排序:

>>> User.query.order_by(User.username)
[<User u'admin'>, <User u'guest'>, <User u'peter'>]

限制返回用户的数量:

>>> User.query.limit(1).all()
[<User u'admin'>]

用主键查询用户:

>>> User.query.get(1)
<User u'admin'>

在视图中查询

当您编写 Flask 视图函数，对于不存在的条目返回一个 404 错误是非常方便的。因为这是一个很常见的问题，Flask-SQLAlchemy 为了解决这个问题提供了一个帮助函数。可以使用 get_or_404() 来代替 get()，使用 first_or_404() 来代替 first()。这样会抛出一个 404 错误，而不是返回 None:

@app.route('/user/<username>')
def show_user(username):
    user = User.query.filter_by(username=username).first_or_404()
    return render_template('show_user.html', user=user)

从 0.12 开始，Flask-SQLAlchemy 可以容易地连接到多个数据库。为了实现这个功能，预配置了 SQLAlchemy 来支持多个 “binds”。

什么是绑定(binds)? 在 SQLAlchemy 中一个绑定(bind)是能执行 SQL 语句并且通常是一个连接或者引擎类的东东。在 Flask-SQLAlchemy 中，绑定(bind)总是背后自动为您创建好的引擎。这些引擎中的每个之后都会关联一个短键（bind key）。这个键会在模型声明时使用来把一个模型关联到一个特定引擎。

如果模型没有关联一个特定的引擎的话，就会使用默认的连接(SQLALCHEMY_DATABASE_URI 配置值)。

示例配置

下面的配置声明了三个数据库连接。特殊的默认值和另外两个分别名为 users（用于用户）和 appmeta 连接到一个提供只读访问应用内部数据的 sqlite 数据库）:

SQLALCHEMY_DATABASE_URI = 'postgres://localhost/main'
SQLALCHEMY_BINDS = {
    'users':        'mysqldb://localhost/users',
    'appmeta':      'sqlite:////path/to/appmeta.db'
}

创建和删除表

create_all() 和 drop_all() 方法默认作用于所有声明的绑定(bind)，包括默认的。这个行为可以通过提供 bind 参数来定制。它可以是单个绑定(bind)名, '__all__' 指向所有绑定(binds)或一个绑定(bind)名的列表。默认的绑定(bind)(SQLALCHEMY_DATABASE_URI) 名为 None:

>>> db.create_all()
>>> db.create_all(bind=['users'])
>>> db.create_all(bind='appmeta')
>>> db.drop_all(bind=None)

引用绑定(Binds)

当您声明模型时，您可以用 __bind_key__ 属性指定绑定(bind):

class User(db.Model):
    __bind_key__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)

bind key 存储在表中的 info 字典中作为 'bind_key' 键值。了解这个很重要，因为当您想要直接创建一个表对象时，您会需要把它放在那:

user_favorites = db.Table('user_favorites',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('message_id', db.Integer, db.ForeignKey('message.id')),
    info={'bind_key': 'users'}
)

如果您在模型上指定了 __bind_key__ ，您可以用它们准确地做您想要的。模型会自行连 接到指定的数据库连接。

您可以订阅如下这些信号以便在更新提交到数据库之前以及之后得到通知。

如果配置中 SQLALCHEMY_TRACK_MODIFICATIONS 启用的话，这些更新变化才能被追踪。

New in version 0.10.

Changed in version 2.1: before_models_committed 正确地被触发。

Deprecated since version 2.1: 在以后的版本中，这个配置项默认是禁用的。

存在以下两个信号:

models_committed

这个信号在修改的模型提交到数据库时发出。发送者是发送修改的应用，模型和操作描述符以 (model, operation) 形式作为元组，这样的元组列表传递给接受者的 changes 参数。

该模型是发送到数据库的模型实例，当一个模型已经插入，操作是 'insert' ，而已删除是 'delete' ，如果更新了任何列，会是 'update' 。

before_models_committed

工作机制和 models_committed 完全一样，但是在提交之前发送。

这部分文档记录了 Flask-SQLAlchemy 里的所有公开的类和函数。

配置

class flask.ext.sqlalchemy.SQLAlchemy(app=None, use_native_unicode=True, session_options=None, metadata=None, query_class=<class 'flask_sqlalchemy.BaseQuery'>, model_class=<class 'flask_sqlalchemy.Model'>)

This class is used to control the SQLAlchemy integration to one or more Flask applications. Depending on how you initialize the object it is usable right away or will attach as needed to a Flask application.

There are two usage modes which work very similarly. One is binding the instance to a very specific Flask application:

app = Flask(__name__)
db = SQLAlchemy(app)

The second possibility is to create the object once and configure the application later to support it:

db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    db.init_app(app)
    return app

The difference between the two is that in the first case methods like create_all() and drop_all() will work all the time but in the second case a flask.Flask.app_context() has to exist.

By default Flask-SQLAlchemy will apply some backend-specific settings to improve your experience with them. As of SQLAlchemy 0.6 SQLAlchemy will probe the library for native unicode support. If it detects unicode it will let the library handle that, otherwise do that itself. Sometimes this detection can fail in which case you might want to set use_native_unicode (or the SQLALCHEMY_NATIVE_UNICODE configuration key) to False. Note that the configuration key overrides the value you pass to the constructor.

This class also provides access to all the SQLAlchemy functions and classes from the sqlalchemy and sqlalchemy.orm modules. So you can declare models like this:

class User(db.Model):
    username = db.Column(db.String(80), unique=True)
    pw_hash = db.Column(db.String(80))

You can still use sqlalchemy and sqlalchemy.orm directly, but note that Flask-SQLAlchemy customizations are available only through an instance of this SQLAlchemy class. Query classes default to BaseQuery for db.Query, db.Model.query_class, and the default query_class for db.relationship and db.backref. If you use these interfaces through sqlalchemy and sqlalchemy.orm directly, the default query class will be that of sqlalchemy.

Check types carefully

Don’t perform type or isinstance checks against db.Table, which emulates Table behavior but is not a class. db.Table exposes the Table interface, but is a function which allows omission of metadata.

You may also define your own SessionExtension instances as well when defining your SQLAlchemy class instance. You may pass your custom instances to the session_extensions keyword. This can be either a single SessionExtension instance, or a list of SessionExtension instances. In the following use case we use the VersionedListener from the SQLAlchemy versioning examples.:

from history_meta import VersionedMeta, VersionedListener

app = Flask(__name__)
db = SQLAlchemy(app, session_extensions=[VersionedListener()])

class User(db.Model):
    __metaclass__ = VersionedMeta
    username = db.Column(db.String(80), unique=True)
    pw_hash = db.Column(db.String(80))

The session_options parameter can be used to override session options. If provided it’s a dict of parameters passed to the session’s constructor.

New in version 0.10: The session_options parameter was added.

New in version 0.16: scopefunc is now accepted on session_options. It allows specifying a custom function which will define the SQLAlchemy session’s scoping.

New in version 2.1: The metadata parameter was added. This allows for setting custom naming conventions among other, non-trivial things.

Query

The BaseQuery class.

apply_driver_hacks(app, info, options)

This method is called before engine creation and used to inject driver specific hacks into the options. The options parameter is a dictionary of keyword arguments that will then be used to call the sqlalchemy.create_engine() function.

The default implementation provides some saner defaults for things like pool sizes for MySQL and sqlite. Also it injects the setting of SQLALCHEMY_NATIVE_UNICODE.

create_all(bind='__all__', app=None)

Creates all tables.

Changed in version 0.12: Parameters were added

create_scoped_session(options=None)

Helper factory method that creates a scoped session. It internally calls create_session().

create_session(options)

Creates the session. The default implementation returns a SignallingSession.

New in version 2.0.

drop_all(bind='__all__', app=None)

Drops all tables.

Changed in version 0.12: Parameters were added

engine

Gives access to the engine. If the database configuration is bound to a specific application (initialized with an application) this will always return a database connection. If however the current application is used this might raise a RuntimeError if no application is active at the moment.

get_app(reference_app=None)

Helper method that implements the logic to look up an application.

get_binds(app=None)

Returns a dictionary with a table->engine mapping.

This is suitable for use of sessionmaker(binds=db.get_binds(app)).

get_engine(app, bind=None)

Returns a specific engine.

New in version 0.12.

get_tables_for_bind(bind=None)

Returns a list of all tables relevant for a bind.

init_app(app)

This callback can be used to initialize an application for the use with this database setup. Never use a database in the context of an application not initialized that way or connections will leak.

make_connector(app, bind=None)

Creates the connector for a given state and bind.

make_declarative_base(model, metadata=None)

Creates the declarative base.

metadata

Returns the metadata

reflect(bind='__all__', app=None)

Reflects tables from the database.

Changed in version 0.12: Parameters were added

模型

class flask.ext.sqlalchemy.Model

Baseclass for custom user models.

__bind_key__

Optionally declares the bind to use. None refers to the default bind. For more information see 绑定多个数据库.

__tablename__

The name of the table in the database. This is required by SQLAlchemy; however, Flask-SQLAlchemy will set it automatically if a model has a primary key defined. If the __table__ or __tablename__ is set explicitly, that will be used instead.

query = None

an instance of query_class. Can be used to query the database for instances of this model.

query_class

the query class used. The query attribute is an instance of this class. By default a BaseQuery is used.

alias of BaseQuery

class flask.ext.sqlalchemy.BaseQuery(entities, session=None)

The default query object used for models, and exposed as Query. This can be subclassed and replaced for individual models by setting the query_class attribute. This is a subclass of a standard SQLAlchemy Query class and has all the methods of a standard query as well.

all()

Return the results represented by this query as a list. This results in an execution of the underlying query.

order_by(*criterion)

apply one or more ORDER BY criterion to the query and return the newly resulting query.

limit(limit)

Apply a LIMIT to the query and return the newly resulting query.

offset(offset)

Apply an OFFSET to the query and return the newly resulting query.

first()

Return the first result of this query or None if the result doesn’t contain any rows. This results in an execution of the underlying query.

first_or_404()

Like first() but aborts with 404 if not found instead of returning None.

get(ident)

Return an instance based on the given primary key identifier, or None if not found.

E.g.:

my_user = session.query(User).get(5)

some_object = session.query(VersionedFoo).get((5, 10))

get() is special in that it provides direct access to the identity map of the owning Session. If the given primary key identifier is present in the local identity map, the object is returned directly from this collection and no SQL is emitted, unless the object has been marked fully expired. If not present, a SELECT is performed in order to locate the object.

get() also will perform a check if the object is present in the identity map and marked as expired - a SELECT is emitted to refresh the object as well as to ensure that the row is still present. If not, ObjectDeletedError is raised.

get() is only used to return a single mapped instance, not multiple instances or individual column constructs, and strictly on a single primary key value. The originating Query must be constructed in this way, i.e. against a single mapped entity, with no additional filtering criterion. Loading options via options() may be applied however, and will be used if the object is not yet locally present.

A lazy-loading, many-to-one attribute configured by relationship(), using a simple foreign-key-to-primary-key criterion, will also use an operation equivalent to get() in order to retrieve the target value from the local identity map before querying the database. See /orm/loading_relationships for further details on relationship loading.

Parameters: ident – A scalar or tuple value representing the primary key. For a composite primary key, the order of identifiers corresponds in most cases to that of the mapped Table object’s primary key columns. For a mapper() that was given the primary key argument during construction, the order of identifiers corresponds to the elements present in this collection.

Returns: The object instance, or None.

get_or_404(ident)

Like get() but aborts with 404 if not found instead of returning None.

paginate(page=None, per_page=None, error_out=True)

Returns per_page items from page page. By default it will abort with 404 if no items were found and the page was larger than 1. This behavor can be disabled by setting error_out to False.

If page or per_page are None, they will be retrieved from the request query. If the values are not ints and error_out is true, it will abort with 404. If there is no request or they aren’t in the query, they default to page 1 and 20 respectively.

Returns an Pagination object.

会话

class flask.ext.sqlalchemy.SignallingSession(db, autocommit=False, autoflush=True, app=None, **options)

The signalling session is the default session that Flask-SQLAlchemy uses. It extends the default session system with bind selection and modification tracking.

If you want to use a different session you can override the SQLAlchemy.create_session() function.

New in version 2.0.

New in version 2.1: The binds option was added, which allows a session to be joined to an external transaction.

app = None

The application that this session belongs to.

实用工具

class flask.ext.sqlalchemy.Pagination(query, page, per_page, total, items)

Internal helper class returned by BaseQuery.paginate(). You can also construct it from any other SQLAlchemy query object if you are working with other libraries. Additionally it is possible to pass None as query object in which case the prev() and next() will no longer work.

has_next

True if a next page exists.

has_prev

True if a previous page exists

items = None

the items for the current page

iter_pages(left_edge=2, left_current=2, right_current=5, right_edge=2)

Iterates over the page numbers in the pagination. The four parameters control the thresholds how many numbers should be produced from the sides. Skipped page numbers are represented as None. This is how you could render such a pagination in the templates:

{% macro render_pagination(pagination, endpoint) %}
   class=pagination>
  {%- for page in pagination.iter_pages() %}
    {% if page %}
      {% if page != pagination.page %}
        <a href="{{ url_for(endpoint, page=page) }}">{{ page }}</a>
      {% else %}
        <strong>{{ page }}</strong>
      {% endif %}
    {% else %}
       class=ellipsis>…
    {% endif %}
  {%- endfor %}

{% endmacro %}

next(error_out=False)

Returns a Pagination object for the next page.

next_num

Number of the next page

page = None

the current page number (1 indexed)

pages

The total number of pages

per_page = None

the number of items to be displayed on a page.

prev(error_out=False)

Returns a Pagination object for the previous page.

prev_num

Number of the previous page.

query = None

the unlimited query object that was used to create this pagination object.

total = None

the total number of items matching the query

flask.ext.sqlalchemy.get_debug_queries()

In debug mode Flask-SQLAlchemy will log all the SQL queries sent to the database. This information is available until the end of request which makes it possible to easily ensure that the SQL generated is the one expected on errors or in unittesting. If you don’t want to enable the DEBUG mode for your unittests you can also enable the query recording by setting the 'SQLALCHEMY_RECORD_QUERIES' config variable to True. This is automatically enabled if Flask is in testing mode.

The value returned will be a list of named tuples with the following attributes:

statement

The SQL statement issued

parameters

The parameters for the SQL statement

start_time / end_time

Time the query started / the results arrived. Please keep in mind that the timer function used depends on your platform. These values are only useful for sorting or comparing. They do not necessarily represent an absolute timestamp.

duration

Time the query took in seconds

context

A string giving a rough estimation of where in your application query was issued. The exact format is undefined so don’t try to reconstruct filename or function name.

在这里您可以看到每一个 Flask-SQLAlchemy 发布版本的变化的完整列表。

Version 2.1

In development

• Table names are automatically generated in more cases, including subclassing mixins and abstract models.

Version 2.0

Released on August 29th 2014, codename Bohrium

• Changed how the builtin signals are subscribed to skip non Flask-SQLAlchemy sessions. This will also fix the attribute error about model changes not existing.
• Added a way to control how signals for model modifications are tracked.
• Made the SignallingSession a public interface and added a hook for customizing session creation.
• If the bind parameter is given to the signalling session it will no longer cause an error that a parameter is given twice.
• Added working table reflection support.
• Enabled autoflush by default.
• Consider SQLALCHEMY_COMMIT_ON_TEARDOWN harmful and remove from docs.

Version 1.0

Released on July 20th 2013, codename Aurum

• Added Python 3.3 support.
• Dropped 2.5 compatibility.
• Various bugfixes
• Changed versioning format to do major releases for each update now.

Version 0.16

• New distribution format (flask_sqlalchemy)
• Added support for Flask 0.9 specifics.

Version 0.15

• Added session support for multiple databases

Version 0.14

• Make relative sqlite paths relative to the application root.

Version 0.13

• Fixed an issue with Flask-SQLAlchemy not selecting the correct binds.

Version 0.12

• Added support for multiple databases.
• Expose Flask-SQLAlchemy’s BaseQuery as db.Query.
• Set default query_class for db.relation, db.relationship, and db.dynamic_loader to Flask-SQLAlchemy’s BaseQuery.
• Improved compatibility with Flask 0.7.

Version 0.11

• Fixed a bug introduced in 0.10 with alternative table constructors.

Version 0.10

• Added support for signals.
• Table names are now automatically set from the class name unless overriden.
• Model.query now always works for applications directly passed to the SQLAlchemy constructor. Furthermore the property now raises an RuntimeError instead of being None.
• added session options to constructor.
• fixed a broken __repr__
• db.Table is now a factor function that creates table objects. This makes it possible to omit the metadata.

Version 0.9

• applied changes to pass the Flask extension approval process.

Version 0.8

• added a few configuration keys for creating connections.
• automatically activate connection recycling for MySQL connections.
• added support for the Flask testing mode.

Version 0.7

• Initial public release