sqlalchemy查询使用

sqlalchemy查询使用

 1.带条件查询

查询是最常用的，对于各种查询我们必须要十分清楚，首先是带条件的查询

1. #带条件查询

2. rows = session.query(User).filter_by(username='jingqi').all()

3. print(rows)

4. rows1 = session.query(User).filter(User.username=='jingqi').all()

5. print(rows1)

6. rows2 = session.query(User.username).filter(User.username=='jingqi').all()

7. print(rows2)

8. rows3 = session.query(User.username).filter(User.username=='jingqi')

9. print(rows3)

`filter_by`和`filter`都是过滤条件，只是用法有区别`filter_by`里面不能用`!= `还有`> <` 等等，所有`filter`用得更多,`filter_by`只能用`=`。

前两个查询的是`User`,所以返回结果也是一个对象，但是`rows2`查询的是属性值，所以返回的是属性值。

`rows3`可以看到`SQLAlchemy `转成的`SQL`语句，`SQLAlchemy`最后都是会转成`SQL`语句，通过这个方法可以查看原生`SQL`,甚至有些时候我们需要把`SQLAlchemy`转成的`SQL`交给DBA审查，合适的才能使用。

查询要知道查询结果的返回怎样的数据

1. print( session.query(User).filter(User.username=='jingqi').all() )

2. print( session.query(User).filter(User.username=='jingqi').first())

3. print( session.query(User).filter(User.username=='jingqi').one())#结果为一个时正常，多了就报错

4. print( session.query(User).get(2))#通过id查询

上面三条记录，第一个查出所有符合条件的记录，第二个查出所有符合记录的第一条记录，第三个返回一个对象，如果结果有多条就会报错，第四个通过主键获取记录

除此之外，我们偶尔也会需要限制返回的结果数量

1. #限制查询返回结果

2. print( session.query(User).filter(User.username!='jingqi').limit(2).all())

3. print( session.query(User).filter(User.username!='jingqi').offset(2).all())

4. print( session.query(User).filter(User.username!='jingqi').slice(2,3).all())

 #可以排序之后再进行限制
 from sqlalchemy import desc
 print( session.query(User).filter(User.username!='budong').order_by(User.username).all())
 print( session.query(User).filter(User.username!='budong').order_by(desc(User.username)).slice(1,3).all())

第一个是限制返回条数，从第一条开始；第二个是从第三条开始返回查询结果；第三个是切片返回记录。

`order_by`默认是顺序，`desc`是降序。

还有其他的带条件查询

1. #不等于

2. print( session.query(User).filter(User.username!='jingqi').all() )

3. #模糊匹配 like

4. print( session.query(User).filter(User.username.like('jingqi')).all() )

5. print( session.query(User).filter(User.username.notlike('jingqi')).all() )

6. #成员属于 in_

7. print( session.query(User).filter(User.username.in_(['jingqi','jingqi1'])).all() )

8. #成员不属于 notin_

9. print( session.query(User).filter(User.username.notin_(['jingqi','jingqi2'])).all() )

10. #空判断

11. print( session.query(User).filter(User.username==None).all() )

12. print( session.query(User).filter(User.username.is_(None)).all() )

13. print( session.query(User).filter(User.username.isnot(None)).all() )

14. #多条件

15. print( session.query(User).filter(User.username.isnot(None),User.password=='qwe123').all() )

16. #选择条件

17. from sqlalchemy import or_,and_,all_,any_

18. print( session.query(User).filter(or_(User.username=='jingqi',User.password=='qwe123')).all() )

19. print( session.query(User).filter(and_(User.username=='jingqi2',User.password=='111')).all() )

以上是各种带条件的查询，大家知道怎么使用，但是需要注意的是，所以的模糊匹配是十分耗费时间的，能不用就尽量不要用。

当然还有聚合函数的使用

1. from sqlalchemy import func,extract

2. print( session.query(User.password,func.count(User.id)).group_by(User.password).all() )

3. print( session.query(User.password,func.count(User.id)).group_by(User.password).having(func.count(User.id)>1).all() )

4. print( session.query(User.password,func.sum(User.id)).group_by(User.password).all() )

5. print( session.query(User.password,func.max(User.id)).group_by(User.password).all() )

6. print( session.query(User.password,func.min(User.id)).group_by(User.password).all() )

7. #使用extract提取时间中的分钟或者天来分组

8. print( session.query(extract('minute', User.creatime).label('minute'),func.count('*').label('count')).group_by('minute').all() )

9. print( session.query(extract('day', User.creatime).label('day'),func.count('*').label('count')).group_by('day').all() )

这里只是告诉大家的用法，其中`group_by`是分组，如果要使用聚合函数，就必须导入`func`,`label`是取别名的意思 。

2.表关系查询

对于有表关系的，也有些不同的查询，首先我们来建立一个有外键关系的表

1. from sqlalchemy.orm import relationship

2. from sqlalchemy import ForeignKey

3.  

4. class UserDetails(Base):

5. __tablename__ = 'user_details'

6. id = Column(Integer,primary_key=True,autoincrement=True)

7. id_card = Column(Integer,nullable=False,unique=True)

8. lost_login = Column(DateTime)

9. login_num = Column(Integer,default=0)

10. user_id = Column(Integer,ForeignKey('user.id'))

11.  

12. userdetail_for_foreignkey = relationship('User',backref='details',uselist=False,cascade='all')

13.  

14. def __repr__(self):

15. return '<UserDetails(id=%s,id_card=%s,lost_login=%s,login_num=%s,user_id=%s)>'%(

16. self.id,

17. self.id_card,

18. self.login_login,

19. self.login_num,

20. self.user_id

21. )

这里要注意`relationship`默认是一对多的关系，使用`uselist=False`则表示一对一的关系，`cascade` 是自动关系处理，就和MySQL中的`ON DELETE`类似，但是有区别，参数选项如下：

`cascade` 所有的可选字符串项是:

- *all* , 所有操作都会自动处理到关联对象上.
- *save-update* , 关联对象自动添加到会话.
- *delete* , 关联对象自动从会话中删除.
- *delete-orphan* , 属性中去掉关联对象, 则会话中会自动删除关联对象.
- *merge* , `session.merge()` 时会处理关联对象.
- *refresh-expire* , `session.expire()` 时会处理关联对象.
- *expunge* , `session.expunge()` 时会处理关联对象.

有如上的表关系之后，查询可以十分方便

1. #表关系查询

2. row = session.query(UserDetails).all()

3. print(row,dir(row[0]))

4. row = session.query(User).filter(User.id==1).first()

5. print(row,dir(row))

6. print(row.details)

7. print(row.details[0].lost_login)

`relationship`会在`User`表里面添加一个属性，通过这个属性就可以查询对应的`user_details`表中的所有字段。省去了很多的代码。

3.多表查询

多表查询也是必须要掌握的知识点。以下是常见的几种表关联方式，需要熟练掌握。

1. #多表查询

2. print( session.query(UserDetails,User).all() ) #这个是 cross join

3. print( session.query(UserDetails,User).filter(User.id==UserDetails.id).all() ) #这是也是cross join 但是加上了where条件

4.  

5. print( session.query(User.username,UserDetails.lost_login).join(UserDetails,UserDetails.id==User.id).all() ) #这个是inner join

6.  

7. print( session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id==User.id).all() ) #这个才是左连接，sqlalchemy没有右连接

8.  

9. q1 = session.query(User.id)

10. q2 = session.query(UserDetails.id)

11. print(q1.union(q2).all()) #这个是union关联

除了上面的几种关联方式，子表查询也是用得很多的，也是要掌握的

1. from sqlalchemy import all_,any_

2. sql_0 = session.query(UserDetails.lost_login).subquery() #这是声明一个子表

3. print( session.query(User).filter((User.creatime > all_(sql_0)) ).all() )

4. print( session.query(User).filter((User.creatime > any_(sql_0)) ).all() )

注意`any_`和`all_`的区别，`all_`要求的是所有都满足，`any_`只需要有满足的就行。

4.原生SQL的查询以及其他使用

再次强调，使用`ORM`或者原生`SQL`没有绝对的那个好一点，怎么方便怎么使用。

1. #第一步写好原生的sql，如果需要传递参数，可以使用字符串拼接的方式

2. sql_1 = """

3. select * from `user`

4. """

5. #第二步执行，得到返回的结果

6. row = session.execute(sql_1)

7. print(row,dir(row))

8. #第三步，自己控制得到数据的方式

9. print( row.fetchone() )

10. print( row.fetchmany() )

11. print( row.fetchall() )

12. #也可以循环获得

13. for i in row:

14. print('===',i)

转载：https://www.cnblogs.com/jingqi/p/8059673.html

 tornado客户端使用sqlalchemy遇到的问题：

1、tornado客户端需要的cookie
#来源
print response.headers.get_all
cookies = response.headers.get_list("Set-Cookie")
cookies = dict([l.split("=", 1) for l in cookies[0].split(";")])
print cookies["_xsrf"]
#设置
h = tornado.httputil.HTTPHeaders()#{"content-type": "text/html"}
h.add("X-Xsrftoken",cookies["_xsrf"])
h.add("Cookie","_xsrf="+cookies["_xsrf"])

2、server获取请求参数不到

h = tornado.httputil.HTTPHeaders({"content-type": "text/html"})
设置之后，参数在body里面，而不是在body_arguments或qurey_arguments

js侧，data: {"data":JSON.stringify(res)},

可以直接处理self.request.body,如果是json格式的body，也这样解析json.loads(self.request.body)

3、sqlalchemy查询语句
session.query(User|User.name, User.id).jion(UserDetail, UserDetail.id=User.id).filter(User.name=='wangmei',A.id=B.id).(order_by(desc(User.username))).(limit(2), offset(2), slice(2,4)).(all()|first()|one())                        #尽量不要cross jion即没有join，outerjoin

4、异常的时候要回滚

try:
    obj = Network(type=ntype, start_ip=start_ip, end_ip=end_ip, mask=get_mask(int(mask_len)), gateway_ip=gateway_ip)
    session.add(obj)
    session.commit()
    # 返回表id
    self.write(str(obj.nid))
except Exception as e:
    session.rollback()
    print e

5、sqlalchemy的修改和删除

先查找到相应记录result

result.name =newname

result.delete(result)

session.commit()

6、sqlalchemy级联删除一对多

1. ips = relationship("Ip", cascade='delete')

2. sid = Column(Integer, ForeignKey('server.sid', ondelete='CASCADE'))

3. https://docs.sqlalchemy.org/en/rel_0_9/orm/cascades.html#delete

级联删除的实质是，先删除相应的子节点再删除父节点。

当父节点删除时候，子节点有几个选择：禁止父删除，将父节点改为null，保持不变，都可以设置；