SQLite en Python

En el caso de que necesitemos una base de datos SQL para guardar datos en nuestra aplicación podemos recurrir a SQLite. Una base de datos ligera que se puede utilizar sin la necesidad de descargar, instalar y configurar ningún software. Solamente es necesario importar el paquete sqlite3, incluido en la instalación de Python. Así que veamos la forma de trabajar con SQLite en Python.

Conexión a una base de datos SQLite

Las bases de datos de SQLite son archivos. Así para conectar a una base de datos solamente es necesario indicar la ruta a este. Para los que se debe utilizar el método sqlite3.connect(). En el caso de que el archivo indicado exista y sea una base de datos válida se conectará a ésta, en caso contrario, si es posible, creará el archivo y se conectará. Devolviendo el método una conexión.

Una vez realizada la conexión es necesario obtener un cursor para poder trabajar. Cursor que se puede obtener mediante el método de la conexión cursor(). Así para indicar la conexión a una base de datos solamente se debe ejecutar los siguientes comandos.

import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()

Obteniendo como resultado una conexión en conn y un cursor el cursor.

Ejecutar una consulta en la base de datos

Ahora que tenemos la conexión se puede ejecutar diferentes consultas en la base de datos. Para lo que se utiliza el método execute() del cursor. Por ejemplo, conocer la lista de tablas en la base de datos solamente es necesario ejecutar

import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()

cursor.execute('SELECT name FROM sqlite_master')

for row in cursor.fetchall(): 
    print(row)

En SQLite la tabla sqlite_master contiene el listado de las tablas que existen en la base de datos con sus propiedades. Siendo name el nombre de la columna. Una vez lanzada la consulta SQL solamente deberíamos iterar sobre las respuestas utilizando el método del cursor fetchall(). Lo que devolverá cada una de las columnas.

Si la base de datos es nueva, como es el caso, el código no devolverá ningún registro debido a que no existe ninguna tabla.

Creación de una tabla

Para crear una tabla en la base de datos se tienen que ejecutar un comando de creación de tabla. Básicamente CREATE TABLE. Por ejemplo, para crear una tabla de clientes con nombre, apellidos, género, dirección IP y un identificador autonumérico solamente se tiene que ejecutar la siguiente consulta.

cursor.execute('''
    CREATE TABLE IF NOT EXISTS clients (
        id integer PRIMARY KEY NOT NULL,
        first_name TEXT,
        last_name TEXT,
        gender TEXT,
        ip_address TEXT);''')

Creación de una clase para tratar con SQLite en Python

El proceso de lectura, creación, actualización y borrado de registros en una base de datos de clientes se puede incluir en una clase de Python. Lo que se muestra en el siguiente código.

import sqlite3


class Database:
    def __init__(self, database):
        self.__conn = sqlite3.connect(database)
        self.__cursor = self.__conn.cursor()
    
        if not self.__table_exists('clients'):
            self.__cursor.execute('''
                CREATE TABLE IF NOT EXISTS clients (
                    id integer PRIMARY KEY NOT NULL,
                    first_name TEXT,
                    last_name TEXT,
                    gender TEXT,
                    ip_address TEXT);''')
    
    
    def __table_exists(self, table):
        self.__cursor.execute('''SELECT count(name) FROM sqlite_master WHERE TYPE = 'table' AND name = '{}';'''.format(table)) 
        if self.__cursor.fetchone()[0] == 1:
            return True 
        else:
            return False
        

    def __read_cursor(self):
        data = []
        
        for row in self.__cursor.fetchall(): 
            data.append(row) 
            
        return data
        
        
    def insert_client(self, first_name, last_name, gender, ip_address):
        self.__cursor.execute('''INSERT INTO clients (first_name, last_name, gender, ip_address) VALUES(?, ?, ?, ?);''',
                               (first_name, last_name, gender, ip_address))
        self.__conn.commit()
        
        
    def get_clients(self):
        self.__cursor.execute('SELECT * FROM clients')
        return self.__read_cursor()
    
    
    def get_client(self, client_id):
        self.__cursor.execute('SELECT * FROM clients WHERE id = {};'.format(client_id))
        return self.__read_cursor()
    
    
    def delete_client(self, client_id):
        self.__cursor.execute('DELETE FROM clients WHERE id = {};'.format(client_id))
        self.__conn.commit()
        
        
    def update_client_ip(self, client_id, ip_address):
        self.__cursor.execute('''UPDATE clients SET ip_address = '{}' WHERE id = {};'''.format(ip_address, client_id))
        self.__conn.commit()

A continuación, se puede ver los diferentes componentes de esta clase.

El constructor de la clase

En primer lugar, se puede ver el constructor de la clase que contiene la creación de la conexión y un cursor. Así como la creación de la tabla si no existe. Lo que se verifica con el método __table_exists.

El método __table_exists

En este método privado se consulta si el nombre de la tabla indicada existe en la base de datos. Lo que, como se ha visto anteriormente, es posible comprobar en la tabla sqlite_master. Si la consulta devuelve un valor la tabla existe, en caso contrario no.

Insertar un cliente

Para la inserción de un cliente se ha creado el método insert_client(). Al que debemos pasar todos los parámetros de la tabla. Dentro del método se incluye una consulta de inserción y, después de lanzar esta, un commit para asegurar que los datos se incluyen en la base de datos.

Obtención de los clientes

Se han creado dos método para obtener todos los clientes, get_clients(), y un único cliente, get_client(). La única diferencia entre los dos es la consulta que se hace para obtener uno o varios clientes. En ambos casos, una vez realizada la consulta se llama al método privado __read_cursor(). Método con el que se guardan los registros obtenidos en la última consulta en una lista.

Borrado y actualización de los datos.

Finalmente tenemos dos método para borrar, delete_client(), y actualizar , update_client_ip(), los datos de un registros. En ambos casos usando el id para identificar el registro a alterar. En este caso lo único importante una vez lanzada la consulta es usar el método commit() de la conexión para asegurarnos que los cambios se aplican.

Ejemplo de uso de SQLite en Python

Ahora que se ha creado la clase se pueden probar los diferentes métodos para comprobar el funcionamiento del código. En primer lugar, se pueden crear registros y comprobar que estos son los que se obtienen al pedir el listado de clientes.

db = Database('clients.db')

db.insert_client('Damita', 'Suett', 'Female', '223.103.33.64')
db.insert_client('Dorie', 'Befroy', 'Female', '236.0.133.20')
db.insert_client('Marlo', 'Roughley', 'Male', '124.10.252.163')
db.insert_client('Cleveland', 'Bentick', 'Male', '26.35.192.36')
db.insert_client('Sofie', 'Eannetta', 'Female', '55.15.100.224')

db.get_clients()

[(1, 'Damita', 'Suett', 'Female', '223.103.33.64'),
 (2, 'Dorie', 'Befroy', 'Female', '236.0.133.20'),
 (3, 'Marlo', 'Roughley', 'Male', '124.10.252.163'),
 (4, 'Cleveland', 'Bentick', 'Male', '26.35.192.36'),
 (5, 'Sofie', 'Eannetta', 'Female', '55.15.100.224')]

Una vez hecho esto se puede comprobar los valores de un cliente en concreto, para lo que se usa el método get_client.

db.get_client(1)

[(1, 'Damita', 'Suett', 'Female', '223.103.33.64')]

Finalmente, comprobaremos los métodos para borrar un registro y modificar otro. Viendo que los valores finales son los esperados.

db.delete_client(1)
db.update_client_ip(2, '197.149.84.113')
db.get_clients()

[(2, 'Dorie', 'Befroy', 'Female', '197.149.84.113'),
 (3, 'Marlo', 'Roughley', 'Male', '124.10.252.163'),
 (4, 'Cleveland', 'Bentick', 'Male', '26.35.192.36'),
 (5, 'Sofie', 'Eannetta', 'Female', '55.15.100.224')]

Conclusiones

En esta ocasión hemos visto cómo trabajar con SQLite en Python a través de la librería sqlite3. Librería que ya está incluida por defecto en las versiones actuales de Python. Gracias a lo que podemos disponer de una base de datos para persistir información en nuestros programas, sin necesitar para ello instalar y configurar un motor de bases de datos. Además, al trabajar con archivos, es más fácil trabajar con varias bases de datos y mover los datos entre diferentes ordenadores.

SQLite es una base de datos muy popular para persistir los datos localmente, por eso ya hemos visto ejemplo de su tanto en RapidMiner como en R.

Imagen de FreePhotosART en Pixabay