{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "Operaciones básicas en SQLite3 --- 20:37 min\n", "===\n", "\n", "* 20:37 min | Última modificación: Octubre 14, 2021 | [YouTube](https://youtu.be/UoWD3nx1jBs)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Este tutorial esta basado en https://es.hortonworks.com/tutorial/beginners-guide-to-apache-pig/\n", "\n", "En este modulo se usará como base de datos `sqlite3` (www.sqlite.org) la cual es ampliamente utilizada para programación de bases de datos portables. El sitio oficial es https://www.sqlite.org/index.html. SQLite provee una interfaz de línea de comandos preinstalada en muchos sistemas Linux llamada `sqlite3`. También existe una versión para Microsoft Windows. \n", "\n", "En este tutorial se usará la interfaz `pysqlite3` de Python. Los comandos SQL presentados en los ejemplos pueden ser usados directamente en la interfaz de línea de comandos o enviados desde Python. \n", "\n", "En este tutorial se ejemplifica: \n", "\n", "* La carga de datos. \n", "\n", "* El uso básico de consultas.\n", "\n", "* La exportación de resultados." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Fuentes de datos\n", "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Los archivos usados en este tutorial se encuentran almacenados en la carpeta actual" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "/tmp/bonus.csv\n", "/tmp/clients.csv\n", "/tmp/clients_a.csv\n", "/tmp/clients_b.csv\n", "/tmp/data.csv\n", "/tmp/data_1.csv\n", "/tmp/data_2.csv\n", "/tmp/dataset_1.csv\n", "/tmp/dataset_2.csv\n", "/tmp/drivers.csv\n", "/tmp/lines.csv\n", "/tmp/sales.csv\n", "/tmp/specific-columns.csv\n", "/tmp/timesheet.csv\n", "/tmp/truck_event_text_partition.csv\n" ] } ], "source": [ "filenames = [\n", " \"drivers.csv\",\n", " \"timesheet.csv\",\n", " \"truck_event_text_partition.csv\",\n", "]\n", "\n", "url = \"https://raw.githubusercontent.com/jdvelasq/datalabs/master/datasets/drivers/\"\n", "\n", "for filename in filenames:\n", " !wget --quiet {url + filename} -P /tmp/\n", "\n", "!ls -1 /tmp/*.csv" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Preparación\n", "---" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "##\n", "## Apertura de la conexión\n", "##\n", "import sqlite3\n", "\n", "conn = sqlite3.connect(\":memory:\") ## aca se indica el nombre de la db.\n", "cur = conn.cursor()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Carga de los datos de los eventos de los conductores\n", "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "La cabecera del archivo que se desea importar es la siguiente:" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "driverId,truckId,eventTime,eventType,longitude,latitude,eventKey,CorrelationId,driverName,routeId,routeName,eventDate\n", "14,25,59:21.4,Normal,-94.58,37.03,14|25|9223370572464814373,3.66E+18,Adis Cesir,160405074,Joplin to Kansas City Route 2,2016-05-27-22\n", "18,16,59:21.7,Normal,-89.66,39.78,18|16|9223370572464814089,3.66E+18,Grant Liu,1565885487,Springfield to KC Via Hanibal,2016-05-27-22\n", "27,105,59:21.7,Normal,-90.21,38.65,27|105|9223370572464814070,3.66E+18,Mark Lochbihler,1325562373,Springfield to KC Via Columbia Route 2,2016-05-27-22\n", "11,74,59:21.7,Normal,-90.2,38.65,11|74|9223370572464814123,3.66E+18,Jamie Engesser,1567254452,Saint Louis to Memphis Route2,2016-05-27-22\n", "22,87,59:21.7,Normal,-90.04,35.19,22|87|9223370572464814101,3.66E+18,Nadeem Asghar,1198242881, Saint Louis to Chicago Route2,2016-05-27-22\n", "22,87,59:22.3,Normal,-90.37,35.21,22|87|9223370572464813486,3.66E+18,Nadeem Asghar,1198242881, Saint Louis to Chicago Route2,2016-05-27-22\n", "23,68,59:22.4,Normal,-89.91,40.86,23|68|9223370572464813450,3.66E+18,Adam Diaz,160405074,Joplin to Kansas City Route 2,2016-05-27-22\n", "11,74,59:22.5,Normal,-89.74,39.1,11|74|9223370572464813355,3.66E+18,Jamie Engesser,1567254452,Saint Louis to Memphis Route2,2016-05-27-22\n", "20,41,59:22.5,Normal,-93.36,41.69,20|41|9223370572464813344,3.66E+18,Chris Harris,160779139,Des Moines to Chicago Route 2,2016-05-27-22\n" ] } ], "source": [ "!head /tmp/truck_event_text_partition.csv" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "driverId\n", "truckId\n", "eventTime\n", "eventType\n", "longitude\n", "latitude\n", "eventKey\n", "CorrelationId\n", "driverName\n", "routeId\n", "routeName\n", "eventDate\n" ] } ], "source": [ "#\n", "# Nombres de las columnas\n", "#\n", "!head -n 1 /tmp/truck_event_text_partition.csv | tr ',' '\\n'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "En el siguiente código se crea crea la tabla de eventos de los conductores en el sistema; la primera instrucción borra la tabla si ya existe." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "#\n", "# executescript() permite enviar varios comandos de SQL\n", "# en la misma cadena de texto.\n", "#\n", "\n", "conn.executescript(\n", " \"\"\"\n", "DROP TABLE IF EXISTS truck_events;\n", "\n", "CREATE TABLE truck_events (driverId INT, \n", " truckId INT,\n", " eventTime STRING,\n", " eventType STRING, \n", " longitude DOUBLE, \n", " latitude DOUBLE,\n", " eventKey STRING, \n", " correlationId STRING, \n", " driverName STRING,\n", " routeId STRING,\n", " routeName STRING,\n", " eventDate STRING);\n", "\"\"\"\n", ")\n", "conn.commit()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Se verifican las tablas existentes en la base de datos." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('truck_events',)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#\n", "# La función execute() permite enviar únicamente un comando SQL\n", "# al motor de la base de datos. El siguiente comando es equivalente\n", "# a `SHOW TABLES;` en SQL\n", "#\n", "cur.execute(\"SELECT name FROM sqlite_master WHERE type='table';\").fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "A continuación se muestra la información detallada de creación de la tabla `truck_events`." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('CREATE TABLE truck_events (driverId INT, \\n truckId INT,\\n eventTime STRING,\\n eventType STRING, \\n longitude DOUBLE, \\n latitude DOUBLE,\\n eventKey STRING, \\n correlationId STRING, \\n driverName STRING,\\n routeId STRING,\\n routeName STRING,\\n eventDate STRING)',)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#\n", "# La siguiente sentencia devuelve un string que\n", "# representa los comandos sql para crear la tabla\n", "#\n", "cur.execute(\n", " \"SELECT sql FROM sqlite_master WHERE type='table' and name='truck_events';\"\n", ").fetchall()" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CREATE TABLE truck_events (driverId INT, \n", " truckId INT,\n", " eventTime STRING,\n", " eventType STRING, \n", " longitude DOUBLE, \n", " latitude DOUBLE,\n", " eventKey STRING, \n", " correlationId STRING, \n", " driverName STRING,\n", " routeId STRING,\n", " routeName STRING,\n", " eventDate STRING)\n" ] } ], "source": [ "#\n", "# Se imprime el comando sql equivalente para cada\n", "# tabla existente en la base de datos\n", "#\n", "for a in cur.execute(\"SELECT sql FROM sqlite_master WHERE type='table';\").fetchall():\n", " print(a[0])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Carga de datos usando INSERT INTO\n", "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Este método de carga consiste en crear una lista de tupas, donde cada elemento de la tupla es un campo de la tabla. " ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('14',\n", " '25',\n", " '59:21.4',\n", " 'Normal',\n", " '-94.58',\n", " '37.03',\n", " '14|25|9223370572464814373',\n", " '3.66E+18',\n", " 'Adis Cesir',\n", " '160405074',\n", " 'Joplin to Kansas City Route 2',\n", " '2016-05-27-22'),\n", " ('18',\n", " '16',\n", " '59:21.7',\n", " 'Normal',\n", " '-89.66',\n", " '39.78',\n", " '18|16|9223370572464814089',\n", " '3.66E+18',\n", " 'Grant Liu',\n", " '1565885487',\n", " 'Springfield to KC Via Hanibal',\n", " '2016-05-27-22'),\n", " ('27',\n", " '105',\n", " '59:21.7',\n", " 'Normal',\n", " '-90.21',\n", " '38.65',\n", " '27|105|9223370572464814070',\n", " '3.66E+18',\n", " 'Mark Lochbihler',\n", " '1325562373',\n", " 'Springfield to KC Via Columbia Route 2',\n", " '2016-05-27-22')]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Lectura de todo el archivo\n", "with open(\"/tmp/truck_event_text_partition.csv\", \"rt\") as f:\n", " data = f.readlines()\n", "\n", "# Elimina el '\\n' al final de la línea\n", "data = [line.replace('\\n', '') line for line in data]\n", "\n", "# Separa los campos por comas\n", "data = [line.split(\",\") for line in data]\n", "\n", "# Convierte la fila en una tupla\n", "data = [tuple(line) for line in data]\n", "\n", "# Descarta la cabecera\n", "data = data[1:]\n", "\n", "# Imprime los primeros 3 registros para verificar\n", "data[0:3]" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(14,\n", " 25,\n", " '59:21.4',\n", " 'Normal',\n", " -94.58,\n", " 37.03,\n", " '14|25|9223370572464814373',\n", " 3660000000000000000,\n", " 'Adis Cesir',\n", " 160405074,\n", " 'Joplin to Kansas City Route 2',\n", " '2016-05-27-22')]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#\n", "# Carga a partir de la lista de tuplas\n", "# contenidas en data\n", "#\n", "cur.executemany(\"INSERT INTO truck_events VALUES (?,?,?,?,?,?,?,?,?,?,?,?)\", data)\n", "\n", "#\n", "# Verificación\n", "#\n", "cur.execute(\"SELECT * FROM truck_events LIMIT 1;\").fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Consulta de datos" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "La consulta se realiza con `SELECT`, usando la sintaxis convencional:\n", "\n", " SELECT \n", " what \n", " FROM \n", " tbl_name \n", " [WHERE where-conditions] \n", " [ORDER BY column_name];" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(14,\n", " 25,\n", " '59:21.4',\n", " 'Normal',\n", " -94.58,\n", " 37.03,\n", " '14|25|9223370572464814373',\n", " 3660000000000000000,\n", " 'Adis Cesir',\n", " 160405074,\n", " 'Joplin to Kansas City Route 2',\n", " '2016-05-27-22'),\n", " (18,\n", " 16,\n", " '59:21.7',\n", " 'Normal',\n", " -89.66,\n", " 39.78,\n", " '18|16|9223370572464814089',\n", " 3660000000000000000,\n", " 'Grant Liu',\n", " 1565885487,\n", " 'Springfield to KC Via Hanibal',\n", " '2016-05-27-22'),\n", " (27,\n", " 105,\n", " '59:21.7',\n", " 'Normal',\n", " -90.21,\n", " 38.65,\n", " '27|105|9223370572464814070',\n", " 3660000000000000000,\n", " 'Mark Lochbihler',\n", " 1325562373,\n", " 'Springfield to KC Via Columbia Route 2',\n", " '2016-05-27-22')]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cur.execute(\"SELECT * FROM truck_events LIMIT 3;\").fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Obtención de un subconjunto de registros " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "En SQLite3 es posible un subconjunto de datos y almacenarlo en una nueva tabla a partir de una consulta que permita obtener los datos deseados. En el siguiente código, se crea la tabla `truck_events_subset` con los primeros 100 registros de la tabla `truck_events`." ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [], "source": [ "conn.executescript(\n", " \"\"\"\n", "DROP TABLE IF EXISTS truck_events_subset;\n", "\n", "CREATE TABLE truck_events_subset \n", "AS\n", " SELECT *\n", " FROM truck_events\n", " LIMIT 100;\n", "\"\"\"\n", ")\n", "conn.commit()" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(14,\n", " 25,\n", " '59:21.4',\n", " 'Normal',\n", " -94.58,\n", " 37.03,\n", " '14|25|9223370572464814373',\n", " 3660000000000000000,\n", " 'Adis Cesir',\n", " 160405074,\n", " 'Joplin to Kansas City Route 2',\n", " '2016-05-27-22')]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cur.execute(\"SELECT * FROM truck_events_subset LIMIT 1;\").fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Obtención de un subconjunto de campos\n", "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "En el siguiente código se obtienen algunas columnas de la tabla `truck_events_subset` para ser almacenadas en una tabla diferente." ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "conn.executescript(\n", " \"\"\"\n", "DROP TABLE IF EXISTS specific_columns; \n", "\n", "CREATE TABLE specific_columns \n", "AS\n", " SELECT\n", " driverId, \n", " eventTime, \n", " eventType\n", " FROM\n", " truck_events_subset;\n", "\n", "SELECT * FROM specific_columns LIMIT 1;\n", "\"\"\"\n", ")\n", "conn.commit()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "La siguiente consulta obtiene los registros 2 al 5." ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(27, '59:21.7', 'Normal'),\n", " (11, '59:21.7', 'Normal'),\n", " (22, '59:21.7', 'Normal')]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cur.execute(\"SELECT * FROM specific_columns LIMIT 2,3;\").fetchall()" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(11, '59:21.7', 'Normal'),\n", " (11, '59:22.5', 'Normal'),\n", " (11, '59:28.3', 'Normal'),\n", " (11, '59:30.0', 'Normal'),\n", " (11, '59:31.8', 'Normal')]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#\n", "# Registros para el driverId == 11\n", "#\n", "cur.execute(\"SELECT * FROM specific_columns WHERE driverId = 11;\").fetchall()" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('Normal',)]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#\n", "# Tipos de eventos\n", "#\n", "cur.execute(\"SELECT DISTINCT eventType FROM specific_columns;\").fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Escritura de la tabla en el disco\n", "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Seguidamente, se procede a escribir el contenido de la tabla en el directorio actual." ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "14688" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#\n", "# Obtención de los datos como una lista de tuplas\n", "#\n", "data = cur.execute(\"SELECT * FROM truck_events_subset;\").fetchall()\n", "\n", "#\n", "# Conversión de los elementos de la tupla a strings\n", "#\n", "text = [[str(e) for e in row] for row in data]\n", "\n", "#\n", "# Concatenación de strings\n", "#\n", "text = [\",\".join(row) for row in text]\n", "text = \"\\n\".join(text)\n", "\n", "#\n", "# Escritura al disco\n", "#\n", "open(\"/tmp/data.csv\", \"wt\").write(text)" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "14,25,59:21.4,Normal,-94.58,37.03,14|25|9223370572464814373,3660000000000000000,Adis Cesir,160405074,Joplin to Kansas City Route 2,2016-05-27-22\n", "18,16,59:21.7,Normal,-89.66,39.78,18|16|9223370572464814089,3660000000000000000,Grant Liu,1565885487,Springfield to KC Via Hanibal,2016-05-27-22\n", "27,105,59:21.7,Normal,-90.21,38.65,27|105|9223370572464814070,3660000000000000000,Mark Lochbihler,1325562373,Springfield to KC Via Columbia Route 2,2016-05-27-22\n", "11,74,59:21.7,Normal,-90.2,38.65,11|74|9223370572464814123,3660000000000000000,Jamie Engesser,1567254452,Saint Louis to Memphis Route2,2016-05-27-22\n", "22,87,59:21.7,Normal,-90.04,35.19,22|87|9223370572464814101,3660000000000000000,Nadeem Asghar,1198242881, Saint Louis to Chicago Route2,2016-05-27-22\n", "22,87,59:22.3,Normal,-90.37,35.21,22|87|9223370572464813486,3660000000000000000,Nadeem Asghar,1198242881, Saint Louis to Chicago Route2,2016-05-27-22\n", "23,68,59:22.4,Normal,-89.91,40.86,23|68|9223370572464813450,3660000000000000000,Adam Diaz,160405074,Joplin to Kansas City Route 2,2016-05-27-22\n", "11,74,59:22.5,Normal,-89.74,39.1,11|74|9223370572464813355,3660000000000000000,Jamie Engesser,1567254452,Saint Louis to Memphis Route2,2016-05-27-22\n", "20,41,59:22.5,Normal,-93.36,41.69,20|41|9223370572464813344,3660000000000000000,Chris Harris,160779139,Des Moines to Chicago Route 2,2016-05-27-22\n", "32,42,59:22.5,Normal,-90.37,35.21,32|42|9223370572464813296,3660000000000000000,Ryan Templeton,1090292248,Peoria to Ceder Rapids Route 2,2016-05-27-22\n" ] } ], "source": [ "#\n", "# Visualización de la cabecera dek archivo\n", "#\n", "!head /tmp/data.csv" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "29,66,59:35.1,Normal,-95.42,35.87,29|66|9223370572464800713,3660000000000000000,Teddy Choi,803014426,Wichita to Little Rock Route 2,2016-05-27-22\n", "15,51,59:35.1,Normal,-91.14,34.96,15|51|9223370572464800666,3660000000000000000,Rohit Bakshi,1384345811,Joplin to Kansas City,2016-05-27-22\n", "12,104,59:35.3,Normal,-89.65,36.37,12|104|9223370572464800526,3660000000000000000,Paul Codding,24929475,Peoria to Ceder Rapids,2016-05-27-22\n", "20,41,59:35.5,Normal,-88.96,42.25,20|41|9223370572464800335,3660000000000000000,Chris Harris,160779139,Des Moines to Chicago Route 2,2016-05-27-22\n", "31,18,59:35.6,Normal,-94.46,37.16,31|18|9223370572464800225,3660000000000000000,Rommel Garcia,1594289134,Memphis to Little Rock Route 2,2016-05-27-22\n", "27,105,59:35.6,Normal,-92.85,38.93,27|105|9223370572464800175,3660000000000000000,Mark Lochbihler,1325562373,Springfield to KC Via Columbia Route 2,2016-05-27-22\n", "14,25,59:35.8,Normal,-94.46,37.16,14|25|9223370572464800006,3660000000000000000,Adis Cesir,160405074,Joplin to Kansas City Route 2,2016-05-27-22\n", "26,57,59:35.9,Normal,-92.74,37.6,26|57|9223370572464799895,3660000000000000000,Michael Aube,1325712174,Saint Louis to Tulsa Route2,2016-05-27-22\n", "18,16,59:36.3,Normal,-92.42,39.76,18|16|9223370572464799486,3660000000000000000,Grant Liu,1565885487,Springfield to KC Via Hanibal,2016-05-27-22\n", "31,18,59:36.3,Normal,-94.58,37.03,31|18|9223370572464799462,3660000000000000000,Rommel Garcia,1594289134,Memphis to Little Rock Route 2,2016-05-27-22" ] } ], "source": [ "#\n", "# Visualización de la parte final del archivo\n", "#\n", "!tail /tmp/data.csv" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Apéndice --- Manejo de nulos\n", "----" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(1, 'A'), (2, None), (None, 'C'), (None, None), (5, 'E')]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "conn.executescript(\n", " \"\"\"\n", "DROP TABLE IF EXISTS nulltable;\n", "CREATE TABLE nulltable (\n", " col1 INT,\n", " col2 VARCHAR(1)\n", ");\n", "\n", "INSERT INTO nulltable VALUES\n", " (1,\"A\"),\n", " (2,NULL),\n", " (NULL,\"C\"),\n", " (NULL,NULL),\n", " (5,\"E\");\"\"\"\n", ")\n", "conn.commit()\n", "\n", "cur.execute(\"SELECT * FROM nulltable;\").fetchall()" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(None, 'C'), (None, None)]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cur.execute(\"SELECT * FROM nulltable WHERE col1 IS NULL;\").fetchall()" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(2, None), (None, None)]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cur.execute(\"SELECT * FROM nulltable WHERE col2 IS NULL;\").fetchall()" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(1, 'A'), (2, 'Unknown'), ('Unknown', 'C'), ('Unknown', 'Unknown'), (5, 'E')]" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#\n", "# condicionales\n", "#\n", "cur.execute(\n", " \"SELECT IFNULL(col1,'Unknown'), IFNULL(col2,'Unknown') FROM nulltable;\"\n", ").fetchall()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.9" } }, "nbformat": 4, "nbformat_minor": 4 }