Dominar la base de datos SQLite en Python

SQLite es un sistema de gestión de bases de datos relacionales basado en el lenguaje SQL; es un motor de base de datos de configuración cero sin servidor. Es uno de los motores de base de datos más populares y muy fácil de usar en aplicaciones pequeñas. Crea solo un archivo de disco para almacenar toda la base de datos, lo que hace que el archivo sea portátil. Se utiliza en el sistema operativo Android como fuente principal para almacenar datos. También lo utiliza Google Chrome para almacenar datos del sitio y datos del usuario, incluidas las contraseñas en la máquina local.

Trabajo avanzado con la base de datos SQLite en Python

En este tutorial, los temas que se cubrirán son: insertar imágenes en una tabla SQLite, enumerar las tablas presentes en una base de datos, identificar los cambios totales desde que la base de datos está conectada, hacer una copia de seguridad de una base de datos, volcar una base de datos SQLite, deshacer en SQLite, eliminar registros de una tabla, eliminación de una tabla y excepciones de la base de datos SQLite.

Es posible que también desee ver la primera parte de este tutorial, que presenta la conceptos básicos de SQLite, ventajas de usarlo, conectarse a un archivo de base de datos, crear una tabla en la base de datos, insertar datos en la tabla, consultar datos de la tabla, actualizar la tabla y muchos más.

Archivos e imágenes en la base de datos SQLite

Mientras trabaja con bases de datos, hay situaciones en las que necesita insertar imágenes o archivos en una base de datos o exportar desde ella. Por ejemplo, si está creando una base de datos para almacenar los datos de los empleados, es posible que también deba insertar imágenes de cada empleado en la base de datos.

Para agregar imágenes en una base de datos SQLite, necesitamos usar el tipo de datos BLOB de SQLite. El tipo de datos BLOB () se utiliza para almacenar objetos grandes, normalmente archivos grandes como imágenes, música, videos, documentos, PDF, etc. El primer paso es convertir los datos y las imágenes en el objeto byte de Python, que es similar a el tipo de datos BLOB de SQLite. Antes de continuar, cree una tabla llamada estudiante en la base de datos con los campos id, nombre, imágenes, marcas. Ejecute el siguiente código para crear la tabla.

import sqlite3

conn = sqlite3.connect("sample.db")
print("n [+] Successfully connected to the database")
cur = conn.cursor()
print("n [+] Cursor has been set up successfully")
table = cur.execute(""" CREATE TABLE student( id INT PRIMARY KEY, name TEXT, images BLOB, marks TEXT ); """)
print("n [+] The table has been created successfully")
cur.close()
conn.commit()
conn.close()

Este programa creará una nueva tabla con el nombre estudiante. Verá la siguiente salida en el terminal.

Insertar una imagen

Para insertar una imagen en una base de datos SQLite, transforme la imagen en un objeto de byte de Python y luego insértela en la columna de imágenes, que acepta datos BLOB. Ejecute el siguiente código para agregar una imagen img.png en la base de datos usando Python.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("n [+] Successfully connected to the database")
cur = conn.cursor()
print("n [+] Cursor has been set up successfully")
with open("img.png","rb") as file:
      data = file.read()

python_tuple = (101,"robin",data,"90")
print("n [+] The image has been successfully imported")
print("n [+] Now inserting in the database")
cur.execute("INSERT INTO student (id,name,images,marks) VALUES (?,?,?,?)", python_tuple)
print("n [+] The Data has been inserted Successfully ")
cur.close()
conn.commit()
conn.close()

Este programa insertará la imagen en la base de datos de estudiantes que creó. Verá el siguiente resultado.

En el programa anterior, hemos abierto el archivo en modo binario y leemos cada byte y lo almacenamos en variable datos. Luego usamos esa variable en la declaración INSERT para insertar la imagen en la base de datos.

Recuperando una imagen

Para recuperar una imagen de una base de datos, busque la fila usando una declaración de selección y luego acceda a los datos binarios de la imagen en una variable de Python, que se almacenará en un archivo de imagen. Consulte el siguiente código para obtener una ilustración.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("n [+] Successfully connected to the database")
cur = conn.cursor()
print("n [+] Cursor has been set up successfully")
print("n [+] Retrieving the image")
cur.execute("SELECT * FROM student")
ret = cur.fetchall()
for i in ret:
     data = i[2]

with open("img2.png","wb") as file:
          file.write(data)

print("n [+] The image has been saved")
cur.close()
conn.commit()
conn.close()

Este sencillo programa recuperará la imagen de la base de datos y la guardará en el disco llamado img2.png. También puede elegir otro nombre para el archivo de imagen. La salida del programa se muestra a continuación.

Enumere todas las tablas de una base de datos

En una base de datos, podemos crear una gran cantidad de tablas. Por lo tanto, también es necesario enumerar todas las tablas presentes en una base de datos. Para enumerar las tablas presentes en una base de datos, consulte la tabla sqlite_master utilizando la instrucción SELECT de SQL. La sintaxis de la consulta será:

SELECT name FROM sqlite_master WHERE type="table"

Así es como usamos esta consulta para listar todas las tablas presentes en nuestra base de datos.

import sqlite3

conn = sqlite3.connect("sample.db")
print("n [+] Successfully connected to the database")
cur = conn.cursor()
print("n [+] Cursor has been set up successfully")
cur.execute("SELECT name from sqlite_master where type="table"")
rows = cur.fetchall()
print(rows)
cur.close()
conn.commit()
conn.close()

El código anterior enumerará todas las tablas presentes en nuestra base de datos. La salida producida por el código cuando se ejecuta es la siguiente. Es posible que vea algún otro resultado en función de las tablas que haya creado en la base de datos.

Identificar los cambios totales desde que se conectó a la base de datos

En cualquier situación, es útil identificar el número de filas que se han modificado, insertado o eliminado desde que se conectó la base de datos. Para eso, use el cambios_total () método del objeto de conexión, que devolverá el número total de filas de la base de datos que se han visto afectadas desde la conexión. Veamos una demostración de ejemplo para saber cómo funciona.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("n [+] Successfully connected to both the database")
cur = conn.cursor()
print("n [+] Both the Cursor has been set up successfully")
cur.execute("INSERT INTO student (id, name, images, marks) VALUES (140, 'David',' ',99 )")
cur.execute("INSERT INTO student (id, name,images, marks) VALUES (150, 'Sam', ' ', 97)")
changes = conn.total_changes
print("n [+] Total now of rows changes is :",changes)
conn.commit()
cur.close()
conn.close()

El programa anterior imprimirá el número de cambios de filas en la conexión actual. Verá el siguiente resultado.

Revertir en SQLite

Cuando se trata de deshacer algunas tareas, puede usar la función rollback (). Este método se puede utilizar para deshacer la tarea que se ha realizado después de la última confirmación. Consulte el siguiente ejemplo para ver una ilustración.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("n [+] Successfully connected to both the database")
cur = conn.cursor()
print("n [+] Both the Cursor has been set up successfully")
cur.execute("INSERT INTO student (id, name, images, marks) VALUES (100001, 'David',' ',99 )")
cur.execute("INSERT INTO student (id, name,images, marks) VALUES (100002, 'Sam', ' ', 97)")
conn.commit()
print("n [+] The Two row has been inserted successfully")
cur.execute("SELECT * FROM student")
first = cur.fetchall()
print("n [+] The new records in the database are :")
for i in first:
    print(i)

cur.execute("INSERT INTO student (id, name, images, marks) VALUES (10003, 'Kishan', ' ', 100)")
cur.execute("INSERT INTO student (id, name, images, marks) VALUES (10004, 'Ankit', ' ', 100)")
print("n [+] The Two row has been inserted successfully but not committed")
conn.rollback()
print("n [+] We have roll back the previous commands so the new data will not been inserted")
conn.commit()
cur.execute("SELECT * FROM student")
second = cur.fetchall()
print("n [+] The new records in the database are :")
for i in second:
     print(i)

cur.close()
conn.close()

En el ejemplo anterior, las dos primeras declaraciones de inserción insertarán los datos como se indica, pero las dos últimas declaraciones de inserción se revertirán para que no agreguen ningún dato a la tabla. La salida será como se muestra a continuación.

Copia de seguridad de una base de datos

Mientras trabaja con la base de datos, es esencial realizar una copia de seguridad de la base de datos. El módulo sqlite3 proporciona una función para realizar la copia de seguridad de la base de datos. Usando el método backup () del objeto de conexión, podemos hacer la copia de seguridad de la base de datos SQLite. La sintaxis básica del método de copia de seguridad es:

backup(target, *, pages=0, progress=None, name="main", sleep=0.250)

Por defecto, o cuando paginas son ambos 0 o un número entero negativo, toda la base de datos se copia en un solo paso, lo que es preferible para una base de datos pequeña; de lo contrario, el método realiza una copia en bucle hasta paginas en un momento que podría hacerse con la extensa base de datos. los nombre El argumento muestra el nombre de la base de datos que se copiará: debe ser una cadena que contenga el valor predeterminado, para indicar la base de datos principal o para indicar la base de datos temporal. los dormir El argumento especifica el tiempo en segundos para dormir entre los intentos de hacer una copia de seguridad de las páginas restantes. Puede ser un valor entero o de coma flotante.

Hagamos una copia de seguridad del database.db base de datos que hemos estado usando en el tutorial.

import sqlite3
conn_main = sqlite3.connect("sample.db")
conn_backup = sqlite3.connect("sample_backup.db")
print("n [+] Successfully connected to both the database")
cur_main = conn_main.cursor()
cur_backup = conn_backup.cursor()
print("n [+] Both the Cursor has been set up successfully")
conn_main.backup(conn_backup, pages=0, progress=None, name="main")
print("The database has been backup successfully")
cur_main.close()
cur_backup.close()
conn_main.commit()
conn_backup.commit()
conn_main.close()
conn_backup.close()

En el código anterior, las dos bases de datos están conectadas, una es la base de datos de la que queremos hacer una copia de seguridad y la segunda es la base de datos en la que realizaremos la copia de seguridad. Utilizar el respaldo() método del primer objeto de conexión a la base de datos para realizar una copia de seguridad. Esta función acepta el objeto de conexión de la segunda base de datos como destino para crear una copia de seguridad en la otra base de datos. Utilizar el páginas = 0 argumentos, por lo que el proceso se llevará a cabo en un solo paso, lo que se recomienda para bases de datos pequeñas. Este programa creará una nueva muestra de nombre de base de datos_backup.db y rellénelo con la copia de seguridad de la primera base de datos. Es posible que vea que se ha creado una nueva base de datos en la carpeta actual con el mismo tamaño de archivo que el anterior.

Volcado de una base de datos SQLite

El volcado de bases de datos es una tarea importante. Por lo general, un archivo de volcado es un conjunto de declaraciones SQL para los datos, que generalmente se utiliza para realizar copias de seguridad. Podemos volcar una base de datos usando el método dump (). Vea el siguiente ejemplo para saber cómo eliminar una base de datos SQLite.

import sqlite3

con = sqlite3.connect("database.db")
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%sn' % line)

El programa anterior volcará la base de datos sample.db y guardará los datos volcados en un archivo llamado dump.sql. Puede ver los datos presentes en el directorio donde están los archivos de Python y abrirlos usando cualquier editor de texto.

método executemany () de SQLite3

los ejecutar muchos () El método ejecuta un comando SQL contra todas las secuencias de parámetros o asignaciones que se encuentran en la secuencia. seq_of_parameters. Para simplificar, este método se puede utilizar para ejecutar la mayoría de los comandos SQL en una línea. Por ejemplo, podemos insertar cualquier número de filas a través de una lista de Python usando este comando. Consulte el ejemplo siguiente para ver la ilustración.

import sqlite3

conn = sqlite3.connect("sample.db")
print("n [+] Successfully connected to the database")
cur = conn.cursor()
print("n [+] Cursor has been set up successfully")
python_list = [(10000000 ,'vivek',' ','10'),
               (100000001,'rose',' ','21'),
               (100000002,'robin',' ','31'),
               (100000003,'Dev',' ','4'),
               (100000004,'michael',' ','52')
]
cur.executemany("INSERT INTO student (id, name, images, marks) VALUES (?,?,?,?)",python_list)
print("n [+] All the Data has been inserted Successfully ")
cur.close()
conn.commit()
conn.close()

El programa anterior insertará todos los datos proporcionados en la lista de Python. La salida producida por el programa se muestra a continuación.

Eliminar registros de una tabla

Podemos usar la operación DELETE para borrar registros de una tabla. Podemos eliminar rápidamente una fila utilizando la operación DELETE con la cláusula WHERE. La sintaxis básica de la instrucción DELETE es:

DELETE from table_name WHERE some_condition;

Veamos un ejemplo. Eliminaremos la fila con id 1001 de la tabla de empleados de nuestra base de datos.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("n [+] Successfully connected to the database")
cur = conn.cursor()
print("n [+] Cursor has been set up successfully")
cur.execute("DELETE FROM student WHERE id=1001")
print("n [+] The row has been deleted Successfully ")
cur.execute("SELECT * FROM student")
data = cur.fetchall()
for row in data:
   print(row)

cur.close()
conn.commit()
conn.close()

El código anterior eliminará la fila con el ID 1001. Puede ver en la devolución de la instrucción SELECT que la fila ha sido eliminada. La salida del programa se muestra a continuación.

Suelta una mesa

Podemos eliminar o eliminar rápidamente una tabla usando la declaración SQLite DROP. La sintaxis de la declaración DROP es la que se muestra a continuación:

DROP table table_name 

Si la tabla no existe, entonces SQLite arrojará un error, por lo que para evitar esto, podemos usar el si existe etiqueta con la declaración DROP. Vea la siguiente sintaxis:

DROP table if exists table_name

Veamos cómo podemos usar esta declaración con Python. sqlite3 módulo para eliminar una tabla. En este programa, eliminaremos el estudiante tabla que hemos creado anteriormente.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("n [+] Successfully connected to the database")
cur = conn.cursor()
print("n [+] Cursor has been set up successfully")
cur.execute("DROP TABLE IF EXISTS student")
print("n [+] The table has been drop successfully")
cur.close()
conn.commit()
conn.close()

El programa anterior eliminará la tabla. estudiante desde el muestra base de datos. Podemos usar el comando list table que hemos visto anteriormente para ver si la tabla está eliminada. La salida del programa se muestra a continuación.

Excepciones de la base de datos SQLite

Algunas excepciones de la base de datos SQLite pueden surgir debido a algún error. Veamos un poco de cuándo surgieron esos errores.

• sqlite3.Warning: Es una subclase de excepción. Esta excepción muestra algunas advertencias, que pueden ignorarse en muchos casos.
• sqlite3.Error: también es una subclase de excepción. Es la clase base de todas las demás excepciones en el sqlite3 módulo.
• sqlite3.DatabaseError: Son los errores que se plantean debido a algunos errores en las bases de datos. Por ejemplo: - Si intentamos conectarnos a una base de datos cifrada o un archivo de base de datos incorrecto, entonces mostrará un DatabaseError que los datos están cifrados o no es un archivo de base de datos válido.
• sqlite3.IntegrityError: esta excepción es una subclase de DatabaseError. Obtendremos esta excepción cuando la integridad relacional de una base de datos se vea afectada, por ejemplo, una falla en la verificación de la clave externa.
• sqlite3.ProgrammingError: esta excepción también es una subclase de DatabaseError. Esta excepción se genera debido a errores de programación, por ejemplo, crear una tabla con el mismo nombre que ya existe, error de sintaxis en las consultas SQL, etc.
• sqlite3.OperationalError: también es una subclase de DatabaseError. Esta excepción surgió por errores relacionados con el funcionamiento de la base de datos y que no están bajo nuestro control. Por ejemplo, una desconexión accidental del sistema, un servidor inactivo, un tiempo de espera, problemas con la fuente de datos, apagado de la máquina, etc.
• sqlite3.NotSupportedError: esta excepción se genera cuando la base de datos no admite una API de base de datos que utilizó.

Esta es la lista de todas las excepciones de SQLite; podemos manejar esas excepciones en nuestros programas usando el método básico de manejo de errores try / except de Python.

Conclusión