Módulo Sqlite3
Python DB-API es un conjunto de estándares recomendados por un grupo de interés especial para la estandarización de módulos de base de datos. Los estándares DB-API se modificaron aún más en DB-API 2.0 mediante otra propuesta de mejora que se específica en el PEP-249
Por otra parte SQLite es una biblioteca que implementa un motor de base de datos SQL ligero y autónomo. A diferencia de otros sistemas de gestión de base de datos, SQLite no require un servidor separado para funcionar. Todo se almacena en un archivo en nuestro sistema de archivos, lo que lo convierte en una opción ideal para aplicaciones pequeñas ( crearemos una en este post ), prototipos y otros desarrollos de caracter rápido.
Conexión a una Base De Datos
Según los estándares prescritos, el primer paso del proceso es obtener la conexión con el objeto que representa la base de datos.
Para establecer una conexión con la base de datos SQLite, es necesario importar el módulo sqlite3 y ejecutar el método connect():
1
2
3
import sqlite3
db_connection = sqlite3.connect("cars.db")
Métodos de un objeto Conexión
El método connect() devuelve un objeto de conexión que hace referencia a la base de datos existente o una nueva base de datos si no existe.
Los siguientes métodos del objeto conexión son útiles:
| Método | Descripción |
|---|---|
cursor() | Devuelve un objeto cursor que usa esta conexión. |
commit() | Compromete explícitamente cualquier transacción pendiente a la base de datos. |
rollback() | Este método opcional hace que una transacción se retrotraiga al punto de partida. |
close() | Cierra la conexión a la base de datos de forma permanente. |
Los métodos commit() y rollback() del objeto de conexión garantizan el control de transacciones. Ejemplo:
1
2
3
4
5
6
7
8
9
10
import sqlite3
db = sqlite3.connect("university.db")
try:
db.execute("Query")
db.commit() # confirmamos explícitamente los cambios
else:
print("Error")
db.rollback() # nos volvemos al punto de partida
db.close()
Crear un Cursor
Un cursor es un objeto de Python que le permite trabajar con la base de datos, actúa como un identificador para una consulta SQL determinada; permite la recuperación de una o más filas del resultado. Para crear un cursor utilizamos el objeto de conexión de la siguiente forma:
1
2
3
4
import sqlite3
db_connection = sqlite3.connect('university.db')
cursor = db_connection.cursor()
Métodos de un objeto Cursor
Los siguientes métodos del objeto cursor son útiles:
| Método | Descripción |
|---|---|
execute() | Ejecuta la consulta SQL como parámetro de cadena. |
executemany() | Ejecuta la consulta SQL usando un conjunto de parámetros en la lista de tuplas. |
fetchone | Obtiene la siguiente fila del conjunto de resultado de la consulta. |
fetchall() | Obtiene todas las filas restante del conjunto de resultados de la consulta. |
close() | Cierra el objeto cursor. |
CRUD de Consola
Ahora vamos hacer un CRUD completo, para que tengamos una aplicación de consola. ¿Entretenido?
Para facilitarnos la existencia, vamos a modularizar el código, comenzando por tener en un archivo db.py lo relacionado con la creación y configuración de la base de datos:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import sqlite3
from sqlite3 import Error
import os
CURDIR = os.path.dirname(os.path.abspath(__file__))
FILENAME = "schema.sql"
FILE = os.path.join(CURDIR, FILENAME)
def open_db():
try:
con = sqlite3.connect('cars.db')
return con
except Error as e:
print('Error: ', e)
def run_query(sql, params='', multiple=False):
with open_db() as con:
cursor = con.cursor()
try:
if multiple:
return cursor.executemany(sql, params)
else:
return cursor.execute(sql, params)
except Error as e:
print('Error: ', e)
def create_schema():
with open(FILE, 'r') as sql_file:
schema_created = run_query(sql_file.read())
if schema_created.rowcount == -1:
print("Database created successfully")
if __name__ == "__main__":
create_schema()
Vamos a resumir que hace el código anterior, sobretodo las funciones:
open_db- Se encargará de crear o abrir la base de datos y se maneja a través
try/exceptpara capturar posibles errores. run_query- Esta función va a utilizar la conexión que retorna
open_dby con ella podemos realizar consultas a la base de datos. create_schema()- Esta función va a construir el esquema de la base de datos que tenemos definido en un archivo llamado
schema.sql.
El archivo que que se encarga de crear la bae de datos se llama schema.sql y lo debemos crear, en él, se definen las siguientes instrucciones:
1
2
3
4
CREATE TABLE IF NOT EXISTS cars(
brand VARCHAR(50) NOT NULL,
model VARCHAR(12) NOT NULL
);
Para comenzar con las operaciones del CRUD, en un archivo crud.py definiremos las funciones de las 4 operaciones.
1. Crud -> CREATE
Empezando con el Crud con la C de ( CREATE ) , vamos a crear o insertar algunos registros en la base de datos, los datos que queremos insertar se pueden obtener de diferentes maneras:
Insertar Datos Manualmente o en Crudo
Podemos ejecutar un comando SQL directamente para insertar datos. Aquí un ejemplo:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import db
def insert_data():
insert_query = "INSERT INTO cars (brand, model) VALUES(?, ?)"
cars_data = [
('Chevrolet', 'Chevrolet Camaro'),
('Chevrolet', 'Chevrolet Captiva'),
('Fiat', 'Fiat 125 Mirafiori'),
('Fiat', 'Fiat 125 Centurion'),
('Honda', 'Honda CR-V'),
('Honda', 'Honda CR-X del Sol'),
('Honda', 'Honda CR-Z')
]
result = db.run_query(insert_query, cars_data, True)
print("Record inserted successfully into table, records: ",result.rowcount)
if __name__ == "__main__":
insert_data()
Como podemos observar, cuando ejecutamos la función run_query() del módulo db que definimos anteriormente, le estamos pasando los siguientes parámetros:
insert_query(la consulta).cars_data(una lista de tuplas con los datos).True(para decirle que este cursor se ejecute con el métodoexecutemany()para crear múltiples registros).
Insertar Datos desde un Archivo CSV
Primero, creamos un archivo cars.csv con el siguiente contenido. Este archivo contendrá los datos de varios automóviles:
1
2
3
4
5
6
7
8
brand, model
Chevrolet, Chevrolet Camaro
Chevrolet', 'Chevrolet Captiva
Fiat, Fiat 125 Mirafiori
Fiat, Fiat 125 Centurion
Honda, Honda CR-V
Honda, Honda CR-X del Sol
Honda, Honda CR-Z
Ya luego escribimos nuevamente la función, ahora importando el módulo csv que viene incluido en Python para leer y escribir archivos CSV:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import db, csv
def insert_data():
insert_query = "INSERT INTO cars (brand, model) VALUES(?, ?)"
record_count = 0
with open("cars.csv") as file:
reader = csv.reader(file)
next(reader) # Saltamos la cabecera
for fila in reader:
db.run_query(insert_query, fila)
record_count += 1
print("Record inserted successfully into table, records: ", record_count)
if __name__ == "__main__":
insert_data()
Insertar Datos Dinámicamente
También podemos modificar la función para solicitar al usuario que ingrese los datos dinámicamente:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import db
def insert_data():
insert_query = "INSERT INTO cars (brand, model) VALUES(?, ?)"
record_count = 0
while True:
brand = input("Ingrese la marca del vehículo (o 'salir' para terminar): ")
if brand.lower() == "salir":
break
model = input("Ingrese el modelo del vehículo: ")
db.run_query(insert_query, (brand, model))
record_count += 1
print("Records inserted successfully into table, records:", record_count)
if __name__ == "__main__":
insert_data()
Ahora desde el módulo principal, importamos la función desde el módulo crud.py y verificamos la acción:
1
2
3
4
5
6
7
from crud import insert_data
def init():
insert_data()
if __name__ == "__main__":
init()
La instrucción
if __name__ == "__main__"comprueba si el script se está ejecutando como programa principal. Si es así, llama a la funcióninit()que de momento solo tiene la invocación de la funcióninsert_data()del módulocrud. Esto es más que nada para promover la modularidad y la reutilización. Permite que el script sirva como programa independiente y como módulo importable.
Como resultado nos mostraría el siguiente mensaje al ejecutar el script main.py:
1
python3 main.py
1
Record inserted successfully into table, records: 7
2. cRud -> READ
Ya que tenemos registros insertados, vamos a recuperarlos y mostrarlo en la consola:
1
2
3
4
5
6
7
8
from prettytable import from_db_cursor # pip install prettytable
def get_data():
select_query = "SELECT * FROM cars"
result = db.run_query(select_query)
mytable = from_db_cursor(result)
mytable.align = "l"
print(mytable)
prettytable es una librería de Python que da formato de tabla a los datos por consola y se debe instalar como lo muestra el comentario en la primera línea.
Ahora desde el módulo principal, importamos la función desde el módulo crud.py y verificamos la acción:
1
2
3
4
5
6
7
8
from crud import insert_data, get_data
def init():
# insert_data() (comentamos esta línea para no insertar duplicados)
get_data()
if __name__ == "__main__":
init()
Volvemos a ejecutar el script principal:
1
python main.py
Como resultado, veremos los registros en un formato agradable gracias a prettytable:
1
2
3
4
5
6
7
8
9
10
11
+-----------+--------------------+
| brand | model |
+-----------+--------------------+
| Chevrolet | Chevrolet Camaro |
| Chevrolet | Chevrolet Captiva |
| Fiat | Fiat 125 Mirafiori |
| Fiat | Fiat 125 Centurion |
| Honda | Honda CR-V |
| Honda | Honda CR-X del Sol |
| Honda | Honda CR-Z |
+-----------+--------------------+
3. crUd -> UPDATE
Continuamos con la 3° operación crUd, definiremos una función que permite actualizar el campo model de la tabla para actualizar el modelo existente por el nuevo modelo:
1
2
3
def update_data(model, newModel):
update_query = "UPDATE cars SET model = ? WHERE model = ?"
db.run_query(update_query, (newModel, model))
Los marcadores de posiciones
?en la instrucción SQL, también denominados param style indican que se esperan parámetros por parte de la interfaz DB API al momento de ejecutar el métodoexecute().
Ahora desde el módulo principal, importamos la función desde el módulo crud.py y verificamos la acción:
1
2
3
4
5
6
7
8
9
10
11
from crud import insert_data, get_data, update_data
def init():
# insert_data() (comentamos esta línea para no insertar duplicados)
get_data() # mostramos los datos antes de actualizar
update_data("Chevrolet Camaro", "Chevrolet Corvette")
update_data("Honda CR-V", "Honda HR-V")
get_data() # mostramos los datos ya actualizados
if __name__ == "__main__":
init()
Volvemos a ejecutar el script principal:
1
python main.py
Observemos que los modelos se actualizaron:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
+-----------+--------------------+
| brand | model |
+-----------+--------------------+
| Chevrolet | Chevrolet Camaro |
| Chevrolet | Chevrolet Captiva |
| Fiat | Fiat 125 Mirafiori |
| Fiat | Fiat 125 Centurion |
| Honda | Honda HR-V |
| Honda | Honda CR-X del Sol |
| Honda | Honda CR-Z |
+-----------+--------------------+
+-----------+--------------------+
| brand | model |
+-----------+--------------------+
| Chevrolet | Chevrolet Corvette |
| Chevrolet | Chevrolet Captiva |
| Fiat | Fiat 125 Mirafiori |
| Fiat | Fiat 125 Centurion |
| Honda | Honda HR-V |
| Honda | Honda CR-X del Sol |
| Honda | Honda CR-Z |
+-----------+--------------------+
4. cruD -> DELETE
Eliminar es muy sencillo, ya que lo único que debemos preocuparnos es de añadir la cláusula WHERE 🤭 para que no te sientas identificado con esta canción:
Basta de canciones, y vamos a definir la función que se encargará de eliminar un registro a través del campo model de la tabla:
1
2
3
def delete_data(model):
delete_query = "DELETE FROM cars WHERE model = ?"
db.run_query(delete_query, (model,))
NO TE OLVIDES DE PONER EL
WHEREEN ELDELETE FROMJAJA ❤️🔥
Ahora desde el módulo principal, importamos la función desde el módulo crud.py y verificamos la acción:
1
2
3
4
5
6
7
8
9
10
from crud import insert_data, get_data, update_data, delete_data
def init():
# insert_data() (comentamos esta línea para no insertar duplicados)
get_data() # mostramos los datos antes de eliminar
delete_data("Chevrolet Captiva")
get_data() # mostramos los datos una vez eliminado el registro
if __name__ == "__main__":
init()
Volvemos a ejecutar el script principal:
1
python main.py
Y terminamos con el siguiente resultado:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
+-----------+--------------------+
| brand | model |
+-----------+--------------------+
| Chevrolet | Chevrolet Corvette |
| Chevrolet | Chevrolet Captiva |
| Fiat | Fiat 125 Mirafiori |
| Fiat | Fiat 125 Centurion |
| Honda | Honda HR-V |
| Honda | Honda CR-X del Sol |
| Honda | Honda CR-Z |
+-----------+--------------------+
+-----------+--------------------+
| brand | model |
+-----------+--------------------+
| Chevrolet | Chevrolet Corvette |
| Fiat | Fiat 125 Mirafiori |
| Fiat | Fiat 125 Centurion |
| Honda | Honda HR-V |
| Honda | Honda CR-X del Sol |
| Honda | Honda CR-Z |
+-----------+--------------------+
Ejemplo Completo en Trinket
Mi lema es