python delete_python delete数据

#!/usr/bin/env python

# -*- coding:utf-8 -*-

# @Time : 2017/11/24 0:27

# @Author : lijunjiang

# @File : delete.py

import MySQLdb

select_sql = '''

SELECT TID FROM (

(

SELECT Score.CouID, Course.TID, Teacher.TName, COUNT(Course.TID) as teacher_count FROM Score

JOIN Course ON Score.CouID = Course.CouID and Score.Grade < 60

JOIN Teacher ON Course.TID = Teacher.TID

GROUP BY Course.TID

ORDER by teacher_count DESC

LIMIT 5

)

as teacher_tid

)

'''

delete_sql = '''

delete from Teacher where TID in (

SELECT TID FROM (

(

SELECT Score.CouID, Course.TID, Teacher.TName, COUNT(Course.TID) as teacher_count FROM Score

JOIN Course ON Score.CouID = Course.CouID and Score.Grade < 60

JOIN Teacher ON Course.TID = Teacher.TID

GROUP BY Course.TID

ORDER by teacher_count DESC

LIMIT 5

)

as teacher_tid

)

)

'''

def connect_mysql():

info_mysql = {

'host': '11.11.11.11',

'port': 3306,

'db': 'python',

# 'charset':'utf8',

'user': 'python',

'passwd': 'python'

}

try:

cnx = MySQLdb.connect(**info_mysql)

except Exception as err:

raise err

return cnx

if __name__ == '__main__':

cnx = connect_mysql()

try:

cus = cnx.cursor()

cus.execute(select_sql)

result = cus.fetchall()

print(result)

cus.execute(delete_sql)

cus.execute(select_sql)

result1 = cus.fetchall()

print(result1)

cus.close()

cnx.commit()

except Exception as err:

cnx.rollback()

raise err

finally:

cnx.close()

运行结果：

C:\Python27\python.exe D:/Python/Mysql/delete.py

((10L,), (4L,), (3L,), (1L,), (9L,))

((8L,), (7L,), (5L,), (6L,))

Process finished with exit code 0