SQLAlchemyCheatSheet

0x00 前言

新工作的技术栈是以 Flask 为主,SQLAlchemy 是 许多玩 Flask 的人的标配。好,文档读起来,笔记搞起来。

本笔记于 2018-03-31 开启,SQLAlchemy 版本为 1.2.0。

Python 圈内三大 ORM SQLAlchemy VS Django ORM VS Peewee

  1. SQLAlchemy 复杂程度最高,同时,这也意味着 SQLAlchemy 可以做更多的事情。使用 DataMapper 方式实现
  2. Django ORM 个人最喜欢,使用 ActiveRecord 实现 如果不是因为现在 Flask 项目已经是用了 SQLAlchemy , 否则的话我甚至会考虑将 Django ORM 配置到 Flask 项目中。当然,也有蛋疼的 SqlAlchemy 使用者已经移植给 django 配置了 SQLAlchemy 的库。
  3. Peewee 没用过,不好评论。以后有机会试试。

0x01 如何快速上手

了解一个框架,最好是从下面几处入手:

  • 官方 Tuorial
  • 官方 Example
  • 官方 Guide
  • 官方 APIDocument
  • 源码

对框架掌握越深,就越需要使用者多多从上开始逐渐向下了解。

其实还有一些途径:

  • Github 上面的 issue
  • Stack Overflow 的高 vote 常见问题
  • 如果作者有一些活跃的社区的话,可以火速前往

鉴于我们的目标在于入手,所以可以火速过一下:

  • 官方 Tuorial
  • 官方 Example
  • 官方 Guide
  • Github 上面的 issue
  • Stack Overflow 的高 vote 常见问题
  • 如果作者有一些活跃的社区的话,可以火速前往

0x02 SQLAlchemy 的基本功能

2.0 SQLAlchemy VS DjangoORM

ORM 通常有 DataMapper 实现和 ActiveRecord 实现两种。

依照我的经验,ActiveRecord 使用起来的更接近对象 (Object) 的操作,DataMapper 使用起来更接近 (Table) 的操作。

SQLAlchemy 是 DataMapper 模式的实现,在该模式下,session 会暴露出来,即 Model 与 session 并不耦合。

DjangoORM 是 ActivityRecord 模式的一种实现,在该模式下,session 并不暴露出来,即 Model 与 session 耦合。

使用 Django ORM 的时候,往往是

1
2
b = Blog(**data)
b.save()

使用 SQLAlchemy 的时候,往往是

1
2
3
b = Blog(**data)
session.add(b)
session.session(b)

由于 Django 帮你屏蔽了 session 的操作。

在通常情况下,

  1. DjangoORM 使用起来更加接近 Object 的操作。
  2. SQLAlchemy 使用起来更加接近 Table 的操作。

举个例子,

一对多,Father 添加两个小孩(其中一个小孩是已经存在的)

在 DjangoORM 里面, 这里更像是一个 Set 的 add 操作。

1
father.children.add(new_child,exsit_child)

在 SQLAlchemy 里面,这里更像是一个 table 的 insert 操作。(麻蛋,你要说是一个 list 的 append 操作也行)

1
2
3
for child in (new_child,exsit_child):
if child in father.children:
father.children.append(child)

写 SQLAlchemy 更接近操作放在数据库里面的数据记录,而 DjangoORM 更接近操作一批放在数据库里面的对象。

由于 session 的使用姿势不同,所以往往会有很多使用上面的区别。

至于孰优孰劣,难以评判。

  • 技术老大 (Flask 和 React 大神)倾向于使用 SQLAlchemy, 他认为

    • 对于一个技术『知其然,知其所以然』
    • 对于 ORM
      • 操作数据库,操作最好要落实在成 SQL
      • 如果有可能的话,每一个 SQL 语句都要经过推敲,而且写这个 SQL 和 ORM 过程要反复练习
    • 对于 Migration 机制
      • Alembic 这个迁移工具是为了省事用的,甚至在某些情况下没必要用。完全可以写 SQL 代替
  • 我 (Django 和 Vue 弱鸡)倾向于使用 DjangoORM, 我认为

    • 对于一个技术『先知其大致然,需要深入的时候知其所以然』
    • 对于 ORM
      • 操作数据,最好抽象为对对象的操作。
      • 测试到位的情况下,快糙狠先出东西。到需要优化的时候该怎么 Profile 怎么 Profile
    • 对于 Migration 机制
      • 用起来啊,能操作对象为什么还要强行到数据库操作?

2.1 模型定义

先看一组模型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base() # 模型基类
from sqlalchemy import Column, Integer, String
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True) # 主键
name = Column(String)
fullname = Column(String)
password = Column(String)
def __repr__(self):
return "<User(name='%s', fullname='%s', password='%s')>" % (
self.name, self.fullname, self.password)
Base.metadata.create_all(engine)

可以看出,包含如下的部分:

  1. Model 与 Model 内部的 Meta
  2. Field 与 Field 内部的 Options
  3. Model 与 Model 之间的关系
  4. 其他,比如索引

Models 与 Meta

https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/schema.py#L3685

关系

One To Many

母亲有若干个孩子,外键在孩子上。

1
2
3
4
5
6
7
class Parent(Base):
#...
children = relationship("Child", backref="parent")
class Child(Base):
#...
parent_id = Column(Integer, ForeignKey('parent.id'))
Many To One

多个母亲共享一个孩子,外键在母亲上。

1
2
3
4
5
6
class Parent(Base):
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child")
class Child(Base):
# ...
One To One

One to One 是 One to Many 或者是 Many to One 的简化版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# Many To One
class Parent(Base):
# ...
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child", backref=backref("parent", uselist=False))
class Child(Base):
# ...
# One To Many 改 One To One
class Parent(Base):
# ...
class Child(Base):
# ...
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship("Parent", backref=backref("child", uselist=False))
Many To Many
1
2
3
4
5
6
7
8
9
10
11
12
13
association_table = Table('association', Base.metadata,
Column('left_id', Integer, ForeignKey('left.id')),
Column('right_id', Integer, ForeignKey('right.id'))
)
class Parent(Base):
# ...
children = relationship("Child",
secondary=association_table,
backref="parents")
class Child(Base):
# ...

注意事项

执行删除 mapping 表的时候尽量这样。

1
myparent.children.remove(somechild)

当你想干掉 somechild 的时候,会执行

1
session.delete(somechild)
  1. 假如 Child 没有 ref Parent 的话,Secondary Table 无删除,则无法删除。
  2. 假如 ref 了的话,则删除 secondary 里面的记录。
  3. TODO
邻接列表关系
1
2
3
4
5
6
7
8
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('node.id'))
data = Column(String(50))
children = relationship("Node",
backref=backref('parent', remote_side=[id])
)
relationship 详解

2.1 Query

Create

1
2
3
4
5
6
7
8
9
c1 = Child(name="苏轼")
session.add(c1)
session.flush()
p = Parent(name="苏辙")
p.best_child = c1
for c in [c1,c2,c3,c4]:
p.children.append(c)
session.add(c1)
session.commit()

Retrieve

过滤

filter(**kwargs)
filter(Singer.name == “周杰伦”)
filter(Singer.name =! “周杰棍”)

跨关系(跨表)查询

session.query(Entry).join(Blog,Blog.entry_id == Entry.id).filter(Blog.name = “SqlAlchemy CheatSheet”)

Limit / Offset / 分页
  • limit()
  • offset()
链式调用
1
2
query = session.query(Order) # query = session.query(Order)
query = query.filter(Order.name.like(f"%name%"))
二进制表达式

我们先 type 一下表达式,找到 eq 的类型

1
type(model.column_name == 'asdf') → sqlalchemy.sql.elements.BinaryExpression

是一个二进制表达式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 等于
query.filter(User.name == 'ed')
# 不等于
query.filter(User.name != 'ed')
# Like(有的数据库不区分大小写)
query.filter(User.name.like('%ed%'))
# ILIKE (case-insensitive LIKE)
query.filter(User.name.ilike('%ed%'))
# IN
query.filter(User.name.in_(['ed', 'wendy', 'jack']))
# Not in
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
# IS NULL
query.filter(User.name == None)
## 如果你用了 pep8/linter 的话
query.filter(User.name.is_(None))
# IS NOT NULL:
query.filter(User.name != None)
## 如果你用了 pep8/linter 的话
query.filter(User.name.isnot(None))
# AND
## use 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')
# OR
query.filter(or_(User.name == 'ed', User.name == 'wendy'))
# MATCH
query.filter(User.name.match('wendy'))
执行查询
1
2
3
4
5
all()
first()
one()
one_or_none()
scalar()

YourModel.query.get((pk1, pk2))

比较

同一个 Session 下面,取到的某一条数据对应的 objects 应该是一样的?

复制 实例
其他
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 查询的是 SomeModel 里面所有的字段 即 select *
query = session.query(SomeModel)
# 查询的是 SomeModel 里面部分的字段 即 select acol, bcol
query = session.query(SomeModel.acol,SomeModel.bcol)
# 即 select acol , bcol
# alias
user_alias = aliased(User, name='user_alias')
for row in session.query(user_alias, user_alias.name).all():
# 即相当于 select name as name_label
print(row.user_alias)
# limit 和 offset
for u in session.query(User).order_by(User.id)[1:3]:
print(u)
# distinct
session.query(model.Name).distinct(model.Name.value).order_by(model.Name.value)
# order_by
User.query.order_by(User.popularity.desc(),User.date_created.desc()).limit(10).all()

Update

单个 object 更新

1
2
3
blog.title = "大宝天天见"
session.add(blog)
session.commit()

批量更新

1
2
3
session.query.filter(Blog.content.like("% 敏感词 %")).update({
Blog.content: "依照相关 XX 无法查看"
})

一对多的更新

1
append

Delete

query.delete()

JOIN

https://stackoverflow.com/questions/6044309/sqlalchemy-how-to-join-several-tables-by-one-query

两表 InnerJoin
1
2
3
4
5
6
7
8
for u, a in session.query(User, Address).\
filter(User.id==Address.user_id).\
filter(Address.email_address=='jack@google.com').\
all():
print(u)
print(a)
# <User(name='jack', fullname='Jack Bean', password='gjffdd')>
# <Address(email_address='jack@google.com')>
多表 InnerJoin + LeftJoin
1
query.outerjoin(User.addresses) # LEFT OUTER JOIN

聚集查询

1
session.query(User).filter(User.name.like('%ed')).count()
1
2
3
4
5
6
7
from sqlalchemy import func
session.query(Table.column, func.count(Table.column)).group_by(Table.column).all()
self.session.query(func.count(Table.column1),Table.column1, Table.column2).group_by(Table.column1, Table.column2).all()
from sqlalchemy.sql import func
session.query(func.avg(Rating.field2).label('average')).filter(Rating.url==url_string.netloc)

缓存机制

Query 对象,下文中,我会聊到这个 Query 对象。这里先跳过。

2.2 原生查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
from sqlalchemy import text
sql = text('select name from penguins')
result = db.engine.execute(sql)
names = []
for row in result:
names.append(row[0])
print names
from collections import namedtuple
Record = namedtuple('Record', result.keys())
records = [Record(*r) for r in result.fetchall()]
for r in records:
print(r)
from sqlalchemy.sql import text
connection = engine.connect()
# recommended
cmd = 'select * from Employees where EmployeeGroup == :group'
employeeGroup = 'Staff'
employees = connection.execute(text(cmd), group = employeeGroup)

get_or_create

1
2
3
4
5
6
7
8
9
10
def get_or_create(session, model, defaults=None, **kwargs):
instance = session.query(model).filter_by(**kwargs).first()
if instance:
return instance, False
else:
params = dict((k, v) for k, v in kwargs.iteritems() if not isinstance(v, ClauseElement))
params.update(defaults or {})
instance = model(**params)
session.add(instance)
return instance, True

2.3 更新查询

1
session.query(Stuff).update({Stuff.foo: Stuff.foo + 1})
1
2
3
4
5
6
7
8
9
10
11
12
1) for c in session.query(Stuff).all():
c.foo += 1
session.commit()
2) session.query().\
update({"foo": (Stuff.foo + 1)})
session.commit()
3) conn = engine.connect()
stmt = Stuff.update().\
values(Stuff.foo = (Stuff.foo + 1))
conn.execute(stmt)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1) user.no_of_logins += 1
session.commit()
2) session.query().\
filter(User.username == form.username.data).\
update({"no_of_logins": (User.no_of_logins +1)})
session.commit()
3) conn = engine.connect()
stmt = User.update().\
values(no_of_logins=(User.no_of_logins + 1)).\
where(User.username == form.username.data)
conn.execute(stmt)
4) setattr(user, 'no_of_logins', user.no_of_logins+1)
session.commit()

2.4 删除

https://stackoverflow.com/questions/5033547/sqlalchemy-cascade-delete

OnDelete

ondelete=’CASCADE’))

批量操作

models.User.query.delete()

如何从 Object 到一个 ORM

如何追踪 Object 的变化?

0x03 SQLAlchemy 的高级特性

表继承

https://stackoverflow.com/questions/1337095/sqlalchemy-inheritance

啥玩意

Flush 和 commit

https://stackoverflow.com/questions/4201455/sqlalchemy-whats-the-difference-between-flush-and-commit

1
2
3
4
5
6
7
8
9
x = Foo(bar=1)
print x.id
# None
session.add(x)
session.flush()
# BEGIN
# INSERT INTO foo (bar) VALUES(1)
# COMMIT
print x.id
1
2
qry = DBSession.query(User).filter(
and_(User.birthday <= '1988-01-17', User.birthday >= '1985-01-17'))

0x04 SQLAlchemy 的基础特性 Under The Hood

Loading 策略

Lazy Loading

Eager Loading

1
2
3
>>> from sqlalchemy.dialects import postgresql
>>> print str(q.statement.compile(dialect=postgresql.dialect()))
Where q is defined as:

0x05 SQLAlchemy 的高级特性 Under The Hood

多线程

https://stackoverflow.com/questions/6297404/multi-threaded-use-of-sqlalchemy
https://stackoverflow.com/questions/9619789/sqlalchemy-proper-session-handling-in-multi-thread-applications

1
2
3
4
5
6
7
8
9
10
11
12
13
https://stackoverflow.com/questions/34322471/sqlalchemy-engine-connection-and-session-difference
https://stackoverflow.com/questions/11769366/why-is-sqlalchemy-insert-with-sqlite-25-times-slower-than-using-sqlite3-directly
https://stackoverflow.com/questions/12223335/sqlalchemy-creating-vs-reusing-a-session
session 是个容器
https://stackoverflow.com/questions/18199053/example-of-what-sqlalchemy-can-do-and-django-orm-cannot
https://stackoverflow.com/questions/7389759/memory-efficient-built-in-sqlalchemy-iterator-generator
# 日志
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

0x06 DEBUG 和 Profile 技巧

1
SQLALCHEMY_TRACK_MODIFICATIONS = False

6.1 查看技巧

dict(u)
u.dict

https://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application

如果用上 Flask+SQLAlchemy 一般也要带上,Flask-Migration 与 Flask-SQLAlchemy, 这两个库也是对 Alembic 和 SQLAlchemy 的浅封装。

那么,对于这个 ORM 库还有那些通用性的知识需要了解?

嗯,是时候了解本质了。

http://derrickgilland.com/posts/demystifying-flask-sqlalchemy/

0x07 ORM 的本质

ORM 的本质是 Data Access Layer 上的一层封装。如果你写原生 SQL, 即手写 DAL 的话,开发效率可能会大打折扣。

ORM 的两种类型 Active Record 与 Data Mappers

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# ActiveRecord 风格写起来类似于 Django ORM, 大致是这样的
## AR 的模型定义
class User(db.models):
name = db.StringField(verbose="xyz")
## AR 的新增
user = User()
user.name = "123456"
user.save() ## 正好对应数据库中的一行
## AR 的查询
users = User.objects.filter(Q(name="黄老板的小姨子")).all()
# Data Mappers 风格写起来类似于 SQLAlchemy ORM, 大致是这样的
## SA 的定义
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
Base.metadata.create_all(engine)
## SA 的新增
user = User()
user.name = "123456"
session.add(user)
sessoon.commit() ## 嗯?其实也是对应数据库中的一行。
## SA 的查询
session.query(User).filter(User.name)

问题来了,这两者到底是什么,看起来似乎相差不大。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
class Person:
lastname
firstname
children
# 数据操作
def findone(self):
pass
def insert(self):
pass
def update(self):
pass
def delete(self):
pass
# 业务逻辑
def getChildrenTax(self):
pass

lastName firstName numberOfDependents

insert update delete

getExemption isFlaggedForAudit getTaxableEarnings

An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data.

The essence of an Active Record is a Domain Model (116) in which the classes match very closely the record structure of an underlying database. Each Active Record is responsible for saving and loading to the database and also for any domain logic that acts on the data. This may be all the domain logic in the application, or you may find that some domain logic is held in Transaction Scripts (110) with common and data-oriented code in the Active Record.

The data structure of the Active Record should exactly match that of the database: one field in the class for each column in the table. Type the fields the way the SQL interface gives you the data—don’t do any conversion at this stage. You may consider Foreign Key Mapping (236), but you may also leave the foreign keys as they are. You can use views or tables with Active Record, although updates through views are obviously harder. Views are particularly useful for reporting purposes.

objects correspond directly to the database tables: an isomorphic schema. If your business logic is complex, you’ll soon want to use your object’s direct relationships, collections, inheritance, and so forth. These don’t map easily onto Active Record, and adding them piecemeal gets very messy. That’s what will lead you to use Data Mapper (165) instead.

Another argument against Active Record is the fact that it couples the object design to the database design. This makes it more difficult to refactor either design as a project goes forward.

Active Record is a good pattern to consider if you’re using Transaction Script (110) and are beginning to feel the pain of code duplication and the difficulty in updating scripts and tables that Transaction Script (110) often brings. In this case you can gradually start creating Active Records and then slowly refactor behavior into them. It often helps to wrap the tables as a Gateway (466) first, and then start moving behavior so that the tables evolve to a Active Record.

其实为什么不选择设计成 ActiveRecord , 而是选择设计成 Data Mapper, 其实就可以回答这个问题:

虽然要设计成 ORM, 考虑到数量和性能因素,SQL 数据库(多个表)并不应该是表现像 Object 集合那样(换言之,也就是 AR 表现的像 Object 的集合一样)。
同时,出于更好的抽象,object 集合也应该表现的像表以及行

于是我们可以得出结论,可以在 SQLAlchemy 上面进行一定的封装,使得最后用起来非常的 Django ORM like,其实 SQLAlchemy 稍加定制还是可以很 Django ORM-like 的。

:TODO: 有机会看看那本书再修改一下本小节

这不,果然有人就这么搞了 https://github.com/absent1706/sqlalchemy-mixins

0x09 踩坑集

关系持久化坑

  1. Rows that point to themselves : 比如一个 insert 一个推荐自己的用户,则需要保存 id / ref_id , 但是在这个 user 插入之前,并不存在 id. 所以,一般情况下是先 insert, 然后保存 ref_id
  2. Mutually Dependent Rows

SQL 注入

session.query(MyClass).filter(“foo={}”.format(getArgs[‘val’]))

0xEE 参考链接


ChangeLog:

  • 2018-03-09 重修文字