1. 基本流程

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column
from sqlalchemy.types import String, Integer
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(`postgresql://test@localhost:5432/test`)
DBSession = sessionmaker(engine)
session = DBSession()

BaseModel = declarative_base()

class User(BaseModel):
    __tablename__ = `user`

    id = Column(String, primary_key=True)
    username = Column(String, index=True)

class Session(BaseModel):
    __tablename__ = `session`

    id = Column(String, primary_key=True)
    user = Column(String, index=True)
    ip = Column(String)

query = session.query(Session, User.username).join(User, User.id == Session.user)
for i in query:
    print dir(i)

2. 建立連線

SQLAlchemy 的連線建立是 Lazy 的方式, 即在需要使用時才會去真正建立. 之前做的工作, 全是”定義”.

連線的定義是在 engine 中做的.

2.1. Engine

engine 的定義包含了三部分的內容, 一是具體資料庫型別的實現, 二是連線池, 三是策略(即engine 自己的實現).

所謂的資料庫型別即是 MYSQL , Postgresql , SQLite 這些不同的資料庫.

一般建立 engine 是使用 create_engine 方法:

engine = create_engine(`postgresql+psycopg2://scott:tiger@localhost/mydatabase`)

引數字串的各部分的意義:

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

對於這個字串, SQLAlchemy 提供了工具可用於處理它:

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.engine.url import make_url
from sqlalchemy.engine.url import URL

s = `postgresql://test@localhost:5432/bbcustom`
url = make_url(s)
s = URL(drivername=`postgresql`, username=`test`, password="",
        host="localhost", port=5432, database="bbcustom")

engine = create_engine(url)
engine = create_engine(s)

print engine.execute(`select id from "user"`).fetchall()

create_engine 函式有很多的控制引數, 這個後面再詳細說.

2.2. Engine的策略

create_engine 的呼叫, 實際上會變成 strategy.create 的呼叫. 而 strategy 就是 engine 的實現細節. strategy 可以在 create_engine 呼叫時通過 strategy 引數指定, 目前官方的支援有三種:

plain, 預設的
threadlocal, 連線是執行緒區域性的
mock, 所有的 SQL 語句的執行會使用指定的函式

mock 這個實現, 會把所有的 SQL 語句的執行交給指定的函式來做, 這個函式是由 create_engine的 executor 引數指定:

def f(sql, *args, **kargs):
    print sql, args, kargs

s = `postgresql://test@localhost:5432/bbcustom`
engine = create_engine(s, strategy=`mock`, executor=f)

print engine.execute(`select id from "user"`)

2.3. 各資料庫實現

各資料庫的實現在 SQLAlchemy 中分成了兩個部分, 一是資料庫的型別, 二是具體資料庫中適配的客戶端實現. 比如對於 Postgresql 的訪問, 可以使用 psycopg2 , 也可以使用 pg8000 :

s = `postgresql+psycopg2://test@localhost:5432/bbcustom`
s = `postgresql+pg8000://test@localhost:5432/bbcustom`
engine = create_engine(s)

具體的適配工作, 是需要在程式碼中實現一個 Dialect 類來完成的. 官方的實現在 dialects 目錄下.

獲取具體的 Dialect 的行為, 則是前面提到的 URL 物件的 get_dialect 方法. create_engine 時你單傳一個字串, SQLAlchemy 自己也會使用 make_url 得到一個 URL 的例項).

2.4. 連線池

SQLAlchemy 支援連線池, 在 create_engine 時新增相關引數即可使用.

pool_size 連線數
max_overflow 最多多幾個連線
pool_recycle 連線重置週期
pool_timeout 連線超時時間

連線池效果:

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.engine.url import make_url
from sqlalchemy.engine.url import URL

s = `postgresql://test@localhost:5432/bbcustom`
engine = create_engine(s, pool_size=2, max_overflow=0)


from threading import Thread

def f():
    print engine.execute(`select pg_sleep(5)`).fetchall()


p = []
for i in range(3):
    p.append(Thread(target=f))

for t in p:
    t.start()

連線池的實現, 在 create_engine 呼叫時也可以指定:

from sqlalchemy.pool import QueuePool
engine = create_engine(`sqlite:///file.db`, poolclass=QueuePool)

還有:

from sqlalchemy.pool import NullPool
engine = create_engine(
         `postgresql+psycopg2://scott:tiger@localhost/test`,
          poolclass=NullPool)

或者僅僅是獲取連線的方法:

import sqlalchemy.pool as pool
import psycopg2

def getconn():
    c = psycopg2.connect(username=`ed`, host=`127.0.0.1`, dbname=`test`)
    # do things with `c` to set up
    return c

engine = create_engine(`postgresql+psycopg2://`, creator=getconn)

連線池可以被單獨使用:

import sqlalchemy.pool as pool
import psycopg2

def getconn():
    c = psycopg2.connect(username=`ed`, host=`127.0.0.1`, dbname=`test`)
    return c

mypool = pool.QueuePool(getconn, max_overflow=10, pool_size=5)

conn = mypool.connect()
cursor = conn.cursor()
cursor.execute("select foo")

連線池可以被多個 engine 共享使用:

e = create_engine(`postgresql://`, pool=mypool)

3. 模型使用

3.1. 模型定義

對於 Table 的定義, 本來是直接的例項化呼叫, 通過 declarative 的包裝, 可以像”定義類”這樣的更直觀的方式來完成.

user = Table(`user`, metadata,
    Column(`user_id`, Integer, primary_key = True),
    Column(`user_name`, String(16), nullable = False),
    Column(`email_address`, String(60)),
    Column(`password`, String(20), nullable = False)
)

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy.types import String, Integer, CHAR, BIGINT
from sqlalchemy.ext.declarative import declarative_base
BaseModel = declarative_base()
Engine = create_engine(`postgresql://test@localhost:5432/test`, echo=True)


class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(CHAR(32), primary_key=True)
    title = Column(String(64), server_default=``, nullable=False)
    text = Column(String, server_default=``, nullable=False)
    user = Column(CHAR(32), index=True, server_default=``, nullable=False)
    create = Column(BIGINT, index=True, server_default=`0`, nullable=False)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(CHAR(32), primary_key=True)
    name = Column(String(32), server_default=``, nullable=False)
    username = Column(String(32), index=True, server_default=``, nullable=False)
    password = Column(String(64), server_default=``, nullable=False)


def init_db():
    BaseModel.metadata.create_all(Engine)

def drop_db():
    BaseModel.metadata.drop_all(Engine)


if __name__ == `__main__`:
    #init_db()
    drop_db()
    #BaseModel.metadata.tables[`user`].create(Engine, checkfirst=True)
    #BaseModel.metadata.tables[`user`].drop(Engine, checkfirst=False)
    pass

3.2. 建立

session = Session()
session.add(User(id=uuid.uuid4().hex))
session.add(Blog(id=uuid.uuid4().hex))
session.add_all([
    User(id=uuid.uuid4().hex),
    Blog(id=uuid.uuid4().hex)
])
session.commit()

執行的順序並不一定會和程式碼順序一致, SQLAlchemy 自己會整合邏輯再執行.

3.3. 查詢

SQLAlchemy 實現的查詢非常強大, 寫起來有一種隨心所欲的感覺.

查詢的結果, 有幾種不同的型別, 這個需要注意, 像是:

instance
instance of list
keyed tuple of list
value of list

基本查詢

session.query(User).filter_by(username=`abc`).all()
session.query(User).filter(User.username==`abc`).all()
session.query(Blog).filter(Blog.create >= 0).all()
session.query(Blog).filter(Blog.create >= 0).first()
session.query(Blog).filter(Blog.create >= 0 | Blog.title == `A`).first()
session.query(Blog).filter(Blog.create >= 0 & Blog.title == `A`).first()
session.query(Blog).filter(Blog.create >= 0).offset(1).limit(1).scalar()
session.query(User).filter(User.username ==  `abc`).scalar()
session.query(User.id).filter(User.username ==  `abc`).scalar()
session.query(Blog.id).filter(Blog.create >= 0).all()
session.query(Blog.id).filter(Blog.create >= 0).all()[0].id
dict(session.query(Blog.id, Blog.title).filter(Blog.create >= 0).all())
session.query(Blog.id, Blog.title).filter(Blog.create >= 0).first().title
session.query(User.id).order_by(`id desc`).all()
session.query(User.id).order_by(`id`).first()
session.query(User.id).order_by(User.id).first()
session.query(User.id).order_by(-User.id).first()
session.query(`id`, `username`).select_from(User).all()
session.query(User).get(`16e19a64d5874c308421e1a835b01c69`)

多表查詢

session.query(Blog, User).filter(Blog.user == User.id).first().User.username
session.query(Blog, User.id, User.username).filter(Blog.user == User.id).first().id
session.query(Blog.id,
              User.id,
              User.username).filter(Blog.user == User.id).first().keys()

條件查詢

from sqlalchemy import or_, not_

session.query(User).filter(or_(User.id == ``,
                               User.id == `16e19a64d5874c308421e1a835b01c69`)).all()
session.query(User).filter(not_(User.id == `16e19a64d5874c308421e1a835b01c69`)).all()
session.query(User).filter(User.id.in_([`16e19a64d5874c308421e1a835b01c69`])).all()
session.query(User).filter(User.id.like(`16e19a%`)).all()
session.query(User).filter(User.id.startswith(`16e19a`)).all()
dir(User.id)

函式

from sqlalchemy import func
session.query(func.count(`1`)).select_from(User).scalar()
session.query(func.count(`1`), func.max(User.username)).select_from(User).first()
session.query(func.count(`1`)).select_from(User).scalar()
session.query(func.md5(User.username)).select_from(User).all()
session.query(func.current_timestamp()).scalar()
session.query(User).count()

3.4. 修改

還是通常的兩種方式:

session.query(User).filter(User.username == `abc`).update({`name`: `123`})
session.commit()

user = session.query(User).filter_by(username=`abc`).scalar()
user.name = `223`
session.commit()

如果涉及對屬性原值的引用, 則要考慮 synchronize_session 這個引數.

`evaluate` 預設值, 會同時修改當前 session 中的物件屬性.
`fetch` 修改前, 會先通過 select 查詢條目的值.
False 不修改當前 session 中的物件屬性.

在預設情況下, 因為會有修改當前會話中的物件屬性, 所以如果語句中有 SQL 函式, 或者”原值引用”, 那是無法完成的操作, 自然也會報錯, 比如:

from sqlalchemy import func
session.query(User).update({User.name: func.trim(`123 `)})
session.query(User).update({User.name: User.name + `x`})

這種情況下, 就不能要求 SQLAlchemy 修改當前 session 的物件屬性了, 而是直接進行資料庫的互動, 不管當前會話值:

session.query(User).update({User.name: User.name + `x`}, synchronize_session=False)

是否修改當前會話的物件屬性, 涉及到當前會話的狀態. 如果當前會話過期, 那麼在獲取相關物件的屬性值時, SQLAlchemy 會自動作一次資料庫查詢, 以便獲取正確的值:

user = session.query(User).filter_by(username=`abc`).scalar()
print user.name
session.query(User).update({User.name: `new`}, synchronize_session=False)
print user.name
session.commit()
print user.name

執行了 update 之後, 雖然相關物件的實際的屬性值已變更, 但是當前會話中的物件屬性值並沒有改變. 直到 session.commit() 之後, 當前會話變成”過期”狀態, 再次獲取 user.name 時, SQLAlchemy 通過 user 的 id 屬性, 重新去資料庫查詢了新值. (如果 user 的 id 變了呢? 那就會出事了啊.)

synchronize_session 設定成 `fetch` 不會有這樣的問題, 因為在做 update 時已經修改了當前會話中的物件了.

不管 synchronize_session 的行為如何, commit 之後 session 都會過期, 再次獲取相關物件值時, 都會重新作一次查詢.

3.5. 刪除

session.query(User).filter_by(username=`abc`).delete()

user = session.query(User).filter_by(username=`abc`).first()
session.delete(user)

刪除同樣有像修改一樣的 synchronize_session 引數的問題, 影響當前會話的狀態.

3.6. JOIN

SQLAlchemy 可以很直觀地作 join 的支援:

r = session.query(Blog, User).join(User, Blog.user == User.id).all()
for blog, user in r:
    print blog.id, blog.user, user.id


r = session.query(Blog, User.name, User.username).join(User, Blog.user == User.id).all()
print r

4. 外來鍵和關係定義

4.1. 外來鍵約束

使用 ForeignKey 來定義一個外來鍵約定:

from sqlalchemy import Column, ForeignKey
from sqlalchemy.types import String, Integer, CHAR, BIGINT

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)
    text = Column(String, server_default=``, nullable=False)
    user = Column(BIGINT, ForeignKey(`user.id`), index=True, nullable=False)
    create = Column(BIGINT, index=True, server_default=`0`, nullable=False)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)
    username = Column(String(32), index=True, server_default=``, nullable=True)
    password = Column(String(64), server_default=``, nullable=False)

建立時:

session = Session()
user = User(name=`first`, username=u`新的`)
session.add(user)
session.flush()
blog = Blog(title=u`第一個`, user=user.id)
session.add(blog)
session.commit()

session.flush() 是進行資料庫互動, 但是事務並沒有提交. 進行資料庫互動之後, user.id 才有值.

定義了外來鍵, 對查詢來說, 並沒有影響. 外來鍵只是單純的一條約束而已. 當然, 可以在外來鍵上定義一些關聯的事件操作, 比如當外來鍵條目被刪除時, 欄位置成 null , 或者關聯條目也被刪除等.

4.2. 關係定義

要定義關係, 必有使用 ForeignKey 約束. 當然, 這裡說的只是在定義模型時必有要有, 至於資料庫中是否真有外來鍵約定, 這並不重要.

from sqlalchemy import Column, ForeignKey
from sqlalchemy.types import String, Integer, CHAR, BIGINT
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship


class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)
    user = Column(BIGINT, ForeignKey(`user.id`), index=True, nullable=False)
    create = Column(BIGINT, index=True, server_default=`0`, nullable=False)

    user_obj = relationship(`User`)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

    blog_list = relationship(`Blog`, order_by=`Blog.create`)

關係只是 SQLAlchemy 提供的工具, 與資料庫無關, 所以任何時候新增都是可以的.

上面的 User-Blog 是一個”一對多”關係, 通過 Blog 的 user 這個 ForeignKey , SQLAlchemy 可以自動處理關係的定義. 在查詢時, 返回的結果自然也是, 一個是列表, 一個是單個物件:

session = Session()
print session.query(Blog).get(1).user_obj
print session.query(User).get(1).blog_list

這種關係的定義, 並不影響查詢並獲取物件的行為, 不會新增額外的 join 操作. 在物件上取一個user_obj 或者取 blog_list 都是發生了一個新的查詢操作.

上面的關係定義, 對應的屬性是實際查詢出的例項列表, 當條目數多的時候, 這樣可能會有問題. 比如使用者名稱下有成千上萬的文章, 一次全取出就太暴力了. 關係對應的屬性可以定義成一個 Query :

class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

    blog_list = relationship(`Blog`, order_by=`Blog.create`, lazy="dynamic")

這樣在獲取例項時就可以自由控制了:

session.query(User).get(1).blog_list.all()
session.query(User).get(1).blog_list.filter(Blog.title == `abc`).first()

4.3. 關係的查詢

關係定義之後, 除了在查詢時會有自動關聯的效果, 在作查詢時, 也可以對定義的關係做操作:

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(Integer, autoincrement=True, primary_key=True)
    title = Column(Unicode(32), server_default=``)
    user = Column(Integer, ForeignKey(`user.id`), index=True)

    user_obj = relationship(`User`)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(32), server_default=``)

    blogs = relationship(`Blog`)

對於一對多的關係, 使用 any() 函式查詢:

user = session.query(User).filter(User.blogs.any(Blog.title == u`A`)).first()

SQLAlchemy 會使用 exists 條件, 類似於:

SELECT *
FROM user
WHERE EXISTS
    (SELECT 1
     FROM blog
     WHERE user.id = blog.user AND blog.title = ?)
 LIMIT ? OFFSET ?

反之, 如果是多對一的關係, 則使用 has() 函式查詢:

blog = session.query(Blog).filter(Blog.user_obj.has(User.name == u`XX`)).first()

最後的 SQL 語句都是一樣的.

4.4. 關係的獲取形式

前面介紹的關係定義中, 提到了兩種關係的獲取形式, 一種是:

user_obj = relationship(`User`)

這種是在物件上獲取關係物件時, 再去查詢.

另一種是:

blog_list = relationship(`Blog`, lazy="dynamic")

這種的結果, 是在物件上獲取關係物件時, 只返回 Query , 而查詢的細節由人為來控制.

總的來說, 關係的獲取分成兩種, Lazy 或 Eager . 在直接查詢層面, 上面兩種都屬於 Lazy 的方式, 而 Eager 的一種, 就是在獲取物件時的查詢語句, 是直接帶 join 的, 這樣關係物件的資料在一個查詢語句中就直接獲取到了:

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)
    user = Column(BIGINT, ForeignKey(`user.id`), index=True, nullable=False)

    user_obj = relationship(`User`, lazy=`joined`, cascade=`all`)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

這樣在查詢時:

blog = session.query(Blog).first()
print blog.user_obj

便會多出 LEFT OUTER JOIN 的語句, 結果中直接獲取到對應的 User 例項物件.

也可以把 joined 換成子查詢, subquery :

class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

    blog_list = relationship(`Blog`, cascade=`all`, lazy=`subquery`)


if __name__ == `__main__`:

    session = Session()
    user = session.query(User).first()
    session.commit()

子查詢會用到臨時表.

上面定義的:

blog_list = relationship(`Blog`, lazy="dynamic")
user_obj = relationship(`User`, lazy=`joined`)
blog_list = relationship(`Blog`, lazy=`subquery`)

都算是一種預設方式. 在具體使用查詢時, 還可以通過 options() 方法定義關聯的獲取方式:

from sqlalchemy.orm import lazyload, joinedload, subqueryload
user = session.query(User).options(lazyload(`blog_list`)).first()
print user.blog_list

更多的用法:

session.query(Parent).options(
    joinedload(`foo`).joinedload(`bar`).joinedload(`bat`)
    ).all()

session.query(A).options(
    defaultload("atob").joinedload("btoc")
    ).all()

session.query(MyClass).options(lazyload(`*`))

session.query(MyClass).options(
    lazyload(`*`), joinedload(MyClass.widget)
    )

session.query(User, Address).options(Load(Address).lazyload(`*`))

如果關聯的定義之前是 Lazy 的, 但是實際使用中, 希望在手工 join 之後, 把關聯物件直接包含進結果例項, 可以使用 contains_eager() 來包裝一下:

from sqlalchemy.orm import contains_eager

blog = session.query(Blog).join(Blog.user_obj)
              .options(contains_eager(Blog.user_obj)).first()
print blog.user_obj

4.5. 關係的表現形式

關係在物件屬性中的表現, 預設是列表, 但是, 這不是唯一的形式. 根據需要, 可以作成 dictionary , set 或者其它你需要的物件.

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(Integer, autoincrement=True, primary_key=True)
    title = Column(Unicode(32), server_default=``)
    user = Column(Integer, ForeignKey(`user.id`), index=True)

    user_obj = relationship(`User`)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(32), server_default=``)

    blogs = relationship(`Blog`)

對於上面的兩個模型:

user = session.query(User).first()
print user.blogs

現在 user.blogs 是一個列表. 我們可以在 relationship() 呼叫時通過 collection_class 引數指定一個類, 來重新定義關係的表現形式:

user = User(name=u`XX`)
session.add_all([Blog(title=u`A`, user_obj=user), Blog(title=u`B`, user_obj=user)])
session.commit()

user = session.query(User).first()
print user.blogs

set , 集合:

blogs = relationship(`Blog`, collection_class=set)

#InstrumentedSet([<__main__.Blog object at 0x1a58710>, <__main__.Blog object at 0x1a587d0>])

attribute_mapped_collection , 字典, 鍵值從屬性取:

from sqlalchemy.orm.collections import attribute_mapped_collection

blogs = relationship(`Blog`, collection_class=attribute_mapped_collection(`title`))

#{u`A`: <__main__.Blog object at 0x20ed810>, u`B`: <__main__.Blog object at 0x20ed8d0>}

如果 title 重複的話, 結果會覆蓋.

mapped_collection , 字典, 鍵值自定義:

from sqlalchemy.orm.collections import mapped_collection

blogs = relationship(`Blog`, collection_class=mapped_collection(lambda blog: blog.title.lower()))

#{u`a`: <__main__.Blog object at 0x1de4890>, u`b`: <__main__.Blog object at 0x1de4950>}

4.6. 多對多關係

先考慮典型的多對多關係結構:

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)

    tag_list = relationship(`Tag`)
    tag_list = relationship(`BlogAndTag`)


class Tag(BaseModel):
    __tablename__ = `tag`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(16), server_default=``, nullable=False)


class BlogAndTag(BaseModel):
    __tablename__ = `blog_and_tag`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    blog = Column(BIGINT, ForeignKey(`blog.id`), index=True)
    tag = Column(BIGINT, ForeignKey(`tag.id`), index=True)
    create = Column(BIGINT, index=True, server_default=`0`)

在 Blog 中的:

tag_list = relationship(`Tag`)

顯示是錯誤的, 因為在 Tag 中並沒有外來鍵. 而:

tag_list = relationship(`BlogAndTag`)

這樣雖然正確, 但是 tag_list 的關係只是到達 BlogAndTag 這一層, 並沒有到達我們需要的 Tag .

這種情況下, 一個多對多關係是有三張表來表示的, 在定義 relationship 時, 就需要一個secondary 引數來指明關係表:

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)

    tag_list = relationship(`Tag`, secondary=lambda: BlogAndTag.__table__)


class Tag(BaseModel):
    __tablename__ = `tag`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(16), server_default=``, nullable=False)


class BlogAndTag(BaseModel):
    __tablename__ = `blog_and_tag`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    blog = Column(BIGINT, ForeignKey(`blog.id`), index=True)
    tag = Column(BIGINT, ForeignKey(`tag.id`), index=True)
    create = Column(BIGINT, index=True, server_default=`0`)

這樣, 在操作時可以直接獲取到對應的例項列表:

blog = session.query(Blog).filter(Blog.title == `a`).one()
print blog.tag_list

訪問 tag_list 時, SQLAlchemy 做的是一個普通的多表查詢.

tag_list 屬性同時支援賦值操作:

session = Session()
blog = session.query(Blog).filter(Blog.title == `a`).one()
blog.tag_list = [Tag(name=`t1`)]
session.commit()

提交時, SQLAlchemy 總是會建立 Tag , 及對應的關係 BlogAndTag .

而如果是:

session = Session()
blog = session.query(Blog).filter(Blog.title == `a`).one()
blog.tag_list = []
session.commit()

tag = session.query(Tag).filter(Tag.name == `x`).one()
blog.tag_list.remove(tag)
session.commit()

那麼 SQLAlchemy 只會刪除對應的關係 BlogAndTag , 不會刪除實體 Tag .

如果你直接刪除實體, 那麼對應的關係是不會自動刪除的:

session = Session()
blog = session.query(Blog).filter(Blog.title == `a`).one()
tag = Tag(name=`ok`)
blog.tag_list = [tag]
session.commit()

tag = session.query(Tag).filter(Tag.name == `ok`).one()
session.delete(tag)
session.commit()

4.7. Cascades 自動關係處理

前面提到的, 當操作關係, 實體時, 與其相關聯的關係, 實體是否會被自動處理的問題, 在 SQLAlchemy 中是通過 Cascades 機制來定義和解決的. ( Cascades 這個詞是來源於Hibernate .)

cascade 是一個 relationship 的引數, 其值是逗號分割的多個字串, 以表示不同的行為. 預設值是 ” save-update, merge” , 稍後會介紹每個詞項的作用.

這裡的所有規則介紹, 只涉及從 Parent 到 Child , Parent 即定義 relationship的類. 不涉及backref .

cascade 所有的可選字串項是:

all , 所有操作都會自動處理到關聯物件上.
save-update , 關聯物件自動新增到會話.
delete , 關聯物件自動從會話中刪除.
delete-orphan , 屬性中去掉關聯物件, 則會話中會自動刪除關聯物件.
merge , session.merge() 時會處理關聯物件.
refresh-expire , session.expire() 時會處理關聯物件.
expunge , session.expunge() 時會處理關聯物件.

save-update

當一個物件被新增進 session 後, 此物件標記為 save-update 的 relationship 關係物件也會同時新增進這個 session .

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)
    user = Column(BIGINT, ForeignKey(`user.id`), index=True, nullable=False)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

    blog_list = relationship(`Blog`, cascade=``)
    blog_list_auto = relationship(`Blog`, cascade=`save-update`)


if __name__ == `__main__`:

    session = Session()

    user = User(name=u`哈哈`)
    blog = Blog(title=u`第一個`)
    user.blog_list = [blog]
    #user.blog_list_auto = [blog]
    session.add(user)
    print blog in session
    session.commit()

delete

當一個物件在 session 中被標記為刪除時, 其屬性中 relationship 關聯的物件也會被標記成刪除, 否則, 關聯物件中的對應外來鍵欄位會被改成 NULL , 不能為 NULL 則報錯.

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)
    user = Column(BIGINT, ForeignKey(`user.id`), index=True, nullable=False)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

    blog_list = relationship(`Blog`, cascade=`save-update, delete`)


if __name__ == `__main__`:
    session = Session()

    #user = User(name=u`使用者`)
    #user.blog_list = [Blog(title=u`哈哈`)]
    #session.add(user)
    user = session.query(User).first()
    session.delete(user)
    session.commit()

delete-orphan

當 relationship 屬性變化時, 被 “去掉” 的物件會被自動刪除. 比如之前是:

user.blog_list = [blog, blog2]

現在變成:

user.blog_list = [blog2]

那麼 blog 這個關聯實體是會自動刪除的.

這各機制只適用於 “一對多” 的關係中, “多對多” 和反過來的 “多對一” 都不適用. 在relationship 定義時, 可以新增 single_parent = True 引數來強制約束. 當然, 在實現上 SQLAlchemy 是會先查出所有關聯實體, 然後計算差集確認哪些需要被刪除.

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)
    user = Column(BIGINT, ForeignKey(`user.id`), index=True, nullable=False)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

    blog_list = relationship(`Blog`, cascade=`save-update, delete-orphan`)


if __name__ == `__main__`:

    session = Session()

    #user = User(name=u`使用者`)
    #blog = Blog(title=u`一`)
    #blog2 = Blog(title=u`二`)
    #user.blog_list = [blog, blog2]
    #session.add(user)
    user = session.query(User).first()
    blog2 =  session.query(Blog).filter(Blog.title == u`二`).first()
    user.blog_list = [blog2]
    #session.delete(user)
    session.commit()

merge

這個選項是標識在 session.merge() 時處理關聯物件. session.merge() 的作用, 是把一個會話外的例項, “整合”進會話, 比如 “有則修改, 無則建立” 就是典型的一種 “整合”:

user = User(id=1, name="1")
session.add(user)
session.commit()

user = User(id=1)
user = session.merge(user)
print user.name

user = User(id=1, name="2")
user = session.merge(user)
session.commit()

cascade 中的 merge 作用:

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)
    user = Column(BIGINT, ForeignKey(`user.id`), index=True, nullable=False)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

    blog_list = relationship(`Blog`,
                             cascade=`save-update, delete, delete-orphan, merge`)


if __name__ == `__main__`:

    session = Session()

    user = User(id=1, name=`1`)
    session.add(user)
    session.commit(user)

    user = User(id=1, blog_list=[Blog(title=`哈哈`)])
    session.merge(user)

    session.commit()

refresh-expire

當使用 session.expire() 標識一個物件過期時, 此物件的關聯物件是否也被標識為過期(訪問屬性會重新查詢資料庫).

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)
    user = Column(BIGINT, ForeignKey(`user.id`), index=True, nullable=False)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

    blog_list = relationship(`Blog`,
            cascade=`save-update, delete, delete-orphan, merge, refresh-expire`)


if __name__ == `__main__`:

    session = Session()

    #user = User(id=1, name=`1`)
    #blog = Blog(title="abc")
    #user.blog_list = [blog]
    #session.add(user)

    user = session.query(User).first()
    blog = user.blog_list[0]
    print user.name
    print blog.title
    session.expire(user)
    print `EXPIRE`
    print user.name
    print blog.title

    session.commit()

expunge

與 merge 相反, 當 session.expunge() 把物件從會話中去除的時候, 此物件的關聯物件也同時從會話中消失.

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    title = Column(String(64), server_default=``, nullable=False)
    user = Column(BIGINT, ForeignKey(`user.id`), index=True, nullable=False)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(32), server_default=``, nullable=False)

    blog_list = relationship(`Blog`, cascade=`delete, delete-orphan, expunge`)


if __name__ == `__main__`:

    session = Session()
    user = User(name=u`使用者`)
    blog = Blog(title=u`第一個`)
    user.blog_list = [blog]

    session.add(user)
    session.add(blog)

    session.expunge(user)
    print blog in session

    #session.commit()

4.8. 屬性代理

考慮這樣的情況, 關係是關聯的整個模型物件的, 但是, 有時我們對於這個關係, 並不關心整個物件, 只關心其中的某個屬性. 考慮下面的場景:

from sqlalchemy.ext.associationproxy import association_proxy

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(Integer, autoincrement=True, primary_key=True)
    title = Column(Unicode(32), nullable=False, server_default=``)
    user = Column(Integer, ForeignKey(`user.id`), index=True)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(32), nullable=False, server_default=``)

    blog_list = relationship(`Blog`)
    blog_title_list = association_proxy(`blog_list`, `title`)

blog_list 是一個正確的一對多關係. 下面的 blog_title_list 就是這個關係上的一個屬性代理.blog_title_list 只處理 blog_list 這個關係中對應的物件的 title 屬性, 包括獲取和設定兩個方向.

session = Session()

user = User(name=`xxx`)
user.blog_list = [Blog(title=`ABC`)]
session.add(user)
session.commit()

user = session.query(User).first()
print user.blog_title_list

上面是獲取屬性的示例. 在”設定”, 或者說”建立”時, 直接操作是有錯的:

user = session.query(User).first()
user.blog_title_list = [`NEW`]
session.add(user)
session.commit()

原因在於, 對於類 Blog 的初始化形式. association_proxy(`blog_list`, `title`) 中的 title 只是獲取時的屬性定義, 而在上面的設定過程中, 實際上的呼叫形式為:

Blog(`NEW`)

Blog 類沒有明確定義 __init__() 方法, 所有這種形式的呼叫會報錯. 可以把 __init__() 方法補上:

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(Integer, autoincrement=True, primary_key=True)
    title = Column(Unicode(32), nullable=False, server_default=``)
    user = Column(Integer, ForeignKey(`user.id`), index=True)

    def __init__(self, title):
        self.title = title

這樣呼叫就沒有問題了.

另一個方法, 是在呼叫 association_proxy() 時使用 creator 引數明確定義”值”和”例項”的關係:

class User(BaseModel):
    __tablename__ = `user`

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(32), nullable=False, server_default=``)

    blog_list = relationship(`Blog`)
    blog_title_list = association_proxy(`blog_list`, `title`,
                                        creator=lambda t: User(title=t))

creator 定義的方法, 返回的物件可以被對應的 blog_list 關係接收即可.

在查詢方面, 多對一的關係代理上, 可以直接使用屬性:

class Blog(BaseModel):
    __tablename__ = `blog`

    id = Column(Integer, autoincrement=True, primary_key=True)
    title = Column(Unicode(32), server_default=``)
    user = Column(Integer, ForeignKey(`user.id`), index=True)

    user_obj = relationship(`User`)
    user_name = association_proxy(`user_obj`, `name`)

查詢:

blog = session.query(Blog).filter(Blog.user_name == u`XX`).first()

反過來的一對多關係代理上, 可以使用 contains() 函式:

user = session.query(User).filter(User.blogs_title.contains(`A`)).first()

5. 會話與事務控制

5.1. 基本使用

SQLAlchemy 的 session 是用於管理資料庫操作的一個像容器一樣的東西. 模型例項物件本身獨立存在, 而要讓其修改(建立)生效, 則需要把它們加入某個 session . 同時你也可以把模型例項物件從 session 中去除. 被 session 管理的例項物件, 在 session.commit() 時被提交到資料庫. 同時 session.rollback() 是回滾變更.

session.flush() 的作用是在事務管理內與資料庫發生互動, 對應的例項狀態被反映到資料庫. 比如自增 ID 被填充上值.

user = User(name=u`名字`)
session.add(user)
session.commit()

try:
    user = session.Query(User).first()
    user.name = u`改名字
    session.commit()
except:
    session.rollback()

5.2. for update

SQLAlchemy 的 Query 支援 select ... for update / share .

session.Query(User).with_for_update().first()
session.Query(User).with_for_update(read=True).first()

完整形式是:

with_for_update(read=False, nowait=False, of=None)

read: 是標識加互斥鎖還是共享鎖. 當為 True 時, 即 for share 的語句, 是共享鎖. 多個事務可以獲取共享鎖, 互斥鎖只能一個事務獲取. 有”多個地方”都希望是”這段時間我獲取的資料不能被修改, 我也不會改”, 那麼只能使用共享鎖.
nowait: 其它事務碰到鎖, 是否不等待直接”報錯”.
of: 指明上鎖的表, 如果不指明, 則查詢中涉及的所有表(行)都會加鎖.

5.3. 事務巢狀

SQLAlchemy 中的事務巢狀有兩種情況. 一是在 session 中管理的事務, 本身有層次性. 二是 session 和原始的 connection 之間, 是一種層次關係, 在這 session , connection 兩個概念之中的事務同樣具有這樣的層次.

session 中的事務, 可能通過 begin_nested() 方法做 savepoint :

session.add(u1)
session.add(u2)

session.begin_nested()
session.add(u3)
session.rollback() # rolls back u3, keeps u1 and u2

session.commit()

或者使用上下文物件:

for record in records:
    try:
        with session.begin_nested():
            session.merge(record)
    except:
        print "Skipped record %s" % record
session.commit()

巢狀的事務的一個效果, 是最外層事務提交整個變更才會生效.

user = User(name=`2`)

session.begin_nested()
session.add(user)
session.commit()

session.rollback()

於是, 前面說的第二種情況有一種應用方式, 就是在 connection 上做一個事務, 最終也在 connection 上回滾這個事務, 如果 session 是 bind 到這個連線上的, 那麼 session 上所做的更改全部不會生效:

conn = Engine.connect()
session = Session(bind=conn)
trans = conn.begin()

user = User(name=`2`)
session.begin_nested()
session.add(user)
session.commit()

session.commit()

trans.rollback()

在測試中這種方式可能會有用.

5.4. 二段式提交

二段式提交, Two-Phase, 是為解決分散式環境下多點事務控制的一套協議.

與一般事務控制的不同是, 一般事務是 begin, 之後 commit 結束.

而二段式提交的流程上, begin 之後, 是 prepare transaction `transaction_id` , 這時相關事務資料已經持久化了. 之後, 再在任何時候(哪怕重啟服務), 作 commit prepared `transaction_id` 或者 rollback prepared `transaction_id` .

從多點事務的控制來看, 應用層要做的事是, 先把任務分發出去, 然後收集”事務準備”的狀態(prepare transaction 的結果). 根據收集的結果決定最後是 commit 還是 rollback .

簡單來說, 就是事務先儲存, 再說提交的事.

SQLAlchemy 中對這個機制的支援, 是在構建會話類是加入 twophase 引數:

Session = sessionmaker(twophase=True)

然後會話類可以根據一些策略, 繫結多個 Engine , 可以是多個資料庫連線, 比如:

Session = sessionmaker(twophase=True)
Session.configure(binds={User: Engine, Blog: Engine2})

這樣, 在獲取一個會話例項之後, 就處在二段式提交機制的支援之下, SQLAlchemy 自己會作多點的協調了. 完整的流程:

Engine = create_engine(`postgresql://test@localhost:5432/test`, echo=True)
Engine2 = create_engine(`postgresql://test@localhost:5432/test2`, echo=True)

Session = sessionmaker(twophase=True)

Session.configure(binds={User: Engine, Blog: Engine2})
session = Session()

user = User(name=u`名字`)
session.add(user)
session.commit()

對應的 SQL 大概就是:

begin;
insert into "user" (name) values (?);
prepare transaction `xx`;
commit prepared `xx`;

使用時, Postgresql 資料庫需要把 max_prepared_transactions 這個配置項的值改成大於 0 .

6. 欄位型別

6.1. 基本型別

欄位型別是在定義模型時, 對每個 Column 的型別約定. 不同型別的欄位型別在輸入輸出上, 及支援的操作方面, 有所區別.

這裡只介紹 sqlalchemy.types.* 中的型別, SQL 標準型別方面, 是寫什麼最後生成的 DDL 語句就是什麼, 比如 BIGINT, BLOG 這些, 但是這些型別並不一定在所有資料庫中都有支援. 除此而外, SQLAlchemy 也支援一些特定資料庫的特定型別, 這些需要從具體的 dialects 實現裡匯入.

Integer/BigInteger/SmallInteger: 整形.
Boolean: 布林型別. Python 中表現為 True/False , 資料庫根據支援情況, 表現為 BOOLEAN 或 SMALLINT. 例項化時可以指定是否建立約束(預設建立).
Date/DateTime/Time (timezone=False): 日期型別, Time 和 DateTime 例項化時可以指定是否帶時區資訊.
Interval: 時間偏差型別. 在 Python 中表現為 datetime.timedelta() , 資料庫不支援此型別則存為日期.
Enum (*enums, **kw): 列舉型別, 根據資料庫支援情況, SQLAlchemy 會使用原生支援或者使用 VARCHAR 型別附加約束的方式實現. 原生支援中涉及新型別建立, 細節在例項化時控制.
Float: 浮點小數.
Numeric (precision=None, scale=None, decimal_return_scale=None, ...): 定點小數, Python 中表現為 Decimal .
LargeBinary (length=None): 位元組資料. 根據資料庫實現, 在例項化時可能需要指定大小.
PickleType: Python 物件的序列化型別.
String (length=None, collation=None, ...): 字串型別, Python 中表現為 Unicode , 資料庫表現為 VARCHAR , 通常都需要指定長度.
Unicode: 類似與字串型別, 在某些資料庫實現下, 會明確表示支援非 ASCII 字元. 同時輸入輸出也強制是 Unicode 型別.
Text: 長文字型別, Python 表現為 Unicode , 資料庫表現為 TEXT .
UnicodeText: 參考 Unicode .

7. 混合屬性機制

7.1. 直接行為

混合屬性, 官方文件中稱之為 Hybrid Attributes . 這種機制表現為, 一個屬性, 在類和層面, 和例項的層面, 其行為是不同的. 之所以需要關注這部分的差異, 原因源於 Python 上下文和 SQL 上下文的差異.

類層面經常是作為 SQL 查詢時的一部分, 它面向的是 SQL 上下文. 而例項是已經得到或者建立的結果, 它面向的是 Python 上下文.

定義模型的 Column() 就是一個典型的混合屬性. 作為例項屬性時, 是具體的物件值訪問, 而作為類屬性時, 則有構成 SQL 語句表示式的功能.

class Interval(BaseModel):
    __tablename__ = `interval`

    id = Column(Integer, autoincrement=True, primary_key=True)
    start = Column(Integer)
    end = Column(Integer)

session.add(Interval(start=0, end=100))
session.commit()

例項行為:

ins = session.query(Interval).first()
print ins.end - ins.start

類行為:

ins = session.query(Interval).filter(Interval.end - Interval.start > 10).first()

這種機制其實一直在被使用, 但是可能大家都沒有留意一個屬性在類和例項上的區別.

如果屬性需要被進一步封裝, 那麼就需要明確宣告 Hybrid Attributes 了:

from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method

class Interval(BaseModel):
    __tablename__ = `interval`

    id = Column(Integer, autoincrement=True, primary_key=True)
    start = Column(Integer)
    end = Column(Integer)

    @hybrid_property
    def length(self):
        return self.end - self.start

    @hybrid_method
    def bigger(self, i):
        return self.length > i


session.add(Interval(start=0, end=100))
session.commit()

ins = session.query(Interval).filter(Interval.length > 10).first()
ins = session.query(Interval).filter(Interval.bigger(10)).first()
print ins.bigger(1)

setter 的定義同樣使用對應的裝飾器即可:

class Interval(BaseModel):
    __tablename__ = `interval`

    id = Column(Integer, autoincrement=True, primary_key=True)
    start = Column(Integer)
    end = Column(Integer)

    @hybrid_property
    def length(self):
        return abs(self.end - self.start)

    @length.setter
    def length(self, l):
        self.end = self.start + l

7.2. 表示式行為

前面說的屬性, 在類和例項上有不同行為, 可以看到, 在類上的行為, 其實就是生成 SQL 表示式時的行為. 上面的例子只是簡單的運算, SQLAlchemy 可以自動處理好 Python 函式和 SQL 函式的區別. 但是如果是一些特性更強的 SQL 函式, 就需要手動指定了. 於時, 這時的情況變成, 例項行為是 Python 範疇的呼叫行為, 而類行為則是生成 SQL 函式的相關表示式.

同時是前面的例子, 對於 length 的定義, 更嚴格上來說, 應該是取絕對值的.

class Interval(BaseModel):
    __tablename__ = `interval`

    id = Column(Integer, autoincrement=True, primary_key=True)
    start = Column(Integer)
    end = Column(Integer)

    @hybrid_property
    def length(self):
        return abs(self.end - self.start)

但是, 如果使用了 Python 的 abs() 函式, 在生成 SQL 表示式時顯示有無法處理了. 所以, 需要手動定義:

from sqlalchemy import func

class Interval(BaseModel):
    __tablename__ = `interval`

    id = Column(Integer, autoincrement=True, primary_key=True)
    start = Column(Integer)
    end = Column(Integer)

    @hybrid_property
    def length(self):
        return abs(self.end - self.start)

    @length.expression
    def length(self):
        return func.abs(self.end - self.start)

這樣查詢時就可以直接使用:

ins = session.query(Interval).filter(Interval.length > 1).first()

對應的 SQL :

SELECT *
FROM interval
WHERE abs(interval."end" - interval.start) > ?
 LIMIT ? OFFSET ?

7.3. 應用於關係

總體上沒有特別之處:

class Account(BaseModel):
    __tablename__ = `account`

    id = Column(Integer, autoincrement=True, primary_key=True)
    user = Column(Integer, ForeignKey(`user.id`), index=True)
    balance = Column(Integer, server_default=`0`)


class User(BaseModel):
    __tablename__ = `user`

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(32), nullable=False, server_default=``)

    accounts = relationship(`Account`)
    #balance = association_proxy(`accounts`, `balance`)

    @hybrid_property
    def balance(self):
        return sum(x.balance for x in self.accounts)

查詢時:

user = session.query(User).first()
print user.balance

這裡涉及的東西都是 Python 自己的, 包括那個 sum() 函式, 和 SQL 沒有關係.

如果想實現的是, 使用 SQL 的 sum() 函式, 取出指定使用者的總賬戶金額數, 那麼就要考慮把balance 作成表示式的形式:

from sqlalchemy import select

@hybrid_property
def balance(self):
    return select([func.sum(Account.balance)]).where(Account.user == self.id).label(`balance_v`)
    #return func.sum(Account.balance)

這樣的話, User.balance 只是單純的一個表示式了, 查詢時指定欄位:

user = session.query(User, User.balance).first()
print user.balance_v

注意, 如果寫成:

session.query(User.balance).first()

意義就不再是”獲取第一個使用者的總金額”, 而變成”獲取總金額的第一個”. 這裡很坑吧.

像上面這樣改, 例項層面就無法使用 balance 屬性. 所以, 還是先前介紹的, 表示式可以單獨處理:

@hybrid_property
def balance(self):
    return sum(x.balance for x in self.accounts)

@balance.expression
def balance(self):
    return select([func.sum(Account.balance)]).where(Account.user == self.id).label(`balance_v`)

定義了表示式的 balance , 這部分作為查詢條件上當然也是可以的:

user = session.query(User).filter(User.balance > 1).first()

8. 示例: AdjacencyList, 單向連結列表

這裡說的 AdjacencyList , 就是最常用來在關聯式資料庫中表示樹結構的, parent 方式:

id	name	parent
1	一	null
2	二	1
3	三	2

上面的資料, 表示的結構就是:

一
  |- 二
    |- 三

模型定義很好做:

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey
from sqlalchemy.types import Integer, Unicode
from sqlalchemy.orm import relationship, sessionmaker, joinedload

BaseModel = declarative_base()
Engine = create_engine(`sqlite://`, echo=True)
Session = sessionmaker(Engine)

class Node(BaseModel):
    __tablename__ = `node`

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(32), nullable=False, server_default=``)
    parent = Column(Integer, ForeignKey(`node.id`), index=True,
                    nullable=False, server_default=`0`)

這裡不讓 parent 欄位有 null , 而使用 0 代替.

這個例子在關係上, 有一個糾結的地方, 因為 node 這個表, 它是自關聯的, 所以如果想要 children和 parent_obj 這兩個關係時:

children = relationship(`Node`)
parent_obj = relationship(`Node`)

呃, 尷尬了.

如果是兩個表, 那麼 SQLAlchemy 可以通過外來鍵在哪張表這個資訊, 來確定關係的方向:

class Blog(BaseModel):
    ...
    user = Column(Integer, ForeignKey(`user.id`))
    user_obj = relationship(`User`)

class User(BaseModel):
    ...
    blog_list = relationship(`Blog`)

因為外來鍵在 Blog 中, 所以 Blog -> User 的 user_obj 是一個 N -> 1 關係.

反之, User -> Blog 的 blog_list 則是一個 1 -> N 的關係.

而自相關的 Node 無法直接判斷方向, 所以 SQLAlchemy 會按 1 -> N 處理, 那麼:

children = relationship(`Node`)
parent_obj = relationship(`Node`)

這兩條之中, children 是正確的, 是我們想要的. 要定義 parent_obj 則需要在 relationship 中通過引數明確表示方向:

parent_obj = relationship(`Node`, remote_side=[id])

這種方式就定義了一個, “到 id” 的 N -> 1 關係.

現在完整的模型定義是:

class Node(BaseModel):
    __tablename__ = `node`

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(32), nullable=False, server_default=``)
    parent = Column(Integer, ForeignKey(`node.id`), index=True,
                    nullable=False, server_default=`0`)

    children = relationship(`Node`) # 1 -> N
    parent_obj = relationship(`Node`, remote_side=[id])

查詢方面沒什麼特殊的了, 不過我發現在自相關的模型關係, lazy 選項不起作用:

children = relationship(`Node`, lazy="joined")
parent_obj = relationship(`Node`, remote_side=[id], lazy="joined")

都是無效的, 只有在查詢時, 手動使用 options() 定義:

n = session.query(Node).filter(Node.name==u`一`)
           .options(joinedload(`parent_obj`)).first()

如果要一次查出多級的子節點:

n = session.query(Node).filter(Node.name==u`一`)
           .options(joinedload(`children`).joinedload(`children`)).first()
print n.name, n.children, n.children[0].children

多個 joinedload() 串連的話, 可以使用 joinedload_all() 來整合:

from sqlalchemy.orm import joinedload_all

n = session.query(Node).filter(Node.name==u`一`)
           .options(joinedload_all(`children`, `children`)).first()

在修改方面, 刪除的話, 配置了 cascade , 刪除父節點, 則子節點也會自動刪除:

children = relationship(`Node`, lazy=`joined`, cascade=`all`) # 1 -> N
node = session.query(Node).filter(Node.name == u`一`).first()
session.delete(node)
session.commit()

如果只刪除子節點, 那麼 delete-orphan 選項就很好用了:

children = relationship(`Node`, lazy=`joined`, cascade=`all, delete-orphan`) # 1 -> N
node = session.query(Node).filter(Node.name == u`一`).first()
node.children = []
session.commit()

9. 示例: 屬性實體化建模

假設有這樣的場景, 某實體在具體條目上, 其屬性是不定的, 或者其屬性是充分稀疏的:

id	name	attr_0	attr_1	attr_2	…	attr_n
1	foo	1	abc	33	…	any

這種情況下, 把屬性看成是單獨的實體, 是一個更好的建模方式:

id	name
1	foo

id	entity_id	attr_name	attr_value
1	1	attr_0	1
2	1	attr_1	33
…	…	…	…
n	1	attr_n	any

這種模型下, ORM 層面我們考慮封裝一個對操作更友好的上層操作介面, 比如:

obj = Entity()
obj[`attr_0`] = `1`
obj[`attr_1`] = `33`
session.add(obj)
session.commit()

實現上, 就是把物件的方法, 包裝成 SQLAlchemy 的 ORM 中的對應的關係操作.

class BaseModel(declarative_base()):
    __abstract__ = True

class Entity(BaseModel):
    __tablename__ = `entity`

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(32), server_default=``)

    _attributes = relationship(`Attribute`,
                               collection_class=attribute_mapped_collection(`key`),
                               lazy=`joined`, cascade="all, delete-orphan")
    attributes = association_proxy(`_attributes`, `value`,
                                   creator=lambda k, v: Attribute(key=k, value=v))


class Attribute(BaseModel):
    __tablename__ = `attribute`

    id = Column(Integer, autoincrement=True, primary_key=True)
    entity = Column(Integer, ForeignKey(`entity.id`), index=True)
    key = Column(Unicode(32), server_default=``)
    value = Column(UnicodeText, server_default=``)


if __name__ == `__main__`:
    BaseModel.metadata.create_all(Engine)

    session = Session()

    entity = Entity(name=u`哈哈`)
    entity.attributes[u`first`] = u`abc`
    entity.attributes[u`sec`] = u`hoho`
    session.add(entity)
    session.commit()

    entity = session.query(Entity).first()
    print entity.attributes
    del entity.attributes[`first`]
    session.commit()

    entity = session.query(Entity).first()
    print entity.attributes

實現上就兩點:

_attributes 關係中, 指定 collection_class , 於是就可以得到一個像 dict 的屬性物件了.
association_proxy 從 dict 的屬性物件中只抽出我們關心的 value 屬性值.

這個場景中, 還可以再進一步, 在 Entity 類上實現 dict 的一些方法, 直接操作其 attributes 屬性,association_proxy 就直接返回 Entity 的例項, 這樣程式碼可以變成這樣:

entity = Entity(name=u`ABC`)
entity[u`first`] = u`a`
entity[u`sec`] = u`hoho`

SQLAlchemy參考