Python中mysql查询重复数据并删除重复数据
程序员文章站
2022-06-11 16:12:31
...
#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')
上一篇: LNMP环境下,部署laravel
下一篇: Python 删除列表中的重复数据