# Flask-SQLAlchemy
Flask-SQLAlchemy 是一个为您的 [Flask](http://flask.pocoo.org/) 应用增加 [SQLAlchemy](http://www.sqlalchemy.org/) 支持的扩展。它需要 SQLAlchemy 0.6 或者更高的版本。它致力于简化在 Flask 中 SQLAlchemy 的使用，提供了有用的默认值和额外的助手来更简单地完成常见任务。
# 快速入门
Flask-SQLAlchemy 使用起来非常有趣，对于基本应用十分容易使用，并且对于大型项目易于扩展。有关完整的指南，请参阅 [`SQLAlchemy`](api.html#flask.ext.sqlalchemy.SQLAlchemy "flask.ext.sqlalchemy.SQLAlchemy") 的 API 文档。
## 一个最小应用
常见情况下对于只有一个 Flask 应用，所有您需要做的事情就是创建 Flask 应用，选择加载配置接着创建 [`SQLAlchemy`](api.html#flask.ext.sqlalchemy.SQLAlchemy "flask.ext.sqlalchemy.SQLAlchemy") 对象时候把 Flask 应用传递给它作为参数。
一旦创建，这个对象就包含 `sqlalchemy` 和 [`sqlalchemy.orm`](http://www.sqlalchemy.org/docs/orm/scalar_mapping.html#module-sqlalchemy.orm "(in SQLAlchemy v1.0)") 中的所有函数和助手。此外它还提供一个名为 `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()`](api.html#flask.ext.sqlalchemy.SQLAlchemy.create_all "flask.ext.sqlalchemy.SQLAlchemy.create_all") 方法来创建表和数据库:
```
>>> from yourapplication import db
>>> db.create_all()
```
Boom, 您的数据库已经生成。现在来创建一些用户:
```
>>> from yourapplication import User
>>> admin = User('admin', 'admin@example.com')
>>> guest = User('guest', 'guest@example.com')
```
但是它们还没有真正地写入到数据库中，因此让我们来确保它们已经写入到数据库中:
```
>>> 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`](api.html#flask.ext.sqlalchemy.SQLAlchemy "flask.ext.sqlalchemy.SQLAlchemy") 允许您访问下面的东西:
* `sqlalchemy` 和 [`sqlalchemy.orm`](http://www.sqlalchemy.org/docs/orm/scalar_mapping.html#module-sqlalchemy.orm "(in SQLAlchemy v1.0)") 下所有的函数和类
* 一个叫做 `session` 的预配置范围的会话(session)
* [`metadata`](api.html#flask.ext.sqlalchemy.SQLAlchemy.metadata "flask.ext.sqlalchemy.SQLAlchemy.metadata") 属性
* [`engine`](api.html#flask.ext.sqlalchemy.SQLAlchemy.engine "flask.ext.sqlalchemy.SQLAlchemy.engine") 属性
* [`SQLAlchemy.create_all()`](api.html#flask.ext.sqlalchemy.SQLAlchemy.create_all "flask.ext.sqlalchemy.SQLAlchemy.create_all") 和 [`SQLAlchemy.drop_all()`](api.html#flask.ext.sqlalchemy.SQLAlchemy.drop_all "flask.ext.sqlalchemy.SQLAlchemy.drop_all")，根据模型用来创建以及删除表格的方法
* 一个 [`Model`](api.html#flask.ext.sqlalchemy.Model "flask.ext.sqlalchemy.Model") 基类，即是一个已配置的声明(declarative)的基础(base)
2. [`Model`](api.html#flask.ext.sqlalchemy.Model "flask.ext.sqlalchemy.Model") 声明基类行为类似一个常规的 Python 类，不过有个 `query` 属性，可以用来查询模型 ([`Model`](api.html#flask.ext.sqlalchemy.Model "flask.ext.sqlalchemy.Model") 和 [`BaseQuery`](api.html#flask.ext.sqlalchemy.BaseQuery "flask.ext.sqlalchemy.BaseQuery"))
3. 您必须提交会话，但是没有必要在每个请求后删除它(session)，Flask-SQLAlchemy 会帮您完成删除操作。
# 引入上下文
如果您计划只使用一个应用程序，您大可跳过这一章节。只需要把您的应用程序传给 [`SQLAlchemy`](api.html#flask.ext.sqlalchemy.SQLAlchemy "flask.ext.sqlalchemy.SQLAlchemy") 构造函数，一般情况下您就设置好了。然而您想要使用不止一个应用或者在一个函数中动态地创建应用的话，您需要仔细阅读。
如果您在一个函数中定义您的应用，但是 [`SQLAlchemy`](api.html#flask.ext.sqlalchemy.SQLAlchemy "flask.ext.sqlalchemy.SQLAlchemy") 对象是全局的，后者如何知道前者了？答案就是 [`init_app()`](api.html#flask.ext.sqlalchemy.SQLAlchemy.init_app "flask.ext.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`](api.html#flask.ext.sqlalchemy.SQLAlchemy "flask.ext.sqlalchemy.SQLAlchemy") 共同工作。然而现在不把 [`SQLAlchemy`](api.html#flask.ext.sqlalchemy.SQLAlchemy "flask.ext.sqlalchemy.SQLAlchemy") 对象绑定到您的应用。为什么不这么做？ 因为那里可能创建不止一个应用。
那么 [`SQLAlchemy`](api.html#flask.ext.sqlalchemy.SQLAlchemy "flask.ext.sqlalchemy.SQLAlchemy") 是如何知道您的应用的？您必须配置一个应用上下文。如果您在一个 Flask 视图函数中进行工作，这会自动实现。但如果您在交互式的 shell 中，您需要手动这么做。(参阅 [创建应用上下文](http://flask.pocoo.org/docs/appcontext/#creating-an-application-context) )。
简而言之，像这样做:
```
>>> 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 扩展能够识别的配置键的清单:
| | |
| --- | --- |
| `SQLALCHEMY_DATABASE_URI` | 用于连接数据的数据库。例如： `sqlite:////tmp/test.db` `mysql://username:password@server/db` |
| `SQLALCHEMY_BINDS` | 一个映射绑定 (bind) 键到 SQLAlchemy 连接 URIs 的字典。 更多的信息请参阅 [_绑定多个数据库_](binds.html#binds)。 |
| `SQLALCHEMY_ECHO` | 如果设置成 `True`，SQLAlchemy 将会记录所有 发到标准输出(stderr)的语句，这对调试很有帮助。 |
| `SQLALCHEMY_RECORD_QUERIES` | 可以用于显式地禁用或者启用查询记录。查询记录 在调试或者测试模式下自动启用。更多信息请参阅 `get_debug_queries()`。 |
| `SQLALCHEMY_NATIVE_UNICODE` | 可以用于显式地禁用支持原生的 unicode。这是 某些数据库适配器必须的（像在 Ubuntu 某些版本上的 PostgreSQL），当使用不合适的指定无编码的数据库 默认值时。 |
| `SQLALCHEMY_POOL_SIZE` | 数据库连接池的大小。默认是数据库引擎的默认值 （通常是 5）。 |
| `SQLALCHEMY_POOL_TIMEOUT` | 指定数据库连接池的超时时间。默认是 10。 |
| `SQLALCHEMY_POOL_RECYCLE` | 自动回收连接的秒数。这对 MySQL 是必须的，默认 情况下 MySQL 会自动移除闲置 8 小时或者以上的连接。 需要注意地是如果使用 MySQL 的话， Flask-SQLAlchemy 会自动地设置这个值为 2 小时。 |
| `SQLALCHEMY_MAX_OVERFLOW` | 控制在连接池达到最大值后可以创建的连接数。当这些额外的 连接回收到连接池后将会被断开和抛弃。 |
| `SQLALCHEMY_TRACK_MODIFICATIONS` | 如果设置成 `True` (默认情况)，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 下面的文档([支持的数据库](http://www.sqlalchemy.org/docs/core/engines.html))。这里展示了一些常见的连接字符串。
SQLAlchemy 把一个引擎的源表示为一个连同设定引擎选项的可选字符串参数的 URI。URI 的形式是:
```
dialect+driver://username:password@host:port/database
```
该字符串中的许多部分是可选的。如果没有指定驱动器，会选择默认的（确保在这种情况下 _不_ 包含 `+` ）。
Postgres:
```
postgresql://scott:tiger@localhost/mydatabase
```
MySQL:
```
mysql://scott:tiger@localhost/mydatabase
```
Oracle:
```
oracle://scott:tiger@127.0.0.1:1521/sidname
```
SQLite (注意开头的四个斜线):
```
sqlite:////absolute/path/to/foo.db
```
## 使用自定义的元数据和命名约定
你可以使用一个自定义的 [`MetaData`](http://www.sqlalchemy.org/docs/core/metadata.html#sqlalchemy.schema.MetaData "(in SQLAlchemy v1.0)") 对象来构造 `SQLAlchemy` 对象。这允许你指定一个 [自定义约束命名约定](http://docs.sqlalchemy.org/en/latest/core/constraints.html#constraint-naming-conventions)。这样做对数据库的迁移是很重要的。因为 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`](http://www.sqlalchemy.org/docs/core/metadata.html#sqlalchemy.schema.MetaData "(in SQLAlchemy v1.0)") 的信息，[请查看官方的文档](http://docs.sqlalchemy.org/en/latest/core/metadata.html)。
# 声明模型
通常下，Flask-SQLAlchemy 的行为就像一个来自 [`declarative`](http://www.sqlalchemy.org/docs/orm/extensions/declarative/api.html#module-sqlalchemy.ext.declarative "(in SQLAlchemy v1.0)") 扩展配置正确的 declarative 基类。因此，我们强烈建议您阅读 SQLAlchemy 文档以获取一个全面的参考。尽管如此，我们这里还是给出了最常用的示例。
需要牢记的事情:
* 您的所有模型的基类叫做 `db.Model`。它存储在您必须创建的 SQLAlchemy 实例上。 细节请参阅 [_快速入门_](quickstart.html#quickstart)。
* 有一些部分在 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` 的第一个参数。您可以直接提供它们或进一步规定（比如提供一个长度）。下面的类型是最常用的:
| | |
| --- | --- |
| `Integer` | 一个整数 |
| `String` (size) | 有长度限制的字符串 |
| `Text` | 一些较长的 unicode 文本 |
| `DateTime` | 表示为 Python `datetime` 对象的 时间和日期 |
| `Float` | 存储浮点值 |
| `Boolean` | 存储布尔值 |
| `PickleType` | 存储为一个持久化的 Python 对象 |
| `LargeBinary` | 存储一个任意大的二进制数据 |
## 一对多(one-to-many)关系
最为常见的关系就是一对多的关系。因为关系在它们建立之前就已经声明，您可以使用 字符串来指代还没有创建的类(例如如果 `Person` 定义了一个到 `Article` 的关系，而 `Article` 在文件的后面才会声明)。
关系使用 [`relationship()`](http://www.sqlalchemy.org/docs/orm/relationship_api.html#sqlalchemy.orm.relationship "(in SQLAlchemy v1.0)") 函数表示。然而外键必须用类 [`sqlalchemy.schema.ForeignKey`](http://www.sqlalchemy.org/docs/core/constraints.html#sqlalchemy.schema.ForeignKey "(in SQLAlchemy v1.0)") 来单独声明:
```
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()`](http://www.sqlalchemy.org/docs/orm/relationship_api.html#sqlalchemy.orm.relationship "(in SQLAlchemy v1.0)") 。
那么 `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()`](http://www.sqlalchemy.org/docs/orm/relationship_api.html#sqlalchemy.orm.backref "(in SQLAlchemy v1.0)") 函数:
```
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 的查询对象。
# 选择(Select),插入(Insert), 删除(Delete)
现在您已经有了 [_declared models_](models.html#models)，是时候从数据库中查询数据。我们将会使用 [_快速入门_](quickstart.html#quickstart) 章节中定义的数据模型。
## 插入记录
在查询数据之前我们必须先插入数据。您的所有模型都应该有一个构造函数，如果您 忘记了，请确保加上一个。只有您自己使用这些构造函数而 SQLAlchemy 在内部不会使用它， 所以如何定义这些构造函数完全取决与您。
向数据库插入数据分为三个步骤:
1. 创建 Python 对象
2. 把它添加到会话
3. 提交会话
这里的会话不是 Flask 的会话，而是 Flask-SQLAlchemy 的会话。它本质上是一个 数据库事务的加强版本。它是这样工作的:
```
>>> from yourapp import User
>>> me = User('admin', 'admin@example.com')
>>> 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`](api.html#flask.ext.sqlalchemy.Model "flask.ext.sqlalchemy.Model") 类上提供了 [`query`](api.html#flask.ext.sqlalchemy.Model.query "flask.ext.sqlalchemy.Model.query") 属性。当您访问它时，您会得到一个新的所有记录的查询对象。在使用 `all()` 或者 `first()` 发起查询之前可以使用方法 `filter()` 来过滤记录。如果您想要用主键查询的话，也可以使用 `get()`。
下面的查询假设数据库中有如下条目:
| `id` | `username` | `email` |
| --- | --- | --- |
| 1 | admin | [admin@example.com](mailto:admin%40example.com) |
| 2 | peter | [peter@example.org](mailto:peter%40example.org) |
| 3 | guest | [guest@example.com](mailto:guest%40example.com) |
通过用户名查询用户:
```
>>> peter = User.query.filter_by(username='peter').first()
>>> peter.id
1
>>> peter.email
u'peter@example.org'
```
同上但是查询一个不存在的用户名返回 `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()`](http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.get "(in SQLAlchemy v1.0)")，使用 `first_or_404()` 来代替 [`first()`](http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.first "(in SQLAlchemy v1.0)")。这样会抛出一个 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()`](api.html#flask.ext.sqlalchemy.SQLAlchemy.create_all "flask.ext.sqlalchemy.SQLAlchemy.create_all") 和 [`drop_all()`](api.html#flask.ext.sqlalchemy.SQLAlchemy.drop_all "flask.ext.sqlalchemy.SQLAlchemy.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__`](api.html#flask.ext.sqlalchemy.Model.__bind_key__ "flask.ext.sqlalchemy.Model.__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`](#models_committed "models_committed") 完全一样，但是在提交之前发送。
# API
这部分文档记录了 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()`](#flask.ext.sqlalchemy.SQLAlchemy.create_all "flask.ext.sqlalchemy.SQLAlchemy.create_all") and [`drop_all()`](#flask.ext.sqlalchemy.SQLAlchemy.drop_all "flask.ext.sqlalchemy.SQLAlchemy.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`](http://www.sqlalchemy.org/docs/orm/scalar_mapping.html#module-sqlalchemy.orm "(in SQLAlchemy v1.0)") 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`](http://www.sqlalchemy.org/docs/orm/scalar_mapping.html#module-sqlalchemy.orm "(in SQLAlchemy v1.0)") directly, but note that Flask-SQLAlchemy customizations are available only through an instance of this [`SQLAlchemy`](#flask.ext.sqlalchemy.SQLAlchemy "flask.ext.sqlalchemy.SQLAlchemy") class. Query classes default to [`BaseQuery`](#flask.ext.sqlalchemy.BaseQuery "flask.ext.sqlalchemy.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`](http://www.sqlalchemy.org/docs/orm/scalar_mapping.html#module-sqlalchemy.orm "(in SQLAlchemy v1.0)") 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`](#flask.ext.sqlalchemy.BaseQuery "flask.ext.sqlalchemy.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()`](http://www.sqlalchemy.org/docs/core/engines.html#sqlalchemy.create_engine "(in SQLAlchemy v1.0)") 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()`](#flask.ext.sqlalchemy.SQLAlchemy.create_session "flask.ext.sqlalchemy.SQLAlchemy.create_session").
`create_session(options)`
Creates the session. The default implementation returns a [`SignallingSession`](#flask.ext.sqlalchemy.SignallingSession "flask.ext.sqlalchemy.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 [_绑定多个数据库_](binds.html#binds).
`__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`](#flask.ext.sqlalchemy.Model.query_class "flask.ext.sqlalchemy.Model.query_class"). Can be used to query the database for instances of this model.
`query_class`
the query class used. The [`query`](#flask.ext.sqlalchemy.Model.query "flask.ext.sqlalchemy.Model.query") attribute is an instance of this class. By default a [`BaseQuery`](#flask.ext.sqlalchemy.BaseQuery "flask.ext.sqlalchemy.BaseQuery") is used.
alias of [`BaseQuery`](#flask.ext.sqlalchemy.BaseQuery "flask.ext.sqlalchemy.BaseQuery")
`class flask.ext.sqlalchemy.BaseQuery(entities, session=None)`
The default query object used for models, and exposed as [`Query`](#flask.ext.sqlalchemy.SQLAlchemy.Query "flask.ext.sqlalchemy.SQLAlchemy.Query"). This can be subclassed and replaced for individual models by setting the [`query_class`](#flask.ext.sqlalchemy.Model.query_class "flask.ext.sqlalchemy.Model.query_class") attribute. This is a subclass of a standard SQLAlchemy [`Query`](http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query "(in SQLAlchemy v1.0)") 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()`](#flask.ext.sqlalchemy.BaseQuery.first "flask.ext.sqlalchemy.BaseQuery.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`](http://www.sqlalchemy.org/docs/orm/exceptions.html#sqlalchemy.orm.exc.ObjectDeletedError "(in SQLAlchemy v1.0)") 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()`](#flask.ext.sqlalchemy.BaseQuery.get "flask.ext.sqlalchemy.BaseQuery.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`](#flask.ext.sqlalchemy.Pagination "flask.ext.sqlalchemy.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()`](#flask.ext.sqlalchemy.SQLAlchemy.create_session "flask.ext.sqlalchemy.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()`](#flask.ext.sqlalchemy.BaseQuery.paginate "flask.ext.sqlalchemy.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()`](#flask.ext.sqlalchemy.Pagination.prev "flask.ext.sqlalchemy.Pagination.prev") and [`next()`](#flask.ext.sqlalchemy.Pagination.next "flask.ext.sqlalchemy.Pagination.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`](#flask.ext.sqlalchemy.Pagination "flask.ext.sqlalchemy.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`](#flask.ext.sqlalchemy.Pagination "flask.ext.sqlalchemy.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
