SQLAlchemy浅析

在 python 众多的 ORM 中，恐怕最常用也最难用的就要数 SQLAlchemy 了。它常用是因为在 Python 中关系型数据库的 ORM 种类本来不多，而且功能强大的也不多，可以说是稀少；而难用呢和关系型数据库的性质有很大的关系，所以，开发者们对 SQLAlchemy 又爱又恨。本文将对 SQLAlchemy 进行一些简单的介绍，并且尽可能简单得描述它提供的各种功能。

安装

安装 SQLAlchemy 的话还算方便，在 Ubuntu 环境下直接使用 pip 即可快速安装成功：

pip install SQLAlchemy

在使用之前，我们可以检查一下我们安装的 SQLAlchemy 是什么版本，其实，在 1.0 之后各小版本之间区别不是很大，只不过是版本越新，一些功能使用起来就越方便。那么该怎么查看版本呢，我们可以在交互式命令行中查看：

>>> import sqlalchemy
>>> print sqlsqlalchemy.__version__ 
1.1.0

这里我使用的版本已经是 1.1.0 版本了，是2016-09-18非常新的一个版本了。但是，如果你安装的是 1.0.x 版本，都不用着急的，因为刚才说了，对于使用，大部分功能都是没差的，所以保持就好，不需要可以升级。

声明模型

ORM 的一个最大的特点就是有模型，也就是我们常说的 Schema，这个 Scheme 是定义我们模型有什么元素，每个元素是什么类型，元素之间有什么关系的约束，在 SQLAlchemy 中定义模型有两种方式，分别是 经典模式 和 现代模式，这里我就举 现代模式 的例子吧，毕竟 经典模式 还是比较麻烦的。

from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class  User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name =  Column(String(50))

这里可以看到，我们先定义了一个基类 Base，然后再让我们的 Model 继承它。这个基类其实管理了一系列关于类和数据库表关联的属性，这些要我们自己来写的话就比较麻烦了，所以一般都是继承的，至于自己写要怎么处理，等下我们可以看到。

现在我们就有了一个自己的Model，我们可以对这个 Model 进行一些探索，看下有什么属性和方法之类的。

>>> User.__table__
Table('user', MetaData(bind=None), Column('id', Integer(), table=<user>, primary_key=True, nullable=False), Column('name', String(length=50), table=<user>), schema=None)

我们调用一下这个 Model 的 __table__ 属性，可以看到一个很奇怪的 Table 的定义，其实，这就是 经典模式 的定义方式，这里面有一个 MetaData，这就是在 Base 中替我们做的一部分代码。

创建数据库表

然而，这个时候有一点需要注意的就是，我们只是定义了 Model，在关系型数据库中其实是还没有创建表的，而和 NoSQL 数据库不一样的是，在关系型数据库中如果没有表，我们是不能做 CRUD 的操作的，所以，下一步我们是需要创建数据库表。

然而，又一个问题出现在面前，那就是虽然我们很想创建表，但是我们现在并不知道在哪个数据库中创建表，所以，至少我们现在还需要指定一个数据库连接，表示我们创建表的位置，这个连接需要这样指定：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+mysqldb://yetship:password@lenove/test?charset=utf8', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

这里有两个概念，分别是 Engine 和 Session。Engine 用于将我们的 SQL 提交给数据库，而 Session 我们可以看作是一个数据库连接，一个持久的连接。有了 Session 之后我们就可以创建数据库表了，创建代码很简单，直接这样即可：

User.metadata.create_all(engine)

这样，我们的表就创建好了。

增删改查

创建好表之后，我们就可以对表进行增删改查了，因为有了 Schema 和 Session，我们的 CRUD 也是比较方便了，但是相对于 NoSQL 来说，还是稍微算是比较复杂的了，下面就来一一查看：

增加记录

user = User(name="Tyrael")
session.add(user)

就两步，一个是定义好我们的数据，然后将它添加到 session 中，然而，这样并没有保存到数据库中，因为 session 其实正确的认识应该是关系型数据库中的事务，我们知道，在关系型数据库中事务是需要提交才会生效的，所以这里没有提交，并未生效，我们可以这样看未生效的数据：

>>> print session.new
IdentitySet([<__main__.User object at 0x7fbcf0239cf8>])

为了让他生效，我们可以将事务提交：

session.commit()

这样，才算是真正得保存到数据库中了。完整的代码应该是：

user = User(name="Tyrael")
session.add(user)
session.commit()

可以发现还是比较复杂的，因为我理想中比较简单的应该这样就好了：

session.add(User(name="Tyrael"))

其实这也是可以实现的，就是我们设置事务的 autocommit，让他自动提交，这样的话就方便多了。

因为 CRUD 都是比较简单的操作，下面就简单带过了：

删除记录

usr = session.query(User).first()
session.delete(usr)
session.commit()

更新记录

usr = session.query(User).first()
usr.name = "LiSi"
session.add(usr)
session.commit()

查询记录

查询比较复杂，而且操作比较多，这里就举一些例子进行说明：

通用过滤器

equals:

query.filter(User.name == 'ed')

not equals:

query.filter(User.name != 'ed')

LIKE:

query.filter(User.name.like('%ed%'))

query.filter(User.name.in_(['ed', 'wendy', 'jack']))
# works with query objects too:
query.filter(User.name.in_(
        session.query(User.name).filter(User.name.like('%ed%'))
))

NOT IN:

query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

IS NULL:

query.filter(User.name == None)
# alternatively, if pep8/linters are a concern
query.filter(User.name.is_(None))

IS NOT NULL:

query.filter(User.name != None)
# alternatively, if pep8/linters are a concern
query.filter(User.name.isnot(None))

AND:

# use and_()
from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
# or send multiple expressions to .filter()
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
# or chain multiple filter()/filter_by() calls
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
Note
Make sure you use and_() and not the Python and operator!

from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))
Note
Make sure you use or_() and not the Python or operator!

MATCH:

query.filter(User.name.match('wendy'))

返回值

first()：使用 limit 选取一个元素
all()：返回符合条件的所有结果列表
one()：获取所有符合条件的结果，如果结果数量不是刚好一个都抛出错误
one_or_none()：和 one 差不多，不过当有多个记录的时候不报错，没有元素的时候返回 None
scalar()：调用 one，但有数据的时候返回第一列的数据

统计个数

普通的统计记录数：

session.query(User).filter(User.name.like('%ed')).count()

类似于 SELECT count(*) FROM table 的统计：

session.query(func.count('*')).select_from(User).scalar()

模型关系

关系型数据库，和其他类型的数据库的最大区别应该就是关系了，如果没了关系，那么关系型数据库的作用便会大打折扣，那么，在 SQLAlchemy 中如何表示模型之间的关系呢，下面，我就来简单谈谈个人的理解：

其实，在关系中，不在乎就这么几种关系：

一对一
一对多（多对一）
多对多

然而，在 SQLAlchemy 中，其他设计中只有两种关系，分别是：

一对多
多对多

其实，我们思考一下，这也就够了，为什么，因为对应于关系型数据库，我们关系就只有两种处理方式：

一种是在表中加外键，这其实就涵盖了一对一 和 多对一 的关系
建立一张关联表，表中有两个表的外键，这其实处理的就是 多对多 的关系

基于这样的考虑，那么我就先从 一对多 的关系开始讲起

一对多的关系

首先先描述一下我的思路，这里我举的例子是这样的：



图 1：模型关系图

一个用户是有多个地址的，这就是一个简单的 一对多 的关系，然后我先写一段代码来描述这种关系：

#!/usr/bin/env python
# encoding: utf-8
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base(bind=engine)
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    addresses = relationship("Address")
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
if  __name__ == "__main__":
    Base.metadata.create_all()
    u = User()
    session.add(u)
    session.commit()
    a1 = Address(user_id=u.id)
    a2 = Address(user_id=u.id)
    session.add(a1)
    session.add(a2)
    session.commit()
    print "address: {}".format(u.addresses)

为了让你可以自己尝试运行，我把所有的代码都贴出来了，这里使用的是内存SQLite，这样的话一般在 Linux 下都可以跑起来而无需安全其他数据库或产生垃圾数据库文件。

这就是一个简单的建立关系的例子，这里有两个地方值得关注：

ForeignKey：外键，指定了 user_id 和 User 的关系
relationship：这个就绑定了两个 Model 的联系，可以通过 User 直接得到所有的地址

在我们下面的验证代码中，我们可以看到只需要在创建 Address 的时候传入用户的id，例如这样： Address(user_id=u.id)，然后，我们就可以通过 User 中的 addresses 来获取用户的所有地址了：u.addresses。

这个时候就有一个问题了，假如我们拿到一个地址，不知道是哪个用户的，我们想知道这个地址的用户是谁，事实上，目前为止的 Model 我们只能拿到 user_id，然后还得手动查询一遍，那么能不能方便些，直接从 Address 就可以获得 User 的对象呢？ SQLAlchemy 的强大帮我们做到了，我们现在修改一下 Model，其他累赘代码就不贴了：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    addresses = relationship("Address")
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship("User")

其实，我只是简单得在 Address 的模型上添加了一个 user = relationship("User"),然后就可以通过 a1.user 来获得用户的 Model了。

事情到这里以为就完了？显然不是啦，这里我们在 User 里面指定 addresses，又从Address 里面制定 user，感觉都好麻烦，那么有没有什么办法只指定一个就好了，另外一个就默认得可以访问呢？这个时候有一个参数叫做 backref 可以帮助我们，我们对 Model 稍作修改：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    addresses = relationship("Address", backref="user")
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))

其实对应于第一版，只是在 addresses = relationship("Address") 中加了一个 backref 参数，于是，我们就可以访问 user 参数了。

题外话

其实 relationship 还有一个参数 back_populates 的，用于指定对方的字段，例如，第二版的 Model 还可以这么写：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    addresses = relationship("Address", back_populates="user")
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship("User", back_populates="addresses")

但是，在这里显然没有必要，因为只有一个关联，我们可以很清晰得知道对应哪个字段，那么什么时候有必要？这里给一个例子则止：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    addresses = relationship("Address")
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship("User")
Base.metadata.create_all()
u = User()
a2 = Address()
a2.user = u
print "address: {}".format(u.addresses)

多对多关系

说完了多对一，再来说下多对多，刚才说了，多对多是需要建立一张关联表的，所以在建 Model 的时候也还是需要一个关联表，这时我们的 Model 还是 User 和 Address 的关系，但是不同的是，现在一个地址可以对应多个用户了：

user_address_table = Table(                         
    'user_adderss', Base.metadata,                  
    Column('user_id', Integer, ForeignKey('user.id')),
    Column('address_id', Integer, ForeignKey('address.id'))
)

这里其实都只有外键，所以就好理解了，然后看下 Model 怎么定义：

class User(Base):        
    __tablename__ = 'user'                                  
    id = Column(Integer, primary_key=True)                  
    addresses = relationship("Address", secondary=user_address_table)
class Child(Base):       
    __tablename__ = 'address'                               
    id = Column(Integer, primary_key=True)                  
    users = relationship("user", secondary=user_address_table)

其实也不复杂，就是 relationship 加上了一个参数：secondary，然后对应于关联表。增删改查其实就当作数组处理就可以了，这里给一些例子：

# 增/查
u1 = User()
u2 = User()
session.add(u1)
session.add(u2)
session.commit()
a1 = Address(users=[u1, u2])
a2 = Address(users=[u1, u2])
session.add(a1)
session.add(a2)
session.commit()
print "children: {}".format(u1.addresses)
print "parent: {}".format(a1.users)
# 改
u1 = User()
u2 = User()
session.add(u1)
session.add(u2)
session.commit()
a2 = Address()
session.add(a2)
session.commit()
a2.users= [u1, u2]
session.commit()
print "parent: {}".format(u1.addresses)
# 删除
u1 = User()
u2 = User()
session.add(u1)
session.add(u2)
session.commit()
a2 = Address()
session.add(a2)
session.commit()
a2.users= [u1, u2]
session.commit()
print "parent: {}".format(u1.addresses)
session.delete(u1)
session.commit()
print "parent: {}".format(a2.users)
print "users: {}".format(session.query(User).count())

格物致知

All Posts

SQLAlchemy浅析

安装

声明模型

增删改查

模型关系

一对多的关系

多对多关系

Reference

文章目录

系列文章