cómo trabajar con Python y SQLite1

Vista previa del material en texto

Práctica
de
laboratorio:
cómo
trabajar
con
Python
y
SQLite
Objetivos
Parte
1:
Cree
la
base
de
datos
Parte
2:
Ingrese
los
datos
y
consulte
la
base
de
datos
Parte
3:
Automatice
el
proceso
con
Python
</b>
Aspectos
básicos/situación
En
esta
práctica
de
laboratorio
aprenderá
cómo
conectarse
a
una
base
de
datos
relacional,
realizar
consultas
y
recuperar
los
datos
a
través
de
una
computadora
portátil
de
Jupyter.
También
aprenderá
cómo
transferir
los
datos
de
una
hoja
de
cálculo
de
Microsoft
Excel
a
una
base
de
datos
de
SQLite.
Los
datos
que
se
le
proporcionan
contienen
distintas
mediciones
para
la
velocidad
de
ping,
carga
y
descarga
en
diferentes
autoridades
locales
alrededor
de
Inglaterra.
De
momento,
los
datos
se
almacenan
en
un
archivo
.csv,
pero
creará
una
base
de
datos
y
la
completará
con
la
medición.
Recursos
necesarios
1
computadora
con
acceso
a
Internet
Raspberry
Pi
versión
2
o
superior
Bibliotecas
de
Python:
sqlite3
y
csvkit
Actualización
de
SQL
Introducción
muy
breve
a
las
bases
de
datos
relacionales
(temporales):
http://searchsqlserver.techtarget.com/definition/relational-database
(http://searchsqlserver.techtarget.com/definition/relational-database)</li>
Más
videos
sobre
las
bases
de
datos
relacionales:
https://www.youtube.com/watch?v=jyju2P-
7hPA&list=PLAwxTw4SYaPm4R6j_wzVOCV9fJaiQDYx4
(https://www.youtube.com/watch?v=jyju2P-
7hPA&list=PLAwxTw4SYaPm4R6j_wzVOCV9fJaiQDYx4)</li>
Introducción
a
SQL:
http://www.w3schools.com/sql/sql_intro.asp
(http://www.w3schools.com/sql/sql_intro.asp)</li>
Trabajo
con
SQLite
mediante
la
línea
de
comandos:
https://www.sqlite.org/cli.html
(https://www.sqlite.org/cli.html)
Parte
1:
Cree
la
base
de
datos
http://searchsqlserver.techtarget.com/definition/relational-database
https://www.youtube.com/watch?v=jyju2P-7hPA&list=PLAwxTw4SYaPm4R6j_wzVOCV9fJaiQDYx4
http://www.w3schools.com/sql/sql_intro.asp
https://www.sqlite.org/cli.html
Emplearemos
SQLite
para
la
conexión
a
la
base
de
datos.
SQLite
es
una
biblioteca
que
implementa
un
motor
de
base
de
datos
SQL
transaccional
autónomo
que
elimina
la
necesidad
de
un
servidor
SQL.
Una
utilidad
simple
de
SQLite
llamada
sqlite3
se
puede
instalar
en
Raspberry
Pi.
SQLite
simplifica
considerablemente
el
proceso
de
la
base
de
datos
SQL.
También
utilizaremos
cvskit,
un
conjunto
de
herramientas
diseñadas
para
convertir
diversas
bases
de
datos
al
formato
.csv
(valores
separados
por
comas).
Paso
1:
Configure
el
entorno
de
trabajo.
Necesitaremos
sqlite3
y
csvkit
para
completar
esta
práctica
de
laboratorio.
Los
siguientes
comandos
se
pueden
ingresar
y
ejecutar
directamente
en
un
terminal
de
Raspberry
Pi.
Puede
obtener
acceso
al
terminal
haciendo
clic
en
Nuevo
>
Terminal
en
la
ficha
Inicio
de
su
navegador.
La
ficha
Inicio
es
la
ficha
que
contiene
la
lista
de
prácticas
de
laboratorio
que
utilizó
para
abrir
esta
práctica.
Nota:
Se
recomienda
configurar
la
ficha
terminal
codo
a
codo
con
esta
ficha.
Esto
le
permite
cambiar
fácilmente
de
la
práctica
de
laboratorio
a
la
ventana
terminal.
a)
Instale
SQLite.
A
continuación,
el
primer
comando
actualiza
y
sincroniza
la
lista
de
paquetes
de
Pi
con
el
servidor
repositorio.
Esto
se
realiza
para
asegurar
que
se
utilicen
las
versiones
más
recientes
de
paquetes.
Una
vez
que
la
lista
de
paquetes
está
actualizada,
el
segundo
comando
instala
sqlite3,
un
paquete
de
SQLite
para
Pi
que
contiene
la
utilidad
sqlite3.
Nota:
Dado
que
Raspberry
Pi
tiene
que
tomar
contacto
con
los
servidores
repositorios
del
paquete
para
descargar
e
instalar
los
paquetes,
estos
comandos
pueden
demorar
un
momento
en
terminar
de
ejecutarse.
Ejecute
la
celda
haciendo
clic
en
el
icono
de
reproducción
a
la
izquierda
de
la
celda
de
código
o
seleccione
la
celda
presionando
.
La
ejecución
de
una
celda
de
código
ejecuta
los
comandos
incluidos
en
su
Raspberry
Pi.
Nota:
En
una
computadora
portátil
de
Jupyter,
las
líneas
que
comienzan
con
un
signo
de
exclamación
(!)
son
comandos
que
serán
ejecutados
por
el
terminal
de
Raspberry
Pi.
# apt-get update
# apt-get -y install sqlite3
Después
de
que
el
proceso
de
instalación
finalice,
instale
cvskit
ejecutando
la
celda
de
código
a
continuación:
Nota:
La
instalación
puede
tardar
un
tiempo.
# pip install csvkit
Ahora
Raspberry
Pi
tiene
sqlite3
y
cvskit
instalados
y
listos
para
usarse.
Paso
2:
Trabajo
con
SQLite.
Ahora
que
sqlite3
y
csvkit
están
instalados,
podemos
empezar
a
utilizarlos
para
crear
una
base
de
datos
y
sus
tablas.
Una
tabla
es
un
conjunto
de
información
dispuesto
en
una
matriz
de
filas
y
columnas.
Una
base
de
datos
es
un
conjunto
de
tablas.
Considere
la
tabla
a
continuación:
</tabla>
La
tabla
anterior
contiene
información
acerca
de
ocho
personas,
presentada
en
una
fila
y
columna.
Nota:
Para
garantizar
la
uniformidad
en
las
ubicaciones
del
archivo,
considere
trabajar
desde
el
directorio
/home/pi/notebooks/myfiles.
Para
cambiar
a
ese
directorio,
utilice
el
comando
cd,
que
se
muestra
a
continuación:
# cd /home/pi/notebooks/myfiles
Antes
de
poder
comenzar
a
trabajar
con
una
base
de
datos,
utilice
sqlite3
para
crear
una
nueva
base
de
datos
de
SQLite
denominada
phonebook.db.
Para
crear
la
nueva
base
de
datos,
ejecute
el
siguiente
comando
en
la
ventana
del
terminal:
# sqlite3 phonebook.db
SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage hints. sqlite>
La
utilidad
sqlite3
muestra
información
de
la
versión
y
presenta
su
propmpt,
sqlite>
.
Este
prompt
indica
que
la
base
de
datos
phonebook.db
se
creó
correctamente
y
sqlite3
está
listo
para
ejecutar
los
comandos
SQL
en
ella.
Cree
una
tabla
en
la
base
de
datos.
En
el
prompt
sqlite3,
ejecute
el
siguiente
comando
para
crear
una
tabla
denominada
colegas.
La
tabla
colegas
tiene
3
columnas
(o
campos):
workid,
nombre
y
título.
Observe
que
el
comando
también
especifica
el
tipo
y
la
cantidad
de
datos
que
cada
campo
puede
admitir.
El
campo
workid
admite
valores
de
número
entero.
El
campo
nombre
admite
20
caracteres
varchar.
El
campo
título
acepta
20
caracteres
varchar.
El
campo
número
es
también
un
número
entero.
sqlite> create table coworkers(workid integer,name varchar(20),title varchar(20),number 
integer);
La
misma
base
de
datos
puede
contener
varias
tablas.
En
el
prompt
sqlite3,
utilice
el
primer
comando
siguiente
para
crear
una
segunda
tabla
en
la
base
de
datos
phonebook.db.
El
comando
crea
una
tabla
denominada
departamentos.
Creada
de
manera
similar
a
la
tabla
colegas,
la
tabla
departamentos
contiene
tres
campos:
deptid,
nombre
y
número.
El
segundo
comando
deja
el
prompt
sqlite3
y
regresa
al
Terminal.
sqlite> create table department(deptid integer,name varchar(20),number integer);
sqlite> .quit
Observe
el
punto
"."
antes
del
comando
quit
en
la
celda
anterior.
Por
lo
general,
sqlite3
pasa
las
líneas
ingresadas
por
el
usuario
en
la
biblioteca
de
SQLite
para
su
ejecución.
Sin
embargo,
las
líneas
de
entrada
que
comienzan
con
un
punto
son
interceptadas
e
interpretadas
por
la
utilidad
sqlite3
en
sí.
También
conocidos
como
comandos
de
punto,
estos
comandos
se
utilizan
frecuentemente
para
cambiar
el
formato
de
entrega
de
consultas,
o
ejecutar
determinadas
declaraciones
de
pedido
armadas
previamente.
Las
bases
de
datos
de
SQLite
son
útiles
porque
la
base
de
datos
y
todas
sus
tablas
están
dentro
de
un
único
archivo
de
.db
y
se
almacenan
en
el
disco.
En
el
terminal
de
Pi,
utilice
el
comando
ls
para
enumerar
el
archivo
que
contiene
la
base
de
datos
phonebook.db
y
sus
tablas.
Utilice
el
switch
-l
para
mostrar
el
formato
largo
de
listas
que
permite
ver
los
permisos,
la
propiedad,
el
tamaño,
y
la
fecha
y
la
hora
del
archivo.
# ls -l phonebook.db
-rw-r--r-- 1 root root 3072 Feb 7 01:29 phonebook.db
Parte
2:
Ingrese
los
datos
y
consulte
la
base
de
datos
Paso
1:
Ingrese
los
datos
en
la
base
de
datos.
Ingrese
el
prompt
sqlite3
nuevamente,
mientras
selecciona
la
base
de
datos
phonebook.db.
# sqlite3 phonebook.db
SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage hints. sqlite>
Use
el
siguiente
comando
para
agregar
una
entrada
a
la
tabla
colegas:sqlite> insert into coworkers values(101,'Han Solo','Bounty Hunter',5556667578);
El
comando
anterior
inserta
una
fila
de
cuatro
valores
en
la
tablacolegas
de
la
siguiente
manera:
101
en
la
columna
workid
Han
Solo
en
la
columna
nombre
Bounty
Hunter
en
la
columna
título
5556667578
en
la
columna
número
Mientras
que
el
comando
anterior
es
bastante
sencillo,
no
funciona
bien.
Otra
opción
es
importar
un
archivo
.csv
directamente
a
la
base
de
datos.
La
estructura
de
la
tabla
se
crea
según
la
estructura
del
archivo
.csv.
Los
siguientes
pasos
crean
el
archivo
.csv
para
importar
a
la
base
de
datos.
En
un
entorno
de
producción,
los
archivos
.csv
ya
existirán
probablemente.
a)
Seleccione
y
copie
el
texto
que
se
muestra
a
continuación
al
tablero.
Una
vez
seleccionado
el
texto,
haga
clic
con
el
botón
secundario
en
el
texto
seleccionado
y
seleccione
Copiar
para
copiar
el
texto
al
tablero.
workid,
nombre,
título,
número
101,Han
Solo,Bounty
Hunter,5556667578
102,Leia
Skywalker,Princess,5556542398
103,Luke
Skywalker,Jedi,5558963267
104,Obi-Wan
Kenobi,Jedi,5558963276
105,Anakin
Skywalker,Sith
Lord,5553477621
106,Jabba
The
Hutt,Gangster,5556613456
107,Greedo,Debt
Collector,5552360918
108,R2D2,Astro
Droid,5555210125
109,C3PO,Protocol
Droid,5556633345
b)
En
el
terminal
de
Pi,
comience
nano.
Nano
es
un
editor
de
texto
de
la
línea
de
comandos
y
es
muy
fácil
de
usar.
Use
el
siguiente
comando
para
comenzar
nano
y
crear
un
nuevo
archivo
de
texto
llamado
coworkers.csv
# nano coworkers.csv
c)
Pegue
el
texto
copiado
en
la
ventana
nano.
Asegúrese
de
eliminar
cualquier
línea
en
blanco
del
archivo.
d)
Cuando
el
texto
se
haya
pegado
en
el
archivo
coworkers.csv
y
se
hayan
eliminado
las
líneas
vacías,
presione
CONTROL+X
para
salir
de
nano.
e)
Presione
“Y”
(Sí)
cuando
nano
le
pregunte
si
desea
guardar
el
archivo.
f)
Para
consultar
el
contenido
del
archivo
CSV
recién
creado,
utilice
el
comando
cat,
como
se
muestra
a
continuación:
# cat coworkers.csv
O
bien,
puede
crear
el
mismo
archivo
CSV
automáticamente
a
través
de
esta
computadora
portátil
de
Jupyter.
Para
crear
el
archivo
CSV
automáticamente
con
la
computadora
portátil
de
Jupyter,
ejecute
la
celda
de
código
a
continuación:
In [16]:
Nuevamente,
utilice
el
comando
cat
para
verificar
la
creación
de
archivos
y
su
contenido.
# cat coworkers.csv
Paso
2:
Importe
los
datos
en
la
base
de
datos.
Primero,
elimine
la
tabla
colegas
de
la
base
de
datos
phonebook.
Debido
a
que
la
estructura
de
la
tabla
se
define
mediante
la
estructura
del
archivo
.csv
y
hay
sólo
una
entrada
en
la
tabla,
es
más
fácil
eliminar
la
tabla
en
conjunto.
En
el
terminal,
ingrese
el
prompt
sqlite3
mientras
selecciona
la
base
de
datos
phonebook.db:
# sqlite3 phonebook.db SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage 
hints. sqlite>
Utilice
el
comando
.tables
para
enumerar
todas
las
tablas
contenidas
actualmente
en
la
base
de
datos:
sqlite>.tables
coworkers department
Dos
tablas,
colegas
y
departamento
están
actualmente
presentes
en
la
base
de
datos.
Utilice
el
comando
de
SQL
drop
para
eliminar
la
tabla
colegas:
sqlite> drop table coworkers;
Ejecute
el
comando
.tables
nuevamente
para
enumerar
todas
las
tablas
actualmente
presentes
en
la
base
de
datos:
sqlite>.tables
department
Observe
que
la
tabla
colegas
se
ha
eliminado
de
la
base
de
datos.
Deje
el
prompt
sqlite3
y
regrese
al
terminal:
sqlite>.quit
En
el
terminal,
utilice
la
utilidad
csvsql
para
crear
una
nueva
tabla
en
la
base
de
datos
phonebook
y
recuperar
los
datos
contenidos
en
el
archivo
coworkers.csv.
csvsql
es
parte
del
paquete
csvkit
instalado
anteriormente
en
esta
práctica
de
laboratorio.
# csvsql --db sqlite:////home/pi/notebooks/myfiles/phonebook.db --insert 
/home/pi/notebooks/myfiles/coworkers.csv
Ingrese
el
prompt
sqlite3
mientras
selecciona
la
base
de
datos
phonebook
y
ejecute
el
comando
.tables
para
enumerar
todas
las
tablas
en
la
base
de
datos:
# sqlite3 phonebook.db SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage 
hints. sqlite>
sqlite>.tables
coworkers department
La
tabla
colegas
está
nuevamente
en
la
base
de
datos.
Paso
3:
Consulte
la
base
de
datos.
En
el
terminal,
ingrese
el
prompt
sqlite3
mientras
selecciona
la
base
de
datos
phonebook.db:
# sqlite3 phonebook.db SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage 
hints. sqlite>
Enumere
todo
el
contenido
de
la
tabla
colegas:
sqlite> select * from coworkers; 101|Han Solo|Bounty Hunter|5556667578 102|Leia 
Skywalker|Princess|5556542398 103|Luke Skywalker|Jedi|5558963267 104|Obi-Wan 
Kenobi|Jedi|5558963276 105|Anakin Skywalker|Sith Lord|5553477621 106|Jabba The 
Hutt|Gangster|5556613456 107|Greedo|Debt Collector|5552360918 108|R2D2|Astro 
Droid|5555210125 109|C3PO|Protocol Droid|5556633345 sqlite>
En
el
comando
de
SQL
select
que
se
muestra
arriba,
el
carácter
de
asterisco
(*)
selecciona
todos
los
campos
en
la
tabla
colegas.
Para
mostrar
los
nombres
de
las
entradas
con
el
cargo
de
Jedi,
utilice
el
siguiente
comando:
sqlite> select name from coworkers where title='Jedi'; Luke Skywalker Obi-Wan Kenobi 
sqlite>
¿Puede
crear
una
consulta
para
mostrar
los
nombres
de
los
cazadores
de
tesoros
en
la
tabla
colegas?
*Escriba
su
respuesta
aquí.*
Parte
3:
Automatice
el
proceso
con
Python
Utilice
Python
para
conectarse
a
la
base
de
datos
y
ejecutar
algunas
consultas
para
saber
cómo
lucen
estos
datos.
Paso
1:
Importe
los
módulos.
Para
este
paso,
serán
necesarios
los
siguientes
módulos:
sqlite3
pandas
pyplot
A
continuación
se
detallan
las
líneas
de
código
para
importar
los
módulos
requeridos
de
Python:
# this is the library to talk to the database
import sqlite3
import pandas as pd
# this is a library for data visualization
from matplotlib import pyplot as plt
# this is to say that we want our plots to be part of this page, and not opened in a new 
window
%matplotlib inline
Paso
2:
Conéctese
a
la
base
de
datos
Antes
de
que
las
consultas
puedan
configurarse
en
la
base
de
datos
a
través
de
Python,
debe
establecerse
una
conexión
con
la
base
de
datos.
El
objeto
de
Python
sqlite3
tiene
un
método
connect()
que
facilita
la
conexión
con
la
base
de
datos.
Nota:
Si
bien
el
objeto
de
Python
y
la
utilidad
de
la
línea
de
comando
utilizados
hasta
ahora
en
esta
práctica
de
laboratorio
comparten
el
mismo
nombre,
sqlite3,
su
alcance
de
uso
es
diferente.
El
último
debe
ejecutarse
desde
Python
mientras
que
el
primero
requiere
un
terminal
de
Linux
para
su
ejecución.
Para
utilizar
el
objeto
de
Python
sqlite3
para
abrir
una
conexión,
ejecute
el
método
connect
del
objeto
sqlite3.
Este
método
toma
una
cadena
que
contiene
el
nombre
de
la
base
de
datos
para
la
cual
la
conexión
es
necesaria.
En
este
caso,
el
comando
será:
sqlite3.connect('/home/pi/notebooks/myfiles/phonebook.db')
Esta
función
regresa
la
conexión,
que
se
almacena
en
una
variable
llamada
conn.
Almacenar
los
detalles
de
la
conexión
en
una
variable
hace
más
sencillo
volver
a
esa
misma
conexión
cuando
es
necesario.
La
línea
de
código
que
contiene
la
asignación
de
la
variable
y
el
establecimiento
de
la
conexión
es
la
siguiente:
conn = sqlite3.connect('/home/pi/notebooks/myfiles/phonebook.db')
Ahora
que
la
conexión
a
la
base
de
datos
se
ha
establecido
y
sus
detalles
están
guardados
y
son
accesibles
mediante
la
variable
conn,
es
necesaria
una
manera
de
navegar
por
la
base
de
datos.
En
este
caso,
utilizaremos
otro
método
del
objeto
sqlite3
denominado
cursor().
Utilizado
como
puntero
deslizable
en
la
base
de
datos,
un
cursor
es
una
estructura
de
control
que
nos
permitirá
navegar
por
las
tablas
y
los
registros.
El
cursor
es
importante
porque
especifica
en
qué
celda
(tabla,
columna
y
fila)
se
leerá
o
escribirá
contenido.
Para
crear
el
cursor,
ejecute
el
método
cursor()
en
una
conexión
de
la
base
de
datos
establecida.
Dado
que
guardamos
los
detalles
de
la
conexión
a
la
base
de
datos
phonebook
en
la
variable
conn,
utilice
esa
variable
para
crear
un
cursor,
de
la
siguiente
manera:
conn.cursor()
Utilice
otra
variable
para
guardar
losdetalles
del
objeto
de
cursor
recién
creado.
La
línea
de
código
a
continuación
guarda
los
detalles
del
cursor
en
la
variable
cur:
cur = conn.cursor()
A
continuación
se
presenta
el
programa
integral
de
Python
hasta
ahora:
In [18]:
Paso
3:
Ejecute
una
consulta.
Para
ejecutar
consultas
en
la
base
de
datos,
utilice
el
método
execute().
Escrito
como
método
del
objeto
de
cursor,
este
método
toma,
como
entrada,
una
cadena
que
contiene
la
consulta
que
nos
interesa.
Para
facilitar
un
poco
el
programa,
guardaremos
la
consulta
en
una
variable
llamada
query.
El
método
de
ejecución
es
el
siguiente:
query = "SELECT name FROM coworkers;"
cur.execute(query)
Ejecute
la
celda
a
continuación
para
realizar
una
consulta
y
guardar
los
resultados
en
cur.
In [21]:
Los
resultados
de
la
consulta
se
guardan
en
el
objeto
de
cur.
Para
mostrar
los
resultados,
debemos
iterar
a
través
de
objeto
de
cur,
e
imprimir
cada
fila.
Ejecute
la
celda
a
continuación
para
iterar
a
través
de
cur
e
imprima
su
contenido:
In [22]:
Desafío
1)
Proporcione
el
código
para
consultar
los
nombres
de
todas
las
princesas.
*Escriba
su
respuesta
aquí.*
2)
Proporcione
el
código
para
consultar
los
nombres
de
todas
las
princesas
y
los
recaudadores.
*Escriba
su
respuesta
aquí.*
3)
Proporcione
el
código
para
consultar
los
nombres
y
los
números
de
todos
los
Jedi.
*Escriba
su
respuesta
aquí.*
Out[21]: <sqlite3.Cursor at 0x6d42eb20>
(101, u'Han Solo', u'Bounty Hunter', 5556667578L) 
(102, u'Leia Skywalker', u'Princess', 5556542398L) 
(103, u'Luke Skywalker', u'Jedi', 5558963267L) 
(104, u'Obi-Wan Kenobi', u'Jedi', 5558963276L) 
(105, u'Anakin Skywalker', u'Sith Lord', 5553477621L) 
(106, u'Jabba The Hutt', u'Gangster', 5556613456L) 
(107, u'Greedo', u'Debt Collector', 5552360918L) 
(108, u'R2D2', u'Astro Droid', 5555210125L) 
(109, u'C3PO', u'Protocol Droid', 5556633345L) 
4)
Proporcione
el
código
para
consultar
los
nombres
de
los
droids.
*Escriba
su
respuesta
aquí.*
©
2017
Cisco
y/o
sus
filiales.
Todos
los
derechos
reservados.
Este
documento
es
información
pública
de
Cisco.
ID Nombre Género Raza Fuerza
sensible
001 Han Masculino Humano No
002 Leia Femenino Humano Sí
003 Luke Masculino Humano Sí
004 Obi-Wan Masculino Humano Sí
005 Anakin Masculino Humano Sí
006 Jabba Masculino Hutt No
007 Greedo Masculino Rodian No
008 R2D2 N/D Droid No
009 C3PO N/D Droid No