Introducción

En esta entrada veremos cómo crear, leer, actualizar y eliminar registros en SQLite3 usando Python 3. Es decir, haremos un CRUD con SQLite. Para ello lo único que necesitamos es instalar Python. La librería que permite gestionar la base de datos ya viene incluida.

Te invito a leer el tutorial de PHP con PDO y SQLite3 para crear CRUD

Nota: si quieres ver el código completo ve a GitHub. También he creado otros tutoriales para conectar Python con SQL Server, PostgreSQL y MySQL.

Abriendo base de datos

Para comenzar a trabajar necesitamos crear o abrir una base de datos. Recordemos que SQLite3 es el motor de base de datos que no necesita servidor, por lo que todos los datos residen en un fichero. Dicho archivo, si no existe, será creado. Y si ya existe, será abierto; por lo que no debemos preocuparnos por la sobreescritura de datos.

Es importante mencionar que para nombrar nuestras bases de datos debemos usar carácteres válidos. No podemos usar nombres que lleven *, /, \, :, etcétera (al menos en el caso de Windows).

Lo primero que tenemos que hacer es importar la librería de Python llamada sqlite3. Así:

improt sqlite3

Abrir en fichero

La forma más común de trabajar con este gestor es guardar los datos en un archivo. Para ello, sólo importamos la librería y llamamos al método connect. Como argumento le pasamos el nombre de nuestra base de datos. Recomiendo usar extensiones .db para que sea más fácil reconocerlas, aunque podemos usar el nombre que deseemos e incluso podemos dejar los ficheros sin extensión. Abriré entonces mi base de datos llamada base_de_datos.db:

import sqlite3
bd = sqlite3.connect("base_de_datos.db")
print("Base de datos abierta")

Con eso ya la habremos abierto. Al ejecutarlo, veremos lo siguiente:

Si verificamos el directorio en donde estamos trabajando, veremos que ahora hay un archivo con el mismo nombre que pusimos en el código.

¿Y cuál es la ventaja de abrirla en el disco duro? recordemos que lo que siempre agradecemos es la persistencia y la portabilidad. Podemos llevar de aquí para allá nuestro archivo, hacer respaldos simplemente copiando un fichero, y todo eso con la confianza de que los datos, registros, filas y estructuras estarán ahí dentro, por mucho tiempo.

Abrir en memoria

Como todos sabemos, en la mayoría de casos el disco duro es más lento que la RAM. Si necesitamos almacenar datos temporales (que sólo vivan mientras se ejecute el programa) y queremos interactuar con ellos de una manera muy muy rápida podemos abrir la base de datos en la memoria de acceso aleatorio. Esto, además, no dejará ningún rastro.

Existe un nombre reservado que ocupar para hacer esto, y es :memory:, así que para abrirla podemos usar lo siguiente:

import sqlite3
bd = sqlite3.connect(":memory:")
print("Base de datos abierta en memoria correctamente")

Si lo ejecutamos, no creará ningún archivo en el disco duro pero sin duda habrá abierto la base de datos.

La ventaja de abrirla en memoria es que las operaciones serán muy rápidas comparadas a las del disco duro, ya que la RAM trabaja más rápido que el primero.

Se me ocurre que podemos usar sqlite3 en memoria para guardar datos de un videojuego. Seríamos capaces de ir guardando los puntajes en tiempo de ejecución y, al final, cuando se cierre el juego, copiarlos a una base de datos del disco duro.

Excepciones al abrir

En los ejemplos de arriba puede que pensemos que no estamos comprobando si realmente se está abriendo la base de datos, ya que sólo imprimimos “Base de datos abierta correctamente”.

Aunque parezca que no es un método fiable, sí lo es. Ya que si se imprime el mensaje y no se muestra ningún error, es que no ha habido problemas. Si los hubiera, no se mostraría el mensaje indicando la apertura correcta.

De todos modos, hay métodos para manejar las excepciones al abrirlas. Podemos usar try y except para capturar algún error. En el siguiente ejemplo trataré de abrir un fichero con un nombre incorrecto, que lleva asteriscos y barras, cosa que no es permitida como nombre de archivo en Windows:

import sqlite3
try:
 bd = sqlite3.connect("nombre *//incorrecto...")
 print("Base de datos abierta correctamente")
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

Al ejecutar el script, obtenemos el error, y no sale el mensaje de “Base de datos abierta correctamente”. En su lugar aparece el mensaje de error:

Con esto podemos asegurarnos de que nuestro código se ejecutará de forma segura y que, si pasa algo, será capturado.

Operaciones CRUD

Una vez hecha la conexión usando cualquier método de arriba, procederemos a obtener el cursor. El cursor es el que nos permitirá interactuar con la base de datos en sí. Para obtenerlo llamamos al método cursor() de sqlite3:

import sqlite3
try:
 bd = sqlite3.connect("base_de_datos.db")
 cursor = bd.cursor() #Con el cursor ya podemos interactuar completamente
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

Ahora sí podemos continuar con el tutorial.

Crear tablas

El nombre de la base de datos en todo el tutorial será libros.db

Podemos crear tablas directamente desde python. En este caso, para trabajar usaremos una tabla que guardará libros de una tienda. En ella tendremos autor, género, precio y título. Recordemos que en SQLite sólo existen los tipos de datos INTEGER, NULL, REAL, TEXT y BLOB. Así que las tres columnas que no son el precio serán de tipo TEXT y la otra será de tipo REAL.

import sqlite3
try:
 bd = sqlite3.connect("libros.db")
 cursor = bd.cursor()
 tablas = [
  """
   CREATE TABLE IF NOT EXISTS libros(
    autor TEXT NOT NULL,
    genero TEXT NOT NULL,
    precio REAL NOT NULL,
    titulo REAL NOT NULL
   );
  """
 ]
 for tabla in tablas:
  cursor.execute(tabla);
 print("Tablas creadas correctamente")
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

Lo que estamos haciendo es definir una lista con todas nuestras tablas. Después la recorremos y por cada valor ejecutamos la sentencia en SQLite.

En este caso sólo es una tabla, pero si en el futuro añadimos otra sólo tenemos que agregarla a la lista (o arreglo) y correr el código de nuevo. No debemos preocuparnos por errores, porque estamos creando la tabla sólo si no existe. Así que si ya existe sólo será ignorada.

Al ejecutar, tenemos lo siguiente:

De esta forma hemos creado nuestra tabla.

Create o insertar

Para poder eliminar, ver o actualizar tenemos que alimentar a nuestra base de datos. Por eso comenzamos por aquí.

Cada vez que insertemos es necesario hacer un commit. Es decir, “guardar” nuestros cambios a la base de datos. También tenemos que hacerlo al eliminar. Si no hacemos esto, los movimientos no se registrarán.

Insertar con datos predefinidos

Voy a insertar 3 libros:

import sqlite3
try:
 bd = sqlite3.connect("libros.db")
 cursor = bd.cursor()
 libros = [
  """
  INSERT INTO libros
  (autor, genero, precio, titulo)
  VALUES
  ('Stephen King', 'Terror', 115,'Cementerio de animales'),
  ('Alfred Bester', 'Ciencia ficción', 200,'Las estrellas, mi destino'),
  ('Margaret Atwood', 'Ciencia ficción', 150,'El cuento de la criada');
  """
 ]
 for sentencia in libros:
  cursor.execute(sentencia);
 bd.commit() #Guardamos los cambios al terminar el ciclo
 print("Libros insertados correctamente")
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

En este caso no hago una sentencia preparada porque no estoy permitiendo entrada del usuario, por lo que es imposible un ataque de inyección SQL. Al ejecutar el script, obtenemos lo siguiente:

Insertar con datos proporcionados por el usuario

Ahora haremos esto más divertido. Vamos a pedirle al usuario los datos del libro que quiere guardar. Para ello necesitamos leer datos del teclado usando input. Así que el código sería más o menos así:

import sqlite3
try:
 bd = sqlite3.connect("libros.db")
 cursor = bd.cursor()
 autor = input("\nAutor: ")
 genero = input("\nGénero: ")
 precio = float(input("\nPrecio: "))
 titulo = input("\nTítulo: ")
 sentencia = "INSERT INTO libros(autor, genero, precio, titulo) VALUES (?,?,?,?)"
 cursor.execute(sentencia, [autor, genero, precio, titulo])
 bd.commit()
 print("Guardado correctamente")
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

Por favor noten que no estoy haciendo ninguna validación para ver si el precio es realmente un flotante, y tampoco compruebo si pusieron una cadena vacía. Lo que sí estoy haciendo es preparar la sentencia para evitar inyecciones SQL. Por eso al escribir INSERT INTO… pongo signos de interrogación en lugar de concatenar la cadena con los valores. Y al llamar a execute le paso como segundo argumento una lista con los datos del usuario para que SQLite se encargue de juntarles internamente.

Al ejecutarlo, pasa lo siguiente:

El script va preguntando cosa por cosa. Nosotros introducimos el dato y presionamos Enter. Así hasta terminar.

Read o listar/leer

Como ya introdujimos datos, ahora tenemos que verlos. Vamos a hacer un listado completo de ellos. Sólo tenemos que hacer un SELECT. El código queda así:

import sqlite3
try:
 bd = sqlite3.connect("libros.db")
 cursor = bd.cursor()
 sentencia = "SELECT * FROM libros;"
 
 cursor.execute(sentencia)
 
 libros = cursor.fetchall()
 
 print(libros)
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

Como podemos ver, fetchall devuelve una lista con todos los datos recuperados. Antes de llamar a este método debemos llamar a execute con la consulta que se supone dichos datos. El resultado entonces es el siguiente:

Claro que no se ve muy bonito, pero ahí están los datos.

Listar datos en una tabla

Una vez que ya tenemos los datos, vamos a imprimirlos en una cosa parecida a una tabla. Para ello me inspiré en cómo MySQL imprime datos por consola. Así que haciendo uso del método format de las cadenas podremos mostrar una tabla bonita.

import sqlite3
try:
 bd = sqlite3.connect("libros.db")
 cursor = bd.cursor()
 sentencia = "SELECT * FROM libros;"
 
 cursor.execute(sentencia)
 
 libros = cursor.fetchall()
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+".format("", "", "", ""))
 print("|{:^20}|{:^20}|{:^10}|{:^50}|".format("Autor", "Género", "Precio", "Título"))
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+".format("", "", "", ""))
 
 
 for autor, genero, precio, titulo in libros:
  print("|{:^20}|{:^20}|{:^10}|{:^50}|".format(autor, genero, precio, titulo))
 
 
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+".format("", "", "", ""))
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

Simplemente estamos recorriendo la lista e imprimiendo una línea formateada. Primero es el encabezado, y después los datos.

Al terminar el ciclo, imprimimos el pie. El resultado es el siguiente:

Buscar datos

Obviamente nunca vamos a querer mostrar todos los datos. En algunas ocasiones necesitaremos filtrar. Veremos cómo podemos buscar un libro por su título.

import sqlite3
try:
 bd = sqlite3.connect("libros.db")
 cursor = bd.cursor()
 
 busqueda = input("Escribe tu búsqueda: ")
 if not busqueda:
  print("Búsqueda inválida")
  exit()
 
 sentencia = "SELECT * FROM libros WHERE titulo LIKE ?;"
 
 cursor.execute(sentencia, [ "%{}%".format(busqueda) ])
 
 libros = cursor.fetchall()
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+".format("", "", "", ""))
 print("|{:^20}|{:^20}|{:^10}|{:^50}|".format("Autor", "Género", "Precio", "Título"))
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+".format("", "", "", ""))
 
 
 for autor, genero, precio, titulo in libros:
  print("|{:^20}|{:^20}|{:^10}|{:^50}|".format(autor, genero, precio, titulo))
 
 
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+".format("", "", "", ""))
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

En este caso le preguntamos al usuario lo que quiere buscar. Si no escribe nada, salimos. En caso de que sí haya escrito algo, lo buscamos usando LIKE. Los carácteres de porcentaje (%) son para que coincida si el título tiene en alguna parte la palabra buscada.

Usamos format y una sentencia preparada para evitar inyecciones SQL. Aquí dejo un ejemplo de lo que pasa:

Update o actualizar

Para actualizar o eliminar necesitamos un índice, índice que por cierto no agregamos. Pero no hay problema, ya que SQLite agrega por sí mismo una clave numérica auto incremental llamada rowid (algo así como id de fila). Cuando hacemos un select no la muestra, pero lo hará sí escribimos:

SELECT *, rowid FROM libros;

Sabiendo esto, podemos entonces listar los datos junto con su rowid, preguntar al usuario cuál quiere editar y volver a preguntarle campo por campo. Finalmente hacer un update y listo. El código queda así:

import sqlite3
try:
 
 #Conectar a la base de datos
 bd = sqlite3.connect("libros.db")
 cursor = bd.cursor()
 
 #Listar los libros
 
 sentencia = "SELECT *,rowid FROM libros;"
 
 cursor.execute(sentencia)
 
 libros = cursor.fetchall()
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+{:-<10}+".format("", "", "", "", ""))
 print("|{:^20}|{:^20}|{:^10}|{:^50}|{:^10}|".format("Autor", "Género", "Precio", "Título", "Rowid"))
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+{:-<10}+".format("", "", "", "", ""))
 
 
 for autor, genero, precio, titulo, rowid in libros:
  print("|{:^20}|{:^20}|{:^10}|{:^50}|{:^10}|".format(autor, genero, precio, titulo, rowid))
 
 
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+{:-<10}+".format("", "", "", "", ""))
 
 #Pedir id del libro a editar
 id_libro = input("\nEscribe el id del libro que quieres editar: ")
 if not id_libro:
  print("No escribiste nada")
  exit()
 
 #Pedir nuevos datos
 autor = input("\nNuevo autor: ")
 genero = input("\nNuevo género: ")
 precio = float(input("\nNuevo precio: "))
 titulo = input("\nNuevo título: ")
 
 #Sentencia para actualizar
 sentencia = "UPDATE libros SET autor = ?, genero = ?, precio = ?, titulo = ? WHERE rowid = ?;"
 
 #Actualizar datos
 cursor.execute(sentencia, [autor, genero, precio, titulo, id_libro])
 bd.commit()
 print("Datos guardados")
 
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

El código es sencillo. Pedir de nuevo todos los datos y al final actualizar. Si no escriben ningún id, salimos. Cabe mencionar que si el id no existe en la base de datos ésta no generará ningún error. Si después ejecutamos el script que imprime los datos en una tabla, podremos ver que se han actualizado correctamente.

En este caso cambié el género y el precio. Lo engorroso de esto es que si no vamos a cambiar todos los datos tenemos que escribir de nuevo el valor original.

Delete o eliminar

Después de haber hecho todos los otros movimientos, eliminar será fácil. Sólo tenemos que listar y preguntar el id del libro. Así queda:

import sqlite3
try:
 
 #Conectar a la base de datos
 bd = sqlite3.connect("libros.db")
 cursor = bd.cursor()
 
 #Listar los libros
 
 sentencia = "SELECT *,rowid FROM libros;"
 
 cursor.execute(sentencia)
 
 libros = cursor.fetchall()
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+{:-<10}+".format("", "", "", "", ""))
 print("|{:^20}|{:^20}|{:^10}|{:^50}|{:^10}|".format("Autor", "Género", "Precio", "Título", "Rowid"))
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+{:-<10}+".format("", "", "", "", ""))
 
 
 for autor, genero, precio, titulo, rowid in libros:
  print("|{:^20}|{:^20}|{:^10}|{:^50}|{:^10}|".format(autor, genero, precio, titulo, rowid))
 
 
 print("+{:-<20}+{:-<20}+{:-<10}+{:-<50}+{:-<10}+".format("", "", "", "", ""))
 
 #Pedir id del libro a editar
 id_libro = input("\nEscribe el id del libro que quieres eliminar: ")
 if not id_libro:
  print("No escribiste nada")
  exit()
 
 #Sentencia para eliminar
 sentencia = "DELETE FROM libros WHERE rowid = ?;"
 
 #Eliminar el libro
 cursor.execute(sentencia, [id_libro])
 bd.commit()
 print("Eliminado con éxito")
 
except sqlite3.OperationalError as error:
 print("Error al abrir:", error)

Si lo ejecutamos, pasa lo que se ve en la imagen:

Puse el id del libro que quería eliminar y se eliminó. Después volví a ejecutar el script que los lista, y ahí ya no se ve el que acabo de eliminar.

Conclusión

Con esto hemos terminado la introducción y los ejemplos de cómo usar SQLite en Python. Es importante destacar que éstos son ejemplos básicos y no funcionales, sólo muestran pequeños conceptos.

Después de esto podemos conectar con una interfaz gráfica o hacer cualquier software manejado por consola.

Por cierto, hice un script para interactuar con cualquier base de datos aquí. Recuerda que si no has instalado Python puedes ver este tutorial.