數(shù)據(jù)庫操作必讀：SQLAlchemy入門

全文約 7991 字，讀完可能需要 12 分鐘。

創(chuàng)新互聯(lián)是一家專注于網(wǎng)站設(shè)計(jì)、成都網(wǎng)站設(shè)計(jì)與策劃設(shè)計(jì),無極網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)10年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:無極等地區(qū)。無極做網(wǎng)站價(jià)格咨詢:13518219792

環(huán)境 ：Ubuntu 15.10 64-bit

SQLAlchemy是Python的ORM框架，它的理念是：數(shù)據(jù)庫的量級(jí)和性能重要于對(duì)象集合，而對(duì)象集合的抽象又重要于表和行。

安裝

直接通過pip安裝：

$ pip install sqlalchemy

打開Python，測試是否安裝成功：

>>> import sqlalchemy>>> sqlalchemy.__version__'1.0.9'

創(chuàng)建引擎

SQLite

首先以SQLite為例，因?yàn)樗容^簡單。

from sqlalchemy import create_engine, MetaDataengine = create_engine('sqlite:///foo.db', echo=True)metadata = MetaData(engine)

參數(shù) sqlite:///foo.db 解釋為：

sqlite://<nohostname>/<path>

其中foo.db是相對(duì)路徑。也可寫成：

sqlite:///./foo.db

SQLAlchemy缺省使用Python內(nèi)建的sqlite3模塊來連接或創(chuàng)建SQLite數(shù)據(jù)庫。執(zhí)行完create_engine后，可以發(fā)現(xiàn)當(dāng)前目錄多了foo.db文件，不妨用sqlite打開看看。

$ sqlite3 foo.dbSQLite version 3.8.11.1 2015-07-29 20:00:57Enter ".help" for usage hints.sqlite> .tables

注意這里用的是sqlite3而非sqlite，因?yàn)閒oo.db是經(jīng)由Python內(nèi)建的sqlite3模塊創(chuàng)建的。

MySQL

再來看看連接MySQL時(shí)怎么創(chuàng)建引擎。 本文后續(xù)示例全部基于MySQL，這是與官方文檔不同的地方。 先在MySQL里創(chuàng)建一個(gè)測試數(shù)據(jù)庫：sa_test，后續(xù)示例都將基于這個(gè)數(shù)據(jù)庫。

mysql> CREATE DATABASE sa_test DEFAULT CHARACTER SET UTF8;

from sqlalchemy import create_engine, MetaDataengine = create_engine('mysql+mysqldb://root:******@localhost/sa_test', echo=True)metadata = MetaData(engine)

這里的參數(shù)看上去就比較復(fù)雜了，完整的格式為：

dialect+driver://username:password@host:port/database

這里driver用了mysqldb，詳見： MySQLdb：Python操作MySQL數(shù)據(jù)庫

引擎配置的詳細(xì)信息可參考官方文檔： Engine Configuration

MetaData

前面在創(chuàng)建MetaData時(shí)綁定了引擎：

metadata = MetaData(engine)

當(dāng)然也可以不綁定。綁定的好處是，后續(xù)很多調(diào)用 （比如 MetaData.create_all()，Table.create()，等等）就不用指定引擎了。

創(chuàng)建表

創(chuàng)建兩張表，user和address，address表里有一個(gè)user id的外鍵。 注意：表名沒有像官方文檔及很多人推薦的那樣使用復(fù)數(shù)形式，個(gè)人偏好而已，詳細(xì)討論請(qǐng)見StackOverflow的這個(gè)問題： Table NamingDilemma: Singular vs. Plural Names

from sqlalchemy import create_engine, MetaData,        Table, Column, Integer, String, ForeignKeyengine = create_engine('mysql+mysqldb://root:******@localhost/sa_test', echo=True)metadata = MetaData(engine)

user_table = Table('user', metadata,        Column('id', Integer, primary_key=True),        Column('name', String(50)),        Column('fullname', String(100))        )address_table = Table('address', metadata,        Column('id', Integer, primary_key=True),        Column('user_id', None, ForeignKey('user.id')),        Column('email', String(128), nullable=False)        )metadata.create_all()

執(zhí)行完metadata.create_all()這一句，兩張表就創(chuàng)建好了，可以在MySQL里立即查看。

MetaData.create all()可以多次調(diào)用，不會(huì)報(bào)錯(cuò)，它在內(nèi)部會(huì)檢查表是否已經(jīng)創(chuàng)建。 因?yàn)镸etaData創(chuàng)建時(shí)已經(jīng)綁定了引擎，所以此處create all()就不必再指定了，否則得寫成：

metadata.create_all(engine)

創(chuàng)建引擎時(shí)，echo參數(shù)為True，程序運(yùn)行時(shí)便有很多調(diào)試信息打印出來。在這些調(diào)試信息中，可以看到如下兩條MySQL的CREATE TABLE語句：

CREATE TABLE user (    id INTEGER NOT NULL AUTO_INCREMENT,    name VARCHAR(50),    fullname VARCHAR(100),    PRIMARY KEY (id))CREATE TABLE address (    id INTEGER NOT NULL AUTO_INCREMENT,    user_id INTEGER,    email VARCHAR(128) NOT NULL,    PRIMARY KEY (id),    FOREIGN KEY(user_id) REFERENCES user (id))

除了metadata.create_all()，Table自己也有create方法：

create(bind=None, checkfirst=False)

參數(shù)bind一般就是指引擎。 參數(shù)checkfirst表示是否檢查表已經(jīng)存在。為True時(shí)，若表已經(jīng)存在，不報(bào)錯(cuò)，只是什么也不做；為False時(shí)，若表已經(jīng)存在，則將引發(fā)異常。 使用這個(gè)方法來創(chuàng)建這兩張表：

user_table.create(checkfirst=True)address_table.create(checkfirst=True)

這里忽略了bind參數(shù)，因?yàn)閯?chuàng)建MetaData對(duì)象時(shí)已經(jīng)綁定了引擎，而創(chuàng)建表對(duì)象時(shí)又傳入了metadata，所以順藤摸瓜，表自己是知道引擎的。 如果調(diào)整一下表的創(chuàng)建順序，就會(huì)報(bào)錯(cuò)，因?yàn)閍ddress表里有一個(gè)user表的外鍵，而這時(shí)候user表還沒創(chuàng)建呢。所以，還是建議使用MetaData.create_all()吧，畢竟它也會(huì)檢查表是否已經(jīng)存在。

表的反射 Table Reflection

表創(chuàng)建好了，一般也就不動(dòng)了。所以實(shí)際應(yīng)用時(shí)，往往表都已經(jīng)存在，并不需要?jiǎng)?chuàng)建，只需把它們"導(dǎo)入"進(jìn)來即可，這時(shí)就得使用autoload參數(shù)。

from sqlalchemy import create_engine, MetaData, Tableengine = create_engine('mysql+mysqldb://root:******@localhost/sa_test', echo=False)metadata = MetaData(engine)user_table = Table('user', metadata, autoload=True)print 'user' in metadata.tablesprint [c.name for c in user_table.columns]address_table = Table('address', metadata, autoload=True)print 'address' in metadata.tables

輸出：

True['id', 'name', 'fullname']True

如果MetaData沒有綁定引擎，則另需指定autoload_with參數(shù)：

user_table = Table('user', metadata, autoload=True, autoload_with=engine)

如果被反射的表外鍵引用了另一個(gè)表，那么被引用的表也會(huì)一并被反射。比如只反射address表，user表也一并被反射了。

from sqlalchemy import create_engine, MetaData, Tableengine = create_engine('mysql+mysqldb://root:******@localhost/sa_test', echo=False)metadata = MetaData(engine)address_table = Table('address', metadata, autoload=True)print 'user' in metadata.tablesprint 'address' in metadata.tables

輸出：

TrueTrue

插入數(shù)據(jù)

插入數(shù)據(jù)之前，必須要有表對(duì)象，不管是新創(chuàng)建的，還是通過反射導(dǎo)入的。

Insert對(duì)象

要往表里插數(shù)據(jù)，先創(chuàng)建一個(gè)Insert對(duì)象：

ins = user_table.insert()print ins

打印這個(gè)Insert對(duì)象，可以看到它所對(duì)應(yīng)的SQL語句：

INSERT INTO user (id, name, fullname) VALUES (%s, %s, %s)

如果連接的數(shù)據(jù)庫不是MySQL而是SQLite，那輸出可能就是下面這樣：

INSERT INTO user (id, name, fullname) VALUES (?, ?, ?)

可見SQLAlchemy幫我們封裝了不同數(shù)據(jù)庫之間語法的差異。 如果MetaData創(chuàng)建時(shí)沒有綁定引擎，那么輸出會(huì)略有不同：

INSERT INTO "user" (id, name, fullname) VALUES (:id, :name, :fullname)

這時(shí)SQLAlchemy還不知道具體的數(shù)據(jù)庫語法，表名加了引號(hào)（"user"），列名也改用為:id之類一般性的格式。 此外，這條INSERT語句列出了user表里的每一列，而id在插入時(shí)一般是不需要指定的，可以通過Insert.values()方法加以限制：

ins = ins.values(name='adam', fullname='Adam Gu')print ins

限制后，id列已經(jīng)沒有了：

INSERT INTO user (name, fullname) VALUES (%s, %s)

可見values()方法限制了INSERT語句所包含的列。但是我們指定的name和fullname的值并沒有打印出來，這兩個(gè)值保存在Insert對(duì)象里，只有等到執(zhí)行時(shí)才會(huì)用到。

執(zhí)行

我們一直在說的引擎，可以理解成一個(gè)數(shù)據(jù)庫連接對(duì)象的倉庫，通過連接對(duì)象可以往數(shù)據(jù)庫發(fā)送具體的SQL語句。調(diào)用引擎的connect()方法可以獲取一個(gè)連接：

conn = engine.connect()

現(xiàn)在把前面的Insert對(duì)象丟給它來執(zhí)行：

result = conn.execute(ins)

由調(diào)試信息可見具體的INSERT語句：

INSERT INTO user (name, fullname) VALUES (%s, %s)('adam', 'Adam Gu')COMMIT

返回值result是一個(gè)ResultProxy對(duì)象，ResultProxy是對(duì)DB- API中cursor的封裝。插入語句的結(jié)果并不常用，但是查詢語句肯定是要用到它的。 不妨在MySQL里看一下剛插入的數(shù)據(jù)。

mysql> select * from user;+----+------+----------+| id | name | fullname |+----+------+----------+|  1 | adam | Adam Gu  |+----+------+----------+1 row in set (0.00 sec)

執(zhí)行多條語句

還記得前面的Insert對(duì)象使用values()方法來限制列嗎？

ins = ins.values(name='adam', fullname='Adam Gu')

這種方式其實(shí)不利于Insert對(duì)象的復(fù)用，更好的做法是把參數(shù)通過execute()方法傳進(jìn)去：

ins = user_table.insert()conn.execute(ins, name='adam', fullname='Adam Gu')

Insert對(duì)象本身還是會(huì)包含所有列，最終INSERT語句里的列由execute()的參數(shù)決定。由調(diào)試信息可見具體的INSERT語句：

INSERT INTO user (name, fullname) VALUES (%s, %s)('adam', 'Adam Gu')COMMIT

一次插入多條記錄也很簡單，只要傳一個(gè)字典列表（每個(gè)字典的鍵必須一致）給execute()即可。

conn.execute(address_table.insert(), [    { 'user_id': 1, 'email': 'sprinfall@gmail.com' },    { 'user_id': 1, 'email': 'sprinfall@hotmail.com' },    ])

調(diào)試信息里具體的INSERT語句：

INSERT INTO address (user_id, email) VALUES (%s, %s)((1, 'sprinfall@gmail.com'), (1, 'sprinfall@hotmail.com'))COMMIT

在MySQL里看一下插入的地址：

mysql> select * from address;+----+---------+-----------------------+| id | user_id | email                 |+----+---------+-----------------------+|  1 |       1 | sprinfall@gmail.com   ||  2 |       1 | sprinfall@hotmail.com |+----+---------+-----------------------+2 rows in set (0.00 sec)