sqlalchemy

　　　　　　　　　　　　　　　　　　query

此文算是自己的一个总结，不敢说对sqlalchemy有多精通，只能算是入门的总结，免得后面忘记了这些个基本的东西。数据库的增，删，改，查，前面已经介绍了session的增，删，改，现在来介绍session的查，当然还有expression的查，这个就留到后面的文章来总结了。

同样是由简入难

 1 from sqlalchemy import create_engine
 2 
 3 engine = create_engine("mysql+pymysql://root:max123@127.0.0.1/test?charset=utf8", echo=True)
 4 
 5 from sqlalchemy import *
 6 from sqlalchemy.orm import relationship
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from datetime import datetime, date
 9 from sqlalchemy.orm import deferred
10 Base = declarative_base()
11 
12 
13 class Parent(Base):
14     __tablename__ = 'parent'
15 
16     id = Column(Integer(), primary_key=True)
17     name = Column(String(50))
18     summary = deferred(Column(Text))
19     children = relationship('ParentChild', back_populates='parent', cascade='all,delete-orphan')
20 
21 
22 class Child(Base):
23     __tablename__ = 'child'
24 
25     id = Column(Integer(), primary_key=True)
26     name = Column(String(50))
27     parents = relationship('ParentChild', back_populates='child')
28 
29 
30 class ParentChild(Base):
31     __tablename__ = 'parent_child'
32 
33     id = Column(Integer(), primary_key=True)
34     child_id = Column(Integer(), ForeignKey('child.id'), nullable=False)
35     parent_id = Column(Integer(), ForeignKey('parent.id'), nullable=False)
36     description = Column(String(100))
37 
38     parent = relationship('Parent', back_populates='children')
39     child = relationship('Child', back_populates='parents')
40 
41 
42 Base.metadata.drop_all(engine)
43 Base.metadata.create_all(engine)
44 
45 
46 from sqlalchemy.orm import sessionmaker
47 
48 Session = sessionmaker(bind=engine)
49 
50 db = Session()
51 
52 child_one = Child(name='purk1')
53 child_two = Child(name='purk2')
54 child_three = Child(name='purk3')
55 child_four = Child(name='purk4')
56 parent_one = Parent(name='Wu1')
57 parent_two = Parent(name='Wu2')
58 parent_child_one = ParentChild(description='association one')
59 parent_child_two = ParentChild(description='association two')
60 parent_child_one.child = child_one
61 parent_child_two.child = child_two
62 parent_one.children.extend([parent_child_one, parent_child_two])
63 
64 db.add_all([parent_one, parent_two, child_four])
65 db.commit()

wfOrzbai

1.delete

有依赖关系的是不能删除的。

1 db.query(Parent).filter_by(name='wu1').delete(synchronize_session=False)

由于parent.id是ParentChild.parent_id的外键，直接使用这种方式删除就会报错了。不过可以使用session.delete(),此方式是可以处理relationship里面的配置，如果配置了cascade='all,delete-orphan'，就会去删除从表的数据了。

1 parent_child = db.query(ParentChild).filter_by(parent_id=1).all()
2 db.query(ParentChild).filter_by(parent_id=1).delete(synchronize_session='fetch')
3 db.commit()
4 print(parent_child[0].id)

这样是可以删除没有依赖的表数据，但是synchronize_session到底有什么用，鄙人看文档也没看懂，希望高人来解答下

2. update

　　　-> update(values, synchronize_session=’evaluate’, update_args=None)

1 db.query(Parent).filter_by(id=1).update({'name': Parent.name + '_update'}, synchronize_session=False)
2 db.query(Parent).filter_by(id=1).update({Parent.name: Parent.name + '_update'}, synchronize_session=False)
3 db.query(Parent).filter_by(id=2).update({Parent.name: case([(Parent.name == 'wu1', 'wu1_wu1')], else_='wu2_wu2')}, synchronize_session=False)
4 db.commit()

这两种方式都可以

然后这个synchronize_session参数的一些含义，和delete一样，也是没有搞懂。

3. query

1) count query的方法，和func.count有点区别

 1 db.query(Parent).count()
 2 >>SELECT count(*) AS count_1 
 3 >>FROM (SELECT parent.id AS parent_id, parent.name AS >>parent_name FROM parent) AS anon_1
 4 
 5 >> 2
 6 
 7 db.query(func.count(1).label('cnt')).select_from(Parent).all()
 8 >>SELECT count(%(count_1)s) AS cnt FROM parent
 9 
10 >>[(2,)]

count是query的方法，其返回的结果是integer，sql是子查询，

func.count有一个参数，sql利用聚合函数。且返回的结果是list对象。

　　2） distinct 一个是query的方法，还有一个就是sqlalchemy的方法

 1 db.query(Parent).distinct().all()
 2 >>SELECT DISTINCT parent.id AS parent_id, parent.name AS parent_name FROM parent
 3 
 4 >>[<__main__.Parent object at 0x0363B1B0>, <__main__.Parent object at 0x0363B550>]
 5 
 6 
 7 db.query(distinct(Parent.name)).all()
 8 >>SELECT DISTINCT parent.name AS anon_1 FROM parent
 9 
10 >>[('Wu1',), ('Wu2',)]

这两个方法的不同之处是query的distinct不需要参数，sqlclchemy的distinct需要传入参数。

3）exsits()

这个好像没有什么特别的，看下面的代码和sql log就明白了。

 1 q = db.query(Parent).filter(Parent.name=='wu1')
 2 
 3 db.query(q.exists()).all()
 4 >>SELECT EXISTS (SELECT 1 FROM parent WHERE parent.name = %(name_1)s) AS anon_1
 5 
 6 >>[(True,)]
 7 
 8 
 9 db.query(Parent.name).filter(q.exists()).all()
10 >>SELECT parent.name AS parent_name FROM parent WHERE EXISTS (SELECT 1 FROM parent WHERE parent.name = %(name_1)s)
11 
12 >>[('Wu1',), ('Wu2',)]

　　4) filter() 这个就牛X了

 1 db.query(Parent).filter(Parent.name=='wu1').all()
 2 >>> SELECT parent.id AS parent_id, parent.name AS parent_name 
 3 >>> FROM parent 
 4 >>> WHERE parent.name = %(name_1)s
 5 >>> {'name_1': 'wu1'}
 6 >>> [<__main__.Parent object at 0x0363B1B0>]

 7 db.query(Parent).filter(Parent.name == 'wu1',Parent.name.like('Wu%')).all()
 8 >>> SELECT parent.id AS parent_id, parent.name AS parent_name 
 9 >>> FROM parent 
10 >>> WHERE parent.name = %(name_1)s AND parent.name LIKE %(name_2)s
11 >>> {'name_2': 'Wu%', 'name_1': 'wu1'}
12 >>> [<__main__.Parent object at 0x0363B1B0>]

13 db.query(Parent).filter(or_(Parent.name=='wu1',Parent.name=='wu2')).all()
14 >>> SELECT parent.id AS parent_id, parent.name AS parent_name 
15 >>> FROM parent 
16 >>> WHERE parent.name = %(name_1)s OR parent.name = %(name_2)s
17 >>> {'name_2': 'wu2', 'name_1': 'wu1'}
18 >>> [<__main__.Parent object at 0x0363B1B0>, <__main__.Parent object at 0x0363B550>]

19 db.query(Parent).filter(Parent.name=='wu1',Parent.id>1).all() 
20 >>> SELECT parent.id AS parent_id, parent.name AS parent_name WHERE parent.name = %(name_1)s AND parent.id > %(id_1)s 
21 >>> []

22 db.query(Parent).filter(Parent.name!='wu1',Parent.id>1).all()
23 >>> SELECT parent.id AS parent_id, parent.name AS parent_name FROM parent WHERE parent.name != %(name_1)s AND parent.id > %(id_1)s
24 >>> {'id_1': 1, 'name_1': 'wu1'}
25 >>> [<__main__.Parent object at 0x0363B550>]
########################################下面这个实在对查询出来的字段进行操作，真的是只有你想不到的，没有他做不到的。
26 db.query(Parent.name +','+Parent.name,literal('developer').label('role'),case([(Parent.name=='wu1','is wu1'),(Parent.name=='wu2','is wu2')],else_='everything').label('case_column')).all()
27 >>>SELECT concat(concat(parent.name, %(name_1)s), parent.name) AS anon_1, %(param_1)s AS role, CASE WHEN (parent.name = %(name_2)s) THEN %(param_2)s WHEN (parent.name = %(name_3)s) THEN %(param_3)s ELSE %(param_4)s END AS case_column FROM parent
28 >>> {'name_2': 'wu1', 'param_3': 'is wu2', 'name_3': 'wu2', 'param_4': 'everything', 'name_1': ',', 'param_2': 'is wu1', 'param_1': 'developer'}
29 >>> [('Wu1,Wu1', 'developer', 'is wu1'), ('Wu2,Wu2', 'developer', 'is wu2')]

　　5) filter_by()

　　　　相较filter，就没有那么灵活了，本身的便捷之处在于可以少些一个前缀（Parent）,但是很多功能

>>> db.query(Parent).filter_by(name='wu1',id=1).all()
SELECT parent.id AS parent_id, parent.name AS parent_name 
FROM parent 
WHERE parent.id = %(id_1)s AND parent.name = %(name_1)s
{'id_1': 1, 'name_1': 'wu1'}
[<__main__.Parent object at 0x0363B1B0>]

>>> db.query(Parent).filter_by(name='wu1',name='wu2').all()   # 'name'关键字一样 报错
SyntaxError: keyword argument repeated

>>> db.query(Parent).filter_by(or_(name='wu1',id=1)).all()    # or_() 报错
Traceback (most recent call last):
  File "<pyshell#45>", line 1, in <module>
    db.query(Parent).filter_by(or_(name='wu1',id=1)).all()
TypeError: or_() got an unexpected keyword argument 'name'


>>> db.query(Parent).filter_by(name='wu1',id>1).all()         # '>' 报错
SyntaxError: non-keyword arg after keyword arg

>>> db.query(Parent).filter_by(name='wu1',id=1).all()         
SELECT parent.id AS parent_id, parent.name AS parent_name 
FROM parent 
WHERE parent.id = %(id_1)s AND parent.name = %(name_1)s
{'id_1': 1, 'name_1': 'wu1'}
[<__main__.Parent object at 0x0363B1B0>]

　　6）group_by(*criterion) and having(criterion)

1 >>> db.query(Parent.name).join(Parent.children).group_by(Parent.id).having(func.count(Parent.id)>1).all()
2 >>> SELECT parent.name AS parent_name FROM parent INNER JOIN parent_child ON parent.id = parent_child.parent_id GROUP BY parent.id HAVING count(parent.id) > %(count_1)s
3 >>> {'count_1': 1}
4 >>> [('Wu1',)]

　　7) limit(limit) and offset(offset)

1 >>> db.query(Parent).limit(1).offset(0).all()
2 >>> SELECT parent.id AS parent_id, parent.name AS parent_name FROM parent LIMIT %(param_1)s, %(param_2)s
3 >>> {'param_2': 1, 'param_1': 0}
4 >>> [<__main__.Parent object at 0x037CB310>]

limit 返回的行数 offset 其实偏移位置

　　8) order_by(*criterion)　　

1 >>> db.query(Parent).order_by(Parent.id,Parent.name).all()
2 >>> SELECT parent.id AS parent_id, parent.name AS parent_name FROM parent ORDER BY parent.id, parent.name
3 >>> [<__main__.Parent object at 0x037BB6B0>, <__main__.Parent object at 0x037BB790>]

　　9) join(*props,**kwargs) and outerjoin(*props,**kwargs) 重点

以下的join关联需要 relationship的配置（必备条件）

 1 db.query(Parent).join(Parent.children).all()
 2 >>> SELECT parent.id AS parent_id, parent.name AS parent_name 
 3 FROM parent INNER JOIN parent_child ON parent.id = parent_child.parent_id
 4 
 5 db.query(Parent).join(ParentChild).all()
 6 >>> SELECT parent.id AS parent_id, parent.name AS parent_name 
 7 FROM parent INNER JOIN parent_child ON parent.id = parent_child.parent_id
 8 
 9 db.query(Parnet).join('ParentChild').all() #报错
10 db.query(Parent).join('children').all() #说明join(str)时，str表示的relationship的配置，而不是被关联对象的类名
11 >>> SELECT parent.id AS parent_id, parent.name AS parent_name 
12 FROM parent INNER JOIN parent_child ON parent.id = parent_child.parent_id
13 
14 db.query(ParentChild).join('parent','child').all()   #报错,说明这种方式的join是从左至右关联的即 ParentChild.parent > Parent.child
15 >>> AttributeError: type object 'Parent' has no attribute 'child'
16 
17 db.query(ParentChild).join(ParentChild.parent,ParentChild.child).all() #这种方式就是与顺序无关了，
18 >>> SELECT parent_child.id AS parent_child_id, parent_child.child_id AS parent_child_child_id, parent_child.parent_id AS parent_child_parent_id, parent_child.description AS parent_child_description 
19 FROM parent_child INNER JOIN parent ON parent.id = parent_child.parent_id INNER JOIN child ON child.id = parent_child.child_id
20 
21 parent_child = db.query(ParentChild).subquery()
22 db.query(Parent).join(parent_child).all()
23 >>> SELECT parent.id AS parent_id, parent.name AS parent_name 
24 FROM parent INNER JOIN (SELECT parent_child.id AS id, parent_child.child_id AS child_id, parent_child.parent_id AS parent_id, parent_child.description AS description 
25 FROM parent_child) AS anon_1 ON parent.id = anon_1.parent_id

以下是join万能关联

db.query(Parent).join(ParentChild,Parent.id==ParentChild.parent_id).all()
>>> SELECT parent.id AS parent_id, parent.name AS parent_name 
FROM parent INNER JOIN parent_child ON parent.id = parent_child.parent_id

db.query(Parent).join(ParentChild,or_(Parent.id==ParentChild.parent_id,ParentChild.description=='test')).all()

>>> SELECT parent.id AS parent_id, parent.name AS parent_name 
FROM parent INNER JOIN parent_child ON parent.id = parent_child.parent_id OR parent_child.description = %(description_1)s

db.query((Parent.name + Parent.summary).label('test_join'),case([(Parent.name=='wu1','find_wu1'),(Parent.name=='wu2','find_wu2')],else_='nothing').label('test_case')).join(ParentChild,ParentChild.parent_id==Parent.id).all()
>>> SELECT concat(parent.name, parent.summary) AS test_join, CASE WHEN (parent.name = %(name_1)s) THEN %(param_1)s WHEN (parent.name = %(name_2)s) THEN %(param_2)s ELSE %(param_3)s END AS test_case 
FROM parent INNER JOIN parent_child ON parent_child.parent_id = parent.id

#####别名的使用，这样可以join同一个表多次######
from sqlalchemy.orm import aliased
parent_child_alias  = aliased(ParentChild)
db.query(Parent).join(ParentChild,Parent.id == ParentChild.parent_id).join(parent_child_alias,parent_child_alias.parent_id==Parent.id).all()
>>> SELECT parent.id AS parent_id, parent.name AS parent_name 
FROM parent INNER JOIN parent_child ON parent.id = parent_child.parent_id INNER JOIN parent_child AS parent_child_1 ON parent_child_1.parent_id = parent.id

最后一个内容就是返回query的结果集

　　10) all() first() one() scalar() get() one_or_none()

　　　　all: 返回所有数据

　　　　first: 所有rows里面取第一行，如果为没有结果则为None,不会抛异常

　　　　one: 返回的rows必须是只有一行，有多行或者没有数据都会抛异常

　　　　one_or_none：返回的rows必须是一行或者没有数据，有多行数据就会抛异常

　　　　scalar：返回的rows必须是一行或者没有数据，有多行数据就会抛异常，并且scalar只会取result里面的第一个结果集

1 db.query(Parent).filter(Parent.name=='wu1').scalar()    #取到的是Parent对象
2 >>> <__main__.Parent object at 0x0368BA10>
3 
4 db.query(Parent.name,Parent.id).filter(Parent.name=='wu1').scalar()   #取到的是只是Wu1
5 >>> 'Wu1'

　　11) cte() subquery()

　　　　cte和subquery返回的都是selecttables,表达是的访问需要用 .c. attribute 。

　　12) from_self()

print(db.query(Parent).from_self())

>>> SELECT anon_1.parent_id AS anon_1_parent_id, anon_1.parent_name AS anon_1_parent_name 
FROM (SELECT parent.id AS parent_id, parent.name AS parent_name 
FROM parent) AS anon_1

db.query(Parent).from_self().join(ParentChild).all()

>>> SELECT anon_1.parent_id AS anon_1_parent_id, anon_1.parent_name AS anon_1_parent_name 
FROM (SELECT parent.id AS parent_id, parent.name AS parent_name 
FROM parent) AS anon_1 INNER JOIN parent_child ON anon_1.parent_id = parent_child.parent_id

　　13) select_from() 在不想要parent的数据只想要ParentChild时，当然在多表关联且只要部分字段时就很有实用价值了。

db.query(ParentChild).select_from(Parent).join(ParentChild).all()

>>> SELECT parent_child.id AS parent_child_id, parent_child.child_id AS parent_child_child_id, parent_child.parent_id AS parent_child_parent_id, parent_child.description AS parent_child_description 
FROM parent INNER JOIN parent_child ON parent.id = parent_child.parent_id