欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

 

相关标签: 随笔 python