Skip to content

Instantly share code, notes, and snippets.

@Emanuel3queijos

Emanuel3queijos/main.ipynb

Created November 14, 2024 05:01
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Emanuel3queijos/571ef8677a468beba9bda5a2cb673010 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Emanuel3queijos/571ef8677a468beba9bda5a2cb673010 to your computer and use it in GitHub Desktop.
Download ZIP
main.ipynb
Display the source blob
Display the rendered blob
Raw
main.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/Emanuel3queijos/571ef8677a468beba9bda5a2cb673010/main.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "XNQIkJMS6LOm"
},
"source": [
"# Inicializar bibliotecas"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true,
"id": "pdf2osN86LOp"
},
"outputs": [],
"source": [
"!wget https://emanuel3queijos.github.io/AWS-University-ETL/python_scripts/requirements.txt -O requirements.txt\n",
"!sed -i '/pywin32/d' requirements.txt\n",
"!pip install -r requirements.txt\n"
]
},
{
"cell_type": "markdown",
"source": [
"# Scripts para adicionar data dentro do banco\n",
"\n"
],
"metadata": {
"id": "4X9oQ2WEH284"
}
},
{
"cell_type": "code",
"source": [
"import psycopg2\n",
"\n",
"def create_connection():\n",
" try:\n",
" conn = psycopg2.connect(\n",
" host=\"db-manu-etluniversityproject-instance.c44vb5orqds7.us-east-1.rds.amazonaws.com\",\n",
" dbname=\"AWS-University-ETL-DB\",\n",
" user=\"postgres\",\n",
" password=\"Emanu0710**\",\n",
" port=5432\n",
" )\n",
"\n",
" return conn\n",
" except Exception as error:\n",
" print(f\"Erro ao conectar ao banco de dados: {error}\")\n",
" return None\n",
"create_connection()"
],
"metadata": {
"id": "Ksa45GelElrN"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"def inserir_departamentos():\n",
" conn = create_connection()\n",
" if conn:\n",
" try:\n",
" cur = conn.cursor()\n",
"\n",
" departamentos = [\n",
" (\"Escola de Tecnologia\", \"Departamento focado em áreas tecnológicas.\"),\n",
" (\"Escola de Engenharia\", \"Departamento voltado para as engenharias.\"),\n",
" (\"Escola de Saúde\", \"Departamento voltado para ciências da saúde.\"),\n",
" (\"Ciências Biológicas\", \"Departamento voltado para biologia e afins.\")\n",
" ]\n",
" for nome, descricao in departamentos:\n",
" cur.execute(\"\"\"\n",
" INSERT INTO tb_departamento (nome, descricao)\n",
" VALUES (%s, %s)\n",
" RETURNING id, nome\n",
" \"\"\", (nome, descricao))\n",
" cod_departamento = cur.fetchone()[0]\n",
" print(f\"Departamento {nome, id} inserido com sucesso. Código: {cod_departamento}\")\n",
"\n",
" conn.commit()\n",
" print(\"Departamentos e inseridos com sucesso.\")\n",
"\n",
" except Exception as error:\n",
" print(f\"Erro ao inserir dados: {error}\")\n",
" conn.rollback()\n",
" finally:\n",
" cur.close()\n",
" conn.close()\n",
"\n",
"inserir_departamentos()\n"
],
"metadata": {
"id": "FPsqKqwYJRft",
"outputId": "1e23fb4f-2425-4182-da48-cb9b8d743696",
"colab": {
"base_uri": "https://localhost:8080/"
}
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"Departamento ('Escola de Tecnologia', <built-in function id>) inserido com sucesso. Código: 9\n",
"Departamento ('Escola de Engenharia', <built-in function id>) inserido com sucesso. Código: 10\n",
"Departamento ('Escola de Saúde', <built-in function id>) inserido com sucesso. Código: 11\n",
"Departamento ('Ciências Biológicas', <built-in function id>) inserido com sucesso. Código: 12\n",
"Departamentos e inseridos com sucesso.\n"
]
}
]
},
{
"cell_type": "code",
"source": [
"def inserir_cursos():\n",
" conn = create_connection()\n",
" if conn:\n",
" try:\n",
" cur = conn.cursor()\n",
"\n",
" cursos_por_departamento = {\n",
" \"Escola de Tecnologia\": [\n",
" (\"Engenharia de Software\", \"Curso voltado ao desenvolvimento de software.\", 4, 1200.00),\n",
" (\"Ciência da Computação\", \"Foco em computação e sistemas.\", 4, 1150.00),\n",
" (\"Inteligência Artificial\", \"Curso para aprendizado de IA.\", 4, 1300.00),\n",
" (\"Redes de Computadores\", \"Infraestrutura e comunicação de redes.\", 3, 1100.00)\n",
" ],\n",
" \"Escola de Engenharia\": [\n",
" (\"Engenharia Civil\", \"Foco em obras e infraestrutura.\", 5, 1400.00),\n",
" (\"Engenharia Elétrica\", \"Curso de sistemas elétricos.\", 5, 1350.00),\n",
" (\"Engenharia Mecânica\", \"Curso sobre mecânica aplicada.\", 5, 1380.00),\n",
" (\"Engenharia de Controle e Automação\", \"Automação industrial.\", 5, 1450.00)\n",
" ],\n",
" \"Escola de Saúde\": [\n",
" (\"Medicina\", \"Foco em prática médica.\", 6, 2000.00),\n",
" (\"Enfermagem\", \"Prática de enfermagem.\", 4, 1100.00),\n",
" (\"Farmácia\", \"Estudos sobre medicamentos.\", 4, 1250.00),\n",
" (\"Biomedicina\", \"Ciência biomédica.\", 4, 1200.00)\n",
" ],\n",
" \"Ciências Biológicas\": [\n",
" (\"Biologia\", \"Estudo da vida.\", 4, 1100.00),\n",
" (\"Ecologia\", \"Curso sobre ecossistemas.\", 4, 1050.00),\n",
" (\"Zoologia\", \"Estudo dos animais.\", 4, 1100.00),\n",
" (\"Botânica\", \"Estudo das plantas.\", 4, 1000.00)\n",
" ]\n",
" }\n",
"\n",
" for nome_departamento, cursos in cursos_por_departamento.items():\n",
" cur.execute(\"\"\"\n",
" SELECT id FROM tb_departamento WHERE nome = %s\n",
" \"\"\", (nome_departamento,))\n",
" cod_departamento = cur.fetchone()\n",
"\n",
" if cod_departamento is None:\n",
" print(f\"Departamento '{nome_departamento}' não encontrado.\")\n",
" continue\n",
"\n",
" cod_departamento = cod_departamento[0]\n",
"\n",
" for nome_curso, descricao_curso, duracao, mensalidade in cursos:\n",
" cur.execute(\"\"\"\n",
" INSERT INTO tb_curso (nome, descricao, id_departamento, duracao, valor_mensalidade)\n",
" VALUES (%s, %s, %s, %s, %s)\n",
" RETURNING nome\n",
" \"\"\", (nome_curso, descricao_curso, cod_departamento, duracao, mensalidade))\n",
" nome = cur.fetchone()[0]\n",
" print(f\"Curso {nome_curso} inserido com sucesso. Código:\")\n",
"\n",
" conn.commit()\n",
" print(\"Departamentos, cursos e disciplinas inseridos com sucesso.\")\n",
"\n",
" except Exception as error:\n",
" print(f\"Erro ao inserir dados: {error}\")\n",
" conn.rollback()\n",
" finally:\n",
" cur.close()\n",
" conn.close()\n",
"\n",
"inserir_cursos()\n"
],
"metadata": {
"id": "KLtdLmYUELLC"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"# Script para inserir disciplinas e depois referencia-las aos cursos(acho q ta funfando)\n",
"from faker import Faker\n",
"import psycopg2\n",
"import random\n",
"\n",
"fake = Faker('pt_BR')\n",
"\n",
"\n",
"\n",
"def gerar_disciplinas_por_semestre():\n",
" disciplinas = {}\n",
" for semestre in range(1, 9):\n",
" disciplinas[semestre] = []\n",
" for _ in range(6):\n",
" disciplina = {\n",
" \"nome\": fake.sentence(nb_words=3),\n",
" \"creditos\": fake.random_int(min=60, max=80),\n",
" \"departamento_id\": random.choice([9, 10, 11]),\n",
" \"semestre\": semestre\n",
" }\n",
" disciplinas[semestre].append(disciplina)\n",
" return disciplinas\n",
"\n",
"def inserir_disciplinas():\n",
" conn = create_connection()\n",
" if conn:\n",
" try:\n",
" cur = conn.cursor()\n",
"\n",
" for semestre, disciplinas in gerar_disciplinas_por_semestre().items():\n",
" for disciplina_data in disciplinas:\n",
" nome_disciplina = disciplina_data[\"nome\"]\n",
" creditos = disciplina_data[\"creditos\"]\n",
" departamento = disciplina_data[\"departamento_id\"]\n",
"\n",
" cur.execute(\"\"\"\n",
" INSERT INTO tb_disciplina (nome, creditos, id_departamento)\n",
" VALUES (%s, %s, %s)\n",
" RETURNING id\n",
" \"\"\", (nome_disciplina, creditos, departamento))\n",
" cod_disciplina = cur.fetchone()[0]\n",
"\n",
" cur.execute(\"\"\"\n",
" SELECT id FROM tb_curso WHERE id_departamento = %s\n",
" \"\"\", (departamento,))\n",
" cursos = cur.fetchall()\n",
"\n",
" if cursos:\n",
" for curso in cursos:\n",
" cod_curso = curso[0]\n",
" cur.execute(\"\"\"\n",
" INSERT INTO tb_curso_disciplina (id_curso, id_disciplina, id_departamento, semestre)\n",
" VALUES (%s, %s, %s, %s)\n",
" \"\"\", (cod_curso, cod_disciplina, departamento, semestre))\n",
"\n",
" print(f\"Disciplina '{nome_disciplina}' inserida no curso '{cod_curso}' do departamento {departamento}, semestre {semestre}.\")\n",
" else:\n",
" print(f\"Nenhum curso encontrado para o departamento {departamento}.\")\n",
"\n",
" conn.commit()\n",
" print(\"Disciplinas inseridas com sucesso.\")\n",
"\n",
" except Exception as error:\n",
" print(f\"Erro ao inserir dados: {error}\")\n",
" conn.rollback()\n",
" finally:\n",
" cur.close()\n",
" conn.close()\n",
"\n",
"inserir_disciplinas()\n"
],
"metadata": {
"id": "3EPldY6rENWE"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"from faker import Faker\n",
"import random\n",
"\n",
"fake = Faker('pt_BR')\n",
"def inserir_professores():\n",
" conn = create_connection()\n",
" if conn:\n",
" try:\n",
" cur = conn.cursor()\n",
" cur.execute(\"SELECT id, id_departamento FROM tb_disciplina\")\n",
" disciplinas = cur.fetchall()\n",
"\n",
" for cod_disciplina, cod_departamento in disciplinas:\n",
" num_professores = random.randint(1, 2)\n",
"\n",
" for _ in range(num_professores):\n",
" nome = fake.first_name()\n",
" sobrenome = fake.last_name()\n",
" nome_completo = f\"{nome}{sobrenome}\"\n",
" matricula = fake.unique.random_number(digits=8)\n",
" email = f\"{fake.user_name()}.{matricula}@ucsal.edu.br\"\n",
" telefone = fake.phone_number()[:15]\n",
" semestre = random.randint(1, 9)\n",
" endereco = fake.address()\n",
" descricao = fake.sentence()\n",
" cpf = fake.unique.numerify('###########')\n",
"\n",
" cur.execute(\"\"\"\n",
" INSERT INTO tb_professor (nome_professor, email_professor, id_departamento, telefone, endereco, matricula, cpf)\n",
" VALUES (%s, %s, %s, %s, %s, %s, %s)\n",
" RETURNING matricula\n",
" \"\"\", (nome_completo, email, cod_departamento, telefone, endereco, matricula, cpf))\n",
"\n",
" cod_professor = cur.fetchone()[0]\n",
"\n",
" cur.execute(\"\"\"\n",
" INSERT INTO tb_professor_disciplina (matricula_professor, id_disciplina, semestre, descricao)\n",
" VALUES (%s,%s,%s, %s)\n",
" \"\"\", (cod_professor, cod_disciplina, semestre, descricao))\n",
"\n",
" print(f\"Professor '{nome_completo}' alocado na disciplina de código '{cod_disciplina}', CPF '{cpf}', email: {email}.\")\n",
"\n",
" conn.commit()\n",
" print(\"Professores inseridos e alocados com sucesso.\")\n",
"\n",
" except Exception as error:\n",
" print(f\"Erro ao inserir professores: {error}\")\n",
" conn.rollback()\n",
" finally:\n",
" cur.close()\n",
" conn.close()\n",
"\n",
"inserir_professores()\n"
],
"metadata": {
"id": "MY7mlcEZPtFu"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"from faker import Faker\n",
"import random\n",
"import psycopg2\n",
"\n",
"fake = Faker('pt_BR')\n",
"\n",
"\n",
"\n",
"def inserir_alunos():\n",
" conn = create_connection()\n",
" if conn:\n",
" try:\n",
" cur = conn.cursor()\n",
" conn.autocommit = False\n",
"\n",
" cur.execute(\"SELECT id, id_departamento, nome FROM tb_curso\")\n",
" cursos = cur.fetchall()\n",
"\n",
" alunos_batch = []\n",
" aluno_disciplinas_batch = set()\n",
"\n",
" for _ in range(1000):\n",
" nome_aluno = fake.unique.name()\n",
" matricula = fake.unique.random_number(digits=8)\n",
" email = f\"{fake.user_name()}.{matricula}@ucsal.edu.br\"\n",
" cpf = fake.unique.numerify('###########')\n",
" media_global = round(random.uniform(0, 10), 2)\n",
" status = random.choice(['A', 'I'])\n",
"\n",
" curso = random.choice(cursos)\n",
" id_curso, id_departamento, nome_curso = curso\n",
"\n",
" alunos_batch.append((nome_aluno, email, cpf, media_global, status, id_curso, matricula, nome_curso))\n",
" cur.execute(\"SELECT id, nome FROM tb_disciplina WHERE id_departamento = %s\", (id_departamento,))\n",
" disciplinas_curso = cur.fetchall()\n",
"\n",
" for _ in range(random.randint(1, 4)):\n",
" if disciplinas_curso:\n",
" disciplina = random.choice(disciplinas_curso)\n",
" id_disciplina, nome_disciplina = disciplina\n",
" ano = random.randint(2015, 2023)\n",
" semestre = random.randint(1, 2)\n",
" nota_final = round(random.uniform(0, 10), 2)\n",
" status_disciplina = 'aprovado' if nota_final >= 5 else 'reprovado'\n",
" nota1 = round(random.uniform(0, 5), 2)\n",
" nota2 = round(random.uniform(0, 5), 2)\n",
"\n",
" record = (matricula, id_disciplina, nome_disciplina, ano, semestre, nota_final, status_disciplina, nota1, nota2)\n",
" aluno_disciplinas_batch.add(record)\n",
"\n",
" if len(alunos_batch) >= 1000:\n",
" cur.executemany(\"\"\"\n",
" INSERT INTO tb_aluno (nome_aluno, email, cpf, media_global, status, id_curso, matricula, nome_curso)\n",
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s)\n",
" \"\"\", alunos_batch)\n",
"\n",
" cur.executemany(\"\"\"\n",
" INSERT INTO tb_aluno_disciplina (matricula_aluno, id_disciplina, nome_disciplina, ano, semestre, nota_final, status_disciplina, nota1, nota2)\n",
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)\n",
" \"\"\", list(aluno_disciplinas_batch))\n",
"\n",
" print(f\"Batch de {len(alunos_batch)} alunos inseridos com sucesso.\")\n",
" conn.commit()\n",
" alunos_batch.clear()\n",
" aluno_disciplinas_batch.clear()\n",
"\n",
" if alunos_batch:\n",
" cur.executemany(\"\"\"\n",
" INSERT INTO tb_aluno (nome_aluno, email, cpf, media_global, status, id_curso, matricula, nome_curso)\n",
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s)\n",
" \"\"\", alunos_batch)\n",
"\n",
" if aluno_disciplinas_batch:\n",
" cur.executemany(\"\"\"\n",
" INSERT INTO tb_aluno_disciplina (matricula_aluno, id_disciplina, nome_disciplina, ano, semestre, nota_final, status_disciplina, nota1, nota2)\n",
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)\n",
" \"\"\", list(aluno_disciplinas_batch))\n",
"\n",
" conn.commit()\n",
" print(\"Alunos e associações inseridos com sucesso.\")\n",
" except Exception as error:\n",
" print(f\"Erro ao inserir alunos: {error}\")\n",
" conn.rollback()\n",
" finally:\n",
" cur.close()\n",
" conn.close()\n",
"\n",
"inserir_alunos()\n"
],
"metadata": {
"id": "eD9Af8oDLDWa"
},
"execution_count": null,
"outputs": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.12.4"
},
"colab": {
"provenance": [],
"include_colab_link": true
}
},
"nbformat": 4,
"nbformat_minor": 0
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment