Last active
June 12, 2024 15:03
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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