摘要：是的一個(gè)數(shù)據(jù)庫工具，提供了強(qiáng)大的對(duì)象模型間的轉(zhuǎn)換，可以滿足絕大多數(shù)數(shù)據(jù)庫操作的需求，并且支持多種數(shù)據(jù)庫引擎，，等，在這里記錄基本用法和學(xué)習(xí)筆記一安裝通過安裝二使用首先是連接到數(shù)據(jù)庫，支持多個(gè)數(shù)據(jù)庫引擎，不同的數(shù)據(jù)庫引擎連接字符串不一樣，常用

SQLAlchemy是python的一個(gè)數(shù)據(jù)庫ORM工具，提供了強(qiáng)大的對(duì)象模型間的轉(zhuǎn)換，可以滿足絕大多數(shù)數(shù)據(jù)庫操作的需求，并且支持多種數(shù)據(jù)庫引擎（sqlite，mysql，postgres, mongodb等），在這里記錄基本用法和學(xué)習(xí)筆記

通過pip安裝

$ pip install SQLAlchemy

首先是連接到數(shù)據(jù)庫，SQLALchemy支持多個(gè)數(shù)據(jù)庫引擎，不同的數(shù)據(jù)庫引擎連接字符串不一樣，常用的有

mysql://username:password@hostname/database
postgresql://username:password@hostname/database
sqlite:////absolute/path/to/database
sqlite:///c:/absolute/path/to/database

更多連接字符串的介紹參見這里

下面是連接和使用sqlite數(shù)據(jù)庫的例子

使用傳統(tǒng)的connection的方式連接和操作數(shù)據(jù)庫

from sqlalchemy import create_engine

# 數(shù)據(jù)庫連接字符串
DB_CONNECT_STRING = "sqlite:///:memory:"

# 創(chuàng)建數(shù)據(jù)庫引擎,echo為True,會(huì)打印所有的sql語句
engine = create_engine(DB_CONNECT_STRING, echo=True)

# 創(chuàng)建一個(gè)connection，這里的使用方式與python自帶的sqlite的使用方式類似
with engine.connect() as con:
    # 執(zhí)行sql語句，如果是增刪改，則直接生效，不需要commit
    rs = con.execute("SELECT 5")
    data = rs.fetchone()[0]
    print "Data: %s" % data

與python自帶的sqlite不同，這里不需要Cursor光標(biāo)，執(zhí)行sql語句不需要commit

使用事務(wù)可以進(jìn)行批量提交和回滾

from sqlalchemy import create_engine

# 數(shù)據(jù)庫連接字符串
DB_CONNECT_STRING = "sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite"
engine = create_engine(DB_CONNECT_STRING, echo=True)

with engine.connect() as connection:
    trans = connection.begin()
    try:
        r1 = connection.execute("select * from User")
        r2 = connection.execute("insert into User(name, age) values(?, ?)", "bomo", 24)
        trans.commit()
    except:
        trans.rollback()
        raise

connection是一般使用數(shù)據(jù)庫的方式，sqlalchemy還提供了另一種操作數(shù)據(jù)庫的方式，通過session對(duì)象，session可以記錄和跟蹤數(shù)據(jù)的改變，在適當(dāng)?shù)臅r(shí)候提交，并且支持強(qiáng)大的ORM的功能，下面是基本使用

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 數(shù)據(jù)庫連接字符串
DB_CONNECT_STRING = "sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite"

# 創(chuàng)建數(shù)據(jù)庫引擎,echo為True,會(huì)打印所有的sql語句
engine = create_engine(DB_CONNECT_STRING, echo=True)

# 創(chuàng)建會(huì)話類
DB_Session = sessionmaker(bind=engine)

# 創(chuàng)建會(huì)話對(duì)象
session = DB_Session()

# dosomething with session

# 用完記得關(guān)閉，也可以用with
session.close()

上面創(chuàng)建了一個(gè)session對(duì)象，接下來可以操作數(shù)據(jù)庫了，session也支持通過sql語句操作數(shù)據(jù)庫

session.execute("select * from User")
session.execute("insert into User(name, age) values("bomo", 13)")
session.execute("insert into User(name, age) values(:name, :age)", {"name": "bomo", "age":12})

# 如果是增刪改，需要commit
session.commit()

注意參數(shù)使用dict，并在sql語句中使用:key占位

上面簡單介紹了sql的簡單用法，既然是ORM框架，我們先定義兩個(gè)模型類User和Role，sqlalchemy的模型類繼承自一個(gè)由declarative_base()方法生成的類，我們先定義一個(gè)模塊Models.py生成Base類

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

User.py

from sqlalchemy import Column, Integer, String
from Models import Base

class User(Base):
    __tablename__ = "User"
    id = Column("id", Integer, primary_key=True, autoincrement=True)
    name = Column("name", String(50))
    age = Column("age", Integer)

Role.py

from sqlalchemy import Column, Integer, String
from Models import Base

class Role(Base):
    __tablename__ = "Role"
    id = Column("id", Integer, primary_key=True, autoincrement=True)
    name = Column("name", String(50))

從上面很容易看出來，這里的模型對(duì)應(yīng)數(shù)據(jù)庫中的表，模型支持的類型有Integer, String, Boolean, Date, DateTime, Float，更多類型包括類型對(duì)應(yīng)的Python的類型參見：這里

Column構(gòu)造函數(shù)相關(guān)設(shè)置

name：名稱

type_：列類型

autoincrement：自增

default：默認(rèn)值

index：索引

nullable：可空

primary_key：外鍵

更多介紹參見這里

接下來通過session進(jìn)行增刪改查

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from User import User
from Role import Role
from Models import Base

DB_CONNECT_STRING = "sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite"
engine = create_engine(DB_CONNECT_STRING, echo=True)
DB_Session = sessionmaker(bind=engine)
session = DB_Session()

# 1. 創(chuàng)建表（如果表已經(jīng)存在，則不會(huì)創(chuàng)建）
Base.metadata.create_all(engine)

# 2. 插入數(shù)據(jù)
u = User(name = "tobi", age = 200)
r = Role(name = "user")

# 2.1 使用add，如果已經(jīng)存在，會(huì)報(bào)錯(cuò)
session.add(u)
session.add(r)
session.commit()
print r.id

# 3 修改數(shù)據(jù)
# 3.1 使用merge方法，如果存在則修改，如果不存在則插入（只判斷主鍵，不判斷unique列）
r.name = "admin"
session.merge(r)

# 3.2 也可以通過這種方式修改
session.query(Role).filter(Role.id == 1).update({"name": "admin"})

# 4. 刪除數(shù)據(jù)
session.query(Role).filter(Role.id == 1).delete()

# 5. 查詢數(shù)據(jù)
# 5.1 返回結(jié)果集的第二項(xiàng)
user = session.query(User).get(2)

# 5.2 返回結(jié)果集中的第2-3項(xiàng)
users = session.query(User)[1:3]

# 5.3 查詢條件
user = session.query(User).filter(User.id < 6).first()

# 5.4 排序
users = session.query(User).order_by(User.name)

# 5.5 降序（需要導(dǎo)入desc方法）
from sqlalchemy import desc
users = session.query(User).order_by(desc(User.name))

# 5.6 只查詢部分屬性
users = session.query(User.name).order_by(desc(User.name))
for user in users:
    print user.name

# 5.7 給結(jié)果集的列取別名
users = session.query(User.name.label("user_name")).all()
for user in users:
    print user.user_name

# 5.8 去重查詢（需要導(dǎo)入distinct方法）
from sqlalchemy import distinct
users = session.query(distinct(User.name).label("name")).all()

# 5.9 統(tǒng)計(jì)查詢
user_count = session.query(User.name).order_by(User.name).count()
age_avg = session.query(func.avg(User.age)).first()
age_sum = session.query(func.sum(User.age)).first()

# 5.10 分組查詢
users = session.query(func.count(User.name).label("count"), User.age).group_by(User.age)
for user in users:
    print "age:{0}, count:{1}".format(user.age, user.count)

# 6.1 exists查詢(不存在則為~exists())
from sqlalchemy.sql import exists
session.query(User.name).filter(~exists().where(User.role_id == Role.id))
# SELECT name AS users_name FROM users WHERE NOT EXISTS (SELECT * FROM roles WHERE users.role_id = roles.id)

# 6.2 除了exists，any也可以表示EXISTS
session.query(Role).filter(Role.users.any())

# 7 random
from sqlalchemy.sql.functions import random
user = session.query(User).order_by(random()).first()

session.close()

參考鏈接：

any

上面的所有操作都是基于單個(gè)表的操作，下面是多表以及關(guān)系的使用，我們修改上面兩個(gè)表，添加外鍵關(guān)聯(lián)（一對(duì)多和多對(duì)一）

User模型

from sqlalchemy import Column, Integer, String
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from Models import Base

class User(Base):
    __tablename__ = "users"
    id = Column("id", Integer, primary_key=True, autoincrement=True)
    name = Column("name", String(50))
    age = Column("age", Integer)

    # 添加角色id外鍵(關(guān)聯(lián)到Role表的id屬性)
    role_id = Column("role_id", Integer, ForeignKey("roles.id"))
    # 添加同表外鍵
    second_role_id = Column("second_role_id", Integer, ForeignKey("roles.id"))

    # 添加關(guān)系屬性，關(guān)聯(lián)到role_id外鍵上
    role = relationship("Role", foreign_keys="User.role_id", backref="User_role_id")
    # 添加關(guān)系屬性，關(guān)聯(lián)到second_role_id外鍵上
    second_role = relationship("Role", foreign_keys="User.second_role_id", backref="User_second_role_id")

Role模型

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import relationship
from Models import Base

class Role(Base):
    __tablename__ = "roles"
    id = Column("id", Integer, primary_key=True, autoincrement=True)
    name = Column("name", String(50))

    # 添加關(guān)系屬性，關(guān)聯(lián)到User.role_id屬性上
    users = relationship("User", foreign_keys="User.role_id", backref="Role_users")
    # 添加關(guān)系屬性，關(guān)聯(lián)到User.second_role_id屬性上
    second_users = relationship("User", foreign_keys="User.second_role_id", backref="Role_second_users")

這里有一點(diǎn)需要注意的是，設(shè)置外鍵的時(shí)候ForeignKey("roles.id")這里面使用的是表名和表列，在設(shè)置關(guān)聯(lián)屬性的時(shí)候relationship("Role", foreign_keys="User.role_id", backref="User_role_id")，這里的foreign_keys使用的時(shí)候類名和屬性名

接下來就可以使用了

u = User(name="tobi", age=200)

r1 = Role(name="admin")
r2 = Role(name="user")

u.role = r1
u.second_role = r2

session.add(u)
session.commit()

# 查詢（對(duì)于外鍵關(guān)聯(lián)的關(guān)系屬性可以直接訪問，在需要用到的時(shí)候session會(huì)到數(shù)據(jù)庫查詢）
roles = session.query(Role).all()
for role in roles:
    print "role:{0} users"
    for user in role.users:
        print "	{0}".format(user.name)
    print "role:{0} second_users"
    for user in role.second_users:
        print "	{0}".format(user.name)

上面表示的是一對(duì)多（多對(duì)一）的關(guān)系，還有一對(duì)一，多對(duì)多，如果要表示一對(duì)一的關(guān)系，在定義relationship的時(shí)候設(shè)置uselist為False（默認(rèn)為True），如在Role中

class Role(Base):
    ...
    user = relationship("User", uselist=False, foreign_keys="User.role_id", backref="Role_user")

多表查詢通常使用join進(jìn)行表連接，第一個(gè)參數(shù)為表名，第二個(gè)參數(shù)為條件，例如

users = db.session.query(User).join(Role, Role.id == User.role_id)

for u in users:
    print u.name

join為內(nèi)連接，還有左連接outerjoin，用法與join類似，右連接和全外鏈接在1.0版本上不支持，通常來說有這兩個(gè)結(jié)合查詢的方法基本夠用了，1.1版本貌似添加了右連接和全外連接的支持，但是目前只是預(yù)覽版

還可以直接查詢多個(gè)表，如下

result = db.session.query(User, Role).filter(User.role_id = Role.id)
# 這里選擇的是兩個(gè)表，使用元組獲取數(shù)據(jù)
for u, r in result:
      print u.name

sqlalchemy的數(shù)據(jù)庫遷移/升級(jí)有兩個(gè)庫支持alembic和sqlalchemy-migrate

由于sqlalchemy-migrate在2011年發(fā)布了0.7.2版本后，就已經(jīng)停止更新了，并且已經(jīng)不維護(hù)了，也積累了很多bug，而alembic是較后來才出現(xiàn)，而且是sqlalchemy的作者開發(fā)的，有良好的社區(qū)支持，所以在這里只學(xué)習(xí)alembic這個(gè)庫

alembic實(shí)現(xiàn)了類似git/svn的版本管理的控制，我們可以通過alembic維護(hù)每次升級(jí)數(shù)據(jù)庫的版本

通過pip安裝，pip會(huì)自動(dòng)安裝相關(guān)的依賴

$ pip install alembic

安裝完成后再項(xiàng)目根目錄運(yùn)行

$ alembic init YOUR_ALEMBIC_DIR

alembic會(huì)在根目錄創(chuàng)建YOUR_ALEMBIC_DIR目錄和alembic.ini文件，如下

yourproject/
    alembic.ini
    YOUR_ALEMBIC_DIR/
        env.py
        README
        script.py.mako
        versions/
            3512b954651e_add_account.py
            2b1ae634e5cd_add_order_id.py
            3adcc9a56557_rename_username_field.py

其中

alembic.ini 提供了一些基本的配置

env.py 每次執(zhí)行Alembic都會(huì)加載這個(gè)模塊，主要提供項(xiàng)目Sqlalchemy Model 的連接

script.py.mako 遷移腳本生成模版

versions 存放生成的遷移腳本目錄

默認(rèn)情況下創(chuàng)建的是基于單個(gè)數(shù)據(jù)庫的，如果需要支持多個(gè)數(shù)據(jù)庫或其他，可以通過alembic list_templates查看支持的模板

$ alembic list_templates
Available templates:

generic - Generic single-database configuration.
multidb - Rudimentary multi-database configuration.
pylons - Configuration that reads from a Pylons project environment.

Templates are used via the "init" command, e.g.:

  alembic init --template generic ./scripts

使用之前，需要配置一下鏈接字符串，打開alembic.ini文件，設(shè)置sqlalchemy.url連接字符串，例如

sqlalchemy.url = sqlite:////Users/zhengxiankai/Desktop/database.db

其他參數(shù)可以參見官網(wǎng)說明：http://alembic.zzzcomputing.com/en/latest/tutorial.html

接下來我們創(chuàng)建一個(gè)數(shù)據(jù)庫版本，并新建兩個(gè)表

$ alembic revision -m "create table"

創(chuàng)建一個(gè)版本（會(huì)在yourproject/YOUR_ALEMBIC_DIR/versions/文件夾中創(chuàng)建一個(gè)python文件1a8a0d799b33_create_table.py）

該python模塊包含upgrade和downgrade兩個(gè)方法，在這里添加一些新增表的邏輯

"""create table

Revision ID: 4fd533a56b34
Revises:
Create Date: 2016-09-18 17:20:27.667100

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "4fd533a56b34"
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    # 添加表
    op.create_table(
        "account",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("name", sa.String(50), nullable=False),
        sa.Column("description", sa.Unicode(200)),
    )

    # 添加列
    # op.add_column("account", sa.Column("last_transaction_date", sa.DateTime))



def downgrade():
    # 刪除表
    op.drop_table("account")

    # 刪除列
    # op.drop_column("account", "last_transaction_date")

這里使用到了了op對(duì)象，關(guān)于op對(duì)象的更多API使用，參見這里

這里生成的文件名是依照在alembic.ini文件聲明的模板來的，默認(rèn)為版本號(hào)+名字，可以加上一些日期信息，否則不好排序，更多參數(shù)參見這里

file_template = %%(year)d_%%(month).2d_%%(day).2d_%%(hour).2d_%%(minute).2d_%%(rev)s_%%(slug)s

另外通常我們也改一下生成模板script.py.mako，加上編碼信息，否則在升級(jí)腳本中如果有中文會(huì)報(bào)錯(cuò)

#!/usr/bin/python
# -*- coding:utf-8 -*-

剛剛實(shí)現(xiàn)了升級(jí)和降級(jí)的方法，通過下面命令升級(jí)數(shù)據(jù)庫到最新版本

$ alembic upgrade head

這時(shí)候可以看到數(shù)據(jù)庫多了兩個(gè)表alembic_version和account，alembic_version存放數(shù)據(jù)庫版本

關(guān)于升級(jí)和降級(jí)的其他命令還有下面這些

# 升到最高版本
$ alembic upgrade head

# 降到最初版本
$ alembic downgrade base

# 升兩級(jí)
$ alembic upgrade +2

# 降一級(jí)
$ alembic downgrade -1

# 升級(jí)到制定版本
$ alembic upgrade e93b8d488143

# 查看當(dāng)前版本
$ alembic current

# 查看歷史版本詳情
$ alembic history --verbose

# 查看歷史版本（-r參數(shù)）類似切片
$ alembic history -r1975ea:ae1027
$ alembic history -r-3:current
$ alembic history -r1975ea:

上面我們是通過API升級(jí)和降級(jí)，我們也可以直接通過元數(shù)據(jù)更新數(shù)據(jù)庫，也就是自動(dòng)生成升級(jí)代碼，先定義兩個(gè)Model（User, Role），這里我定義成三個(gè)文件

yourproject/
    YOUR_ALEMBIC_DIR/
    tutorial/Db
        Models.py
        User.py
        Role.py

代碼就放在一起了

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column("id", Integer, primary_key=True, autoincrement=True)
    name = Column("name", String)

class Role(Base):
    __tablename__ = "roles"

    id = Column("id", Integer, primary_key=True, autoincrement=True)
    name = Column("name", String)

在YOUR_ALEMBIC_DIR/env.py配置元數(shù)據(jù)

target_metadata = None

改為

import os
import sys

# 這里需要添加相對(duì)路徑到sys.path，否則會(huì)引用失敗，嘗試過使用相對(duì)路徑，但各種不好使，還是使用這種方法靠譜些
sys.path.append(os.path.abspath(os.path.join(os.getcwd(), "../yourproject/tutorial/Db")))

from User import User
from Role import Role
from Models import Base
target_metadata = Base.metadata

os.path.join(os.getcwd()這個(gè)獲取到的地址不是env.py的路徑，而是根目錄

在創(chuàng)建數(shù)據(jù)庫版本的時(shí)候添加--autogenerate參數(shù)，就會(huì)從Base.metadata元數(shù)據(jù)中生成腳本

$ alembic revision --autogenerate -m "add user table"

這時(shí)候會(huì)在生成升級(jí)代碼

"""add user table

Revision ID: 97de1533584a
Revises: 8678ab6d48c1
Create Date: 2016-09-19 21:58:00.758410

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "97de1533584a"
down_revision = "8678ab6d48c1"
branch_labels = None
depends_on = None

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table("roles",
    sa.Column("id", sa.Integer(), nullable=False),
    sa.Column("name", sa.String(), nullable=True),
    sa.PrimaryKeyConstraint("id")
    )
    op.create_table("users",
    sa.Column("id", sa.Integer(), nullable=False),
    sa.Column("name", sa.String(), nullable=True),
    sa.PrimaryKeyConstraint("id")
    )
    op.drop_table("account")
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table("account",
    sa.Column("id", sa.INTEGER(), nullable=False),
    sa.Column("name", sa.VARCHAR(length=50), nullable=False),
    sa.Column("description", sa.VARCHAR(length=200), nullable=True),
    sa.Column("last_transaction_date", sa.DATETIME(), nullable=True),
    sa.PrimaryKeyConstraint("id")
    )
    op.drop_table("users")
    op.drop_table("roles")
    ### end Alembic commands ###

由于我沒有定義account模型，會(huì)被識(shí)別為刪除，如果刪除了model的列的聲明，則會(huì)被識(shí)別為刪除列，自動(dòng)生成的版本我們也可以自己修改，然后執(zhí)行升級(jí)命令即可升級(jí)alembic upgrade head

需要注意的是

Base.metadata聲明的類必須以數(shù)據(jù)庫中的一一對(duì)應(yīng)，如果數(shù)據(jù)庫中有的表，而在元數(shù)據(jù)中沒有，會(huì)識(shí)別成刪除表

revision創(chuàng)建版本之前執(zhí)行之前需要升級(jí)到最新版本

配置Base之前，需要保證所有的Model都已經(jīng)執(zhí)行（即導(dǎo)入）過一次了，否則無法讀取到，也就是需要把所有Model都import進(jìn)來

數(shù)據(jù)庫升級(jí)有風(fēng)險(xiǎn)，升級(jí)前最好先檢查一遍upgrade函數(shù)，可以的話做好備份哈

如果使用mysql數(shù)據(jù)庫，String類型對(duì)應(yīng)的是VARCHAR類型，需要指定長度，否則會(huì)報(bào)下面錯(cuò)誤，而在sqlite不會(huì)出現(xiàn)

(in table "user", column "name"): VARCHAR requires a length on dialect mysql

如有問題歡迎到我的博客留言

Auto Generating Migrations

tutorial

最后安利一下自己的博客：http://zhengbomo.github.com