sqlalchemy - day4

2023-08-23 08:28:28

　　　　　　　　　　　　　　　　　　query

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

同样是由简入难

 from sqlalchemy import create_engine

 engine = create_engine("mysql+pymysql://root:max123@127.0.0.1/test?charset=utf8", echo=True)

 from sqlalchemy import *

 from sqlalchemy.orm import relationship

 from sqlalchemy.ext.declarative import declarative_base

 from datetime import datetime, date

 from sqlalchemy.orm import deferred

 Base = declarative_base()

 class Parent(Base):

     __tablename__ = 'parent'

     id = Column(Integer(), primary_key=True)

     name = Column(String(50))

     summary = deferred(Column(Text))

     children = relationship('ParentChild', back_populates='parent', cascade='all,delete-orphan')

 class Child(Base):

     __tablename__ = 'child'

     id = Column(Integer(), primary_key=True)

     name = Column(String(50))

     parents = relationship('ParentChild', back_populates='child')

 class ParentChild(Base):

     __tablename__ = 'parent_child'

     id = Column(Integer(), primary_key=True)

     child_id = Column(Integer(), ForeignKey('child.id'), nullable=False)

     parent_id = Column(Integer(), ForeignKey('parent.id'), nullable=False)

     description = Column(String(100))

     parent = relationship('Parent', back_populates='children')

     child = relationship('Child', back_populates='parents')

 Base.metadata.drop_all(engine)

 Base.metadata.create_all(engine)

 from sqlalchemy.orm import sessionmaker

 Session = sessionmaker(bind=engine)

 db = Session()

 child_one = Child(name='purk1')

 child_two = Child(name='purk2')

 child_three = Child(name='purk3')

 child_four = Child(name='purk4')

 parent_one = Parent(name='Wu1')

 parent_two = Parent(name='Wu2')

 parent_child_one = ParentChild(description='association one')

 parent_child_two = ParentChild(description='association two')

 parent_child_one.child = child_one

 parent_child_two.child = child_two

 parent_one.children.extend([parent_child_one, parent_child_two])

 db.add_all([parent_one, parent_two, child_four])

 db.commit()

wfOrzbai

1.delete

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

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

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

 parent_child = db.query(ParentChild).filter_by(parent_id=1).all()

 db.query(ParentChild).filter_by(parent_id=1).delete(synchronize_session='fetch')

 db.commit()

 print(parent_child[0].id)

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

2. update

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

 db.query(Parent).filter_by(id=1).update({'name': Parent.name + '_update'}, synchronize_session=False)

 db.query(Parent).filter_by(id=1).update({Parent.name: Parent.name + '_update'}, synchronize_session=False)

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

 db.commit()

这两种方式都可以

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

3. query

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

 db.query(Parent).count()

 >>SELECT count(*) AS count_1

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

 >> 2

 db.query(func.count(1).label('cnt')).select_from(Parent).all()

 >>SELECT count(%(count_1)s) AS cnt FROM parent

 >>[(2,)]

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

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

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

 db.query(Parent).distinct().all()

 >>SELECT DISTINCT parent.id AS parent_id, parent.name AS parent_name FROM parent

 >>[<__main__.Parent object at 0x0363B1B0>, <__main__.Parent object at 0x0363B550>]

 db.query(distinct(Parent.name)).all()

 >>SELECT DISTINCT parent.name AS anon_1 FROM parent

 >>[('Wu1',), ('Wu2',)]

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

3）exsits()

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

 q = db.query(Parent).filter(Parent.name=='wu1')

 db.query(q.exists()).all()

 >>SELECT EXISTS (SELECT 1 FROM parent WHERE parent.name = %(name_1)s) AS anon_1

 >>[(True,)]

 db.query(Parent.name).filter(q.exists()).all()

 >>SELECT parent.name AS parent_name FROM parent WHERE EXISTS (SELECT 1 FROM parent WHERE parent.name = %(name_1)s)

 >>[('Wu1',), ('Wu2',)]

　　4) filter() 这个就牛X了

 db.query(Parent).filter(Parent.name=='wu1').all()

 >>> SELECT parent.id AS parent_id, parent.name AS parent_name

 >>> FROM parent

 >>> WHERE parent.name = %(name_1)s

 >>> {'name_1': 'wu1'}

 >>> [<__main__.Parent object at 0x0363B1B0>]

 db.query(Parent).filter(Parent.name == 'wu1',Parent.name.like('Wu%')).all()

 >>> SELECT parent.id AS parent_id, parent.name AS parent_name

 >>> FROM parent

 >>> WHERE parent.name = %(name_1)s AND parent.name LIKE %(name_2)s

 >>> {'name_2': 'Wu%', 'name_1': 'wu1'}

 >>> [<__main__.Parent object at 0x0363B1B0>]

 db.query(Parent).filter(or_(Parent.name=='wu1',Parent.name=='wu2')).all()

 >>> SELECT parent.id AS parent_id, parent.name AS parent_name

 >>> FROM parent

 >>> WHERE parent.name = %(name_1)s OR parent.name = %(name_2)s

 >>> {'name_2': 'wu2', 'name_1': 'wu1'}

 >>> [<__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 >>> []

 db.query(Parent).filter(Parent.name!='wu1',Parent.id>1).all()

 >>> 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

 >>> {'id_1': 1, 'name_1': 'wu1'}

 >>> [<__main__.Parent object at 0x0363B550>]
########################################下面这个实在对查询出来的字段进行操作，真的是只有你想不到的，没有他做不到的。

 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()

 >>>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

 >>> {'name_2': 'wu1', 'param_3': 'is wu2', 'name_3': 'wu2', 'param_4': 'everything', 'name_1': ',', 'param_2': 'is wu1', 'param_1': 'developer'}

 >>> [('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)

 >>> db.query(Parent.name).join(Parent.children).group_by(Parent.id).having(func.count(Parent.id)>1).all()

 >>> 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

 >>> {'count_1': 1}

 >>> [('Wu1',)]

　　7) limit(limit) and offset(offset)

 >>> db.query(Parent).limit(1).offset(0).all()

 >>> SELECT parent.id AS parent_id, parent.name AS parent_name FROM parent LIMIT %(param_1)s, %(param_2)s

 >>> {'param_2': 1, 'param_1': 0}

 >>> [<__main__.Parent object at 0x037CB310>]

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

　　8) order_by(*criterion)　　

 >>> db.query(Parent).order_by(Parent.id,Parent.name).all()

 >>> SELECT parent.id AS parent_id, parent.name AS parent_name FROM parent ORDER BY parent.id, parent.name

 >>> [<__main__.Parent object at 0x037BB6B0>, <__main__.Parent object at 0x037BB790>]

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

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

 db.query(Parent).join(Parent.children).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).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(Parnet).join('ParentChild').all() #报错

 db.query(Parent).join('children').all() #说明join(str)时，str表示的relationship的配置，而不是被关联对象的类名

 >>> 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(ParentChild).join('parent','child').all()   #报错,说明这种方式的join是从左至右关联的即 ParentChild.parent > Parent.child

 >>> AttributeError: type object 'Parent' has no attribute 'child'

 db.query(ParentChild).join(ParentChild.parent,ParentChild.child).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_child INNER JOIN parent ON parent.id = parent_child.parent_id INNER JOIN child ON child.id = parent_child.child_id

 parent_child = db.query(ParentChild).subquery()

 db.query(Parent).join(parent_child).all()

 >>> SELECT parent.id AS parent_id, parent.name AS parent_name

 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

 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里面的第一个结果集

 db.query(Parent).filter(Parent.name=='wu1').scalar()    #取到的是Parent对象

 >>> <__main__.Parent object at 0x0368BA10>

 db.query(Parent.name,Parent.id).filter(Parent.name=='wu1').scalar()   #取到的是只是Wu1

 >>> '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

码农公寓

query

1.delete

2. update

3. query

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

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

3）exsits()

4) filter() 这个就牛X了

5) filter_by()

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

7) limit(limit) and offset(offset)

8) order_by(*criterion)

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

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

11) cte() subquery()

12) from_self()

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

相关文章

　　　　　　　　　　　　　　　　　　query

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

　　4) filter() 这个就牛X了

　　5) filter_by()

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

　　7) limit(limit) and offset(offset)

　　8) order_by(*criterion)　　

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

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

　　11) cte() subquery()

　　12) from_self()

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