python mysql删除数据_python-mysql删除和更新数据

删除数据

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 = '''select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'''

try:

cus = cnx.cursor()

cus.execute(sql)

result = cus.fetchall()

with codecs.open('select.txt', 'w+') as f:

for line in result:

f.write(str(line))

f.write('\n')

cus.close()

cnx.commit()

except Exception as e:

cnx.rollback()

print('error')

raise e

finally:

cnx.close()

结果：

正确执行

修改数据

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 = '''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()

结果：

321

0