python删除列索引_29. Python 删除 更改 索引数据

1.删除数据

需求：课程的及格率太低，最后名最差的5名老师将会被开除

import codecs

import MySQLdb

def connect_mysql():

db_config = {

'host': '192.168.48.128',

'port': 3306,

'user': 'xiang',

'passwd': '123456',

'db': 'python',

'charset': 'utf8'

}

cnx = MySQLdb.connect(**db_config)

return cnx

if __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搂