Consultas de Selección: DISTINCT, WHERE, LIMIT, FETCH¶
Conexión a la Base de Datos¶
[1]:
import pandas as pd
import psycopg2
import sqlalchemy
import matplotlib as plt
%matplotlib inline
from sqlalchemy import create_engine
POSTGRES_ADDRESS = 'localhost' # Este es el servidor, puede ser una IP
POSTGRES_PORT = '5432'
POSTGRES_USERNAME = 'postgres'
POSTGRES_PASSWORD = '1234'
POSTGRES_DBNAME = 'dvdrental'
# Ahora se configura la cadena de conexión.
# Esta es una cadena que contiene los parámetros necesarios para establecer una
# conexión con posgres.
postgres_str = ('postgresql://{username}:{password}@{ipaddress}:{port}/{dbname}'.format(username=POSTGRES_USERNAME,
password=POSTGRES_PASSWORD,
ipaddress=POSTGRES_ADDRESS,
port=POSTGRES_PORT,
dbname=POSTGRES_DBNAME))
# A continuación se crea la conexión
cnx = create_engine(postgres_str)
La Cláusula DISTINCT¶
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.
[2]:
#Crearemos una tabla para este ejemplo
sql = ''' CREATE TABLE colores (
id serial NOT NULL PRIMARY KEY,
primerColor VARCHAR,
segundoColor VARCHAR ); '''
cnx.execute(sql)
# Revisemos el concepto de SERIAL en Postgres http://sqlines.com/postgresql/datatypes/serial
[2]:
<sqlalchemy.engine.result.ResultProxy at 0x1cae2792850>
[3]:
# Insertamos algunos registros
sql = ''' INSERT INTO colores (primerColor, segundoColor)
VALUES ('rojo', 'rojo'), ('rojo', 'rojo'),
('rojo', NULL),(NULL, 'rojo'),
('rojo', 'verde'),('rojo', 'azul'),
('verde', 'rojo'),('verde', 'azul'),
('verde', 'verde'),('azul', 'rojo'),
('azul', 'verde'),('azul', 'azul'); '''
cnx.execute(sql)
[3]:
<sqlalchemy.engine.result.ResultProxy at 0x1cadfecb9d0>
[4]:
# Consultamos los registros insertados
SQL = '''SELECT id, primerColor, segundoColor from colores;'''
pd.read_sql_query(SQL, cnx)
[4]:
| id | primercolor | segundocolor | |
|---|---|---|---|
| 0 | 1 | rojo | rojo |
| 1 | 2 | rojo | rojo |
| 2 | 3 | rojo | None |
| 3 | 4 | None | rojo |
| 4 | 5 | rojo | verde |
| 5 | 6 | rojo | azul |
| 6 | 7 | verde | rojo |
| 7 | 8 | verde | azul |
| 8 | 9 | verde | verde |
| 9 | 10 | azul | rojo |
| 10 | 11 | azul | verde |
| 11 | 12 | azul | azul |
[5]:
# Cuáles son los colores disponibles en como primerColor
sql = ''' SELECT
DISTINCT primerColor
FROM colores
ORDER BY primerColor;'''
pd.read_sql_query(sql, cnx)
[5]:
| primercolor | |
|---|---|
| 0 | azul |
| 1 | rojo |
| 2 | verde |
| 3 | None |
[6]:
# Cuáles son las combinaciones únicas de color 1 y 2
sql = ''' SELECT
DISTINCT primerColor, segundoColor
FROM colores
ORDER BY primerColor, segundoColor;'''
pd.read_sql_query(sql, cnx)
[6]:
| primercolor | segundocolor | |
|---|---|---|
| 0 | azul | azul |
| 1 | azul | rojo |
| 2 | azul | verde |
| 3 | rojo | azul |
| 4 | rojo | rojo |
| 5 | rojo | verde |
| 6 | rojo | None |
| 7 | verde | azul |
| 8 | verde | rojo |
| 9 | verde | verde |
| 10 | None | rojo |
[7]:
# La siguiente declaración ordena el conjunto de resultados por color 1 y 2
# luego, para cada grupo de duplicados, mantiene la primera fila
# en el conjunto de resultados devuelto.
sql = ''' SELECT
DISTINCT ON (primerColor) primerColor, segundoColor
FROM colores
ORDER BY primerColor, segundoColor;'''
pd.read_sql_query(sql, cnx)
[7]:
| primercolor | segundocolor | |
|---|---|---|
| 0 | azul | azul |
| 1 | rojo | azul |
| 2 | verde | azul |
| 3 | None | rojo |
[8]:
# ¿Qué hace la siguiente sentencia?
sql = ''' SELECT
DISTINCT ON (primerColor) segundoColor
FROM colores
ORDER BY primerColor;'''
pd.read_sql_query(sql, cnx)
[8]:
| segundocolor | |
|---|---|
| 0 | azul |
| 1 | verde |
| 2 | verde |
| 3 | rojo |
[9]:
#Eliminamos la tabla de ejemplo
sql = ''' DROP TABLE colores; '''
cnx.execute(sql)
[9]:
<sqlalchemy.engine.result.ResultProxy at 0x1cae30ecc70>
La Cláusula WHERE¶
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.
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.
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.
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í.
[10]:
# Consultamos los clientes que sellamen Jamie y que su apellido no sea Rice
sql = ''' SELECT last_name, first_name
FROM customer
WHERE first_name = 'Jamie' AND last_name <> 'Rice';'''
pd.read_sql_query(sql, cnx)
[10]:
| last_name | first_name | |
|---|---|---|
| 0 | Waugh | Jamie |
[11]:
# Consultamos los clientes que se llamen Jamie o que su nombre comience con FR
sql = ''' SELECT last_name, first_name
FROM customer
WHERE first_name = 'Jamie' OR first_name LIKE 'Fr%';'''
pd.read_sql_query(sql, cnx)
[11]:
| last_name | first_name | |
|---|---|---|
| 0 | Parker | Frances |
| 1 | Rice | Jamie |
| 2 | Waggoner | Frank |
| 3 | Wheat | Fred |
| 4 | Sikes | Francis |
| 5 | Isbell | Frederick |
| 6 | Skidmore | Francisco |
| 7 | Troutman | Franklin |
| 8 | Waugh | Jamie |
| 9 | Duggan | Freddie |
[12]:
# Consultamos los clientes que estén en una lista
sql = ''' SELECT last_name, first_name
FROM customer
WHERE first_name in ('Marion', 'Kelly', 'Willie');'''
pd.read_sql_query(sql, cnx)
[12]:
| last_name | first_name | |
|---|---|---|
| 0 | Torres | Kelly |
| 1 | Snyder | Marion |
| 2 | Howell | Willie |
| 3 | Markham | Willie |
| 4 | Knott | Kelly |
| 5 | Ocampo | Marion |
[13]:
# Consultamos los clientes cuyo nombre comience por A y la
# longitud de su nombre tenga entre 3 y 4 lentras
sql = '''
SELECT first_name, LENGTH(first_name) cantCaracteres
FROM customer
WHERE
first_name LIKE 'A%' AND
LENGTH(first_name) BETWEEN 3 AND 4
ORDER BY cantCaracteres;
'''
pd.read_sql_query(sql, cnx)
[13]:
| first_name | cantcaracteres | |
|---|---|---|
| 0 | Ann | 3 |
| 1 | Ana | 3 |
| 2 | Amy | 3 |
| 3 | Alma | 4 |
| 4 | Adam | 4 |
| 5 | Alan | 4 |
| 6 | Alex | 4 |
| 7 | Andy | 4 |
| 8 | Anna | 4 |
| 9 | Anne | 4 |
[14]:
# Consultar los montos pagados que NO estén entre 2 y 10 USD
sql = '''
SELECT customer_id, payment_id, amount
FROM payment
WHERE amount NOT BETWEEN 2 AND 10;
'''
pd.read_sql_query(sql, cnx)
[14]:
| customer_id | payment_id | amount | |
|---|---|---|---|
| 0 | 341 | 17504 | 1.99 |
| 1 | 343 | 17514 | 0.99 |
| 2 | 343 | 17515 | 0.99 |
| 3 | 343 | 17518 | 0.99 |
| 4 | 344 | 17521 | 0.99 |
| ... | ... | ... | ... |
| 3427 | 229 | 32090 | 0.99 |
| 3428 | 234 | 32091 | 0.99 |
| 3429 | 236 | 32092 | 0.99 |
| 3430 | 251 | 32095 | 0.99 |
| 3431 | 263 | 32097 | 0.99 |
3432 rows × 3 columns
[15]:
# Consultar los pagos realizados entre el 7 y el 15 de febrero del 2007
sql = '''
SELECT customer_id, payment_id, amount, payment_date
FROM payment
WHERE payment_date BETWEEN '2007-02-07' AND '2007-02-15';
'''
pd.read_sql_query(sql, cnx)
[15]:
| customer_id | payment_id | amount | payment_date | |
|---|---|---|---|---|
| 0 | 368 | 17610 | 0.99 | 2007-02-14 23:25:11.996577 |
| 1 | 370 | 17617 | 6.99 | 2007-02-14 23:33:58.996577 |
| 2 | 402 | 17743 | 4.99 | 2007-02-14 23:53:34.996577 |
| 3 | 416 | 17793 | 2.99 | 2007-02-14 21:21:59.996577 |
| 4 | 432 | 17854 | 5.99 | 2007-02-14 23:07:27.996577 |
| 5 | 481 | 18051 | 2.99 | 2007-02-14 22:03:35.996577 |
| 6 | 512 | 18155 | 6.99 | 2007-02-14 22:57:03.996577 |
| 7 | 516 | 18173 | 4.99 | 2007-02-14 21:23:39.996577 |
| 8 | 546 | 18276 | 1.99 | 2007-02-14 23:10:43.996577 |
| 9 | 561 | 18322 | 2.99 | 2007-02-14 23:52:46.996577 |
| 10 | 592 | 18441 | 6.99 | 2007-02-14 21:41:12.996577 |
| 11 | 595 | 18456 | 2.99 | 2007-02-14 22:16:01.996577 |
| 12 | 1 | 18495 | 5.99 | 2007-02-14 23:22:38.996577 |
| 13 | 46 | 18686 | 4.99 | 2007-02-14 21:45:29.996577 |
| 14 | 49 | 18698 | 0.99 | 2007-02-14 21:44:52.996577 |
| 15 | 95 | 18870 | 2.99 | 2007-02-14 22:41:17.996577 |
| 16 | 119 | 18963 | 7.99 | 2007-02-14 23:05:16.996577 |
| 17 | 139 | 19036 | 2.99 | 2007-02-14 22:11:22.996577 |
| 18 | 173 | 19159 | 2.99 | 2007-02-14 23:32:33.996577 |
| 19 | 186 | 19212 | 4.99 | 2007-02-14 23:47:05.996577 |
| 20 | 191 | 19239 | 2.99 | 2007-02-14 22:23:12.996577 |
| 21 | 196 | 19257 | 5.99 | 2007-02-14 23:13:47.996577 |
| 22 | 197 | 19265 | 2.99 | 2007-02-14 22:43:41.996577 |
| 23 | 210 | 19293 | 2.99 | 2007-02-14 23:01:30.996577 |
| 24 | 239 | 19399 | 4.99 | 2007-02-14 21:29:00.996577 |
| 25 | 244 | 19421 | 6.99 | 2007-02-14 23:32:48.996577 |
| 26 | 264 | 19498 | 3.99 | 2007-02-14 21:44:53.996577 |
La cláusula Limit¶
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.
[16]:
# COnsultar las últimas 5 películas en orden alfabético
sql = '''
SELECT film_id, title, release_year
FROM film
ORDER BY title desc
LIMIT 5;
'''
pd.read_sql_query(sql, cnx)
[16]:
| film_id | title | release_year | |
|---|---|---|---|
| 0 | 1000 | Zorro Ark | 2006 |
| 1 | 999 | Zoolander Fiction | 2006 |
| 2 | 998 | Zhivago Core | 2006 |
| 3 | 997 | Youth Kick | 2006 |
| 4 | 996 | Young Language | 2006 |
[17]:
# COnsultar las últimas 5 películas en orden alfabético
sql = '''
SELECT film_id, title, release_year
FROM film
ORDER BY title
LIMIT 10;
'''
pd.read_sql_query(sql, cnx)
[17]:
| film_id | title | release_year | |
|---|---|---|---|
| 0 | 1 | Academy Dinosaur | 2006 |
| 1 | 2 | Ace Goldfinger | 2006 |
| 2 | 3 | Adaptation Holes | 2006 |
| 3 | 4 | Affair Prejudice | 2006 |
| 4 | 5 | African Egg | 2006 |
| 5 | 6 | Agent Truman | 2006 |
| 6 | 7 | Airplane Sierra | 2006 |
| 7 | 8 | Airport Pollock | 2006 |
| 8 | 9 | Alabama Devil | 2006 |
| 9 | 10 | Aladdin Calendar | 2006 |
[18]:
# Consultar las 3 peliculas siguientes a las primeras 5.
# Es decir, omitir las primeras 5 y tomar las 3 siguientes.
sql = '''
SELECT film_id, title, release_year
FROM film
ORDER BY film_id
LIMIT 3 OFFSET 5;
'''
pd.read_sql_query(sql, cnx)
[18]:
| film_id | title | release_year | |
|---|---|---|---|
| 0 | 6 | Agent Truman | 2006 |
| 1 | 7 | Airplane Sierra | 2006 |
| 2 | 8 | Airport Pollock | 2006 |
[19]:
# Consultar las 10 peliculas con el alquiler mas caro
sql = '''
SELECT film_id, title, rental_rate
FROM film
ORDER BY rental_rate DESC
LIMIT 10;
'''
pd.read_sql_query(sql, cnx)
[19]:
| film_id | title | rental_rate | |
|---|---|---|---|
| 0 | 13 | Ali Forever | 4.99 |
| 1 | 20 | Amelie Hellfighters | 4.99 |
| 2 | 7 | Airplane Sierra | 4.99 |
| 3 | 10 | Aladdin Calendar | 4.99 |
| 4 | 2 | Ace Goldfinger | 4.99 |
| 5 | 8 | Airport Pollock | 4.99 |
| 6 | 98 | Bright Encounters | 4.99 |
| 7 | 133 | Chamber Italian | 4.99 |
| 8 | 384 | Grosse Wonderful | 4.99 |
| 9 | 21 | American Circus | 4.99 |
Cláusula FETCH¶
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.
[20]:
# Tomar la primera fila
sql = '''
SELECT film_id, title
FROM film
ORDER BY title
FETCH FIRST ROW ONLY;
'''
pd.read_sql_query(sql, cnx)
[20]:
| film_id | title | |
|---|---|---|
| 0 | 1 | Academy Dinosaur |
[21]:
# Tomar las primeras 3, si se pone 1 es equivalente a la sentencia anterior
sql = '''
SELECT film_id, title
FROM film
ORDER BY title
FETCH FIRST 3 ROW ONLY;
'''
pd.read_sql_query(sql, cnx)
[21]:
| film_id | title | |
|---|---|---|
| 0 | 1 | Academy Dinosaur |
| 1 | 2 | Ace Goldfinger |
| 2 | 3 | Adaptation Holes |
[22]:
# Devuelve las siguientes tres películas después de las
# primeras cinco películas ordenadas por títulos
sql = '''
SELECT film_id, title
FROM film
ORDER BY title
OFFSET 5 ROWS
FETCH FIRST 3 ROW ONLY;
'''
pd.read_sql_query(sql, cnx)
[22]:
| film_id | title | |
|---|---|---|
| 0 | 6 | Agent Truman |
| 1 | 7 | Airplane Sierra |
| 2 | 8 | Airport Pollock |