Flask SQLAlchemy 中文文檔 · Flask 擴展文檔匯總

# 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