{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Consultas de Selección: Concatenación y Ordenación\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": [ "## Concatenación" ] }, { "cell_type": "code", "execution_count": 2, "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", "
nombre_completocorreo
0Jared Elyjared.ely@sakilacustomer.org
1Mary Smithmary.smith@sakilacustomer.org
2Patricia Johnsonpatricia.johnson@sakilacustomer.org
3Linda Williamslinda.williams@sakilacustomer.org
4Barbara Jonesbarbara.jones@sakilacustomer.org
.........
594Terrence Gundersonterrence.gunderson@sakilacustomer.org
595Enrique Forsytheenrique.forsythe@sakilacustomer.org
596Freddie Dugganfreddie.duggan@sakilacustomer.org
597Wade Delvallewade.delvalle@sakilacustomer.org
598Austin Cintronaustin.cintron@sakilacustomer.org
\n", "

599 rows × 2 columns

\n", "
" ], "text/plain": [ " nombre_completo correo\n", "0 Jared Ely jared.ely@sakilacustomer.org\n", "1 Mary Smith mary.smith@sakilacustomer.org\n", "2 Patricia Johnson patricia.johnson@sakilacustomer.org\n", "3 Linda Williams linda.williams@sakilacustomer.org\n", "4 Barbara Jones barbara.jones@sakilacustomer.org\n", ".. ... ...\n", "594 Terrence Gunderson terrence.gunderson@sakilacustomer.org\n", "595 Enrique Forsythe enrique.forsythe@sakilacustomer.org\n", "596 Freddie Duggan freddie.duggan@sakilacustomer.org\n", "597 Wade Delvalle wade.delvalle@sakilacustomer.org\n", "598 Austin Cintron austin.cintron@sakilacustomer.org\n", "\n", "[599 rows x 2 columns]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Operador de Concatenación y los Alias\n", "SQL = '''SELECT first_name || ' ' || last_name as nombre_completo, email as correo\n", "FROM customer;'''\n", "\n", "datos = pd.read_sql_query(SQL, cnx)\n", "\n", "datos" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 Jared Ely\n", "1 Mary Smith\n", "2 Patricia Johnson\n", "3 Linda Williams\n", "4 Barbara Jones\n", " ... \n", "594 Terrence Gunderson\n", "595 Enrique Forsythe\n", "596 Freddie Duggan\n", "597 Wade Delvalle\n", "598 Austin Cintron\n", "Name: nombre_completo, Length: 599, dtype: object" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Acceso a un atributo específico\n", "datos.nombre_completo" ] }, { "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", "
nombre completocorreo
0Jared Elyjared.ely@sakilacustomer.org
1Mary Smithmary.smith@sakilacustomer.org
2Patricia Johnsonpatricia.johnson@sakilacustomer.org
3Linda Williamslinda.williams@sakilacustomer.org
4Barbara Jonesbarbara.jones@sakilacustomer.org
.........
594Terrence Gundersonterrence.gunderson@sakilacustomer.org
595Enrique Forsytheenrique.forsythe@sakilacustomer.org
596Freddie Dugganfreddie.duggan@sakilacustomer.org
597Wade Delvallewade.delvalle@sakilacustomer.org
598Austin Cintronaustin.cintron@sakilacustomer.org
\n", "

599 rows × 2 columns

\n", "
" ], "text/plain": [ " nombre completo correo\n", "0 Jared Ely jared.ely@sakilacustomer.org\n", "1 Mary Smith mary.smith@sakilacustomer.org\n", "2 Patricia Johnson patricia.johnson@sakilacustomer.org\n", "3 Linda Williams linda.williams@sakilacustomer.org\n", "4 Barbara Jones barbara.jones@sakilacustomer.org\n", ".. ... ...\n", "594 Terrence Gunderson terrence.gunderson@sakilacustomer.org\n", "595 Enrique Forsythe enrique.forsythe@sakilacustomer.org\n", "596 Freddie Duggan freddie.duggan@sakilacustomer.org\n", "597 Wade Delvalle wade.delvalle@sakilacustomer.org\n", "598 Austin Cintron austin.cintron@sakilacustomer.org\n", "\n", "[599 rows x 2 columns]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Operador de Concatenación y los Alias con espacio en blanco\n", "SQL = '''SELECT first_name || ' ' || last_name as \"nombre completo\", email correo\n", "FROM customer;'''\n", "\n", "datos = pd.read_sql_query(SQL, cnx)\n", "\n", "datos" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 Jared Ely\n", "1 Mary Smith\n", "2 Patricia Johnson\n", "3 Linda Williams\n", "4 Barbara Jones\n", " ... \n", "594 Terrence Gunderson\n", "595 Enrique Forsythe\n", "596 Freddie Duggan\n", "597 Wade Delvalle\n", "598 Austin Cintron\n", "Name: nombre completo, Length: 599, dtype: object" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Acceso a un atributo específico cuando el alias tiene espacio en blanco\n", "datos['nombre completo']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ordenación" ] }, { "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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_namelast_name
0KarenJackson
1KarlSeal
2KatherineRivera
3KathleenAdams
4KathrynColeman
5KathyJames
6KatieElliott
7KayCaldwell
8KeithRico
9KellyTorres
10KellyKnott
11KenPrewitt
12KennethGooden
13KentArsenault
14KevinSchuler
15KimCruz
16KimberlyLee
17KirkStclair
18KristenChavez
19KristinJohnston
20KristinaChambers
21KurtEmmons
22KyleSpurlock
\n", "
" ], "text/plain": [ " first_name last_name\n", "0 Karen Jackson\n", "1 Karl Seal\n", "2 Katherine Rivera\n", "3 Kathleen Adams\n", "4 Kathryn Coleman\n", "5 Kathy James\n", "6 Katie Elliott\n", "7 Kay Caldwell\n", "8 Keith Rico\n", "9 Kelly Torres\n", "10 Kelly Knott\n", "11 Ken Prewitt\n", "12 Kenneth Gooden\n", "13 Kent Arsenault\n", "14 Kevin Schuler\n", "15 Kim Cruz\n", "16 Kimberly Lee\n", "17 Kirk Stclair\n", "18 Kristen Chavez\n", "19 Kristin Johnston\n", "20 Kristina Chambers\n", "21 Kurt Emmons\n", "22 Kyle Spurlock" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Primer nombre ordenado ascendentemente (a -> z) y apellido ordenado descendentemente (z -> a),\n", "# de todos los clientes con nombre que comienza por K\n", "\n", "SQL = '''SELECT first_name, last_name FROM customer\n", "WHERE first_name LIKE 'K%'\n", "ORDER BY first_name ASC, last_name DESC'''\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
primer_nombreconteo_letras
0Christopher11
1Jacqueline10
2Constance9
3Katherine9
4Nathaniel9
.........
594Ida3
595Sue3
596Sam3
597Dan3
598Jo2
\n", "

599 rows × 2 columns

\n", "
" ], "text/plain": [ " primer_nombre conteo_letras\n", "0 Christopher 11\n", "1 Jacqueline 10\n", "2 Constance 9\n", "3 Katherine 9\n", "4 Nathaniel 9\n", ".. ... ...\n", "594 Ida 3\n", "595 Sue 3\n", "596 Sam 3\n", "597 Dan 3\n", "598 Jo 2\n", "\n", "[599 rows x 2 columns]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Ordene los nombres por la cantidad de letras que contengan descendentemente\n", "SQL = '''SELECT first_name as primer_nombre, LENGTH(first_name) as conteo_letras\n", "FROM customer \n", "ORDER BY conteo_letras desc;'''\n", "\n", "pd.read_sql_query(SQL, cnx)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Manejo de Nulos en Order By\n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Eliminar la tabla para el ejemplo en caso de que exista\n", "sql = '''\n", "DROP TABLE IF EXISTS wait_list;\n", "'''\n", "\n", "cnx.execute(sql)" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "# Crearemos una tabla para ejemplificar el manejo de NULOS usando SQLAlchemy\n", "from sqlalchemy import Table, Column, Integer, String, MetaData\n", "meta = MetaData()\n", "\n", "lista_espera = Table(\n", " 'wait_list', meta, \n", " Column('id', Integer, primary_key = True), \n", " Column('name', String), \n", " Column('lastname', String), \n", ")\n", "\n", "meta.create_all(cnx)" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'INSERT INTO wait_list (id, name, lastname) VALUES (:id, :name, :lastname)'" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Se inserta un registro\n", "ins = lista_espera.insert().values(id = 1923, name = 'Robert', lastname = 'Maldani')\n", "result = cnx.execute(ins)\n", "str(ins)" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'INSERT INTO wait_list (id, name, lastname) VALUES (:id, :name, :lastname)'" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Se inserta un registro\n", "ins = lista_espera.insert().values(id = 3456, name = 'Natalia', lastname = 'Rosas')\n", "result = cnx.execute(ins)\n", "str(ins)" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Se inserta un registro, nótese que es posible enviar la sentencia construida manualmente\n", "ins = \"INSERT INTO wait_list (id, name, lastname) VALUES ('1234', 'Manuela', 'Saenz')\"\n", "cnx.execute(ins)" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'INSERT INTO wait_list (id, name) VALUES (:id, :name)'" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Se inserta un registro con un atributo faltante\n", "ins = lista_espera.insert().values(id = 5465, name = 'Julieta')\n", "result = cnx.execute(ins)\n", "str(ins)" ] }, { "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", "
idnamelastname
05465JulietaNone
11923RobertMaldani
23456NataliaRosas
31234ManuelaSaenz
\n", "
" ], "text/plain": [ " id name lastname\n", "0 5465 Julieta None\n", "1 1923 Robert Maldani\n", "2 3456 Natalia Rosas\n", "3 1234 Manuela Saenz" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Realizamos la consulta, los nulos primero\n", "SQL = '''SELECT *\n", "FROM wait_list \n", "ORDER BY lastname NULLS FIRST;'''\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", "
idnamelastname
01923RobertMaldani
13456NataliaRosas
21234ManuelaSaenz
35465JulietaNone
\n", "
" ], "text/plain": [ " id name lastname\n", "0 1923 Robert Maldani\n", "1 3456 Natalia Rosas\n", "2 1234 Manuela Saenz\n", "3 5465 Julieta None" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Realizamos la consulta, los nulos al final\n", "SQL = '''SELECT *\n", "FROM wait_list \n", "ORDER BY lastname NULLS LAST;'''\n", "\n", "pd.read_sql_query(SQL, cnx)" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "# Se elimina la tabla, por finalizar el ejemplo\n", "lista_espera.drop(cnx, checkfirst=True)\n", "\n", "# También es posible enviar directamente la sentencia SQL\n", "# sql = 'DROP TABLE IF EXISTS wait_list;'\n", "# result = cnx.execute(sql)" ] } ], "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 }