Consultas de Selección: Concatenación y Ordenación¶
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)
Concatenación¶
[2]:
#Operador de Concatenación y los Alias
SQL = '''SELECT first_name || ' ' || last_name as nombre_completo, email as correo
FROM customer;'''
datos = pd.read_sql_query(SQL, cnx)
datos
[2]:
| nombre_completo | correo | |
|---|---|---|
| 0 | Jared Ely | jared.ely@sakilacustomer.org |
| 1 | Mary Smith | mary.smith@sakilacustomer.org |
| 2 | Patricia Johnson | patricia.johnson@sakilacustomer.org |
| 3 | Linda Williams | linda.williams@sakilacustomer.org |
| 4 | Barbara Jones | barbara.jones@sakilacustomer.org |
| ... | ... | ... |
| 594 | Terrence Gunderson | terrence.gunderson@sakilacustomer.org |
| 595 | Enrique Forsythe | enrique.forsythe@sakilacustomer.org |
| 596 | Freddie Duggan | freddie.duggan@sakilacustomer.org |
| 597 | Wade Delvalle | wade.delvalle@sakilacustomer.org |
| 598 | Austin Cintron | austin.cintron@sakilacustomer.org |
599 rows × 2 columns
[3]:
# Acceso a un atributo específico
datos.nombre_completo
[3]:
0 Jared Ely
1 Mary Smith
2 Patricia Johnson
3 Linda Williams
4 Barbara Jones
...
594 Terrence Gunderson
595 Enrique Forsythe
596 Freddie Duggan
597 Wade Delvalle
598 Austin Cintron
Name: nombre_completo, Length: 599, dtype: object
[4]:
#Operador de Concatenación y los Alias con espacio en blanco
SQL = '''SELECT first_name || ' ' || last_name as "nombre completo", email correo
FROM customer;'''
datos = pd.read_sql_query(SQL, cnx)
datos
[4]:
| nombre completo | correo | |
|---|---|---|
| 0 | Jared Ely | jared.ely@sakilacustomer.org |
| 1 | Mary Smith | mary.smith@sakilacustomer.org |
| 2 | Patricia Johnson | patricia.johnson@sakilacustomer.org |
| 3 | Linda Williams | linda.williams@sakilacustomer.org |
| 4 | Barbara Jones | barbara.jones@sakilacustomer.org |
| ... | ... | ... |
| 594 | Terrence Gunderson | terrence.gunderson@sakilacustomer.org |
| 595 | Enrique Forsythe | enrique.forsythe@sakilacustomer.org |
| 596 | Freddie Duggan | freddie.duggan@sakilacustomer.org |
| 597 | Wade Delvalle | wade.delvalle@sakilacustomer.org |
| 598 | Austin Cintron | austin.cintron@sakilacustomer.org |
599 rows × 2 columns
[5]:
# Acceso a un atributo específico cuando el alias tiene espacio en blanco
datos['nombre completo']
[5]:
0 Jared Ely
1 Mary Smith
2 Patricia Johnson
3 Linda Williams
4 Barbara Jones
...
594 Terrence Gunderson
595 Enrique Forsythe
596 Freddie Duggan
597 Wade Delvalle
598 Austin Cintron
Name: nombre completo, Length: 599, dtype: object
Ordenación¶
[6]:
# Primer nombre ordenado ascendentemente (a -> z) y apellido ordenado descendentemente (z -> a),
# de todos los clientes con nombre que comienza por K
SQL = '''SELECT first_name, last_name FROM customer
WHERE first_name LIKE 'K%'
ORDER BY first_name ASC, last_name DESC'''
pd.read_sql_query(SQL, cnx)
[6]:
| first_name | last_name | |
|---|---|---|
| 0 | Karen | Jackson |
| 1 | Karl | Seal |
| 2 | Katherine | Rivera |
| 3 | Kathleen | Adams |
| 4 | Kathryn | Coleman |
| 5 | Kathy | James |
| 6 | Katie | Elliott |
| 7 | Kay | Caldwell |
| 8 | Keith | Rico |
| 9 | Kelly | Torres |
| 10 | Kelly | Knott |
| 11 | Ken | Prewitt |
| 12 | Kenneth | Gooden |
| 13 | Kent | Arsenault |
| 14 | Kevin | Schuler |
| 15 | Kim | Cruz |
| 16 | Kimberly | Lee |
| 17 | Kirk | Stclair |
| 18 | Kristen | Chavez |
| 19 | Kristin | Johnston |
| 20 | Kristina | Chambers |
| 21 | Kurt | Emmons |
| 22 | Kyle | Spurlock |
[7]:
# Ordene los nombres por la cantidad de letras que contengan descendentemente
SQL = '''SELECT first_name as primer_nombre, LENGTH(first_name) as conteo_letras
FROM customer
ORDER BY conteo_letras desc;'''
pd.read_sql_query(SQL, cnx)
[7]:
| primer_nombre | conteo_letras | |
|---|---|---|
| 0 | Christopher | 11 |
| 1 | Jacqueline | 10 |
| 2 | Constance | 9 |
| 3 | Katherine | 9 |
| 4 | Nathaniel | 9 |
| ... | ... | ... |
| 594 | Ida | 3 |
| 595 | Sue | 3 |
| 596 | Sam | 3 |
| 597 | Dan | 3 |
| 598 | Jo | 2 |
599 rows × 2 columns
Manejo de Nulos en Order By¶
NULLes un marcador que indica los datos faltantes o los datos son desconocidos en el momento de la inserción. Cuando ordena las filas que contiene NULL, puede especificar el orden de NULL con otros valores no nulos utilizando la opción NULLS FIRST o NULLS LAST de la cláusula ORDER BY.
[10]:
# Eliminar la tabla para el ejemplo en caso de que exista
sql = '''
DROP TABLE IF EXISTS wait_list;
'''
cnx.execute(sql)
[10]:
<sqlalchemy.engine.result.ResultProxy at 0x21d7506d610>
[11]:
# Crearemos una tabla para ejemplificar el manejo de NULOS usando SQLAlchemy
from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()
lista_espera = Table(
'wait_list', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
meta.create_all(cnx)
[12]:
# Se inserta un registro
ins = lista_espera.insert().values(id = 1923, name = 'Robert', lastname = 'Maldani')
result = cnx.execute(ins)
str(ins)
[12]:
'INSERT INTO wait_list (id, name, lastname) VALUES (:id, :name, :lastname)'
[13]:
# Se inserta un registro
ins = lista_espera.insert().values(id = 3456, name = 'Natalia', lastname = 'Rosas')
result = cnx.execute(ins)
str(ins)
[13]:
'INSERT INTO wait_list (id, name, lastname) VALUES (:id, :name, :lastname)'
[14]:
# Se inserta un registro, nótese que es posible enviar la sentencia construida manualmente
ins = "INSERT INTO wait_list (id, name, lastname) VALUES ('1234', 'Manuela', 'Saenz')"
cnx.execute(ins)
[14]:
<sqlalchemy.engine.result.ResultProxy at 0x21d72c49820>
[15]:
# Se inserta un registro con un atributo faltante
ins = lista_espera.insert().values(id = 5465, name = 'Julieta')
result = cnx.execute(ins)
str(ins)
[15]:
'INSERT INTO wait_list (id, name) VALUES (:id, :name)'
[16]:
#Realizamos la consulta, los nulos primero
SQL = '''SELECT *
FROM wait_list
ORDER BY lastname NULLS FIRST;'''
pd.read_sql_query(SQL, cnx)
[16]:
| id | name | lastname | |
|---|---|---|---|
| 0 | 5465 | Julieta | None |
| 1 | 1923 | Robert | Maldani |
| 2 | 3456 | Natalia | Rosas |
| 3 | 1234 | Manuela | Saenz |
[17]:
#Realizamos la consulta, los nulos al final
SQL = '''SELECT *
FROM wait_list
ORDER BY lastname NULLS LAST;'''
pd.read_sql_query(SQL, cnx)
[17]:
| id | name | lastname | |
|---|---|---|---|
| 0 | 1923 | Robert | Maldani |
| 1 | 3456 | Natalia | Rosas |
| 2 | 1234 | Manuela | Saenz |
| 3 | 5465 | Julieta | None |
[18]:
# Se elimina la tabla, por finalizar el ejemplo
lista_espera.drop(cnx, checkfirst=True)
# También es posible enviar directamente la sentencia SQL
# sql = 'DROP TABLE IF EXISTS wait_list;'
# result = cnx.execute(sql)