Material de clases/Capítulo 3/Parte b/01 - Consultas sobre bases de datos relacionales.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p>\n",
    "<font size='5' face='Georgia, Arial'>IIC2115 - Programación como herramienta para la ingeniería</font><br>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Consultas sobre bases de datos relacionales\n",
    "\n",
    "Tal como se indicó anteriormente, en esta parte de la materia nos centraremos en la consulta y manipulación de datos con SQL, a través de su MDL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lenguaje de manipulación de datos (DML)\n",
    "Este subconjunto de SQL permite declarar consultas que se ejecutarán sobre una o más tablas.\n",
    "\n",
    "### Estructura básica de una consulta\n",
    "La estructura básica de una consulta en SQL es la siguiente:\n",
    "\n",
    "> SELECT [DISTINCT] select-list <br>\n",
    "> FROM from-list <br>\n",
    "> WHERE qualification <br>\n",
    "\n",
    "Cada consulta debe tener una cláusula **SELECT**, que especifica las columnas que deben conservarse en el resultado, y una cláusula **FROM**, que especifica un producto cruzado de tablas. La cláusula opcional **WHERE** especifica las condiciones de selección en las tablas mencionadas en la cláusula FROM. Consideremos a continuación un ejemplo sencillo, encontrar los nombres y edades de todos los estudiantes:\n",
    "\n",
    "> SELECT DISTINCT E.nombre, E.edad <br>\n",
    "> FROM Estudiantes E\n",
    "\n",
    "La respuesta a la consulta anterior es un conjunto de filas, cada una de las cuales es un par (nombre, edad). Si dos o más estudiantes tienen el mismo par (nombre, edad) la respuesta contendrá sólo un par de estos. Si omitimos el modificador **DISTINCT**, obtendríamos una copia de la fila, para cada estudiante con el mismo nombre y edad. Esto se conoce como un *multiconjunto*, que a diferencia de un conjunto tradicional, puede contener valores repetidos. \n",
    "\n",
    "La siguiente consulta agrega una restricción simple a la consulta anterior:\n",
    "\n",
    "> SELECT * <br>\n",
    "> FROM Estudiantes E <br>\n",
    "> WHERE E.promedio > 5.0\n",
    "\n",
    "Existen dos diferencia principales entre esta consulta y la anterior. Primero, dado que queremos obtener todos los datos de un alumno, no es necesario especificar cada columna, ya que basta con ingresar un \\*. Segundo, dentro de los registros de la tabla Estudiantes, sólo retornaremos aquellos que tienen un promedio superior a 5.0.\n",
    "\n",
    "Consideremos ahora la sintaxis de una consulta SQL básica con más detalle:\n",
    "\n",
    "* La from-list en la cláusula FROM es una lista de nombres de tabla. Un nombre de tabla puede ser seguido por una variable de rango; una variable de rango es particularmente útil cuando el mismo nombre de la tabla aparece más de una vez en la from-list.\n",
    "* La select-list es una lista de nombres de columnas de tablas nombradas en la from-list.\n",
    "* Los argumentos en la cláusula WHERE son una combinación booleana (es decir, una expresión utilizando los conectivos lógicas AND, OR y NOT) de condiciones con forma *expresión op expresión*, donde *op* es uno de los siguientes operadores (<, <=, =, <>,> =,>). Una expresión es un nombre de columna, un constante o una expresión aritmética o de string.\n",
    "\n",
    "### Consultas entre tablas (JOIN)\n",
    "A continuación veremos consultas de ejemplo, donde es necesario comparar o combinar elementos de distintas tablas, lo que se conoce como un **join**. El primer ejemplo present una consulta que encuentra a los alumnos de postgrado de 24 o más años.\n",
    "\n",
    "> SELECT E.nombre  <br>\n",
    "> FROM Estudiantes E, Estudiantes_Postgrado P  <br>\n",
    "> WHERE E.id = P.id AND E.edad >= 24\n",
    "\n",
    "Como se aprecia, sólo se deben comparar las ocurrencias de **id**, ya que esta columna aparece en ambas tablas. Por el contrario, **edad**, al estar sólo en la tabla **Estudiantes**, no necesita comparación.\n",
    "\n",
    "Una situación más compleja se puede ver en la siguiente consulta, donde buscamos los cursos en que está inscrito cada estudiante:\n",
    "\n",
    "> SELECT E.nombre, C.nombre  <br>\n",
    "> FROM Estudiantes E, Cursos C, Inscrito I  <br>\n",
    "> WHERE E.id = I.id_estudiante AND C.id = I.id_curso\n",
    "\n",
    "Esta consulta contiene un join de tres tablas, ya que es necesario pasar por la tabla **Inscrito** para relacionar a los estudiates con sus cursos.\n",
    "\n",
    "La última consulta básica que revisaremos involucra el ordenamiento de los resultados de manera transparente para el usuario:\n",
    "\n",
    "> SELECT E.nombre  <br>\n",
    "> FROM Estudiantes E, Estudiantes_Postgrado P  <br>\n",
    "> WHERE E.id = P.id AND edad >= 24  <br>\n",
    "> ORDER BY edad ASC\n",
    "\n",
    "Como se puede ver, la consulta es una extensión de la primera consulta básica revisada, donde ahora los resultados se ordenarán de menor a mayor, en base a la edad de los estudiantes.\n",
    "\n",
    "### Matching de patrones en strings\n",
    "El siguiente tipo de consulta que revisaremos son bastante comunes, ya que involucran la búsqueda sobre strings, con patrones que no necesariamente calzan completamente con la información almacenada. Por ejemplo, si actualmente quisieramos buscar todos los alumnos cuyos nombre comiencen con \"MA\", sería necesario realizar una cantidad enorme de consultas, considerando todas las posibles combinaciones. Afortunamente, SQL entrega un mecanismo simple para solucionar esta situación:\n",
    "\n",
    "> SELECT \\*  <br>\n",
    "> FROM Estudiantes E  <br>\n",
    "> WHERE E.nombre LIKE 'MA%'\n",
    "\n",
    "Existen multiples patrones de strings que pueden verificarse, no solo patrones de inicio. Más información sobre esto puede encontrarse [acá](https://www.w3schools.com/sql/sql_like.asp).\n",
    "\n",
    "### Manipulación y comparación de conjuntos\n",
    "SQL proporciona también constructos de manipulación y comparación de conjuntos que extienden las consultas básicas presentadas anteriormente. Como la respuesta a una consulta es un conjunto de múltiples filas, es\n",
    "natural considerar el uso de operaciones tales como unión, intersección y diferencia. SQL admite estas operaciones bajo los nombres UNION, INTERSECT y EXCEPT (o MINUS). SQL también proporciona otras operaciones de conjunto: IN (para verificar si un elemento está en un conjunto dado), op ANY, op ALL (para comparar un valor con los elementos de un conjunto dado, usando el operador de comparación op), y EXISTS (para verificar si un conjunto es vacío). IN y EXISTS pueden ser prefijados por NOT, con la modificación obvia a su significado. Como ejemplos, considere al siguiente consulta, que busca todos los identificadores de los alumnos con promedio superior a 5.0 **o** que estén en el curso con identificador IIC2115:\n",
    "\n",
    "> SELECT E.id <br>\n",
    "> FROM Estudiantes E  <br>\n",
    "> WHERE E.promedio > 5.0 <br>\n",
    "> UNION <br>\n",
    "> SELECT I.id_estudiante <br>\n",
    "> FROM Inscritos I <br>\n",
    "> WHERE I.id_curso = 'IIC2115'\n",
    "\n",
    "Si quisieramos encontrar los alumnos que tienen promedio superior a 5.0 **y** están en IIC2115, basta con intercambiar UNION por INTERSECT. Más aun, si quisieramos encontrar todos los alumnos con promedio superior a 5.0 y que **no** están en IIC2115, bastan con cambiar UNION (o INTERSECT) por EXCEPT. \n",
    "\n",
    "Los operadores de comparación de conjuntos los analizaremos en el contexto del próximo tipo de consultas.\n",
    "\n",
    "\n",
    "### Consultas anidadas\n",
    "Una de las características más potentes de SQL son las consultas anidadas. Una consulta anidada es una consulta que tiene otra consulta incrustada dentro de ella; la consulta incrustada se llama subconsulta (_subquery_). La consulta incrustrada puede ser, por supuesto, una consulta anidada en sí mismoa; por lo tanto, son posibles consultas que tienen multiples niveles de anidación. La necesidad de este tipo de consultas nace cuando escribiendo una consulta se necesita expresar una condición, que debe calcularse en el momento haciendo referencia también a una tabla. La consulta utilizada para calcular esta tabla subsidiaria es una subconsulta y aparece como parte de la consulta principal. Una subconsulta típicamente aparece dentro de la cláusula WHERE.\n",
    "\n",
    "Para comenzar, consideremos la siguiente consulta, que retorna el nombre de todos los alumnos que **no** se encuentren inscritos en el curso IIC2115\n",
    "\n",
    "> SELECT E.nombre <br>\n",
    "> FROM Estudiantes E  <br>\n",
    "> WHERE E.id NOT IN (SELECT I.id_estudiante <br>\n",
    "> &emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;FROM Inscritos I <br>\n",
    "> &emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;WHERE I.id_curso = 'IIC2115')\n",
    "\n",
    "También es posible hacer que la subconsulta dependa de la consulta principal. Para verlo, consideremos la siguiente consulta, cuyo resultado es el mismo que el de la consulta anterior:\n",
    "\n",
    "> SELECT E.nombre <br>\n",
    "> FROM Estudiantes E  <br>\n",
    "> WHERE EXISTS (SELECT * <br>\n",
    "> &emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&nbsp;FROM Inscritos I <br>\n",
    "> &emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&nbsp;WHERE I.id_curso = 'IIC2115' AND I.id_estudiante = E.id)\n",
    "\n",
    "Esta consulta también ilustra el uso del símbolo $*$ en situaciones donde todo lo que queremos hacer es verificar que exista una cierta fila, y realmente no queremos recuperar ninguna columna de la fila. Este es uno de los pocos usos de $*$ en la cláusula SELECT que se considera un buen estilo de programación.\n",
    "\n",
    "La comparación de conjuntos sigue un esquema bastante similar. Por ejemplo, si buscamos los alumnos cuyo promedio es superior al de todos los alumnos de nombre José, podemos utilizar la siguiente consulta anidada:\n",
    "\n",
    "> SELECT E.nombre <br>\n",
    "> FROM Estudiantes E  <br>\n",
    "> WHERE E.promedio > ALL (SELECT E2.promedio <br>\n",
    "> &emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&nbsp;FROM Estudiantes E2 <br>\n",
    "> &emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&nbsp;WHERE E2.nombre = 'José')\n",
    "\n",
    "### Agregación\n",
    "Además de simplemente recuperar datos, a menudo queremos realizar algún cálculo o agregación de estos. A continuación, revisaremos una poderosa clase de constructos para calcular valores agregados como MIN y SUM. SQL soporta cinco operaciones agregadas, que pueden aplicarse en cualquier columna, digamos A, de una relación:\n",
    "\n",
    "* COUNT ([DISTINCT] A): La cantidad de valores (unicos) en la columna A.\n",
    "* SUM ([DISTINCT] A): La suma de todos los valores (unicos) en la columna A.\n",
    "* AVG ([DISTINCT] A): El promedio de todos los valores (unicos) en la columna A.\n",
    "* MAX (A): El máximo valor en la columna A.\n",
    "* MIN (A): El mínimo valor en la columna A.\n",
    "\n",
    "Veamos a continuación un ejemplo simple:\n",
    "\n",
    "> SELECT AVG (E.edad)  <br>\n",
    "> FROM Estudiantes E\n",
    "\n",
    "La consulta anterior permite calcular de manera eficiente el promedio de las edades de todos los alumnos registrados. Si deseamos filtrar un poco más los resultados, podemos realizar la siguiente consulta :\n",
    "\n",
    "> SELECT AVG (E.edad)  <br>\n",
    "> FROM Estudiantes E  <br>\n",
    "> WHERE E.promedio > 5.0\n",
    "\n",
    "Cabe destacar que todos los elementos vistos anteriormente para la cláusula WHERE, pueden ser usados en conjunto con las funciones de agregación.\n",
    "\n",
    "\n",
    "### Agrupación\n",
    "Hasta ahora, hemos aplicado operaciones agregadas a todas las filas de una relación. A menudo, queremos aplicar operaciones agregadas a ciertos grupos de filas en una relación, donde el número de grupos depende de la instancia de relación (es decir, no se conoce de antemano). Por ejemplo, si queremos calcular el promedio de edad de los estudiantes, agrupados por sus promedios, deberíamos realizar algo por el estilo:\n",
    "\n",
    "> SELECT AVG (E.edad)  <br>\n",
    "> FROM Estudiantes E  <br>\n",
    "> WHERE E.promedio = i\n",
    "\n",
    "con i = 1,.., 7 (asumiendo notas enteras, como se indica en el esquema de la relación). Claramente, escribir 7 veces la misma consulta es tedioso, pero más importante aún, no siempre se conoce a priori el número de categorías por los que se quiere agrupar.\n",
    "\n",
    "Para facilitar este tipo de problemas, SQL incluye una extensión a las consultas básicas en la forma de la cláusula **GROUP BY** (y el filtro opcional **HAVING**). Usando esta nueva funcionalidad, podemos escribir la consulta anterior de la siguiente manera:\n",
    "\n",
    "> SELECT AVG(E.edad) <br>\n",
    "> FROM Estudiantes E  <br>\n",
    "> GROUP BY E.promedio <br>\n",
    "> HAVING E.promedio >= 5\n",
    "\n",
    "### Valores nulos\n",
    "Hasta ahora, hemos asumido que los valores de columna en una fila son siempre conocidos. En la práctica, esto no es siempre cierto, ya que algunos de estos valores pueden ser desconocidos. Por ejemplo, cuando un alumo se inscribe en un curso, es natural que aún no tenga una nota asignada. Dado que la definición para la tabla Inscrito tiene una columna nota, ¿qué deberíamos insertar en esa columna? Lo que se necesita aquí es un valor especial que denote algo desconocido.\n",
    "\n",
    "SQL proporciona un valor de columna especial llamado **NULL** para usar en tales situaciones. Se utiliza este valor cuando el contenido de la columna sea desconocido o inaplicable. Para verificar la existencia de un valor **NULL**, SQL proporciona el operador de comparación **IS NULL**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Consultas en SQL a través de Python\n",
    "Para ejemplificar el uso de Python para realizar consultas, continuaremos con el ejemplo utilizado en la parte anterior. Para facilitar las cosas, repetiremos el código para crear y llenar la base de datos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "connection = sqlite3.connect('example.db')   \n",
    "cursor = connection.cursor()                    \n",
    "cursor.execute(\"CREATE TABLE countries(name TEXT, continent TEXT, population INTEGER, language TEXT)\")\n",
    "countries = [('Andorra','Europe',77281, 'Catalan'),\n",
    "             ('China','Asia', 1403500365, 'Mandarin'),\n",
    "             ('Jamaica', 'America', 2881355, 'English'),\n",
    "             ('Chile', 'America', 18006407, 'Spanish'),\n",
    "             ('Australia', 'Oceania', 24680100, 'English')]\n",
    "cursor.executemany('INSERT INTO countries VALUES (?,?,?,?)', countries)\n",
    "connection.commit() \n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Si queremos verificar que la inserción fue realizada correctamente, debemos ejecutar una consulta sobre la misma base de datos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect('example.db')\n",
    "cursor = connection.cursor()\n",
    "cursor.execute('SELECT * FROM countries')\n",
    "\n",
    "#obtenemos sólo una fila, si queremos todas, debemos usar fetchall (no cambia el resultado en este caso)\n",
    "print(cursor.fetchone())\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Otra manera de revisar los contenidos de la base de datos, es utilizando una herramienta especializada para esto. Dentro de la gran variedad existente, una opción simple y liviana es [SQLiteStudio](sqlitestudio.pl), que también es multiplataforma (Windows, Linux, Mac)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parametrización de consultas\n",
    "\n",
    "La manera más directa y sencilla de introducir información variable en una consulta, es utilizando *strings*. Por ejemplo, si permitimos a un usuario indicar el nombre del país sobre el cuál se buscará información, es posible construir una consulta parametrizada de la siguiente manera:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect('example.db')\n",
    "cursor = connection.cursor()\n",
    "country = \"'China'\"\n",
    "cursor.execute(\"SELECT * FROM countries WHERE name = %s\" % country)\n",
    "print(cursor.fetchone())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A pesar de que la consulta anterior entregó el resultado esperado, el usar este tipo de estrategia para parametrizar consultas es un error muy común en la gran mayoría de los programas que interactuan con bases de datos. Esto puede generar grandes problemas de seguridad, debido a un ataque conocido como *SQL Injection*, que consiste en introducir, como dato para una consulta, consultas completas de SQL que pueden causar grandes pérdidas en la base de datos. Veamos el siguiente caso como ejemplo, donde eliminaremos completamente la tabla *countries* utilizando esta técnica:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect('example.db')\n",
    "cursor = connection.cursor()\n",
    "country = \"'China'; DROP TABLE countries;\"\n",
    "# a diferencia de execute, el método executescript permite ejecutar más de una consulta de \n",
    "# manera secuencial (esto abre la puerta a la ejecución de código indeseado).\n",
    "cursor.executescript(\"SELECT * FROM countries WHERE name = %s\" % country)\n",
    "\n",
    "#verificamos la existencia de la tabla\n",
    "cursor.execute('PRAGMA table_info([countries])')\n",
    "print(cursor.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para evitar situaciones de este tipo, es fundamental utilizar una técnica segura, como la **sustitución de parámetros**. En el caso de SQLite en Python, se debe poner el signo **?** en una consulta, cada vez que se desee sustituir posteriormente con un valor. Posteriormente, basta con introducir como una tupla los valores deseados, como segundo parámetro del método *execute*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"CREATE TABLE countries(name TEXT, continent TEXT, population INTEGER, language TEXT)\")\n",
    "countries = [('Andorra','Europe',77281, 'Catalan'),\n",
    "             ('China','Asia', 1403500365, 'Mandarin'),\n",
    "             ('Jamaica', 'America', 2881355, 'English'),\n",
    "             ('Chile', 'America', 18006407, 'Spanish'),\n",
    "             ('Australia', 'Oceania', 24680100, 'English')]\n",
    "cursor.executemany('INSERT INTO countries VALUES (?,?,?,?)', countries)\n",
    "\n",
    "country = ('Andorra',)\n",
    "cursor.execute('SELECT * FROM countries WHERE name = ?', country)\n",
    "print(cursor.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A diferencia del uso de *strings*, la sustitución de parámetros, al hacer explícita la existencia de información externa, permite realizar verificaciones de seguridad que impiden la ejecución de código malicioso. De esta manera, si intentamos realizar nuevamente un ataque de *SQL Injection*, el resultado será distinto:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country = (\"'China'; DROP TABLE countries;\",)\n",
    "cursor.execute(\"SELECT * FROM countries WHERE name = ?\", country)\n",
    "cursor.execute('PRAGMA table_info([countries])')\n",
    "print(cursor.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finalmente, con el fin de hacer más limpia (y formateable) la impresión de los resultados obtenidos por una consulta, es posible utilizar el cursor resultante de una consulta como un iterador:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for country in cursor.execute('SELECT * FROM countries ORDER BY name'):\n",
    "    print(country)\n",
    "connection.close()"
   ]
  }
 ],
 "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.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}