<jupyter_start><jupyter_text>Script de select datos MySQL en cluster multidomain<jupyter_code>!pip install mysql-connector==2.1.7
!pip install pandas
!pip install sqlalchemy
#requiere instalación adicional, consultar https://github.com/PyMySQL/mysqlclient
!pip install mysqlclient
!pip install numpy
!pip install pymysql
import mysql.connector
from mysql.connector import Error
import pandas as pd
import numpy as np
import sqlalchemy
import time
import matplotlib.pyplot as plt
import psutil
import random

from mysql.connector.cursor import MySQLCursor
#Los resultados de medidas de tiempo en carga por dominios se almacenan en estos objetos.
#Se itera durante 100 iteraciones para sacar medias
#repeticiones
repeats = 100
#Ficheros de salida
resultados_etl_select = '../Results/MySQL/MySQLSelect_test_{}.csv'
def save_results_to_csv(results,file):
    #Guardamos los resultados en csv
    from datetime import datetime
    
    csv_df = pd.DataFrame(results, columns=['Registros', 'Tiempo', 'CPU','Memoria'])
    dia = datetime.now().strftime("%d%m%Y_%H_%M_%S")
    csv_df.to_csv(file.format(str(dia)))
conn = mysql.connector.connect(host='localhost',
                                    port=6446,
                                    database='CustomerProfileDomainSchema',
                                    user='root',                                       
                                    password='<PASSWORD>')
if conn.is_connected():
        conn.autocommit = False #Apenas se observa diferencia de rendimiento
        print('Connected to MySQL database')
    
cursor = conn.cursor()  
    
def connection_open():
   
    #InnoDB Cluster
    conn = mysql.connector.connect(host='localhost',
                                    port=6446,
                                    database='CustomerProfileDomainSchema',
                                    user='root',                                       
                                    password='<PASSWORD>')


    if conn.is_connected():
        conn.autocommit = False #Apenas se observa diferencia de rendimiento
        print('Connected to MySQL database')
    
    cursor = conn.cursor()  
cursor = conn.cursor(buffered=True)
cursor.execute('set global group_replication_transaction_size_limit = 300000000;')
cursor.close()<jupyter_output><empty_output><jupyter_text>Select test multidomain<jupyter_code>#Obtenemos los posibles valores de pais. Se iterará por ellos para cambiar en bucle los registros
cursor = conn.cursor(buffered=True)
cursor.execute("""SELECT DISTINCT (PartyId) FROM CustomerProfileDomainSchema.CustomerProfile""")
partyId_list= []
for partyId in cursor.fetchall():
    partyId_list.append(partyId[0])
print(len(partyId_list))
cursor.close()
time_inicial = 0
time_final = 0
registers = []
 
for iteracion in range(0,repeats): 
    select_query = """SELECT * FROM CustomerProfileDomainSchema.CustomerProfile cp
INNER JOIN CurrentAccountDomainSchema.CurrentAccount ca ON ca.PartyId = cp.PartyId
INNER JOIN CurrentAccountDomainSchema.AccountInfo ai ON ai.AccountId = ca.AccountId
INNER JOIN PositionKeepingDomainSchema.PositionKeeping pk ON pk.AccountId = ca.AccountId
INNER JOIN PositionKeepingDomainSchema.Amount am ON am.AmountId = pk.AmountId
INNER JOIN PositionKeepingDomainSchema.CreditLine cl ON cl.CreditLineId = pk.CreditLineId
INNER JOIN PositionKeepingDomainSchema.Currency cr ON cr.CurrencyId = am.CurrencyId
WHERE cp.PartyId = {}""".format(random.choice(partyId_list))
    cursor = conn.cursor(buffered=True)
    time_inicial = time.time()
    cursor.execute(select_query)
    time_final = time.time() 
    used_cpu = psutil.cpu_percent()
    mem_used = psutil.virtual_memory().percent
    # Tupla con numero de registros, tiempo parcial de la transacción y tiempo acumulado de trxs
    total_time = round(time_final - time_inicial,3)
    registers.append((iteracion + 1, total_time ,used_cpu, mem_used))
    #print((iteracion + 1 , total_time ,used_cpu, mem_used))
    cursor.close()
conn.close()
#Guardamos los resultados Customer Profile
save_results_to_csv(registers,resultados_etl_select)
conn.close()
print('Conexion cerrada')<jupyter_output>Conexion cerrada
{'fraction_non_alphanumeric': 0.08593374939990399, 'fraction_numerical': 0.007921267402784446, 'mean_word_length': 3.4095238095238094, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 10, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '652674', 'license': 'Apache-2.0', 'n_tokens_mistral': 1273, 'n_tokens_neox': 1198, 'n_words': 307, 'path': 'Scripts/Select_MySQL.ipynb', 'repo_name': 'danielherranzsegundo/TFM_2022', 'size': '4156'}