{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7716f48f-1d81-4d8d-a9fe-afecab00fae5",
   "metadata": {},
   "source": [
    "# Acceso a AzureSQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ce3930d9-8b58-4f49-b986-61809d78bc55",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3999b1bb-2e07-4d1a-9e86-e807709b5d6b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "master SQL_Latin1_General_CP1_CI_AS\n",
      "CusoC2B SQL_Latin1_General_CP1_CI_AS\n"
     ]
    }
   ],
   "source": [
    "# Test funcionamiento conexión.  Código extraído de la documentación de Azure\n",
    "\n",
    "server = 'sqlc2bcurso.database.windows.net'\n",
    "database = 'CusoC2B'\n",
    "username = 'administrador'\n",
    "password = 'Campus.5678@'   \n",
    "driver= '{ODBC Driver 17 for SQL Server}'\n",
    "\n",
    "with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:\n",
    "    with conn.cursor() as cursor:\n",
    "        cursor.execute(\"SELECT TOP 3 name, collation_name FROM sys.databases\")\n",
    "        row = cursor.fetchone()\n",
    "        while row:\n",
    "            print (str(row[0]) + \" \" + str(row[1]))\n",
    "            row = cursor.fetchone()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "451f14fa-eb57-4ba9-b98f-28f7a9f21d5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57bf8e30-5e8f-4812-b732-27576f6eb79f",
   "metadata": {},
   "source": [
    "### Prueba de descarga de datos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "15eddb50-c993-47b3-a3d6-8267cc4bca8a",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-01-01 Año nuevo Urteberri eguna CAE EAE Todos/denak None None None\n",
      "2022-01-06 Epifanía del Señor Errege Magoen eguna CAE EAE Todos/denak None None None\n",
      "2022-04-14 Jueves Santo Ostegun Santua CAE EAE Todos/denak None None None\n",
      "2022-04-15 Viernes Santo Ostiral Santua CAE EAE Todos/denak None None None\n",
      "2022-04-18 Lunes de Pascua de Resurrección Pazko Astelehena CAE EAE Todos/denak None None None\n",
      "2022-07-25 Santiago Apóstol Santiago Apostolua CAE EAE Todos/denak None None None\n",
      "2022-08-15 Asunción de la Virgen Ama Birjinaren Jasokundea CAE EAE Todos/denak None None None\n",
      "2022-09-06 V Centenario de la Vuelta al Mundo capitaneada por Juan Sebastián Elcano Juan Sebastian Elkano kapitain zela munduari bira eman izanaren V. Mendeurrena CAE EAE Todos/denak None None None\n",
      "2022-10-12 Fiesta Nacional Espainiako Jai Nazionala CAE EAE Todos/denak None None None\n",
      "2022-11-01 Todos los Santos Santu Guztien Eguna CAE EAE Todos/denak None None None\n",
      "2022-12-06 Día de la Constitución Konstituzio Eguna CAE EAE Todos/denak None None None\n",
      "2022-12-08 Inmaculada Concepción Andre Maria Sortzez Garbiaren eguna CAE EAE Todos/denak None None None\n",
      "2022-07-04 San Valentín de Berriotxoa San Balendin Berrio-Otxoa Bizkaia Bizkaia Bizkaia None None None\n",
      "2022-09-09 Nuestra Señora de Aránzazu Arantzazuko Amaren eguna Gipuzkoa Gipuzkoa Gipuzkoa None None None\n",
      "2022-04-28 San Prudencio   San Prudentzio Álava - Araba Álava - Araba Álava - Araba None None None\n",
      "2022-02-03 Fiesta local Tokiko jai-eguna Abadiño   Abadiño   Bizkaia 1 43.15 -2.610278\n",
      "2022-06-30 Santa Lucía Santa Luzia Abanto y Ciérvana - Abanto Zierbena  Abanto y Ciérvana-Abanto Zierbena  Bizkaia 2 43.316389 -3.083333\n",
      "2022-05-26 La Ascensión   Asentzio Eguna   Ajangiz Ajangiz Bizkaia 911 43301.0 -2671.0\n",
      "2022-09-02 San Antolín San Antolín Alonsotegi Alonsotegi Bizkaia 912 43.245186 -2.988701\n",
      "2022-07-16 Ntra. Sra. del Carmen   Karmengo Amaren Eguna Amorebieta-Etxano   Amorebieta-Etxano   Bizkaia 3 43.226104 -2.712628\n",
      "2022-07-05 San Martín   San Martin Amoroto   Amoroto   Bizkaia 4 43.3285 -2.5125\n",
      "2022-07-18 Santa Marina Santa Marina Arakaldo   Arakaldo   Bizkaia 5 43.14955 -2.925086\n",
      "2022-06-29 San Pedro San Pedro Arantzazu   Arantzazu   Bizkaia 6 43.152904 -2.79043\n",
      "2022-08-24 San Bartolomé Bartolome Deuna Areatza   Areatza   Bizkaia 93 43.121079 -2.767771\n",
      "2022-06-29 San Pedro San Pedro eguna Arrankudiaga Arrankudiaga Bizkaia 9 43.173503 -2.917886\n",
      "2022-12-21 Santo Tomas Santo Tomas eguna Arratzu Arratzu Bizkaia 914 43.311667 -2.638611\n",
      "2022-11-11 San Martín San Martín eguna Arrieta   Arrieta   Bizkaia 10 43.340362 -2.769799\n",
      "2022-07-22 Santa María Magdalena Andra Maria Magdalena Arrigorriaga Arrigorriaga Bizkaia 11 43.20752 -2.887839\n",
      "2022-09-08 Fiesta local Tokiko jai-eguna Artea   Artea   Bizkaia 23 43.133718 -2.784141\n",
      "2022-09-02 San Antolín San Antolin Artzentales   Artzentales   Bizkaia 8 43.240556 -3.241389\n",
      "2022-06-29 San Pedro   San Pedro Eguna Atxondo   Atxondo   Bizkaia 91 43.130342 -2.585079\n",
      "2022-06-24 San Juan   San Juan eguna Aulesti   Aulesti   Bizkaia 70 43.296084 -2.563355\n",
      "2022-08-29 San Juan Degollado Joan Doloz Eguna Bakio   Bakio   Bizkaia 12 43.427778 -2.811389\n",
      "2022-01-24 Fundación de la Villa Fundación de la Villa Balmaseda   Balmaseda   Bizkaia 90 43.192866 -3.196942\n",
      "2022-07-16 Nuestra Señora del Carmen Karmengo Andra Mari Barakaldo   Barakaldo   Bizkaia 13 43.297222 -2.991667\n",
      "2022-06-24 San Juan San Juan Barrika   Barrika   Bizkaia 14 43.406384 -2.962425\n",
      "2022-10-13 San Fuasto San Fausto Basauri   Basauri   Bizkaia 15 43.233333 -2.883333\n",
      "2022-06-24 San Juan Bautista San Juan Bautista Bedia   Bedia   Bizkaia 92 43.209374 -2.804965\n",
      "2022-08-04 Santo Domingo de Guzmán Santo Domingo de Guzman eguna Berango Berango Bizkaia 16 43.365899 -2.996356\n",
      "2022-09-08 Andra Mari Andra Mari Eguna Bermeo   Bermeo   Bizkaia 17 43.420422 -2.722158\n",
      "2022-05-29 Fiesta local Tokiko jai-eguna Berriatua   Berriatua   Bizkaia 18 43.316667 -2.466667\n",
      "2022-06-29 San Pedro San Pedro Berriz   Berriz   Bizkaia 19 43.193923 -2.589165\n",
      "2022-08-26 Viernes de la Semana Grande Aste Nagusiko Ostirala Bilbao   Bilbao   Bizkaia 20 43.256963 -2.923441\n",
      "2022-06-09 Fiesta local Tokiko jai-eguna Busturia   Busturia   Bizkaia 21 43383.0 -2697.0\n",
      "2022-05-16 San Isidro Labrador San Isidro Nekazari Deuna  Derio   Derio   Bizkaia 901 43302.0 -2.881833\n",
      "2022-06-29 San Pedro San Pedro eguna Dima   Dima   Bizkaia 26 43.143389 -2.751464\n",
      "2022-10-13 San Fausto San Fausto eguna Durango Durango Bizkaia 27 43.169068 -2.632655\n",
      "2022-06-24 San Juan San Juan eguna Ea   Ea   Bizkaia 28 43.380135 -2.585593\n",
      "2022-06-29 San Pedro San Pedro Elantxobe   Elantxobe   Bizkaia 31 43.403422 -2.6398\n",
      "2022-09-05 Fiesta local Tokiko jai-eguna Elorrio   Elorrio   Bizkaia 32 43.130387 -2.542155\n",
      "2022-06-16 Corpus Christi Gorpuzti Erandio   Erandio   Bizkaia 902 43.307458 -2.946418\n",
      "2022-09-29 San Miguel Arcángel San Miguel Arcángel Ereño   Ereño   Bizkaia 33 43.356944 -2.621667\n",
      "2022-07-27 Día de Zezen Zezen eguna Ermua   Ermua   Bizkaia 34 43.186642 -2.502607\n",
      "2022-06-24 San Juan San Juan Errigoiti Errigoiti Bizkaia 79 43.318709 -2.725344\n",
      "2022-12-26 San Esteban San Esteban Etxebarri   Etxebarri   Bizkaia 29 43.247179 -2.890139\n",
      "2022-11-30 San Andrés San Andres eguna Etxebarria   Etxebarria   Bizkaia 30 43.254146 -2.477354\n",
      "2022-11-11 San Martin  San Martin Forua   Forua   Bizkaia 906 43.333712 -2.678589\n",
      "2022-08-10 San Lorenzo San Lorenzo Fruiz   Fruiz   Bizkaia 35 43.328889 -2.783333\n",
      "2022-09-14 Exaltación de la Santa Cruz Gurutze Santuaren gorespena Galdakao   Galdakao   Bizkaia 36 43.230556 -2.845833\n",
      "2022-06-29 San Pedro Apóstol   San Pedro Apostol Galdames   Galdames   Bizkaia 37 43255.0 -3.096389\n",
      "2022-11-11 San Martín San Martín Gamiz-Fika   Gamiz-Fika   Bizkaia 38 43.311728 -2.810701\n",
      "2022-07-26 Santa Ana Santa Ana Garai   Garai   Bizkaia 39 43.194722 -2.609722\n",
      "2022-04-25 San Marcos Markos Deuna Gatika   Gatika   Bizkaia 40 43.363889 -2.871944\n",
      "2022-01-26 San Policarpio San Policarpio eguna Gautegiz Arteaga Gautegiz Arteaga Bizkaia 41 43.353104 -2.650985\n",
      "2022-08-16 San Roque San Roke Gernika-Lumo   Gernika-Lumo   Bizkaia 46 43.317073 -2.678975\n",
      "2022-08-10 San Lorenzo San Lorenzo eguna  Getxo   Getxo   Bizkaia 44 43.344167 -3.006389\n",
      "2022-09-08 Andra Mari Andra Mari Eguna Gizaburuaga Gizaburuaga Bizkaia 47 43.33194 -2.536111\n",
      "2022-09-26 San Cosme y San Damián San Cosme y San Damián Gordexola   Gordexola   Bizkaia 42 43.178611 -3.072778\n",
      "2022-08-05 Nuestra Señora de Aguirre y de las Nieves Agirreko Andra Maria eta Elurretakoa Gorliz   Gorliz   Bizkaia 43 43.416667 -2.933333\n",
      "2022-08-10 San Lorenzo San Lorenzo Güeñes   Güeñes   Bizkaia 45 43.210321 -3.095317\n",
      "2022-06-16 Corpus Christi Corpus Christi Ibarrangelu Ibarrangelu Bizkaia 48 43.390215 -2.633802\n",
      "2022-06-13 Fiesta local Tokiko jai-eguna Igorre Igorre Bizkaia 94 43.165833 -2.776111\n",
      "2022-07-26 Santa Ana Santa Ana Ispaster Ispaster Bizkaia 49 43.362954 -2.544635\n",
      "2022-09-29 San Miguel   San Miguel Iurreta   Iurreta   Bizkaia 910 43.176705 -2.633777\n",
      "2022-09-08 Ntra. Sra. De Erdozia Ntra. Sra. De Erdozia Izurtza   Izurtza   Bizkaia 50 43.152273 -2.641978\n",
      "2022-09-29 San Miguel   San Miguel Valle de Carranza Karrantza Harana Bizkaia 22 43.225278 -3.359444\n",
      "2022-07-26 Fiesta local Tokiko jai-eguna Kortezubi   Kortezubi   Bizkaia 907 43.335982 -2.654319\n",
      "2022-08-05 La Virgen de las Nieves La Virgen de las Nieves Lanestosa Lanestosa Bizkaia 51 43.221169 -3.439148\n",
      "2022-09-29 San Miguel San Miguel Larrabetzu Larrabetzu Bizkaia 52 43.260973 -2.796175\n",
      "2022-09-29 San Miguel San Miguel Laukiz Laukiz Bizkaia 53 43.353333 -2.906111\n",
      "2022-06-24 San Juan San Juan eguna Leioa  Leioa  Bizkaia 54 43.326097 -2.987704\n",
      "2022-09-02 San Antolín   San Antolin eguna Lekeitio Lekeitio Bizkaia 57 43.365509 -2.503344\n",
      "2022-09-08 Andra Mari Andra Mari Lemoa Lemoa Bizkaia 55 43.208759 -2.774116\n",
      "2022-10-17 Fiesta Local Tokiko jai-eguna Lemoiz Lemoiz Bizkaia 56 43.413244 -2.902253\n",
      "2022-09-08 Andra Mari Andra Mari eguna Lezama Lezama Bizkaia 81 43.272167 -2.832167\n",
      "2022-06-29 Fiesta local Tokiko jai-eguna Loiu Loiu Bizkaia 903 43315.0 -2.938333\n",
      "2022-03-02 Miércoles de Ceniza Hautsezko asteazkena Mallabia Mallabia Bizkaia 58 43.189651 -2.529969\n",
      "2022-10-21 Santa Ursula Santa Ursula Mañaria Mañaria Bizkaia 59 43.137485 -2.661135\n",
      "2022-07-16 Fiesta local Tokiko jai-eguna Markina-Xemein Markina-Xemein Bizkaia 60 43.270604 -2.497155\n",
      "2022-08-10 San Lorenzo Lorentzo Deuna Maruri-Jatabe Maruri-Jatabe Bizkaia 61 43.382772 -2.874989\n",
      "2022-09-29 San Miguel San Miguel Mendata Mendata Bizkaia 62 43.283333 -2.633333\n",
      "2022-08-01 San Pedro Advincula San Pedro Advincula Mendexa Mendexa Bizkaia 63 43.346389 -2.483333\n",
      "2022-08-16 San Roque Roke Deuna Meñaka Meñaka Bizkaia 64 43.364464 -2.801795\n",
      "2022-07-26 Santa Ana Santa Ana Morga Morga Bizkaia 66 43.297932 -2.752739\n",
      "2022-06-29 San Pedro y San Pablo San Pedro eta San Paulo jai eguna Mundaka Mundaka Bizkaia 68 43.407335 -2.698322\n",
      "2022-06-29 Fiesta local Tokiko jai-eguna Mungia Mungia Bizkaia 69 43.35441 -2.846714\n",
      "2022-06-29 San Pedro San Pedro Eguna Munitibar-Arbatzegi Gerrikaitz Munitibar-Arbatzegi Gerrikaitz Bizkaia 7 43.265332 -2.591845\n",
      "2022-09-08 Fiesta local Tokiko jai-eguna Murueta Murueta Bizkaia 908 43.354463 -2.684153\n",
      "2022-06-24 San Juan San Juan Muskiz Muskiz Bizkaia 71 43.323333 -3.121667\n",
      "2022-10-31 Último lunes de octubre de Gernika Gernikako urriko azken astelehena Muxika Muxika Bizkaia 67 43.289668 -2.692941\n",
      "2022-09-08 Andra Mari Andra Mari eguna Nabarniz Nabarniz Bizkaia 909 43.321597 -2.5844\n",
      "2022-08-17 Día del pescador Arrantzale eguna Ondarroa Ondarroa Bizkaia 73 43.320698 -2.420475\n",
      "2022-09-02 San Antolin San Antolin Orozko Orozko Bizkaia 75 43.108611 -2.911111\n",
      "2022-05-18 San Félix de Cantalicio Kantaliziozko Felix Deuna Ortuella Ortuella Bizkaia 83 43.31137 -3.055952\n",
      "2022-07-18 Santa Marina Santa Marina Otxandio   Otxandio   Bizkaia 72 43.040447 -2.654584\n",
      "2022-07-22 La Magdalena La Magdalena Plentzia   Plentzia   Bizkaia 77 43.40443 -2.950102\n",
      "2022-08-16 San Roque San Roque Portugalete   Portugalete   Bizkaia 78 43.320664 -3.020252\n",
      "2022-07-16 Virgen del Carmen Virgen del Carmen Santurtzi   Santurtzi   Bizkaia 82 43.328711 -3.031772\n",
      "2022-06-29 San Pedro Apóstol San Pedro apostoluaren eguna Sestao   Sestao   Bizkaia 84 43.309635 -3.00694\n",
      "2022-06-24 Fiesta local Tokiko jaieguna Sondika   Sondika   Bizkaia 904 43.300063 -2.927103\n",
      "2022-06-29 San Pedro San Pedro Sopelana   Sopelana   Bizkaia 85 43.378918 -2.983002\n",
      "2022-07-26 Santa Ana Santa Ana Sopuerta   Sopuerta   Bizkaia 86 43.262778 -3.1525\n",
      "2022-11-30 San Andrés   San Andres   Sukarrieta   Sukarrieta   Bizkaia 76 43.395342 -2.696367\n",
      "2022-06-29 San Pedro San Pedro Trucios-Turtzioz   Trucios-Turtzioz   Bizkaia 87 43.272678 -3.288545\n",
      "2022-06-24 San Juan San Juan eguna Ubide   Ubide   Bizkaia 88 43.026832 -2.688209\n",
      "2022-09-08 Nuestra Señora de la Virgen de Udiarraga   Nuestra Señora de la Virgen de Udiarraga   Ugao-Miraballes   Ugao-Miraballes   Bizkaia 65 43.179989 -2.902508\n",
      "2022-09-08 Andramari Andramari Urduliz   Urduliz   Bizkaia 89 43.379667 -2962.0\n",
      "2022-05-09 Otxomaio   Otxomaio   Urduña-Orduña   Urduña-Orduña   Bizkaia 74 42.994449 -3.009622\n",
      "2022-08-08 Transfiguración del Señor Transfiguración del Señor Valle de Trápaga-Trapagaran   Valle de Trápaga-Trapagaran   Bizkaia 80 43.303108 -3.035548\n",
      "2022-07-08 Fiesta local Tokiko jaieguna Zaldibar   Zaldibar   Bizkaia 95 43.17097 -2.54713\n",
      "2022-10-03 Día de Gangas Gangas eguna Zalla   Zalla   Bizkaia 96 43.214009 -3.135223\n",
      "2022-11-11 San Martín San Martin eguna Zamudio   Zamudio   Bizkaia 905 43.283333 -2.866667\n",
      "2022-08-10 San Lorenzo   San Lorenzo eguna  Zaratamo   Zaratamo   Bizkaia 97 43.211505 -2.873626\n",
      "2022-08-16 San Roque San Roke eguna Zeanuri   Zeanuri   Bizkaia 24 43.099664 -2.749615\n",
      "2022-09-02 San Antolín   San Antolin eguna Zeberio   Zeberio   Bizkaia 25 43.152778 -2.852778\n",
      "2022-10-17 Día de la Independencia de Zierbena   Zierbenako Independentziaren Eguna   Zierbena   Zierbena   Bizkaia 913 43.347655 -3.086152\n",
      "2022-06-24 San Juan San Juan Ziortza-Bolibar   Ziortza-Bolibar   Bizkaia 915 43.24982 -2.550174\n",
      "2022-06-24 San Juan San Juan Abaltzisketa   Abaltzisketa   Gipuzkoa 1 43.047582 -2.105388\n",
      "2022-08-16 Fiesta local Tokiko jai-eguna Aduna   Aduna   Gipuzkoa 2 43.203792 -2.050079\n",
      "2022-08-10 San Lorenzo San Lorenzo Aia   Aia   Gipuzkoa 16 43.236922 -2.149008\n",
      "2022-09-29 San Miguel Arcángel Mikel Goi Angerua Aizarnazabal   Aizarnazabal   Gipuzkoa 3 43.255405 -2.236461\n",
      "2022-08-16 Fiesta local Tokiko jaieguna Albiztur   Albiztur   Gipuzkoa 4 43.129723 -2.13718\n",
      "2022-07-16 Fiesta local Tokiko jaieguna Alegia   Alegia   Gipuzkoa 5 43.100017 -2.096273\n",
      "2022-09-08 Natividad de la Virgen Amabirjinaren jaiotze eguna Alkiza   Alkiza   Gipuzkoa 6 43.172434 -2.109436\n",
      "2022-09-29 San Miguel Arcángel Mikel Goi Angerua Altzaga   Altzaga   Gipuzkoa 906 43.06449 -2.153833\n",
      "2022-02-28 Lunes de Carnaval   Iñauterietako Astelehena Altzo   Altzo   Gipuzkoa 7 43.100072 -2.083905\n",
      "2022-08-24 San Bartolomé Bartolome Deuna Amezketa   Amezketa   Gipuzkoa 8 43.044517 -2.083235\n",
      "2022-06-24 San Juan San Juan Andoain   Andoain   Gipuzkoa 9 43.22048 -2.021122\n",
      "2022-06-16 Corpus Christi   Gorpuzti-eguna Anoeta   Anoeta   Gipuzkoa 10 43.161467 -2.070695\n",
      "2022-07-15 Fiesta local Tokiko jai-eguna Antzuola   Antzuola   Gipuzkoa 11 43.099441 -2.381747\n",
      "2022-11-11 San Martín   Martin Deuna Arama   Arama   Gipuzkoa 12 43.063637 -2.165558\n",
      "2022-08-16 Fiesta local Tokiko jai-eguna Aretxabaleta   Aretxabaleta   Gipuzkoa 13 43.036248 -2.504542\n",
      "2022-06-24 San Juan Bautista San Juan Bataiatzailea Arrasate-Mondragon   Arrasate-Mondragon   Gipuzkoa 55 43.066517 -2.487562\n",
      "2022-06-29 San Pedro   San Pedro Asteasu   Asteasu   Gipuzkoa 14 43.195198 -2.097548\n",
      "2022-07-26 Fiesta local Tokiko jai-eguna Astigarraga   Astigarraga   Gipuzkoa 903 43.280652 -1.94756\n",
      "2022-11-11 San Martín San Martin Ataun   Ataun  Gipuzkoa 15 43.00548 -2.17662\n",
      "2022-11-30 San Andrés   San Andres Azkoitia   Azkoitia   Gipuzkoa 17 43.178205 -2.31164\n",
      "2022-01-20 San Sebastián San Sebastian Azpeitia   Azpeitia   Gipuzkoa 18 43.1839 -2.265466\n",
      "2022-08-16 Fiesta local Tokiko jai-eguna Baliarrain   Baliarrain   Gipuzkoa 904 43.069306 -2.129235\n",
      "2022-05-30 Fiesta local Tokiko jai-eguna Beasain   Beasain   Gipuzkoa 19 43.047076 -2.204605\n",
      "2022-06-29 Fiesta local Tokiko jai-eguna Beizama   Beizama   Gipuzkoa 20 43.134191 -2.200113\n",
      "2022-06-24 San Juan San Juan Belaunza   Belauntza   Gipuzkoa 21 43.135283 -2.050705\n",
      "2022-08-10 San Lorenzo   San Lorentzo Berastegi   Berastegi   Gipuzkoa 22 43.124029 -1.979426\n",
      "2022-09-16 San Martín Agirre San Martin Agirre Bergara   Bergara   Gipuzkoa 74 43.118217 -2.413358\n",
      "2022-11-30 San Andrés San Andres Berrobi   Berrobi   Gipuzkoa 23 43.145479 -2.026374\n",
      "2022-08-16 Día siguiente a la Virgen Andra Mariren biharmunea Bidania  - Goiatz   Bidania  - Goiatz   Gipuzkoa 24 43.142086 -2.167475\n",
      "2022-08-24 San Bartolomé Bartolome Deuna Bidania - Bidania   Bidania - Bidania   Gipuzkoa 24 43.139399 -2.159979\n",
      "2022-08-16 San Roque San Roke Deba   Deba   Gipuzkoa 29 43.295562 -2.353894\n",
      "2022-01-20 San Sebastián   San Sebastian Donostia-San Sebastian   Donostia-San Sebastian   Gipuzkoa 69 43.321874 -1.98552\n",
      "2022-06-24 San Juan San Juan Eibar   Eibar   Gipuzkoa 30 43.18418 -2.473289\n",
      "2022-09-08 Natividad de la Virgen Amabirjinaren jaiotze eguna Elduaien   Elduain   Gipuzkoa 31 43.140712 -2.000488\n",
      "2022-07-01 Fiesta local Tokiko jai-eguna Elgeta   Elgeta   Gipuzkoa 33 43.136572 -2.487392\n",
      "2022-01-17 San Antón San Anton Elgoibar   Elgoibar   Gipuzkoa 32 43.215187 -2.415702\n",
      "2022-07-22 María Magdalena   Madalena eguna Errenteria   Errenteria   Gipuzkoa 67 43.312752 -1.899633\n",
      "2022-11-11 Fiesta local Tokiko jaieguna Errezil   Errezil   Gipuzkoa 66 43.164674 -2.173594\n",
      "2022-06-29 San Pedro San Pedro Eskoriatza   Eskoriatza   Gipuzkoa 34 43.017074 -2.52698\n",
      "2022-05-09 Aparicio de San Miguel Mikel Deuna Ezkio-Itsaso - Ezkio   Ezkio-Itsaso - Ezkio   Gipuzkoa 35 43.080484 -2.275421\n",
      "2022-06-29 San Pedro Pedro Deuna Ezkio-Itsaso - Sta. Lutzi-Anduaga Ezkio-Itsaso - Sta. Lutzi-Anduaga Gipuzkoa 35 43.064321 -2.274735\n",
      "2022-08-16 San Roque   San Roke Gabiria   Gabiria   Gipuzkoa 38 43.049689 -2.279856\n",
      "2022-09-29 San Miguel   San Migel Gaintza   Gaintza   Gipuzkoa 37 43.052629 -2.13237\n",
      "2022-08-16 Fiesta local Tokiko jaieguna Gaztelu   Gaztelu   Gipuzkoa 907 43.116579 -2.022278\n",
      "2022-08-06 San Salvador   Salbatore Deuna Getaria   Getaria   Gipuzkoa 39 43.303261 -2.204363\n",
      "2022-06-24 San Juan Bautista San Joan Bataiatzailea Hernani   Hernani   Gipuzkoa 40 43.267549 -1.975814\n",
      "2022-08-16 Fiesta local Tokiko jai-eguna Hernialde   Hernialde   Gipuzkoa 41 43.154467 -2.085025\n",
      "2022-09-08 Ntra. Sra. De Guadalupe Guadalupeko Ama Hondarribia   Hondarribia   Gipuzkoa 36 43.362531 -1.791498\n",
      "2022-08-24 San Bartolomé   San Bartolome Ibarra   Ibarra   Gipuzkoa 42 43.132345 -2.065462\n",
      "2022-02-03 Fiesta local Tokiko jai-eguna Idiazabal   Idiazabal   Gipuzkoa 43 43.011782 -2.234214\n",
      "2022-08-10 San Lorenzo San Lorentzo Ikaztegieta   Ikaztegieta   Gipuzkoa 44 43.09496 -2.1239\n",
      "2022-06-30 San Marcial San Marcial Irun   Irun   Gipuzkoa 45 43.338233 -1.789272\n",
      "2022-09-29 San Miguel  San Migel Irura   Irura   Gipuzkoa 46 43.165688 -2.069616\n",
      "2022-07-22 María Magdalena Madalena Donea Itsaso - Barrio Itxaso-Alegia  Itsaso - Itxaso-Alegia auzoa  Gipuzkoa 35 43.058055 -2.268215\n",
      "2022-08-24 San Bartolomé Bartolome Deuna Itsaso - Casco Itsaso - hirigunea Gipuzkoa 35 43.072544 -2.257418\n",
      "2022-09-08 Día de la Virgen de Kizkitza Kizkitzako Ama Birjinaren eguna Itsaso - Mendialdea Itsaso - Mendialdea Zozkera Gipuzkoa 35 43.072544 -2.257418\n",
      "2022-09-23 Fiesta local Tokiko jai-eguna Itsasondo   Itsasondo   Gipuzkoa 47 43.066932 -2.167393\n",
      "2022-08-03 Fiesta local Tokiko jai-eguna Larraul   Larraul   Gipuzkoa 48 43.187739 -2.102534\n",
      "2022-06-29 San Pedro  San Pedro  Lasarte-Oria   Lasarte-Oria Gipuzkoa 902 43.267455 -2.020179\n",
      "2022-04-28 San Prudencio   San Prudentzio Lazkao   Lazkao   Gipuzkoa 49 43.034324 -2.187379\n",
      "2022-06-29 San Pedro  San Pedro Leaburu   Leaburu   Gipuzkoa 50 43.124984 -2.052106\n",
      "2022-05-03 Santa Cruz Santikutz eguna Legazpi   Legazpi   Gipuzkoa 51 43.054361 -2.333641\n",
      "2022-05-26 La Ascensión Azintzio eguna Legorreta   Legorreta   Gipuzkoa 52 43.085794 -2.150909\n",
      "2022-09-08 Festividad de la Virgen Amabirjinaren eguna Leintz-Gatzaga   Leintz-Gatzaga   Gipuzkoa 68 42.987112 -2.568626\n",
      "2022-09-14 Exaltación de la Santa Cruz   Gurutze Santuaren gorespena Lezo   Lezo   Gipuzkoa 53 43.321127 -1.898775\n",
      "2022-09-08 Fiesta local Tokiko jaieguna Lizartza   Lizartza   Gipuzkoa 54 43.10372 -2.034254\n",
      "2022-07-26 Santa Ana Santa Ana Mendaro   Mendaro   Gipuzkoa 901 43.264167 -2.3755\n",
      "2022-06-29 San Pedro   San Pedro Mutiloa   Mutiloa   Gipuzkoa 57 43.02238 -2.272826\n",
      "2022-07-22 María Magdalena Madalen eguna Mutriku   Mutriku   Gipuzkoa 56 43.307069 -2.385083\n",
      "2022-08-03 San Esteban   Don Eztebe Oiartzun   Oiartzun   Gipuzkoa 63 43.299282 -1.857874\n",
      "2022-06-24 San Juan San Joan Olaberria   Olaberria   Gipuzkoa 58 43.027075 -2.203089\n",
      "2022-09-29 San Miguel San Migel Oñati   Oñati   Gipuzkoa 59 43.032888 -2.411348\n",
      "2022-07-26 Santa Ana Santa Ana Ordizia   Ordizia   Gipuzkoa 76 43.054198 -2.178115\n",
      "2022-01-20 San Sebastián San Sebastian Orendain   Orendain   Gipuzkoa 905 43.079721 -2.11429\n",
      "2022-05-03 Fiesta local Tokiko jaieguna Orexa   Orexa   Gipuzkoa 60 43.093881 -2.011392\n",
      "2022-06-29 San Pedro   San Pedro Orio   Orio   Gipuzkoa 61 43.278323 -2.126597\n",
      "2022-11-30 San Andrés   San Andres Ormaiztegi   Ormaiztegi   Gipuzkoa 62 43.042735 -2.255848\n",
      "2022-07-07 San Fermín San Fermin Pasaia - Antxo   Pasaia - Antxo   Gipuzkoa 64 43.317534 -1.917562\n",
      "2022-06-24 San Juan San Juan Pasaia - Donibane   Pasaia - Donibane   Gipuzkoa 64 43.327481 -1.919883\n",
      "2022-06-29 San Pedro   San Pedro Pasaia - San Pedro   Pasaia - San Pedro   Gipuzkoa 64 43.323348 -1.930432\n",
      "2022-07-16 Virgen del Carmen   Karmengo Ama Pasaia - Trintxerpe   Pasaia - Trintxerpe   Gipuzkoa 64 43.326138 -1.931587\n",
      "2022-06-24 SanJuan San Joan Segura   Segura   Gipuzkoa 70 43.009216 -2.252519\n",
      "2022-07-26 Santa Ana   Santa Ana   Soraluze - P. De Las Armas Soraluze - P. De Las Armas Gipuzkoa 65 43.17543 -2.412494\n",
      "2022-06-24 Fiesta local Tokiko jaieguna Tolosa   Tolosa   Gipuzkoa 71 43.136978 -2.073914\n",
      "2022-09-29 San Miguel   San Migel Urnieta   Urnieta   Gipuzkoa 72 43.2482 -1.990517\n",
      "2022-09-19 Santa Anastasia   Santa Anastasia Urretxu   Urretxu   Gipuzkoa 77 43.092282 -2.314383\n",
      "2022-07-02 Santa Isabel Santixabel Usurbil   Usurbil   Gipuzkoa 73 43.271382 -2.049934\n",
      "2022-07-26 Santa Ana Santa Ana Villabona   Billabona   Gipuzkoa 75 43.189944 -2.052453\n",
      "2022-10-06 Santa Fe   Santa Fe Zaldibia   Zaldibia   Gipuzkoa 78 43.037964 -2.150894\n",
      "2022-06-27 San Pelayo   San Pelaio Zarautz   Zarautz   Gipuzkoa 79 43.280364 -2.171588\n",
      "2022-06-06 Lunes de Pentecostés   Mendekoste astelehena Zegama   Zegama   Gipuzkoa 25 42.975677 -2.290072\n",
      "2022-08-16 San Roque San Roke Zerain   Zerain   Gipuzkoa 26 43.012119 -2.274118\n",
      "2022-09-08 Festividad de la Virgen Ama Birjinaren eguna Zestoa   Zestoa   Gipuzkoa 27 43.239442 -2.258113\n",
      "2022-09-08 Fiesta local Tokiko jai-eguna Zizurkil   Zizurkil   Gipuzkoa 28 43.199523 -2.074073\n",
      "2022-06-29 San Pedro   San Pedro Zumaia   Zumaia   Gipuzkoa 81 43.297258 -2.256848\n",
      "2022-07-02 Santa Isabel   Santa Isabel Zumarraga   Zumarraga   Gipuzkoa 80 43.090476 -2.31322\n",
      "2022-02-03 San Blas San Joan Bataiatzailea Alegría-Dulantzi Alegria-Dulantzi Álava - Araba 2 42.841035 -2.512684\n",
      "2022-08-16 Fiesta local Tokiko jai-eguna Amurrio Amurrio Álava - Araba 1 43.057875 -2.998775\n",
      "2022-07-11 San Cristóbal Done Kristobal Añana   Añana   Álava - Araba 49 42.802352 -2.982607\n",
      "2022-07-04 Fiesta local Tokiko jaieguna Aramaio   Aramaio   Álava - Araba 3 43054.0 -2566.0\n",
      "2022-11-30 San Andrés Apóstol San Andrés Apostolua Armiñón   Armiñón   Álava - Araba 6 42.723045 -2.872574\n",
      "2022-06-20 San Adrián San Adrian Arraia-Maeztu   Arraia-Maeztu   Álava - Araba 37 42.739815 -2.44598\n",
      "2022-12-26 San Esteban San Esteban Arrazua-Ubarrundia   Arrazua-Ubarrundia   Álava - Araba 8 42.890278 -2.639167\n",
      "2022-09-08 Virgen de la Encina Arteako Andra Maria Artziniega   Artziniega   Álava - Araba 4 43.122201 -3.128209\n",
      "2022-06-29 San Pedro Apóstol Done Petri Asparrena   Asparrena   Álava - Araba 9 42.895667 -2321.0\n",
      "2022-07-26 Santiago Done Jakue Ayala - Agiñaga Aiara - Agiñaga Álava - Araba 10 43.03508 -3.071144\n",
      "2022-05-21 San Ramiro Done Ramiro Ayala - Añes   Aiara - Añes   Álava - Araba 10 43.058414 -3.132924\n",
      "2022-09-29 San Miguel Done Mikel Ayala - Beotegi   Aiara - Beotegi   Álava - Araba 10 43.08866 -3.075259\n",
      "2022-09-29 San Miguel Done Mikel Ayala - Costera   Aiara - Opellora Álava - Araba 10 43.102599 -3.103772\n",
      "2022-05-03 La Cruz Gurutzea Ayala - Erbi   Aiara - Erbi   Álava - Araba 10 43.074143 -3.111819\n",
      "2022-05-09 Ntra. Sra. de la Antigua  Antiguako Ama  Ayala - Etxegoien   Aiara - Etxegoien   Álava - Araba 10 43.043726 -3.02107\n",
      "2022-01-07 San Julián Done Julian Ayala - Izoria   Aiara - Izoria   Álava - Araba 10 43.061416 -3.036324\n",
      "2022-12-09 La Inmaculada Sortzez Garbiaren Eguna Ayala - Lejarzo   Aiara - Lexartzu Álava - Araba 10 43.060158 -3.122923\n",
      "2022-08-16 La Asunción Jasokundearen Eguna Ayala - Lujo   Aiara - Luxo   Álava - Araba 10 43.077952 -3.096407\n",
      "2022-07-22 La Magdalena Magdalena Eguna Ayala - Luiaondo   Aiara - Luiaondo   Álava - Araba 10 43.102723 -2.997211\n",
      "2022-07-26 Santiago Done Jakue Ayala - Llanteno   Aiara - Llanteno   Álava - Araba 10 43.091803 -3.099984\n",
      "2022-11-23 San Clemente Done Klemente Ayala - Madaria   Aiara - Madaria   Álava - Araba 10 43.04389 -3.085953\n",
      "2022-01-17 San Antón Done Anton Ayala - Maroño   Aiara - Maroño   Álava - Araba 10 43.055065 -3.05901\n",
      "2022-06-29 San Pedro Done Petri Ayala - Menagarai   Aiara - Menagarai   Álava - Araba 10 43.096299 -3.072769\n",
      "2022-04-25 San Marcos Done Marko Ayala - Menoio   Aiara - Menoio   Álava - Araba 10 43.069585 -3.072926\n",
      "2022-06-24 San Juan Done Joane Ayala - Murga   Aiara - Murga   Álava - Araba 10 43.075372 -3.02559\n",
      "2022-01-24 San Babilás Done Babilas Ayala - Olabezar   Aiara - Olabezar   Álava - Araba 10 43.069997 -3.014813\n",
      "2022-10-08 Ntra. Sra. del Rosario  Errosarioko Andra Maria Ayala - Ozeka   Aiara - Ozeka   Álava - Araba 10 43.070824 -3.090905\n",
      "2022-08-05 Las Nieves Elurretako Andra Maria Ayala - Quejana   Aiara - Kexaa Álava - Araba 10 43.081485 -3.063094\n",
      "2022-05-16 San Isidro Done Isidro Ayala - Respaldiza   Aiara - Arespalditza   Álava - Araba 10 43.078065 -3.042931\n",
      "2022-07-22 La Magdalena Magdalena Eguna Ayala - Retes de Llanteno   Aiara - Retes de Llanteno   Álava - Araba 10 43.091803 -3.099984\n",
      "2022-07-18 Santa Marina Dona Marina Ayala - Salmantón   Aiara - Salmanton   Álava - Araba 10 43.052629 -3.088347\n",
      "2022-08-30 San Emeterio Done Meteri Ayala - Sojo   Aiara - Soxo   Álava - Araba 10 43.09073 -3.122839\n",
      "2022-07-18 Santa Marina  Dona Marina Ayala - Zuaza   Aiara - Zuhatza   Álava - Araba 10 43.099672 -3.049447\n",
      "2022-08-24 San Bartolomé Done Bartolome Baños de Ebro   Mañueta Álava - Araba 11 42.53029 -2.679144\n",
      "2022-06-24 San Juan Done Joane Barrundia   Barrundia   Álava - Araba 13 42.916667 -2.491667\n",
      "2022-09-08 Asunción de María Jasokundearen eguna Berantevilla   Berantevilla   Álava - Araba 14 42.68245 -2.860401\n",
      "2022-09-08 Fiesta local Tokiko jaieguna Bernedo   Bernedo   Álava - Araba 16 42.626684 -2.497532\n",
      "2022-05-16 San Isidro Done Isidro Campezo   Kanpezu Álava - Araba 17 42.691944 -2.37\n",
      "2022-09-08 La Virgen Andra Maria Kripan   Kripan   Álava - Araba 19 42.591666 -2.516079\n",
      "2022-06-24 Fiesta local Tokiko jaieguna Kuartango   Kuartango   Álava - Araba 20 42.871389 -2.885556\n",
      "2022-06-29 San Pedro San Pedro Elburgo   Burgelu Álava - Araba 21 42.849564 -2.544762\n",
      "2022-09-08 Virgen de la Plaza Plazako Andra Maria Elciego   Zieko Álava - Araba 22 42.515073 -2.618277\n",
      "2022-08-16 San Roque San Roke Elvillar Bilar Álava - Araba 23 42.570497 -2.544915\n",
      "2022-06-24 Fiesta local Tokiko jai-eguna Valle de Arana Harana Álava - Araba None 42.752731 -2.312303\n",
      "2022-08-16 San Roque Done Roke Iruña de Oca - Nanclares de la Oca Iruña Oka - Langraiz Oka Álava - Araba 901 42.817197 -2.811518\n",
      "2022-06-24 San Juan Done Joane Iruraiz-Gauna   Iruraiz-Gauna   Álava - Araba 27 42.822072 -2.495043\n",
      "2022-11-25 2º día de Acción de Gracias   2. Esker Oneko Eguna Labastida   Labastida   Álava - Araba 28 42.590459 -2.792991\n",
      "2022-08-24 Fiesta local Tokiko jaieguna Lagrán   Lagrán   Álava - Araba 30 42.626395 -2.58382\n",
      "2022-06-24 San Juan Done Joane Laguardia   Laguardia   Álava - Araba 31 42.554278 -2.584237\n",
      "2022-09-08 Virgen del Campo Amabirjina Lanciego   Lantziego   Álava - Araba 32 42.562619 -2.513204\n",
      "2022-09-16 Santos Cornelio y Cipriano   Done Kornelio eta Done Zipri Lantarón   Lantarón   Álava - Araba 902 42.756673 -2.987556\n",
      "2022-08-24 San Bartolomé Done Bartolome Lapuebla de Labarca   Lapuebla de Labarca   Álava - Araba 33 42.493969 -2.573024\n",
      "2022-09-12 La Cofradias Kofradiak Legutio Legutiano   Álava - Araba 58 42.977822 -2.642724\n",
      "2022-08-16 San Roque San Roke Leza   Leza   Álava - Araba 34 42.566068 -2.633635\n",
      "2022-08-16 San Roque San Roke Llodio   Laudio Álava - Araba 36 43.143333 -2.962985\n",
      "2022-08-19 Acción de gracias Esker onak Moreda de Alava   Moreda de Alava   Álava - Araba 39 42.528016 -2.40767\n",
      "2022-06-02 Fiesta local Tokiko jaieguna Navaridas   Nabaridas   Álava - Araba 41 42.545171 -2.624532\n",
      "2022-08-24 San Bartolomé San Bartolome Okondo   Okondo   Álava - Araba 42 43.162222 -3.018889\n",
      "2022-01-22 San Vicente y San Anastasio Bikendi eta Anastasi Santuak Oyón/Oion   Oyón/Oion   Álava - Araba 43 42.506137 -2.436965\n",
      "2022-05-16 San Isidro Labrador Done Isidro Nekazaria Peñacerrada   Urizaharra Álava - Araba 44 42.644095 -2.713374\n",
      "2022-05-16 San Isidro Labrador Done Isidro Nekazaria Ribera Alta   Erribera Goitia Álava - Araba 46 42.807222 -2.9175\n",
      "2022-08-29 Fiesta local Tokiko jaieguna Ribera Baja   Erribera Beitia Álava - Araba 47 42.719678 -2.907609\n",
      "2022-06-24 San Juan San Joan Salvatierra   Agurain Álava - Araba 51 42.852442 -2.389455\n",
      "2022-09-08 Nuestra Señora Virgen del Valle Haranako Andra Maria Samaniego   Samaniego   Álava - Araba 52 42.569832 -2.680185\n",
      "2022-11-11 San Millán Done Milian San Millán Donemiliaga Álava - Araba 53 42.874722 -2.376111\n",
      "2022-10-03 Fiesta local Tokiko jaieguna Urkabustaiz   Urkabustaiz   Álava - Araba 54 42.971944 -2.922222\n",
      "2022-05-16 Fiesta local Tokiko jaieguna Valdegovía   Valdegovía   Álava - Araba 55 42.85 -3.1\n",
      "2022-11-30 San Andrés San Andres Villabuena de Álava   Eskuernaga Álava - Araba 57 42.548717 -2.665456\n",
      "2022-08-05 Vírgen Blanca Andra Mari Zuria Vitoria-Gasteiz   Vitoria-Gasteiz   Álava - Araba 59 42.846406 -2.667893\n",
      "2022-08-19 Nuestra Señora de Bercijana Berzijanako Andra Maria Yécora Ikeora Álava - Araba 60 42.569303 -2.470064\n",
      "2022-06-11 Fiesta local Tokiko jaieguna Zalduondo   Zalduondo   Álava - Araba 61 42.885626 -2.347254\n",
      "2022-08-05 Fiesta local Tokiko jaieguna Zambrana   Zambrana   Álava - Araba 62 42.659981 -2.879353\n",
      "2022-08-05 Fiesta local Tokiko jaieguna Zigoitia   Zigoitia   Álava - Araba 18 42.9682 -2.7183\n",
      "2022-09-29 San Miguel San Miguel Zuia   Zuia   Álava - Araba 63 42.981667 -2844.0\n"
     ]
    }
   ],
   "source": [
    "# Descarga del set de datos Calendario Festivos 2022\n",
    "\n",
    "server = 'sqlc2bcurso.database.windows.net'\n",
    "database = 'CusoC2B'\n",
    "username = 'administrador'\n",
    "password = 'Campus.5678@'   \n",
    "driver= '{ODBC Driver 17 for SQL Server}'\n",
    "\n",
    "with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:\n",
    "    with conn.cursor() as cursor:\n",
    "        cursor.execute(\"SELECT * FROM calendario_festivos_2022\")\n",
    "        row = cursor.fetchone()\n",
    "        while row:\n",
    "            print (str(row[0]) + \" \" + str(row[1]) + \" \" + str(row[2]) + \n",
    "                   \" \" + str(row[3]) + \" \" + str(row[4]) + \" \" + str(row[5]) + \n",
    "                   \" \" + str(row[6]) + \" \" + str(row[7]) + \" \" + str(row[8]))\n",
    "            row = cursor.fetchone()\n",
    "\n",
    "            \n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "123ce45f-25de-4ed6-93b1-091dd25126b0",
   "metadata": {},
   "source": [
    "### Prueba descarga tablas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0ab2bd6-7966-4dff-8853-861c8a7e2f77",
   "metadata": {},
   "source": [
    "#### **Descarga tabla completa**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "385836f4-56e6-439a-bcd2-591c98d18569",
   "metadata": {},
   "outputs": [],
   "source": [
    "server = 'sqlc2bcurso.database.windows.net'\n",
    "database = 'CusoC2B'\n",
    "username = 'administrador'\n",
    "password = 'Campus.5678@'   \n",
    "driver= '{ODBC Driver 17 for SQL Server}'\n",
    "\n",
    "db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)\n",
    "cursor = db.cursor()\n",
    "\n",
    "df = pd.read_sql_query('SELECT * FROM calendario_festivos_2022', db)\n",
    "\n",
    "#cursor.execute(\"DROP TABLE table_name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "284d5408-5cd5-473f-9083-567e294cb5b4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>descripcionEs</th>\n",
       "      <th>descriptionEu</th>\n",
       "      <th>municipalityEs</th>\n",
       "      <th>MunicipalityEu</th>\n",
       "      <th>territory</th>\n",
       "      <th>municipalitycode</th>\n",
       "      <th>latwgs84</th>\n",
       "      <th>lonwgs84</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>Año nuevo</td>\n",
       "      <td>Urteberri eguna</td>\n",
       "      <td>CAE</td>\n",
       "      <td>EAE</td>\n",
       "      <td>Todos/denak</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-06</td>\n",
       "      <td>Epifanía del Señor</td>\n",
       "      <td>Errege Magoen eguna</td>\n",
       "      <td>CAE</td>\n",
       "      <td>EAE</td>\n",
       "      <td>Todos/denak</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-04-14</td>\n",
       "      <td>Jueves Santo</td>\n",
       "      <td>Ostegun Santua</td>\n",
       "      <td>CAE</td>\n",
       "      <td>EAE</td>\n",
       "      <td>Todos/denak</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-04-15</td>\n",
       "      <td>Viernes Santo</td>\n",
       "      <td>Ostiral Santua</td>\n",
       "      <td>CAE</td>\n",
       "      <td>EAE</td>\n",
       "      <td>Todos/denak</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-04-18</td>\n",
       "      <td>Lunes de Pascua de Resurrección</td>\n",
       "      <td>Pazko Astelehena</td>\n",
       "      <td>CAE</td>\n",
       "      <td>EAE</td>\n",
       "      <td>Todos/denak</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>2022-08-19</td>\n",
       "      <td>Nuestra Señora de Bercijana</td>\n",
       "      <td>Berzijanako Andra Maria</td>\n",
       "      <td>Yécora</td>\n",
       "      <td>Ikeora</td>\n",
       "      <td>Álava - Araba</td>\n",
       "      <td>60.0</td>\n",
       "      <td>42.569303</td>\n",
       "      <td>-2.470064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>Fiesta local</td>\n",
       "      <td>Tokiko jaieguna</td>\n",
       "      <td>Zalduondo</td>\n",
       "      <td>Zalduondo</td>\n",
       "      <td>Álava - Araba</td>\n",
       "      <td>61.0</td>\n",
       "      <td>42.885626</td>\n",
       "      <td>-2.347254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>294</th>\n",
       "      <td>2022-08-05</td>\n",
       "      <td>Fiesta local</td>\n",
       "      <td>Tokiko jaieguna</td>\n",
       "      <td>Zambrana</td>\n",
       "      <td>Zambrana</td>\n",
       "      <td>Álava - Araba</td>\n",
       "      <td>62.0</td>\n",
       "      <td>42.659981</td>\n",
       "      <td>-2.879353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295</th>\n",
       "      <td>2022-08-05</td>\n",
       "      <td>Fiesta local</td>\n",
       "      <td>Tokiko jaieguna</td>\n",
       "      <td>Zigoitia</td>\n",
       "      <td>Zigoitia</td>\n",
       "      <td>Álava - Araba</td>\n",
       "      <td>18.0</td>\n",
       "      <td>42.968200</td>\n",
       "      <td>-2.718300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>2022-09-29</td>\n",
       "      <td>San Miguel</td>\n",
       "      <td>San Miguel</td>\n",
       "      <td>Zuia</td>\n",
       "      <td>Zuia</td>\n",
       "      <td>Álava - Araba</td>\n",
       "      <td>63.0</td>\n",
       "      <td>42.981667</td>\n",
       "      <td>-2844.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>297 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date                    descripcionEs            descriptionEu  \\\n",
       "0    2022-01-01                        Año nuevo          Urteberri eguna   \n",
       "1    2022-01-06               Epifanía del Señor      Errege Magoen eguna   \n",
       "2    2022-04-14                     Jueves Santo           Ostegun Santua   \n",
       "3    2022-04-15                    Viernes Santo           Ostiral Santua   \n",
       "4    2022-04-18  Lunes de Pascua de Resurrección         Pazko Astelehena   \n",
       "..          ...                              ...                      ...   \n",
       "292  2022-08-19      Nuestra Señora de Bercijana  Berzijanako Andra Maria   \n",
       "293  2022-06-11                     Fiesta local          Tokiko jaieguna   \n",
       "294  2022-08-05                     Fiesta local          Tokiko jaieguna   \n",
       "295  2022-08-05                     Fiesta local          Tokiko jaieguna   \n",
       "296  2022-09-29                       San Miguel               San Miguel   \n",
       "\n",
       "    municipalityEs MunicipalityEu      territory  municipalitycode   latwgs84  \\\n",
       "0              CAE            EAE    Todos/denak               NaN        NaN   \n",
       "1              CAE            EAE    Todos/denak               NaN        NaN   \n",
       "2              CAE            EAE    Todos/denak               NaN        NaN   \n",
       "3              CAE            EAE    Todos/denak               NaN        NaN   \n",
       "4              CAE            EAE    Todos/denak               NaN        NaN   \n",
       "..             ...            ...            ...               ...        ...   \n",
       "292         Yécora         Ikeora  Álava - Araba              60.0  42.569303   \n",
       "293    Zalduondo      Zalduondo    Álava - Araba              61.0  42.885626   \n",
       "294     Zambrana       Zambrana    Álava - Araba              62.0  42.659981   \n",
       "295     Zigoitia       Zigoitia    Álava - Araba              18.0  42.968200   \n",
       "296         Zuia           Zuia    Álava - Araba              63.0  42.981667   \n",
       "\n",
       "        lonwgs84  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "4            NaN  \n",
       "..           ...  \n",
       "292    -2.470064  \n",
       "293    -2.347254  \n",
       "294    -2.879353  \n",
       "295    -2.718300  \n",
       "296 -2844.000000  \n",
       "\n",
       "[297 rows x 9 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "4fd9932f-f44f-4367-9c89-6239f40660d8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 297 entries, 0 to 296\n",
      "Data columns (total 9 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   date              297 non-null    object \n",
      " 1   descripcionEs     297 non-null    object \n",
      " 2   descriptionEu     297 non-null    object \n",
      " 3   municipalityEs    297 non-null    object \n",
      " 4   MunicipalityEu    297 non-null    object \n",
      " 5   territory         297 non-null    object \n",
      " 6   municipalitycode  281 non-null    float64\n",
      " 7   latwgs84          282 non-null    float64\n",
      " 8   lonwgs84          282 non-null    float64\n",
      "dtypes: float64(3), object(6)\n",
      "memory usage: 21.0+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "d2e8536d-4d80-438c-aea1-9ca015deb555",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "241f6573-f0e0-406d-a42b-197d917dfcb2",
   "metadata": {},
   "source": [
    "#### **Descarga de datos filtrados**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fa215cb-dd59-4e39-9605-49a8fa6f0814",
   "metadata": {},
   "source": [
    "La sintaxis para extracción de datos vía sql es la siguiente:\n",
    "\n",
    "SELECT campos (separados por comas) FROM tabla WHERE condicion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "6d654eb9-aff9-4d39-87e5-5c795afdd2c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "server = 'sqlc2bcurso.database.windows.net'\n",
    "database = 'CusoC2B'\n",
    "username = 'administrador'\n",
    "password = 'Campus.5678@'   \n",
    "driver= '{ODBC Driver 17 for SQL Server}'\n",
    "\n",
    "db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)\n",
    "cursor = db.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "bbc0b071-a0dc-4e21-8b83-f8eba73fabca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Codigo_Postal</th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "      <th>Transacciones</th>\n",
       "      <th>Importe_Medio</th>\n",
       "      <th>Importe_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_parking</td>\n",
       "      <td>8</td>\n",
       "      <td>9.24</td>\n",
       "      <td>73.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_pharmacy</td>\n",
       "      <td>3</td>\n",
       "      <td>15.40</td>\n",
       "      <td>46.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_taxi</td>\n",
       "      <td>2</td>\n",
       "      <td>19.80</td>\n",
       "      <td>39.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_gas</td>\n",
       "      <td>2</td>\n",
       "      <td>52.98</td>\n",
       "      <td>105.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_fastfood</td>\n",
       "      <td>2</td>\n",
       "      <td>29.25</td>\n",
       "      <td>58.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8996</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_jewelry</td>\n",
       "      <td>1</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8997</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_opticians</td>\n",
       "      <td>1</td>\n",
       "      <td>4.81</td>\n",
       "      <td>4.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8998</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_techsme</td>\n",
       "      <td>1</td>\n",
       "      <td>439.87</td>\n",
       "      <td>439.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8999</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_homesme</td>\n",
       "      <td>1</td>\n",
       "      <td>116.00</td>\n",
       "      <td>116.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9000</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_veterinarian</td>\n",
       "      <td>1</td>\n",
       "      <td>9.85</td>\n",
       "      <td>9.85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9001 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Fecha  Codigo_Postal Tipo_de_Comercio  Transacciones  \\\n",
       "0     2015-01-03          48004       es_parking              8   \n",
       "1     2015-01-03          48004      es_pharmacy              3   \n",
       "2     2015-01-03          48004          es_taxi              2   \n",
       "3     2015-01-03          48004           es_gas              2   \n",
       "4     2015-01-03          48004      es_fastfood              2   \n",
       "...          ...            ...              ...            ...   \n",
       "8996  2016-01-02          48004       es_jewelry              1   \n",
       "8997  2016-01-02          48004     es_opticians              1   \n",
       "8998  2016-01-02          48004       es_techsme              1   \n",
       "8999  2016-01-02          48004       es_homesme              1   \n",
       "9000  2016-01-02          48004  es_veterinarian              1   \n",
       "\n",
       "      Importe_Medio  Importe_Total  \n",
       "0              9.24          73.92  \n",
       "1             15.40          46.20  \n",
       "2             19.80          39.60  \n",
       "3             52.98         105.96  \n",
       "4             29.25          58.50  \n",
       "...             ...            ...  \n",
       "8996         100.00         100.00  \n",
       "8997           4.81           4.81  \n",
       "8998         439.87         439.87  \n",
       "8999         116.00         116.00  \n",
       "9000           9.85           9.85  \n",
       "\n",
       "[9001 rows x 6 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extraer de la tabla transacciones, aquellas realizadas en el CP 48004\n",
    "pd.read_sql_query('SELECT * FROM Transacciones WHERE Codigo_Postal = 48004', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "15f0df38-fed8-495d-8d6b-2688ce77aeb6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Codigo_Postal</th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "      <th>Transacciones</th>\n",
       "      <th>Importe_Medio</th>\n",
       "      <th>Importe_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_parking</td>\n",
       "      <td>8</td>\n",
       "      <td>9.24</td>\n",
       "      <td>73.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_pharmacy</td>\n",
       "      <td>3</td>\n",
       "      <td>15.40</td>\n",
       "      <td>46.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_taxi</td>\n",
       "      <td>2</td>\n",
       "      <td>19.80</td>\n",
       "      <td>39.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_gas</td>\n",
       "      <td>2</td>\n",
       "      <td>52.98</td>\n",
       "      <td>105.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_fastfood</td>\n",
       "      <td>2</td>\n",
       "      <td>29.25</td>\n",
       "      <td>58.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57109</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48010</td>\n",
       "      <td>es_sport</td>\n",
       "      <td>1</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57110</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48010</td>\n",
       "      <td>es_car</td>\n",
       "      <td>1</td>\n",
       "      <td>300.00</td>\n",
       "      <td>300.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57111</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48010</td>\n",
       "      <td>es_pub</td>\n",
       "      <td>1</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57112</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48010</td>\n",
       "      <td>es_leather</td>\n",
       "      <td>1</td>\n",
       "      <td>45.00</td>\n",
       "      <td>45.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57113</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48010</td>\n",
       "      <td>es_opticians</td>\n",
       "      <td>1</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57114 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Fecha  Codigo_Postal Tipo_de_Comercio  Transacciones  \\\n",
       "0      2015-01-03          48004       es_parking              8   \n",
       "1      2015-01-03          48004      es_pharmacy              3   \n",
       "2      2015-01-03          48004          es_taxi              2   \n",
       "3      2015-01-03          48004           es_gas              2   \n",
       "4      2015-01-03          48004      es_fastfood              2   \n",
       "...           ...            ...              ...            ...   \n",
       "57109  2016-01-02          48010         es_sport              1   \n",
       "57110  2016-01-02          48010           es_car              1   \n",
       "57111  2016-01-02          48010           es_pub              1   \n",
       "57112  2016-01-02          48010       es_leather              1   \n",
       "57113  2016-01-02          48010     es_opticians              1   \n",
       "\n",
       "       Importe_Medio  Importe_Total  \n",
       "0               9.24          73.92  \n",
       "1              15.40          46.20  \n",
       "2              19.80          39.60  \n",
       "3              52.98         105.96  \n",
       "4              29.25          58.50  \n",
       "...              ...            ...  \n",
       "57109          50.00          50.00  \n",
       "57110         300.00         300.00  \n",
       "57111         100.00         100.00  \n",
       "57112          45.00          45.00  \n",
       "57113          50.00          50.00  \n",
       "\n",
       "[57114 rows x 6 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extraer de la tabla transacciones, aquellas realizadas en el CP>48004 y CP< 48010\n",
    "pd.read_sql_query('SELECT * FROM Transacciones WHERE Codigo_Postal >= 48004 AND Codigo_Postal <= 48010', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "ede7a723-c378-4fb6-b4da-7fc48bf84a2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "      <th>Transacciones</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>es_parking</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>es_pharmacy</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>es_taxi</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>es_gas</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>es_fastfood</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57109</th>\n",
       "      <td>es_sport</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57110</th>\n",
       "      <td>es_car</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57111</th>\n",
       "      <td>es_pub</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57112</th>\n",
       "      <td>es_leather</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57113</th>\n",
       "      <td>es_opticians</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57114 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Tipo_de_Comercio  Transacciones\n",
       "0           es_parking              8\n",
       "1          es_pharmacy              3\n",
       "2              es_taxi              2\n",
       "3               es_gas              2\n",
       "4          es_fastfood              2\n",
       "...                ...            ...\n",
       "57109         es_sport              1\n",
       "57110           es_car              1\n",
       "57111           es_pub              1\n",
       "57112       es_leather              1\n",
       "57113     es_opticians              1\n",
       "\n",
       "[57114 rows x 2 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extraer de la tabla transacciones la columna Tipo de Comercio y Transacciones, para transacciones aquellas realizadas en el CP>48004 y CP< 48010\n",
    "pd.read_sql_query('SELECT Tipo_de_Comercio, Transacciones FROM Transacciones WHERE Codigo_Postal >= 48004 AND Codigo_Postal <= 48010', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "39cf305d-e1f2-44cb-aa93-a1a61ff893c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "      <th>Transacciones</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>es_parking</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>es_pharmacy</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>es_taxi</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>es_gas</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>es_fastfood</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57109</th>\n",
       "      <td>es_sport</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57110</th>\n",
       "      <td>es_car</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57111</th>\n",
       "      <td>es_pub</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57112</th>\n",
       "      <td>es_leather</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57113</th>\n",
       "      <td>es_opticians</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57114 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Tipo_de_Comercio  Transacciones\n",
       "0           es_parking              8\n",
       "1          es_pharmacy              3\n",
       "2              es_taxi              2\n",
       "3               es_gas              2\n",
       "4          es_fastfood              2\n",
       "...                ...            ...\n",
       "57109         es_sport              1\n",
       "57110           es_car              1\n",
       "57111           es_pub              1\n",
       "57112       es_leather              1\n",
       "57113     es_opticians              1\n",
       "\n",
       "[57114 rows x 2 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Otra manera\n",
    "pd.read_sql_query('SELECT Tipo_de_Comercio, Transacciones FROM Transacciones WHERE Codigo_Postal BETWEEN 48004 AND 48010 ', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db55fe4d-2d68-4965-9b28-6e01210dfaea",
   "metadata": {},
   "source": [
    "**Lógica booleana y operadores en SQL**\n",
    "\n",
    "Algunos de los operadores que nos permiten construir expresiones booleanas son:\n",
    "\n",
    "    > : \"A > B\" devuelve cierto si A es estrictamente mayor que B, de lo contrario devuelve falso.\n",
    "    < : \"A < B\" devuelve cierto si A es estrictamente menor que B, de lo contrario devuelve falso.\n",
    "    = : \"A = B\" devuelve cierto si A es igual a B, de lo contrario devuelve falso.\n",
    "    >= : \"A >= B\" devuelve cierto si A es mayor o igual a B, de lo contrario devuelve falso.\n",
    "    <= : \"A <= B\" devuelve cierto si A es menor o igual a B, de lo contrario devuelve falso.\n",
    "    != : \"A != B\" devuelve cierto si A es distinto a B, de lo contrario devuelve falso.\n",
    "    \n",
    "    AND : \"A and B\" devuelve cierto si A y B valen cierto, y falso en cualquier otro caso.\n",
    "    OR : \"A or B\" devuelve cierto si A o B valen cierto, y falso únicamente cuando tanto A como B valen falso.\n",
    "    NOT : \"not A\" devuelve falso si A vale cierto, y cierto si A vale falso."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "c71513d8-4e35-4130-b392-5ff067c1e07b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Codigo_Postal</th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "      <th>Transacciones</th>\n",
       "      <th>Importe_Medio</th>\n",
       "      <th>Importe_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_parking</td>\n",
       "      <td>8</td>\n",
       "      <td>9.24</td>\n",
       "      <td>73.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>48001</td>\n",
       "      <td>es_jewelry</td>\n",
       "      <td>8</td>\n",
       "      <td>213.56</td>\n",
       "      <td>1708.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>48001</td>\n",
       "      <td>es_hotel</td>\n",
       "      <td>8</td>\n",
       "      <td>83.31</td>\n",
       "      <td>666.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>48009</td>\n",
       "      <td>es_drugstorebig</td>\n",
       "      <td>8</td>\n",
       "      <td>85.15</td>\n",
       "      <td>681.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>48013</td>\n",
       "      <td>es_jewelry</td>\n",
       "      <td>8</td>\n",
       "      <td>99.88</td>\n",
       "      <td>799.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3087</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48009</td>\n",
       "      <td>es_books</td>\n",
       "      <td>8</td>\n",
       "      <td>41.41</td>\n",
       "      <td>331.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3088</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48011</td>\n",
       "      <td>es_cafe</td>\n",
       "      <td>8</td>\n",
       "      <td>34.20</td>\n",
       "      <td>273.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3089</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48011</td>\n",
       "      <td>es_books</td>\n",
       "      <td>8</td>\n",
       "      <td>24.95</td>\n",
       "      <td>199.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3090</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48012</td>\n",
       "      <td>es_pharmacy</td>\n",
       "      <td>8</td>\n",
       "      <td>42.50</td>\n",
       "      <td>340.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3091</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_restaurant</td>\n",
       "      <td>8</td>\n",
       "      <td>58.98</td>\n",
       "      <td>471.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3092 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Fecha  Codigo_Postal Tipo_de_Comercio  Transacciones  \\\n",
       "0     2015-01-03          48004       es_parking              8   \n",
       "1     2015-01-04          48001       es_jewelry              8   \n",
       "2     2015-01-04          48001         es_hotel              8   \n",
       "3     2015-01-04          48009  es_drugstorebig              8   \n",
       "4     2015-01-04          48013       es_jewelry              8   \n",
       "...          ...            ...              ...            ...   \n",
       "3087  2016-01-02          48009         es_books              8   \n",
       "3088  2016-01-02          48011          es_cafe              8   \n",
       "3089  2016-01-02          48011         es_books              8   \n",
       "3090  2016-01-02          48012      es_pharmacy              8   \n",
       "3091  2016-01-02          48014    es_restaurant              8   \n",
       "\n",
       "      Importe_Medio  Importe_Total  \n",
       "0              9.24          73.92  \n",
       "1            213.56        1708.48  \n",
       "2             83.31         666.48  \n",
       "3             85.15         681.20  \n",
       "4             99.88         799.04  \n",
       "...             ...            ...  \n",
       "3087          41.41         331.28  \n",
       "3088          34.20         273.60  \n",
       "3089          24.95         199.60  \n",
       "3090          42.50         340.00  \n",
       "3091          58.98         471.84  \n",
       "\n",
       "[3092 rows x 6 columns]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM Transacciones WHERE (Transacciones = 8)', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f15dc175-e25e-43b0-8630-f4d9fc569d5e",
   "metadata": {},
   "source": [
    "#### **Agregados**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "4be6b91e-6846-461b-b59b-947947f49eca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1786676</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          \n",
       "0  1786676"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total transacciones\n",
    "pd.read_sql_query('SELECT SUM(Transacciones) FROM Transacciones', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "fe69fb72-387e-43cc-a41d-5da3459f5d8a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>110948</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         \n",
       "0  110948"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total registros en tabla\n",
    "pd.read_sql_query('SELECT count(*) FROM Transacciones', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "b64f1c1f-3ea9-409a-896b-5c8edcd4deb7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1786676</td>\n",
       "      <td>110948</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  \n",
       "0  1786676  110948"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Podemos agrupar ambas consultas en una\n",
    "pd.read_sql_query('SELECT SUM(Transacciones), count(*) FROM Transacciones', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "019562e3-4038-43ab-8768-710d31a3522c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     \n",
       "0  16"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Incluso ejecutar operaciones\n",
    "pd.read_sql_query('SELECT SUM(Transacciones) / count(*) FROM Transacciones', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "aa76414d-238a-4333-9134-3caceb053654",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     \n",
       "0  16"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Medias\n",
    "pd.read_sql_query('SELECT avg(Transacciones) FROM Transacciones', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "4cdd8d01-edf2-42ff-9e9f-2cbdd21e9a21",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Sum_Transacciones</th>\n",
       "      <th>Transacciones</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1786676</td>\n",
       "      <td>110948</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Sum_Transacciones  Transacciones\n",
       "0            1786676         110948"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Podemos obtener el resultado desde sql con nombres de columna\n",
    "pd.read_sql_query('SELECT SUM(Transacciones) AS Sum_Transacciones, count(*) AS Transacciones  FROM Transacciones', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7b72fe9-12b3-4fb7-9a19-b20bbef6e6c1",
   "metadata": {},
   "source": [
    "#### **Agrupaciones**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "f55d2eb8-730d-4bd2-8b27-d87fb16dea18",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>es_boatrental</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>es_travelweb</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>es_taxi</td>\n",
       "      <td>2104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>es_mall</td>\n",
       "      <td>502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>es_wellness</td>\n",
       "      <td>527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>es_carrental</td>\n",
       "      <td>292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>es_tobacconists</td>\n",
       "      <td>2637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>es_floristssme</td>\n",
       "      <td>1583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>es_telephony</td>\n",
       "      <td>1467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>es_insurance</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Tipo_de_Comercio  Total\n",
       "0     es_boatrental      6\n",
       "1      es_travelweb      2\n",
       "2           es_taxi   2104\n",
       "3           es_mall    502\n",
       "4       es_wellness    527\n",
       "..              ...    ...\n",
       "62     es_carrental    292\n",
       "63  es_tobacconists   2637\n",
       "64   es_floristssme   1583\n",
       "65     es_telephony   1467\n",
       "66     es_insurance     58\n",
       "\n",
       "[67 rows x 2 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Transacciones totales por tipo de comercio\n",
    "pd.read_sql_query('SELECT Tipo_de_Comercio, count(*) AS Total FROM Transacciones GROUP BY Tipo_de_Comercio', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "140f9b20-8315-46e3-85d7-e20b5c76ff53",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>es_boatrental</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>es_travelweb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>es_taxi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>es_mall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>es_wellness</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>es_carrental</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>es_tobacconists</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>es_floristssme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>es_telephony</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>es_insurance</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Tipo_de_Comercio\n",
       "0     es_boatrental\n",
       "1      es_travelweb\n",
       "2           es_taxi\n",
       "3           es_mall\n",
       "4       es_wellness\n",
       "..              ...\n",
       "62     es_carrental\n",
       "63  es_tobacconists\n",
       "64   es_floristssme\n",
       "65     es_telephony\n",
       "66     es_insurance\n",
       "\n",
       "[67 rows x 1 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Elementos únicos en una columna\n",
    "pd.read_sql_query('SELECT Tipo_de_Comercio FROM Transacciones GROUP BY Tipo_de_Comercio', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "ebd2940a-452e-404b-a022-944d3a35e826",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>es_boatrental</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>es_travelweb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>es_taxi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>es_mall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>es_wellness</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>es_carrental</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>es_tobacconists</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>es_floristssme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>es_telephony</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>es_insurance</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Tipo_de_Comercio\n",
       "0     es_boatrental\n",
       "1      es_travelweb\n",
       "2           es_taxi\n",
       "3           es_mall\n",
       "4       es_wellness\n",
       "..              ...\n",
       "62     es_carrental\n",
       "63  es_tobacconists\n",
       "64   es_floristssme\n",
       "65     es_telephony\n",
       "66     es_insurance\n",
       "\n",
       "[67 rows x 1 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Otra manera\n",
    "pd.read_sql_query('SELECT DISTINCT Tipo_de_Comercio FROM Transacciones', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c314222-4c7f-48f7-9c86-2778fa0993e3",
   "metadata": {},
   "source": [
    "#### **Ordenar los datos**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "ce015ffa-a2a7-4f55-9f73-9c09a635d990",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Codigo_Postal</th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "      <th>Transacciones</th>\n",
       "      <th>Importe_Medio</th>\n",
       "      <th>Importe_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-12-19</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>2500</td>\n",
       "      <td>2.86</td>\n",
       "      <td>7150.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-06-21</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>2440</td>\n",
       "      <td>2.83</td>\n",
       "      <td>6905.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-12-20</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>2438</td>\n",
       "      <td>2.79</td>\n",
       "      <td>6802.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-11-29</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>2406</td>\n",
       "      <td>2.72</td>\n",
       "      <td>6544.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-10-03</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>2405</td>\n",
       "      <td>2.97</td>\n",
       "      <td>7142.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110943</th>\n",
       "      <td>2015-04-23</td>\n",
       "      <td>48008</td>\n",
       "      <td>es_leisuretime</td>\n",
       "      <td>1</td>\n",
       "      <td>63.80</td>\n",
       "      <td>63.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110944</th>\n",
       "      <td>2015-04-23</td>\n",
       "      <td>48008</td>\n",
       "      <td>es_car</td>\n",
       "      <td>1</td>\n",
       "      <td>220.91</td>\n",
       "      <td>220.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110945</th>\n",
       "      <td>2015-04-23</td>\n",
       "      <td>48008</td>\n",
       "      <td>es_homesme</td>\n",
       "      <td>1</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110946</th>\n",
       "      <td>2015-04-23</td>\n",
       "      <td>48008</td>\n",
       "      <td>es_drugstoresme</td>\n",
       "      <td>1</td>\n",
       "      <td>22.50</td>\n",
       "      <td>22.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110947</th>\n",
       "      <td>2015-04-23</td>\n",
       "      <td>48008</td>\n",
       "      <td>es_musicalinstrument</td>\n",
       "      <td>1</td>\n",
       "      <td>30.00</td>\n",
       "      <td>30.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110948 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Fecha  Codigo_Postal      Tipo_de_Comercio  Transacciones  \\\n",
       "0       2015-12-19          48015               es_toll           2500   \n",
       "1       2015-06-21          48015               es_toll           2440   \n",
       "2       2015-12-20          48015               es_toll           2438   \n",
       "3       2015-11-29          48015               es_toll           2406   \n",
       "4       2015-10-03          48015               es_toll           2405   \n",
       "...            ...            ...                   ...            ...   \n",
       "110943  2015-04-23          48008        es_leisuretime              1   \n",
       "110944  2015-04-23          48008                es_car              1   \n",
       "110945  2015-04-23          48008            es_homesme              1   \n",
       "110946  2015-04-23          48008       es_drugstoresme              1   \n",
       "110947  2015-04-23          48008  es_musicalinstrument              1   \n",
       "\n",
       "        Importe_Medio  Importe_Total  \n",
       "0                2.86        7150.00  \n",
       "1                2.83        6905.20  \n",
       "2                2.79        6802.02  \n",
       "3                2.72        6544.32  \n",
       "4                2.97        7142.85  \n",
       "...               ...            ...  \n",
       "110943          63.80          63.80  \n",
       "110944         220.91         220.91  \n",
       "110945         100.00         100.00  \n",
       "110946          22.50          22.50  \n",
       "110947          30.00          30.00  \n",
       "\n",
       "[110948 rows x 6 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM Transacciones ORDER BY Transacciones DESC', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "ff48fd9a-a59d-47f4-83d9-6bc1560a8bba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Codigo_Postal</th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "      <th>Transacciones</th>\n",
       "      <th>Importe_Medio</th>\n",
       "      <th>Importe_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-12-06</td>\n",
       "      <td>48001</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>1250</td>\n",
       "      <td>14.12</td>\n",
       "      <td>17650.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-12-25</td>\n",
       "      <td>48001</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>1221</td>\n",
       "      <td>14.83</td>\n",
       "      <td>18107.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-07-26</td>\n",
       "      <td>48001</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>1090</td>\n",
       "      <td>14.09</td>\n",
       "      <td>15358.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-07-12</td>\n",
       "      <td>48001</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>1089</td>\n",
       "      <td>13.99</td>\n",
       "      <td>15235.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-12-10</td>\n",
       "      <td>48001</td>\n",
       "      <td>es_toll</td>\n",
       "      <td>1085</td>\n",
       "      <td>13.58</td>\n",
       "      <td>14734.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110943</th>\n",
       "      <td>2015-12-16</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_hospital</td>\n",
       "      <td>1</td>\n",
       "      <td>278.00</td>\n",
       "      <td>278.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110944</th>\n",
       "      <td>2015-12-16</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_jewelry</td>\n",
       "      <td>1</td>\n",
       "      <td>99.00</td>\n",
       "      <td>99.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110945</th>\n",
       "      <td>2015-12-16</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_tobacconists</td>\n",
       "      <td>1</td>\n",
       "      <td>22.25</td>\n",
       "      <td>22.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110946</th>\n",
       "      <td>2015-12-16</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_books</td>\n",
       "      <td>1</td>\n",
       "      <td>13.95</td>\n",
       "      <td>13.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110947</th>\n",
       "      <td>2015-12-16</td>\n",
       "      <td>48015</td>\n",
       "      <td>es_fashionsme</td>\n",
       "      <td>1</td>\n",
       "      <td>58.00</td>\n",
       "      <td>58.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110948 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Fecha  Codigo_Postal Tipo_de_Comercio  Transacciones  \\\n",
       "0       2015-12-06          48001          es_toll           1250   \n",
       "1       2015-12-25          48001          es_toll           1221   \n",
       "2       2015-07-26          48001          es_toll           1090   \n",
       "3       2015-07-12          48001          es_toll           1089   \n",
       "4       2015-12-10          48001          es_toll           1085   \n",
       "...            ...            ...              ...            ...   \n",
       "110943  2015-12-16          48015      es_hospital              1   \n",
       "110944  2015-12-16          48015       es_jewelry              1   \n",
       "110945  2015-12-16          48015  es_tobacconists              1   \n",
       "110946  2015-12-16          48015         es_books              1   \n",
       "110947  2015-12-16          48015    es_fashionsme              1   \n",
       "\n",
       "        Importe_Medio  Importe_Total  \n",
       "0               14.12       17650.00  \n",
       "1               14.83       18107.43  \n",
       "2               14.09       15358.10  \n",
       "3               13.99       15235.11  \n",
       "4               13.58       14734.30  \n",
       "...               ...            ...  \n",
       "110943         278.00         278.00  \n",
       "110944          99.00          99.00  \n",
       "110945          22.25          22.25  \n",
       "110946          13.95          13.95  \n",
       "110947          58.00          58.00  \n",
       "\n",
       "[110948 rows x 6 columns]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ordenar por varios campos\n",
    "pd.read_sql_query('SELECT * FROM Transacciones ORDER BY Codigo_Postal, Transacciones DESC', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c5c740b-b77b-4a90-99a8-8b7b7761de36",
   "metadata": {},
   "source": [
    "#### **Operador LIKE**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "2f66addc-a674-4c26-bf66-cd2b7de25dd9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Codigo_Postal</th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>48001</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>48008</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>48009</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>48010</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2938</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48008</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2939</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48009</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2940</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48010</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2941</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48011</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2942</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_hospital</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2943 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Fecha  Codigo_Postal Tipo_de_Comercio\n",
       "0     2015-01-04          48001      es_hospital\n",
       "1     2015-01-04          48004      es_hospital\n",
       "2     2015-01-04          48008      es_hospital\n",
       "3     2015-01-04          48009      es_hospital\n",
       "4     2015-01-04          48010      es_hospital\n",
       "...          ...            ...              ...\n",
       "2938  2016-01-02          48008      es_hospital\n",
       "2939  2016-01-02          48009      es_hospital\n",
       "2940  2016-01-02          48010      es_hospital\n",
       "2941  2016-01-02          48011      es_hospital\n",
       "2942  2016-01-02          48014      es_hospital\n",
       "\n",
       "[2943 rows x 3 columns]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A través de este operador podemos hacer búsquedas que cumplan un determinado criterio\n",
    "pd.read_sql_query(\"SELECT Fecha, Codigo_Postal, Tipo_de_Comercio FROM Transacciones WHERE Tipo_de_Comercio LIKE 'es_hospital' \", db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "02357865-e6d2-4461-9c2d-1ededc7e3fca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Codigo_Postal</th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_parking</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_pharmacy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_taxi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_gas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_fastfood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108000</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_drugstorebig</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108001</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_cafe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108002</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_taxi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108003</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_wellness</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108004</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_leather</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>108005 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Fecha  Codigo_Postal Tipo_de_Comercio\n",
       "0       2015-01-03          48004       es_parking\n",
       "1       2015-01-03          48004      es_pharmacy\n",
       "2       2015-01-03          48004          es_taxi\n",
       "3       2015-01-03          48004           es_gas\n",
       "4       2015-01-03          48004      es_fastfood\n",
       "...            ...            ...              ...\n",
       "108000  2016-01-02          48014  es_drugstorebig\n",
       "108001  2016-01-02          48014          es_cafe\n",
       "108002  2016-01-02          48014          es_taxi\n",
       "108003  2016-01-02          48014      es_wellness\n",
       "108004  2016-01-02          48014       es_leather\n",
       "\n",
       "[108005 rows x 3 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Y la consulta opuesta\n",
    "pd.read_sql_query(\"SELECT Fecha, Codigo_Postal, Tipo_de_Comercio FROM Transacciones WHERE Tipo_de_Comercio NOT LIKE 'es_hospital' \", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba1095f0-28e3-44ad-bb7d-5bf51bab51b6",
   "metadata": {},
   "source": [
    "#### **Extracción de datos NO nulos**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "f6476d4e-2f2d-4220-a30b-16622aaca343",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Codigo_Postal</th>\n",
       "      <th>Tipo_de_Comercio</th>\n",
       "      <th>Transacciones</th>\n",
       "      <th>Importe_Medio</th>\n",
       "      <th>Importe_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_parking</td>\n",
       "      <td>8</td>\n",
       "      <td>9.24</td>\n",
       "      <td>73.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_pharmacy</td>\n",
       "      <td>3</td>\n",
       "      <td>15.40</td>\n",
       "      <td>46.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_taxi</td>\n",
       "      <td>2</td>\n",
       "      <td>19.80</td>\n",
       "      <td>39.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_gas</td>\n",
       "      <td>2</td>\n",
       "      <td>52.98</td>\n",
       "      <td>105.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>48004</td>\n",
       "      <td>es_fastfood</td>\n",
       "      <td>2</td>\n",
       "      <td>29.25</td>\n",
       "      <td>58.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110943</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_cafe</td>\n",
       "      <td>1</td>\n",
       "      <td>36.00</td>\n",
       "      <td>36.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110944</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_hospital</td>\n",
       "      <td>1</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110945</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_taxi</td>\n",
       "      <td>1</td>\n",
       "      <td>22.50</td>\n",
       "      <td>22.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110946</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_wellness</td>\n",
       "      <td>1</td>\n",
       "      <td>22.00</td>\n",
       "      <td>22.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110947</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>48014</td>\n",
       "      <td>es_leather</td>\n",
       "      <td>1</td>\n",
       "      <td>59.00</td>\n",
       "      <td>59.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110948 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Fecha  Codigo_Postal Tipo_de_Comercio  Transacciones  \\\n",
       "0       2015-01-03          48004       es_parking              8   \n",
       "1       2015-01-03          48004      es_pharmacy              3   \n",
       "2       2015-01-03          48004          es_taxi              2   \n",
       "3       2015-01-03          48004           es_gas              2   \n",
       "4       2015-01-03          48004      es_fastfood              2   \n",
       "...            ...            ...              ...            ...   \n",
       "110943  2016-01-02          48014          es_cafe              1   \n",
       "110944  2016-01-02          48014      es_hospital              1   \n",
       "110945  2016-01-02          48014          es_taxi              1   \n",
       "110946  2016-01-02          48014      es_wellness              1   \n",
       "110947  2016-01-02          48014       es_leather              1   \n",
       "\n",
       "        Importe_Medio  Importe_Total  \n",
       "0                9.24          73.92  \n",
       "1               15.40          46.20  \n",
       "2               19.80          39.60  \n",
       "3               52.98         105.96  \n",
       "4               29.25          58.50  \n",
       "...               ...            ...  \n",
       "110943          36.00          36.00  \n",
       "110944          50.00          50.00  \n",
       "110945          22.50          22.50  \n",
       "110946          22.00          22.00  \n",
       "110947          59.00          59.00  \n",
       "\n",
       "[110948 rows x 6 columns]"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"SELECT * FROM Transacciones WHERE Tipo_de_Comercio IS NOT NULL \", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0718e89-dedd-48e3-a22d-df8b92e39da7",
   "metadata": {},
   "source": [
    "#### **Producto cartesiano entre tablas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "f9c59236-648e-4f39-9a21-73c60130ff0c",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>6</td>\n",
       "      <td>Ekaitz</td>\n",
       "      <td>Armario</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>6</td>\n",
       "      <td>Ekaitz</td>\n",
       "      <td>Armario</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>6</td>\n",
       "      <td>Ekaitz</td>\n",
       "      <td>Armario</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "      <td>6</td>\n",
       "      <td>Ekaitz</td>\n",
       "      <td>Armario</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "      <td>6</td>\n",
       "      <td>Ekaitz</td>\n",
       "      <td>Armario</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>7</td>\n",
       "      <td>Agasne</td>\n",
       "      <td>Puerta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>7</td>\n",
       "      <td>Agasne</td>\n",
       "      <td>Puerta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>7</td>\n",
       "      <td>Agasne</td>\n",
       "      <td>Puerta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "      <td>7</td>\n",
       "      <td>Agasne</td>\n",
       "      <td>Puerta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "      <td>7</td>\n",
       "      <td>Agasne</td>\n",
       "      <td>Puerta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>9</td>\n",
       "      <td>Bittor</td>\n",
       "      <td>Ventana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>9</td>\n",
       "      <td>Bittor</td>\n",
       "      <td>Ventana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>9</td>\n",
       "      <td>Bittor</td>\n",
       "      <td>Ventana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "      <td>9</td>\n",
       "      <td>Bittor</td>\n",
       "      <td>Ventana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "      <td>9</td>\n",
       "      <td>Bittor</td>\n",
       "      <td>Ventana</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id Nombre    Ciudad  Id  Nombre   Ciudad\n",
       "0    1  Irati   Algorta   1   Irati    Silla\n",
       "1    2  Koldo    Bilbao   1   Irati    Silla\n",
       "2    3  Ander    Bilbao   1   Irati    Silla\n",
       "3    5  Saioa  Donostia   1   Irati    Silla\n",
       "4    8  Mikel   Mutriku   1   Irati    Silla\n",
       "5    1  Irati   Algorta   2   Koldo     Mesa\n",
       "6    2  Koldo    Bilbao   2   Koldo     Mesa\n",
       "7    3  Ander    Bilbao   2   Koldo     Mesa\n",
       "8    5  Saioa  Donostia   2   Koldo     Mesa\n",
       "9    8  Mikel   Mutriku   2   Koldo     Mesa\n",
       "10   1  Irati   Algorta   6  Ekaitz  Armario\n",
       "11   2  Koldo    Bilbao   6  Ekaitz  Armario\n",
       "12   3  Ander    Bilbao   6  Ekaitz  Armario\n",
       "13   5  Saioa  Donostia   6  Ekaitz  Armario\n",
       "14   8  Mikel   Mutriku   6  Ekaitz  Armario\n",
       "15   1  Irati   Algorta   7  Agasne   Puerta\n",
       "16   2  Koldo    Bilbao   7  Agasne   Puerta\n",
       "17   3  Ander    Bilbao   7  Agasne   Puerta\n",
       "18   5  Saioa  Donostia   7  Agasne   Puerta\n",
       "19   8  Mikel   Mutriku   7  Agasne   Puerta\n",
       "20   1  Irati   Algorta   9  Bittor  Ventana\n",
       "21   2  Koldo    Bilbao   9  Bittor  Ventana\n",
       "22   3  Ander    Bilbao   9  Bittor  Ventana\n",
       "23   5  Saioa  Donostia   9  Bittor  Ventana\n",
       "24   8  Mikel   Mutriku   9  Bittor  Ventana"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"SELECT * FROM TablaA, TablaB\"\"\", db)\n",
    "\n",
    "# Básicamente combina todos los registros de A con todos los de B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "32365506-d0fc-4e99-a0a7-0ad0a14b5584",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id Nombre   Ciudad  Id Nombre Ciudad\n",
       "0   1  Irati  Algorta   1  Irati  Silla\n",
       "1   2  Koldo   Bilbao   2  Koldo   Mesa"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Extracción de datos de ambas tablas en base a un criterio\n",
    "pd.read_sql_query(\"\"\"SELECT * FROM TablaA, TablaB WHERE TablaA.Nombre = TablaB.Nombre\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34066da5-0278-46f4-9c7e-9bd397b62eab",
   "metadata": {},
   "source": [
    "#### **Unión de tablas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "19b80199-952f-4fe9-a69a-473324c503c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id Nombre   Ciudad  Id Nombre Ciudad\n",
       "0   1  Irati  Algorta   1  Irati  Silla\n",
       "1   2  Koldo   Bilbao   2  Koldo   Mesa"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Inner\n",
    "pd.read_sql_query(\"\"\"SELECT * FROM TablaA C INNER JOIN TablaB P ON C.Id = P.Id\"\"\", db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "314c442c-5534-4366-80bb-d30db09f6634",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id Nombre    Ciudad   Id Nombre Ciudad\n",
       "0   1  Irati   Algorta  1.0  Irati  Silla\n",
       "1   2  Koldo    Bilbao  2.0  Koldo   Mesa\n",
       "2   3  Ander    Bilbao  NaN   None   None\n",
       "3   5  Saioa  Donostia  NaN   None   None\n",
       "4   8  Mikel   Mutriku  NaN   None   None"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Left\n",
    "pd.read_sql_query(\"\"\"SELECT * FROM TablaA C LEFT JOIN TablaB P ON C.Id = P.Id\"\"\", db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "9913b99c-9ab5-464e-9e23-47f94cc55a5e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>6</td>\n",
       "      <td>Ekaitz</td>\n",
       "      <td>Armario</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>7</td>\n",
       "      <td>Agasne</td>\n",
       "      <td>Puerta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9</td>\n",
       "      <td>Bittor</td>\n",
       "      <td>Ventana</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id Nombre   Ciudad  Id  Nombre   Ciudad\n",
       "0  1.0  Irati  Algorta   1   Irati    Silla\n",
       "1  2.0  Koldo   Bilbao   2   Koldo     Mesa\n",
       "2  NaN   None     None   6  Ekaitz  Armario\n",
       "3  NaN   None     None   7  Agasne   Puerta\n",
       "4  NaN   None     None   9  Bittor  Ventana"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Rigth\n",
    "pd.read_sql_query(\"\"\"SELECT * FROM TablaA C RIGHT JOIN TablaB P ON C.Nombre = P.Nombre\"\"\", db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "04feb558-2fcd-46bf-ba65-f5133261bf13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Silla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.0</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Ekaitz</td>\n",
       "      <td>Armario</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Agasne</td>\n",
       "      <td>Puerta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Bittor</td>\n",
       "      <td>Ventana</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id Nombre    Ciudad   Id  Nombre   Ciudad\n",
       "0  1.0  Irati   Algorta  1.0   Irati    Silla\n",
       "1  2.0  Koldo    Bilbao  2.0   Koldo     Mesa\n",
       "2  3.0  Ander    Bilbao  NaN    None     None\n",
       "3  5.0  Saioa  Donostia  NaN    None     None\n",
       "4  8.0  Mikel   Mutriku  NaN    None     None\n",
       "5  NaN   None      None  6.0  Ekaitz  Armario\n",
       "6  NaN   None      None  7.0  Agasne   Puerta\n",
       "7  NaN   None      None  9.0  Bittor  Ventana"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Outer\n",
    "pd.read_sql_query(\"\"\"SELECT * FROM TablaA C FULL OUTER JOIN TablaB P ON C.Nombre = P.Nombre\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8023fb2-9cf7-4107-bcf9-a3ed8caceea3",
   "metadata": {},
   "source": [
    "### Operaciones CRUD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "4d9fe43b-12dd-4632-85ff-64018e65f760",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Cerramos el cursor previo\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "5366ee53-9118-485f-8e2a-d4895a14eeef",
   "metadata": {},
   "outputs": [],
   "source": [
    "server = 'sqlc2bcurso.database.windows.net'\n",
    "database = 'CusoC2B'\n",
    "username = 'administrador'\n",
    "password = 'Campus.5678@'   \n",
    "driver= '{ODBC Driver 17 for SQL Server}'\n",
    "\n",
    "db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)\n",
    "cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "9eb97261-c1c3-45ab-a8b7-d2157b6178e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "datos = pd.read_sql_query(\"\"\"SELECT * FROM Personas\"\"\", db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "74795a99-3e0d-49b0-a5d4-0509ce0e3e1d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id Nombre    Ciudad\n",
       "0   1  Irati   Algorta\n",
       "1   2  Koldo    Bilbao\n",
       "2   3  Ander    Bilbao\n",
       "3   5  Saioa  Donostia\n",
       "4   8  Mikel   Mutriku"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datos"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9286a0e5-4b70-4d00-b00d-4935f9ed3693",
   "metadata": {},
   "source": [
    "La implementación del SQL en Azure no sigue el estandar 100, por ello vamos a crear una BBDD SQL virtual (en memoria) contra la que haremos las operaciones CRUD con procedimientos estándar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "1fd21079-c255-4fc0-9049-86d9cf6d2472",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('sqlite://', echo=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "id": "92dbd9f4-b357-45c3-9fc2-3cd89f9613ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>User 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>User 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>User 3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name\n",
       "0  User 1\n",
       "1  User 2\n",
       "2  User 3"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creamos un dataset\n",
    "df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "0e425251-d9eb-49d0-aa04-655220ecf158",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0, 'User 1'), (1, 'User 2'), (2, 'User 3')]"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Insertamos la tabla recién creada en la BBDD.  Hay que tener en cuenta que la BBDD está vacía.\n",
    "df.to_sql('users', con=engine)\n",
    "\n",
    "engine.execute(\"SELECT * FROM users\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "18c61daf-27eb-4f01-9039-1ebca3a25bb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'name' : ['User 4', 'User 5']})\n",
    "df1.to_sql('users', con=engine, if_exists='append') # Las opciones disponibles son fail, append, replace\n",
    "engine.execute(\"SELECT * FROM users\").fetchall()\n",
    "\n",
    "#fail: Si la tabla no existe, devuelve ValueError\n",
    "#replace: Borra la tabla existente y crea una nueva con los nuevos datos\n",
    "#append: Añade nuevos elementos a la tabla existente"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "9f906e83-49e6-4d31-a3fe-e5f912cdfdb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame({'name' : ['User 6', 'User 7']})\n",
    "df2.to_sql('users', con=engine, if_exists='append')\n",
    "engine.execute(\"SELECT * FROM users\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "d59b4cd8-b6a4-4c2d-80bd-050ea8a96c0d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0, 'User 1'),\n",
       " (1, 'User 2'),\n",
       " (2, 'User 3'),\n",
       " (0, 'User 4'),\n",
       " (1, 'User 5'),\n",
       " (0, 'User 6'),\n",
       " (1, 'User 7')]"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "b93fa89f-2f29-41f2-a62d-9bfc511712ad",
   "metadata": {},
   "source": [
    "#### **Inserción de datos en Azure a través de to_sql**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "id": "eef74309-1a48-4114-b74d-d26711962d0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# El proceso varía ligeramente de lo visto en el paso previo\n",
    "from urllib.parse import quote_plus\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine, event\n",
    "import pyodbc\n",
    "\n",
    "# cadena de conexión a azure sql\n",
    "server = 'sqlc2bcurso.database.windows.net'\n",
    "database = 'CusoC2B'\n",
    "username = 'administrador'\n",
    "password = 'Campus.5678@'   \n",
    "driver= '{ODBC Driver 17 for SQL Server}'\n",
    "\n",
    "# crear la conexión\n",
    "conn ='Driver={ODBC Driver 17 for SQL Server};Server=tcp:sqlc2bcurso.database.windows.net,1433;Database=CusoC2B;Uid=administrador;Pwd=Campus.5678@;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'\n",
    "quoted = quote_plus(conn)\n",
    "engine=create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))\n",
    "\n",
    "# carga de datos en la bbdd\n",
    "datos.to_sql('prueba2', engine, index=False, if_exists='append')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "id": "3804de26-983b-4e61-a6af-31cefa8eaa7e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id Nombre    Ciudad\n",
       "0   1  Irati   Algorta\n",
       "1   2  Koldo    Bilbao\n",
       "2   3  Ander    Bilbao\n",
       "3   5  Saioa  Donostia\n",
       "4   8  Mikel   Mutriku\n",
       "0   1  Irati   Algorta\n",
       "1   2  Koldo    Bilbao\n",
       "2   3  Ander    Bilbao\n",
       "3   5  Saioa  Donostia\n",
       "4   8  Mikel   Mutriku"
      ]
     },
     "execution_count": 237,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datos"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9138142a-f851-4a21-a8c2-7bf0a3c31bcf",
   "metadata": {},
   "source": [
    "#### **Inserción de datos en azure.  ¡Ojo! Es necesario que la tabla ya exista en la BBDD.**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "id": "838c98db-934e-48ff-83fa-76dd2a492b5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "server = 'sqlc2bcurso.database.windows.net'\n",
    "database = 'CusoC2B'\n",
    "username = 'administrador'\n",
    "password = 'Campus.5678@'   \n",
    "driver= '{ODBC Driver 17 for SQL Server}'\n",
    "\n",
    "db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)\n",
    "cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "id": "3d4cf9da-3753-4322-b6cf-3ceb2284fcbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Este proceso hace un append sobre la tabla Prueba\n",
    "\n",
    "for index, row in datos.iterrows():\n",
    "     cursor.execute(\"INSERT INTO Prueba (Id,Nombre,Ciudad) values(?,?,?)\", row.Id, row.Nombre, row.Ciudad)\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "id": "11143e22-1801-4956-bc22-82bc5791a3d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Nombre</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>Irati</td>\n",
       "      <td>Algorta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2</td>\n",
       "      <td>Koldo</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>Ander</td>\n",
       "      <td>Bilbao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>5</td>\n",
       "      <td>Saioa</td>\n",
       "      <td>Donostia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>8</td>\n",
       "      <td>Mikel</td>\n",
       "      <td>Mutriku</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id Nombre    Ciudad\n",
       "0    1  Irati   Algorta\n",
       "1    2  Koldo    Bilbao\n",
       "2    3  Ander    Bilbao\n",
       "3    5  Saioa  Donostia\n",
       "4    8  Mikel   Mutriku\n",
       "5    1  Irati   Algorta\n",
       "6    2  Koldo    Bilbao\n",
       "7    3  Ander    Bilbao\n",
       "8    5  Saioa  Donostia\n",
       "9    8  Mikel   Mutriku\n",
       "10   1  Irati   Algorta\n",
       "11   2  Koldo    Bilbao\n",
       "12   3  Ander    Bilbao\n",
       "13   5  Saioa  Donostia\n",
       "14   8  Mikel   Mutriku"
      ]
     },
     "execution_count": 218,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"\"\"SELECT * FROM Prueba\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5806ed9-15bb-459f-b95d-7b037255eaf2",
   "metadata": {},
   "source": [
    "#### **Crear tablas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "id": "2e53466f-acd8-4fb7-bd53-776709d54dda",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Cerramos el cursor previo\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "id": "db8d4530-5b24-40e8-831e-8b195fa14e42",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Supongamos que queremos crear una tabla, llamada Productos, con los siguientes campos:\n",
    "\n",
    "# Variable      Tipo variable\n",
    "# ----------------------------------\n",
    "# idProducto    int (clave primaria)\n",
    "# producto      nvarchar(50)\n",
    "# precio        int\n",
    "\n",
    "# Para generar la nueva tabla..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "id": "7b8b93a7-6ae5-49a2-b4aa-a8aa2e45b59a",
   "metadata": {},
   "outputs": [],
   "source": [
    "server = 'sqlc2bcurso.database.windows.net'\n",
    "database = 'CusoC2B'\n",
    "username = 'administrador'\n",
    "password = 'Campus.5678@'   \n",
    "driver= '{ODBC Driver 17 for SQL Server}'\n",
    "\n",
    "db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)\n",
    "cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e890e9ba-8729-451f-8183-33bfede2bc2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generación de la tabla\n",
    "cursor.execute('''CREATE TABLE Productos (idProducto int primary key, producto nvarchar(50),precio int)''')\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "id": "404ccce4-5410-48b5-987c-d4db6ea7b058",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}