Skip to content

Instantly share code, notes, and snippets.

@victormalvao

victormalvao/.py

Last active July 9, 2025 18:24
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save victormalvao/83231d30245bec13a7a1ccb51faaaa3a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save victormalvao/83231d30245bec13a7a1ccb51faaaa3a to your computer and use it in GitHub Desktop.
Download ZIP
Script em python para carga de dados no mysql e ddl do banco de dados
Raw
.py
import mysql.connector
from faker import Faker
import random
from decimal import Decimal
from datetime import date, timedelta
import argparse # Biblioteca para aceitar argumentos na linha de comando
import sys # Para usar o sys.stdout.flush
# --- CONFIGURAÇÕES DO BANCO DE DADOS ---
DB_CONFIG = {
'host': 'localhost',
'port': 3307,
'user': 'root', # Usuário padrão do MySQL
'password': 'SUA_SENHA', # !! TROQUE PELA SENHA ROOT QUE VOCÊ DEFINIU !!
'database': 'gymdb'
}
# Inicializa o Faker para o idioma português do Brasil
fake = Faker('pt_BR')
def conectar_db():
"""Estabelece a conexão com o banco de dados."""
try:
conn = mysql.connector.connect(**DB_CONFIG)
print("Conexão com o banco de dados estabelecida com sucesso.")
return conn
except mysql.connector.Error as err:
print(f"Erro ao conectar ao MySQL: {err}")
return None
def limpar_tabelas(conn):
"""Limpa os dados das tabelas para uma nova carga."""
cursor = conn.cursor()
print("Limpando tabelas para a carga inicial...")
try:
cursor.execute("SET FOREIGN_KEY_CHECKS = 0;")
cursor.execute("TRUNCATE TABLE pagamentos;")
cursor.execute("TRUNCATE TABLE mensalidades;")
cursor.execute("TRUNCATE TABLE alunos;")
cursor.execute("SET FOREIGN_KEY_CHECKS = 1;")
conn.commit()
print("Tabelas limpas com sucesso.")
except mysql.connector.Error as err:
print(f"Erro ao limpar as tabelas: {err}")
finally:
cursor.close()
def matricular_alunos(conn, numero_de_alunos):
"""Usa a procedure sp_matricular_aluno para criar alunos com dados do Faker."""
cursor = conn.cursor()
print(f"Iniciando a matrícula de {numero_de_alunos} alunos...")
for i in range(numero_de_alunos):
try:
nome = fake.name()
cpf = fake.cpf().replace('.', '').replace('-', '')
email = fake.unique.email()
telefone = fake.phone_number()
data_nascimento = fake.date_of_birth(minimum_age=18, maximum_age=60)
valor_mensal = round(Decimal(random.uniform(120.0, 135.0)), 2)
dia_vencimento = random.choice([5, 10, 15, 20])
args = (nome, cpf, email, telefone, data_nascimento, valor_mensal, dia_vencimento)
cursor.callproc('sp_matricular_aluno', args)
# Imprime um ponto para cada aluno processado para mostrar progresso
print(".", end="")
sys.stdout.flush()
except mysql.connector.Error as err:
if err.errno != 1062: # Ignora o erro de duplicidade, mas mostra outros
print(f"\nErro ao matricular aluno {i+1}: {err}")
print() # Pula uma linha após a barra de progresso
conn.commit()
cursor.close()
print("Matrícula de alunos concluída.")
def gerar_historico_financeiro(conn, meses_historico):
"""Gera mensalidades e pagamentos para os meses anteriores."""
if meses_historico == 0:
print("\nGeração de histórico pulada (0 meses).")
return
cursor = conn.cursor()
print(f"\nGerando histórico financeiro de {meses_historico} meses para os alunos...")
try:
cursor.execute("SELECT id_aluno, nome_completo FROM alunos")
alunos = cursor.fetchall()
for id_aluno, nome_completo in alunos:
cursor.execute(
"SELECT valor_devido, data_vencimento FROM mensalidades WHERE id_aluno = %s ORDER BY mes_referencia DESC LIMIT 1",
(id_aluno,)
)
resultado = cursor.fetchone()
if not resultado:
continue
valor_base, ultima_data_venc = resultado
for i in range(1, meses_historico + 1):
mes_referencia = (ultima_data_venc - timedelta(days=i*30)).replace(day=1)
data_vencimento = mes_referencia.replace(day=ultima_data_venc.day)
sql_insert_mensalidade = "INSERT INTO mensalidades (id_aluno, mes_referencia, valor_devido, data_vencimento) VALUES (%s, %s, %s, %s)"
cursor.execute(sql_insert_mensalidade, (id_aluno, mes_referencia, valor_base, data_vencimento))
id_mensalidade_gerada = cursor.lastrowid
if random.random() <= 0.85:
forma_pagamento = random.choice(['Pix', 'Cartão de Crédito', 'Dinheiro'])
cursor.callproc('sp_registrar_pagamento', (id_mensalidade_gerada, valor_base, forma_pagamento, 'Pagamento de histórico'))
# Imprime um ponto para cada aluno para mostrar progresso
print(".", end="")
sys.stdout.flush()
print()
conn.commit()
print("Geração de histórico concluída.")
except mysql.connector.Error as err:
print(f"\nErro ao gerar histórico: {err}")
finally:
cursor.close()
if __name__ == '__main__':
parser = argparse.ArgumentParser(description="Script de carga de dados para o banco da academia.")
parser.add_argument('--alunos', type=int, default=50, help='Número de alunos a serem matriculados.')
parser.add_argument('--meses', type=int, default=6, help='Número de meses de histórico financeiro a serem gerados.')
args = parser.parse_args()
conn = conectar_db()
if conn and conn.is_connected():
limpar_tabelas(conn)
matricular_alunos(conn, args.alunos)
gerar_historico_financeiro(conn, args.meses)
conn.close()
print("\nProcesso de carga de dados finalizado.")
# Para executar o script, use:
# python insertdata.py --alunos 100 --meses 12
# Isso irá gerar 100 alunos e um histórico financeiro de 12 meses.
Raw
createtable.sql
CREATE TABLE alunos (
id_aluno INT PRIMARY KEY AUTO_INCREMENT,
nome_completo VARCHAR(255) NOT NULL,
cpf VARCHAR(11) UNIQUE NOT NULL,
data_nascimento DATE,
email VARCHAR(100) UNIQUE,
telefone VARCHAR(20),
data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ativo BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE mensalidades (
id_mensalidade INT PRIMARY KEY AUTO_INCREMENT,
id_aluno INT NOT NULL,
mes_referencia DATE NOT NULL,
valor_devido DECIMAL(10, 2) NOT NULL,
data_vencimento DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'Pendente',
FOREIGN KEY (id_aluno) REFERENCES alunos(id_aluno) ON DELETE RESTRICT,
UNIQUE (id_aluno, mes_referencia)
);
CREATE TABLE pagamentos (
id_pagamento INT PRIMARY KEY AUTO_INCREMENT,
id_mensalidade INT NOT NULL,
valor_pago DECIMAL(10, 2) NOT NULL,
data_pagamento DATE NOT NULL,
forma_pagamento VARCHAR(50),
observacao TEXT,
FOREIGN KEY (id_mensalidade) REFERENCES mensalidades(id_mensalidade) ON DELETE RESTRICT
);
Raw
transactions.sql
DELIMITER $$
-- ===================================================================================
-- PROCEDURE 1: Matricular um novo aluno e gerar sua primeira mensalidade.
-- Versão refinada com mais parâmetros e lógica de vencimento aprimorada.
-- ===================================================================================
DROP PROCEDURE IF EXISTS sp_matricular_aluno$$
CREATE PROCEDURE sp_matricular_aluno(
IN p_nome_completo VARCHAR(255),
IN p_cpf VARCHAR(11),
IN p_email VARCHAR(100),
IN p_telefone VARCHAR(20),
IN p_data_nascimento DATE, -- Parâmetro adicionado para o cadastro completo
IN p_valor_mensal DECIMAL(10,2),
IN p_dia_vencimento INT -- Dia do mês para o vencimento (ex: 10)
)
BEGIN
DECLARE v_id_aluno INT;
DECLARE v_data_vencimento DATE;
-- Se algo der errado, desfaz todas as alterações.
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Falha na matrícula. Nenhuma alteração foi aplicada.';
END;
START TRANSACTION;
-- 1. Cria o aluno
INSERT INTO alunos (nome_completo, cpf, email, telefone, data_nascimento, ativo)
VALUES (p_nome_completo, p_cpf, p_email, p_telefone, p_data_nascimento, TRUE);
SET v_id_aluno = LAST_INSERT_ID();
-- 2. Calcula a data do primeiro vencimento de forma inteligente
SET v_data_vencimento = STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-', p_dia_vencimento), '%Y-%m-%d');
-- Se o dia do vencimento já passou neste mês, a primeira cobrança é para o mês seguinte.
IF v_data_vencimento < CURDATE() THEN
SET v_data_vencimento = v_data_vencimento + INTERVAL 1 MONTH;
END IF;
-- 3. Gera a primeira mensalidade
INSERT INTO mensalidades (id_aluno, mes_referencia, valor_devido, data_vencimento, status)
VALUES (
v_id_aluno,
DATE_FORMAT(v_data_vencimento, '%Y-%m-01'), -- O mês de referência é o mesmo do vencimento
p_valor_mensal,
v_data_vencimento,
'Pendente'
);
COMMIT;
END$$
-- ===================================================================================
-- PROCEDURE 2: Registrar um pagamento (funciona em conjunto com o trigger).
-- ===================================================================================
DROP PROCEDURE IF EXISTS sp_registrar_pagamento$$
CREATE PROCEDURE sp_registrar_pagamento(
IN p_id_mensalidade INT,
IN p_valor_pago DECIMAL(10,2),
IN p_forma_pgto VARCHAR(50),
IN p_obs TEXT
)
BEGIN
-- Rollback automático se der erro.
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Falha ao registrar pagamento. Nenhuma alteração foi aplicada.';
END;
START TRANSACTION;
-- 1. Insere o pagamento.
INSERT INTO Pagamentos (id_mensalidade, valor_pago, data_pagamento, forma_pagamento, observacao)
VALUES (p_id_mensalidade, p_valor_pago, CURDATE(), p_forma_pgto, p_obs);
-- 2. O trigger 'tg_after_insert_pagamento' fará o resto do trabalho.
COMMIT;
END$$
DELIMITER ;
DELIMITER $$
-- ===================================================================================
-- PROCEDURE: sp_estornar_pagamento
-- Objetivo: Desfaz um registro de pagamento e recalcula o status da mensalidade
-- associada de forma segura e atômica (ACID).
-- ===================================================================================
DROP PROCEDURE IF EXISTS sp_estornar_pagamento$$
CREATE PROCEDURE sp_estornar_pagamento(
IN p_id_pagamento INT
)
BEGIN
-- Variáveis para armazenar dados intermediários
DECLARE v_id_mensalidade INT;
DECLARE v_total_pago DECIMAL(10,2);
DECLARE v_data_vencimento DATE;
-- Handler para garantir que, se qualquer erro ocorrer, a transação
-- seja desfeita (ROLLBACK) e uma mensagem clara seja retornada.
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Falha ao estornar pagamento. Nenhuma alteração foi aplicada.';
END;
-- Inicia a transação para garantir que todas as operações sejam um bloco único.
START TRANSACTION;
-- 1. Encontra a mensalidade associada ao pagamento e bloqueia a linha
-- com 'FOR UPDATE' para evitar que outra transação a modifique ao mesmo tempo.
SELECT id_mensalidade
INTO v_id_mensalidade
FROM Pagamentos
WHERE id_pagamento = p_id_pagamento
FOR UPDATE;
-- 2. Valida se o pagamento realmente existe.
IF v_id_mensalidade IS NULL THEN
-- Força um erro para ser capturado pelo EXIT HANDLER.
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Pagamento não encontrado.';
END IF;
-- 3. Exclui o registro do pagamento da tabela.
DELETE FROM Pagamentos WHERE id_pagamento = p_id_pagamento;
-- 4. Recalcula o valor total que ainda resta pago para a mensalidade.
SELECT IFNULL(SUM(valor_pago), 0)
INTO v_total_pago
FROM Pagamentos
WHERE id_mensalidade = v_id_mensalidade;
-- 5. Busca a data de vencimento para definir o status corretamente.
SELECT data_vencimento
INTO v_data_vencimento
FROM Mensalidades
WHERE id_mensalidade = v_id_mensalidade;
-- 6. Atualiza o status da mensalidade com base na nova situação financeira.
UPDATE Mensalidades
SET status = CASE
-- Se ainda resta algum valor pago, é parcial.
WHEN v_total_pago > 0 THEN 'Pago Parcialmente'
-- Se não resta nada pago E a data já passou, está vencido.
WHEN v_data_vencimento < CURDATE() THEN 'Vencido'
-- Se não, volta a ser pendente.
ELSE 'Pendente'
END
WHERE id_mensalidade = v_id_mensalidade;
-- Se todos os passos foram executados com sucesso, confirma as alterações.
COMMIT;
END$$
DELIMITER ;
Raw
triggersandindex.sql
-- Trigger 1: ATUALIZA STATUS DA MENSALIDADE APÓS INSERIR UM PAGAMENTO (LÓGICA DE PAGAMENTO PARCIAL)
DROP TRIGGER IF EXISTS tg_after_insert_pagamento;
CREATE TRIGGER tg_after_insert_pagamento
AFTER INSERT ON pagamentos
FOR EACH ROW
BEGIN
DECLARE v_total_pago DECIMAL(10, 2);
DECLARE v_valor_devido DECIMAL(10, 2);
SELECT IFNULL(SUM(valor_pago), 0) INTO v_total_pago FROM pagamentos WHERE id_mensalidade = NEW.id_mensalidade;
SELECT valor_devido INTO v_valor_devido FROM mensalidades WHERE id_mensalidade = NEW.id_mensalidade;
IF v_total_pago >= v_valor_devido THEN
UPDATE mensalidades SET status = 'Pago' WHERE id_mensalidade = NEW.id_mensalidade;
ELSE
UPDATE mensalidades SET status = 'Pago Parcialmente' WHERE id_mensalidade = NEW.id_mensalidade;
END IF;
END$$
DELIMITER $$
-- Trigger 2: RECALCULA O STATUS DA MENSALIDADE APÓS EXCLUIR UM PAGAMENTO
DROP TRIGGER IF EXISTS tg_after_delete_pagamento;
CREATE TRIGGER tg_after_delete_pagamento
AFTER DELETE ON pagamentos
FOR EACH ROW
BEGIN
DECLARE v_total_pago DECIMAL(10, 2);
DECLARE v_data_vencimento DATE;
SELECT IFNULL(SUM(valor_pago), 0) INTO v_total_pago FROM pagamentos WHERE id_mensalidade = OLD.id_mensalidade;
SELECT data_vencimento INTO v_data_vencimento FROM mensalidades WHERE id_mensalidade = OLD.id_mensalidade;
IF v_total_pago > 0 THEN
UPDATE mensalidades SET status = 'Pago Parcialmente' WHERE id_mensalidade = OLD.id_mensalidade;
ELSEIF v_data_vencimento < CURDATE() THEN
UPDATE mensalidades SET status = 'Vencido' WHERE id_mensalidade = OLD.id_mensalidade;
ELSE
UPDATE mensalidades SET status = 'Pendente' WHERE id_mensalidade = OLD.id_mensalidade;
END IF;
END$$
-- Trigger 3: IMPEDE A EXCLUSÃO DE ALUNOS COM QUALQUER TIPO DE PENDÊNCIA FINANCEIRA
DROP TRIGGER IF EXISTS tg_before_delete_aluno;
CREATE TRIGGER tg_before_delete_aluno
BEFORE DELETE ON alunos
FOR EACH ROW
BEGIN
DECLARE qtd_dividas INT;
SELECT COUNT(*) INTO qtd_dividas
FROM mensalidades
WHERE id_aluno = OLD.id_aluno
AND status IN ('Pendente', 'Vencido', 'Pago Parcialmente');
IF qtd_dividas > 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Não é possível excluir aluno com pendências financeiras (mensalidades em aberto ou vencidas).';
END IF;
END$$
-- 1. Índice para permitir que a busca de alunos por nome seja rápida.
CREATE INDEX idx_alunos_nome ON Alunos(nome_completo);
-- 2. Índice para acelerar relatórios financeiros que buscam pagamentos por data.
CREATE INDEX idx_pagamentos_data ON Pagamentos(data_pagamento);
-- 3. Índice composto para otimizar tarefas automáticas do sistema, como a atualização
-- de mensalidades pendentes para vencidas.
CREATE INDEX idx_mensalidades_status_vencimento ON Mensalidades(status, data_vencimento);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment