轉(zhuǎn)載握玛，覺得這篇寫 SQLAlchemy Core寺惫，寫得非常不錯菇晃。不過后續(xù)他沒寫SQLAlchemy ORM...

SQLAlchemy Introduce

Tao Junjie

2015-12-13 19:17

Source

perface

我們每天都要面對數(shù)據(jù)祈远，數(shù)據(jù)庫CRUD操作的能力對每個任務(wù)都至關(guān)重要呆万。無論你是開發(fā)網(wǎng)頁，桌面或其他應(yīng)用车份，他們都需要快速且安全的接入數(shù)據(jù)谋减。關(guān)系型數(shù)據(jù)庫仍然是儲存數(shù)據(jù)最主流的手段之一。

SQL是操作數(shù)據(jù)的利器扫沼，不過有時(shí)候要整合到應(yīng)用里非常麻煩出爹。作為一個Pythoner，你可能需要通過ODBC接口或者數(shù)據(jù)庫API充甚，用一些字符串生成查詢以政。雖然這些方法可以有效的處理數(shù)據(jù)霸褒，但是數(shù)據(jù)安全和修改變得很困難伴找。

這本書將介紹一個非常強(qiáng)大且具有靈活性的Python庫SQLAlchemy，可以跨越關(guān)系型數(shù)據(jù)庫與傳統(tǒng)編程直接的鴻溝废菱。SQLAlchemy允
許你使用原始的SQL語言查詢技矮，更鼓勵使用更高級的Pythonic展示方式和更友好的方法實(shí)現(xiàn)數(shù)據(jù)的查詢和更新抖誉。它提供工具讓你一次性把應(yīng)用的類和對象
映射成數(shù)據(jù)庫的表，然后就可以不再理會表結(jié)構(gòu)了衰倦，還可以讓你不斷優(yōu)化模型的性能袒炉。

SQLAlchemy功能強(qiáng)大且充滿柔性，但它還是有點(diǎn)復(fù)雜樊零。SQLAlchemy教程只介紹了它的冰山一角我磁，盡管在線文檔也很全，但是更適合做參考書驻襟，不適合做教程夺艰。這本書的目的是要成為一個學(xué)習(xí)工具和一個方便的參考，幫你快速完成任務(wù)沉衣。

本書內(nèi)容用的SQLAlchemy是1.0版本郁副；但是之前的版本都可以用。0.8版可能需要一點(diǎn)小改動豌习，大部分都是源自0.5版存谎。

這本書分成三部分：SQLAlchemy Core，SQLAlchemy
ORM和一個Cookbook部分肥隆。前兩部分盡可能的做到一致性既荚。我們有意在兩個部分用同意的例子，目的是讓你更方便的比較兩種方式的用法栋艳。因此固以，你也可
以根據(jù)自己的需要閱讀本書SQLAlchemy Core和SQLAlchemy ORM的章節(jié)。

1.SQLAlchemy簡介

SQLAlchemy是一個與數(shù)據(jù)庫進(jìn)行交互的庫嘱巾。它可以讓你用Python的類和語句創(chuàng)建數(shù)據(jù)模型來查詢數(shù)據(jù)憨琳。在2005年由Mike Bayer創(chuàng)建，SQLAlchemy被許多公司使用旬昭，已經(jīng)漸漸成為Python處理關(guān)系型數(shù)據(jù)庫的一種主流手段篙螟。

它可以連接許多數(shù)據(jù)庫，包括 Postgres问拘，MySQL遍略，SQLite和Oracle等等。也提供了一種通用的接口支持其他關(guān)系型數(shù)據(jù)庫骤坐。Amazon的Redshift數(shù)據(jù)庫绪杏，作為Postgres的一種變體，就是SQLAlchemy社區(qū)對數(shù)據(jù)庫支持的好榜樣纽绍。

本章蕾久，我們將首先解釋為什么要用SQLAlchemy，然后介紹兩個主要模型并連接一個數(shù)據(jù)庫拌夏。

為什么用SQLAlchemy

用SQLAlchemy的主要原因是僧著，把你從底層的數(shù)據(jù)庫和SQL奇葩語法中解放出來履因。SQLAlchemy將常用語句和類型和SQL語句對應(yīng)起
來，讓你可以更容易地理解數(shù)據(jù)庫類型盹愚，而不需要擔(dān)心太多細(xì)節(jié)栅迄。這樣在處理像Oracle到PostgreSQL數(shù)據(jù)庫這類的遷移工作，或從一個應(yīng)用數(shù)據(jù)庫
到數(shù)據(jù)倉庫時(shí)皆怕，事情就簡單了毅舆。它還能確保數(shù)據(jù)在增加到數(shù)據(jù)庫之前是經(jīng)過安全的，適當(dāng)轉(zhuǎn)義處理的愈腾。這樣可以避免SQL注入之類的事情發(fā)生朗兵。

SQLAlchemy通過兩個主要的模型來實(shí)現(xiàn)靈活的操作：SQL表達(dá)式語言（通常也叫Core）和ORM（Object-relational mapping，對象關(guān)系映射）顶滩。這兩個模型可以根據(jù)你的需要獨(dú)立使用余掖，也可以合在一起使用。

SQLAlchemy Core和SQL表達(dá)式語言

SQL表達(dá)式語言是用Pythonic方式的來表達(dá)SQL語句和表達(dá)式礁鲁，只是對傳統(tǒng)的SQL語言的輕微抽象盐欺。它側(cè)重于實(shí)用數(shù)據(jù)庫的模式
（schema，其實(shí)是具體到一個Tabel和View等）仅醇，但是它實(shí)現(xiàn)了不同數(shù)據(jù)庫之間標(biāo)準(zhǔn)化的接口冗美。SQL表達(dá)式語言也是SQLAlchemy
ORM的基礎(chǔ)。

ORM

SQLAlchemy ORM與你在其他語言里遇到的ORM類似析二。它側(cè)重于應(yīng)用的Domain
Model（一種將數(shù)據(jù)與其行為集成在一起的模式）粉洼，借助工作單元的模式來維護(hù)對象狀態(tài)。它還在SQL表達(dá)式語言之上增加了一層抽象叶摄，讓用戶可以更容易的
操作數(shù)據(jù)庫属韧。你可以把ORM和SQL表達(dá)式語言結(jié)合起來構(gòu)建強(qiáng)大的應(yīng)用。ORM構(gòu)建了一個聲明式的系統(tǒng)蛤吓，與許多其他ORM模型（如Ruby on
Rails）使用的 active-record systems類似宵喂。

雖然ORM非常有用，但是你要注意会傲，類的很多用法與數(shù)據(jù)庫的工作方式是不一樣的锅棕。我們將在后面的章節(jié)介紹這些差異。

Core和ORM的選擇

究竟是選擇Core還是ORM作為應(yīng)用的數(shù)據(jù)鏈接層呢淌山？除了個人喜好裸燎，理由可以歸結(jié)為一些影響因素。這兩種模式的語法不太一樣泼疑，但Core和ORM最大的差異是Core對數(shù)據(jù)模式和業(yè)務(wù)對象（business objects）的不同處理方式德绿。

SQLAlchemy Core是以模式為中心，和普通SQL一樣有表，鍵和索引等脆炎。SQLAlchemy
Core最擅長的時(shí)數(shù)據(jù)倉庫梅猿，報(bào)表分析氓辣，以及其他使用數(shù)據(jù)查詢和其他操作可以牢牢掌控的地方秒裕。它擁有強(qiáng)大的數(shù)據(jù)庫連接池（ connection
pool）和數(shù)據(jù)結(jié)果集（ResultSet）優(yōu)化，非常適合處理大量數(shù)據(jù)钞啸，甚至多數(shù)據(jù)庫也適用几蜻。

但是，如果你更側(cè)重于領(lǐng)域驅(qū)動設(shè)計(jì)(domain driven design)体斩，
那么ORM就可以將原數(shù)據(jù)和業(yè)務(wù)對象的底層的模式和結(jié)構(gòu)大部分細(xì)節(jié)都封裝起來梭稚。這樣封裝讓數(shù)據(jù)庫連接更簡單，更像Python代碼絮吵。大多數(shù)應(yīng)用都更適合按
照這種方法建模弧烤。ORM可以用一種非常高效的方法把領(lǐng)域驅(qū)動設(shè)計(jì)方法導(dǎo)入傳統(tǒng)應(yīng)用，或者改造原來帶有原始SQL語句的應(yīng)用蹬敲。還有一個好處就是暇昂，通過對底層
數(shù)據(jù)庫的合理抽象，ORM讓開發(fā)者把精力更多地集中在業(yè)務(wù)流程的實(shí)現(xiàn)上伴嗡。

不過急波，ORM是建立在SQLAlchemy Core基礎(chǔ)之上的，你可以把處理MySQL的同樣方式用于Oracle的數(shù)據(jù)倉庫和Amazon Redshift數(shù)據(jù)庫瘪校。當(dāng)你需要業(yè)務(wù)對象和倉庫數(shù)據(jù)時(shí)澄暮，ORM可以無縫的銜接每個環(huán)節(jié)。

如果你的應(yīng)用框架已經(jīng)使用了ORM阱扬，但是想要更強(qiáng)大的報(bào)表功能泣懊，使用Core

如果你不想像普通SQL一樣以模式為中心，用ORM

如果你的數(shù)據(jù)不需要業(yè)務(wù)對象麻惶，用Core

如果你把數(shù)據(jù)看成業(yè)務(wù)對象嗅定，用ORM

如果要建立快速原型，用ORM

如果你既要業(yè)務(wù)對象用踩，又要其他數(shù)據(jù)無關(guān)的功能（報(bào)表渠退，數(shù)據(jù)分析等等），兩個都用脐彩。

知道了如何選擇Core和ORM碎乃，我們介紹SQLAlchemy的安裝與數(shù)據(jù)庫連接方法。

SQLAlchemy安裝與數(shù)據(jù)庫連接

SQLAlchemy支持Python 2.6+惠奸，Python 3.3+和Pypy 2.1+梅誓，強(qiáng)烈推薦conda安裝，pip也可以（Python 2.7.5+和Python 3.4+自帶pip）。

conda install sqlalchemy

or

pip install sqlalchemy

安裝數(shù)據(jù)庫驅(qū)動

SQLAlchemy默認(rèn)支持SQLite3梗掰，不需要安裝驅(qū)動嵌言；但是，其他數(shù)據(jù)庫都需要用符合Python的DBAPI（PEP-249）標(biāo)準(zhǔn)的驅(qū)
動及穗。這些DBAPI提供了與各種數(shù)據(jù)庫進(jìn)行交互的方言摧茴，可以體現(xiàn)不同數(shù)據(jù)庫獨(dú)有的特性。不同的數(shù)據(jù)庫也可能有多個不同的DBAPI埂陆，下面主要介紹
PostgreSQL和MySQL數(shù)據(jù)庫的驅(qū)動苛白。

PostgreSQL

Psycopg2提供了PostgreSQL的驅(qū)動，可以用pip install psycopg2
安裝焚虱。

MySQL

PyMySQL是MySQL的驅(qū)動购裙，可以用pip install pymysql
安
裝。SQLAlchemy支持MySQL
4.1+版本鹃栽，由于之前的版本密碼工作方式不同躏率。如果某個類型只存在于MySQL的特定版本中，那么SQLAlchemy不會支持那些沒有該類型的
MySQL版本使用該類型民鼓。用MySQL時(shí)薇芝，如果SQLAlchemy的某個函數(shù)不能正常運(yùn)行，建議看看MySQL的文檔摹察。

Others

關(guān)于其他數(shù)據(jù)庫的SQLAlchemy支持恩掷，請參考SQLAlchemy文檔。Drizzle供嚎，F(xiàn)irebird黄娘，Oracle，
Sybase克滴，Microsoft SQL Server都支持逼争。IBM DB2，Informix劝赔，Amazon
Redshift誓焦，EXASolution，SAP SQL
Anywhere着帽，Monet等數(shù)據(jù)庫也有SQLAlchemy社區(qū)支持杂伟。額外的方言也可以由SQLAlchemy支持，在第八章：自定義方言里面有介
紹仍翰。

有了DBAPI連接件赫粥，我們再看看如何連接數(shù)據(jù)庫。

連接數(shù)據(jù)庫

連接數(shù)據(jù)庫需要SQLAlchemy引擎予借，SQLAlchemy引擎為數(shù)據(jù)庫執(zhí)行SQL語句創(chuàng)建了一個常用的接口越平。引擎通過封裝一個數(shù)據(jù)庫連接池和
方言來實(shí)現(xiàn)不同數(shù)據(jù)庫類型統(tǒng)一的接口频蛔。這樣做使得Python代碼不需要關(guān)心不同數(shù)據(jù)庫DBAPI之間的差異。SQLAlchemy提供了一個帶連接字符
串（connection string）和一些參數(shù)的函數(shù)來創(chuàng)建引擎秦叛。連接字符串形式如下：

數(shù)據(jù)庫類型（SQLite晦溪，Postgres，MySQL等）

默認(rèn)數(shù)據(jù)庫類型的方言（Psycopg2挣跋，PyMySQL等）

驗(yàn)證信息（用戶名和密碼）

數(shù)據(jù)庫的位置（文件名或數(shù)據(jù)庫服務(wù)器地址）

數(shù)據(jù)庫服務(wù)器端口（可選）

數(shù)據(jù)庫名稱（可選）

SQLite數(shù)據(jù)庫連接字符串就是一個文件或儲存位置三圆。例1-1中，第二行表示SQLAlchemy連接了當(dāng)前文件夾中的一個SQLite數(shù)據(jù)庫文件cookies.db
浆劲，第三行是連接內(nèi)存數(shù)據(jù)庫嫌术，第四哀澈、五行分別是Unix和Windows系統(tǒng)中的全路徑文件牌借。Windows系統(tǒng)路徑名稱分隔符（
）在Python中是'\'
或r''
。

from sqlalchemy import create_engine
engine = create_engine('sqlite:///cookies.db')
engine2 = create_engine('sqlite:///:memory:')
engine3 = create_engine('sqlite:////home/cookiemonster/cookies.db')
engine3 = create_engine('sqlite:///c:\Users\cookiemonster\cookies.db')

create_engine
函數(shù)創(chuàng)建了一個引擎實(shí)例割按，但是膨报，它并沒有真正打開鏈接律歼，直到一個動作要求引擎執(zhí)行時(shí)才會執(zhí)行术奖，比如查詢或新建數(shù)據(jù)。

下面再讓我們創(chuàng)建一個PostgreSQL數(shù)據(jù)庫mydb
双絮。然后我們用函數(shù)構(gòu)建一個引擎實(shí)例弛矛，如例1-2所示够吩，你會發(fā)現(xiàn)我用了postgresql+psycopg2
作為連接字符串的引擎和方言部分。

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://username:password@localhost:5432/mydb')

同理丈氓，我們再看看MySQL引擎的創(chuàng)建周循，如例1-3所示，我們把參數(shù)pool_recycle
設(shè)置成3600
万俗，表示每一小時(shí)自動連接一次湾笛。

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://cookiemonster:chocolatechip'
'@mysql01.monster.internal/cookies', pool_recycle=3600)

MySQL默認(rèn)超過8小時(shí)空閑則斷開連接。為了繞開這個問題闰歪，引擎設(shè)置pool_recycle=3600
嚎研。

create_engine
函數(shù)的可選參數(shù)是：

echo
：表示引擎行為的日志是否顯示，像執(zhí)行的SQL語句和其他參數(shù)等等库倘。默認(rèn)是False
临扮。

ecoding
：默認(rèn)使用SQLAlchemy的字符串編碼utf-8
，大多數(shù)DBAPI都用此編碼教翩。

isolation_level
：SQLAlchemy分離等級杆勇。PostgreSQL+psycopg2的分類等級有READ COMMITTED
，READ UNCOMMITTED
迂曲，REPEATABLE READ
靶橱，SERIALIZABLE
，AUTOCOMMIT
五種，默認(rèn)是READ COMMITTED
关霸。PyMySQL也是這五種传黄，InnoDB存儲引擎數(shù)據(jù)庫默認(rèn)是REPEATABLE READ
。
用isolation_level
關(guān)鍵詞會為具體的DBAPI設(shè)置隔離等級队寇，可就像數(shù)據(jù)庫對應(yīng)連接字符串的鍵值對一樣膘掰，比如PostgreSQL是用psycopg2。

pool_recycle
：這個參數(shù)是指數(shù)據(jù)庫連接多少秒循環(huán)檢查一次佳遣，對MySQL非常重要识埋。默認(rèn)值為-1
，表示沒有時(shí)間限制零渐，一直連接窒舟。

一旦引擎建立，我們就可以連接數(shù)據(jù)庫了诵盼。通過connect()
函數(shù)就可以惠豺。

connection = engine.connect()

現(xiàn)在我們已經(jīng)連接了數(shù)據(jù)庫，可以用SQLAlchemy Core和ORM了风宁。下面這部分洁墙，我們將探索SQLAlchemy Core的內(nèi)容，學(xué)習(xí)如何定義和查詢數(shù)據(jù)庫戒财。

SQLAlchemy Core

連接數(shù)據(jù)庫之后热监，我們就可以使用SQLAlchemy Core來進(jìn)行數(shù)據(jù)庫操作了。SQLAlchemy
Core是用Pythonic方式的SQL表達(dá)式語言來表示SQL命令和數(shù)據(jù)結(jié)構(gòu)的饮寞。SQLAlchemy
Core具有的這種特性讓它不僅可以用于Django或SQLAlchemy ORM孝扛，也可以單獨(dú)使用。

首先我們需要定義數(shù)據(jù)表的數(shù)據(jù)類型骂际，數(shù)據(jù)的關(guān)聯(lián)性以及其他約束條件疗琉。

2.Schema and Types

為了搞定數(shù)據(jù)庫，SQLAlchemy提供了以下三種方式來表示數(shù)據(jù)庫中的表結(jié)構(gòu)：

Core：自定義表Table
對象

ORM：用類class
表示數(shù)據(jù)表

SQLsoup和Automap：直接從數(shù)據(jù)庫映射

本章重點(diǎn)介紹第一種方式歉铝，即通過SQLAlchemy Core來實(shí)現(xiàn)盈简，后面的章節(jié)會介紹其他兩種方法。Table
對象包括了一系列具有特定類型的列和屬性太示，這些通過一個常用的元數(shù)據(jù)容器來控制柠贤。首先我們介紹SQLAlchemy建數(shù)據(jù)表的數(shù)據(jù)類型。

Types

SQLAlchemy有四種數(shù)據(jù)類型：

通用類型（Generic）

SQL標(biāo)準(zhǔn)類型（SQL standard）

數(shù)據(jù)庫特有類型（Vendor Specific）

用戶自定義類型（User Defined）

SQLAlchemy為不同的數(shù)據(jù)庫定義了一些通用的數(shù)據(jù)類型类缤。這些類型都在sqlalchemy.types
模塊中臼勉，為了引用方便也支持放在sqlalchemy
里面。

布爾型通用類型使用BOOLEAN
SQL類型餐弱，對應(yīng)的Python類型就是True
和False
宴霸；但是囱晴，對那些不支持BOOLEAN
SQL類型的數(shù)據(jù)庫通常要使用SMALLINT
來代替。由于SQLAlchemy把這些細(xì)節(jié)都隱藏了瓢谢，因此你可以放心大膽的操作數(shù)據(jù)畸写，不用擔(dān)心后面數(shù)據(jù)庫用的是什么細(xì)節(jié)，只要在Python代碼里處理True
和False
就行氓扛。即使數(shù)據(jù)倉庫和交換數(shù)據(jù)庫不一樣枯芬，通用類型也可以完成數(shù)據(jù)處理。通用類型對應(yīng)Python和SQL的含義如下表所示：

SQLAlchemy

Python

SQL

BigInteger

int

BIGINT

Boolean

bool

BOOLEAN or SMALLINT

Date

datetime.date

Date (SQLite: String)

DateTime

datetime.datetime

DATETIME (SQLite: String)

Enum

str

ENUM or VARCHAR

Float

float or Decimal

FLOAT or REAL

Integer

int

Integer

Interval

datetime.timedelta

INTERVAL or DATE from epoch

LargeBinary

byte

BLOB or BYTEA

Numeric

decimal.Decimal

NUMERIC or DECIMAL

Unicode

unicode

UNICODE or VARCHAR

Text

str

CLOB or TEXT

Time

datetime.time

DATETIME

掌握通用類型非常重要采郎，會經(jīng)常使用

如果通用類型不能滿足需求千所，也會用到SQL標(biāo)準(zhǔn)類型和數(shù)據(jù)庫專有類型。CHAR
和NVARCHAR
類型就是最好的例證蒜埋，源自SQL類型淫痰。如果數(shù)據(jù)庫的模式是在使用SQLAlchemy之前建立的，我們就要注意原模式與SQLAlchemy的差異理茎。SQL標(biāo)準(zhǔn)類型的特性在不同的數(shù)據(jù)庫里面可能有很大變化黑界，也是在sqlalchemy.types
模塊中管嬉，為了和通用類型分開皂林，都用大寫字母表示。

數(shù)據(jù)庫專有類型只存在于特定的數(shù)據(jù)庫中蚯撩。你可以通過SQLALchemy網(wǎng)站或某種數(shù)據(jù)庫的方言（dialect）文檔查詢此類型础倍，放在sqlalchemy.dialects
模塊和數(shù)據(jù)庫方言的子模塊中，這些類型同樣是大寫字母胎挎。例如沟启，我們想使用PostgreSQL強(qiáng)大的JSON類型，我們可以這樣：

from sqlalchemy.dialects.postgresql import JSON

這樣我們就可以用PostgreSQL特有的JSON函數(shù)為我們的應(yīng)用定義JSON類型犹菇，比如array_to_json
德迹。

你也可以根據(jù)自己的需要自定義類型。例如揭芍，當(dāng)增加數(shù)據(jù)記錄時(shí)胳搞，需要對即將存儲到VARCHAR
列的文本增加前綴，下一次從數(shù)據(jù)記錄中獲取這些記錄的時(shí)候要重新去掉前綴称杨。這樣定義類型就可以給那些系統(tǒng)中已經(jīng)存在的舊數(shù)據(jù)進(jìn)行標(biāo)識肌毅，表示這類數(shù)據(jù)在新應(yīng)用中并沒什么用或者不太重要。

了解了四類數(shù)據(jù)類型之后姑原，我們來看看如果用元數(shù)據(jù)組織數(shù)據(jù)庫的結(jié)構(gòu)悬而。

Metadata

元數(shù)據(jù)用于把數(shù)據(jù)庫結(jié)構(gòu)集成在一起方便SQLAlchemy快速對接《а矗可以把元數(shù)據(jù)看成是一種表目錄笨奠，再加一些引擎和鏈接的信息袭蝗。這些信息可以通過MetaData.tables
查看。讀操作是線程安全的般婆，但是呻袭，表的建立不完全是線程安全的。元數(shù)據(jù)使用之前需要導(dǎo)入并初始化腺兴。下面讓我們建立一個Metadata
對象左电，為本章后面的例子建立容器來盛放數(shù)據(jù)庫的信息目錄。

from sqlalchemy import MetaData
metadata = MetaData()

有了盛放數(shù)據(jù)庫結(jié)構(gòu)的地方页响，我們就可以建立數(shù)據(jù)表了篓足。

Tables

在SQLAlchemy Core里數(shù)據(jù)表對象初始化過程是通過Table
通過表名稱，元數(shù)據(jù)和數(shù)據(jù)列的名稱闰蚕，類型和屬性共同構(gòu)建栈拖，最終放入MetaData
對象的。列對象表示數(shù)據(jù)表的每個字段没陡，都是用包含名稱涩哟、數(shù)據(jù)類型和一些SQL結(jié)構(gòu)與約束特征的Column
對象表示。我們將從這里開始建立一些數(shù)據(jù)表在SQLAlchemy Core部分使用盼玄。例2-1如下所示贴彼，建立一個網(wǎng)店的餅干庫存表。

from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey

cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50), index=True),
Column('cookie_recipe_url', String(255)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2))

)

list(cookies.columns)

[Column('cookie_id', Integer(), table=<cookies>, primary_key=True, nullable=False),
Column('cookie_name', String(length=50), table=<cookies>),
Column('cookie_recipe_url', String(length=255), table=<cookies>),
Column('quantity', Integer(), table=<cookies>),
Column('unit_cost', Numeric(precision=12, scale=2), table=<cookies>)]

建立新表之前埃儿，我們需要理解表的基礎(chǔ)單元——列器仗。

Columns

列定義了數(shù)據(jù)表中的字段，它們通過我們對其關(guān)鍵詞參數(shù)的設(shè)置來表達(dá)具體的含義童番。不同類型的主要參數(shù)不同精钮。例如，字符串類型的基本參數(shù)是長度剃斧，而帶小數(shù)的數(shù)值類型基本參數(shù)是精度和長度轨香。其他類型大都沒有基本參數(shù)。

有時(shí)你也會看到字符串類型沒有設(shè)置長度幼东。并非所有的數(shù)據(jù)庫都支持這種特性臂容，包括MySQL也不支持。

列也有一些別的參數(shù)來幫助它們建立更豐富的特性筋粗。我們可以為列設(shè)置是否允許空值或必須具有唯一性（unique
）策橘。還可以定義初始值（default
），通常這么做是為了日志記錄或財(cái)務(wù)審計(jì)的需要娜亿，如下所示丽已。

from datetime import datetime
from sqlalchemy import DateTime
users = Table('users', metadata,
Column('user_id', Integer(), primary_key=True),
Column('username', String(15), nullable=False, unique=True),
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)

list(users.columns)

[Column('user_id', Integer(), table=<users>, primary_key=True, nullable=False),
Column('username', String(length=15), table=<users>, nullable=False),
Column('email_address', String(length=255), table=<users>, nullable=False),
Column('phone', String(length=20), table=<users>, nullable=False),
Column('password', String(length=25), table=<users>, nullable=False),
Column('created_on', DateTime(), table=<users>, default=ColumnDefault(<function ColumnDefault._maybe_wrap_callable.<locals>.<lambda> at 0x0000000005AD3730>)),
Column('updated_on', DateTime(), table=<users>, onupdate=ColumnDefault(<function ColumnDefault._maybe_wrap_callable.<locals>.<lambda> at 0x0000000005AD3950>), default=ColumnDefault(<function ColumnDefault._maybe_wrap_callable.<locals>.<lambda> at 0x0000000005AD38C8>))]

你會發(fā)現(xiàn)這里使用datatime.now
，而不是datatime.now()
买决。如果我們直接用函數(shù)沛婴，那么時(shí)間可能就是數(shù)據(jù)表第一次建立的時(shí)間吼畏。不帶()
我們就可以在每條記錄生成時(shí)產(chǎn)生一個新的時(shí)間。

我們通過列關(guān)鍵詞參數(shù)定義了數(shù)據(jù)表的結(jié)構(gòu)和約束嘁灯；但是泻蚊，列對象有時(shí)可能需要和其他表進(jìn)行關(guān)聯(lián)。當(dāng)你處理數(shù)據(jù)庫時(shí)丑婿，你必須告訴SQLAlchemy關(guān)
于這個數(shù)據(jù)庫內(nèi)部的模式性雄，結(jié)構(gòu)和約束。假如有一個數(shù)據(jù)庫和SQLAlchemy使用的索引名稱模式不同羹奉，那么你必須定義索引才能正常使用秒旋。下面兩個小節(jié)將
告訴你怎么做。

Keys and Constraints和Index兩節(jié)的程序其實(shí)都可以在Table
構(gòu)造函數(shù)里直接實(shí)現(xiàn)诀拭，也可以通過特定方法在表建立之后實(shí)現(xiàn)迁筛，再增加到表中。它們都會作為單獨(dú)一行語句保存到metadata
里面耕挨。

Keys and Constraints

鍵和約束是用來保證數(shù)據(jù)在存到數(shù)據(jù)庫之前能夠滿足一些約束條件的方法细卧。表示鍵和約束的對象在SQLAlchemy模塊里面都有，常用的三個如下所示：

from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint

主鍵是最常用的筒占，表示數(shù)據(jù)表中一條記錄的唯一標(biāo)識贪庙，可以用來保證不同表里相關(guān)數(shù)據(jù)之間正確的關(guān)系。前面的例1和例2中赋铝，我們用primary_key
參
數(shù)將一列設(shè)置為主鍵插勤。你還可以用若干列構(gòu)成的元組來定義一個復(fù)合主鍵。這個鍵在表中被看成是一個內(nèi)容為元組的列革骨，會按照它們原始順序進(jìn)行排列。主鍵還可以
在表構(gòu)建之后再定義析恋，如下所示良哲。你可以用逗號分隔，增加多列形成一個復(fù)合主鍵助隧。如果我們想在上面例2中顯示定義主鍵筑凫，可以這樣做：

PrimaryKeyConstraint('user_id', name='user_pk')

另一個常用的約束就是唯一性約束，確保一個字段里任意兩個值不重復(fù)并村，如果在登錄系統(tǒng)里面出現(xiàn)兩個用戶名是一樣的巍实，那就麻煩了。我們也可以用下面的方式定義例2中用戶名列的唯一性約束：

UniqueConstraint('username', name='uix_username')

例2里沒有用到檢查約束類型哩牍。這種約束類型是用來保證一列數(shù)據(jù)與用戶定義的條件一致棚潦。下面的例子，我們保證unit_cost
列永遠(yuǎn)非負(fù)膝昆，因?yàn)槌杀静豢赡苄∮?丸边。

CheckConstraint('unit_coust >= 0.00', name='unit_coust_positive')

除了鍵和約束叠必，我們還想更高效的查詢一些字段。這就要介紹索引（Indexes）妹窖。

Indexes

索引是用來加速查詢的纬朝，在例1里面，我們把cookie_name
加上了索引骄呼，因?yàn)槲覀冎牢覀兘?jīng)常需要查詢它們共苛。索引創(chuàng)建之后你就會獲得一個ix_cookies_cookie_name
索引。我們也可以顯式定義一個索引蜓萄。多列的索引可以通過分號分隔名稱來建立俄讹。你還可以增加一個參數(shù)unique=True
來保證索引具有唯一性。如果顯式聲明索引绕德，它們就會在放在對應(yīng)列后面患膛。下面的做法與例1相同：

from sqlalchemy import Index

Index('ix_cookies_cookie_name', 'cookie_name')

我們還可以創(chuàng)建了函數(shù)索引，不同的數(shù)據(jù)庫可能用法上會有點(diǎn)兒變化耻蛇。這樣可以讓你為一些經(jīng)常查詢不常用信息的需求創(chuàng)建檢索踪蹬。例如，如果你想從餅干的SKU號和名稱的組合中找SKU0001 Chocolate Chip
信息臣咖。我們就可以建立一個復(fù)合索引來查詢：

Index('ix_test', mytable.c.cookie_sku, mytable.c.cookie_name)

下面到了關(guān)系型數(shù)據(jù)庫最重要的部分了跃捣，就是表的關(guān)聯(lián)關(guān)系與定義。

Relationships and ForeignKeyConstraints

現(xiàn)在我們有了一個約束和索引都正確的表夺蛇，讓我們看看表之間的關(guān)系如何建立疚漆。我們需要一種方法來跟蹤訂單，包括記錄已銷售的餅干和數(shù)量的信息刁赦。表關(guān)系圖如下所示：

例3實(shí)現(xiàn)了line_items
表order_id
列的關(guān)系娶聘，就是用ForeignKeyConstraint
定義兩個表的關(guān)系。在本例中甚脉，我們有很多l(xiāng)ine_items
表示單個訂單丸升。但是，如果你深入到這些訂單中牺氨，你會發(fā)現(xiàn)訂單與cookies
表的cookie_id
外鍵有關(guān)聯(lián)關(guān)系狡耻。這是因?yàn)閘ine_items
表其實(shí)與orders
和cookies
表的一些數(shù)據(jù)都有關(guān)聯(lián)。關(guān)聯(lián)表用來表示另外兩個表之間的多對多關(guān)系猴凹。通常一個外鍵用來表示一對多關(guān)系夷狰，但是如果一個表有多個外鍵，那么這個表很可能是一個關(guān)聯(lián)表郊霎。

from sqlalchemy import ForeignKey, Boolean
orders = Table('orders', metadata,
Column('order_id', Integer(), primary_key=True),
Column('user_id', ForeignKey('users.user_id')),
Column('shipped', Boolean(), default=False)
)
line_items = Table('line_items', metadata,
Column('line_items_id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.order_id')),
Column('cookie_id', ForeignKey('cookies.cookie_id')),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2))
)

用字符串替換實(shí)際的列名可以在多個模塊中把表的定義分離沼头，這樣就不用擔(dān)心表加載的順序了。這是因?yàn)榇趼ǎ粫谑状芜B接表名稱和列名稱的時(shí)候解析字符串執(zhí)行表查詢瘫证。如果我們直接用表引用揉阎，比如cookies.c.cookie_id
，我們的外鍵在每次模塊初始化的時(shí)候都要執(zhí)行一次背捌，如果表加載的順序特別靠后就會出錯毙籽。

你可以顯式的調(diào)研ForeignKeyConstraint
定義，在SQLAlchemy里面可以對已經(jīng)建好的表建立外鍵毡庆，就像其他的鍵坑赡，約束和索引的創(chuàng)建一樣。需要先倒入ForeignKeyConstraint
模塊然后定義么抗。下面的代碼是創(chuàng)建一個ForeignKeyConstraint
表示line_items
表和orders
之間order_id
外鍵毅否。

from sqlalchemy import ForeignKeyConstraint

ForeignKeyConstraint(['order_id'], ['orders.order_id'])

前面做的每件事情都是用SQLAlchemy可以理解的方式定義的。如果你的數(shù)據(jù)庫已經(jīng)存在蝇刀，而且schema已經(jīng)建立螟加，你就可以進(jìn)行查詢了。如果你還沒建立吞琐，下面就介紹如何把數(shù)據(jù)庫建成文件捆探。

Persisting the Tables

表和模式定義與原數(shù)據(jù)相關(guān)。把模式保存到數(shù)據(jù)庫很簡單站粟，用metadata
的create_all()
方法就可以了：

metadata.create_all(engine)

默認(rèn)情況下黍图，create_all()
方法不會重新創(chuàng)建已經(jīng)存在的數(shù)據(jù)庫，所有運(yùn)行多次也很安全奴烙。相比直接在應(yīng)用代碼里修改數(shù)據(jù)庫助被，用Alembic那樣的遷移工具處理數(shù)據(jù)庫的更新或其他模式是更好的方法。我們將在后面的章節(jié)里介紹∏芯鳎現(xiàn)在數(shù)據(jù)庫里面已經(jīng)有表了揩环，這一章完整的代碼如下所示：

from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
Boolean, DateTime, ForeignKey, create_engine)
metadata = MetaData()
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50), index=True),
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2))
)
users = Table('users', metadata,
Column('user_id', Integer(), primary_key=True),
Column('customer_number', Integer(), autoincrement=True),
Column('username', String(15), nullable=False, unique=True),
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(),
default=datetime.now, onupdate=datetime.now)
)
orders = Table('orders', metadata,
Column('order_id', Integer(), primary_key=True),
Column('user_id', ForeignKey('users.user_id')),
Column('shipped', Boolean(), default=False)
)
line_items = Table('line_items', metadata,
Column('line_items_id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.order_id')),
Column('cookie_id', ForeignKey('cookies.cookie_id')),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2))
)
engine = create_engine('sqlite:///:memory:')
metadata.create_all(engine)
connection = engine.connect()

本章我們介紹了如何在SQLAlchemy里把元數(shù)據(jù)當(dāng)作目錄（catalog）來存放表模式和其他數(shù)據(jù)。我們還定義了帶有多列和多約束的表趾牧。然
后检盼，我們介紹約束的類型和這些約束對列進(jìn)行的顯式定義的方法。其次翘单，我們介紹了默認(rèn)值的設(shè)置和為了查賬進(jìn)行更新值的方法。最后蹦渣，我們介紹了把模式保存到數(shù)
據(jù)庫進(jìn)行重用的方法哄芜。下面我們介紹如何在模式中用SQL表達(dá)式語言進(jìn)行數(shù)據(jù)的操作。

3.SQLAlchemy Core數(shù)據(jù)操作

現(xiàn)在數(shù)據(jù)庫里面有了表柬唯，讓我們來操作它們认臊。首先我們將演示如何增刪改查，然后介紹如果排序锄奢，組合以及如何使用關(guān)系失晴。我們用SQLAlchemy Core提供的SEL（SQL表達(dá)式語言）演示剧腻。還是用上一章建立的數(shù)據(jù)庫，首先我們看看如何新建數(shù)據(jù)涂屁。

Inserting Data

首先书在，我們在cookies
表新建一行我最喜歡的餅干（巧克力味的）。用cookies
表的insert()
方法拆又，然后在values()
語句里面設(shè)置各個列的值就可以了儒旬。如下所示：

ins = cookies.insert().values(
cookie_name="chocolate chip",
cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
cookie_sku="CC01",
quantity="12",
unit_cost="0.50"
)
print(str(ins))

INSERT INTO cookies (cookie_name, cookie_recipe_url, cookie_sku, quantity, unit_cost) VALUES (:cookie_name, :cookie_recipe_url, :cookie_sku, :quantity, :unit_cost)

上面顯示了對應(yīng)的SQL語句。每一列的值都用:column_name
代替了帖族，SQLAlchemy的str()
函數(shù)就是這樣顯示的栈源。數(shù)值都進(jìn)行過清洗和轉(zhuǎn)義處理，保證數(shù)據(jù)安全竖般，避免SQL注入攻擊甚垦。因?yàn)椴煌N類的數(shù)據(jù)庫處理參數(shù)值的方言可能有點(diǎn)差別，所以通過編譯版本的語句可以看到輸入的內(nèi)容涣雕。ins
對象的compile()
方法會返回一個SQLAlchemy對象艰亮，通過params
屬性就可以看到數(shù)值了。

ins.compile().params

{'cookie_name': 'chocolate chip',
'cookie_recipe_url': 'http://some.aweso.me/cookie/recipe.html',
'cookie_sku': 'CC01',
'quantity': '12',
'unit_cost': '0.50'}

ins.compile()
通過方言編譯數(shù)據(jù)胞谭，但是并沒執(zhí)行垃杖，因此我們需要用params
屬性查看。

介紹了新建語句的用法之后丈屹，我們用connection
的execute()
方法把數(shù)據(jù)加入數(shù)據(jù)表调俘。

result = connection.execute(ins)

我們還可以用inserted_primary_key
屬性查看剛剛新建數(shù)據(jù)的ID號。

result.inserted_primary_key

[1]

我們簡單介紹一下excute()
執(zhí)行的過程旺垒。當(dāng)我們建立前面那條SQL表達(dá)式語言的插入語句時(shí)彩库，實(shí)際上是創(chuàng)建了一個可以快速向下遍歷的樹狀結(jié)構(gòu)。當(dāng)我們調(diào)用excute()
方法時(shí)先蒋，它把剛剛傳入的語句和其他任何參數(shù)一起編譯成對應(yīng)數(shù)據(jù)庫方言編譯器能夠識別的語句骇钦。編譯器通過遍歷那個樹狀結(jié)構(gòu)建成一個普通的SQL語句。這個語句返回到excute()
方法竞漾，excute()
方法通過綁定的連接把語句傳遞到數(shù)據(jù)庫眯搭。數(shù)據(jù)庫服務(wù)器就執(zhí)行SQL語句然后把結(jié)果返回給excute()
方法。

insert
除了可以作為表對象的實(shí)例方法业岁，也可以當(dāng)作頂層函數(shù)使用鳞仙，這樣可以把表對象作為參數(shù)，更具靈活性笔时。例如棍好，假如公司的兩個部門分別擁有相互獨(dú)立的庫存數(shù)據(jù)，就可以按照例3-3的形式再插入一行數(shù)據(jù)。

from sqlalchemy import insert
ins = insert(cookies).values(
cookie_name="chocolate chip",
cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
cookie_sku="CC01",
quantity="12",
unit_cost="0.50"
)

雖然insert
的表對象方法和更具一般性的函數(shù)兩種形式結(jié)果一樣借笙，我還是更喜歡后者扒怖，因?yàn)樗咏黃QL語句的用法。

連接對象的execute()
方法不僅僅只是處理語句业稼，還可以把values
當(dāng)作execute()
方法的參數(shù)盗痒。當(dāng)語句被編譯時(shí)，它會把每個關(guān)鍵詞參數(shù)的鍵增加到字段列表中，然后再把每個字段對應(yīng)的值增加到SQL語句的VALUE
參數(shù)里。

ins = cookies.insert()
result = connection.execute(
ins,
cookie_name='dark chocolate chip',
cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html',
cookie_sku='CC02',
quantity='1',
unit_cost='0.75'
)
result.inserted_primary_key

[2]

不過這種形式并不常用冠骄，但是黔衡，它為語句在傳到數(shù)據(jù)庫服務(wù)器之前的編譯和組織方式提供了一個很好的解釋。我們可以用放了字段和數(shù)值詞典的列表一次性插入多個記錄。讓我們把兩種餅干的庫存數(shù)據(jù)插入cookies
表。

ins = cookies.insert()
inventory_list = [
{
'cookie_name': 'peanut butter',
'cookie_recipe_url': 'http://some.aweso.me/cookie/peanut.html',
'cookie_sku': 'PB01',
'quantity': '24',
'unit_cost': '0.25'
},
{
'cookie_name': 'oatmeal raisin',
'cookie_recipe_url': 'http://some.okay.me/cookie/raisin.html',
'cookie_sku': 'EWW01',
'quantity': '100',
'unit_cost': '1.00'
}
]
result = connection.execute(ins, inventory_list)

列表中的每個詞典都要有同樣的鍵（keys
）。首先方言編譯器會編譯第一個詞典的語句內(nèi)容川慌，如果后面詞典的鍵與第一條不同就會失敗，因?yàn)榈谝粭l的字段已經(jīng)建好了祠乃。

現(xiàn)在有了數(shù)據(jù)梦重，我們就可以查詢了。

Querying Data

from sqlalchemy.sql import select
s = select([cookies])
rp = connection.execute(s)
results = rp.fetchall()
results

d:\Miniconda3\lib\site-packages\sqlalchemy\sql\sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does not support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
'storage.' % (dialect.name, dialect.driver))

[(1, 'chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'CC01', 12, Decimal('0.50')),
(2, 'dark chocolate chip', 'http://some.aweso.me/cookie/recipe_dark.html', 'CC02', 1, Decimal('0.75')),
(3, 'peanut butter', 'http://some.aweso.me/cookie/peanut.html', 'PB01', 24, Decimal('0.25')),
(4, 'oatmeal raisin', 'http://some.okay.me/cookie/raisin.html', 'EWW01', 100, Decimal('1.00'))]

print(str(s))

SELECT cookies.cookie_id, cookies.cookie_name, cookies.cookie_recipe_url, cookies.cookie_sku, cookies.quantity, cookies.unit_cost
FROM cookies

和insert
用法類似亮瓷，select
也是既可以作為表對象的實(shí)例方法琴拧，也可以作為更具一般性的頂層函數(shù)使用。我更喜歡頂層函數(shù)的使用方式嘱支，因?yàn)楹蚐QL用法一樣蚓胸。

from sqlalchemy.sql import select
s = cookies.select()
rp = connection.execute(s)
results = rp.fetchall()
results

[(1, 'chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'CC01', 12, Decimal('0.50')),
(2, 'dark chocolate chip', 'http://some.aweso.me/cookie/recipe_dark.html', 'CC02', 1, Decimal('0.75')),
(3, 'peanut butter', 'http://some.aweso.me/cookie/peanut.html', 'PB01', 24, Decimal('0.25')),
(4, 'oatmeal raisin', 'http://some.okay.me/cookie/raisin.html', 'EWW01', 100, Decimal('1.00'))]

ResultProxy

ResultProxy是對數(shù)據(jù)庫API光標(biāo)對象的封裝，其主要目的是讓數(shù)據(jù)操作更簡單除师。例如沛膳，它可以通過索引，字段名稱和列對象讓查詢操作更簡單汛聚，演示如例3-8所示锹安。任何一種方法查詢數(shù)據(jù)都很簡單。

first_row = results[0]

first_row[1]

'chocolate chip'

first_row.cookie_name

'chocolate chip'

first_row[cookies.c.cookie_name]

'chocolate chip'

上面的輸出結(jié)果都是results
變量第一個記錄的數(shù)據(jù)chocolate chip
倚舀。接入靈活只是ResultProxy能力的一部分叹哭。我們還可以用ResultProxy實(shí)現(xiàn)循環(huán)。例如痕貌，我們可以打印所有餅干的名稱话速。

rp = connection.execute(s)
for record in rp:
print(record.cookie_name)

chocolate chip
dark chocolate chip
peanut butter
oatmeal raisin

ResultProxy除了可以循環(huán)，或調(diào)用fetchall()
方法之外芯侥，很多其他數(shù)據(jù)接入方式也可以用。其實(shí)，上例中所有的result
變量所有的插入數(shù)據(jù)操作都是用ResultProxy實(shí)現(xiàn)的柱查。rowcount()
和inserted_primary_key()
方法也是ResultProxy獲取信息的一種方式廓俭。你也可以用下面的方法獲取信息：

first()——如果存在則返回第一條記錄并關(guān)閉連接

fetchone()——返回一條記錄，光標(biāo)繼續(xù)開著唉工，等待新的查詢

scalar()——如果查詢結(jié)果只有一行一列研乒，就返回一個值

如果要看結(jié)果中所有列名，可以用keys()
方法淋硝。后面的章節(jié)里雹熬，我們還會經(jīng)常使用first
，scalar
谣膳，fetchone
竿报，fetchall
方法和ResultProxy循環(huán)。

產(chǎn)品代碼
寫產(chǎn)品代碼的時(shí)候继谚，我有幾條原則：

用first
獲取一條記錄烈菌，不用scalar
和fetchone
，因?yàn)檫@樣更清晰

用ResultProxy循環(huán)花履，不用fetchall
和fetchone
方法芽世。這樣內(nèi)存開銷更新，因?yàn)橥ǔＮ覀兌际且淮翁幚硪粭l記錄

盡量不要用fetchone
诡壁，因?yàn)樗鼤屵B接一直開著

scalar
也要少用济瓢，因?yàn)楫?dāng)查詢返回多余一行一列數(shù)據(jù)的時(shí)候容易出錯，這一點(diǎn)在測試的時(shí)候經(jīng)常被忽略

在上例中妹卿，每一次我們查詢數(shù)據(jù)集的時(shí)候每條記錄的所有列都會返回旺矾。而通常我們只需要一部分列就可以。如果數(shù)據(jù)非常大纽帖，這樣查詢就會非常耗費(fèi)內(nèi)存宠漩，查
詢速度就會很慢。SQLAlchemy不會為查詢或ResultProxy增加負(fù)擔(dān)懊直；但是扒吁，通常查詢完成后，你需要看看查詢是否消耗了太多內(nèi)存室囊。下面我們
就來介紹如何控制查詢的范圍雕崩。

控制查詢的列

可以用select()
方法將要查詢的列以列表形式放入。例如融撞，下面代碼是只需要查看餅干的名稱和質(zhì)量時(shí)的操作盼铁。

s = select([cookies.c.cookie_name, cookies.c.quantity])
rp = connection.execute(s)
print(rp.keys())
result = rp.first()

['cookie_name', 'quantity']

result

('chocolate chip', 12)

這樣我們就建立了簡單的select
語句，我們將看看其他改變選擇結(jié)果的操作尝偎。首先我們看看如何改變順序饶火。

Ordering

如果在上面例10中你要查看所有的數(shù)據(jù)結(jié)果鹏控，你會發(fā)現(xiàn)名稱排序很混亂。但是肤寝，如果我們想讓名稱按照指定順序排列当辐，可用select
的order_by()
語句。

s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity)
rp = connection.execute(s)
for cookie in rp:
print('{} - {}'.format(cookie.quantity, cookie.cookie_name))

1 - dark chocolate chip
12 - chocolate chip
24 - peanut butter
100 - oatmeal raisin

如果要逆序排列鲤看，就在order_by()
里面增加desc()
缘揪。用desc()
包裹排序參數(shù)即可。

desc()
也可以當(dāng)成列對象的方法來使用义桂，

from sqlalchemy import desc
s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(desc(cookies.c.quantity))
rp = connection.execute(s)
for cookie in rp:
print('{} - {}'.format(cookie.quantity, cookie.cookie_name))

100 - oatmeal raisin
24 - peanut butter
12 - chocolate chip
1 - dark chocolate chip

還可以限制查詢記錄結(jié)果的數(shù)量找筝。

Limiting

前面的例子里，first()
和fetchone()
方法是用來獲得一行記錄的慷吊。ResultProxy可以提供一行數(shù)據(jù)袖裕，實(shí)際中我們經(jīng)常需要多行數(shù)據(jù)。如果我們想限制查詢數(shù)量罢浇，我們可以用limit()
函數(shù)陆赋。例如，如果我現(xiàn)在想做銷量最好的兩種餅干嚷闭，那么對排序后的查詢增加一個限制條件就可以了攒岛。

s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity)
s = s.limit(2)
rp = connection.execute(s)
print([result.cookie_name for result in rp])

['dark chocolate chip', 'chocolate chip']

現(xiàn)在我們知道餅干的種類了，我開始關(guān)心庫存還有多少胞锰。很多數(shù)據(jù)庫都有一堆SQL函數(shù)對數(shù)據(jù)進(jìn)行統(tǒng)計(jì)灾锯，必然SUM等等，讓我們看看這些函數(shù)如何使用嗅榕。

Builtin SQL Functions and Labels

SQLAlchemy的SQL函數(shù)里最常用的是SUM()
和COUNT()
顺饮。使用這個函數(shù)之前我們需要導(dǎo)入sqlalchemy.sql.func
模塊，這些函數(shù)只要包裹列對象就可以運(yùn)行凌那。所以要統(tǒng)計(jì)餅干的總量兼雄，我們可以這樣：

from sqlalchemy import func
s = select([func.sum(cookies.c.quantity)])
rp = connection.execute(s)
print(rp.scalar())

137

因?yàn)镻ython內(nèi)置函數(shù)也有sum
，所以使用SQLAlchemy的sum
時(shí)帽蝶，建議導(dǎo)入func
用func.sum
赦肋。

現(xiàn)在讓我們用count
函數(shù)統(tǒng)計(jì)cookie
表里有多少條庫存記錄。

s = select([func.count(cookies.c.quantity)])
rp = connection.execute(s)
record = rp.first()
print(record.keys()) # 顯示Resultproxy里的列
print(record.count_1) # 列名是自動生成的励稳，命名方式是<函數(shù)名>_<位置>

['count_1']
4

這個列名有點(diǎn)隱蔽佃乘。如果我們想自定義名稱，也可以用在count()
函數(shù)后用label()
函數(shù)來定義列名驹尼。例如趣避，我們想用更好記的名稱來定義記錄數(shù)量。

s = select([func.count(cookies.c.cookie_name).label('inventory_count')])
rp = connection.execute(s)
record = rp.first()
print(record.keys())
print(record.inventory_count)

['inventory_count']
4

介紹了如何限制行和列的數(shù)量之后新翎，我們再看看如何進(jìn)行數(shù)據(jù)過濾程帕。

Filtering

過濾查詢和SQL一樣用where()
函數(shù)來實(shí)現(xiàn)住练。通常where()
函數(shù)有一個列名稱，一個操作符和一個值或列骆捧。也可以用連接多個where()
語句澎羞，像SQL里面邏輯操作符AND
的作用。下面我們來找名稱為chocolate chip
的餅干敛苇。

s = select([cookies]).where(cookies.c.cookie_name == 'chocolate chip')
rp = connection.execute(s)
record = rp.first()
record.items() # 顯示所有列名和數(shù)值

[('cookie_id', 1),
('cookie_name', 'chocolate chip'),
('cookie_recipe_url', 'http://some.aweso.me/cookie/recipe.html'),
('cookie_sku', 'CC01'),
('quantity', 12),
('unit_cost', Decimal('0.50'))]

我們還可以用where()
找包含chocolate
的餅干名稱。

s = select([cookies]).where(cookies.c.cookie_name.like('%chocolate%'))
rp = connection.execute(s)
for record in rp.fetchall():
print(record.cookie_name)

chocolate chip
dark chocolate chip

where()
語句里我們用cookies.c.cookie_name
作為過濾的ClauseElement
類型顺呕。我們現(xiàn)在停下來枫攀，看看ClauseElement
類型的其他功能。

Operators

ClauseElements就是我們在從句中使用的一個元素株茶，通常都是數(shù)據(jù)表的列来涨；但是，和列不同启盛，ClauseElements有許多功能蹦掐。在例3-18里面，我們用來like()
方法僵闯，其實(shí)還有很多方法如表3-1所示卧抗。每個方法都和標(biāo)準(zhǔn)SQL里面的函數(shù)類似。后面會大量使用它們鳖粟。

方法

目的

between(cleft, cright)

Find where the column is between cleft and cright

concat(column_two)

Concatenate column with column_two

distinct()

Find only unique values for column

in_([list])

Find where the column is in the list

is_(None)

Find where the column is None (commonly used for Null checks with None)

contains(string)

Find where the column has string in it (Case-sensitive)

endswith(string)

Find where the column ends with string (Case-sensitive)

like(string)

Find where the column is like string (Case-sensitive)

startswith(string)

Find where the column begins with string (Case-sensitive)

ilike(string)

Find where the column is like string (NOT Case-sensitive)

還有兩個相反操作的函數(shù)notlike()
和notin_()
社裆，以及一個不帶下劃線的函數(shù)isnot()
。

如果我們不用這些方法向图，我們還可以用運(yùn)算符泳秀。大部分運(yùn)算符都和你的習(xí)慣一樣，不過我們要介紹一些奇怪的地方榄攀。

操作符

到目前為止我們過濾數(shù)據(jù)都是通過判斷列是否等于一個數(shù)值嗜傅，或者用ClauseElement方法的函數(shù)，比如like()
檩赢；其實(shí)我們還可以用很多運(yùn)算符號來過濾數(shù)據(jù)吕嘀。SQLAlchemy提供了大量Python的標(biāo)準(zhǔn)運(yùn)算符。這包括所有的標(biāo)準(zhǔn)比較運(yùn)算符（==
漠畜，!=
币他，<
，>
憔狞，>=
）蝴悉，與Python語句里使用方法一致。==
操作符在和None
比較時(shí)還表示IS NULL
瘾敢。算法運(yùn)算符（+
,-
,*
,/
,%
）也可用于數(shù)據(jù)庫字符串處理拍冠，如例3-19所示尿这。

s = select([cookies.c.cookie_name, 'SKU-' + cookies.c.cookie_sku])
for row in connection.execute(s):
print(row)

('chocolate chip', 'SKU-CC01')
('dark chocolate chip', 'SKU-CC02')
('peanut butter', 'SKU-PB01')
('oatmeal raisin', 'SKU-EWW01')

另一個常用操作就是計(jì)算多列的數(shù)值。通常做財(cái)務(wù)和統(tǒng)計(jì)報(bào)表時(shí)經(jīng)常用到庆杜，例3-20計(jì)算庫存金額：

from sqlalchemy import cast
s = select([cookies.c.cookie_name,
cast((cookies.c.quantity * cookies.c.unit_cost),
Numeric(12,2)).label('inv_cost')])
for row in connection.execute(s):
print('{} - {}'.format(row.cookie_name, row.inv_cost))

chocolate chip - 6.00
dark chocolate chip - 0.75
peanut butter - 6.00
oatmeal raisin - 100.00

d:\Miniconda3\lib\site-packages\sqlalchemy\sql\sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does not support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
'storage.' % (dialect.name, dialect.driver))

Boolean Operators

SQLAlchemy也支持邏輯運(yùn)算符（與AND射众，或OR，非NOT）晃财，用位操作符表示（&
叨橱，|
，~
）断盛。使用時(shí)一定要注意罗洗，因?yàn)镻ython自帶的邏輯運(yùn)算符規(guī)則。比如钢猛，&
比<
優(yōu)先級高伙菜，當(dāng)你寫A < B & C < D
的時(shí)候，運(yùn)算結(jié)果是A < (B & C) < D
命迈，而你實(shí)際想寫的是(A < B) & (C < D)
贩绕。為了清晰表達(dá)意思，請用連接詞（conjunctions）壶愤，不要用這些重載運(yùn)算符淑倾。

通常在處理多個從句時(shí)，從句之間存在與或非關(guān)系時(shí)公你，應(yīng)該用連接詞踊淳。

Conjunctions

雖然可以把多個where()
從句連起來，但是用連接詞會讓語句更清晰好看陕靠。SQLAlchemy的連接詞是and_()
迂尝，or_()
和not_()
。如果我們想要查詢庫存和單價(jià)滿足特定條件的餅干時(shí)剪芥，可以用and_()
實(shí)現(xiàn)垄开。

from sqlalchemy import and_, or_, not_
s = select([cookies]).where(
and_(
cookies.c.quantity > 23,
cookies.c.unit_cost < 0.40
)
)
for row in connection.execute(s):
print(row.cookie_name)

peanut butter

or_()
函數(shù)與and_()
相反，包含任何一種情形就可以税肪。如果我們想找到庫存在10到50之間溉躲，或名稱包含chip的餅干時(shí)，可以用or_
益兄。

from sqlalchemy import and_, or_, not_
s = select([cookies]).where(
or_(
cookies.c.quantity.between(10, 50),
cookies.c.cookie_name.contains('chip')
)
)
for row in connection.execute(s):
print(row.cookie_name)

chocolate chip
dark chocolate chip
peanut butter

not()
連接詞類似锻梳，只是表示不包含條件的結(jié)果。到這里我們已經(jīng)可以輕松的查詢數(shù)據(jù)了净捅，下面我們看看如何更新數(shù)據(jù)疑枯。

Updating Data

還有一個數(shù)據(jù)更新方法，和前面用的insert
方法類似蛔六，除了它們需要指定一個條件表面要更新的行荆永，語法與insert
完全一致废亭。更新方法可以用update()
函數(shù)或待更新表的update()
方法。如果不增加條件具钥，就會更新表中所有行豆村。當(dāng)我做完新的餅干之后，就要更新庫存數(shù)據(jù)骂删。在例3-23中掌动，我們更新對應(yīng)餅干的庫存，然后計(jì)算新的總庫存量桃漾。

from sqlalchemy import update
u = update(cookies).where(cookies.c.cookie_name == 'chocolate chip')
u = u.values(quantity=(cookies.c.quantity + 120))
result = connection.execute(u)
print(result.rowcount)

s = select([cookies]).where(cookies.c.cookie_name == "chocolate chip")
result = connection.execute(s).first()
for key in result.keys():
print('{:>20}: {}'.format(key, result[key]))

1
cookie_id: 1
cookie_name: chocolate chip
cookie_recipe_url: http://some.aweso.me/cookie/recipe.html
cookie_sku: CC01
quantity: 132
unit_cost: 0.50

除了更新數(shù)據(jù)坏匪，還要刪除數(shù)據(jù)。

Deleting Data

刪除數(shù)據(jù)可以用delete()
函數(shù)或表的delete()
方法撬统。和insert()
，update()
不同的是敦迄，delete()
無數(shù)值參數(shù)恋追，只有一個可選的where()
用于設(shè)置刪除區(qū)域（如果沒有就刪除全表所有數(shù)據(jù)）。

from sqlalchemy import delete

u = delete(cookies).where(cookies.c.cookie_name == 'dark chocolate chip')
result = connection.execute(u)
print(result.rowcount)

s = select([cookies]).where(cookies.c.cookie_name == "dark chocolate chip")
result = connection.execute(s).fetchall()
print(len(result))

1
0

現(xiàn)在讓我們回想一下所學(xué)的知識罚屋，對users
苦囱，orders
，line_items
表進(jìn)行更新脾猛。你可以直接復(fù)制代碼撕彤，但是建議你試試其他方法新建數(shù)據(jù)。

customer_list = [
{
'username': 'cookiemon',
'email_address': 'mon@cookie.com',
'phone': '111-111-1111',
'password': 'password'
},
{
'username': 'cakeeater',
'email_address': 'cakeeater@cake.com',
'phone': '222-222-2222',
'password': 'password'
},
{
'username': 'pieguy',
'email_address': 'guy@pie.com',
'phone': '333-333-3333',
'password': 'password'
}
]
ins = users.insert()
result = connection.execute(ins, customer_list)

有了用戶數(shù)據(jù)猛拴，讓我們再更新他們的orders
羹铅，line_items
表。

from sqlalchemy import insert
ins = insert(orders).values(user_id=1, order_id=1)
result = connection.execute(ins)
ins = insert(line_items)
order_items = [
{
'order_id': 1,
'cookie_id': 1,
'quantity': 2,
'extended_cost': 1.00
},
{
'order_id': 1,
'cookie_id': 3,
'quantity': 12,
'extended_cost': 3.00
}
]
result = connection.execute(ins, order_items)

ins = insert(orders).values(user_id=2, order_id=2)
result = connection.execute(ins)
ins = insert(line_items)
order_items = [
{
'order_id': 2,
'cookie_id': 1,
'quantity': 24,
'extended_cost': 12.00
},
{
'order_id': 2,
'cookie_id': 4,
'quantity': 6,
'extended_cost': 6.00
}
]
result = connection.execute(ins, order_items)

在SQLAlchemy Core的第二章中我們介紹過外鍵和關(guān)系愉昆；但是我們還沒有用它們做過查詢职员，下面我們就來看看這些關(guān)系。

Joins

用join()
和outerjoin()
方法進(jìn)行數(shù)據(jù)關(guān)聯(lián)跛溉。例如焊切，發(fā)貨之前需要了解用戶cookiemon
訂購了那種餅干。這就需要用3個join
來匯總?cè)龔埍淼臄?shù)據(jù)芳室。另外专肪，當(dāng)用多個join
匯總數(shù)據(jù)時(shí)，你可能需要重新組織from
后面join
關(guān)聯(lián)內(nèi)容的順序堪侯，SQLAlchemy提供了select_from
來實(shí)現(xiàn)這個功能嚎尤。通過select_from
我們可以將整個from
從句替換成SQLAlchemy支持的形式。

columns = [orders.c.order_id, users.c.username, users.c.phone,
cookies.c.cookie_name, line_items.c.quantity,
line_items.c.extended_cost]
cookiemon_orders = select(columns)
cookiemon_orders = cookiemon_orders.select_from(orders.join(users).join(
line_items).join(cookies)).where(users.c.username ==
'cookiemon')
result = connection.execute(cookiemon_orders).fetchall()
for row in result:
print(row)

(1, 'cookiemon', '111-111-1111', 'chocolate chip', 2, Decimal('1.00'))
(1, 'cookiemon', '111-111-1111', 'peanut butter', 12, Decimal('3.00'))

d:\Miniconda3\lib\site-packages\sqlalchemy\sql\sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does not support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
'storage.' % (dialect.name, dialect.driver))

上面的SQL語句是這樣：

print(str(cookiemon_orders))

SELECT orders.order_id, users.username, users.phone, cookies.cookie_name, line_items.quantity, line_items.extended_cost
FROM orders JOIN users ON users.user_id = orders.user_id JOIN line_items ON orders.order_id = line_items.order_id JOIN cookies ON cookies.cookie_id = line_items.cookie_id
WHERE users.username = :username_1

統(tǒng)計(jì)所有用戶各自的訂單數(shù)量也常用抖格，不只是當(dāng)前的訂單诺苹」窘可以通過outerjoin()
方法實(shí)現(xiàn)，需要注意join
的順序收奔，因?yàn)橛胦uterjoin()
方法生成的表會返回所有外鍵匹配結(jié)果掌呜。

columns = [users.c.username, func.count(orders.c.order_id)]
all_orders = select(columns)
all_orders = all_orders.select_from(users.outerjoin(orders))
all_orders = all_orders.group_by(users.c.username) #后面介紹分組
result = connection.execute(all_orders).fetchall()
for row in result:
print(row)

('cakeeater', 1)
('cookiemon', 1)
('pieguy', 0)

現(xiàn)在，我們已經(jīng)可以通過join
實(shí)現(xiàn)關(guān)聯(lián)查詢了坪哄。但是质蕉，如果我們有一個像員工與老板關(guān)系表，用SQLAlchemy清楚的讀取和理解內(nèi)容需要用alias
翩肌。

Aliases

用join
的時(shí)候模暗，通常要對一個表進(jìn)行多次引用。在SQL里面念祭，這是通過查詢中的aliases
實(shí)現(xiàn)的兑宇。例如，假設(shè)我們有下面的schema
結(jié)構(gòu)：

employee_table = Table(
'employee', metadata,
Column('id', Integer, primary_key=True),
Column('manager_id', None, ForeignKey('employee.id')),
Column('name', String(255)))

現(xiàn)在假如我們想選擇Fred
手下的所有員工粱坤。在SQL里面隶糕，我們會這么做：

SELECT employee.name
FROM employee, employee AS manager
WHERE employee.manager_id = manager.id
AND manager.name = 'Fred'

SQLAlchemy也允許用alias()
方法實(shí)現(xiàn)：

manager = employee_table.alias('mgr')

stmt = select([employee_table.c.name],
and_(employee_table.c.id==manager.c.id,
manager.c.name=='Fred'))

print(stmt)

SELECT employee.name
FROM employee, employee AS mgr
WHERE employee.id = mgr.id AND mgr.name = :name_1

SQLAlchemy也可以自動選擇別名：

manager = employee_table.alias()

stmt = select([employee_table.c.name],
and_(employee_table.c.id==manager.c.id,
manager.c.name=='Fred'))

print(stmt)

SELECT employee.name
FROM employee, employee AS employee_1
WHERE employee.id = employee_1.id AND employee_1.name = :name_1

數(shù)據(jù)分組group_by
也是很有用的，下面我們來看看站玄。

Grouping

SQLAlchemy可以對一個或多個列進(jìn)行數(shù)據(jù)分組枚驻，然后統(tǒng)計(jì)各組的計(jì)數(shù)項(xiàng)（count
），總和（sum
）和其他統(tǒng)計(jì)參數(shù)株旷，與SQL類似再登。下面我們看看每個客戶的訂單數(shù)：

columns = [users.c.username, func.count(orders.c.order_id)]
all_orders = select(columns)
all_orders = all_orders.select_from(users.outerjoin(orders))
all_orders = all_orders.group_by(users.c.username)
result = connection.execute(all_orders).fetchall()
for row in result:
print(row)

('cakeeater', 1)
('cookiemon', 1)
('pieguy', 0)

前面的join
的例子中我們已經(jīng)用過，這里再看看group_by
的用法晾剖。

Chaining

前面我們已經(jīng)用過鏈?zhǔn)奖磉_(dá)式锉矢，只是那時(shí)沒有明說。進(jìn)行數(shù)據(jù)查詢時(shí)鏈?zhǔn)奖磉_(dá)式能夠清楚地顯示查詢的邏輯钞瀑。因此沈撞，如果我們想用一個函數(shù)獲取訂單數(shù)據(jù)，可以如例3-28所示雕什。

def get_orders_by_customer(cust_name):
columns = [orders.c.order_id, users.c.username, users.c.phone,
cookies.c.cookie_name, line_items.c.quantity,
line_items.c.extended_cost]
cust_orders = select(columns)
cust_orders = cust_orders.select_from(users.join(orders).join(line_items).join(cookies))
cust_orders = cust_orders.where(users.c.username == cust_name)
result = connection.execute(cust_orders).fetchall()
return result

get_orders_by_customer('cakeeater')

[(2, 'cakeeater', '222-222-2222', 'chocolate chip', 24, Decimal('12.00')),
(2, 'cakeeater', '222-222-2222', 'oatmeal raisin', 6, Decimal('6.00'))]

如果我們只想顯示已發(fā)貨或未發(fā)貨的訂單缠俺，怎么辦？我們可以再寫函數(shù)來支持其他選項(xiàng)贷岸，或者我們可以用鏈?zhǔn)讲樵儊磉^濾壹士。推薦后一種方法，尤其是處理復(fù)雜查詢和制作報(bào)表時(shí)威力非常強(qiáng)大偿警。

def get_orders_by_customer(cust_name, shipped=None, details=False):
columns = [orders.c.order_id, users.c.username, users.c.phone]
joins = users.join(orders)
if details:
columns.extend([cookies.c.cookie_name, line_items.c.quantity,
line_items.c.extended_cost])
joins = joins.join(line_items).join(cookies)
cust_orders = select(columns)
cust_orders = cust_orders.select_from(joins)
cust_orders = cust_orders.where(users.c.username == cust_name)
if shipped is not None:
cust_orders = cust_orders.where(orders.c.shipped == shipped)
result = connection.execute(cust_orders).fetchall()
return result

get_orders_by_customer('cakeeater')

[(2, 'cakeeater', '222-222-2222')]

get_orders_by_customer('cakeeater', details=True)

[(2, 'cakeeater', '222-222-2222', 'chocolate chip', 24, Decimal('12.00')),
(2, 'cakeeater', '222-222-2222', 'oatmeal raisin', 6, Decimal('6.00'))]

get_orders_by_customer('cakeeater', shipped=True)

[]

get_orders_by_customer('cakeeater', shipped=False)

[(2, 'cakeeater', '222-222-2222')]

get_orders_by_customer('cakeeater', shipped=False, details=True)

[(2, 'cakeeater', '222-222-2222', 'chocolate chip', 24, Decimal('12.00')),
(2, 'cakeeater', '222-222-2222', 'oatmeal raisin', 6, Decimal('6.00'))]

到此為止躏救，我們已經(jīng)用SQL表達(dá)式語言的例子演示了SQLAlchemy Core。其實(shí)，你也可以直接用標(biāo)準(zhǔn)的SQL語言來進(jìn)行盒使。

Raw Queries

在SQLAlchemy Core里面可以用原始的SQL語句進(jìn)行操作崩掘，結(jié)果也是返回一個代理，后面的操作和SQLAlchemy Core的SQL表達(dá)式語法一樣少办。除非必須苞慢，一般不推薦使用原始SQL，因?yàn)檫@么做存在安全隱患英妓。下面我們簡單演示一下挽放。

result = connection.execute('select * from orders').fetchall()
print(result)

[(1, 1, 0), (2, 2, 0)]

有時(shí)候一些SQL小片段可以讓語句表述更清晰。下面是用text()
函數(shù)實(shí)現(xiàn)where
的條件蔓纠。

from sqlalchemy import text
stmt = select([users]).where(text("username='cookiemon'"))
print(connection.execute(stmt).fetchall())

[(1, None, 'cookiemon', 'mon@cookie.com', '111-111-1111', 'password', datetime.datetime(2015, 9, 1, 21, 37, 37, 531465), datetime.datetime(2015, 9, 1, 21, 37, 37, 531465))]

通過本章介紹的增刪改查等操作辑畦，現(xiàn)在你應(yīng)該已經(jīng)理解SQLAlchemy Core的SQL表達(dá)式用法了，用自己的數(shù)據(jù)庫試試吧腿倚。下面我們來介紹SQLAlchemy的異常處理纯出，以及事務(wù)處理transactions
分組的用法。

Exceptions and Transactions

前面做數(shù)據(jù)處理時(shí)敷燎，大部分工作我們都只用一行語句就搞定了潦刃，我們盡量避免做任何可能引起異常的事情，而本章我們會刻意搞點(diǎn)bug來演示異常處理方法懈叹。另外，我們還會介紹如何把對需要處理的任務(wù)分成單獨(dú)的事務(wù)進(jìn)行處理分扎，保證每個事務(wù)都可以被適當(dāng)?shù)貓?zhí)行或正確地清理澄成。

Exceptions

SQLAlchemy會產(chǎn)生許多不同類型的異常；不過這里只重點(diǎn)介紹一些常見的異常：AttributeErrors
和IntegrityErrors
畏吓。通過對常用異常處理方法的學(xué)習(xí)墨状，你可以掌握其他異常的處理方法。

下面菲饼，請重新開一個Python shell或Notebook肾砂，然后用SQLAlchemy Core建立數(shù)據(jù)表。具體過程如例4-1所示宏悦。

from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
DateTime, ForeignKey, Boolean, create_engine,
CheckConstraint)
metadata = MetaData()
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50), index=True),
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2)),
CheckConstraint('quantity > 0', name='quantity_positive')
)
users = Table('users', metadata,
Column('user_id', Integer(), primary_key=True),
Column('username', String(15), nullable=False, unique=True),
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(),
default=datetime.now, onupdate=datetime.now)
)
orders = Table('orders', metadata,
Column('order_id', Integer()),
Column('user_id', ForeignKey('users.user_id')),
Column('shipped', Boolean(), default=False)
)
line_items = Table('line_items', metadata,
Column('line_items_id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.order_id')),
Column('cookie_id', ForeignKey('cookies.cookie_id')),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2))
)
engine = create_engine('sqlite:///:memory:')
metadata.create_all(engine)
connection = engine.connect()

metadata.tables.keys()

dict_keys(['users', 'cookies', 'line_items', 'orders'])

AttributeError

AttributeError
是指獲取一個不存在的屬性時(shí)產(chǎn)生的異常镐确。經(jīng)常是在連接ResultProxy
里沒有的一列時(shí)發(fā)生。在嘗試獲取一個對象不存在的屬性時(shí)AttributeError
也會發(fā)生饼煞。在普通的Python代碼里也會發(fā)生源葫。這里單獨(dú)拿出來說是因?yàn)镾QLAlchemy里非常容易出現(xiàn)這類異常，而其產(chǎn)生的根源卻很容易忽略砖瞧。為了演示這類異常息堂，我們在users
表里插入一列數(shù)據(jù)，然后我們查詢沒有selsect
的一列來誘發(fā)異常。

from sqlalchemy import select, insert
ins = insert(users).values(
username="cookiemon",
email_address="mon@cookie.com",
phone="111-111-1111",
password="password"
)
result = connection.execute(ins)

s = select([users.c.username])
results = connection.execute(s)
for result in results:
print(result.username)
print(result.password)

cookiemon

KeyError Traceback (most recent call last)
d:\programfiles\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py in getitem(self, key)
69 try:
---> 70 processor, obj, index = self._keymap[key]
71 except KeyError:

KeyError: 'password'

During handling of the above exception, another exception occurred:

NoSuchColumnError Traceback (most recent call last)
d:\programfiles\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py in getattr(self, name)
95 try:
---> 96 return self[name]
97 except KeyError as e:

d:\programfiles\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py in getitem(self, key)
71 except KeyError:
---> 72 processor, obj, index = self._parent._key_fallback(key)
73 except TypeError:

d:\programfiles\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py in _key_fallback(self, key, raiseerr)
405 "Could not locate column in row for column '%s'" %
--> 406 expression._string_or_unprintable(key))
407 else:

NoSuchColumnError: "Could not locate column in row for column 'password'"

During handling of the above exception, another exception occurred:

AttributeError Traceback (most recent call last)
<ipython-input-9-c4520631a10a> in <module>()
3 for result in results:
4 print(result.username)
----> 5 print(result.password)

d:\programfiles\Miniconda3\lib\site-packages\sqlalchemy\engine\result.py in getattr(self, name)
96 return self[name]
97 except KeyError as e:
---> 98 raise AttributeError(e.args[0])
99
100

AttributeError: Could not locate column in row for column 'password'

在例4-2中我們看到Python拋出了AttributeError
并停止了程序荣堰，我們可以看到AttributeError
是Python里常見的形式床未。首先顯示錯異常的類型，然后一個箭頭會指明異常發(fā)生的位置振坚，緊接著是異常發(fā)生源代碼薇搁。最后一行會顯示異常的具體內(nèi)容，它會顯示異常類型屡拨，以及為什么發(fā)生異常只酥。這里出現(xiàn)異常的原因是ResultProxy里面沒有password
列，我們只查詢了username
列呀狼。這是在使用SQLAlchemy對象時(shí)出現(xiàn)的一個常見Python異常裂允，還有一些SQLAlchemy自己特有的異常。下面就是其中一個：IntegrityError
哥艇。

IntegrityError

IntegrityError
是另一個SQLAlchemy常見的異常绝编，當(dāng)我們做了不符合數(shù)據(jù)表或字段約束條件的事情時(shí)就會發(fā)生。當(dāng)你請求的數(shù)據(jù)是唯一的貌踏，比如users
表中的username
,想創(chuàng)建兩個同名用戶時(shí)就會產(chǎn)生IntegrityError
十饥，演示程序如下所示。

s = select([users.c.username])

connection.execute(s).fetchall()

[('cookiemon',)]

ins = insert(users).values(
username="cookiemon",
email_address="damon@cookie.com",
phone="111-111-1111",
password="password"
)
result = connection.execute(ins)

和前面的AttributeError
類似祖乳。首先顯示錯異常的類型逗堵，然后一個箭頭會指明異常發(fā)生的位置，緊接著是異常發(fā)生源代碼眷昆。最后一行會顯示異常的具體內(nèi)容蜒秤，它會顯示異常類型，以及為什么發(fā)生異常亚斋。這里指明了發(fā)生的原因：

UNIQUE constraint failed: users.username

這就告訴我們在users
表的username
里面插入同名用戶是不允許的作媚。之后的內(nèi)容是SQLAlchemy表達(dá)式轉(zhuǎn)變成的SQL語句，我們在第三章里介紹過帅刊，還有我們計(jì)劃插入?yún)s產(chǎn)生異常的數(shù)據(jù)纸泡。程序在這里停止。

當(dāng)然會有很多種異常類型赖瞒，這里介紹的兩種是最常見的女揭。SQLAlchemy里所有異常發(fā)生都會按照這兩種方式產(chǎn)生。具體異常的內(nèi)容請查看SQLAlchemy文檔冒黑。

為了保證程序在發(fā)生異常時(shí)繼續(xù)運(yùn)行田绑，我們需要實(shí)現(xiàn)異常處理方法。

Handling Errors

要防止異常中斷程序抡爹，我們需要正確地處理異常掩驱。這與Python的異常處理方法一樣，用try/except
代碼塊實(shí)現(xiàn)。例如欧穴，我們可以用try/except
代碼塊捕捉異常顯示信息民逼，然后讓后面的程序繼續(xù)運(yùn)行。

from sqlalchemy.exc import IntegrityError

ins = insert(users).values(
username="cookiemon",
email_address="damon@cookie.com",
phone="111-111-1111",
password="password"
)
try:
result = connection.execute(ins)
except IntegrityError as error:
print(error)

(sqlite3.IntegrityError) UNIQUE constraint failed: users.username [SQL: 'INSERT INTO users (username, email_address, phone, password, created_on, updated_on) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: ('cookiemon', 'damon@cookie.com', '111-111-1111', 'password', '2015-09-01 13:45:18.991258', '2015-09-01 13:45:18.992259')]

雖然和前面例子代碼一樣涮帘，但是因?yàn)橛衪ry/except
處理IntegrityError
異常拼苍，所有結(jié)果就是簡單的異常信息。這個例子只是演示了異常打印功能调缨，其實(shí)我們可以在異常處理中寫任何Python代碼厚满。返回異常信息告訴用戶剛剛的操作失敗了是很有用的做法邻耕。異常處理完成后肴盏，程序還會繼續(xù)運(yùn)行威沫。雖然這里只處理了IntegrityError
，但對其他SQLAlchemy異常也適用伤哺。

try/except
里面的代碼越少越好燕侠。因?yàn)榇a太多可能會出現(xiàn)你意料之外的異常，不符合你捕捉的目標(biāo)立莉。

用傳統(tǒng)的Python方法可以捕捉一行語句的異常绢彤，如果我們有多個數(shù)據(jù)庫語句，彼此之間互相關(guān)聯(lián)蜓耻，這個方法可能就不適用了茫舶。這時(shí)，我們需要將那些語句封裝成一個數(shù)據(jù)庫事務(wù)刹淌，SQLAlchemy提供了一個簡單的包裝來建立對象之間的鏈接：transactions奇适。

Transactions

我們不需要學(xué)習(xí)一堆數(shù)據(jù)庫理論，可以把事務(wù)看成是一種確保多個數(shù)據(jù)庫語句打包成一組運(yùn)行成功或運(yùn)行失敗的處理方式芦鳍。當(dāng)啟動一個事務(wù)的時(shí)候，我們記錄
數(shù)據(jù)庫的狀態(tài)葛账，然后執(zhí)行多條SQL語句柠衅。如果所有的SQL語句都能順利執(zhí)行，數(shù)據(jù)庫就會不斷的更新狀態(tài)籍琳，忽略前面的狀態(tài)菲宴，如下圖所示。

但是趋急，如果有一條語句運(yùn)行失敗了喝峦，整個數(shù)據(jù)庫就要回退（rollback）到原來的狀態(tài)，如下圖所示呜达。

我們再新建一個Python Shell或Notebook赏僧，還用第三章的表，只是為quantity
字段增加一個CheckConstraint
限制條件扭倾，即庫存量不能小于0淀零。然后我們創(chuàng)建用戶cookiemon，并設(shè)置chocolate chip和dark chocolate chip cookie的庫存量吆录，其中chocolate chip庫存量為12窑滞，dark chocolate chip cookie庫存量為1，具體程序如下所示恢筝。

from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
DateTime, ForeignKey, Boolean, create_engine,
CheckConstraint)

metadata = MetaData()
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50), index=True),
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2)),
CheckConstraint('quantity >= 0', name='quantity_positive')
)
users = Table('users', metadata,
Column('user_id', Integer(), primary_key=True),
Column('username', String(15), nullable=False, unique=True),
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(),
default=datetime.now, onupdate=datetime.now)
)
orders = Table('orders', metadata,
Column('order_id', Integer()),
Column('user_id', ForeignKey('users.user_id')),
Column('shipped', Boolean(), default=False)
)
line_items = Table('line_items', metadata,
Column('line_items_id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.order_id')),
Column('cookie_id', ForeignKey('cookies.cookie_id')),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2))
)
engine = create_engine('sqlite:///:memory:')
metadata.create_all(engine)
connection = engine.connect()

from sqlalchemy import select, insert, update
ins = insert(users).values(
username="cookiemon",
email_address="mon@cookie.com",
phone="111-111-1111",
password="password"
)
result = connection.execute(ins)
ins = cookies.insert()
inventory_list = [
{
'cookie_name': 'chocolate chip',
'cookie_recipe_url': 'http://some.aweso.me/cookie/recipe.html',
'cookie_sku': 'CC01',
'quantity': '12',
'unit_cost': '0.50'
},
{
'cookie_name': 'dark chocolate chip',
'cookie_recipe_url': 'http://some.aweso.me/cookie/recipe_dark.html',
'cookie_sku': 'CC02',
'quantity': '1',
'unit_cost': '0.75'
}
]
result = connection.execute(ins, inventory_list)

s = select([cookies.c.cookie_name, cookies.c.quantity])
connection.execute(s).fetchall()

[('chocolate chip', 12), ('dark chocolate chip', 1)]

現(xiàn)在哀卫，我們再定義兩個cookiemon的訂單，第一條訂單是9塊chocolate chip撬槽，第二條訂單是4塊chocolate chip和1塊dark chocolate chip cookie此改。我們用插入語句來實(shí)現(xiàn)，具體如下所示侄柔。

ins = insert(orders).values(user_id=1, order_id='1')
result = connection.execute(ins)
ins = insert(line_items)
order_items = [
{
'order_id': 1,
'cookie_id': 1,
'quantity': 9,
'extended_cost': 4.50
}
]
result = connection.execute(ins, order_items)
ins = insert(orders).values(user_id=1, order_id='2')
result = connection.execute(ins)
ins = insert(line_items)
order_items = [
{
'order_id': 2,
'cookie_id': 2,
'quantity': 1,
'extended_cost': 0.75
},
{
'order_id': 2,
'cookie_id': 1,
'quantity': 4,
'extended_cost': 2.00
}
]
result = connection.execute(ins, order_items)

這樣我們就有了演示事務(wù)的訂單數(shù)據(jù)了共啃，現(xiàn)在我們需要定義一個函數(shù)ship_it
。這個函數(shù)接受一個order_id
暂题，然后從庫存中去掉對應(yīng)的購買量移剪，并把訂單標(biāo)記成已發(fā)貨，shipped=True
薪者，如下所示纵苛。

from sqlalchemy.exc import IntegrityError
def ship_it(order_id):
try:
s = select([line_items.c.cookie_id, line_items.c.quantity])
s = s.where(line_items.c.order_id == order_id)
cookies_to_ship = connection.execute(s)
for cookie in cookies_to_ship:
u = update(cookies).where(cookies.c.cookie_id == cookie.cookie_id)
u = u.values(quantity=cookies.c.quantity - cookie.quantity)
result = connection.execute(u)
u = update(orders).where(orders.c.order_id == order_id)
u = u.values(shipped=True)
result = connection.execute(u)
print("Shipped order ID: {}".format(order_id))
except IntegrityError as error:
print(error)

當(dāng)一條訂單發(fā)貨之后，函數(shù)ship_it
就會執(zhí)行動作言津。讓我們對第一條訂單執(zhí)行函數(shù)ship_it
攻人，然后看看cookies
表是否更新了庫存。

ship_it(1)

Shipped order ID: 1

s = select([cookies.c.cookie_name, cookies.c.quantity])
connection.execute(s).fetchall()

[('chocolate chip', 3), ('dark chocolate chip', 1)]

運(yùn)行正常悬槽。但是現(xiàn)在的庫存量不能滿足第二條訂單怀吻；但是，在工作節(jié)奏很快的倉庫中初婆，這些訂單應(yīng)該可以同時(shí)處理∨钇拢現(xiàn)在我們再用函數(shù)ship_it
處理一下第二條訂單猿棉。

ship_it(2)

(sqlite3.IntegrityError) CHECK constraint failed: quantity_positive [SQL: 'UPDATE cookies SET quantity=(cookies.quantity - ?) WHERE cookies.cookie_id = ?'] [parameters: (4, 1)]

s = select([cookies.c.cookie_name, cookies.c.quantity])
connection.execute(s).fetchall()

[('chocolate chip', 3), ('dark chocolate chip', 0)]

程序中庫存量先扣減1塊dark chocolate chip cookie，可以正常執(zhí)行渣窜，再扣減4塊chocolate chip铺根，因?yàn)闆]有足夠的chocolate chip
庫存導(dǎo)致了IntegrityError
異常。但是dark chocolate chip
的庫存扣減了乔宿，這不是我們想看到的位迂。我們這里想要發(fā)生整個訂單，不允許單獨(dú)讓一部分先發(fā)貨详瑞。用前面介紹的異常處理方法可以解決這個問題掂林，但是，事務(wù)提供了更好的處理方式坝橡。

事務(wù)通過connection
對象的begin()
方法啟動泻帮。這樣我們就獲得一個事務(wù)，可以處理后面所有的語句计寇。如果這些語句都成功執(zhí)行锣杂，我們就可以用commit()
方法對數(shù)據(jù)庫進(jìn)行狀態(tài)確認(rèn)。如果不完全成功番宁，我們就用rollback()
方法讓數(shù)據(jù)庫回退到原始狀態(tài)元莫。下面讓我們用事務(wù)把函數(shù)ship_it
改一下。

def ship_it(order_id):
s = select([line_items.c.cookie_id, line_items.c.quantity])
s = s.where(line_items.c.order_id == order_id)
transaction = connection.begin()
cookies_to_ship = connection.execute(s).fetchall()
try:
for cookie in cookies_to_ship:
u = update(cookies).where(cookies.c.cookie_id == cookie.cookie_id)
u = u.values(quantity=cookies.c.quantity-cookie.quantity)
result = connection.execute(u)
u = update(orders).where(orders.c.order_id == order_id)
u = u.values(shipped=True)
result = connection.execute(u)
print("Shipped order ID: {}".format(order_id))
transaction.commit()
except IntegrityError as error:
transaction.rollback()
print(error)

現(xiàn)在我們把dark chocolate chip
的庫存重新補(bǔ)成1蝶押。

u = update(cookies).where(cookies.c.cookie_name == "dark chocolate chip")
u = u.values(quantity = 1)
result = connection.execute(u)

s = select([cookies.c.cookie_name, cookies.c.quantity])
connection.execute(s).fetchall()

[('chocolate chip', 3), ('dark chocolate chip', 1)]

再用新的函數(shù)ship_it
對第二條訂單進(jìn)行處理踱蠢。程序不會因?yàn)楫惓６Ｖ梗粫蛴‘惓Ｐ畔ⅰ?/p>

ship_it(2)

(sqlite3.IntegrityError) CHECK constraint failed: quantity_positive [SQL: 'UPDATE cookies SET quantity=(cookies.quantity - ?) WHERE cookies.cookie_id = ?'] [parameters: (4, 1)]

再用庫存查看語句看看庫存的狀態(tài)棋电，回退到第二條訂單處理之前的狀態(tài)了茎截。

s = select([cookies.c.cookie_name, cookies.c.quantity])
connection.execute(s).fetchall()

[('chocolate chip', 3), ('dark chocolate chip', 1)]

這就是事務(wù)的作用，短短幾行代碼就可以讓數(shù)據(jù)庫回退到異常發(fā)生之前的狀態(tài)赶盔，非常給力吧企锌。

本章我們介紹了單行語句和成組的多行語句的異常處理方法。對單行語句可以通過try/except
來防止程序因?yàn)槌霈F(xiàn)異常而中斷于未。我們還介紹了事務(wù)的處理方法霎俩，保證應(yīng)用在處理成組的多行語句時(shí)不發(fā)生中斷。現(xiàn)在沉眶，我們還需要學(xué)習(xí)如何測試代碼的方法，保證其行為符合我們的預(yù)期杉适，讓我們開始下一章吧谎倔。