{ "cells": [ { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "-" } }, "source": [ "# SQLAlchemy\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "SQLAlchemy es una librería para Python que facilita el acceso a una base de datos relacional, así como las operaciones a realizar sobre la misma.\n", "\n", "Es independiente del motor de base de datos a utilizar, es decir, en principio, es compatible con la mayoría de bases de datos relacionales conocidas: PostgreSQL, MySQL, Oracle, Microsoft SQL Server, Sqlite, …" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Conección a bases de datos\n", "\n", "Lo primero que hay que hacer para trabajar con SQLAlchemy es crear un `engine`. El `engine` es el punto de entrada a la base de datos, es decir, el que permite a SQLAlchemy comunicarse con esta.\n", "\n", "El motor se usa principalmente para manejar dos elementos: \n", "* **pools de conexiones**: Cuando se usa una conexión a la base de datos, esta ya está creada previamente y es reutilizada por el programa.\n", "\n", "* **dialectos de base de datos**: A pesar de que el lenguaje SQL es universal, cada motor de base de datos introduce ciertas variaciones propietarias sobre dicho lenguaje. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "En la carpeta data se encuentra el archivo `chinook.db`, que es básicamente una base de datos sql. \n", "\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Las 11 tablas se definen de la siguiente forma (en inglés):\n", "\n", "- ```employees``` table stores employees data such as employee id, last name, first name, etc. It also has a field named ReportsTo to specify who reports to whom.\n", "- ```customers``` table stores customers data.\n", "- ```invoices``` & ```invoice_items``` tables: these two tables store invoice data. The ```invoices``` table stores invoice header data and the ```invoice_items``` table stores the invoice line items data.\n", "- ```artists``` table stores artists data. It is a simple table that contains only artist id and name.\n", "- ```albums``` table stores data about a list of tracks. Each album belongs to one artist. However, one artist may have multiple albums.\n", "- ```media_types``` table stores media types such as MPEG audio file, ACC audio file, etc.\n", "- ```genres``` table stores music types such as rock, jazz, metal, etc.\n", "- ```tracks``` table store the data of songs. Each track belongs to one album.\n", "- ```playlists``` & ```playlist_track tables```: ```playlists``` table store data about playlists. Each playlist contains a list of tracks. Each track may belong to multiple playlists. The relationship between the ```playlists``` table and ```tracks``` table is many-to-many. The ```playlist_track``` table is used to reflect this relationship.\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Nuestra primera consulata consiste en ver todas las tablas de el esquema de datos." ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [], "source": [ "from sqlalchemy import create_engine\n", "import pandas as pd\n", "import os \n", "\n", "# Crear conector\n", "engine = create_engine(os.path.join('sqlite:///','data', 'chinook.db'))" ] }, { "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
0albums
1sqlite_sequence
2artists
3customers
4employees
5genres
6invoices
7invoice_items
8media_types
9playlists
10playlist_track
11tracks
12sqlite_stat1
\n", "
" ], "text/plain": [ " name\n", "0 albums\n", "1 sqlite_sequence\n", "2 artists\n", "3 customers\n", "4 employees\n", "5 genres\n", "6 invoices\n", "7 invoice_items\n", "8 media_types\n", "9 playlists\n", "10 playlist_track\n", "11 tracks\n", "12 sqlite_stat1" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Crear query\n", "query =\"\"\"\n", "SELECT name \n", "FROM sqlite_master \n", "WHERE type='table'\n", "\"\"\"\n", "df = pd.read_sql(query, con=engine)\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Comandos en SQL\n", "\n", "### Manipulación de datos\n", "Los comandos básicos de manipulación de datos son:\n", "\n", "\n", "\n", "| Comando | Descripción | |\n", "|---------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---|\n", "| SELECT | Utilizado para consultar registros de la base de datos que satisfagan un criterio determinado | |\n", "| INSERT | Utilizado para cargar lotes de datos en la base de datos en una única operación. | |\n", "| UPDATE | Utilizado para modificar los valores de los campos y registros especificados Utilizado para modificar las tablas agregando campos o cambiando la definición de los campos. | |\n", "| DELETE | Utilizado para eliminar registros de una tabla | |" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "De estos cuatro comandos, esta seción estará centrada en el comando `SELECT` (útil para hacer consultas en SQL)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Clausulas\n", "Las cláusulas son condiciones de modificación utilizadas para definir los datos que desea seleccionar o manipular.\n", "\n", "\n", "\n", "| Comando | Descripción |\n", "|----------|---------------------------------------------------------------------------------------|\n", "| FROM | Utilizada para especificar la tabla de la cual se van a seleccionar los registros |\n", "| GROUP BY | Utilizada para separar los registros seleccionados en grupos específicos |\n", "| HAVING | Utilizada para expresar condición que debe satisfacer cada grupo |\n", "| ORDER BY | Utilizada para ordenar los registros seleccionados de acuerdo con un orden específico |\n", "| WHERE | Utilizada para determinar los registros seleccionados en la clausula FROM |" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Operadores\n", "\n", "#### Operadores Lógicos\n", "\n", "| Operador | Descripción |\n", "|----------|--------------------------------------------------------------------------------------------------------|\n", "| AND | Es el “y” lógico. Evalúa dos condiciones y devuelve un valor de verdad sólo si ambas son ciertas. |\n", "| OR | Es el “o” lógico. Evalúa dos condiciones y devuelve un valor de verdad si alguna de las dos es cierta. |\n", "| NOT | Negación lógica. Devuelve el valor contrario de la expresión. |" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Operadores de comparación\n", "\n", "| Operador | Descripción |\n", "|----------|-------------------|\n", "| < | Menor que |\n", "| > | Mayor que |\n", "| <> | Distinto de |\n", "| <= | Menor o igual que |\n", "| >= | Mayor o igual que |\n", "| BETWEEN | Intervalo |\n", "| LIKE | Comparación |\n", "| In | Especificar |" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Funciones de agregado\n", "\n", "Las funciones de agregado se usan dentro de una cláusula SELECT en grupos de registros para devolver un único valor que se aplica a un grupo de registros.\n", "\n", "\n", "\n", "| Comando | Descripción |\n", "|---------|------------------------------------------------------------------------------|\n", "| AVG | Utilizada para calcular el promedio de los valores de un campo determinado |\n", "| COUNT | Utilizada para devolver el número de registros de la selección |\n", "| SUM | Utilizada para devolver la suma de todos los valores de un campo determinado |\n", "| MAX | Utilizada para devolver el valor más alto de un campo especificado |\n", "| MIN | Utilizada para devolver el valor más bajo de un campo especificado |" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Consultas\n", "Las consultas de selección se utilizan para indicar al motor de datos que devuelva información de las bases de datos, esta información es devuelta en forma de conjunto de registros. Este conjunto de registros es modificable.\n", "\n", "Lo primero es crear una función para poder conectarse a la base de datos y realizar las distintas consultas." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Consultas básicas\n", "\n", "Se realizarán distintas consultas ocupando los operadores básicos y de comparación." ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [], "source": [ "# funcion de consultas\n", "def consulta(query,conn):\n", " return pd.read_sql_query(query, con=conn)" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [], "source": [ "# Crear conector\n", "conn = create_engine(os.path.join('sqlite:///','data', 'chinook.db'))" ] }, { "cell_type": "code", "execution_count": 23, "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", "
EmployeeIdLastNameFirstNameTitle
01AdamsAndrewGeneral Manager
12EdwardsNancySales Manager
23PeacockJaneSales Support Agent
34ParkMargaretSales Support Agent
45JohnsonSteveSales Support Agent
56MitchellMichaelIT Manager
67KingRobertIT Staff
78CallahanLauraIT Staff
\n", "
" ], "text/plain": [ " EmployeeId LastName FirstName Title\n", "0 1 Adams Andrew General Manager\n", "1 2 Edwards Nancy Sales Manager\n", "2 3 Peacock Jane Sales Support Agent\n", "3 4 Park Margaret Sales Support Agent\n", "4 5 Johnson Steve Sales Support Agent\n", "5 6 Mitchell Michael IT Manager\n", "6 7 King Robert IT Staff\n", "7 8 Callahan Laura IT Staff" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# basicas\n", "query = \"\"\"\n", "SELECT EmployeeId,LastName, FirstName , Title FROM employees\n", "\"\"\"\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 24, "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", "
EmployeeIdLastNameFirstNameTitle
01AdamsAndrewGeneral Manager
18CallahanLauraIT Staff
22EdwardsNancySales Manager
35JohnsonSteveSales Support Agent
47KingRobertIT Staff
56MitchellMichaelIT Manager
64ParkMargaretSales Support Agent
73PeacockJaneSales Support Agent
\n", "
" ], "text/plain": [ " EmployeeId LastName FirstName Title\n", "0 1 Adams Andrew General Manager\n", "1 8 Callahan Laura IT Staff\n", "2 2 Edwards Nancy Sales Manager\n", "3 5 Johnson Steve Sales Support Agent\n", "4 7 King Robert IT Staff\n", "5 6 Mitchell Michael IT Manager\n", "6 4 Park Margaret Sales Support Agent\n", "7 3 Peacock Jane Sales Support Agent" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Ordenar los registros: una columna\n", "query = \"\"\"\n", "SELECT EmployeeId,LastName, FirstName , Title FROM employees ORDER BY LastName\n", "\"\"\"\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 25, "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", "
EmployeeIdLastNameFirstNameTitle
01AdamsAndrewGeneral Manager
18CallahanLauraIT Staff
22EdwardsNancySales Manager
35JohnsonSteveSales Support Agent
47KingRobertIT Staff
56MitchellMichaelIT Manager
64ParkMargaretSales Support Agent
73PeacockJaneSales Support Agent
\n", "
" ], "text/plain": [ " EmployeeId LastName FirstName Title\n", "0 1 Adams Andrew General Manager\n", "1 8 Callahan Laura IT Staff\n", "2 2 Edwards Nancy Sales Manager\n", "3 5 Johnson Steve Sales Support Agent\n", "4 7 King Robert IT Staff\n", "5 6 Mitchell Michael IT Manager\n", "6 4 Park Margaret Sales Support Agent\n", "7 3 Peacock Jane Sales Support Agent" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Ordenar los registros: varias columnas\n", "query = \"\"\"\n", "SELECT EmployeeId,LastName, FirstName , Title FROM employees ORDER BY LastName,Title\n", "\"\"\"\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 26, "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", "
EmployeeIdLastNameFirstNameTitle
03PeacockJaneSales Support Agent
14ParkMargaretSales Support Agent
26MitchellMichaelIT Manager
37KingRobertIT Staff
45JohnsonSteveSales Support Agent
52EdwardsNancySales Manager
68CallahanLauraIT Staff
71AdamsAndrewGeneral Manager
\n", "
" ], "text/plain": [ " EmployeeId LastName FirstName Title\n", "0 3 Peacock Jane Sales Support Agent\n", "1 4 Park Margaret Sales Support Agent\n", "2 6 Mitchell Michael IT Manager\n", "3 7 King Robert IT Staff\n", "4 5 Johnson Steve Sales Support Agent\n", "5 2 Edwards Nancy Sales Manager\n", "6 8 Callahan Laura IT Staff\n", "7 1 Adams Andrew General Manager" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Ordenar los registros: especificar el orden \n", "query = \"\"\"\n", "SELECT EmployeeId,LastName, FirstName , Title FROM employees ORDER BY LastName DESC , Title ASC;\n", "\"\"\"\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 27, "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", "
EmployeeIdLastNameFirstNameTitleReportsToBirthDateHireDateAddressCityStateCountryPostalCodePhoneFaxEmail
01AdamsAndrewGeneral ManagerNaN1962-02-18 00:00:002002-08-14 00:00:0011120 Jasper Ave NWEdmontonABCanadaT5K 2N1+1 (780) 428-9482+1 (780) 428-3457andrew@chinookcorp.com
12EdwardsNancySales Manager1.01958-12-08 00:00:002002-05-01 00:00:00825 8 Ave SWCalgaryABCanadaT2P 2T3+1 (403) 262-3443+1 (403) 262-3322nancy@chinookcorp.com
23PeacockJaneSales Support Agent2.01973-08-29 00:00:002002-04-01 00:00:001111 6 Ave SWCalgaryABCanadaT2P 5M5+1 (403) 262-3443+1 (403) 262-6712jane@chinookcorp.com
34ParkMargaretSales Support Agent2.01947-09-19 00:00:002003-05-03 00:00:00683 10 Street SWCalgaryABCanadaT2P 5G3+1 (403) 263-4423+1 (403) 263-4289margaret@chinookcorp.com
45JohnsonSteveSales Support Agent2.01965-03-03 00:00:002003-10-17 00:00:007727B 41 AveCalgaryABCanadaT3B 1Y71 (780) 836-99871 (780) 836-9543steve@chinookcorp.com
56MitchellMichaelIT Manager1.01973-07-01 00:00:002003-10-17 00:00:005827 Bowness Road NWCalgaryABCanadaT3B 0C5+1 (403) 246-9887+1 (403) 246-9899michael@chinookcorp.com
67KingRobertIT Staff6.01970-05-29 00:00:002004-01-02 00:00:00590 Columbia Boulevard WestLethbridgeABCanadaT1K 5N8+1 (403) 456-9986+1 (403) 456-8485robert@chinookcorp.com
78CallahanLauraIT Staff6.01968-01-09 00:00:002004-03-04 00:00:00923 7 ST NWLethbridgeABCanadaT1H 1Y8+1 (403) 467-3351+1 (403) 467-8772laura@chinookcorp.com
\n", "
" ], "text/plain": [ " EmployeeId LastName FirstName Title ReportsTo \\\n", "0 1 Adams Andrew General Manager NaN \n", "1 2 Edwards Nancy Sales Manager 1.0 \n", "2 3 Peacock Jane Sales Support Agent 2.0 \n", "3 4 Park Margaret Sales Support Agent 2.0 \n", "4 5 Johnson Steve Sales Support Agent 2.0 \n", "5 6 Mitchell Michael IT Manager 1.0 \n", "6 7 King Robert IT Staff 6.0 \n", "7 8 Callahan Laura IT Staff 6.0 \n", "\n", " BirthDate HireDate Address \\\n", "0 1962-02-18 00:00:00 2002-08-14 00:00:00 11120 Jasper Ave NW \n", "1 1958-12-08 00:00:00 2002-05-01 00:00:00 825 8 Ave SW \n", "2 1973-08-29 00:00:00 2002-04-01 00:00:00 1111 6 Ave SW \n", "3 1947-09-19 00:00:00 2003-05-03 00:00:00 683 10 Street SW \n", "4 1965-03-03 00:00:00 2003-10-17 00:00:00 7727B 41 Ave \n", "5 1973-07-01 00:00:00 2003-10-17 00:00:00 5827 Bowness Road NW \n", "6 1970-05-29 00:00:00 2004-01-02 00:00:00 590 Columbia Boulevard West \n", "7 1968-01-09 00:00:00 2004-03-04 00:00:00 923 7 ST NW \n", "\n", " City State Country PostalCode Phone Fax \\\n", "0 Edmonton AB Canada T5K 2N1 +1 (780) 428-9482 +1 (780) 428-3457 \n", "1 Calgary AB Canada T2P 2T3 +1 (403) 262-3443 +1 (403) 262-3322 \n", "2 Calgary AB Canada T2P 5M5 +1 (403) 262-3443 +1 (403) 262-6712 \n", "3 Calgary AB Canada T2P 5G3 +1 (403) 263-4423 +1 (403) 263-4289 \n", "4 Calgary AB Canada T3B 1Y7 1 (780) 836-9987 1 (780) 836-9543 \n", "5 Calgary AB Canada T3B 0C5 +1 (403) 246-9887 +1 (403) 246-9899 \n", "6 Lethbridge AB Canada T1K 5N8 +1 (403) 456-9986 +1 (403) 456-8485 \n", "7 Lethbridge AB Canada T1H 1Y8 +1 (403) 467-3351 +1 (403) 467-8772 \n", "\n", " Email \n", "0 andrew@chinookcorp.com \n", "1 nancy@chinookcorp.com \n", "2 jane@chinookcorp.com \n", "3 margaret@chinookcorp.com \n", "4 steve@chinookcorp.com \n", "5 michael@chinookcorp.com \n", "6 robert@chinookcorp.com \n", "7 laura@chinookcorp.com " ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# operador *\n", "query = \"\"\"\n", "SELECT * FROM employees;\n", "\"\"\"\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 30, "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", "
LastName
0Adams
1Edwards
2Peacock
3Park
4Johnson
\n", "
" ], "text/plain": [ " LastName\n", "0 Adams\n", "1 Edwards\n", "2 Peacock\n", "3 Park\n", "4 Johnson" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# operador LIMIT\n", "query = \"\"\"\n", "SELECT LastName FROM employees LIMIT 5;\n", "\"\"\"\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 31, "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", "
LastName
0Adams
1Edwards
2Peacock
3Park
4Johnson
5Mitchell
6King
7Callahan
\n", "
" ], "text/plain": [ " LastName\n", "0 Adams\n", "1 Edwards\n", "2 Peacock\n", "3 Park\n", "4 Johnson\n", "5 Mitchell\n", "6 King\n", "7 Callahan" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# operador DISTINCT\n", "query = \"\"\"\n", "SELECT DISTINCT LastName FROM employees \n", "\"\"\"\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 43, "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", "
EmployeeIdLastNameFirstNameTitleReportsToBirthDateHireDateAddressCityStateCountryPostalCodePhoneFaxEmail
03PeacockJaneSales Support Agent21973-08-29 00:00:002002-04-01 00:00:001111 6 Ave SWCalgaryABCanadaT2P 5M5+1 (403) 262-3443+1 (403) 262-6712jane@chinookcorp.com
14ParkMargaretSales Support Agent21947-09-19 00:00:002003-05-03 00:00:00683 10 Street SWCalgaryABCanadaT2P 5G3+1 (403) 263-4423+1 (403) 263-4289margaret@chinookcorp.com
25JohnsonSteveSales Support Agent21965-03-03 00:00:002003-10-17 00:00:007727B 41 AveCalgaryABCanadaT3B 1Y71 (780) 836-99871 (780) 836-9543steve@chinookcorp.com
\n", "
" ], "text/plain": [ " EmployeeId LastName FirstName Title ReportsTo \\\n", "0 3 Peacock Jane Sales Support Agent 2 \n", "1 4 Park Margaret Sales Support Agent 2 \n", "2 5 Johnson Steve Sales Support Agent 2 \n", "\n", " BirthDate HireDate Address City State \\\n", "0 1973-08-29 00:00:00 2002-04-01 00:00:00 1111 6 Ave SW Calgary AB \n", "1 1947-09-19 00:00:00 2003-05-03 00:00:00 683 10 Street SW Calgary AB \n", "2 1965-03-03 00:00:00 2003-10-17 00:00:00 7727B 41 Ave Calgary AB \n", "\n", " Country PostalCode Phone Fax \\\n", "0 Canada T2P 5M5 +1 (403) 262-3443 +1 (403) 262-6712 \n", "1 Canada T2P 5G3 +1 (403) 263-4423 +1 (403) 263-4289 \n", "2 Canada T3B 1Y7 1 (780) 836-9987 1 (780) 836-9543 \n", "\n", " Email \n", "0 jane@chinookcorp.com \n", "1 margaret@chinookcorp.com \n", "2 steve@chinookcorp.com " ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# operadores logicos\n", "\n", "query1 = \"\"\"\n", "SELECT * FROM employees WHERE ReportsTo>1 AND ReportsTo<4\n", "\"\"\"\n", "\n", "query2 = \"\"\"\n", "SELECT * FROM employees WHERE ReportsTo>1 OR City='Calgary'\n", "\"\"\"\n", "\n", "query3 = \"\"\"\n", "SELECT * FROM employees WHERE NOT City = 'Calgary'\n", "\"\"\"\n", "\n", "consulta(query1,conn)" ] }, { "cell_type": "code", "execution_count": 45, "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", "
EmployeeIdLastNameFirstNameTitleReportsToBirthDateHireDateAddressCityStateCountryPostalCodePhoneFaxEmail
02EdwardsNancySales Manager11958-12-08 00:00:002002-05-01 00:00:00825 8 Ave SWCalgaryABCanadaT2P 2T3+1 (403) 262-3443+1 (403) 262-3322nancy@chinookcorp.com
16MitchellMichaelIT Manager11973-07-01 00:00:002003-10-17 00:00:005827 Bowness Road NWCalgaryABCanadaT3B 0C5+1 (403) 246-9887+1 (403) 246-9899michael@chinookcorp.com
23PeacockJaneSales Support Agent21973-08-29 00:00:002002-04-01 00:00:001111 6 Ave SWCalgaryABCanadaT2P 5M5+1 (403) 262-3443+1 (403) 262-6712jane@chinookcorp.com
34ParkMargaretSales Support Agent21947-09-19 00:00:002003-05-03 00:00:00683 10 Street SWCalgaryABCanadaT2P 5G3+1 (403) 263-4423+1 (403) 263-4289margaret@chinookcorp.com
45JohnsonSteveSales Support Agent21965-03-03 00:00:002003-10-17 00:00:007727B 41 AveCalgaryABCanadaT3B 1Y71 (780) 836-99871 (780) 836-9543steve@chinookcorp.com
\n", "
" ], "text/plain": [ " EmployeeId LastName FirstName Title ReportsTo \\\n", "0 2 Edwards Nancy Sales Manager 1 \n", "1 6 Mitchell Michael IT Manager 1 \n", "2 3 Peacock Jane Sales Support Agent 2 \n", "3 4 Park Margaret Sales Support Agent 2 \n", "4 5 Johnson Steve Sales Support Agent 2 \n", "\n", " BirthDate HireDate Address City \\\n", "0 1958-12-08 00:00:00 2002-05-01 00:00:00 825 8 Ave SW Calgary \n", "1 1973-07-01 00:00:00 2003-10-17 00:00:00 5827 Bowness Road NW Calgary \n", "2 1973-08-29 00:00:00 2002-04-01 00:00:00 1111 6 Ave SW Calgary \n", "3 1947-09-19 00:00:00 2003-05-03 00:00:00 683 10 Street SW Calgary \n", "4 1965-03-03 00:00:00 2003-10-17 00:00:00 7727B 41 Ave Calgary \n", "\n", " State Country PostalCode Phone Fax \\\n", "0 AB Canada T2P 2T3 +1 (403) 262-3443 +1 (403) 262-3322 \n", "1 AB Canada T3B 0C5 +1 (403) 246-9887 +1 (403) 246-9899 \n", "2 AB Canada T2P 5M5 +1 (403) 262-3443 +1 (403) 262-6712 \n", "3 AB Canada T2P 5G3 +1 (403) 263-4423 +1 (403) 263-4289 \n", "4 AB Canada T3B 1Y7 1 (780) 836-9987 1 (780) 836-9543 \n", "\n", " Email \n", "0 nancy@chinookcorp.com \n", "1 michael@chinookcorp.com \n", "2 jane@chinookcorp.com \n", "3 margaret@chinookcorp.com \n", "4 steve@chinookcorp.com " ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Operador BETWEEN\n", "query = \"\"\"\n", "SELECT * FROM employees WHERE ReportsTo BETWEEN 1 AND 4\n", "\"\"\"\n", "\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 49, "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", "
EmployeeIdLastNameFirstNameTitleReportsToBirthDateHireDateAddressCityStateCountryPostalCodePhoneFaxEmail
02EdwardsNancySales Manager11958-12-08 00:00:002002-05-01 00:00:00825 8 Ave SWCalgaryABCanadaT2P 2T3+1 (403) 262-3443+1 (403) 262-3322nancy@chinookcorp.com
13PeacockJaneSales Support Agent21973-08-29 00:00:002002-04-01 00:00:001111 6 Ave SWCalgaryABCanadaT2P 5M5+1 (403) 262-3443+1 (403) 262-6712jane@chinookcorp.com
24ParkMargaretSales Support Agent21947-09-19 00:00:002003-05-03 00:00:00683 10 Street SWCalgaryABCanadaT2P 5G3+1 (403) 263-4423+1 (403) 263-4289margaret@chinookcorp.com
\n", "
" ], "text/plain": [ " EmployeeId LastName FirstName Title ReportsTo \\\n", "0 2 Edwards Nancy Sales Manager 1 \n", "1 3 Peacock Jane Sales Support Agent 2 \n", "2 4 Park Margaret Sales Support Agent 2 \n", "\n", " BirthDate HireDate Address City State \\\n", "0 1958-12-08 00:00:00 2002-05-01 00:00:00 825 8 Ave SW Calgary AB \n", "1 1973-08-29 00:00:00 2002-04-01 00:00:00 1111 6 Ave SW Calgary AB \n", "2 1947-09-19 00:00:00 2003-05-03 00:00:00 683 10 Street SW Calgary AB \n", "\n", " Country PostalCode Phone Fax \\\n", "0 Canada T2P 2T3 +1 (403) 262-3443 +1 (403) 262-3322 \n", "1 Canada T2P 5M5 +1 (403) 262-3443 +1 (403) 262-6712 \n", "2 Canada T2P 5G3 +1 (403) 263-4423 +1 (403) 263-4289 \n", "\n", " Email \n", "0 nancy@chinookcorp.com \n", "1 jane@chinookcorp.com \n", "2 margaret@chinookcorp.com " ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Operador LIKE\n", "\n", "query = \"\"\"\n", "SELECT * FROM employees WHERE PostalCode LIKE '%T2P%'\n", "\"\"\"\n", "\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 50, "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", "
EmployeeIdLastNameFirstNameTitleReportsToBirthDateHireDateAddressCityStateCountryPostalCodePhoneFaxEmail
02EdwardsNancySales Manager11958-12-08 00:00:002002-05-01 00:00:00825 8 Ave SWCalgaryABCanadaT2P 2T3+1 (403) 262-3443+1 (403) 262-3322nancy@chinookcorp.com
16MitchellMichaelIT Manager11973-07-01 00:00:002003-10-17 00:00:005827 Bowness Road NWCalgaryABCanadaT3B 0C5+1 (403) 246-9887+1 (403) 246-9899michael@chinookcorp.com
27KingRobertIT Staff61970-05-29 00:00:002004-01-02 00:00:00590 Columbia Boulevard WestLethbridgeABCanadaT1K 5N8+1 (403) 456-9986+1 (403) 456-8485robert@chinookcorp.com
38CallahanLauraIT Staff61968-01-09 00:00:002004-03-04 00:00:00923 7 ST NWLethbridgeABCanadaT1H 1Y8+1 (403) 467-3351+1 (403) 467-8772laura@chinookcorp.com
\n", "
" ], "text/plain": [ " EmployeeId LastName FirstName Title ReportsTo \\\n", "0 2 Edwards Nancy Sales Manager 1 \n", "1 6 Mitchell Michael IT Manager 1 \n", "2 7 King Robert IT Staff 6 \n", "3 8 Callahan Laura IT Staff 6 \n", "\n", " BirthDate HireDate Address \\\n", "0 1958-12-08 00:00:00 2002-05-01 00:00:00 825 8 Ave SW \n", "1 1973-07-01 00:00:00 2003-10-17 00:00:00 5827 Bowness Road NW \n", "2 1970-05-29 00:00:00 2004-01-02 00:00:00 590 Columbia Boulevard West \n", "3 1968-01-09 00:00:00 2004-03-04 00:00:00 923 7 ST NW \n", "\n", " City State Country PostalCode Phone Fax \\\n", "0 Calgary AB Canada T2P 2T3 +1 (403) 262-3443 +1 (403) 262-3322 \n", "1 Calgary AB Canada T3B 0C5 +1 (403) 246-9887 +1 (403) 246-9899 \n", "2 Lethbridge AB Canada T1K 5N8 +1 (403) 456-9986 +1 (403) 456-8485 \n", "3 Lethbridge AB Canada T1H 1Y8 +1 (403) 467-3351 +1 (403) 467-8772 \n", "\n", " Email \n", "0 nancy@chinookcorp.com \n", "1 michael@chinookcorp.com \n", "2 robert@chinookcorp.com \n", "3 laura@chinookcorp.com " ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Operador IN\n", "\n", "query = \"\"\"\n", "SELECT * FROM employees WHERE ReportsTo IN (1,6)\n", "\"\"\"\n", "\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 56, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SUM(ReportsTo)COUNT(ReportsTo)AVG(ReportsTo)
02072.857143
\n", "
" ], "text/plain": [ " SUM(ReportsTo) COUNT(ReportsTo) AVG(ReportsTo)\n", "0 20 7 2.857143" ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Operadores matematicos\n", "\n", "query = \"\"\"\n", "SELECT SUM(ReportsTo),COUNT(ReportsTo),AVG(ReportsTo) FROM employees \n", "\"\"\"\n", "\n", "consulta(query,conn)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Cosultas mediante GROUP BY \n", "Combina los registros con valores idénticos, en la lista de campos especificados, en un único registro:\n", "\n", "**Ejemplo**: En la base datos chinook existe la tabla employees. ¿Cómo agrupar por la columna Title?" ] }, { "cell_type": "code", "execution_count": 58, "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", "
TitleSUM(ReportsTo)COUNT(ReportsTo)AVG(ReportsTo)
0General ManagerNaN0NaN
1IT Manager1.011.0
2IT Staff12.026.0
3Sales Manager1.011.0
4Sales Support Agent6.032.0
\n", "
" ], "text/plain": [ " Title SUM(ReportsTo) COUNT(ReportsTo) AVG(ReportsTo)\n", "0 General Manager NaN 0 NaN\n", "1 IT Manager 1.0 1 1.0\n", "2 IT Staff 12.0 2 6.0\n", "3 Sales Manager 1.0 1 1.0\n", "4 Sales Support Agent 6.0 3 2.0" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = \"\"\"\n", "SELECT Title, SUM(ReportsTo),COUNT(ReportsTo),AVG(ReportsTo) FROM employees GROUP BY Title\n", "\"\"\"\n", "\n", "consulta(query,conn)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Cosultas mediante JOIN\n", "La sentencia SQL JOIN se utiliza para relacionar varias tablas. Nos permitirá obtener un listado de los campos que tienen coincidencias en ambas tablas.\n", "\n", "**Ejemplo**: En la base datos chinook existen las tablas albums y artists. ¿Cómo agregar el nombre del artista a la tabla de álbumes?\n", "\n" ] }, { "cell_type": "code", "execution_count": 66, "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", "
ArtistIdNameAlbumIdTitleArtistId
01AC/DC1For Those About To Rock We Salute You1
12Accept2Balls to the Wall2
22Accept3Restless and Wild2
31AC/DC4Let There Be Rock1
43Aerosmith5Big Ones3
..................
342226Eugene Ormandy343Respighi:Pines of Rome226
343272Emerson String Quartet344Schubert: The Late String Quartets & String Qu...272
344273C. Monteverdi, Nigel Rogers - Chiaroscuro; Lon...345Monteverdi: L'Orfeo273
345274Nash Ensemble346Mozart: Chamber Music274
346275Philip Glass Ensemble347Koyaanisqatsi (Soundtrack from the Motion Pict...275
\n", "

347 rows × 5 columns

\n", "
" ], "text/plain": [ " ArtistId Name AlbumId \\\n", "0 1 AC/DC 1 \n", "1 2 Accept 2 \n", "2 2 Accept 3 \n", "3 1 AC/DC 4 \n", "4 3 Aerosmith 5 \n", ".. ... ... ... \n", "342 226 Eugene Ormandy 343 \n", "343 272 Emerson String Quartet 344 \n", "344 273 C. Monteverdi, Nigel Rogers - Chiaroscuro; Lon... 345 \n", "345 274 Nash Ensemble 346 \n", "346 275 Philip Glass Ensemble 347 \n", "\n", " Title ArtistId \n", "0 For Those About To Rock We Salute You 1 \n", "1 Balls to the Wall 2 \n", "2 Restless and Wild 2 \n", "3 Let There Be Rock 1 \n", "4 Big Ones 3 \n", ".. ... ... \n", "342 Respighi:Pines of Rome 226 \n", "343 Schubert: The Late String Quartets & String Qu... 272 \n", "344 Monteverdi: L'Orfeo 273 \n", "345 Mozart: Chamber Music 274 \n", "346 Koyaanisqatsi (Soundtrack from the Motion Pict... 275 \n", "\n", "[347 rows x 5 columns]" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# EJEMPLO 01\n", "query = \"\"\"\n", "SELECT * FROM artists JOIN albums ON artists.ArtistId=albums.ArtistId\n", "\"\"\"\n", "\n", "consulta(query,conn)" ] }, { "cell_type": "code", "execution_count": 75, "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", "
ArtistIdNameAlbumIdTitle
01AC/DC1For Those About To Rock We Salute You
12Accept2Balls to the Wall
22Accept3Restless and Wild
31AC/DC4Let There Be Rock
43Aerosmith5Big Ones
...............
342226Eugene Ormandy343Respighi:Pines of Rome
343272Emerson String Quartet344Schubert: The Late String Quartets & String Qu...
344273C. Monteverdi, Nigel Rogers - Chiaroscuro; Lon...345Monteverdi: L'Orfeo
345274Nash Ensemble346Mozart: Chamber Music
346275Philip Glass Ensemble347Koyaanisqatsi (Soundtrack from the Motion Pict...
\n", "

347 rows × 4 columns

\n", "
" ], "text/plain": [ " ArtistId Name AlbumId \\\n", "0 1 AC/DC 1 \n", "1 2 Accept 2 \n", "2 2 Accept 3 \n", "3 1 AC/DC 4 \n", "4 3 Aerosmith 5 \n", ".. ... ... ... \n", "342 226 Eugene Ormandy 343 \n", "343 272 Emerson String Quartet 344 \n", "344 273 C. Monteverdi, Nigel Rogers - Chiaroscuro; Lon... 345 \n", "345 274 Nash Ensemble 346 \n", "346 275 Philip Glass Ensemble 347 \n", "\n", " Title \n", "0 For Those About To Rock We Salute You \n", "1 Balls to the Wall \n", "2 Restless and Wild \n", "3 Let There Be Rock \n", "4 Big Ones \n", ".. ... \n", "342 Respighi:Pines of Rome \n", "343 Schubert: The Late String Quartets & String Qu... \n", "344 Monteverdi: L'Orfeo \n", "345 Mozart: Chamber Music \n", "346 Koyaanisqatsi (Soundtrack from the Motion Pict... \n", "\n", "[347 rows x 4 columns]" ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# EJEMPLO 02\n", "query = \"\"\"\n", "SELECT A.ArtistId, A.Name, B.AlbumId,B.Title FROM artists AS A JOIN albums AS B ON A.ArtistId=B.ArtistId\n", "\"\"\"\n", "\n", "consulta(query,conn)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Referencias\n", "\n", "1. [SQL Tutorial](https://www.w3schools.com/sql/default.asp)" ] } ], "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.7.3" } }, "nbformat": 4, "nbformat_minor": 4 }