python sqlalchemy session_python之sqlalchemy的使用

1 from sqlalchemy.orm importsessionmaker2 from sqlalchemy importcreate_engine3 from models importUsers, Student, Depart4

5 engine =create_engine(6 "mysql+pymysql://root:123456@192.168.226.150:3306/flask_demo?charset=utf8",7 max_overflow=0, #超过连接池大小外最多创建的连接

8 pool_size=5, #连接池大小

9 pool_timeout=30, #池中没有线程最多等待的时间，否则报错

10 pool_recycle=-1 #多久之后对线程池中的线程进行一次连接的回收(重置)

11 )12 SessionFactory = sessionmaker(bind=engine)13

14 #从连接池获取一个连接

15 session =SessionFactory()16

17 ############################### 基本增删改查 ###############################

18 #1. 增加

19 obj = Users(name='tang')20 session.add(obj)21 session.commit()22

23 #批量增加

24 session.add_all([25 Users(name='tang'),26 Users(name='chen')27 ])28 session.commit()29

30 #2. 查

31 result =session.query(Users).all()32 for row inresult:33 print(row.id,row.name)34

35 #sqlalchemy 的语法跟Python很相似

36 result = session.query(Users).filter(Users.id >= 2)37 for row inresult:38 print(row.id,row.name)39

40

41 #获取第一个

42 result = session.query(Users).filter(Users.id >= 2).first()43 print(result)44

45 #3.删

46 session.query(Users).filter(Users.id >= 2).delete()47 session.commit()48

49 #4.改 通过字典

50 session.query(Users).filter(Users.id == 4).update({Users.name:'tang'})51 session.query(Users).filter(Users.id == 4).update({'name':'tang'})52 session.query(Users).filter(Users.id == 4).update({'name':Users.name+"_lao"},synchronize_session=False)53 session.commit()54

55 ############################### 其他常用 ###############################

56 #1. 指定列 去别名

57 #对应原生SQL：select id,name as cname from users;

58 result = session.query(Users.id,Users.name.label('cname')).all()59 for item inresult:60 print(item[0],item.id,item.cname)61

62

63 #2. 默认条件and

64 session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()65

66 #3. between

67 session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()68

69 #4. in

70 session.query(Users).filter(Users.id.in_([1,3,4])).all()71 #not in

72 session.query(Users).filter(~Users.id.in_([1,3,4])).all()73

74 #5. 子查询

75 session.query(Users).filter(Users.id.in_(session.query(Users.id).filter(Users.name=='tang'))).all()76

77 #6. and 和 or

78 from sqlalchemy importand_, or_79 session.query(Users).filter(Users.id > 3, Users.name == 'tang').all()80 session.query(Users).filter(and_(Users.id > 3, Users.name == 'tang')).all()81 session.query(Users).filter(or_(Users.id < 2, Users.name == 'tang')).all()82 session.query(Users).filter(or_(Users.id < 2,and_(Users.name == 'eric', Users.id > 3),Users.extra != "")).all()83

84 #7. filter_by 只需字段名

85 session.query(Users).filter_by(name='alex').all()86

87 #8. 通配符

88 ret = session.query(Users).filter(Users.name.like('e%')).all()89 ret = session.query(Users).filter(~Users.name.like('e%')).all()90

91 #9. 切片

92 result = session.query(Users)[1:2]93

94 #10.排序

95 ret =session.query(Users).order_by(Users.name.desc()).all()96 ret =session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()97

98 #11. group by

99 from sqlalchemy.sql importfunc100

101 ret =session.query(Users.depart_id,func.count(Users.id),).group_by(Users.depart_id).all()102 for item inret:103 print(item)104 #105 #from sqlalchemy.sql import func

106 #分组之后再进行查询

107 ret =session.query(108 Users.depart_id,109 func.count(Users.id),110 ).group_by(Users.depart_id).having(func.count(Users.id) >= 2).all()111 for item inret:112 print(item)113

114 #12.union 和 union all

115 """

116 select id,name from users117 UNION118 select id,name from users;119 """

120 """

121 select id,name from users122 UNION ALL123 select id,name from users;124 """

125 q1 = session.query(Depart.title).filter(Depart.id > 2)126 q2 = session.query(Student.name).filter(Student.id < 2)127 ret =q1.union(q2).all()128 #129 #q1 = session.query(Users.name).filter(Users.id > 2)

130 #q2 = session.query(Favor.caption).filter(Favor.nid < 2)

131 #ret = q1.union_all(q2).all()

132

133 """

134 union 和 union_all 的区别135 union 去重136 union_all 不去重137

138 相同点：合并的两张表的列要相同139 """

140

141 """

142 union 和 join的区别143 union是垂直合并成一张表144 join是水平合并成一张表145 """

146

147 """

148 查看原生sql 打印不获取结果的语句就可以149 sql = session.query(Users).filter(Users.id==1)150 print(sql)151 """

152

153 session.close()