python - 使用 python mysql 查找重复条目

Question

我正在编写一个 python 脚本，该脚本使用 python-mysql-connector 在 mysql 数据库的表中搜索重复条目。我希望函数输出客户信息表中的重复条目。我不确定如何存储重复项并跟踪表中项目的索引。它们应该存储在列表中还是集合中？

import mysql.connector

dbconnect = mysql.connector.connect(host='localhost', user='root', password='wordpass', db='contacts')


cur= dbconnect.cursor(buffered= True)


rows= cur.fetchall()


def find_duplicates(query):

    for row in rows:

       query= cur.execute ("SELECT id, name, address1, city, postal_code COUNT(*) FROM customer " 
    "GROUP BY name, address1, city, postal_code HAVING COUNT(*) > 1")

       if row in cur.fetchone():
         return row
       else:
         cur.fetchone()

Answer 1

我认为您可以更改查询以返回完整的重复结果集。我认为这样的事情应该可行:

SELECT t.* FROM customer AS t 
    INNER JOIN (
        SELECT name, address1, city, postal_code 
        FROM customer GROUP BY name, address1, city, postal_code 
        HAVING COUNT(*) > 1) AS td 
    ON t.name = td.name AND t.address1 = td.address1 
        AND t.city = td.city AND t.postal_code = td.postal_code;

一旦你掌握了所有带有 ID 的骗子，我认为你可以很容易地在 python 中对它们进行分组。