Manipular Dados no MySQL com Pandas

Fala galera do mundo dos dados! Seguimos na prática como conectar um banco de dados MySQL com Python. Se não viu o artigo anterior, confere como Conectar Banco de Dados MySQL com Python. Agora com mais alguns ingredientes para deixar a solução bem mais interessante. Vamos manipular dados no MySQL com Pandas. Sendo assim, utilizaremos mais duas bibliotecas Python, são elas a SQLAlchemy e o Pandas, para trabalhar com um volume maior de dados e deixar a solução mais robusta. Partiu para o código!!!

Conectar MySQL com SQLAlchemy 

O SQLAlchemy é uma biblioteca para manipulação de bancos relacionais através da linguagem Python. Com ela é possível trabalhar com os principais bancos relacionais de mercado, inclusive o MySQL.

Então, seguimos para o código Python mostrando como conectar MySQL com SQLAlchemy. E o primeiro passo é fazer a importação das bibliotecas necessárias. No código abaixo importamos a função create_engine da biblioteca sqlalchemy e a biblioteca pandas. 

from sqlalchemy import create_engine
import pandas as pd

Com as bibliotecas importadas, criaremos a função de conexão do banco de dados. Usando a função create_engine, que recebe a string de conexão com os parâmetros host, user, passwd e database. Finalizando a função retorna a conexão com o banco.

def mysql_connection(host, user, passwd, database=None):
    engine = create_engine(f'mysql+pymysql://{user}:{passwd}@{host}/{database}')
    return engine.connect()

Agora chamamos a função mysql_connection, e passando os parâmetros host, user, passwd e database, para criar a conexão.

connection = mysql_connection('localhost', 'root', 'mysql123', 'dadosaocubo')

Conexão criada, podemos criar uma tabela no banco de dados.

Criar Tabela com SQLAlchemy 

O passo seguinte, será a criação da tabela na base de dados dadosaocubo. Então, a variável query, vai receber um comando SQL (CREATE TABLE, para criação da tabela). Em seguida, executamos o comando SQL da variável query através da variável connection.

query = '''
    CREATE TABLE clientes2 (
        id       INT PRIMARY KEY, 
        nome     VARCHAR(100),
        telefone VARCHAR(15),
        email    VARCHAR(100),
        cidade   VARCHAR(50)
    )
'''
connection.execute(query)

Com a nossa tabela pronta no MySQL, podemos inserir e consultar dados no MySQL. Primeiro, realizaremos a operação de forma mais artesanal utilizando a execução de queries do SQLAlchemy, e em seguida, a realizaremos diretamente com as funções SQL do Pandas.

Inserir Dados com SQLAlchemy 

Para inserir os dados com SQLAlchemy, criamos um dataframe pandas, começando com a criação de um dicionário. No código Python abaixo a variável dic contem o dicionário.

dic = {'id': [1001, 1002, 1003, 1004, 1005, 1006],
       'nome': ['Joãozinho','Paula','Patricia','Zé','Richarlison','Vini'],
       'telefone': ['7199999-9999','2199999-9999','1199999-9999',
                    '4199999-9999','3199999-9999','2199999-9999'],
       'email': ['joao@email.com','paula@email.com','paty@email.com',
                 'ze@email.com','pombo@email.com','vini@email.com'],
       'cidade': ['Salvador','Rio','Sampa','Curitiba','Nova Venécia','Madri']}

Em seguida, transformando esse dicionário no dataframe.

df = pd.DataFrame(dic)
df.head()

Confere na imagem abaixo, como ficaram os dados do nosso dataframe pandas.

O código Python abaixo, vai inserir cada linha do dataframe Pandas no banco de dados MySQL, através da biblioteca SQLAlchemy. Para inserir cada linha por vez, foi criado um loop for para percorrer cada index do dataframe.

Os registros serão inseridos na tabela. Temos a variável query, com um comando SQL (INSERT INTO, para inserir dados), na string SQL os valores a serem inseridos no banco, são passados através de parâmetros de acordo com as colunas do dataframe. Em seguida, é executado o comando SQL da variável query através da variável connection.

for i in df.index:
    query = '''
    INSERT INTO clientes2 VALUES 
        ('%s','%s','%s','%s','%s');
    ''' % (df['id'][i], df['nome'][i], df['telefone'][i], df['email'][i], df['cidade'][i])
    connection.execute(query)

Agora podemos consultar os dados inseridos na tabela clientes2.

Consultar Dados com SQLAlchemy 

Para consultar os dados no MySQL com Python. Mais uma vez a variável query, agora com um comando SQL (SELECT para consultar dados). Em seguida, é executado o comando SQL da variável query através da variável connection, armazenando os dados na variável result através da função fetchall. Para armazenar o nome das colunas na variável columns, seguimos o mesmo passo anterior, mas através da função keys.

query = '''
    SELECT * FROM clientes2
'''
result = connection.execute(query).fetchall()
columns = tuple(connection.execute(query).keys())

Em seguida, transformando essas variáveis em um dataframe.

df2 = pd.DataFrame(result, columns=columns)
df2.head()

Confere na imagem abaixo, como ficaram os dados do nosso dataframe pandas. Exatamente igual de como ele foi criado antes de inserir os dados no banco.

Dá para melhorar ainda esses códigos!!! Sendo assim, faremos diretamente com as funções SQL do Pandas.

Inserir Dados no MySQL com Pandas

Antes de inserir os dados criaremos uma tabela de clientes exatamente igual a primeira, mas chamaremos de clientes3.

E então, chegou a hora de fazer a mágica e com apenas uma linha de código, levar os dados de um dataframe Pandas para o banco MySQL com a função to_sql. 

df.to_sql('clientes3', connection, if_exists='append', index=False)

Se não acredita que essa linha funciona, partiu consultar os dados dessa tabela de clientes3.

Consultar Dados no MySQL com Pandas

Para consultar, mais uma mágica com a função read_sql, também em uma linha de código, pegamos os dados do MySQL e colocamos em um dataframe Pandas.

df3 = pd.read_sql('SELECT * FROM clientes3', connection)
df3.head()

Na imagem abaixo, o resultado dos dados do nosso dataframe pandas direto do MySQL. Exatamente igual aos outros dois criados anteriormente.

Me diz se MySQL com Pandas não é uma combinação perfeita?!

MySQL com Pandas ao Cubo

E então, chegamos ao final de como manipular dados no MySQL com Pandas. Vimos como fazer algumas das principais operações SQL integradas com o Pandas. Se curtiu, não esqueça de compartilhar o conteúdo e deixar aquele feedback para nós. Tamo juntos e até a próxima.

Conteúdos ao Cubo

Se você curtiu o conteúdo, lá no Dados ao Cubo tem muito mais. Então, deixo algumas sugestões de conteúdos que você pode encontrar por lá, sempre falando sobre o mundo dos dados.

• Bem Vindos ao Dados ao Cubo
• Profissão: Cientista de Dados Parte I
• Profissão: Cientista de Dados Parte II
• Pipeline dos Projetos de Ciência de Dados
• Engenharia de Atributos AKA Feature Engineering Parte I
• Engenharia de Atributos AKA Feature Engineering Parte II
• Visualização de Dados com Matplotlib Python

Finalizando com um convite para quem quer ser Parceiro de Publicação Dados ao Cubo, escrever o próximo artigo e ter divulgação para toda a comunidade de dados no LinkedIn.

Tiago Dias

Baiano, apaixonado por dados e tecnologia, amante das inovações tecnológicas que facilitam a vida humana! Formado em Engenharia da Computação e com MBA em Gestão da Informação e Business Intelligence e especialização em Data Science. Atualmente atua como Data Tech Lead na Lopes, além de Professor na área de dados e nas horas vagas cria uns modelos de Machine Learning com Python e soluções diversas com dados!