{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Consultas de Selección: Muestras aleatorias. Common Table Expressions.\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": [ "## Muestras aleatorias de Filas\n", "\n", "En diversas ocasiones no es útil trabajar con todo el universo de datos de una tabla. Se puede tomar una muestra aleatoria de la misma mediante SQL." ] }, { "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", "
count
01000
\n", "
" ], "text/plain": [ " count\n", "0 1000" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Consultar cuántos registros se tienen en la tabla\n", "\n", "sql = '''\n", "SELECT count(*) FROM film;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)" ] }, { "cell_type": "code", "execution_count": 3, "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", "
film_idtitledescriptionrelease_yearlanguage_idrental_durationrental_ratelengthreplacement_costratinglast_updatespecial_featuresfulltext
0399Happiness UnitedA Action-Packed Panorama of a Husband And a Fe...2006162.9910023.99G2013-05-26 14:50:58.951[Deleted Scenes]'action':5 'action-pack':4 'ancient':21 'femin...
1662Paths ControlA Astounding Documentary of a Butler And a Cat...2006134.991189.99PG2013-05-26 14:50:58.951[Trailers, Behind the Scenes]'ancient':18 'astound':4 'butler':8 'cat':11 '...
2252Dream PickupA Epic Display of a Car And a Composer who mus...2006162.9913518.99PG2013-05-26 14:50:58.951[Trailers, Commentaries, Behind the Scenes]'car':8 'compos':11 'display':5 'dream':1 'epi...
3200Curtain VideotapeA Boring Reflection of a Dentist And a Mad Cow...2006170.9913327.99PG-132013-05-26 14:50:58.951[Trailers, Commentaries, Deleted Scenes, Behin...'agent':18 'bore':4 'chase':15 'cow':12 'curta...
4681Pirates RoxanneA Stunning Drama of a Woman And a Lumberjack w...2006140.9910020.99PG2013-05-26 14:50:58.951[Commentaries, Deleted Scenes]'canadian':20 'drama':5 'lumberjack':11 'must'...
5518Liaisons SweetA Boring Drama of a A Shark And a Explorer who...2006154.9914015.99PG2013-05-26 14:50:58.951[Commentaries, Behind the Scenes]'bore':4 'canadian':20 'drama':5 'explor':12 '...
6677Pianist OutfieldA Intrepid Story of a Boy And a Technical Writ...2006160.9913625.99NC-172013-05-26 14:50:58.951[Trailers, Commentaries, Deleted Scenes]'boy':8 'intrepid':4 'lumberjack':17 'monaster...
7879Telegraph VoyageA Fateful Yarn of a Husband And a Dog who must...2006134.9914820.99PG2013-05-26 14:50:58.951[Commentaries]'battl':14 'boat':20 'dog':11 'fate':4 'husban...
8591Monsoon CauseA Astounding Tale of a Crocodile And a Car who...2006164.9918220.99PG2013-05-26 14:50:58.951[Commentaries, Behind the Scenes]'astound':4 'boat':21 'car':11 'caus':2 'croco...
9704Pure RunnerA Thoughtful Documentary of a Student And a Ma...2006132.9912125.99NC-172013-05-26 14:50:58.951[Trailers, Deleted Scenes]'challeng':14 'documentari':5 'madman':11 'man...
\n", "
" ], "text/plain": [ " film_id title \\\n", "0 399 Happiness United \n", "1 662 Paths Control \n", "2 252 Dream Pickup \n", "3 200 Curtain Videotape \n", "4 681 Pirates Roxanne \n", "5 518 Liaisons Sweet \n", "6 677 Pianist Outfield \n", "7 879 Telegraph Voyage \n", "8 591 Monsoon Cause \n", "9 704 Pure Runner \n", "\n", " description release_year \\\n", "0 A Action-Packed Panorama of a Husband And a Fe... 2006 \n", "1 A Astounding Documentary of a Butler And a Cat... 2006 \n", "2 A Epic Display of a Car And a Composer who mus... 2006 \n", "3 A Boring Reflection of a Dentist And a Mad Cow... 2006 \n", "4 A Stunning Drama of a Woman And a Lumberjack w... 2006 \n", "5 A Boring Drama of a A Shark And a Explorer who... 2006 \n", "6 A Intrepid Story of a Boy And a Technical Writ... 2006 \n", "7 A Fateful Yarn of a Husband And a Dog who must... 2006 \n", "8 A Astounding Tale of a Crocodile And a Car who... 2006 \n", "9 A Thoughtful Documentary of a Student And a Ma... 2006 \n", "\n", " language_id rental_duration rental_rate length replacement_cost rating \\\n", "0 1 6 2.99 100 23.99 G \n", "1 1 3 4.99 118 9.99 PG \n", "2 1 6 2.99 135 18.99 PG \n", "3 1 7 0.99 133 27.99 PG-13 \n", "4 1 4 0.99 100 20.99 PG \n", "5 1 5 4.99 140 15.99 PG \n", "6 1 6 0.99 136 25.99 NC-17 \n", "7 1 3 4.99 148 20.99 PG \n", "8 1 6 4.99 182 20.99 PG \n", "9 1 3 2.99 121 25.99 NC-17 \n", "\n", " last_update special_features \\\n", "0 2013-05-26 14:50:58.951 [Deleted Scenes] \n", "1 2013-05-26 14:50:58.951 [Trailers, Behind the Scenes] \n", "2 2013-05-26 14:50:58.951 [Trailers, Commentaries, Behind the Scenes] \n", "3 2013-05-26 14:50:58.951 [Trailers, Commentaries, Deleted Scenes, Behin... \n", "4 2013-05-26 14:50:58.951 [Commentaries, Deleted Scenes] \n", "5 2013-05-26 14:50:58.951 [Commentaries, Behind the Scenes] \n", "6 2013-05-26 14:50:58.951 [Trailers, Commentaries, Deleted Scenes] \n", "7 2013-05-26 14:50:58.951 [Commentaries] \n", "8 2013-05-26 14:50:58.951 [Commentaries, Behind the Scenes] \n", "9 2013-05-26 14:50:58.951 [Trailers, Deleted Scenes] \n", "\n", " fulltext \n", "0 'action':5 'action-pack':4 'ancient':21 'femin... \n", "1 'ancient':18 'astound':4 'butler':8 'cat':11 '... \n", "2 'car':8 'compos':11 'display':5 'dream':1 'epi... \n", "3 'agent':18 'bore':4 'chase':15 'cow':12 'curta... \n", "4 'canadian':20 'drama':5 'lumberjack':11 'must'... \n", "5 'bore':4 'canadian':20 'drama':5 'explor':12 '... \n", "6 'boy':8 'intrepid':4 'lumberjack':17 'monaster... \n", "7 'battl':14 'boat':20 'dog':11 'fate':4 'husban... \n", "8 'astound':4 'boat':21 'car':11 'caus':2 'croco... \n", "9 'challeng':14 'documentari':5 'madman':11 'man... " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Tomar 10 registros al azar\n", "\n", "sql = '''\n", "SELECT * FROM film order by RANDOM() limit 10;\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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_idtitledescriptionrelease_yearlanguage_idrental_durationrental_ratelengthreplacement_costratinglast_updatespecial_featuresfulltext
0430Hook ChariotsA Insightful Story of a Boy And a Dog who must...2006170.994923.99G2013-05-26 14:50:58.951[Trailers, Commentaries, Behind the Scenes]'australia':18 'boy':8,16 'chariot':2 'dog':11...
1450Idols SnatchersA Insightful Drama of a Car And a Composer who...2006152.998429.99NC-172013-05-26 14:50:58.951[Trailers]'car':8 'compos':11 'drama':5 'fight':14 'idol...
2431Hoosiers BirdcageA Astounding Display of a Explorer And a Boat ...2006132.9917612.99G2013-05-26 14:50:58.951[Trailers, Commentaries, Deleted Scenes]'astound':4 'birdcag':2 'boat':11 'car':16 'di...
3432Hope TootsieA Amazing Documentary of a Student And a Sumo ...2006142.9913922.99NC-172013-05-26 14:50:58.951[Trailers, Deleted Scenes, Behind the Scenes]'amaz':4 'documentari':5 'hope':1 'must':14 'o...
4433Horn WorkingA Stunning Display of a Mad Scientist And a Te...2006142.999523.99PG2013-05-26 14:50:58.951[Trailers]'display':5 'horn':1 'mad':8 'monkey':18 'must...
5434Horror ReignA Touching Documentary of a A Shark And a Car ...2006130.9913925.99R2013-05-26 14:50:58.951[Deleted Scenes, Behind the Scenes]'build':15 'car':12 'documentari':5 'horror':1...
6435Hotel HappinessA Thrilling Yarn of a Pastry Chef And a A Shar...2006164.9918128.99PG-132013-05-26 14:50:58.951[Behind the Scenes]'challeng':16 'chef':9 'happi':2 'hotel':1 'ma...
7436Hours RageA Fateful Story of a Explorer And a Feminist w...2006140.9912214.99NC-172013-05-26 14:50:58.951[Trailers, Deleted Scenes]'explor':8 'fate':4 'feminist':11 'georgia':20...
8437House DynamiteA Taut Story of a Pioneer And a Squirrel who m...2006172.9910913.99R2013-05-26 14:50:58.951[Commentaries, Deleted Scenes, Behind the Scenes]'battl':14 'dynamit':2 'georgia':19 'hous':1 '...
9438Human GraffitiA Beautiful Reflection of a Womanizer And a Su...2006132.996822.99NC-172013-05-26 14:50:58.951[Trailers, Behind the Scenes]'administr':18 'beauti':4 'chase':15 'databas'...
10439Hunchback ImpossibleA Touching Yarn of a Frisbee And a Dentist who...2006144.9915128.99PG-132013-05-26 14:50:58.951[Trailers, Deleted Scenes]'ancient':18 'compos':16 'dentist':11 'fight':...
11440Hunger RoofA Unbelieveable Yarn of a Student And a Databa...2006160.9910521.99G2013-05-26 14:50:58.951[Deleted Scenes, Behind the Scenes]'abandon':20 'administr':12 'databas':11 'hung...
12441Hunter AlterA Emotional Drama of a Mad Cow And a Boat who ...2006152.9912521.99PG-132013-05-26 14:50:58.951[Commentaries, Deleted Scenes, Behind the Scenes]'agent':18 'alter':2 'boat':12 'cow':9 'drama'...
13442Hunting MusketeersA Thrilling Reflection of a Pioneer And a Dent...2006162.996524.99NC-172013-05-26 14:50:58.951[Trailers, Deleted Scenes]'abandon':19 'dentist':11 'hunt':1 'mine':20 '...
14443Hurricane AffairA Lacklusture Epistle of a Database Administra...2006162.994911.99PG2013-05-26 14:50:58.951[Trailers, Commentaries, Behind the Scenes]'abandon':20 'administr':9 'affair':2 'databas...
15444Hustler PartyA Emotional Reflection of a Sumo Wrestler And ...2006134.998322.99NC-172013-05-26 14:50:58.951[Trailers, Commentaries, Behind the Scenes]'conquer':15 'desert':21 'emot':4 'hustler':1 ...
16445Hyde DoctorA Fanciful Documentary of a Boy And a Woman wh...2006152.9910011.99G2013-05-26 14:50:58.951[Trailers, Deleted Scenes]'boat':20 'boy':8 'doctor':2 'documentari':5 '...
17446Hysterical GrailA Amazing Saga of a Madman And a Dentist who m...2006154.9915019.99PG2013-05-26 14:50:58.951[Trailers, Commentaries, Deleted Scenes]'amaz':4 'build':14 'car':16 'dentist':11 'gra...
18447Ice CrossingA Fast-Paced Tale of a Butler And a Moose who ...2006152.9913128.99R2013-05-26 14:50:58.951[Deleted Scenes]'butler':10 'cross':2 'fast':5 'fast-pac':4 'i...
19506Lady StageA Beautiful Character Study of a Woman And a M...2006144.996714.99PG2013-05-26 14:50:58.951[Trailers, Deleted Scenes, Behind the Scenes]'beauti':4 'boat':22 'charact':5 'explor':17 '...
20507Ladybugs ArmageddonA Fateful Reflection of a Dog And a Mad Scient...2006140.9911313.99NC-172013-05-26 14:50:58.951[Deleted Scenes]'armageddon':2 'dog':8 'fate':4 'ladybug':1 'm...
21508Lambs CincinattiA Insightful Story of a Man And a Feminist who...2006164.9914418.99PG-132013-05-26 14:50:58.951[Trailers, Behind the Scenes]'australia':18 'cincinatti':2 'compos':16 'fem...
22509Language CowboyA Epic Yarn of a Cat And a Madman who must Van...2006150.997826.99NC-172013-05-26 14:50:58.951[Trailers, Deleted Scenes]'abandon':19 'amus':20 'cat':8 'cowboy':2 'den...
23510Lawless VisionA Insightful Yarn of a Boy And a Sumo Wrestler...2006164.9918129.99G2013-05-26 14:50:58.951[Deleted Scenes, Behind the Scenes]'boy':8 'car':17 'insight':4 'lawless':1 'must...
24511Lawrence LoveA Fanciful Yarn of a Database Administrator An...2006170.9917523.99NC-172013-05-26 14:50:58.951[Commentaries, Deleted Scenes, Behind the Scenes]'administr':9 'berlin':20 'cow':13 'databas':8...
25512League HellfightersA Thoughtful Saga of a A Shark And a Monkey wh...2006154.9911025.99PG-132013-05-26 14:50:58.951[Trailers]'ancient':19 'china':20 'hellfight':2 'leagu':...
26513Leathernecks DwarfsA Fateful Reflection of a Dog And a Mad Cow wh...2006162.9915321.99PG-132013-05-26 14:50:58.951[Trailers, Commentaries, Behind the Scenes]'abandon':20 'cow':12 'dog':8 'dwarf':2 'fate'...
27514Lebowski SoldiersA Beautiful Epistle of a Secret Agent And a Pi...2006162.996917.99PG-132013-05-26 14:50:58.951[Commentaries, Deleted Scenes]'agent':9 'ancient':19 'astronaut':17 'beauti'...
28515Legally SecretaryA Astounding Tale of a A Shark And a Moose who...2006174.9911314.99PG2013-05-26 14:50:58.951[Trailers, Commentaries, Behind the Scenes]'astound':4 'desert':21 'legal':1 'meet':15 'm...
29516Legend JediA Awe-Inspiring Epistle of a Pioneer And a Stu...2006170.995918.99PG2013-05-26 14:50:58.951[Commentaries, Deleted Scenes]'awe':5 'awe-inspir':4 'crocodil':18 'epistl':...
30517Lesson CleopatraA Emotional Display of a Man And a Explorer wh...2006130.9916728.99NC-172013-05-26 14:50:58.951[Deleted Scenes, Behind the Scenes]'boy':16 'build':14 'cleopatra':2 'display':5 ...
31518Liaisons SweetA Boring Drama of a A Shark And a Explorer who...2006154.9914015.99PG2013-05-26 14:50:58.951[Commentaries, Behind the Scenes]'bore':4 'canadian':20 'drama':5 'explor':12 '...
32519Liberty MagnificentA Boring Drama of a Student And a Cat who must...2006132.9913827.99G2013-05-26 14:50:58.951[Commentaries, Deleted Scenes, Behind the Scenes]'baloon':20 'bore':4 'cat':11 'drama':5 'liber...
33520License WeekendA Insightful Story of a Man And a Husband who ...2006172.999128.99NC-172013-05-26 14:50:58.951[Deleted Scenes, Behind the Scenes]'husband':11 'insight':4 'licens':1 'madman':1...
34521Lies TreatmentA Fast-Paced Character Study of a Dentist And ...2006174.9914728.99NC-172013-05-26 14:50:58.951[Commentaries, Deleted Scenes, Behind the Scenes]'charact':7 'compos':19 'defeat':17 'dentist':...
35522Life TwistedA Thrilling Reflection of a Teacher And a Comp...2006142.991379.99NC-172013-05-26 14:50:58.951[Commentaries, Deleted Scenes]'compos':11 'find':14 'first':19 'life':1 'man...
36523Lights DeerA Unbelieveable Epistle of a Dog And a Woman w...2006170.9917421.99R2013-05-26 14:50:58.951[Commentaries]'confront':14 'deer':2 'dog':8 'epistl':5 'gul...
37560Mars RomanA Boring Drama of a Car And a Dog who must Suc...2006160.996221.99NC-172013-05-26 14:50:58.951[Commentaries, Deleted Scenes]'bore':4 'car':8 'dog':11 'drama':5 'georgia':...
38524Lion UncutA Intrepid Display of a Pastry Chef And a Cat ...2006160.995013.99PG2013-05-26 14:50:58.951[Trailers, Deleted Scenes]'ancient':20 'cat':12 'chef':9 'china':21 'dis...
39525Loathing LegallyA Boring Epistle of a Pioneer And a Mad Scient...2006140.9914029.99R2013-05-26 14:50:58.951[Deleted Scenes]'bore':4 'epistl':5 'escap':15 'frisbe':17 'gu...
40526Lock RearA Thoughtful Character Study of a Squirrel And...2006172.9912010.99R2013-05-26 14:50:58.951[Trailers, Commentaries]'ancient':20 'charact':5 'japan':21 'lock':1 '...
41527Lola AgentA Astounding Tale of a Mad Scientist And a Hus...2006144.998524.99PG2013-05-26 14:50:58.951[Trailers, Commentaries]'administr':18 'agent':2 'ancient':20 'astound...
42528Lolita WorldA Thrilling Drama of a Girl And a Robot who mu...2006142.9915525.99NC-172013-05-26 14:50:58.951[Trailers, Deleted Scenes]'abandon':19 'drama':5 'girl':8 'lolita':1 'mi...
43529Lonely ElephantA Intrepid Story of a Student And a Dog who mu...2006132.996712.99G2013-05-26 14:50:58.951[Trailers, Commentaries, Deleted Scenes, Behin...'challeng':14 'dog':11 'eleph':2 'explor':16 '...
44530Lord ArizonaA Action-Packed Display of a Frisbee And a Pas...2006152.9910827.99PG-132013-05-26 14:50:58.951[Trailers]'action':5 'action-pack':4 'arizona':2 'boat':...
45531Lose InchA Stunning Reflection of a Student And a Techn...2006130.9913718.99R2013-05-26 14:50:58.951[Trailers, Commentaries]'battl':15 'butler':17 'first':20 'inch':2 'lo...
46532Loser HustlerA Stunning Drama of a Robot And a Feminist who...2006154.998028.99PG2013-05-26 14:50:58.951[Deleted Scenes, Behind the Scenes]'butler':16 'drama':5 'feminist':11 'hustler':...
47533Lost BirdA Emotional Character Study of a Robot And a A...2006142.999821.99PG-132013-05-26 14:50:58.951[Deleted Scenes]'bird':2 'charact':5 'defeat':16 'emot':4 'los...
48534Louisiana HarryA Lacklusture Drama of a Girl And a Technical ...2006150.997018.99PG-132013-05-26 14:50:58.951[Trailers]'drama':5 'girl':8 'harri':2 'lacklustur':4 'l...
49535Love SuicidesA Brilliant Panorama of a Hunter And a Explore...2006160.9918121.99R2013-05-26 14:50:58.951[Trailers, Behind the Scenes]'abandon':19 'brilliant':4 'dentist':16 'explo...
\n", "
" ], "text/plain": [ " film_id title \\\n", "0 430 Hook Chariots \n", "1 450 Idols Snatchers \n", "2 431 Hoosiers Birdcage \n", "3 432 Hope Tootsie \n", "4 433 Horn Working \n", "5 434 Horror Reign \n", "6 435 Hotel Happiness \n", "7 436 Hours Rage \n", "8 437 House Dynamite \n", "9 438 Human Graffiti \n", "10 439 Hunchback Impossible \n", "11 440 Hunger Roof \n", "12 441 Hunter Alter \n", "13 442 Hunting Musketeers \n", "14 443 Hurricane Affair \n", "15 444 Hustler Party \n", "16 445 Hyde Doctor \n", "17 446 Hysterical Grail \n", "18 447 Ice Crossing \n", "19 506 Lady Stage \n", "20 507 Ladybugs Armageddon \n", "21 508 Lambs Cincinatti \n", "22 509 Language Cowboy \n", "23 510 Lawless Vision \n", "24 511 Lawrence Love \n", "25 512 League Hellfighters \n", "26 513 Leathernecks Dwarfs \n", "27 514 Lebowski Soldiers \n", "28 515 Legally Secretary \n", "29 516 Legend Jedi \n", "30 517 Lesson Cleopatra \n", "31 518 Liaisons Sweet \n", "32 519 Liberty Magnificent \n", "33 520 License Weekend \n", "34 521 Lies Treatment \n", "35 522 Life Twisted \n", "36 523 Lights Deer \n", "37 560 Mars Roman \n", "38 524 Lion Uncut \n", "39 525 Loathing Legally \n", "40 526 Lock Rear \n", "41 527 Lola Agent \n", "42 528 Lolita World \n", "43 529 Lonely Elephant \n", "44 530 Lord Arizona \n", "45 531 Lose Inch \n", "46 532 Loser Hustler \n", "47 533 Lost Bird \n", "48 534 Louisiana Harry \n", "49 535 Love Suicides \n", "\n", " description release_year \\\n", "0 A Insightful Story of a Boy And a Dog who must... 2006 \n", "1 A Insightful Drama of a Car And a Composer who... 2006 \n", "2 A Astounding Display of a Explorer And a Boat ... 2006 \n", "3 A Amazing Documentary of a Student And a Sumo ... 2006 \n", "4 A Stunning Display of a Mad Scientist And a Te... 2006 \n", "5 A Touching Documentary of a A Shark And a Car ... 2006 \n", "6 A Thrilling Yarn of a Pastry Chef And a A Shar... 2006 \n", "7 A Fateful Story of a Explorer And a Feminist w... 2006 \n", "8 A Taut Story of a Pioneer And a Squirrel who m... 2006 \n", "9 A Beautiful Reflection of a Womanizer And a Su... 2006 \n", "10 A Touching Yarn of a Frisbee And a Dentist who... 2006 \n", "11 A Unbelieveable Yarn of a Student And a Databa... 2006 \n", "12 A Emotional Drama of a Mad Cow And a Boat who ... 2006 \n", "13 A Thrilling Reflection of a Pioneer And a Dent... 2006 \n", "14 A Lacklusture Epistle of a Database Administra... 2006 \n", "15 A Emotional Reflection of a Sumo Wrestler And ... 2006 \n", "16 A Fanciful Documentary of a Boy And a Woman wh... 2006 \n", "17 A Amazing Saga of a Madman And a Dentist who m... 2006 \n", "18 A Fast-Paced Tale of a Butler And a Moose who ... 2006 \n", "19 A Beautiful Character Study of a Woman And a M... 2006 \n", "20 A Fateful Reflection of a Dog And a Mad Scient... 2006 \n", "21 A Insightful Story of a Man And a Feminist who... 2006 \n", "22 A Epic Yarn of a Cat And a Madman who must Van... 2006 \n", "23 A Insightful Yarn of a Boy And a Sumo Wrestler... 2006 \n", "24 A Fanciful Yarn of a Database Administrator An... 2006 \n", "25 A Thoughtful Saga of a A Shark And a Monkey wh... 2006 \n", "26 A Fateful Reflection of a Dog And a Mad Cow wh... 2006 \n", "27 A Beautiful Epistle of a Secret Agent And a Pi... 2006 \n", "28 A Astounding Tale of a A Shark And a Moose who... 2006 \n", "29 A Awe-Inspiring Epistle of a Pioneer And a Stu... 2006 \n", "30 A Emotional Display of a Man And a Explorer wh... 2006 \n", "31 A Boring Drama of a A Shark And a Explorer who... 2006 \n", "32 A Boring Drama of a Student And a Cat who must... 2006 \n", "33 A Insightful Story of a Man And a Husband who ... 2006 \n", "34 A Fast-Paced Character Study of a Dentist And ... 2006 \n", "35 A Thrilling Reflection of a Teacher And a Comp... 2006 \n", "36 A Unbelieveable Epistle of a Dog And a Woman w... 2006 \n", "37 A Boring Drama of a Car And a Dog who must Suc... 2006 \n", "38 A Intrepid Display of a Pastry Chef And a Cat ... 2006 \n", "39 A Boring Epistle of a Pioneer And a Mad Scient... 2006 \n", "40 A Thoughtful Character Study of a Squirrel And... 2006 \n", "41 A Astounding Tale of a Mad Scientist And a Hus... 2006 \n", "42 A Thrilling Drama of a Girl And a Robot who mu... 2006 \n", "43 A Intrepid Story of a Student And a Dog who mu... 2006 \n", "44 A Action-Packed Display of a Frisbee And a Pas... 2006 \n", "45 A Stunning Reflection of a Student And a Techn... 2006 \n", "46 A Stunning Drama of a Robot And a Feminist who... 2006 \n", "47 A Emotional Character Study of a Robot And a A... 2006 \n", "48 A Lacklusture Drama of a Girl And a Technical ... 2006 \n", "49 A Brilliant Panorama of a Hunter And a Explore... 2006 \n", "\n", " language_id rental_duration rental_rate length replacement_cost \\\n", "0 1 7 0.99 49 23.99 \n", "1 1 5 2.99 84 29.99 \n", "2 1 3 2.99 176 12.99 \n", "3 1 4 2.99 139 22.99 \n", "4 1 4 2.99 95 23.99 \n", "5 1 3 0.99 139 25.99 \n", "6 1 6 4.99 181 28.99 \n", "7 1 4 0.99 122 14.99 \n", "8 1 7 2.99 109 13.99 \n", "9 1 3 2.99 68 22.99 \n", "10 1 4 4.99 151 28.99 \n", "11 1 6 0.99 105 21.99 \n", "12 1 5 2.99 125 21.99 \n", "13 1 6 2.99 65 24.99 \n", "14 1 6 2.99 49 11.99 \n", "15 1 3 4.99 83 22.99 \n", "16 1 5 2.99 100 11.99 \n", "17 1 5 4.99 150 19.99 \n", "18 1 5 2.99 131 28.99 \n", "19 1 4 4.99 67 14.99 \n", "20 1 4 0.99 113 13.99 \n", "21 1 6 4.99 144 18.99 \n", "22 1 5 0.99 78 26.99 \n", "23 1 6 4.99 181 29.99 \n", "24 1 7 0.99 175 23.99 \n", "25 1 5 4.99 110 25.99 \n", "26 1 6 2.99 153 21.99 \n", "27 1 6 2.99 69 17.99 \n", "28 1 7 4.99 113 14.99 \n", "29 1 7 0.99 59 18.99 \n", "30 1 3 0.99 167 28.99 \n", "31 1 5 4.99 140 15.99 \n", "32 1 3 2.99 138 27.99 \n", "33 1 7 2.99 91 28.99 \n", "34 1 7 4.99 147 28.99 \n", "35 1 4 2.99 137 9.99 \n", "36 1 7 0.99 174 21.99 \n", "37 1 6 0.99 62 21.99 \n", "38 1 6 0.99 50 13.99 \n", "39 1 4 0.99 140 29.99 \n", "40 1 7 2.99 120 10.99 \n", "41 1 4 4.99 85 24.99 \n", "42 1 4 2.99 155 25.99 \n", "43 1 3 2.99 67 12.99 \n", "44 1 5 2.99 108 27.99 \n", "45 1 3 0.99 137 18.99 \n", "46 1 5 4.99 80 28.99 \n", "47 1 4 2.99 98 21.99 \n", "48 1 5 0.99 70 18.99 \n", "49 1 6 0.99 181 21.99 \n", "\n", " rating last_update \\\n", "0 G 2013-05-26 14:50:58.951 \n", "1 NC-17 2013-05-26 14:50:58.951 \n", "2 G 2013-05-26 14:50:58.951 \n", "3 NC-17 2013-05-26 14:50:58.951 \n", "4 PG 2013-05-26 14:50:58.951 \n", "5 R 2013-05-26 14:50:58.951 \n", "6 PG-13 2013-05-26 14:50:58.951 \n", "7 NC-17 2013-05-26 14:50:58.951 \n", "8 R 2013-05-26 14:50:58.951 \n", "9 NC-17 2013-05-26 14:50:58.951 \n", "10 PG-13 2013-05-26 14:50:58.951 \n", "11 G 2013-05-26 14:50:58.951 \n", "12 PG-13 2013-05-26 14:50:58.951 \n", "13 NC-17 2013-05-26 14:50:58.951 \n", "14 PG 2013-05-26 14:50:58.951 \n", "15 NC-17 2013-05-26 14:50:58.951 \n", "16 G 2013-05-26 14:50:58.951 \n", "17 PG 2013-05-26 14:50:58.951 \n", "18 R 2013-05-26 14:50:58.951 \n", "19 PG 2013-05-26 14:50:58.951 \n", "20 NC-17 2013-05-26 14:50:58.951 \n", "21 PG-13 2013-05-26 14:50:58.951 \n", "22 NC-17 2013-05-26 14:50:58.951 \n", "23 G 2013-05-26 14:50:58.951 \n", "24 NC-17 2013-05-26 14:50:58.951 \n", "25 PG-13 2013-05-26 14:50:58.951 \n", "26 PG-13 2013-05-26 14:50:58.951 \n", "27 PG-13 2013-05-26 14:50:58.951 \n", "28 PG 2013-05-26 14:50:58.951 \n", "29 PG 2013-05-26 14:50:58.951 \n", "30 NC-17 2013-05-26 14:50:58.951 \n", "31 PG 2013-05-26 14:50:58.951 \n", "32 G 2013-05-26 14:50:58.951 \n", "33 NC-17 2013-05-26 14:50:58.951 \n", "34 NC-17 2013-05-26 14:50:58.951 \n", "35 NC-17 2013-05-26 14:50:58.951 \n", "36 R 2013-05-26 14:50:58.951 \n", "37 NC-17 2013-05-26 14:50:58.951 \n", "38 PG 2013-05-26 14:50:58.951 \n", "39 R 2013-05-26 14:50:58.951 \n", "40 R 2013-05-26 14:50:58.951 \n", "41 PG 2013-05-26 14:50:58.951 \n", "42 NC-17 2013-05-26 14:50:58.951 \n", "43 G 2013-05-26 14:50:58.951 \n", "44 PG-13 2013-05-26 14:50:58.951 \n", "45 R 2013-05-26 14:50:58.951 \n", "46 PG 2013-05-26 14:50:58.951 \n", "47 PG-13 2013-05-26 14:50:58.951 \n", "48 PG-13 2013-05-26 14:50:58.951 \n", "49 R 2013-05-26 14:50:58.951 \n", "\n", " special_features \\\n", "0 [Trailers, Commentaries, Behind the Scenes] \n", "1 [Trailers] \n", "2 [Trailers, Commentaries, Deleted Scenes] \n", "3 [Trailers, Deleted Scenes, Behind the Scenes] \n", "4 [Trailers] \n", "5 [Deleted Scenes, Behind the Scenes] \n", "6 [Behind the Scenes] \n", "7 [Trailers, Deleted Scenes] \n", "8 [Commentaries, Deleted Scenes, Behind the Scenes] \n", "9 [Trailers, Behind the Scenes] \n", "10 [Trailers, Deleted Scenes] \n", "11 [Deleted Scenes, Behind the Scenes] \n", "12 [Commentaries, Deleted Scenes, Behind the Scenes] \n", "13 [Trailers, Deleted Scenes] \n", "14 [Trailers, Commentaries, Behind the Scenes] \n", "15 [Trailers, Commentaries, Behind the Scenes] \n", "16 [Trailers, Deleted Scenes] \n", "17 [Trailers, Commentaries, Deleted Scenes] \n", "18 [Deleted Scenes] \n", "19 [Trailers, Deleted Scenes, Behind the Scenes] \n", "20 [Deleted Scenes] \n", "21 [Trailers, Behind the Scenes] \n", "22 [Trailers, Deleted Scenes] \n", "23 [Deleted Scenes, Behind the Scenes] \n", "24 [Commentaries, Deleted Scenes, Behind the Scenes] \n", "25 [Trailers] \n", "26 [Trailers, Commentaries, Behind the Scenes] \n", "27 [Commentaries, Deleted Scenes] \n", "28 [Trailers, Commentaries, Behind the Scenes] \n", "29 [Commentaries, Deleted Scenes] \n", "30 [Deleted Scenes, Behind the Scenes] \n", "31 [Commentaries, Behind the Scenes] \n", "32 [Commentaries, Deleted Scenes, Behind the Scenes] \n", "33 [Deleted Scenes, Behind the Scenes] \n", "34 [Commentaries, Deleted Scenes, Behind the Scenes] \n", "35 [Commentaries, Deleted Scenes] \n", "36 [Commentaries] \n", "37 [Commentaries, Deleted Scenes] \n", "38 [Trailers, Deleted Scenes] \n", "39 [Deleted Scenes] \n", "40 [Trailers, Commentaries] \n", "41 [Trailers, Commentaries] \n", "42 [Trailers, Deleted Scenes] \n", "43 [Trailers, Commentaries, Deleted Scenes, Behin... \n", "44 [Trailers] \n", "45 [Trailers, Commentaries] \n", "46 [Deleted Scenes, Behind the Scenes] \n", "47 [Deleted Scenes] \n", "48 [Trailers] \n", "49 [Trailers, Behind the Scenes] \n", "\n", " fulltext \n", "0 'australia':18 'boy':8,16 'chariot':2 'dog':11... \n", "1 'car':8 'compos':11 'drama':5 'fight':14 'idol... \n", "2 'astound':4 'birdcag':2 'boat':11 'car':16 'di... \n", "3 'amaz':4 'documentari':5 'hope':1 'must':14 'o... \n", "4 'display':5 'horn':1 'mad':8 'monkey':18 'must... \n", "5 'build':15 'car':12 'documentari':5 'horror':1... \n", "6 'challeng':16 'chef':9 'happi':2 'hotel':1 'ma... \n", "7 'explor':8 'fate':4 'feminist':11 'georgia':20... \n", "8 'battl':14 'dynamit':2 'georgia':19 'hous':1 '... \n", "9 'administr':18 'beauti':4 'chase':15 'databas'... \n", "10 'ancient':18 'compos':16 'dentist':11 'fight':... \n", "11 'abandon':20 'administr':12 'databas':11 'hung... \n", "12 'agent':18 'alter':2 'boat':12 'cow':9 'drama'... \n", "13 'abandon':19 'dentist':11 'hunt':1 'mine':20 '... \n", "14 'abandon':20 'administr':9 'affair':2 'databas... \n", "15 'conquer':15 'desert':21 'emot':4 'hustler':1 ... \n", "16 'boat':20 'boy':8 'doctor':2 'documentari':5 '... \n", "17 'amaz':4 'build':14 'car':16 'dentist':11 'gra... \n", "18 'butler':10 'cross':2 'fast':5 'fast-pac':4 'i... \n", "19 'beauti':4 'boat':22 'charact':5 'explor':17 '... \n", "20 'armageddon':2 'dog':8 'fate':4 'ladybug':1 'm... \n", "21 'australia':18 'cincinatti':2 'compos':16 'fem... \n", "22 'abandon':19 'amus':20 'cat':8 'cowboy':2 'den... \n", "23 'boy':8 'car':17 'insight':4 'lawless':1 'must... \n", "24 'administr':9 'berlin':20 'cow':13 'databas':8... \n", "25 'ancient':19 'china':20 'hellfight':2 'leagu':... \n", "26 'abandon':20 'cow':12 'dog':8 'dwarf':2 'fate'... \n", "27 'agent':9 'ancient':19 'astronaut':17 'beauti'... \n", "28 'astound':4 'desert':21 'legal':1 'meet':15 'm... \n", "29 'awe':5 'awe-inspir':4 'crocodil':18 'epistl':... \n", "30 'boy':16 'build':14 'cleopatra':2 'display':5 ... \n", "31 'bore':4 'canadian':20 'drama':5 'explor':12 '... \n", "32 'baloon':20 'bore':4 'cat':11 'drama':5 'liber... \n", "33 'husband':11 'insight':4 'licens':1 'madman':1... \n", "34 'charact':7 'compos':19 'defeat':17 'dentist':... \n", "35 'compos':11 'find':14 'first':19 'life':1 'man... \n", "36 'confront':14 'deer':2 'dog':8 'epistl':5 'gul... \n", "37 'bore':4 'car':8 'dog':11 'drama':5 'georgia':... \n", "38 'ancient':20 'cat':12 'chef':9 'china':21 'dis... \n", "39 'bore':4 'epistl':5 'escap':15 'frisbe':17 'gu... \n", "40 'ancient':20 'charact':5 'japan':21 'lock':1 '... \n", "41 'administr':18 'agent':2 'ancient':20 'astound... \n", "42 'abandon':19 'drama':5 'girl':8 'lolita':1 'mi... \n", "43 'challeng':14 'dog':11 'eleph':2 'explor':16 '... \n", "44 'action':5 'action-pack':4 'arizona':2 'boat':... \n", "45 'battl':15 'butler':17 'first':20 'inch':2 'lo... \n", "46 'butler':16 'drama':5 'feminist':11 'hustler':... \n", "47 'bird':2 'charact':5 'defeat':16 'emot':4 'los... \n", "48 'drama':5 'girl':8 'harri':2 'lacklustur':4 'l... \n", "49 'abandon':19 'brilliant':4 'dentist':16 'explo... " ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Tomar 5% registros al azar\n", "# https://wiki.postgresql.org/wiki/TABLESAMPLE_Implementation\n", "# The percentage of result set size to the total tuple size will be sometimes larger, \n", "# sometimes smaller than the percentage specified. \n", "# You might use \"limit \" to get the top of tuples.\n", "\n", "sql = '''\n", "SELECT * FROM film TABLESAMPLE SYSTEM(5) Limit 1000 * 0.05;\n", "'''\n", "\n", "pd.read_sql_query(sql, cnx)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Expresiones de tabla comunes (CTE - Common Table Expressions)\n", "\n", "Es un conjunto de resultados temporal que se puede hacer referencia en otro comando SQL incluyendo SELECT, INSERT, UPDATEo DELETE.\n", "\n", "* Es otro modo de agrupar consultas y facilitar su reuso dentro de la misma sesión.\n", "* Las expresiones de tabla comunes son temporales en el sentido de que solo existen durante la ejecución de la consulta." ] }, { "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
film_idtitlelength
04Affair PrejudiceLong
15African EggLong
26Agent TrumanLong
39Alabama DevilLong
411Alamo VideotapeLong
............
675994Wyoming StormLong
676996Young LanguageLong
677997Youth KickLong
678998Zhivago CoreLong
679999Zoolander FictionLong
\n", "

680 rows × 3 columns

\n", "
" ], "text/plain": [ " film_id title length\n", "0 4 Affair Prejudice Long\n", "1 5 African Egg Long\n", "2 6 Agent Truman Long\n", "3 9 Alabama Devil Long\n", "4 11 Alamo Videotape Long\n", ".. ... ... ...\n", "675 994 Wyoming Storm Long\n", "676 996 Young Language Long\n", "677 997 Youth Kick Long\n", "678 998 Zhivago Core Long\n", "679 999 Zoolander Fiction Long\n", "\n", "[680 rows x 3 columns]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql = ''' \n", "WITH cte_film AS (\n", " SELECT \n", " film_id, \n", " title,\n", " (CASE \n", " WHEN length < 30 THEN 'Short'\n", " WHEN length < 90 THEN 'Medium'\n", " ELSE 'Long'\n", " END) length \n", " FROM\n", " film\n", ")\n", "\n", "SELECT *\n", "FROM \n", " cte_film\n", "WHERE\n", " length = 'Long'\n", "ORDER BY \n", " title; \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 }