dedupe-mark.py

   1 #! /usr/bin/python3
   2
   3 # SPDX-FileCopyrightText: 2023 Andrew Engelbrecht
   4 #
   5 # SPDX-License-Identifier: GPL-3.0-or-later
   6
   7 import sys
   8 import mysql.connector
   9
  10 mydb = mysql.connector.connect(
  11   host="localhost",
  12   user="root",
  13   password=sys.argv[1],
  14   database="civicrm"
  15 )
  16
  17 mycursor0 = mydb.cursor(buffered=True)
  18 mycursor1 = mydb.cursor(buffered=True)
  19 mycursor2 = mydb.cursor(buffered=True)
  20
  21 mycursor0.execute(
  22 """
  23 select c.id, c.external_identifier, x.*
  24 from civicrm_contact c inner join civicrm_email e on c.id = e.contact_id
  25 inner join (
  26     select i.id, i.external_identifier, j.email, count(*) as qty
  27     from civicrm_contact i inner join civicrm_email j on i.id = j.contact_id
  28     group by email
  29     having count(*) > 1
  30 ) x on e.email = x.email and c.external_identifier is not NULL and x.external_identifier is not NULL and c.external_identifier != x.external_identifier;
  31 """
  32 )
  33
  34 for row in mycursor0:
  35     id1 = row[0] # c.id
  36     id2 = row[2] # x.id
  37
  38     mycursor1.execute("select count(*) from civicrm_dedupe_exception where (contact_id1 = %s and contact_id2 = %s) or (contact_id1 = %s and contact_id2 = %s);", (id1, id2, id2, id1))
  39
  40     count = mycursor1.fetchone()[0]
  41     if count == 0:
  42         print('inserting:', id1, id2)
  43         mycursor2.execute("INSERT INTO civicrm_dedupe_exception (contact_id1, contact_id2) VALUES (%s, %s)", (id1, id2))
  44
  45         #print("only do one for now, for debugging purposes")
  46         #break
  47
  48 mydb.commit()
  49