利用Python对MySQL数据库进行批量行内去重

需求:将表内某一字段下的内容做行内去重

例如:将上图变成下图:

python代码:

#!/usr/bin/python3
import pymysql
import re
#连接数据库
db = pymysql.connect("localhost","root","密码","库" )	
cursor = db.cursor()
sql = """
SELECT
表.id,
表.字段2
FROM
表
ORDER BY
表.字段2 DESC
"""
try:
	cursor.execute(sql)
	results = cursor.fetchall()
	for row in results:
		classs = row[1]
		#去除掉重复的字符串
		liclasss = classs.split(",")
		liclasssN = list(set(liclasss))
		classsN = ",".join(liclasssN)
		print ( "new_class=%s" % classsN )
		# SQL 更新语句
		sql = "UPDATE `表` SET `字段2`='%s' WHERE (`id`='%s')" % (classsN,row[0])
		print ( sql )
		try:
		   cursor.execute(sql)
		   db.commit()
		except:
		   db.rollback()
except:
   print ("Error: SQL 查询语句")
   
# 关闭数据库连接
db.close()

 

留下评论