Un tutorial de SQLite con Python

U

Introducción

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

Creación de una base de datos y 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 connect(...) en el módulo sqlite3, y si la base de datos representada por el archivo no existe, se creará una en esa ruta.

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

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

# 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

Crear tablas

Para crear tablas de base de datos, debe tener una idea de la estructura de los datos que le interesa almacenar. Hay muchas consideraciones de diseño que intervienen en la definición de las tablas de una base de datos relacional, sobre las que 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 bases de datos SQLite con Python, trabajaré con la premisa de que es necesario crear una base de datos para una librería ficticia que ya tenga los datos a continuación recopilados sobre ventas de libros.

fecha del cliente precio del producto

Alan Turing22/02/1944Introducción a la combinatoria7,99
Donald Knuth3/7/1967Una guía para escribir historias cortas17,99
Donald Knuth3/7/1967Estructuras de datos y algoritmos11,99
Edgar Codd12/01/1969Teoría avanzada de conjuntos16,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:

>>> 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 crear una instancia de un objeto de cursor. El objeto cursor se utiliza para ejecutar sentencias SQL en la base de datos SQLite.

Con el cursor creado, luego 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 lo asigné a una variable llamada customers_sql. Entonces llamo al execute(...) método del objeto cursor pasándole el customers_sql variable. Luego creo una tabla de productos de manera similar.

Puede consultar el sqlite_master table, una tabla de metadatos SQLite incorporada, para verificar que los comandos anteriores fueron exitosos.

Para ver todas las tablas en la base de datos actualmente conectada, consulte el name columna de la sqlite_master mesa donde el type es igual a “tabla”.

>>> 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, consulte el sql columna de la misma tabla donde el type sigue siendo “mesa” y el name es igual a “clientes” y / o “productos”.

>>> 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 de pedidos que asocia a los clientes a los pedidos mediante una clave externa 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.

>>> 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 de artículos de línea que brinda una contabilidad detallada de los productos en cada pedido.

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, mostraré cómo INSERTAR nuestros datos de muestra en las tablas recién creadas. Un lugar de partida natural sería rellenar primero la tabla de productos porque sin productos no podemos tener una venta y, por lo tanto, no tendríamos las claves externas para relacionarnos con los artículos de línea y los pedidos. Al observar los datos de muestra, veo que hay cuatro productos:

  • Introducción a la combinatoria ($ 7,99)
  • Una guía para escribir historias cortas ($ 17,99)
  • Estructuras de datos y algoritmos ($ 11,99)
  • Teoría avanzada de conjuntos ($ 16,99)

El flujo de trabajo para ejecutar declaraciones INSERT es simplemente:

  • Conectarse a la base de datos
  • Crea un objeto de cursor
  • Escriba una declaración SQL de inserción parametrizada y almacénela como una variable
  • Llame al método de ejecución en el objeto cursor pasándole la variable sql y los valores, como una tupla, para ser insertados en la tabla

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

>>> 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 parece bastante obvio, pero permítanme discutirlo un poco ya que hay algunas cosas importantes que están sucediendo aquí. La declaración de inserción sigue la sintaxis estándar de SQL, excepto por la ? poco. los ?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 los lenguajes de programación modernos de alto nivel, como el módulo sqlite3 en Python. Este tipo de consulta sirve para mejorar la eficacia de las consultas que se repiten varias veces. Quizás más importante, también desinfectan los insumos que toman el lugar de los ? marcadores de posición que se pasan durante la llamada al ejecutar método del objeto cursor para evitar entradas nefastas que conducen a inyección SQL. El siguiente es un cómic del popular xkcd.com blog 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á:

  • Inserte el nuevo cliente en la tabla de clientes y recupere su ID de clave principal
  • 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
  • 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.

>>> 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.

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

>>> 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 de pedido y asociarlo a una entrada de artículo de línea junto con el producto que ordenó el Sr. Turing.

>>> 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 a excepción del pedido realizado a Donald Knuth, que recibirá dos entradas de artículos de línea. Sin embargo, la naturaleza repetitiva de tal tarea está clamando la necesidad de envolver estas funcionalidades en funciones reutilizables. En el módulo db_utils.py agregue el siguiente código:

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 algo de eficiencia!

Necesitaras exit() su intérprete de Python y vuelva a cargarlo para que sus nuevas funciones sean accesibles en el intérprete.

>>> 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 artesanía del software. Cuando se encuentre realizando múltiples manipulaciones de la base de datos (INSERTs en este caso) para lograr lo que en realidad es una tarea acumulativa (es decir, crear un pedido), es mejor agrupar las subtareas (crear cliente, pedido y luego artículos de línea) transacción de base de datos única para que pueda comprometerse con éxito o retroceder si se produce un error en el camino.

Esto se vería así:

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 historias cortas a 10,99 (saldrá a la venta).

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

Consultar la base de datos

Generalmente, 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, mostraré 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, pase una instrucción SELECT a la execute(...) método del objeto cursor. Después de esto, puede iterar sobre los resultados de la consulta llamando al fetchall() método del mismo objeto cursor.

>>> 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, en su lugar, 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 Donald Knuth de 2, y luego llamando fetchone() método del objeto cursor.

>>> 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 la forma de una tupla? Bueno, aunque 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 forma de representar los datos de una manera que quizás sea más flexible para algunos. Todo lo que necesita hacer es configurar el row_factory método del objeto de conexión a 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.

>>> 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 considero que son las características y funcionalidades más importantes de la interfaz sqlite3 Python para la base de datos SQLite liviana de un solo archivo que viene preempaquetada con la mayoría de las instalaciones de Python. También traté de dar algunos consejos sobre las mejores prácticas cuando se trata de la programación de bases de datos, pero advierto al recién llegado que las complejidades de la programación de bases de datos es generalmente una de las más propensas a los agujeros de seguridad a nivel empresarial y más. el conocimiento es necesario antes de tal empresa.

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

 

About the author

Ramiro de la Vega

Bienvenido a Pharos.sh

Soy Ramiro de la Vega, Estadounidense con raíces Españolas. Empecé a programar hace casi 20 años cuando era muy jovencito.

Espero que en mi web encuentres la inspiración y ayuda que necesitas para adentrarte en el fantástico mundo de la programación y conseguir tus objetivos por difíciles que sean.

Add comment

Sobre mi

Últimos Post

Etiquetas

Esta web utiliza cookies propias para su correcto funcionamiento. Al hacer clic en el botón Aceptar, aceptas el uso de estas tecnologías y el procesamiento de tus datos para estos propósitos. Más información
Privacidad