Un tutorial de SQLite con Python

Este tutorial cubrirá el uso de SQLite en combinación con la interfaz sqlite3 de Python. SQLite es una base de datos relacional de un solo archivo que se incluye con la mayoría de Py...

Introducción

Este tutorial cubrirá el uso de SQLite en combinación con la interfaz sqlite3 de Python. SQLite es una base de datos relacional de un solo archivo incluida con la mayoría de las instalaciones estándar de Python. SQLite suele ser la tecnología elegida para aplicaciones pequeñas, en particular las de sistemas integrados y dispositivos como teléfonos y tabletas, dispositivos inteligentes e instrumentos. Sin embargo, no es raro escuchar que se usa para aplicaciones web y de escritorio pequeñas y medianas.

Creación de una base de datos y establecimiento de una conexión {#creación de una base de datos y establecimiento de una conexión}

Crear una nueva base de datos SQLite es tan simple como crear una conexión usando el módulo sqlite3 en la biblioteca estándar de Python. Para establecer una conexión, todo lo que necesita hacer es pasar una ruta de archivo al método connect(...) en el módulo sqlite3, y si la base de datos representada por el archivo no existe, se creará una en esa ruta.

1
2
import sqlite3
con = sqlite3.connect('/path/to/file/db.sqlite3')

Descubrirá que en la programación diaria de la base de datos estará constantemente creando conexiones a su base de datos, por lo que es una buena idea envolver esta declaración de conexión simple en una función generalizada reutilizable.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# db_utils.py
import os
import sqlite3

# create a default path to connect to and create (if necessary) a database
# called 'database.sqlite3' in the same directory as this script
DEFAULT_PATH = os.path.join(os.path.dirname(__file__), 'database.sqlite3')

def db_connect(db_path=DEFAULT_PATH):
    con = sqlite3.connect(db_path)
    return con

Creación de tablas

Para crear tablas de bases de datos, debe tener una idea de la estructura de los datos que desea almacenar. Hay muchas consideraciones de diseño que intervienen en la definición de las tablas de una base de datos relacional, sobre las cuales se han escrito libros completos. No entraré en los detalles de esta práctica y, en cambio, dejaré que el lector investigue más a fondo.

Sin embargo, para ayudar en nuestra discusión sobre la programación de la base de datos SQLite con Python, partiré de la premisa de que es necesario crear una base de datos para una librería ficticia que tenga los siguientes datos ya recopilados sobre las ventas de libros.

precio del producto fecha del cliente


Alan Turing 22/02/1944 Introducción a la combinatoria 7,99 Donald Knuth 7/3/1967 Una guía para escribir cuentos 17,99 Donald Knuth 3/7/1967 Estructuras de datos y algoritmos 11,99 Edgar Codd 1/12/1969 Teoría de conjuntos avanzada 16,99

Al inspeccionar estos datos, es evidente que contienen información sobre clientes, productos y pedidos. Un patrón común en el diseño de bases de datos para sistemas transaccionales de este tipo es dividir los pedidos en dos tablas adicionales, pedidos y elementos de línea (a veces denominados detalles del pedido) para lograr una mayor normalización.

En un intérprete de Python, en el mismo directorio que el módulo db_utils.py definido anteriormente, ingrese el SQL para crear las tablas de clientes y productos a continuación:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
>>> from db_utils import db_connect
>>> con = db_connect() # connect to the database
>>> cur = con.cursor() # instantiate a cursor obj
>>> customers_sql = """
... CREATE TABLE customers (
...     id integer PRIMARY KEY,
...     first_name text NOT NULL,
...     last_name text NOT NULL)"""
>>> cur.execute(customers_sql)
>>> products_sql = """
... CREATE TABLE products (
...     id integer PRIMARY KEY,
...     name text NOT NULL,
...     price real NOT NULL)"""
>>> cur.execute(products_sql)

El código anterior crea un objeto de conexión y luego lo usa para instanciar un objeto de cursor. El objeto de cursor se utiliza para ejecutar sentencias SQL en la base de datos SQLite.

Con el cursor creado, escribí el SQL para crear la tabla de clientes, dándole una clave principal junto con un campo de texto de nombre y apellido y asignándolo a una variable llamada customers_sql. Luego llamo al método execute(...) del objeto del cursor pasándole la variable customers_sql. Luego creo una tabla de productos de manera similar.

Puede consultar la tabla sqlite_master, una tabla de metadatos de SQLite incorporada, para verificar que los comandos anteriores se ejecutaron correctamente.

Para ver todas las tablas en la base de datos actualmente conectada, consulta la columna name de la tabla sqlite_master donde type es igual a "table".

1
2
3
4
>>> cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
<sqlite3.Cursor object at 0x104ff7ce0>
>>> print(cur.fetchall())
[('customers',), ('products',)]

Para ver el esquema de las tablas, consulta la columna sql de la misma tabla donde el tipo sigue siendo "tabla" y el nombre es igual a "clientes" y/o " productos".

1
2
3
4
5
6
7
8
>>> cur.execute("""SELECT sql FROM sqlite_master WHERE type='table'
… AND name='customers'""")
<sqlite3.Cursor object at 0x104ff7ce0>
>>> print(cur.fetchone()[0])
CREATE TABLE customers (
    id integer PRIMARY KEY,
    first_name text NOT NULL,
    last_name text NOT NULL)

La siguiente tabla a definir será la tabla pedidos que asocia clientes a pedidos mediante clave foránea y la fecha de su compra. Dado que SQLite no admite un tipo de datos de fecha/hora real (o una clase de datos para ser coherente con la lengua vernácula de SQLite), todas las fechas se representarán como valores de texto.

1
2
3
4
5
6
7
>>> orders_sql = """
... CREATE TABLE orders (
...     id integer PRIMARY KEY,
...     date text NOT NULL,
...     customer_id integer,
...     FOREIGN KEY (customer_id) REFERENCES customers (id))"""
>>> cur.execute(orders_sql)

La tabla final a definir será la tabla elementos de línea que da una contabilidad detallada de los productos en cada pedido.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
lineitems_sql = """
... CREATE TABLE lineitems (
...     id integer PRIMARY KEY,
...     quantity integer NOT NULL,
...     total real NOT NULL,
...     product_id integer,
...     order_id integer,
...     FOREIGN KEY (product_id) REFERENCES products (id),
...     FOREIGN KEY (order_id) REFERENCES orders (id))"""
>>> cur.execute(lineitems_sql)

Cargando los datos

En esta sección, demostraré cómo INSERTAR nuestros datos de muestra en las tablas que acabamos de crear. Un punto de partida natural sería llenar primero la tabla productos porque sin productos no podemos tener una venta y, por lo tanto, no tendríamos las claves externas para relacionarnos con las líneas de pedido y los pedidos. Mirando los datos de muestra, veo que hay cuatro productos:

  • Introducción a la Combinatoria ($7.99)
  • Una guía para escribir cuentos cortos ($17.99)
  • Estructuras de datos y algoritmos ($11.99)
  • Teoría de conjuntos avanzada ($16.99)

El flujo de trabajo para ejecutar declaraciones INSERT es simplemente:

  1. Conectarse a la base de datos
  2. Crea un objeto cursor
  3. Escriba una instrucción SQL de inserción parametrizada y guárdela como una variable
  4. Llame al método de ejecución en el objeto del cursor pasándole la variable sql y los valores, como una tupla, para insertarlos en la tabla

Dado este esquema general, escribamos algo más de código.

1
2
3
4
5
6
7
>>> con = db_connect()
>>> cur = con.cursor()
>>> product_sql = "INSERT INTO products (name, price) VALUES (?, ?)"
>>> cur.execute(product_sql, ('Introduction to Combinatorics', 7.99))
>>> cur.execute(product_sql, ('A Guide to Writing Short Stories', 17.99))
>>> cur.execute(product_sql, ('Data Structures and Algorithms', 11.99))
>>> cur.execute(product_sql, ('Advanced Set Theory', 16.99))

El código anterior probablemente parezca bastante obvio, pero permítanme discutirlo un poco, ya que aquí están sucediendo algunas cosas importantes. La declaración de inserción sigue la sintaxis SQL estándar excepto por el bit ?. Los ?'s son en realidad marcadores de posición en lo que se conoce como "consulta parametrizada".

Las consultas parametrizadas son una característica importante de prácticamente todas las interfaces de bases de datos para lenguajes de programación modernos de alto nivel, como el módulo sqlite3 en Python. Este tipo de consulta sirve para mejorar la eficiencia de las consultas que se repiten varias veces. Quizás más importante, también desinfectan las entradas que toman el lugar de los marcadores de posición ? que se pasan durante la llamada a [ejecutar](https://docs.python.org/3/library/sqlite3.html#sqlite3 .Cursor.execute) del objeto cursor para evitar entradas nefastas que conduzcan a inyección SQL. El siguiente es un cómic del popular blog xkcd.com que describe los peligros de la inyección SQL.

Para completar las tablas restantes, vamos a seguir un patrón ligeramente diferente para cambiar un poco las cosas. El flujo de trabajo para cada pedido, identificado por una combinación de nombre y apellido del cliente y la fecha de compra, será:

  1. Inserte el nuevo cliente en la tabla de clientes y recupere su ID de clave principal
  2. Cree una entrada de pedido basada en la identificación del cliente y la fecha de compra, luego recupere su identificación de clave principal
  3. Para cada producto en el pedido, determine su ID de clave principal y cree una entrada de artículo de línea que asocie el pedido y el producto.

Para simplificarnos las cosas, permítanos hacer una búsqueda rápida de todos nuestros productos. Por ahora, no se preocupe demasiado por la mecánica de la instrucción SELECT SQL, ya que le dedicaremos una sección en breve.

1
2
3
4
5
6
7
8
9
>>> cur.execute("SELECT id, name, price FROM products")
>>> formatted_result = [f"{id:<5}{name:<35}{price:>5}" for id, name, price in cur.fetchall()]
>>> id, product, price = "Id", "Product", "Price"
>>> print('\n'.join([f"{id:<5}{product:<35}{price:>5}"] + formatted_result))
Id   Product                            Price
1    Introduction to Combinatorics       7.99
2    A Guide to Writing Short Stories   17.99
3    Data Structures and Algorithms     11.99
4    Advanced Set Theory                16.99

El primer pedido fue realizado el 22 de febrero de 1944 por Alan Turing, quien compró Introducción a la combinatoria por $7,99.

Comience creando un nuevo registro de cliente para el Sr. Turing y luego determine su ID de clave principal accediendo al campo lastrowid del objeto del cursor.

1
2
3
4
5
>>> customer_sql = "INSERT INTO customers (first_name, last_name) VALUES (?, ?)"
>>> cur.execute(customer_sql, ('Alan', 'Turing'))
>>> customer_id = cur.lastrowid
>>> print(customer_id)
1

Ahora podemos crear una entrada de pedido, recopilar el nuevo valor de identificación del pedido y asociarlo a una entrada de artículo junto con el producto que ordenó el Sr. Turing.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> order_sql = "INSERT INTO orders (date, customer_id) VALUES (?, ?)"
>>> date = "1944-02-22" # ISO formatted date 
>>> cur.execute(order_sql, (date, customer_id))
>>> order_id = cur.lastrowid
>>> print(order_id)
1
>>> li_sql = """INSERT INTO lineitems 
...       (order_id, product_id, quantity, total)
...     VALUES (?, ?, ?, ?)"""
>>> product_id = 1
>>> cur.execute(li_sql, (order_id, 1, 1, 7.99))

Los registros restantes se cargan exactamente igual, excepto el pedido realizado a Donald Knuth, que recibirá dos entradas de elementos de línea. Sin embargo, la naturaleza repetitiva de dicha tarea exige a gritos la necesidad de envolver estas funcionalidades en funciones reutilizables. En el módulo db_utils.py agregue el siguiente código:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
def create_customer(con, first_name, last_name):
    sql = """
        INSERT INTO customers (first_name, last_name)
        VALUES (?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (first_name, last_name))
    return cur.lastrowid

def create_order(con, customer_id, date):
    sql = """
        INSERT INTO orders (customer_id, date)
        VALUES (?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (customer_id, date))
    return cur.lastrowid

def create_lineitem(con, order_id, product_id, qty, total):
    sql = """
        INSERT INTO lineitems
            (order_id, product_id, quantity, total)
        VALUES (?, ?, ?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (order_id, product_id, qty, total))
    return cur.lastrowid

¡Awh, ahora podemos trabajar con cierta eficiencia!

Necesitará salir() de su intérprete de Python y volver a cargarlo para que sus nuevas funciones sean accesibles en el intérprete.

1
2
3
4
5
6
7
8
9
>>> from db_utils import db_connect, create_customer, create_order, create_lineitem
>>> con = db_connect()
>>> knuth_id = create_customer(con, 'Donald', 'Knuth')
>>> knuth_order = create_order(con, knuth_id, '1967-07-03')
>>> knuth_li1 = create_lineitem(con, knuth_order, 2, 1, 17.99)
>>> knuth_li2 = create_lineitem(con, knuth_order, 3, 1, 11.99)
>>> codd_id = create_customer(con, 'Edgar', 'Codd')
>>> codd_order = create_order(con, codd_id, '1969-01-12')
>>> codd_li = create_lineitem(con, codd_order, 4, 1, 16.99)

Me siento obligado a dar un consejo adicional como estudiante de creación de software. Cuando se encuentra realizando múltiples manipulaciones de bases de datos (INSERTAR en este caso) para lograr lo que en realidad es una tarea acumulativa (es decir, crear un pedido), es mejor envolver las subtareas (crear cliente, pedido, luego artículos de línea) en un transacción de base de datos única para que pueda confirmar si tiene éxito o retroceder si ocurre un error en el camino.

Esto se vería algo como esto:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
try:
    codd_id = create_customer(con, 'Edgar', 'Codd')
    codd_order = create_order(con, codd_id, '1969-01-12')
    codd_li = create_lineitem(con, codd_order, 4, 1, 16.99)

    # commit the statements
    con.commit()
except:
    # rollback all database actions since last commit
    con.rollback()
    raise RuntimeError("Uh oh, an error occurred ...")

Quiero terminar esta sección con una demostración rápida de cómo ACTUALIZAR un registro existente en la base de datos. Actualicemos el precio de la Guía para escribir cuentos a 10,99 (en oferta).

1
2
>>> update_sql = "UPDATE products SET price = ? WHERE id = ?"
>>> cur.execute(update_sql, (10.99, 2))

Consultando la base de datos

En general, la acción más común que se realiza en una base de datos es la recuperación de algunos de los datos almacenados en ella a través de una instrucción SELECT. Para esta sección, demostraré cómo usar la interfaz sqlite3 para realizar consultas SELECT simples.

Para realizar una consulta básica de varias filas de la tabla de clientes, pasa una declaración SELECT al método ejecutar (...) del objeto del cursor. Después de esto, puede iterar sobre los resultados de la consulta llamando al método fetchall() del mismo objeto de cursor.

1
2
3
4
5
6
7
>>> cur.execute("SELECT id, first_name, last_name FROM customers")
>>> results = cur.fetchall()
>>> for row in results:
...     print(row)
(1, 'Alan', 'Turing')
(2, 'Donald', 'Knuth')
(3, 'Edgar', 'Codd')

Digamos que le gustaría recuperar un registro de la base de datos. Puede hacer esto escribiendo una consulta más específica, por ejemplo, para la identificación de 2 de Donald Knuth, y luego llamando al método fetchone() del objeto del cursor.

1
2
3
4
>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> print(result)
(2, 'Donald', 'Knuth')

¿Ves cómo la fila individual de cada resultado tiene forma de tupla? Bueno, mientras que las tuplas son una estructura de datos Pythonic muy útil para algunos casos de uso de programación, muchas personas las encuentran un poco complicadas cuando se trata de la tarea de recuperación de datos. Da la casualidad de que hay una manera de representar los datos de una manera que quizás sea más flexible para algunos. Todo lo que necesita hacer es establecer el método row_factory del objeto de conexión en algo más adecuado, como sqlite3.Row. Esto le dará la posibilidad de acceder a los elementos individuales de una fila por posición o valor de palabra clave.

1
2
3
4
5
6
7
8
>>> import sqlite3
>>> con.row_factory = sqlite3.Row
>>> cur = con.cursor()
>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> id, first_name, last_name = result['id'], result['first_name'], result['last_name']
>>> print(f"Customer: {first_name} {last_name}'s id is {id}")
Customer: Donald Knuth's id is 2

Conclusión

En este artículo, ofrecí una breve demostración de lo que creo que son las características y funcionalidades más importantes de la interfaz de Python sqlite3 para la base de datos SQLite de un solo archivo liviano que viene preinstalado con la mayoría de las instalaciones de Python. También traté de dar algunos consejos sobre las mejores prácticas en lo que respecta a la programación de bases de datos, pero advierto al recién llegado que las complejidades de la programación de bases de datos son generalmente una de las más propensas a los agujeros de seguridad a nivel empresarial y más allá. el conocimiento es necesario antes de tal empresa.

Como siempre, les agradezco su lectura y agradezco los comentarios y críticas a continuación. n.