Python之mysql删改优化
来源:互联网 发布:维弗网络 编辑:程序博客网 时间:2024/06/15 17:33
Python之mysql操作
1.删除数据
#!/usr/bin/env python# -*- coding:utf8 -*-# @Time : 2017/11/24 9:09# @Author : hantong# @File : mysql_delete.pyimport MySQLdbdef connect_mysql(): db_config = { "host":'10.3.1.113', "port":'3306', "user":'cn_uts', "passwd":'cn_uts', "db": 'test', "charset":'utf-8' } cnx = MySQLdb.connect(**db_config) return cnxif __name__ == '__main__': cnx = connect_mysql() sql = '''delete from Teacher where TID in( select TID from (select Course.CouID, Course.TID, Teacher.TName, count(Teacher.TID) as count_teacher from Course left join Score on Score.Grade < 60 and Course.CouID = Score.CouID left join Teacher on Course.TID = Teacher.TID group by Course.TID order by count_teacher desc limit 5) as test ) ''' try: cus = cnx.cursor() cus.execute(sql) result = cus.fetchall() cus.close() cnx.commit() except Exception as e: cnx.rollback() print('error') raise e finally: cnx.close()# 解释:# 1. 先查询出Course表中的Course.TID和Course.TID# 2. left join 是关联Score表,查出Score.Grade > 59,并且,课程ID和课程表的CouID要对应上# 3. left join Teacher 是关联老师表,课程中的了老师ID和老师表中的老师ID对应上# 4. select中加上老师的名字Teacher.Tname和count(Teacher.TID)# 5. group by Course.TID,在根据老师的的TID进行分组# 6. oder by 最后对count_teacher进行排序,取前5行,# 7. 在通过套用一个select子查询,把所有的TID搂出来# 8. 然后delete from Teacher 最后删除TID在上表中的子查询中。2.更新数据
#!/usr/bin/env python# -*- coding:utf8 -*-# @Time : 2017/11/24 9:11# @Author : hantong# @File : mysql_update.pyimport MySQLdbdef connect_mysql(): db_config = { "host":'10.3.1.113', "port":'3306', "user":'cn_uts', "passwd":'cn_uts', "db": 'test', "charset":'utf-8' } cnx = MySQLdb.connect(**db_config) return cnxif __name__ == '__main__': cnx = connect_mysql() sql = '''select *, (grade+60) as newGrade from Score where Grade <5;''' update = '''update Score set grade = grade + 60 where grade < 5; ''' try: cus_start = cnx.cursor() cus_start.execute(sql) result1 = cus_start.fetchall() print(len(result1)) cus_start.close() cus_update = cnx.cursor() cus_update.execute(update) cus_update.close() cus_end = cnx.cursor() cus_end.execute(sql) result2 = cus_end.fetchall() print(len(result2)) cus_end.close() cnx.commit() except Exception as e: cnx.rollback() print('error') raise e finally: cnx.close()# 解释:# 1. 刚开始,我们可以查到分数小于5分的总个数有321个# 2. select *, (grade+60) as newGrade from Score where Grade <5;这个sql是把所有的成绩小于5的都列出来,然后最后加一列分数加60分的结果。# 3. update Score set grade = grade + 60 where grade < 5;是把分数小于5的所有成绩都加60分# 4. 最后在检查分数小于5的个数为0,说明所有低于5分的分数都发生了改变。3.添加索引
#!/usr/bin/env python# -*- coding:utf8 -*-# @Time : 2017/11/24 9:11# @Author : hantong# @File : mysql_index.pyimport MySQLdbdef connect_mysql(): db_config = { "host":'10.3.1.113', "port":'3306', "user":'cn_uts', "passwd":'cn_uts', "db": 'test', "charset":'utf-8' } cnx = MySQLdb.connect(**db_config) return cnxif __name__ == '__main__': cnx = connect_mysql() sql1 = '''alter table Teacher add primary key(TID);''' sql2 = '''alter table Student add primary key(StdID);''' sql3 = '''alter table Score add primary key(SID);''' sql4 = '''alter table Course add primary key(CouID);''' sql5 = '''alter table Score add index idx_StdID_CouID(StdID, CouID);''' # sql6 = '''alter table Score drop index idx_StdID_CouID;''' 删除索引 sql7 = '''explain select * from Score where StdID = 16213;''' try: cus = cnx.cursor() cus.execute(sql1) cus.close() cus = cnx.cursor() cus.execute(sql2) cus.close() cus = cnx.cursor() cus.execute(sql3) cus.close() cus = cnx.cursor() cus.execute(sql4) cus.close() cus = cnx.cursor() cus.execute(sql5) cus.close() cus = cnx.cursor() cus.execute(sql7) result = cus.fetchall() print(result) cus.close() cnx.commit() except Exception as e: cnx.rollback() print('error') raise e finally: cnx.close()# 解释:# Sql1, sql2, sql3, sql4是添加主键,sql5是增加一个索引,我们也可以在mysql的客户端上执行sq7,得到如下的结果:# mysql> explain select * from Score where StdID = 16213;# +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+# | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |# +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+# | 1 | SIMPLE | Score | ref | idx_StdID_CouID | idx_StdID_CouID | 4 | const | 4 | NULL |# +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+# 1 row in set (0.00 sec)# 这个说明,我们在搜索StdID的时候,是走了idx_StdID_CouID索引的。
阅读全文
0 0
- Python之mysql删改优化
- mysql增查删改
- PHP MySQL 增查删改
- MySQL动态添删改列字段
- MySQL是什么玩意儿(三)查删改
- mybatis+mysql动态字段添删改小结
- mysql优化之查询优化
- mysql优化之索引优化
- 性能优化之--MySql优化
- 性能优化之MySQL优化
- python 之性能优化
- PHP 操作 mongoDB 之添删改查
- MySql优化之二三事
- 数据库优化之MySQL
- 优化mysql之key_buffer_size
- MYSQL之性能优化
- MySQL优化之filesort
- mysql 之 优化
- mysql自动备份
- 不服来战,看Kotlin如何完爆Java
- 万树IT:Java概述与基本语法
- 大道至简
- BZOJ1137:[POI2009]Wsp 岛屿(半平面交)
- Python之mysql删改优化
- 用powershell登录网站,获取登录后的response,再对response进行字符串的截取
- Linux中环境变量文件及配置
- 语音合成技术
- Bailian2730 求20以内n的阶乘【递推】(Bailian2729 求12以内n的阶乘)
- Android官方ORM数据库Room技术解决方案:@Embedded内嵌对象(二)
- VGG-阅读笔记-理解
- Java8 Stream篇章
- GAN之根据文本描述生成图像