ORM 江湖#####

曾幾何時(shí)被芳，程序員因?yàn)閼峙?code>SQL而在開(kāi)發(fā)的時(shí)候小心翼翼的寫著sql侵状，心中總是少不了恐慌诽俯，萬(wàn)一不小心sql語(yǔ)句出錯(cuò)仙粱，搞壞了數(shù)據(jù)庫(kù)怎么辦？又或者為了獲取一些數(shù)據(jù)，什么內(nèi)外左右連接，函數(shù)存儲(chǔ)過(guò)程等等拜轨。毫無(wú)疑問(wèn)，不搞懂這些恶复，怎么都覺(jué)得變扭腊满，說(shuō)不定某天就跳進(jìn)了坑里，叫天天不應(yīng)津肛，喊地地不答搪花。

ORM 的出現(xiàn)，讓畏懼SQL的開(kāi)發(fā)者咧擂，在坑里看見(jiàn)了爬出去的繩索，仿佛天空并不是那么黑暗设联，至少再暗耀盗，我們也有了眼睛。顧名思義，ORM 對(duì)象關(guān)系映射，簡(jiǎn)而言之，就是把數(shù)據(jù)庫(kù)的一個(gè)個(gè)table(表)，映射為編程語(yǔ)言的class(類)粗梭。

python中比較著名的ORM框架有很多，大名頂頂?shù)?SQLAlchemy 是python世界里當(dāng)仁不讓的ORM框架炕置。江湖中peewee口锭，strom， pyorm椰拒，SQLObject 各領(lǐng)風(fēng)騷到涂，可是最終還是SQLAlchemy 傲視群雄。

SQLAlchemy 簡(jiǎn)介#####

SQLAlchemy 分為兩個(gè)部分，一共用于 ORM 的對(duì)象映射护侮，另外一個(gè)是核心的 SQL expression 敌完。第一個(gè)很好理解，純粹的ORM羊初，后面這個(gè)不是 ORM滨溉，而是DBAPI的封裝，當(dāng)然也提供了很多方法长赞，避免了直接寫sql晦攒，而是通過(guò)一些sql表達(dá)式。使用 SQLAlchemy 則可以分為三種方式得哆。

• 使用 sql expression 脯颜，通過(guò) SQLAlchemy 的方法寫sql表達(dá)式，簡(jiǎn)介的寫sql
• 使用 raw sql贩据， 直接書寫 sql
• 使用 ORM 避開(kāi)直接書寫 sql

本文先探討 SQLAlchemy的 sql expresstion 部分的用法栋操。主要還是跟著官方的 SQL Expression Language Tutorial.介紹

為什么要學(xué)習(xí) sql expresstion ，而不直接上 ORM乐设？因?yàn)楹竺孢@個(gè)兩個(gè)是 orm 的基礎(chǔ)讼庇。并且绎巨，即是不使用orm近尚，后面這兩個(gè)也能很好的完成工作，并且代碼的可讀性更好场勤。純粹把SQLAlchemy當(dāng)成dbapi使用戈锻。首先SQLAlchemy 內(nèi)建數(shù)據(jù)庫(kù)連接池歼跟，解決了連接操作相關(guān)繁瑣的處理。其次格遭，提供方便的強(qiáng)大的log功能哈街，最后，復(fù)雜的查詢語(yǔ)句拒迅，依靠單純的ORM比較難實(shí)現(xiàn)骚秦。

實(shí)戰(zhàn)#####

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

首先需要導(dǎo)入 sqlalchemy 庫(kù)，然后建立數(shù)據(jù)庫(kù)連接璧微，這里使用 mysql作箍。通過(guò)create_engine方法進(jìn)行

from sqlalchemy import create_engine
engine = create_engine("mysql://root:@localhost:3306/webpy?charset=utf8",encoding="utf-8", echo=True)

create_engine 方法進(jìn)行數(shù)據(jù)庫(kù)連接，返回一個(gè) db 對(duì)象前硫。里面的參數(shù)表示

數(shù)據(jù)庫(kù)類型://用戶名:密碼（沒(méi)有密碼則為空胞得，不填）@數(shù)據(jù)庫(kù)主機(jī)地址/數(shù)據(jù)庫(kù)名?編碼
echo = True 是為了方便 控制臺(tái) logging 輸出一些sql信息，默認(rèn)是False

通過(guò)這個(gè)engine對(duì)象可以直接execute 進(jìn)行查詢屹电，例如 engine.execute("SELECT * FROM user") 也可以通過(guò) engine 獲取連接在查詢阶剑，例如 conn = engine.connect() 通過(guò) conn.execute()方法進(jìn)行查詢。兩者有什么差別呢危号？

• 直接使用engine的execute執(zhí)行sql的方式, 叫做connnectionless執(zhí)行,
• 借助 engine.connect()獲取conn, 然后通過(guò)conn執(zhí)行sql, 叫做connection執(zhí)行
  主要差別在于是否使用transaction模式, 如果不涉及transaction, 兩種方法效果是一樣的. 官網(wǎng)推薦使用后者牧愁。

定義表#####

定義數(shù)據(jù)表，才能進(jìn)行sql表達(dá)式的操作葱色，畢竟sql表達(dá)式的表的確定递宅，是sqlalchemy制定的，如果數(shù)據(jù)庫(kù)已經(jīng)存在了數(shù)據(jù)表還需要定義么苍狰？當(dāng)然办龄，這里其實(shí)是一個(gè)映射關(guān)系，如果不指定淋昭，查詢表達(dá)式就不知道是附加在那個(gè)表的操作俐填，當(dāng)然定義的時(shí)候，注意表名和字段名翔忽，代碼和數(shù)據(jù)的必須保持一致英融。定義好之后,就能創(chuàng)建數(shù)據(jù)表，一旦創(chuàng)建了歇式，再次運(yùn)行創(chuàng)建的代碼驶悟，數(shù)據(jù)庫(kù)是不會(huì)創(chuàng)建的。

# -*- coding: utf-8 -*-
__author__ = 'ghost'

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
# 連接數(shù)據(jù)庫(kù) 
engine = create_engine("mysql://root:@localhost:3306/webpy?charset=utf8",encoding="utf-8", echo=True)
# 獲取元數(shù)據(jù)
metadata = MetaData()
# 定義表
user = Table('user', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String(20)),
        Column('fullname', String(40)),
    )

address = Table('address', metadata,
        Column('id', Integer, primary_key=True),
        Column('user_id', None, ForeignKey('user.id')),
        Column('email', String(60), nullable=False)
    )
# 創(chuàng)建數(shù)據(jù)表材失，如果數(shù)據(jù)表存在痕鳍，則忽視
metadata.create_all(engine)
# 獲取數(shù)據(jù)庫(kù)連接
conn = engine.connect()

插入 insert

有了數(shù)據(jù)表和連接對(duì)象，對(duì)應(yīng)數(shù)據(jù)庫(kù)操作就簡(jiǎn)單了。

>>> i = user.insert()   # 使用查詢
>>> i  
<sqlalchemy.sql.dml.Insert object at 0x0000000002637748>
>>> print i  # 內(nèi)部構(gòu)件的sql語(yǔ)句
INSERT INTO "user" (id, name, fullname) VALUES (:id, :name, :fullname)
>>> u = dict(name='jack', fullname='jack Jone')
>>> r = conn.execute(i, **u)  # 執(zhí)行查詢笼呆，第一個(gè)為查詢對(duì)象熊响，第二個(gè)參數(shù)為一個(gè)插入數(shù)據(jù)字典，如果插入的是多個(gè)對(duì)象诗赌，就把對(duì)象字典放在列表里面
>>> r
<sqlalchemy.engine.result.ResultProxy object at 0x0000000002EF9390>
>>> r.inserted_primary_key  # 返回插入行 主鍵 id
[4L]
>>> addresses
[{'user_id': 1, 'email': 'jack@yahoo.com'}, {'user_id': 1, 'email': 'jack@msn.com'}, {'user_id': 2, 'email': 'www@www.org'}, {'user_id': 2, 'email': 'wendy@aol.com'}]
>>> i = address.insert()
>>> r = conn.execute(i, addresses)   # 插入多條記錄
>>> r
<sqlalchemy.engine.result.ResultProxy object at 0x0000000002EB5080>
>>> r.rowcount   #返回影響的行數(shù)
4L

>>> i = user.insert().values(name='tom', fullname='tom Jim')
>>> i.compile()
<sqlalchemy.sql.compiler.SQLCompiler object at 0x0000000002F6F390>
>>> print i.compile()
INSERT INTO "user" (name, fullname) VALUES (:name, :fullname)
>>> print i.compile().params
{'fullname': 'tom Jim', 'name': 'tom'}
>>> r = conn.execute(i)
>>> r.rowcount
1L

查詢 select

查詢方式很靈活汗茄，多數(shù)時(shí)候使用 sqlalchemy.sql 下面的 select方法

>>> s = select([user])  # 查詢 user表
>>> s
<sqlalchemy.sql.selectable.Select at 0x25a7748; Select object>
>>> print s
SELECT "user".id, "user".name, "user".fullname 
FROM "user"

如果需要查詢自定義的字段，可是使用 user 的cloumn 對(duì)象，例如
>>> user.c # 表 user 的字段column對(duì)象
<sqlalchemy.sql.base.ImmutableColumnCollection object at 0x0000000002E804A8>
>>> print user.c
['user.id', 'user.name', 'user.fullname']
>>> s = select([user.c.name,user.c.fullname])
>>> r = conn.execute(s)
>>> r
<sqlalchemy.engine.result.ResultProxy object at 0x00000000025A7748>
>>> r.rowcount # 影響的行數(shù)
5L
>>> ru = r.fetchall()
>>> ru
[(u'hello', u'hello world'), (u'Jack', u'Jack Jone'), (u'Jack', u'Jack Jone'), (u'jack', u'jack Jone'), (u'tom', u'tom Jim')]
>>> r
<sqlalchemy.engine.result.ResultProxy object at 0x00000000025A7748>
>>> r.closed # 只要 r.fetchall() 之后，就會(huì)自動(dòng)關(guān)閉 ResultProxy 對(duì)象
True

同時(shí)查詢兩個(gè)表
>>> s = select([user.c.name, address.c.user_id]).where(user.c.id==address.c.user_id) # 使用了字段和字段比較的條件
>>> s
<sqlalchemy.sql.selectable.Select at 0x2f03390; Select object>
>>> print s
SELECT "user".name, address.user_id
FROM "user", address
WHERE "user".id = address.user_id

操作符######

>>> print user.c.id == address.c.user_id  # 返回一個(gè)編譯的字符串
"user".id = address.user_id
>>> print user.c.id == 7
"user".id = :id_1   # 編譯成為帶參數(shù)的sql 語(yǔ)句片段字符串
>>> print user.c.id != 7
"user".id != :id_1
>>> print user.c.id > 7
"user".id > :id_1
>>> print user.c.id == None
"user".id IS NULL
>>> print user.c.id + address.c.id   # 使用兩個(gè)整形的變成 +
"user".id + address.id
>>> print user.c.name + address.c.email  # 使用兩個(gè)字符串 變成 ||
"user".name || address.email

操作連接######

這里的連接指條件查詢的時(shí)候堵幽，邏輯運(yùn)算符的連接，即 and or 和 not
>>> print and_(
user.c.name.like('j%'),
user.c.id == address.c.user_id,
or_(
address.c.email == 'wendy@aol.com',
address.c.email == 'jack@yahoo.com'
),
not_(user.c.id>5))
"user".name LIKE :name_1 AND "user".id = address.user_id AND (address.email = :email_1 OR address.email = :email_2) AND "user".id <= :id_1
>>>

得到的結(jié)果為 編譯的sql語(yǔ)句片段偶宫，下面看一個(gè)完整的例子

>>> se_sql = [(user.c.fullname +", " + address.c.email).label('title')]
>>> wh_sql = and_(
              user.c.id == address.c.user_id,
              user.c.name.between('m', 'z'),
              or_(
                  address.c.email.like('%@aol.com'),
                  address.c.email.like('%@msn.com')
              )
         )
>>> print wh_sql
"user".id = address.user_id AND "user".name BETWEEN :name_1 AND :name_2 AND (address.email LIKE :email_1 OR address.email LIKE :email_2)
>>> s = select(se_sql).where(wh_sql)
>>> print s
SELECT "user".fullname || :fullname_1 || address.email AS title 
FROM "user", address 
WHERE "user".id = address.user_id AND "user".name BETWEEN :name_1 AND :name_2 AND (address.email LIKE :email_1 OR address.email LIKE :email_2)
>>> r = conn.execute(s)
>>> r.fetchall()

使用 raw sql 方式

遇到負(fù)責(zé)的sql語(yǔ)句的時(shí)候，可以使用 sqlalchemy.sql 下面的 text 函數(shù)环鲤。將字符串的sql語(yǔ)句包裝編譯成為 execute執(zhí)行需要的sql對(duì)象纯趋。例如：、
>>> text_sql = "SELECT id, name, fullname FROM user WHERE id=:id" # 原始sql語(yǔ)句冷离，參數(shù)用（ ：value）表示
>>> s = text(text_sql)
>>> print s
SELECT id, name, fullname FROM user WHERE id=:id
>>> s
<sqlalchemy.sql.elements.TextClause object at 0x0000000002587668>
>>> conn.execute(s, id=3).fetchall() # id=3 傳遞：id參數(shù)
[(3L, u'Jack', u'Jack Jone')]

連接 join

連接有join 和 outejoin 兩個(gè)方法吵冒，join 有兩個(gè)參數(shù)，第一個(gè)是join 的表西剥，第二個(gè)是on 的條件痹栖，joing之后必須要配合select_from 方法
>>> print user.join(address)
"user" JOIN address ON "user".id = address.user_id # 因?yàn)殚_(kāi)啟了外鍵 ，所以join 能只能識(shí)別 on 條件
>>> print user.join(address, address.c.user_id==user.c.id) # 手動(dòng)指定 on 條件
"user" JOIN address ON address.user_id = "user".id

>>> s = select([user.c.name, address.c.email]).select_from(user.join(address, user.c.id==address.c.user_id))  # 被jion的sql語(yǔ)句需要用 select_from方法配合
>>> s
<sqlalchemy.sql.selectable.Select at 0x2eb63c8; Select object>
>>> print s
SELECT "user".name, address.email 
FROM "user" JOIN address ON "user".id = address.user_id
>>> conn.execute(s).fetchall()
[(u'hello', u'jack@yahoo.com'), (u'hello', u'jack@msn.com'), (u'hello', u'jack@yahoo.com'), (u'hello', u'jack@msn.com'), (u'Jack', u'www@www.org'), (u'Jack', u'wendy@aol.com'), (u'Jack', u'www@www.org'), (u'Jack', u'wendy@aol.com')]

更復(fù)雜的連接參考 官方的文檔了瞭空。

排序 分組 分頁(yè)

排序使用 order_by 方法揪阿，分組是 group_by ，分頁(yè)自然就是limit 和 offset兩個(gè)方法配合

>>> s = select([user.c.name]).order_by(user.c.name)  # order_by
>>> print s
SELECT "user".name 
FROM "user" ORDER BY "user".name
>>> s = select([user]).order_by(user.c.name.desc())
>>> print s
SELECT "user".id, "user".name, "user".fullname 
FROM "user" ORDER BY "user".name DESC
>>> s = select([user]).group_by(user.c.name)       # group_by
>>> print s
SELECT "user".id, "user".name, "user".fullname 
FROM "user" GROUP BY "user".name
>>> s = select([user]).order_by(user.c.name.desc()).limit(1).offset(3)  # limit(1).offset(3)
>>> print s
SELECT "user".id, "user".name, "user".fullname 
FROM "user" ORDER BY "user".name DESC
 LIMIT :param_1 OFFSET :param_2
[(4L, u'jack', u'jack Jone')]

更新 update

前面都是一些查詢咆畏，更新和插入的方法很像南捂，都是 表下面的方法，不同的是旧找，update 多了一個(gè) where 方法 用來(lái)選擇過(guò)濾
>>> s = user.update()
>>> print s
UPDATE "user" SET id=:id, name=:name, fullname=:fullname
>>> s = user.update().values(fullname=user.c.name) # values 指定了更新的字段
>>> print s
UPDATE "user" SET fullname="user".name
>>> s = user.update().where(user.c.name == 'jack').values(name='ed') # where 進(jìn)行選擇過(guò)濾
>>> print s
UPDATE "user" SET name=:name WHERE "user".name = :name_1
>>> r = conn.execute(s)
>>> print r.rowcount # 影響行數(shù)
3

還有一個(gè)高級(jí)用法溺健，就是一次命令執(zhí)行多個(gè)記錄的更新，需要用到 bindparam 方法

>>> s = user.update().where(user.c.name==bindparam('oldname')).values(name=bindparam('newname'))   # oldname 與下面的傳入的從拿書進(jìn)行綁定钮蛛，newname也一樣
>>> print s
UPDATE "user" SET name=:newname WHERE "user".name = :oldname
>>> u = [{'oldname':'hello', 'newname':'edd'},
{'oldname':'ed', 'newname':'mary'},
{'oldname':'tom', 'newname':'jake'}]
>>> r = conn.execute(s, u)
>>> r.rowcount
5L

刪除 delete

刪除比較容易鞭缭，調(diào)用 delete方法即可，不加 where 過(guò)濾魏颓，則刪除所有數(shù)據(jù)岭辣，但是不會(huì)drop掉表，等于清空了數(shù)據(jù)表
>>> r = conn.execute(address.delete()) # 清空表
>>> print r
<sqlalchemy.engine.result.ResultProxy object at 0x0000000002EAF550>
>>> r.rowcount
8L
>>> r = conn.execute(users.delete().where(users.c.name > 'm')) # 刪除記錄
>>> r.rowcount
3L

至此甸饱，sqlalchemy sql表達(dá)式的基本用法介紹完畢沦童，更深入的閱讀可以查看官方的api SQL Statements and Expressions API