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


安装

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

  1. pip install SQLAlchemy

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

  1. >>> import sqlalchemy
  2. >>> print sqlsqlalchemy.__version__
  3. 1.1.0

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

声明模型

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

  1. from sqlalchemy import Column, String, Integer
  2. from sqlalchemy.ext.declarative import declarative_base
  3. Base = declarative_base()
  4. class User(Base):
  5. __tablename__ = 'user'
  6. id = Column(Integer, primary_key=True)
  7. name = Column(String(50))

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

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

  1. >>> User.__table__
  2. 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 的操作的,所以,下一步我们是需要创建数据库表。

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

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

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

  1. User.metadata.create_all(engine)

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

增删改查

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

增加记录

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

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

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

为了让他生效,我们可以将事务提交:

  1. session.commit()

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

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

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

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

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

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

删除记录

  1. usr = session.query(User).first()
  2. session.delete(usr)
  3. session.commit()

更新记录

  1. usr = session.query(User).first()
  2. usr.name = "LiSi"
  3. session.add(usr)
  4. session.commit()

查询记录

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

通用过滤器

  1. query.filter(User.name == 'ed')
  1. query.filter(User.name != 'ed')
  1. query.filter(User.name.like('%ed%'))
  1. query.filter(User.name.in_(['ed', 'wendy', 'jack']))
  2. # works with query objects too:
  3. query.filter(User.name.in_(
  4. session.query(User.name).filter(User.name.like('%ed%'))
  5. ))
  1. query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
  1. query.filter(User.name == None)
  2. # alternatively, if pep8/linters are a concern
  3. query.filter(User.name.is_(None))
  1. query.filter(User.name != None)
  2. # alternatively, if pep8/linters are a concern
  3. query.filter(User.name.isnot(None))
  1. # use and_()
  2. from sqlalchemy import and_
  3. query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
  4. # or send multiple expressions to .filter()
  5. query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
  6. # or chain multiple filter()/filter_by() calls
  7. query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
  8. Note
  9. Make sure you use and_() and not the Python and operator!
  1. from sqlalchemy import or_
  2. query.filter(or_(User.name == 'ed', User.name == 'wendy'))
  3. Note
  4. Make sure you use or_() and not the Python or operator!
  1. query.filter(User.name.match('wendy'))

返回值

统计个数

普通的统计记录数:

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

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

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

模型关系

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

其实,在关系中,不在乎就这么几种关系:

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

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

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

一对多的关系

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

图 1:模型关系图

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

  1. #!/usr/bin/env python
  2. # encoding: utf-8
  3. from sqlalchemy import create_engine
  4. from sqlalchemy import Table, Column, Integer, ForeignKey
  5. from sqlalchemy.orm import relationship, sessionmaker
  6. from sqlalchemy.ext.declarative import declarative_base
  7. engine = create_engine('sqlite:///:memory:', echo=True)
  8. Session = sessionmaker(bind=engine)
  9. session = Session()
  10. Base = declarative_base(bind=engine)
  11. class User(Base):
  12. __tablename__ = 'user'
  13. id = Column(Integer, primary_key=True)
  14. addresses = relationship("Address")
  15. class Address(Base):
  16. __tablename__ = 'address'
  17. id = Column(Integer, primary_key=True)
  18. user_id = Column(Integer, ForeignKey('user.id'))
  19. if __name__ == "__main__":
  20. Base.metadata.create_all()
  21. u = User()
  22. session.add(u)
  23. session.commit()
  24. a1 = Address(user_id=u.id)
  25. a2 = Address(user_id=u.id)
  26. session.add(a1)
  27. session.add(a2)
  28. session.commit()
  29. print "address: {}".format(u.addresses)

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

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

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

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

  1. class User(Base):
  2. __tablename__ = 'user'
  3. id = Column(Integer, primary_key=True)
  4. addresses = relationship("Address")
  5. class Address(Base):
  6. __tablename__ = 'address'
  7. id = Column(Integer, primary_key=True)
  8. user_id = Column(Integer, ForeignKey('user.id'))
  9. user = relationship("User")

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

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

  1. class User(Base):
  2. __tablename__ = 'user'
  3. id = Column(Integer, primary_key=True)
  4. addresses = relationship("Address", backref="user")
  5. class Address(Base):
  6. __tablename__ = 'address'
  7. id = Column(Integer, primary_key=True)
  8. user_id = Column(Integer, ForeignKey('user.id'))

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

题外话

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

  1. class User(Base):
  2. __tablename__ = 'user'
  3. id = Column(Integer, primary_key=True)
  4. addresses = relationship("Address", back_populates="user")
  5. class Address(Base):
  6. __tablename__ = 'address'
  7. id = Column(Integer, primary_key=True)
  8. user_id = Column(Integer, ForeignKey('user.id'))
  9. user = relationship("User", back_populates="addresses")

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

  1. class User(Base):
  2. __tablename__ = 'user'
  3. id = Column(Integer, primary_key=True)
  4. addresses = relationship("Address")
  5. class Address(Base):
  6. __tablename__ = 'address'
  7. id = Column(Integer, primary_key=True)
  8. user_id = Column(Integer, ForeignKey('user.id'))
  9. user = relationship("User")
  10. Base.metadata.create_all()
  11. u = User()
  12. a2 = Address()
  13. a2.user = u
  14. print "address: {}".format(u.addresses)

多对多关系

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

  1. user_address_table = Table(
  2. 'user_adderss', Base.metadata,
  3. Column('user_id', Integer, ForeignKey('user.id')),
  4. Column('address_id', Integer, ForeignKey('address.id'))
  5. )

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

  1. class User(Base):
  2. __tablename__ = 'user'
  3. id = Column(Integer, primary_key=True)
  4. addresses = relationship("Address", secondary=user_address_table)
  5. class Child(Base):
  6. __tablename__ = 'address'
  7. id = Column(Integer, primary_key=True)
  8. users = relationship("user", secondary=user_address_table)

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

  1. # 增/查
  2. u1 = User()
  3. u2 = User()
  4. session.add(u1)
  5. session.add(u2)
  6. session.commit()
  7. a1 = Address(users=[u1, u2])
  8. a2 = Address(users=[u1, u2])
  9. session.add(a1)
  10. session.add(a2)
  11. session.commit()
  12. print "children: {}".format(u1.addresses)
  13. print "parent: {}".format(a1.users)
  14. # 改
  15. u1 = User()
  16. u2 = User()
  17. session.add(u1)
  18. session.add(u2)
  19. session.commit()
  20. a2 = Address()
  21. session.add(a2)
  22. session.commit()
  23. a2.users= [u1, u2]
  24. session.commit()
  25. print "parent: {}".format(u1.addresses)
  26. # 删除
  27. u1 = User()
  28. u2 = User()
  29. session.add(u1)
  30. session.add(u2)
  31. session.commit()
  32. a2 = Address()
  33. session.add(a2)
  34. session.commit()
  35. a2.users= [u1, u2]
  36. session.commit()
  37. print "parent: {}".format(u1.addresses)
  38. session.delete(u1)
  39. session.commit()
  40. print "parent: {}".format(a2.users)
  41. print "users: {}".format(session.query(User).count())

Reference