{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Consultas de Selección: DISTINCT, WHERE, LIMIT, FETCH\n", "\n", "## Conexión a la Base de Datos" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import psycopg2\n", "import sqlalchemy\n", "import matplotlib as plt\n", "\n", "%matplotlib inline\n", "\n", "from sqlalchemy import create_engine\n", "\n", "POSTGRES_ADDRESS = 'localhost' # Este es el servidor, puede ser una IP\n", "POSTGRES_PORT = '5432'\n", "POSTGRES_USERNAME = 'postgres' \n", "POSTGRES_PASSWORD = '1234' \n", "POSTGRES_DBNAME = 'dvdrental' \n", "\n", "# Ahora se configura la cadena de conexión.\n", "# Esta es una cadena que contiene los parámetros necesarios para establecer una\n", "# conexión con posgres.\n", "postgres_str = ('postgresql://{username}:{password}@{ipaddress}:{port}/{dbname}'.format(username=POSTGRES_USERNAME,\n", " password=POSTGRES_PASSWORD,\n", " ipaddress=POSTGRES_ADDRESS,\n", " port=POSTGRES_PORT,\n", " dbname=POSTGRES_DBNAME))\n", "# A continuación se crea la conexión\n", "cnx = create_engine(postgres_str)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## La Cláusula DISTINCT\n", "\n", "Esta cláusula se utiliza en la declaración SELECT para eliminar filas duplicadas de un conjunto de resultados. NO las elimina de la tabla. DISTINCT mantiene una fila para cada grupo de duplicados, se puede aplicar a una o más columnas en la lista de selección de la consulta SELECT." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Crearemos una tabla para este ejemplo\n", "sql = ''' CREATE TABLE colores ( \n", " id serial NOT NULL PRIMARY KEY,\n", " primerColor VARCHAR,\n", " segundoColor VARCHAR ); '''\n", "\n", "cnx.execute(sql)\n", "\n", "# Revisemos el concepto de SERIAL en Postgres http://sqlines.com/postgresql/datatypes/serial" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Insertamos algunos registros\n", "sql = ''' INSERT INTO colores (primerColor, segundoColor)\n", " VALUES ('rojo', 'rojo'), ('rojo', 'rojo'),\n", " ('rojo', NULL),(NULL, 'rojo'),\n", " ('rojo', 'verde'),('rojo', 'azul'),\n", " ('verde', 'rojo'),('verde', 'azul'),\n", " ('verde', 'verde'),('azul', 'rojo'),\n", " ('azul', 'verde'),('azul', 'azul'); '''\n", "\n", "cnx.execute(sql)" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idprimercolorsegundocolor
01rojorojo
12rojorojo
23rojoNone
34Nonerojo
45rojoverde
56rojoazul
67verderojo
78verdeazul
89verdeverde
910azulrojo
1011azulverde
1112azulazul
\n", "
" ], "text/plain": [ " id primercolor segundocolor\n", "0 1 rojo rojo\n", "1 2 rojo rojo\n", "2 3 rojo None\n", "3 4 None rojo\n", "4 5 rojo verde\n", "5 6 rojo azul\n", "6 7 verde rojo\n", "7 8 verde azul\n", "8 9 verde verde\n", "9 10 azul rojo\n", "10 11 azul verde\n", "11 12 azul azul" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultamos los registros insertados\n", "SQL = '''SELECT id, primerColor, segundoColor from colores;'''\n", "\n", "pd.read_sql_query(SQL, cnx)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
primercolor
0azul
1rojo
2verde
3None
\n", "
" ], "text/plain": [ " primercolor\n", "0 azul\n", "1 rojo\n", "2 verde\n", "3 None" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Cuáles son los colores disponibles en como primerColor\n", "sql = ''' SELECT \n", " DISTINCT primerColor\n", " FROM colores\n", " ORDER BY primerColor;'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
primercolorsegundocolor
0azulazul
1azulrojo
2azulverde
3rojoazul
4rojorojo
5rojoverde
6rojoNone
7verdeazul
8verderojo
9verdeverde
10Nonerojo
\n", "
" ], "text/plain": [ " primercolor segundocolor\n", "0 azul azul\n", "1 azul rojo\n", "2 azul verde\n", "3 rojo azul\n", "4 rojo rojo\n", "5 rojo verde\n", "6 rojo None\n", "7 verde azul\n", "8 verde rojo\n", "9 verde verde\n", "10 None rojo" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Cuáles son las combinaciones únicas de color 1 y 2\n", "sql = ''' SELECT \n", " DISTINCT primerColor, segundoColor\n", " FROM colores\n", " ORDER BY primerColor, segundoColor;'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
primercolorsegundocolor
0azulazul
1rojoazul
2verdeazul
3Nonerojo
\n", "
" ], "text/plain": [ " primercolor segundocolor\n", "0 azul azul\n", "1 rojo azul\n", "2 verde azul\n", "3 None rojo" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# La siguiente declaración ordena el conjunto de resultados por color 1 y 2\n", "# luego, para cada grupo de duplicados, mantiene la primera fila \n", "# en el conjunto de resultados devuelto.\n", "\n", "sql = ''' SELECT \n", " DISTINCT ON (primerColor) primerColor, segundoColor\n", " FROM colores\n", " ORDER BY primerColor, segundoColor;'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
segundocolor
0azul
1verde
2verde
3rojo
\n", "
" ], "text/plain": [ " segundocolor\n", "0 azul\n", "1 verde\n", "2 verde\n", "3 rojo" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# ¿Qué hace la siguiente sentencia?\n", "\n", "sql = ''' SELECT \n", " DISTINCT ON (primerColor) segundoColor\n", " FROM colores\n", " ORDER BY primerColor;'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Eliminamos la tabla de ejemplo\n", "sql = ''' DROP TABLE colores; '''\n", "\n", "cnx.execute(sql)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## La Cláusula WHERE\n", "\n", "La declaración SELECT devuelve todas las filas de una o más columnas en una tabla. Para seleccionar filas que satisfagan una condición específica, use la cláusula WHERE.\n", "\n", "La consulta solo devuelve filas que cumplen con condición en la cláusula WHERE. Es decir, solo las filas que hacen que la condición de evaluación sea verdadera se incluirán en el conjunto de resultados.\n", "\n", "El uso de esta cláusula es clave en la declaración UPDATE y DELETE para especificar las filas que se actualizarán o eliminarán. En caso de no especificarse, se actualizan o eliminan todas las filas lo cual conduce a pérdida de información o resultados indeseados. \n", "\n", "Para formar la condición en la cláusula WHERE, se usan operadores de comparación y lógicos. Puedes consultar los operadores lógicos [aquí](https://www.postgresqltutorial.com/postgresql-where/).\n", "\n" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
last_namefirst_name
0WaughJamie
\n", "
" ], "text/plain": [ " last_name first_name\n", "0 Waugh Jamie" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultamos los clientes que sellamen Jamie y que su apellido no sea Rice\n", "sql = ''' SELECT last_name, first_name\n", " FROM customer\n", " WHERE first_name = 'Jamie' AND last_name <> 'Rice';'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
last_namefirst_name
0ParkerFrances
1RiceJamie
2WaggonerFrank
3WheatFred
4SikesFrancis
5IsbellFrederick
6SkidmoreFrancisco
7TroutmanFranklin
8WaughJamie
9DugganFreddie
\n", "
" ], "text/plain": [ " last_name first_name\n", "0 Parker Frances\n", "1 Rice Jamie\n", "2 Waggoner Frank\n", "3 Wheat Fred\n", "4 Sikes Francis\n", "5 Isbell Frederick\n", "6 Skidmore Francisco\n", "7 Troutman Franklin\n", "8 Waugh Jamie\n", "9 Duggan Freddie" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultamos los clientes que se llamen Jamie o que su nombre comience con FR\n", "sql = ''' SELECT last_name, first_name\n", " FROM customer\n", " WHERE first_name = 'Jamie' OR first_name LIKE 'Fr%';'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
last_namefirst_name
0TorresKelly
1SnyderMarion
2HowellWillie
3MarkhamWillie
4KnottKelly
5OcampoMarion
\n", "
" ], "text/plain": [ " last_name first_name\n", "0 Torres Kelly\n", "1 Snyder Marion\n", "2 Howell Willie\n", "3 Markham Willie\n", "4 Knott Kelly\n", "5 Ocampo Marion" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultamos los clientes que estén en una lista\n", "sql = ''' SELECT last_name, first_name\n", " FROM customer\n", " WHERE first_name in ('Marion', 'Kelly', 'Willie');'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_namecantcaracteres
0Ann3
1Ana3
2Amy3
3Alma4
4Adam4
5Alan4
6Alex4
7Andy4
8Anna4
9Anne4
\n", "
" ], "text/plain": [ " first_name cantcaracteres\n", "0 Ann 3\n", "1 Ana 3\n", "2 Amy 3\n", "3 Alma 4\n", "4 Adam 4\n", "5 Alan 4\n", "6 Alex 4\n", "7 Andy 4\n", "8 Anna 4\n", "9 Anne 4" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultamos los clientes cuyo nombre comience por A y la \n", "# longitud de su nombre tenga entre 3 y 4 lentras\n", "\n", "sql = ''' \n", "SELECT first_name, LENGTH(first_name) cantCaracteres\n", "FROM customer\n", "WHERE \n", " first_name LIKE 'A%' AND\n", " LENGTH(first_name) BETWEEN 3 AND 4\n", "ORDER BY cantCaracteres;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
customer_idpayment_idamount
0341175041.99
1343175140.99
2343175150.99
3343175180.99
4344175210.99
............
3427229320900.99
3428234320910.99
3429236320920.99
3430251320950.99
3431263320970.99
\n", "

3432 rows × 3 columns

\n", "
" ], "text/plain": [ " customer_id payment_id amount\n", "0 341 17504 1.99\n", "1 343 17514 0.99\n", "2 343 17515 0.99\n", "3 343 17518 0.99\n", "4 344 17521 0.99\n", "... ... ... ...\n", "3427 229 32090 0.99\n", "3428 234 32091 0.99\n", "3429 236 32092 0.99\n", "3430 251 32095 0.99\n", "3431 263 32097 0.99\n", "\n", "[3432 rows x 3 columns]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultar los montos pagados que NO estén entre 2 y 10 USD\n", "\n", "sql = ''' \n", "SELECT customer_id, payment_id, amount\n", "FROM payment\n", "WHERE amount NOT BETWEEN 2 AND 10;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
customer_idpayment_idamountpayment_date
0368176100.992007-02-14 23:25:11.996577
1370176176.992007-02-14 23:33:58.996577
2402177434.992007-02-14 23:53:34.996577
3416177932.992007-02-14 21:21:59.996577
4432178545.992007-02-14 23:07:27.996577
5481180512.992007-02-14 22:03:35.996577
6512181556.992007-02-14 22:57:03.996577
7516181734.992007-02-14 21:23:39.996577
8546182761.992007-02-14 23:10:43.996577
9561183222.992007-02-14 23:52:46.996577
10592184416.992007-02-14 21:41:12.996577
11595184562.992007-02-14 22:16:01.996577
121184955.992007-02-14 23:22:38.996577
1346186864.992007-02-14 21:45:29.996577
1449186980.992007-02-14 21:44:52.996577
1595188702.992007-02-14 22:41:17.996577
16119189637.992007-02-14 23:05:16.996577
17139190362.992007-02-14 22:11:22.996577
18173191592.992007-02-14 23:32:33.996577
19186192124.992007-02-14 23:47:05.996577
20191192392.992007-02-14 22:23:12.996577
21196192575.992007-02-14 23:13:47.996577
22197192652.992007-02-14 22:43:41.996577
23210192932.992007-02-14 23:01:30.996577
24239193994.992007-02-14 21:29:00.996577
25244194216.992007-02-14 23:32:48.996577
26264194983.992007-02-14 21:44:53.996577
\n", "
" ], "text/plain": [ " customer_id payment_id amount payment_date\n", "0 368 17610 0.99 2007-02-14 23:25:11.996577\n", "1 370 17617 6.99 2007-02-14 23:33:58.996577\n", "2 402 17743 4.99 2007-02-14 23:53:34.996577\n", "3 416 17793 2.99 2007-02-14 21:21:59.996577\n", "4 432 17854 5.99 2007-02-14 23:07:27.996577\n", "5 481 18051 2.99 2007-02-14 22:03:35.996577\n", "6 512 18155 6.99 2007-02-14 22:57:03.996577\n", "7 516 18173 4.99 2007-02-14 21:23:39.996577\n", "8 546 18276 1.99 2007-02-14 23:10:43.996577\n", "9 561 18322 2.99 2007-02-14 23:52:46.996577\n", "10 592 18441 6.99 2007-02-14 21:41:12.996577\n", "11 595 18456 2.99 2007-02-14 22:16:01.996577\n", "12 1 18495 5.99 2007-02-14 23:22:38.996577\n", "13 46 18686 4.99 2007-02-14 21:45:29.996577\n", "14 49 18698 0.99 2007-02-14 21:44:52.996577\n", "15 95 18870 2.99 2007-02-14 22:41:17.996577\n", "16 119 18963 7.99 2007-02-14 23:05:16.996577\n", "17 139 19036 2.99 2007-02-14 22:11:22.996577\n", "18 173 19159 2.99 2007-02-14 23:32:33.996577\n", "19 186 19212 4.99 2007-02-14 23:47:05.996577\n", "20 191 19239 2.99 2007-02-14 22:23:12.996577\n", "21 196 19257 5.99 2007-02-14 23:13:47.996577\n", "22 197 19265 2.99 2007-02-14 22:43:41.996577\n", "23 210 19293 2.99 2007-02-14 23:01:30.996577\n", "24 239 19399 4.99 2007-02-14 21:29:00.996577\n", "25 244 19421 6.99 2007-02-14 23:32:48.996577\n", "26 264 19498 3.99 2007-02-14 21:44:53.996577" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultar los pagos realizados entre el 7 y el 15 de febrero del 2007\n", "\n", "sql = ''' \n", "SELECT customer_id, payment_id, amount, payment_date\n", "FROM payment\n", "WHERE payment_date BETWEEN '2007-02-07' AND '2007-02-15';\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## La cláusula Limit\n", "\n", "Restringe el número de filas devueltas por la consulta. La cláusula LIMIT es ampliamente utilizada por muchos sistemas de administración de bases de datos relacionales como SQLite, Postgres, MySQL, H2 y HSQLDB. " ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
film_idtitlerelease_year
01000Zorro Ark2006
1999Zoolander Fiction2006
2998Zhivago Core2006
3997Youth Kick2006
4996Young Language2006
\n", "
" ], "text/plain": [ " film_id title release_year\n", "0 1000 Zorro Ark 2006\n", "1 999 Zoolander Fiction 2006\n", "2 998 Zhivago Core 2006\n", "3 997 Youth Kick 2006\n", "4 996 Young Language 2006" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# COnsultar las últimas 5 películas en orden alfabético\n", "\n", "sql = ''' \n", "SELECT film_id, title, release_year\n", "FROM film\n", "ORDER BY title desc\n", "LIMIT 5;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
film_idtitlerelease_year
01Academy Dinosaur2006
12Ace Goldfinger2006
23Adaptation Holes2006
34Affair Prejudice2006
45African Egg2006
56Agent Truman2006
67Airplane Sierra2006
78Airport Pollock2006
89Alabama Devil2006
910Aladdin Calendar2006
\n", "
" ], "text/plain": [ " film_id title release_year\n", "0 1 Academy Dinosaur 2006\n", "1 2 Ace Goldfinger 2006\n", "2 3 Adaptation Holes 2006\n", "3 4 Affair Prejudice 2006\n", "4 5 African Egg 2006\n", "5 6 Agent Truman 2006\n", "6 7 Airplane Sierra 2006\n", "7 8 Airport Pollock 2006\n", "8 9 Alabama Devil 2006\n", "9 10 Aladdin Calendar 2006" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# COnsultar las últimas 5 películas en orden alfabético\n", "\n", "sql = ''' \n", "SELECT film_id, title, release_year\n", "FROM film\n", "ORDER BY title\n", "LIMIT 10;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
film_idtitlerelease_year
06Agent Truman2006
17Airplane Sierra2006
28Airport Pollock2006
\n", "
" ], "text/plain": [ " film_id title release_year\n", "0 6 Agent Truman 2006\n", "1 7 Airplane Sierra 2006\n", "2 8 Airport Pollock 2006" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultar las 3 peliculas siguientes a las primeras 5. \n", "# Es decir, omitir las primeras 5 y tomar las 3 siguientes.\n", "\n", "sql = ''' \n", "SELECT film_id, title, release_year\n", "FROM film\n", "ORDER BY film_id\n", "LIMIT 3 OFFSET 5;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
film_idtitlerental_rate
013Ali Forever4.99
120Amelie Hellfighters4.99
27Airplane Sierra4.99
310Aladdin Calendar4.99
42Ace Goldfinger4.99
58Airport Pollock4.99
698Bright Encounters4.99
7133Chamber Italian4.99
8384Grosse Wonderful4.99
921American Circus4.99
\n", "
" ], "text/plain": [ " film_id title rental_rate\n", "0 13 Ali Forever 4.99\n", "1 20 Amelie Hellfighters 4.99\n", "2 7 Airplane Sierra 4.99\n", "3 10 Aladdin Calendar 4.99\n", "4 2 Ace Goldfinger 4.99\n", "5 8 Airport Pollock 4.99\n", "6 98 Bright Encounters 4.99\n", "7 133 Chamber Italian 4.99\n", "8 384 Grosse Wonderful 4.99\n", "9 21 American Circus 4.99" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultar las 10 peliculas con el alquiler mas caro\n", "\n", "sql = ''' \n", "SELECT film_id, title, rental_rate\n", "FROM film\n", "ORDER BY rental_rate DESC\n", "LIMIT 10;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Cláusula FETCH\n", "\n", "La cláusula LIMIT no es un estándar SQL. Para cumplir con el estándar SQL, PostgreSQL admite la cláusula FETCH para recuperar una cantidad de filas devueltas por una consulta. Tenga en cuenta que la esta cláusula se introdujo en SQL versión 2008.\n" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
film_idtitle
01Academy Dinosaur
\n", "
" ], "text/plain": [ " film_id title\n", "0 1 Academy Dinosaur" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Tomar la primera fila\n", "\n", "sql = ''' \n", "SELECT film_id, title\n", "FROM film\n", "ORDER BY title \n", "FETCH FIRST ROW ONLY;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
film_idtitle
01Academy Dinosaur
12Ace Goldfinger
23Adaptation Holes
\n", "
" ], "text/plain": [ " film_id title\n", "0 1 Academy Dinosaur\n", "1 2 Ace Goldfinger\n", "2 3 Adaptation Holes" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Tomar las primeras 3, si se pone 1 es equivalente a la sentencia anterior\n", "\n", "sql = ''' \n", "SELECT film_id, title\n", "FROM film\n", "ORDER BY title \n", "FETCH FIRST 3 ROW ONLY;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
film_idtitle
06Agent Truman
17Airplane Sierra
28Airport Pollock
\n", "
" ], "text/plain": [ " film_id title\n", "0 6 Agent Truman\n", "1 7 Airplane Sierra\n", "2 8 Airport Pollock" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Devuelve las siguientes tres películas después de las \n", "# primeras cinco películas ordenadas por títulos\n", "\n", "sql = ''' \n", "SELECT film_id, title\n", "FROM film\n", "ORDER BY title \n", "OFFSET 5 ROWS \n", "FETCH FIRST 3 ROW ONLY; \n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] } ], "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.5" } }, "nbformat": 4, "nbformat_minor": 4 }