generar db_pg.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Con este documento se crea una base de datos en PostgreSQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importación de bibliotecas\n",
    "from datetime import date\n",
    "import pandas as pd\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creación de dataframes\n",
    "profes = pd.read_csv('./data/claustro.csv', sep=';')\n",
    "DS1 = pd.read_csv('./data/listado_DS_clase_1.csv', sep=';')\n",
    "DS2 = pd.read_csv('./data/listado_DS_clase_2.csv', sep=';')\n",
    "FS1 = pd.read_csv('./data/listado_FS_clase_1.csv', sep=';')\n",
    "FS2 = pd.read_csv('./data/listado_FS_clase_2.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nombre_campus</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>campus_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Madrid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Valencia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          nombre_campus\n",
       "campus_id              \n",
       "1                Madrid\n",
       "2              Valencia"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creación de dataframe campus\n",
    "datos_campus = {'nombre_campus':profes['Campus'].unique()}\n",
    "campus = pd.DataFrame(datos_campus, index=range(1, len(profes['Campus'].unique()) +1))\n",
    "campus.index.name = \"campus_id\"\n",
    "campus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nombre_promocion</th>\n",
       "      <th>fecha_comienzo</th>\n",
       "      <th>vertical</th>\n",
       "      <th>campus_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>promocion_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Septiembre</td>\n",
       "      <td>18/09/2023</td>\n",
       "      <td>DS</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Septiembre</td>\n",
       "      <td>18/09/2023</td>\n",
       "      <td>FS</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Febrero</td>\n",
       "      <td>12/02/2024</td>\n",
       "      <td>DS</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Febrero</td>\n",
       "      <td>12/02/2024</td>\n",
       "      <td>FS</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Sin datos</td>\n",
       "      <td>Sin datos</td>\n",
       "      <td>Sin datos</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             nombre_promocion fecha_comienzo   vertical  campus_id\n",
       "promocion_id                                                      \n",
       "1                  Septiembre     18/09/2023         DS          1\n",
       "2                  Septiembre     18/09/2023         FS          1\n",
       "3                     Febrero     12/02/2024         DS          1\n",
       "4                     Febrero     12/02/2024         FS          2\n",
       "5                   Sin datos      Sin datos  Sin datos          1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creación de dataframe promocion\n",
    "datos_promocion = {'nombre_promocion': [], 'fecha_comienzo': [], 'vertical': [], 'campus_id': []}\n",
    "for i in [DS1, FS1, DS2, FS2]:\n",
    "    if len(i['Promoción'].unique()) == 1:\n",
    "        datos_promocion['nombre_promocion'].append(*i['Promoción'].unique())\n",
    "    else:\n",
    "        print('Error. Revisar los datos de \\'Promoción\\' a mano.')\n",
    "\n",
    "    if len(i['Fecha_comienzo'].unique()) == 1:\n",
    "        datos_promocion['fecha_comienzo'].append(*i['Fecha_comienzo'].unique())\n",
    "    else:\n",
    "        print('Error. Revisar los datos de \\'Fecha_comienzo\\' a mano.')\n",
    "\n",
    "    if 'Proyecto_EDA' in i.columns:\n",
    "        datos_promocion['vertical'].append('DS')\n",
    "    else:\n",
    "        datos_promocion['vertical'].append('FS')\n",
    "\n",
    "    if i['Campus'].unique() == 'Madrid':\n",
    "        datos_promocion['campus_id'].append(1)\n",
    "    else:\n",
    "        datos_promocion['campus_id'].append(2)\n",
    "\n",
    "# Ahora generamos el dataframe:\n",
    "promocion = pd.DataFrame(datos_promocion, index = range(1, len(datos_promocion) + 1))\n",
    "promocion.index.name = \"promocion_id\"\n",
    "\n",
    "list_row = [ 'Sin datos', \"Sin datos\", 'Sin datos', 1]\n",
    "promocion.loc[5] = list_row\n",
    "\n",
    "promocion\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nombre</th>\n",
       "      <th>rol</th>\n",
       "      <th>promocion_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>profesor_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Noa Yáñez</td>\n",
       "      <td>TA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Saturnina Benitez</td>\n",
       "      <td>TA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anna Feliu</td>\n",
       "      <td>TA</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rosalva Ayuso</td>\n",
       "      <td>TA</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ana Sofía Ferrer</td>\n",
       "      <td>TA</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Angélica Corral</td>\n",
       "      <td>TA</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ariel Lledó</td>\n",
       "      <td>TA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Mario Prats</td>\n",
       "      <td>LI</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Luis Ángel Suárez</td>\n",
       "      <td>LI</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>María Dolores Diaz</td>\n",
       "      <td>LI</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         nombre rol  promocion_id\n",
       "profesor_id                                      \n",
       "1                     Noa Yáñez  TA             1\n",
       "2             Saturnina Benitez  TA             1\n",
       "3                    Anna Feliu  TA             2\n",
       "4                 Rosalva Ayuso  TA             5\n",
       "5              Ana Sofía Ferrer  TA             4\n",
       "6               Angélica Corral  TA             5\n",
       "7                   Ariel Lledó  TA             1\n",
       "8                   Mario Prats  LI             4\n",
       "9             Luis Ángel Suárez  LI             2\n",
       "10           María Dolores Diaz  LI             1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creación de dataframe claustro:\n",
    "datos_claustro = {'nombre': profes['Nombre'].to_list(), \n",
    "                  'rol': profes['Rol'].to_list(), \n",
    "                  'promocion_id': [1, 1, 2, 5, 4, 5, 1, 4, 2, 1]}\n",
    "datos_claustro\n",
    "claustro = pd.DataFrame(datos_claustro, index = range(1, len(profes)+1))\n",
    "claustro.index.name = \"profesor_id\"\n",
    "claustro"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nombre</th>\n",
       "      <th>email</th>\n",
       "      <th>promocion_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>alumno_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jafet Casals</td>\n",
       "      <td>Jafet_Casals@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jorge Manzanares</td>\n",
       "      <td>Jorge_Manzanares@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Onofre Adadia</td>\n",
       "      <td>Onofre_Adadia@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Merche Prada</td>\n",
       "      <td>Merche_Prada@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Pilar Abella</td>\n",
       "      <td>Pilar_Abella@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Leoncio Tena</td>\n",
       "      <td>Leoncio_Tena@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Odalys Torrijos</td>\n",
       "      <td>Odalys_Torrijos@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Eduardo Caparrós</td>\n",
       "      <td>Eduardo_Caparrós@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Ignacio Goicoechea</td>\n",
       "      <td>Ignacio_Goicoechea@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Clementina Santos</td>\n",
       "      <td>Clementina_Santos@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Daniela Falcó</td>\n",
       "      <td>Daniela_Falcó@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Abraham Vélez</td>\n",
       "      <td>Abraham_Vélez@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Maximiliano Menéndez</td>\n",
       "      <td>Maximiliano_Menéndez@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Anita Heredia</td>\n",
       "      <td>Anita_Heredia@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Eli Casas</td>\n",
       "      <td>Eli_Casas@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Amor Larrañaga</td>\n",
       "      <td>Amor_Larrañaga@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Teodoro Alberola</td>\n",
       "      <td>Teodoro_Alberola@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Cleto Plana</td>\n",
       "      <td>Cleto_Plana@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Aitana Sebastián</td>\n",
       "      <td>Aitana_Sebastián@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Dolores Valbuena</td>\n",
       "      <td>Dolores_Valbuena@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Julie Ferrer</td>\n",
       "      <td>Julie_Ferrer@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Mireia Cabañas</td>\n",
       "      <td>Mireia_Cabañas@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Flavia Amador</td>\n",
       "      <td>Flavia_Amador@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Albino Macias</td>\n",
       "      <td>Albino_Macias@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Ester Sánchez</td>\n",
       "      <td>Ester_Sánchez@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Luis Miguel Galvez</td>\n",
       "      <td>Luis_Miguel_Galvez@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Loida Arellano</td>\n",
       "      <td>Loida_Arellano@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Heraclio Duque</td>\n",
       "      <td>Heraclio_Duque@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Herberto Figueras</td>\n",
       "      <td>Herberto_Figueras@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Guillermo Borrego</td>\n",
       "      <td>Guillermo_Borrego@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Sergio Aguirre</td>\n",
       "      <td>Sergio_Aguirre@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Carlito Carrión</td>\n",
       "      <td>Carlito_Carrión@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Haydée Figueroa</td>\n",
       "      <td>Haydée_Figueroa@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Chita Mancebo</td>\n",
       "      <td>Chita_Mancebo@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Joaquina Asensio</td>\n",
       "      <td>Joaquina_Asensio@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Cristian Sarabia</td>\n",
       "      <td>Cristian_Sarabia@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Isabel Ibáñez</td>\n",
       "      <td>Isabel_Ibáñez@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Desiderio Jordá</td>\n",
       "      <td>Desiderio_Jordá@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Rosalina Llanos</td>\n",
       "      <td>Rosalina_Llanos@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Teresa Laguna</td>\n",
       "      <td>Teresa_Laguna@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Estrella Murillo</td>\n",
       "      <td>Estrella_Murillo@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Ernesto Uriarte</td>\n",
       "      <td>Ernesto_Uriarte@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Daniela Guitart</td>\n",
       "      <td>Daniela_Guitart@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Timoteo Trillo</td>\n",
       "      <td>Timoteo_Trillo@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Ricarda Tovar</td>\n",
       "      <td>Ricarda_Tovar@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Alejandra Vilaplana</td>\n",
       "      <td>Alejandra_Vilaplana@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Daniel Rosselló</td>\n",
       "      <td>Daniel_Rosselló@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Rita Olivares</td>\n",
       "      <td>Rita_Olivares@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Cleto Montes</td>\n",
       "      <td>Cleto_Montes@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Marino Castilla</td>\n",
       "      <td>Marino_Castilla@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Estefanía Valcárcel</td>\n",
       "      <td>Estefanía_Valcárcel@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Noemí Vilanova</td>\n",
       "      <td>Noemí_Vilanova@gmail.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         nombre                           email  promocion_id\n",
       "alumno_id                                                                    \n",
       "1                  Jafet Casals          Jafet_Casals@gmail.com             1\n",
       "2              Jorge Manzanares      Jorge_Manzanares@gmail.com             1\n",
       "3                 Onofre Adadia         Onofre_Adadia@gmail.com             1\n",
       "4                  Merche Prada          Merche_Prada@gmail.com             1\n",
       "5                  Pilar Abella          Pilar_Abella@gmail.com             1\n",
       "6                  Leoncio Tena          Leoncio_Tena@gmail.com             1\n",
       "7               Odalys Torrijos       Odalys_Torrijos@gmail.com             1\n",
       "8              Eduardo Caparrós      Eduardo_Caparrós@gmail.com             1\n",
       "9            Ignacio Goicoechea    Ignacio_Goicoechea@gmail.com             1\n",
       "10            Clementina Santos     Clementina_Santos@gmail.com             1\n",
       "11                Daniela Falcó         Daniela_Falcó@gmail.com             1\n",
       "12                Abraham Vélez         Abraham_Vélez@gmail.com             1\n",
       "13         Maximiliano Menéndez  Maximiliano_Menéndez@gmail.com             1\n",
       "14                Anita Heredia         Anita_Heredia@gmail.com             1\n",
       "15                    Eli Casas             Eli_Casas@gmail.com             1\n",
       "16               Amor Larrañaga        Amor_Larrañaga@gmail.com             2\n",
       "17             Teodoro Alberola      Teodoro_Alberola@gmail.com             2\n",
       "18                  Cleto Plana           Cleto_Plana@gmail.com             2\n",
       "19             Aitana Sebastián      Aitana_Sebastián@gmail.com             2\n",
       "20             Dolores Valbuena      Dolores_Valbuena@gmail.com             2\n",
       "21                 Julie Ferrer          Julie_Ferrer@gmail.com             2\n",
       "22               Mireia Cabañas        Mireia_Cabañas@gmail.com             2\n",
       "23                Flavia Amador         Flavia_Amador@gmail.com             2\n",
       "24                Albino Macias         Albino_Macias@gmail.com             2\n",
       "25                Ester Sánchez         Ester_Sánchez@gmail.com             2\n",
       "26           Luis Miguel Galvez    Luis_Miguel_Galvez@gmail.com             2\n",
       "27               Loida Arellano        Loida_Arellano@gmail.com             2\n",
       "28               Heraclio Duque        Heraclio_Duque@gmail.com             2\n",
       "29            Herberto Figueras     Herberto_Figueras@gmail.com             2\n",
       "30            Guillermo Borrego     Guillermo_Borrego@gmail.com             3\n",
       "31               Sergio Aguirre        Sergio_Aguirre@gmail.com             3\n",
       "32              Carlito Carrión       Carlito_Carrión@gmail.com             3\n",
       "33              Haydée Figueroa       Haydée_Figueroa@gmail.com             3\n",
       "34                Chita Mancebo         Chita_Mancebo@gmail.com             3\n",
       "35             Joaquina Asensio      Joaquina_Asensio@gmail.com             3\n",
       "36             Cristian Sarabia      Cristian_Sarabia@gmail.com             3\n",
       "37                Isabel Ibáñez         Isabel_Ibáñez@gmail.com             3\n",
       "38              Desiderio Jordá       Desiderio_Jordá@gmail.com             3\n",
       "39              Rosalina Llanos       Rosalina_Llanos@gmail.com             3\n",
       "40                Teresa Laguna         Teresa_Laguna@gmail.com             4\n",
       "41             Estrella Murillo      Estrella_Murillo@gmail.com             4\n",
       "42              Ernesto Uriarte       Ernesto_Uriarte@gmail.com             4\n",
       "43              Daniela Guitart       Daniela_Guitart@gmail.com             4\n",
       "44               Timoteo Trillo        Timoteo_Trillo@gmail.com             4\n",
       "45                Ricarda Tovar         Ricarda_Tovar@gmail.com             4\n",
       "46          Alejandra Vilaplana   Alejandra_Vilaplana@gmail.com             4\n",
       "47              Daniel Rosselló       Daniel_Rosselló@gmail.com             4\n",
       "48                Rita Olivares         Rita_Olivares@gmail.com             4\n",
       "49                 Cleto Montes          Cleto_Montes@gmail.com             4\n",
       "50              Marino Castilla       Marino_Castilla@gmail.com             4\n",
       "51          Estefanía Valcárcel   Estefanía_Valcárcel@gmail.com             4\n",
       "52               Noemí Vilanova        Noemí_Vilanova@gmail.com             4"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creación de dataframe de estudiantes:\n",
    "datos_estudiantes = {'nombre': [], 'email': [], 'promocion_id': []}\n",
    "# DS1:\n",
    "prom = 1\n",
    "for i in [DS1, FS1, DS2, FS2]:\n",
    "    [datos_estudiantes['nombre'].append(i['Nombre'][j]) for j in range(len(i['Nombre']))]\n",
    "    [datos_estudiantes['email'].append(i['Email'][j]) for j in range(len(i['Email']))]\n",
    "    [datos_estudiantes['promocion_id'].append(prom) for j in range(len(i['Email']))]\n",
    "    prom += 1\n",
    "\n",
    "\n",
    "estudiantes = pd.DataFrame(datos_estudiantes, index=range(1, len(datos_estudiantes['nombre'])+1))\n",
    "estudiantes.index.name = 'alumno_id'\n",
    "estudiantes\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>proyecto_id</th>\n",
       "      <th>alumno_id</th>\n",
       "      <th>resultado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>evaluacion_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Apto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>No Apto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Apto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Apto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Apto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>6</td>\n",
       "      <td>52</td>\n",
       "      <td>Apto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>7</td>\n",
       "      <td>52</td>\n",
       "      <td>No Apto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>8</td>\n",
       "      <td>52</td>\n",
       "      <td>No Apto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>9</td>\n",
       "      <td>52</td>\n",
       "      <td>Apto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260</th>\n",
       "      <td>10</td>\n",
       "      <td>52</td>\n",
       "      <td>Apto</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>260 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               proyecto_id  alumno_id resultado\n",
       "evaluacion_id                                  \n",
       "1                        1          1      Apto\n",
       "2                        2          1   No Apto\n",
       "3                        3          1      Apto\n",
       "4                        4          1      Apto\n",
       "5                        5          1      Apto\n",
       "...                    ...        ...       ...\n",
       "256                      6         52      Apto\n",
       "257                      7         52   No Apto\n",
       "258                      8         52   No Apto\n",
       "259                      9         52      Apto\n",
       "260                     10         52      Apto\n",
       "\n",
       "[260 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creación de dataframe evaluación:\n",
    "datos_evaluacion = {'proyecto_id': [], 'alumno_id': [], 'resultado': []}\n",
    "# DS1:\n",
    "al_id = 1\n",
    "for i in [DS1, FS1, DS2, FS2]:\n",
    "    for j in i.values:\n",
    "        for k in range(1, 6):\n",
    "            if 'Proyecto_FrontEnd' in i.columns:\n",
    "                datos_evaluacion['proyecto_id'].append(k+5)\n",
    "            else:\n",
    "                datos_evaluacion['proyecto_id'].append(k)\n",
    "            datos_evaluacion['alumno_id'].append(al_id)\n",
    "            datos_evaluacion['resultado'].append(j[4+k])\n",
    "        al_id += 1\n",
    "\n",
    "evaluacion = pd.DataFrame(datos_evaluacion, index=range(1, len(datos_evaluacion['proyecto_id'])+1))\n",
    "evaluacion.index.name = 'evaluacion_id'\n",
    "evaluacion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nombre</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>proyecto_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Proyecto_HLF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Proyecto_EDA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Proyecto_BBDD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Proyecto_ML</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Proyecto_Deployment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Proyecto_WebDev</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Proyecto_FrontEnd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Proyecto_Backend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Proyecto_React</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Proyecto_FullSatck</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          nombre\n",
       "proyecto_id                     \n",
       "1                   Proyecto_HLF\n",
       "2                   Proyecto_EDA\n",
       "3                  Proyecto_BBDD\n",
       "4                    Proyecto_ML\n",
       "5            Proyecto_Deployment\n",
       "6                Proyecto_WebDev\n",
       "7              Proyecto_FrontEnd\n",
       "8               Proyecto_Backend\n",
       "9                 Proyecto_React\n",
       "10            Proyecto_FullSatck"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creación de dataframe proyectos:\n",
    "datos_proyectos = {'nombre':[]}\n",
    "[datos_proyectos['nombre'].append(i) for i in DS1.columns[5:]]\n",
    "[datos_proyectos['nombre'].append(i) for i in FS1.columns[5:]]\n",
    "\n",
    "proyectos = pd.DataFrame(datos_proyectos, index=range(1, len(datos_proyectos['nombre'])+1))\n",
    "proyectos.index.name = 'proyecto_id'\n",
    "proyectos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Vamos a generar la estructura de la base de datos en función del esquema\n",
    "![Imagen](img/modelo_relacional.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "conexion = psycopg2.connect(database=\"escuela\", \n",
    "                            user='postgres', \n",
    "                            password='123456', \n",
    "                            host='localhost', \n",
    "                            port= '5432'\n",
    ")\n",
    "#Creating a cursor object using the cursor() method\n",
    "c = conexion.cursor()\n",
    "\n",
    "\n",
    "# Creación de tablas\n",
    "c.execute('''\n",
    "            CREATE TABLE IF NOT EXISTS campus (\n",
    "            campus_id integer PRIMARY KEY,\n",
    "            nombre_campus TEXT\n",
    "            );\n",
    "          ''')\n",
    "\n",
    "c.execute('''\n",
    "            CREATE TABLE IF NOT EXISTS promocion (\n",
    "            promocion_id INTEGER PRIMARY KEY,\n",
    "            nombre_promocion TEXT,\n",
    "            fecha_comienzo TEXT,\n",
    "            vertical TEXT,\n",
    "            campus_id INT NOT NULL,\n",
    "            FOREIGN KEY (campus_id) REFERENCES campus(campus_id)\n",
    "            )\n",
    "          ''')\n",
    "\n",
    "c.execute('''\n",
    "            CREATE TABLE IF NOT EXISTS estudiantes (\n",
    "            alumno_id INTEGER PRIMARY KEY,\n",
    "            nombre TEXT,\n",
    "            email TEXT,\n",
    "            promocion_id INT NOT NULL,\n",
    "            FOREIGN KEY (promocion_id) REFERENCES promocion(promocion_id)\n",
    "            )\n",
    "          ''')\n",
    "\n",
    "c.execute('''\n",
    "            CREATE TABLE IF NOT EXISTS proyectos (\n",
    "            proyecto_id integer PRIMARY KEY,\n",
    "            nombre TEXT\n",
    "            );\n",
    "          ''')\n",
    "\n",
    "c.execute('''\n",
    "            CREATE TABLE IF NOT EXISTS evaluacion (\n",
    "            evaluacion_id INTEGER PRIMARY KEY,\n",
    "            proyecto_id INTEGER,\n",
    "            alumno_id INTEGER NOT NULL,\n",
    "            resultado TEXT,\n",
    "            FOREIGN KEY (proyecto_id) REFERENCES proyectos(proyecto_id),\n",
    "            FOREIGN KEY (alumno_id) REFERENCES estudiantes(alumno_id)\n",
    "            )\n",
    "          ''')\n",
    "\n",
    "c.execute('''\n",
    "            CREATE TABLE IF NOT EXISTS claustro (\n",
    "            profesor_id INTEGER PRIMARY KEY,\n",
    "            nombre TEXT,\n",
    "            rol TEXT,\n",
    "            promocion_id INTEGER,\n",
    "            FOREIGN KEY (promocion_id) REFERENCES promocion(promocion_id)\n",
    "            )\n",
    "          ''')\n",
    "\n",
    "\n",
    "conexion.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "conexion.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Generación de base de datos PostgreSQL en local:\n",
    "\n",
    "conexion = create_engine('postgresql://postgres:123456@localhost:5432/escuela')\n",
    "\n",
    "campus.to_sql('campus', con=conexion, if_exists='append')\n",
    "proyectos.to_sql('proyectos', con=conexion, if_exists='append')\n",
    "promocion.to_sql('promocion', con=conexion, if_exists='append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "260"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "claustro.to_sql('claustro', con=conexion, if_exists='append')\n",
    "estudiantes.to_sql('estudiantes', con=conexion, if_exists='append')\n",
    "evaluacion.to_sql('evaluacion', con=conexion, if_exists='append')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}