{
"cells": [
{
"cell_type": "markdown",
"id": "e3da6b37",
"metadata": {},
"source": [
"# Datasets, Pandas DataFrames and Databases"
]
},
{
"cell_type": "markdown",
"id": "01876e7e",
"metadata": {},
"source": [
"## Abstract"
]
},
{
"cell_type": "markdown",
"id": "e8a1728b",
"metadata": {},
"source": [
"Para la adquisición de datos, en general se dispone de archivos que contienen los datos almacenados. Sin embargo, el modo más común para persistir información es en bases de datos.\n",
"En esta notebook se realizarán operaciones básicas con bases de datos. En particular se utiliza SQLite interactuando con DataFrames Pandas."
]
},
{
"cell_type": "markdown",
"id": "0e081dc0",
"metadata": {},
"source": [
"## 1. Introduccion"
]
},
{
"cell_type": "markdown",
"id": "08b1d8cc",
"metadata": {},
"source": [
"### Acerca de SQLite"
]
},
{
"cell_type": "markdown",
"id": "3d7979a5",
"metadata": {},
"source": [
"\"SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world with more than a trillon of databeses (1e12) in use.\" (del Sitio oficial: https://www.sqlite.org/index.html).
\n",
"SQLite permite crear una base de datos SQL en tiempo de ejecución con solo importar una librería y crear el archivo. Mediante sentencias SQL puede, insertar datos, consultarlos, modificar tablas, etc. Puede navegar las bases de datos con https://sqlitebrowser.org/. Y puede eliminar la DB símplemente eliminando el archivo.\n",
"
\n",
"Para descargar e instalar Sqlite Browser: https://sqlitebrowser.org/dl/
\n",
"
\n",
"Para instalar el módulo SQLlite:
\n",
"- conda install sqlite3 (instalar desde Anaconda)
\n",
"- pip install sqlite3 (instalar desde Terminal - Linux / MacOS - o desde DOS / Powershell - Windows)
\n",
"\n"
]
},
{
"cell_type": "markdown",
"id": "a9a990fa",
"metadata": {},
"source": [
"### Librerias"
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "fb7fe131",
"metadata": {},
"outputs": [],
"source": [
"import sqlite3\n",
"import pandas as pd\n",
"import datetime as datetime\n",
"import os\n",
"#\n",
"# El separador presentado abajo es multi OS.\n",
"separador = os.path.sep"
]
},
{
"cell_type": "markdown",
"id": "fec08141",
"metadata": {},
"source": [
"En este ejemplo, cargaremos un dataset desde un archivo, crearemos una base de datos y almacenaremos el daframe en la base de datos."
]
},
{
"cell_type": "markdown",
"id": "4b17b6aa",
"metadata": {},
"source": [
"## 2. DDL"
]
},
{
"cell_type": "markdown",
"id": "21cb5921",
"metadata": {},
"source": [
"Dentro de SQL, un sublenguaje es DDL (Data Definition Languaje). Consiste en un conjunto de sentencias para la definición y modificación de la base de datos y sus tablas."
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "b49f1b6a",
"metadata": {},
"outputs": [],
"source": [
"# Cargamos los datos desde un CSV\n",
"archivoStrokesCsv='datasets'+str(separador)+'train_strokes.csv'\n",
"df=pd.read_csv(archivoStrokesCsv)"
]
},
{
"cell_type": "code",
"execution_count": 18,
"id": "ed0ed116",
"metadata": {},
"outputs": [],
"source": [
"# Crearemos una base de datos, el cursor y la conexión\n",
"nombreDB='datasets'+str(separador)+'train_strokes.db'\n",
"conexion = sqlite3.connect(nombreDB, uri=True)\n",
"cursor = conexion.cursor()"
]
},
{
"cell_type": "markdown",
"id": "8cfcb2e9",
"metadata": {},
"source": [
"### Crear Tablas"
]
},
{
"cell_type": "code",
"execution_count": 19,
"id": "84974d4b",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"se creo la tabla employees\n",
"se creo la tabla strokes\n"
]
}
],
"source": [
"# Esta tabla se crea para ser eliminada (y poder mostrarlo)\n",
"sentencia=\"\"\"CREATE TABLE employees(\n",
" id integer PRIMARY KEY, \n",
" name text, \n",
" salary real, \n",
" department text, \n",
" position text, \n",
" startDate text\n",
" )\"\"\"\n",
"try:\n",
" cursor.execute(sentencia)\n",
" print(\"se creo la tabla employees\")\n",
"except sqlite3.OperationalError:\n",
" print(\"Tabla existente\")\n",
"\n",
"# Esta tabla se crea para persistir.\n",
"sentence=\"\"\"CREATE TABLE strokes(\n",
" id integer PRIMARY KEY AUTOINCREMENT,\n",
" gender text,\n",
" age real,\n",
" hypertension real,\n",
" heart_disease real,\n",
" ever_married text,\n",
" work_type text,\n",
" Residence_type text,\n",
" avg_glucose_level real,\n",
" bmi real,\n",
" smoking_status text,\n",
" stroke real\n",
" )\"\"\"\n",
"try:\n",
" cursor.execute(sentence)\n",
" print(\"se creo la tabla strokes\")\n",
"except sqlite3.OperationalError:\n",
" print(\"Tabla existente\")"
]
},
{
"cell_type": "markdown",
"id": "789b0b34",
"metadata": {},
"source": [
"### Eliminar Tablas"
]
},
{
"cell_type": "code",
"execution_count": 20,
"id": "7076f2c7",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"se elimino la tabla employees\n"
]
}
],
"source": [
"# Eliminación de tabla creada\n",
"sentencia=\"\"\"drop table employees\"\"\"\n",
"try:\n",
" cursor.execute(sentencia)\n",
" print(\"se elimino la tabla employees\")\n",
"except:\n",
" print(\"Error operacional\")"
]
},
{
"cell_type": "markdown",
"id": "26e0a067",
"metadata": {},
"source": [
"#### Confirmación: Navegamos con DB Browser"
]
},
{
"cell_type": "markdown",
"id": "f997342b",
"metadata": {},
"source": [
"Abrimos la DB \"train_strokes.db\" y abrimos la tabla \"strokes\"\n",
""
]
},
{
"cell_type": "markdown",
"id": "14ed27ff",
"metadata": {},
"source": [
"## 3. DML"
]
},
{
"cell_type": "markdown",
"id": "9d831903",
"metadata": {},
"source": [
"DML (Data Manipulation Languaje) es un sub-lenguaje de SQL. Es un conjunto de sentencias para la manipulación de los datos almacenados."
]
},
{
"cell_type": "code",
"execution_count": 21,
"id": "d4bb5240",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n", " | id | \n", "gender | \n", "age | \n", "hypertension | \n", "heart_disease | \n", "ever_married | \n", "work_type | \n", "Residence_type | \n", "avg_glucose_level | \n", "bmi | \n", "smoking_status | \n", "stroke | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "30669 | \n", "Male | \n", "3.0 | \n", "0 | \n", "0 | \n", "No | \n", "children | \n", "Rural | \n", "95.12 | \n", "18.0 | \n", "0 | \n", "0 | \n", "
1 | \n", "30468 | \n", "Male | \n", "58.0 | \n", "1 | \n", "0 | \n", "Yes | \n", "Private | \n", "Urban | \n", "87.96 | \n", "39.2 | \n", "never smoked | \n", "0 | \n", "
2 | \n", "16523 | \n", "Female | \n", "8.0 | \n", "0 | \n", "0 | \n", "No | \n", "Private | \n", "Urban | \n", "110.89 | \n", "17.6 | \n", "0 | \n", "0 | \n", "
3 | \n", "56543 | \n", "Female | \n", "70.0 | \n", "0 | \n", "0 | \n", "Yes | \n", "Private | \n", "Rural | \n", "69.04 | \n", "35.9 | \n", "formerly smoked | \n", "0 | \n", "
4 | \n", "46136 | \n", "Male | \n", "14.0 | \n", "0 | \n", "0 | \n", "No | \n", "Never_worked | \n", "Rural | \n", "161.28 | \n", "19.1 | \n", "0 | \n", "0 | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
43395 | \n", "56196 | \n", "Female | \n", "10.0 | \n", "0 | \n", "0 | \n", "No | \n", "children | \n", "Urban | \n", "58.64 | \n", "20.4 | \n", "never smoked | \n", "0 | \n", "
43396 | \n", "5450 | \n", "Female | \n", "56.0 | \n", "0 | \n", "0 | \n", "Yes | \n", "Govt_job | \n", "Urban | \n", "213.61 | \n", "55.4 | \n", "formerly smoked | \n", "0 | \n", "
43397 | \n", "28375 | \n", "Female | \n", "82.0 | \n", "1 | \n", "0 | \n", "Yes | \n", "Private | \n", "Urban | \n", "91.94 | \n", "28.9 | \n", "formerly smoked | \n", "0 | \n", "
43398 | \n", "27973 | \n", "Male | \n", "40.0 | \n", "0 | \n", "0 | \n", "Yes | \n", "Private | \n", "Urban | \n", "99.16 | \n", "33.2 | \n", "never smoked | \n", "0 | \n", "
43399 | \n", "36271 | \n", "Female | \n", "82.0 | \n", "0 | \n", "0 | \n", "Yes | \n", "Private | \n", "Urban | \n", "79.48 | \n", "20.6 | \n", "never smoked | \n", "0 | \n", "
43400 rows × 12 columns
\n", "