python去重复的数据_Python中mysql查询重复数据并删除重复数据

#coding:utf-8

import mysql.connector

mydb = mysql.connector.connect(

host='localhost',

user='root',

passwd='123456',

database='mydatabase',

buffered = True

)

print(mydb)

#1、创建数据库并插入数据：

mycursor = mydb.cursor()

sql = "insert into customers(name,address) values(%s,%s)"

val =("John","London 21")

mycursor.execute(sql,val)

mydb.commit()

print(mycursor.rowcount,"record inserted")

val2 = [

('Peter', 'Lowstreet 4'),

('Amy', 'Apple st 652'),

('Hannah', 'Mountain 21'),

('Michael', 'Valley 345'),

('Sandy', 'Ocean blvd 2'),

('Betty', 'Green Grass 1'),

('Richard', 'Sky st 331'),

('Susan', 'One way 98'),

('Vicky', 'Yellow Garden 2'),

('Ben', 'Park Lane 38'),

('William', 'Central st 954'),

('Chuck', 'Main Road 989'),

('Viola', 'Sideway 1633')

]

insertFn(mycursor,'customers','name,address',val2) #插入多条数据

# select出重复数据并删除

def deleteSameData(delSql,tableName):

mycursor.execute(delSql)

mycursor.execute(str("select * from " +tableName))

myresult1 = mycursor.fetchall()

for x in myresult1:

print(x)

#select出重复数据并删除

print("select出重复数据并删除")

#根据name字段count计算>1表示有重复，只显示最小id的客户信息

sql="select * from customers where id in (select min(id) from customers group by name having count(id)>1)"

delSql="delete from a using customers as a where a.id not in (select * from (select min(id) from customers group by name having count(id)>1) a)"

deleteSameData(delSql,'customers')