Skip to content

Instantly share code, notes, and snippets.

@carlosdelfino
Last active June 12, 2024 15:03
Show Gist options
  • Save carlosdelfino/44a691edfa63da3b564260d9afa4e9c6 to your computer and use it in GitHub Desktop.
Save carlosdelfino/44a691edfa63da3b564260d9afa4e9c6 to your computer and use it in GitHub Desktop.
O script abaixo é para importar os dados do arquivos xlsx para o mysql
import pandas as pd
from sqlalchemy import create_engine, Table, MetaData
# Configurações do banco de dados
db_username = 'telegram_bot'
db_password = 'telegram_bot'
db_host = 'localhost'
db_name = 'telegram_bot'
# Criação da engine de conexão
engine = create_engine(f'mysql+pymysql://{db_username}:{db_password}@{db_host}/{db_name}')
# Leitura do arquivo XLSX
df = pd.read_excel('../telegram_bot/data/dados_smd.xlsx')
# Função para inserir dados nas tabelas relacionadas
def insert_related_data(df, table_name, id_column, name_column, engine):
unique_values = df[[id_column]].drop_duplicates()
unique_values.columns = [name_column]
unique_values = unique_values.applymap(lambda x: x.strip() if isinstance(x, str) else x)
unique_values = unique_values.applymap(lambda x: ' '.join(x.split()) if isinstance(x, str) else x)
# Verificar se a tabela já existe e inserir dados novos
metadata = MetaData()
table = Table(table_name, metadata, autoload_with=engine)
# Verificar a existência dos valores
existing_values_query = f"SELECT {name_column} FROM {table_name}"
existing_values = pd.read_sql(existing_values_query, con=engine)
new_values = unique_values[~unique_values[name_column].isin(existing_values[name_column])]
# Inserir novos valores
if not new_values.empty:
new_values.to_sql(table_name, con=engine, if_exists='append', index=False)
# Inserção de dados nas tabelas relacionadas
insert_related_data(df, 'manufature', 'Mnf', 'mnf', engine)
insert_related_data(df, 'function', 'Function', 'function_id', engine)
insert_related_data(df, 'case_type', 'Case', 'case_id', engine)
# Renomear a coluna 'case' para 'case_type' no DataFrame antes de inserir na tabela smd_code
df = df.rename(columns={'case': 'case_type'})
# Inserção dos dados principais na tabela smd_code
df.to_sql('smd_code', con=engine, if_exists='append', index=False)
create user telegram_bot@localhost IDENTIFIED BY "telegram_bot";
grant all on telegram_bot.* to telegram_bot@localhost;
flush privileges;
create database telegram_bot;
use telegram_bot;
CREATE TABLE manufature (
mnf CHAR(50) PRIMARY KEY,
name VARCHAR(255) DEFAULT '' NOT NULL,
site VARCHAR(255),
email VARCHAR(255),
INDEX (name)
);
CREATE TABLE function (
function_id CHAR(50) PRIMARY KEY,
name VARCHAR(255) DEFAULT '' NOT NULL,
description TEXT,
INDEX (name)
);
CREATE TABLE case_type (
case_id CHAR(50) PRIMARY KEY,
description TEXT DEFAULT '',
url_image VARCHAR(255)
);
CREATE TABLE smd_code (
id CHAR(50) PRIMARY KEY,
mnf CHAR(50),
function_id CHAR(50),
case_type CHAR(50),
code VARCHAR(50),
description TEXT,
FOREIGN KEY (mnf) REFERENCES manufature(mnf),
FOREIGN KEY (function_id) REFERENCES function(function_id),
FOREIGN KEY (case_type) REFERENCES case_type(case_id)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment