====== Detectar y eliminar duplicados en SQL ======


<code python: delDuplicates.py>
import mariadb

# Connect to the database
cnx = mariadb.connect(
    host="localhost",
    port=3306,
    user="user",
    password="my****SUPERSECRET****Password",
    database="VeeamReports"
)

# Get all tables in the database
cursor = cnx.cursor()
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()

# Iterate over each table
for table in tables:
    table_name = table[0]

    # Create a query to delete duplicates based on 'creationtime' and 'vmname'
    # The INNER JOIN compares rows in the same table (t1 and t2) based on 'creationtime' and 'vmname'
    # It then deletes rows where t1.id is less than t2.id, keeping only the row with the lowest id
    query = f"""
    DELETE t1 FROM {table_name} t1
    INNER JOIN {table_name} t2
    WHERE t1.creationtime = t2.creationtime AND t1.vmname = t2.vmname AND t1.id < t2.id
    """
    cursor.execute(query)

# Commit the changes and close the connection
cnx.commit()
cnx.close()
</code>

Estos comentarios explican cada sección del script:

  - **Connect to the database**: Establece la conexión con la base de datos.
  - **Get all tables in the database**: Obtiene una lista de todas las tablas en la base de datos.
  - **Iterate over each table**: Recorre cada tabla.
  - **Create a query to delete duplicates based on 'creationtime' and 'vmname'**: Crea una consulta para eliminar duplicados basados en las columnas 'creationtime' y 'vmname'.
    - **INNER JOIN**: Compara filas en la misma tabla (`t1` y `t2`) basándose en 'creationtime' y 'vmname'.
    - **WHERE clause**: Elimina filas donde `t1.id` es menor que `t2.id`, conservando solo la fila con el id más bajo.
  - **Commit the changes and close the connection**: Confirma los cambios y cierra la conexión con la base de datos.