Linked Questions

Popular Questions

how to pass and update databases with python?

Asked by At

I have a connection with python that brings records from a SQL Server database to one of MySQL databases. I had programmed it so that the script runs every hour and if there were any changes the record is replaced directly with an "if exist replace".

The problem is that now I have to link some records from the MySQL database with another table. When running the script they are deleted or duplicated. It also happens to me that if a record is deleted in the source SQL database, it doesn't. It is deleted in MySQL. I tried with triggers and I couldn't find a way.

This is the last code that I tried to use (I did not put the connection data)

columns_to_update = ['Fecha', 'Vencimiento', 'Saldo', 'Comprobante', 'Corredor', 'Grupo', 'Nombre', 'Telefono', 'Email', 'Mora', 'Rango_venc']

# Obtener los datos existentes de la tabla
# Obtener los datos existentes de la tabla
existing_data_query = "SELECT * FROM cobranzas_cliente"
existing_data = pd.read_sql(existing_data_query, engine)

for table_name, table_content in tables_content.items():
    # Reemplazar los valores "NULL" por None
    table_content = table_content.fillna(0)
    # Reemplazar los valores en blanco o espacios en blanco por 0
    table_content = table_content.replace(['', ' '], 0)
    # Crear una nueva tabla temporal con las filas únicas
    table_content_unique = table_content.drop_duplicates(subset=columns_to_update)

    # Agregar nuevos registros y actualizar registros existentes
    for row in table_content_unique.itertuples(index=False):
        # Verificar si el registro ya existe antes de insertar
        comprobante_value = getattr(row, "Comprobante")
        comprobante_query = f"SELECT * FROM cobranzas_cliente WHERE Comprobante = '{comprobante_value}'"
        existing_comprobante = pd.read_sql(comprobante_query, engine)

        if existing_comprobante.empty:
            # Insertar un nuevo registro
            insert_values = [f"'{getattr(row, column)}'" for column in row._fields]
            insert_query = f"INSERT INTO cobranzas_cliente ({', '.join(row._fields)}) VALUES ({', '.join(insert_values)})"
            # Actualizar el registro existente
            update_values = [f"{column} = '{getattr(row, column)}'" for column in columns_to_update]
            update_query = f"UPDATE cobranzas_cliente SET {', '.join(update_values)} WHERE Comprobante = '{comprobante_value}'"

    # Eliminar registros que ya no están en la tabla tables_content
    delete_rows = []
    for existing_row in existing_data.itertuples(index=False):
        condition_values = [f"{column} = '{getattr(existing_row, column)}'" for column in columns_to_update]
        condition_query = f"SELECT * FROM cobranzas_cliente WHERE {' AND '.join(condition_values)}"
        matching_row = pd.read_sql(condition_query, engine)

        if matching_row.empty:

    if delete_rows:
        for row in delete_rows:
            condition_values = [f"{column} = '{getattr(row, column)}'" for column in columns_to_update]
            delete_query = f"DELETE FROM cobranzas_cliente WHERE {' AND '.join(condition_values)}"

Related Questions