Ayudantías/C4b/Solución C4b.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8ba5d236",
   "metadata": {},
   "source": [
    "# <u><h><center> Pauta C4b</center></h></u>\n",
    "## IIC2115 - Programación Como Herramienta para la Ingeniería\n",
    "### Ayudante: Felipe Fuentes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f46d111",
   "metadata": {},
   "source": [
    "#### La primera parte es para generar la base de datos, utilizada en la actividad C4a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0cd592a0",
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "table Equipos already exists",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "Input \u001b[1;32mIn [1]\u001b[0m, in \u001b[0;36m<cell line: 11>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      7\u001b[0m cursor \u001b[38;5;241m=\u001b[39m connection\u001b[38;5;241m.\u001b[39mcursor()                    \n\u001b[0;32m      9\u001b[0m \u001b[38;5;66;03m# Creamos las tablas:\u001b[39;00m\n\u001b[1;32m---> 11\u001b[0m \u001b[43mcursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mCREATE TABLE Equipos(eid INTEGER PRIMARY KEY, nombre TEXT UNIQUE)\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[0;32m     12\u001b[0m cursor\u001b[38;5;241m.\u001b[39mexecute(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCREATE TABLE Temporadas(tid INTEGER PRIMARY KEY, ano INTEGER UNIQUE)\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m     13\u001b[0m cursor\u001b[38;5;241m.\u001b[39mexecute(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCREATE TABLE Ligas(lid INTEGER PRIMARY KEY, nombre TEXT UNIQUE)\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[1;31mOperationalError\u001b[0m: table Equipos already exists"
     ]
    }
   ],
   "source": [
    "import sqlite3  # importamos el módulo de sqlite\n",
    "\n",
    "connection = sqlite3.connect('futbol.db') \n",
    "            \n",
    "# Generamos un cursor al contenido de la base de datos, lo que nos permite \n",
    "# acceder al contenido de las tablas y a realizar consultas sobre ellas.   \n",
    "cursor = connection.cursor()                    \n",
    "\n",
    "# Creamos las tablas:\n",
    "\n",
    "cursor.execute(\"CREATE TABLE Equipos(eid INTEGER PRIMARY KEY, nombre TEXT UNIQUE)\")\n",
    "cursor.execute(\"CREATE TABLE Temporadas(tid INTEGER PRIMARY KEY, ano INTEGER UNIQUE)\")\n",
    "cursor.execute(\"CREATE TABLE Ligas(lid INTEGER PRIMARY KEY, nombre TEXT UNIQUE)\")\n",
    "\n",
    "cursor.execute(\"\"\"CREATE TABLE Partidos(pid INTEGER PRIMARY KEY, liga INTEGER, \n",
    "    temporada INTEGER, fecha DATE, equipo INTEGER, localidad TEXT, resultado TEXT,\n",
    "    puntos INTEGER, goles INTEGER, goles_perdidos INTEGER, pases_profundos INTEGER, \n",
    "    pases_profundos_permitidos INTEGER, ppda REAL, oppda REAL, \n",
    "    FOREIGN KEY (liga) REFERENCES Ligas, FOREIGN KEY (temporada) REFERENCES Temporadas, \n",
    "    FOREIGN KEY (equipo) REFERENCES Equipos)\"\"\")\n",
    "\n",
    "connection.commit()\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "adb48bdf",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect('futbol.db')  \n",
    "cursor = connection.cursor()                    \n",
    "\n",
    "# Con esto podemos eliminar las tablas que creamos:\n",
    "\n",
    "cursor.execute(\"DROP TABLE IF EXISTS  Equipos\")\n",
    "cursor.execute(\"DROP TABLE IF EXISTS Temporadas\")\n",
    "cursor.execute(\"DROP TABLE IF EXISTS  Ligas\")\n",
    "cursor.execute(\"DROP TABLE IF EXISTS  Partidos\")\n",
    "\n",
    "connection.commit() \n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7c142095",
   "metadata": {},
   "outputs": [],
   "source": [
    "#importamos los datos\n",
    "\n",
    "import json\n",
    "with open('football_results.json', encoding = 'utf8') as movies_file:\n",
    "    partidos = json.load(movies_file)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "81419d98",
   "metadata": {},
   "outputs": [],
   "source": [
    "#insertamos los datos\n",
    "\n",
    "connection = sqlite3.connect('futbol.db')\n",
    "cursor = connection.cursor()\n",
    "\n",
    "eid = 1\n",
    "tid = 1\n",
    "lid = 1\n",
    "pid = 1\n",
    "dicc_equipos= {}\n",
    "dicc_season= {}\n",
    "dicc_ligas= {}\n",
    "\n",
    "\n",
    "#el orden de inserción es importante, fijarse en crear primero las tablas a las que se les \n",
    "#va a hacer referencia por medio de foreign keys\n",
    "for partido in partidos:\n",
    "    \n",
    "    if not partido[\"team\"] in dicc_equipos:\n",
    "        cursor.execute(\"INSERT INTO Equipos(eid, nombre) VALUES (?,?)\", (eid, partido[\"team\"]))\n",
    "        dicc_equipos[partido[\"team\"]]= eid\n",
    "        eid+=1\n",
    "        \n",
    "    if not partido[\"league\"] in dicc_ligas:\n",
    "        cursor.execute(\"INSERT INTO Ligas(lid, nombre) VALUES (?,?)\", (lid, partido[\"league\"]))\n",
    "        dicc_ligas[partido[\"league\"]]= lid\n",
    "        lid+=1\n",
    "            \n",
    "    if not partido[\"season\"] in dicc_season:\n",
    "        cursor.execute(\"INSERT INTO Temporadas(tid, ano) VALUES (?,?)\", (tid, partido[\"season\"]))\n",
    "        dicc_season[partido[\"season\"]]= tid\n",
    "        tid+=1\n",
    "        \n",
    "    cursor.execute(\"INSERT INTO Partidos(pid, liga, temporada, fecha, equipo, localidad, resultado, puntos, goles, goles_perdidos, pases_profundos, pases_profundos_permitidos,ppda, oppda) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)\", (pid, dicc_ligas[partido[\"league\"]], dicc_season[partido[\"season\"]], partido[\"date\"], dicc_equipos[partido[\"team\"]], partido[\"h_a\"], partido[\"result\"], partido[\"pts\"],partido[\"goals_scored\"], partido[\"goals_missed\"], partido[\"deep_passes\"], partido[\"deep_passes_allowed\"], partido[\"ppda\"], partido[\"oppda\"]))\n",
    "\n",
    "    pid += 1\n",
    "\n",
    "    \n",
    "    \n",
    "\n",
    "\n",
    "connection.commit()\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3a29851",
   "metadata": {},
   "source": [
    "## C4b"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e62c720c",
   "metadata": {},
   "source": [
    "### Consultas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cca6f21f",
   "metadata": {},
   "source": [
    "#### Consulta 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "f81bb4ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tabla:\n",
      " Liga | Numero de equipos distintos\n",
      "('Bundesliga', 24)\n",
      "('EPL', 30)\n",
      "('La_liga', 30)\n",
      "('Ligue_1', 29)\n",
      "('RFPL', 25)\n",
      "('Serie_A', 30)\n"
     ]
    }
   ],
   "source": [
    "connection = sqlite3.connect('futbol.db')\n",
    "cursor = connection.cursor()\n",
    "\n",
    "cursor.execute(\"\"\"SELECT Ligas.nombre, COUNT(DISTINCT Partidos.equipo) FROM Ligas, Partidos\n",
    "                    WHERE Ligas.lid=Partidos.liga\n",
    "                    GROUP BY Partidos.liga\"\"\")\n",
    "print(\"Tabla:\\n Liga | Numero de equipos distintos\")\n",
    "for row in cursor.fetchall():\n",
    "    print(row)\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12071e8d",
   "metadata": {},
   "source": [
    "#### Consulta 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "5724101c",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tabla:\n",
      " Equipo | Diferencias de goles | Goles | Goles Perdidos\n",
      "('Barcelona', 423, 613, 190)\n",
      "('Paris Saint Germain', 386, 556, 170)\n",
      "('Bayern Munich', 380, 529, 149)\n",
      "('Manchester City', 334, 537, 203)\n",
      "('Real Madrid', 333, 561, 228)\n",
      "('Juventus', 288, 456, 168)\n",
      "('Liverpool', 218, 451, 233)\n",
      "('Napoli', 216, 456, 240)\n",
      "('Atletico Madrid', 212, 364, 152)\n",
      "('Zenit St. Petersburg', 201, 337, 136)\n",
      "('Roma', 194, 431, 237)\n",
      "('Borussia Dortmund', 182, 430, 248)\n",
      "('Tottenham', 179, 415, 236)\n",
      "('Lyon', 177, 418, 241)\n",
      "('Chelsea', 162, 411, 249)\n",
      "('CSKA Moscow', 161, 303, 142)\n",
      "('Inter', 151, 385, 234)\n",
      "('Arsenal', 150, 416, 266)\n",
      "('Manchester United', 145, 364, 219)\n",
      "('Lazio', 143, 421, 278)\n",
      "('FC Krasnodar', 139, 296, 157)\n",
      "('Monaco', 129, 382, 253)\n",
      "('Marseille', 109, 362, 253)\n",
      "('RasenBallsport Leipzig', 109, 267, 158)\n",
      "('Atalanta', 95, 373, 278)\n",
      "('Bayer Leverkusen', 87, 359, 272)\n",
      "('Lokomotiv Moscow', 77, 240, 163)\n",
      "('AC Milan', 74, 336, 262)\n",
      "('Borussia M.Gladbach', 74, 333, 259)\n",
      "('Sevilla', 73, 356, 283)\n",
      "('Valencia', 63, 334, 271)\n",
      "('Villarreal', 61, 317, 256)\n",
      "('Spartak Moscow', 54, 258, 204)\n",
      "('Fiorentina', 50, 333, 283)\n",
      "('Hoffenheim', 42, 341, 299)\n",
      "('Nice', 34, 289, 255)\n",
      "('Leicester', 33, 336, 303)\n",
      "('Saint-Etienne', 24, 269, 245)\n",
      "('Lille', 23, 266, 243)\n",
      "('Wolfsburg', 16, 299, 283)\n",
      "('Athletic Club', 15, 276, 261)\n",
      "('Wolverhampton Wanderers', 12, 98, 86)\n",
      "('FC Rostov', 11, 201, 190)\n",
      "('Real Sociedad', 10, 315, 305)\n",
      "('Bordeaux', 9, 277, 268)\n",
      "('Rennes', 8, 266, 258)\n",
      "('Torino', 6, 322, 316)\n",
      "('Montpellier', 6, 267, 261)\n",
      "('Everton', 2, 311, 309)\n",
      "('PFC Sochi', 1, 40, 39)\n",
      "('Sheffield United', 0, 39, 39)\n",
      "('Brest', -3, 34, 37)\n",
      "('FC Tambov', -4, 37, 41)\n",
      "('Dinamo Moscow', -9, 162, 171)\n",
      "('Rubin Kazan', -13, 176, 189)\n",
      "('Strasbourg', -13, 134, 147)\n",
      "('Schalke 04', -13, 266, 279)\n",
      "('Kuban Krasnodar', -14, 66, 80)\n",
      "('Nimes', -16, 86, 102)\n",
      "('Southampton', -16, 287, 303)\n",
      "('Torpedo Moscow', -17, 28, 45)\n",
      "('FK Akhmat', -17, 188, 205)\n",
      "('Union Berlin', -17, 41, 58)\n",
      "('Carpi', -20, 37, 57)\n",
      "('Eintracht Frankfurt', -20, 290, 310)\n",
      "('GFC Ajaccio', -21, 37, 58)\n",
      "('Evian Thonon Gaillard', -21, 41, 62)\n",
      "('Parma Calcio 1913', -22, 93, 115)\n",
      "('SD Huesca', -22, 43, 65)\n",
      "('Nancy', -23, 29, 52)\n",
      "('Sampdoria', -25, 309, 334)\n",
      "('Mallorca', -25, 40, 65)\n",
      "('Girona', -25, 87, 112)\n",
      "('Middlesbrough', -26, 27, 53)\n",
      "('Angers', -27, 194, 221)\n",
      "('Elche', -27, 35, 62)\n",
      "('Lens', -29, 32, 61)\n",
      "('Almeria', -29, 35, 64)\n",
      "('Sassuolo', -30, 309, 339)\n",
      "('FC Orenburg', -30, 92, 122)\n",
      "('Reims', -30, 156, 186)\n",
      "('Real Valladolid', -30, 64, 94)\n",
      "('Ingolstadt', -30, 69, 99)\n",
      "('FC Yenisey Krasnoyarsk', -31, 24, 55)\n",
      "('Tosno', -31, 23, 54)\n",
      "('Queens Park Rangers', -31, 42, 73)\n",
      "('Lecce', -32, 49, 81)\n",
      "('Getafe', -33, 203, 236)\n",
      "('Cardiff', -35, 34, 69)\n",
      "('Cesena', -37, 36, 73)\n",
      "('SKA-Khabarovsk', -39, 16, 55)\n",
      "('Mordovya', -41, 52, 93)\n",
      "('Parma', -42, 33, 75)\n",
      "('Amkar', -42, 92, 134)\n",
      "('West Ham', -42, 305, 347)\n",
      "('Nuernberg', -42, 26, 68)\n",
      "('Hertha Berlin', -42, 261, 303)\n",
      "('FC Ufa', -43, 153, 196)\n",
      "('Lorient', -43, 135, 178)\n",
      "('SC Bastia', -43, 102, 145)\n",
      "('Celta Vigo', -43, 300, 343)\n",
      "('Brescia', -44, 35, 79)\n",
      "('Nantes', -44, 214, 258)\n",
      "('Amiens', -45, 99, 144)\n",
      "('Cordoba', -46, 22, 68)\n",
      "('Malaga', -46, 153, 199)\n",
      "('Tom Tomsk', -47, 17, 64)\n",
      "('Fulham', -47, 34, 81)\n",
      "('Fortuna Duesseldorf', -47, 85, 132)\n",
      "('Pescara', -48, 35, 83)\n",
      "('Krylya Sovetov Samara', -48, 108, 156)\n",
      "('Alaves', -48, 154, 202)\n",
      "('Crotone', -50, 74, 124)\n",
      "('Darmstadt', -50, 66, 116)\n",
      "('Benevento', -51, 33, 84)\n",
      "('Arsenal Tula', -51, 150, 201)\n",
      "('Sporting Gijon', -52, 82, 134)\n",
      "('FC Cologne', -54, 209, 263)\n",
      "('Werder Bremen', -54, 298, 352)\n",
      "('Eibar', -55, 268, 323)\n",
      "('Mainz 05', -55, 263, 318)\n",
      "('Stoke', -59, 165, 224)\n",
      "('Brighton', -60, 108, 168)\n",
      "('Crystal Palace', -60, 263, 323)\n",
      "('West Bromwich Albion', -60, 146, 206)\n",
      "('Hull', -61, 70, 131)\n",
      "('Osasuna', -62, 86, 148)\n",
      "('Real Betis', -62, 227, 289)\n",
      "('Bologna', -63, 212, 275)\n",
      "('Leganes', -63, 137, 200)\n",
      "('Empoli', -66, 166, 232)\n",
      "('Swansea', -66, 161, 227)\n",
      "('Freiburg', -67, 204, 271)\n",
      "('Augsburg', -67, 259, 326)\n",
      "('Dijon', -69, 159, 228)\n",
      "('Ural', -70, 194, 264)\n",
      "('Genoa', -71, 261, 332)\n",
      "('Paderborn', -71, 68, 139)\n",
      "('Rayo Vallecano', -72, 139, 211)\n",
      "('Palermo', -73, 124, 197)\n",
      "('Burnley', -74, 191, 265)\n",
      "('Sunderland', -76, 108, 184)\n",
      "('Norwich', -77, 65, 142)\n",
      "('Espanyol', -78, 247, 325)\n",
      "('Newcastle United', -78, 203, 281)\n",
      "('Las Palmas', -79, 122, 201)\n",
      "('SPAL 2013', -80, 109, 189)\n",
      "('Frosinone', -81, 64, 145)\n",
      "('Guingamp', -81, 210, 291)\n",
      "('VfB Stuttgart', -81, 160, 241)\n",
      "('Troyes', -82, 60, 142)\n",
      "('Hamburger SV', -83, 127, 210)\n",
      "('Huddersfield', -84, 50, 134)\n",
      "('Bournemouth', -89, 241, 330)\n",
      "('Cagliari', -91, 224, 315)\n",
      "('Udinese', -91, 248, 339)\n",
      "('Caen', -93, 185, 278)\n",
      "('Levante', -93, 221, 314)\n",
      "('Watford', -93, 212, 305)\n",
      "('Verona', -94, 160, 254)\n",
      "('Anzhi Makhachkala', -97, 96, 193)\n",
      "('Deportivo La Coruna', -97, 161, 258)\n",
      "('Hannover 96', -97, 146, 243)\n",
      "('Aston Villa', -101, 99, 200)\n",
      "('Granada', -103, 157, 260)\n",
      "('Chievo', -106, 175, 281)\n",
      "('Toulouse', -109, 220, 329)\n",
      "('Metz', -113, 131, 244)\n"
     ]
    }
   ],
   "source": [
    "connection = sqlite3.connect('futbol.db')\n",
    "cursor = connection.cursor()\n",
    "\n",
    "\n",
    "cursor.execute(\"\"\"SELECT Equipos.nombre, Sum(Partidos.goles-Partidos.goles_perdidos) as goles, Sum(Partidos.goles), Sum(Partidos.goles_perdidos)\n",
    "                    FROM Equipos, Partidos\n",
    "                    WHERE Equipos.eid=Partidos.equipo\n",
    "                    GROUP BY Partidos.equipo\n",
    "                    Order By goles DESC\"\"\")\n",
    "print(\"Tabla:\\n Equipo | Diferencias de goles | Goles | Goles Perdidos\")\n",
    "for row in cursor.fetchall():\n",
    "    print(row)\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76e39b95",
   "metadata": {},
   "source": [
    "#### Consulta 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "866974d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tabla:\n",
      " Liga | Numero de equipos distintos\n",
      "('EPL', 82)\n",
      "('Ligue_1', 78)\n",
      "('Serie_A', 76)\n",
      "('La_liga', 74)\n",
      "('Bundesliga', 63)\n",
      "('RFPL', 55)\n"
     ]
    }
   ],
   "source": [
    "connection = sqlite3.connect('futbol.db')\n",
    "cursor = connection.cursor()\n",
    "\n",
    "\n",
    "cursor.execute(\"\"\"SELECT Ligas.nombre, Max(Points_league.diff) \n",
    "                    FROM Ligas,\n",
    "                    \n",
    "                        (SELECT Ligas.nombre as nombre, Temporadas.ano, Max(Puntos.points)-Min(Puntos.points) as diff\n",
    "                        FROM Ligas, Partidos, Temporadas, \n",
    "\n",
    "                            (SELECT Partidos.temporada as temporada, Partidos.liga as liga, Partidos.equipo as equipo, Sum(Partidos.puntos) as points\n",
    "                            FROM Partidos\n",
    "                            GROUP BY Partidos.temporada, Partidos.liga, Partidos.equipo) as Puntos\n",
    "                                            \n",
    "                        WHERE Ligas.lid=Partidos.liga AND Temporadas.tid=Partidos.temporada\n",
    "                        AND Puntos.liga=Partidos.liga AND Partidos.temporada=Puntos.temporada\n",
    "                        AND Puntos.equipo= Partidos.equipo\n",
    "                        GROUP BY Partidos.temporada, Partidos.liga) as Points_league\n",
    "                    WHERE Ligas.nombre= Points_league.nombre\n",
    "                    GROUP BY Points_league.nombre\n",
    "                    ORDER BY Points_league.diff DESC\n",
    "                    \"\"\")\n",
    "print(\"Tabla:\\n Liga | Numero de equipos distintos\")\n",
    "for row in cursor.fetchall():\n",
    "    print(row)\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ef050fb",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}