Un tutorial de SQLite con Python

    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.

     

    Etiquetas:

    Deja una respuesta

    Tu direcci贸n de correo electr贸nico no ser谩 publicada. Los campos obligatorios est谩n marcados con *