import mysql.connector import configparser DB_host = 'base223.c8lcuo0a2bu6.us-east-1.rds.amazonaws.com' DB_user = 'gps' DB_password = 'p0o9i8u7' DB_database = 'gps_reportes' DB_charset = 'utf8mb4' DB_use_pure = True def get_db_connection(config_file='config.ini'): """Establece y devuelve una conexión a la base de datos MySQL.""" config = configparser.ConfigParser() config.read(config_file) try: cnx = mysql.connector.connect( host=DB_host, user=DB_user, password=DB_password, database=DB_database ) print("Conexión a la base de datos exitosa.") return cnx except mysql.connector.Error as err: print(f"Error al conectar a la base de datos: {err}") return None def get_table_structure(cursor, table_name): """Obtiene la estructura de una tabla (columnas e índices).""" columns = {} indexes = {} # Obtener columnas cursor.execute(f"DESCRIBE {table_name}") for col in cursor: field_name = col[0] col_type = col[1] nullable = "YES" if col[2] == "YES" else "NO" columns[field_name] = {'type': col_type, 'nullable': nullable} # Obtener índices cursor.execute(f"SHOW INDEXES FROM {table_name}") for idx in cursor: key_name = idx[2] column_name = idx[4] if key_name not in indexes: indexes[key_name] = [] indexes[key_name].append(column_name) return columns, indexes def apply_alterations(cnx, table_name, alterations): """Aplica las sentencias ALTER a la tabla.""" cursor = cnx.cursor() for statement in alterations: try: print(f" Ejecutando: {statement}") cursor.execute(statement) cnx.commit() print(f" Sentencia ejecutada exitosamente.") except mysql.connector.Error as err: print(f" Error al ejecutar '{statement}': {err}") cursor.close() def main(): expected_charset = 'utf8mb4' expected_collation = 'utf8mb4_unicode_ci' cnx = get_db_connection() if not cnx: return expected_columns = { 'IdReporte': {'type': 'int', 'nullable': 'NO'}, # Primary key usually not nullable 'Fecha': {'type': 'datetime', 'nullable': 'YES'}, 'Reporte': {'type': 'varchar(2000)', 'nullable': 'YES'}, 'Puerto': {'type': 'varchar(10)', 'nullable': 'YES'}, 'Pc': {'type': 'varchar(45)', 'nullable': 'YES'}, 'EnProceso': {'type': 'int', 'nullable': 'YES'}, 'TipoReporte': {'type': 'varchar(45)', 'nullable': 'YES'}, 'Evento': {'type': 'varchar(3)', 'nullable': 'YES'}, 'NroMensaje': {'type': 'varchar(45)', 'nullable': 'YES'}, 'IdDispositivo': {'type': 'varchar(45)', 'nullable': 'YES'}, 'ModeloDisp': {'type': 'varchar(45)', 'nullable': 'YES'}, 'FechaRecibido': {'type': 'datetime', 'nullable': 'YES'}, 'Protocolo': {'type': 'char(5)', 'nullable': 'YES'}, 'Enlace': {'type': 'char(50)', 'nullable': 'YES'} } expected_indexes = { 'PRIMARY': ['IdReporte'], 'idx_fecha': ['Fecha'], 'idx_tipoReporte': ['TipoReporte'], 'idx_evento': ['Evento'], 'idx_temporal_7081_enproceso': ['EnProceso'], # Assuming the example index is for 7081, we generalize 'idx_temporal_7081_enp_fecha': ['EnProceso', 'Fecha'], # Assuming the example index is for 7081, we generalize 'idx_protocolo': ['Protocolo'], 'idx_enlace': ['Enlace'] } cursor = cnx.cursor() for i in range(7000, 7100): table_name = f"temporal_{i}" print(f"\n--- Verificando tabla: {table_name} ---") print(f"[LOG] Verificando charset de la tabla...") # Verificar charset de la tabla cursor.execute(f"SHOW TABLE STATUS LIKE '{table_name}'") table_status = cursor.fetchone() if table_status: table_collation = table_status[14] # 'Collation' está en la posición 14 if table_collation != expected_collation: print(f"[LOG] Collation de la tabla '{table_name}' es '{table_collation}', corrigiendo a '{expected_collation}'...") alterations = [f"ALTER TABLE `{table_name}` CONVERT TO CHARACTER SET {expected_charset} COLLATE {expected_collation};"] else: print(f"[LOG] Collation de la tabla '{table_name}' ya es '{expected_collation}'.") alterations = [] else: print(f"[LOG] No se pudo obtener el status de la tabla '{table_name}'.") alterations = [] print(f"[LOG] Verificando charset/collation de los campos...") cursor.execute(f"SHOW FULL COLUMNS FROM `{table_name}`") columns_info = cursor.fetchall() for col in columns_info: field = col[0] collation = col[2] type_ = col[1] # Convertir a string si es bytes if isinstance(type_, bytes): type_ = type_.decode() type_ = str(type_).lower() if collation is not None and isinstance(collation, bytes): collation_str = collation.decode() elif collation is not None: collation_str = str(collation) else: collation_str = None # Solo verificar campos tipo texto if any(t in type_ for t in ['char', 'varchar', 'text']) and collation_str: if collation_str != expected_collation: print(f"[LOG] Collation del campo '{field}' es '{collation_str}', corrigiendo a '{expected_collation}'...") alterations.append(f"ALTER TABLE `{table_name}` MODIFY `{field}` {type_} CHARACTER SET {expected_charset} COLLATE {expected_collation} NULL;") else: print(f"[LOG] Collation del campo '{field}' ya es '{collation_str}'.") # 1. Verificar existencia de la tabla cursor.execute(f"SHOW TABLES LIKE '{table_name}'") table_exists = cursor.fetchone() if not table_exists: print(f"[LOG] La tabla '{table_name}' NO existe. Creándola...") create_table_sql = f""" CREATE TABLE `{table_name}` ( `IdReporte` INT UNSIGNED NOT NULL AUTO_INCREMENT, `Fecha` DATETIME NULL, `Reporte` VARCHAR(2000) NULL, `Puerto` VARCHAR(10) NULL, `Pc` VARCHAR(45) NULL, `EnProceso` INT NULL, `TipoReporte` VARCHAR(45) NULL, `Evento` VARCHAR(3) NULL, `NroMensaje` VARCHAR(45) NULL, `IdDispositivo` VARCHAR(45) NULL, `ModeloDisp` VARCHAR(45) NULL, `FechaRecibido` DATETIME NULL, `Protocolo` CHAR(5) NULL, `Enlace` CHAR(50) NULL, PRIMARY KEY (`IdReporte`) ); """ try: cursor.execute(create_table_sql) cnx.commit() print(f"[LOG] Tabla '{table_name}' creada exitosamente.") # Si la tabla se acaba de crear, ahora le añadimos los índices current_columns, current_indexes = get_table_structure(cursor, table_name) except mysql.connector.Error as err: print(f"[LOG] Error al crear la tabla '{table_name}': {err}") continue # Pasa a la siguiente tabla si hay un error al crear else: print(f"[LOG] La tabla '{table_name}' existe. Verificando estructura...") current_columns, current_indexes = get_table_structure(cursor, table_name) # ...existing code... # 2. Verificar columnas print(f"[LOG] Verificando columnas esperadas...") for col_name, col_props in expected_columns.items(): if col_name not in current_columns: print(f"[LOG] Columna '{col_name}' no encontrada. Añadiéndola...") column_type = col_props['type'].upper() nullable_prop = "NULL" if col_props['nullable'] == 'YES' else "NOT NULL" if col_name == 'IdReporte': # Primary key is handled during initial table creation or requires special ALTER ADD # If IdReporte is missing, it implies the primary key is missing, which is a more complex fix. # For simplicity, we assume IdReporte is always there if the table exists. # If the table was just created, IdReporte and PRIMARY KEY are already added. pass else: alterations.append(f"ALTER TABLE `{table_name}` ADD COLUMN `{col_name}` {column_type} {nullable_prop};") else: print(f"[LOG] Columna '{col_name}' ya existe.") print(f"[LOG] Verificando índices esperados...") # 3. Verificar índices # Generalizamos los nombres de índice para evitar dependencia específica de 7081 generic_expected_indexes = { 'PRIMARY': ['IdReporte'], 'idx_fecha': ['Fecha'], 'idx_tipoReporte': ['TipoReporte'], 'idx_evento': ['Evento'], 'idx_enproceso': ['EnProceso'], # Renombrado para ser genérico 'idx_enp_fecha': ['EnProceso', 'Fecha'], # Renombrado para ser genérico 'idx_protocolo': ['Protocolo'] } # Mapear los nombres de índices esperados a los que realmente pueden aparecer en SHOW INDEXES # Algunos motores MySQL renombran automáticamente índices como 'PRIMARY' current_index_names_lower = {k.lower(): v for k, v in current_indexes.items()} for idx_name, idx_cols in generic_expected_indexes.items(): # Crear un nombre de índice "ideal" para la tabla actual (ej. idx_temporal_7000_enproceso) if idx_name == 'idx_enproceso': actual_idx_name_to_check = f"idx_{table_name.split('_')[0]}_{i}_enproceso" # idx_temporal_7000_enproceso legacy_idx_name_to_check = f"idx_temporal_{i}_enproceso" # Para compatibilidad con la imagen elif idx_name == 'idx_enp_fecha': actual_idx_name_to_check = f"idx_{table_name.split('_')[0]}_{i}_enp_fecha" # idx_temporal_7000_enp_fecha legacy_idx_name_to_check = f"idx_temporal_{i}_enp_fecha" # Para compatibilidad con la imagen else: actual_idx_name_to_check = idx_name legacy_idx_name_to_check = "" found_index = False for current_idx_key, current_idx_columns in current_indexes.items(): # Comparamos ignorando el orden de las columnas para índices compuestos if set(idx_cols) == set(current_idx_columns) and \ (current_idx_key.lower() == actual_idx_name_to_check.lower() or \ current_idx_key.lower() == legacy_idx_name_to_check.lower() or \ (idx_name == 'PRIMARY' and current_idx_key.lower() == 'primary')): found_index = True break if not found_index: print(f"[LOG] Índice '{idx_name}' (columnas: {', '.join(idx_cols)}) no encontrado. Añadiéndolo...") if idx_name == 'PRIMARY': # Add primary key is more complex if the column is already there and not PK # We assume if IdReporte exists, it's already the PK or we're adding the table initially. # If the table was just created, the PK is already there. pass else: # Usamos un nombre de índice más consistente si no es PRIMARY index_create_name = f"idx_{table_name}_{'_'.join(idx_cols).lower()}" alterations.append(f"CREATE INDEX `{index_create_name}` ON `{table_name}` ({', '.join([f'`{col}`' for col in idx_cols])});") if alterations: print(f"[LOG] Aplicando alteraciones a la tabla '{table_name}':") apply_alterations(cnx, table_name, alterations) else: print(f"[LOG] La tabla '{table_name}' ya tiene la estructura deseada (columnas, índices y charset).") cursor.close() cnx.close() print("\nProceso completado.") if __name__ == "__main__": main()