8 - Pandas x SQL

Iniciamos o processo de construção do teste comparativo entre o Pandas e o SQL, importando os pacotes e métodos necessários, criando a função medir_tempo para uso com o decorador "@medir_tempo" .

Passo 1

Importamos os pacotes pandas, numpy, time e o módulo connector do pacote mysql.

import pandas as pd
import numpy as np
import time
from mysql import connector

Passo 2

Declaramos a função medir_tempo usada com o decorador "@medir_tempo" para medir o tempo de execução dos métodos das classes.

def medir_tempo(func):
    def embrulho(*args,**kwargs):
        inicio = time.time()
        resultado = func(*args,**kwargs)
        fim = time.time()
        tempo = fim - inicio
        print(f"Tempo de execução de {func.__name__}(): {(tempo):.5f} segundos")
        return resultado, tempo
    return embrulho

Utilizamos um servidor SGBDR MySQL para realizar os testes.

Passo 3

Declaramos a classe BD_SQL especializada no acesso ao banco de dados MySQL.

A classe BD_SQL tem os seguintes atributos:

• usuario: Usuário do servidor MySQL.
• senha: Senha do servidor MySQL.
• host: Host do servidor MySQL.
• banco_dados: Nome do banco de dados.

A classe BD_SQL tem os seguintes métodos:

• conectar_servidor_mysql(): Conecta ao servidor MySQL utilizando o usuário, a senha e o host.
• criar_banco_dados(): Exclui o banco de dados com o nome determinado na criação do objeto, se existir no servidor MySQL, e cria um novo.
• selecao_banco_dados(): Seleciona o banco de dados para uso.
• desconectar(): Encerra a conexão ao servidor MySQL.

class BD_SQL():
    def __init__(self,
        usuario='root',senha='',host='localhost',
        banco_dados='MeuBancoDeDados'):
        self.usuario = usuario
        self.senha = senha
        self.host = host
        self.banco_dados = banco_dados

    @medir_tempo
    def conectar_servidor_mysql(self): 
        # Conectando ao servidor MySQL
        self.conexao = connector.connect(
            host=self.host,
            user=self.usuario,
            password=self.senha
        )
        self.cursor = self.conexao.cursor()

    @medir_tempo
    def criar_banco_dados(self):
        """Excluir o banco de dados se existir e criar o novo"""
        self.cursor.execute(f"DROP DATABASE IF EXISTS {self.banco_dados}")
        self.cursor.execute(f"CREATE DATABASE {self.banco_dados}")

    @medir_tempo
    def selecao_banco_dados(self):
        """Selecionar o banco de dados"""
        self.cursor.execute(f"USE {self.banco_dados}")

    @medir_tempo
    def desconectar(self):
        """Desconecta do servidor MySQL"""
        self.cursor.close()
        self.conexao.close()

Passo 4

Instanciamos na variável bd_sql o objeto de acesso ao banco de dados SQL da classe BD_SQL.

bd_sql = BD_SQL(banco_dados='pandas_x_sql_02')
bd_sql.conectar_servidor_mysql()
bd_sql.criar_banco_dados()
bd_sql.selecao_banco_dados()

InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' (10061 Nenhuma conexão pôde ser feita porque a máquina de destino as recusou ativamente) <traceback object at 0x0000018D3A978940>

Passo 5

Criamos a classe Pandas_x_SQL para acesso ao Pandas e SQL.

A classe Pandas_x_SQL tem os seguintes atributos:

• bd_sql: Objeto da classe BD_SQL.
• nome_tabela: Nome da tabela.
• num_linhas: Quantidade de linhas.
• num_colunas: Quantidade de colunas.

A classe Pandas_x_SQL tem os seguintes métodos:

• criar_dataframe_aleatorio(): Criando o dataframe aleatório.
• criar_tabela(): Criando a tabela no banco de dados SQL.
• criar_indices(): Criando os índices da tabela no banco de dados SQL.
• salvar_registros_sql(): Salvando os registros na tabela SQL.
• preparar_testes(): Preparando os testes.
• selecao_sql_rapida(): Seleciona as linhas na tabela SQL em que coluna_1 esteja entre 0.3 e 0.4
• selecao_dataframe_rapida(): Seleciona as linhas no dataframe em que coluna_1 esteja entre 0.3 e 0.4.
• selecao_sql_lenta_contagem(): Conta o número de linhas na tabela SQL em que a coluna coluna_1 com join dela mesma usando subquery.
• selecao_sql_lenta_linhas(): Seleciona as linhas na tabela SQL em que a coluna coluna_1 com join dela mesma usando subquery.
• selecao_dataframe_lenta(): Seleciona as linhas no dataframe em que a coluna coluna_1 com join dela mesma usando subselecao.

class Pandas_x_SQL():
    def __init__(self,
        bd_sql,nome_tabela,num_linhas):
        self.bd_sql = bd_sql
        self.nome_tabela = nome_tabela
        self.num_linhas = num_linhas
        self.num_colunas = 10
        self.df = None

    @medir_tempo
    def criar_dataframe_aleatorio(self):
        """Criando o dataframe aleatório"""
        # Gerando números aleatórios entre 0 e 1
        data = np.random.rand(self.num_linhas, self.num_colunas)
        # Criando o DataFrame
        colunas = [f'coluna_{i+1}' for i in range(self.num_colunas)]
        self.df = pd.DataFrame(data, columns=colunas)

    @medir_tempo
    def criar_tabela(self):
        """Criando a tabela com o número de colunas especificado"""
        self.bd_sql.cursor.execute(f"""DROP TABLE IF EXISTS {self.nome_tabela}""")
        self.bd_sql.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {self.nome_tabela} (
                id INT AUTO_INCREMENT PRIMARY KEY,
                coluna_1 DECIMAL(10, 9),
                coluna_2 DECIMAL(10, 9),
                coluna_3 DECIMAL(10, 9),
                coluna_4 DECIMAL(10, 9),
                coluna_5 DECIMAL(10, 9),
                coluna_6 DECIMAL(10, 9),
                coluna_7 DECIMAL(10, 9),
                coluna_8 DECIMAL(10, 9),
                coluna_9 DECIMAL(10, 9),
                coluna_10 DECIMAL(10, 9)
            )
        """)

    @medir_tempo
    def criar_indices(self):
        """Criando índices para cada coluna"""
        for i in range(1, 11):
            sql = f"""
                CREATE INDEX
                    idx_{self.nome_tabela}_{i}
                ON
                    {self.nome_tabela} (coluna_{i})
            """
            self.bd_sql.cursor.execute(sql)

    @medir_tempo
    def salvar_registros_sql(self):
        """Salvando os registros no banco de dados"""
        max_bloco = 1000
        for i in range(len(self.df)):
            valores = tuple(self.df.iloc[i])
            sql = f"""
                INSERT INTO {self.nome_tabela} (
                    coluna_1, coluna_2, coluna_3, coluna_4, coluna_5,
                    coluna_6, coluna_7, coluna_8, coluna_9, coluna_10)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            self.bd_sql.cursor.execute(sql,valores)
            if i % max_bloco == 0:
                # Commit após max_bloco inserções para confirmar os dados inseridos
                self.bd_sql.cursor.execute("COMMIT")

    @medir_tempo
    def preparar_testes(self):
        """Preparando os testes"""
        self.criar_dataframe_aleatorio()
        self.criar_tabela()
        self.salvar_registros_sql()
        self.criar_indices()

    @medir_tempo
    def selecao_dataframe_rapida(self):
        """Seleção de linhas"""
        df_aux = self.df[(self.df['coluna_1'] >= 0.3) & (self.df['coluna_1'] < 0.4)]
        return df_aux

    @medir_tempo
    def selecao_sql_rapida(self):
        """Seleção de linhas"""
        sql = f"SELECT * FROM {self.nome_tabela} WHERE coluna_1 BETWEEN 0.3 AND 0.4"
        self.bd_sql.cursor.execute(sql)
        dados = self.bd_sql.cursor.fetchall()
        return dados

    @medir_tempo
    def selecao_dataframe_lenta(self):
        """Seleção de linhas"""
        t1 = self.df
        t2 = self.df

        # Passo 2: Encontrar o valor máximo de coluna_1 em t2 para cada valor de coluna_1
        df_aux = t2.groupby('coluna_1', as_index=False)['coluna_1'].max()

        # Passo 3: Realizar o join entre t1 e t2 baseado nesses valores
        df_aux = pd.merge(t1, df_aux, on='coluna_1', suffixes=('_t1', '_t2'))

        # Adicionar colunas de t2 ao DataFrame final
        df_aux = pd.merge(df_aux, t2, on='coluna_1', suffixes=('_t1', '_t2'))

        return df_aux

    def execute_selecao_sql_lenta(self, select):
        """Seleção lenta com join e subqueries"""
        sql = """
            SELECT 
                {0}
            FROM
                {1} AS t1
            LEFT OUTER JOIN 
                {1} AS t2
            ON t1.coluna_1 = (SELECT MAX(tbaux.coluna_1) FROM {1} AS tbaux WHERE tbaux.coluna_1 = t2.coluna_1);
        """.format(select,self.nome_tabela)
        self.bd_sql.cursor.execute(sql)
        return self.bd_sql.cursor.fetchall()

    @medir_tempo
    def selecao_sql_lenta_contagem(self):
        """Contagem de linhas"""
        contagem = self.execute_selecao_sql_lenta("COUNT(*)")
        return contagem[0][0]
   
    @medir_tempo
    def selecao_sql_lenta_linhas(self):
        """Seleção de linhas"""
        dados = self.execute_selecao_sql_lenta("t1.*, t2.*")
        return dados

Passo 6

Atribuimos na variável pandas_x_sql_10_mil a instância do objeto Pandas_x_SQL contendo um dataframe com 10.000 linhas e 10 colunas com valores reais aleatórios para acesso com o Pandas e com o SQL.

pandas_x_sql_10_mil = Pandas_x_SQL(bd_sql, 'tb_10k', 10000)
pandas_x_sql_10_mil.preparar_testes()

Tempo de execução de criar_dataframe_aleatorio(): 0.00300 segundos
AttributeError: 'BD_SQL' object has no attribute 'cursor' <traceback object at 0x0000018D2BA72040>

Passo 7

Realizamos a seleção rápida de linhas na tabela SQL para os valores de coluna_1 entre 0.3 e 0.4.

Chamamos de seleção rápida no sentindo de usar uma sentença SQL simples, com apenas uma tabela usando uma apenas uma condição na seleção.

res = pandas_x_sql_10_mil.selecao_sql_rapida()
retornados_1 = len(res[0])
tempo_1 = res[1]
print(f"Registros: {retornados_1}")

AttributeError: 'BD_SQL' object has no attribute 'cursor' <traceback object at 0x0000018D3A978140>

Passo 8

Realizamos a seleção rápida de linhas no dataframe pandas_x_sql_10_mil para os valores de coluna_1 entre 0.3 e 0.4.

Chamamos de seleção rápida no sentindo de usar uma consulta simples ao dataframe do objeto.

res = pandas_x_sql_10_mil.selecao_dataframe_rapida()
retornados_2 = len(res[0])
tempo_2 = res[1]
print(f"Registros: {retornados_2}")

Tempo de execução de selecao_dataframe_rapida(): 0.00200 segundos
Registros: 975

Passo 9

Ambos objetos bd quando df possuem o mesmo número de linhas, mas a seleção usando os dados do dataframe foi bem mais rápida do que buscando as linhas no banco de dados SQL, co parando-se os tempos de seleção de linhas de cada um.

print(f"Registros retornados usando SQL | Dataframes: {retornados_1} | {retornados_2}")
print(f"Tempo de consulta usando SQL | Dataframes: {tempo_2:.5f} | {tempo_1:.5f}")
relacao = tempo_1/tempo_2 if tempo_2 > 0 else 0.00001
print(f"Relação de tempo entre SQL | Dataframes: {relacao:.2f}")
print(f"Ou seja, o tempo de acesso ao banco de dados SQL é {(relacao):.2f} vezes maior do que usando dataframes do Pandas.")

NameError: name 'retornados_1' is not defined <traceback object at 0x0000018D2FA7FCC0>

Passo 10

Realizamos a contagem lenta de linhas na tabela SQL para a junção da tabela tb_10k com a própria tabela tb_10k.

Chamamos de contagem lenta no sentindo de usar uma sentença SQL composta, com a junção de tabelas e uma subseleção.

contagem = pandas_x_sql_10_mil.selecao_sql_lenta_contagem()
print(f"Contagem feita no banco de dados SQL: {contagem[0]}")

AttributeError: 'BD_SQL' object has no attribute 'cursor' <traceback object at 0x0000018D2B61D880>

Passo 11

Realizamos com o banco de dados SQL o teste de junção da tabela tb_10k com a própria tabela tb_10k

Chamamos de seleção lenta no sentindo de usar uma sentença SQL composta, com a junção de tabelas e uma subseleção.

res = pandas_x_sql_10_mil.selecao_sql_lenta_linhas()
retornados_1 = len(res[0])
tempo_1 = res[1]
print(f"Registros: {retornados_1}")

AttributeError: 'BD_SQL' object has no attribute 'cursor' <traceback object at 0x0000018D3A97A100>

Passo 12

Realizamos o teste de junção do dataframe pandas_x_sql_10_mil com o próprio dataframe pandas_x_sql_10_mil

res = pandas_x_sql_10_mil.selecao_dataframe_lenta()
retornados_2 = len(res[0])
tempo_2 = res[1]
print(f"Registros: {retornados_2}")

Tempo de execução de selecao_dataframe_lenta(): 0.02501 segundos
Registros: 10000

Passo 13

Ambos dataframes df_1 e df_2 possuem o mesmo número de linhas, mas com a seleção usando dataframe foi bem mais rápida do que buscando as linhas no banco de dados SQL.

print(f"Registros retornados usando SQL | Dataframes: {retornados_1} | {retornados_2}")
print(f"Tempo de consulta usando SQL | Dataframes: {tempo_1:.5f} | {tempo_2:.5f}")
relacao = tempo_1/tempo_2 if tempo_2 > 0 else 0.00001
print(f"Relação de tempo entre SQL | Dataframes: {relacao:.2f}")
print(f"Ou seja, o tempo de acesso ao banco de dados SQL é {(relacao):.2f} vezes maior do que usando dataframes do Pandas.")

NameError: name 'retornados_1' is not defined <traceback object at 0x0000018D3469F280>

Passo 14

Agora, iniciaremos de um novo teste comparativo usando um dataframe de 10 milhões de linhas com 10 colunas cada, para demonstrar como as operações com SQL têm alto consumo de tempo e recursos.

pandas_x_sql_10_milhoes = Pandas_x_SQL(bd_sql, 'tb_10_milhoes', 1000000)
pandas_x_sql_10_milhoes.preparar_testes()

Tempo de execução de criar_dataframe_aleatorio(): 0.09339 segundos
AttributeError: 'BD_SQL' object has no attribute 'cursor' <traceback object at 0x0000018D3469F880>

Passo 15

Realizamos a seleção específica de linhas na tabela SQL para os valores de coluna_1 entre 0.3 e 0.4.

res = pandas_x_sql_10_milhoes.selecao_sql_rapida()
retornados_1 = len(res[0])
tempo_1 = res[1]
print(f"Registros: {retornados_1}")

AttributeError: 'BD_SQL' object has no attribute 'cursor' <traceback object at 0x0000018D3469F980>

Passo 16

Realizamos a seleção específica de linhas no dataframe pandas_x_sql_10_milhoes para os valores de coluna_1 entre 0.3 e 0.4.

res = pandas_x_sql_10_milhoes.selecao_dataframe_rapida()
retornados_2 = len(res[0])
tempo_2 = res[1]
print(f"Registros: {retornados_2}")

Tempo de execução de selecao_dataframe_rapida(): 0.02311 segundos
Registros: 99573

Passo 17

Ambos dataframes df_1 e df_2 possuem o mesmo número de linhas, mas com a seleção usando dataframe foi bem mais rápida do que buscando as linhas no banco de dados SQL.

print(f"Registros retornados usando SQL | Dataframes: {retornados_1} | {retornados_2}")
print(f"Tempo de consulta usando SQL | Dataframes: {tempo_1:.5f} | {tempo_2:.5f}")
relacao = tempo_1/tempo_2 if tempo_2 > 0 else 0.00001
print(f"Relação de tempo entre SQL | Dataframes: {relacao:.2f}")
print(f"Ou seja, o tempo de acesso ao banco de dados SQL é {(relacao):.2f} vezes maior do que usando dataframes do Pandas.")

NameError: name 'retornados_1' is not defined <traceback object at 0x0000018D3469FF40>

Passo 18

Vamos repetir os testes com o acesso ao SQL para verificar se o cache na mesma pesquisa colabora em um acesso mais rápido ao servidor MySQL.

Realizamos a seleção específica de linhas na tabela SQL para os valores de coluna_1 entre 0.3 e 0.4.

res = pandas_x_sql_10_milhoes.selecao_sql_rapida()
retornados_1 = len(res[0])
tempo_1 = res[1]
print(f"Registros: {retornados_1}")

AttributeError: 'BD_SQL' object has no attribute 'cursor' <traceback object at 0x0000018D3469E5C0>

Passo 19

Ambos dataframes df_1 e df_2 possuem o mesmo número de linhas, mas com a seleção usando dataframe foi bem mais rápida do que buscando as linhas no banco de dados SQL.

print(f"Registros retornados usando SQL | Dataframes: {retornados_1} | {retornados_2}")
print(f"Tempo de consulta usando SQL | Dataframes: {tempo_1:.5f} | {tempo_2:.5f}")
relacao = tempo_1/tempo_2 if tempo_2 > 0 else 0.00001
print(f"Relação de tempo entre SQL | Dataframes: {relacao:.2f}")
print(f"Ou seja, o tempo de acesso ao banco de dados SQL é {(relacao):.2f} vezes maior do que usando dataframes do Pandas.")

NameError: name 'retornados_1' is not defined <traceback object at 0x0000018D3469D300>

Passo 20

Desconectamos a conexão com o servidor MySQL.

bd_sql.desconectar()
bd_sql = None
pandas_x_sql_10_mil = None