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 es a menudo la tecnología de elección para aplicaciones pequeñas, particularmente las de sistemas y dispositivos integrados como teléfonos y tabletas, electrodomésticos inteligentes e instrumentos. Sin embargo, no es raro escuchar que se utiliza para pequeñas y medianas aplicaciones web y de escritorio.,

crear una base de datos y hacer 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.,

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

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

crear tablas

para crear tablas de base de datos necesita tener una idea de la estructura de los datos que está interesado en almacenar. Hay muchas consideraciones de diseño que entran en la definición de las tablas de una base de datos relacional, sobre la que se han escrito libros enteros., No entraré en los detalles de esta práctica y en su lugar dejaré que el lector investigue más a fondo.

sin embargo, para ayudar en nuestra discusión de la programación de la base de datos SQLite con Python, trabajaré fuera de la premisa de que se debe crear una base de datos para una tienda de libros ficticia que tenga los siguientes datos ya recopilados sobre ventas de libros.,

customer date product price
Alan Turing 2/22/1944 Introduction to Combinatorics 7.99
Donald Knuth 7/3/1967 A Guide to Writing Short Stories 17.99
Donald Knuth 7/3/1967 Data Structures and Algorithms 11.99
Edgar Codd 1/12/1969 Advanced Set Theory 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 las órdenes en dos tablas adicionales, órdenes y elementos de línea (a veces denominados detalles de la orden) para lograr una mayor normalización.

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

el código anterior crea un objeto de conexión y lo usa para crear una instancia de un objeto cursor. El objeto 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 primaria 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 cursor pasándole la variable customers_sql. Luego creo una tabla de productos de una manera similar.

Puede consultar la tabla sqlite_master, una tabla de metadatos SQLite incorporada, para verificar que los comandos anteriores tuvieron éxito.

para ver todas las tablas en la base de datos actualmente conectada, consulte la columna name de la tabla sqlite_master donde la tabla type es igual a «tabla».,

para ver el esquema de las tablas, consulte la columna sql de la misma tabla donde type sigue siendo «tabla» y name es igual a «clientes» y / o «productos».

la siguiente tabla a definir será la tabla de pedidos que asocia a los clientes a los pedidos a través de 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 que sea coherente con la lengua vernácula de SQLite), todas las fechas se representarán como valores de texto.,

la tabla final a definir será la tabla de partidas que da una contabilidad detallada de los productos en cada orden.

Cargando los datos

en esta sección demostraré cómo insertar nuestros datos de muestra en las tablas recién creadas. Un punto 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., Mirando los datos de muestra veo que hay cuatro productos:

el flujo de trabajo para ejecutar instrucciones INSERT es simplemente:

  1. conectarse a la base de datos
  2. Crear un objeto cursor
  3. Escribir una instrucción SQL insert parametrizada y almacenar como una variable
  4. llamar al método execute en el objeto cursor pasándole la variable sql y los valores, como una tupla, que se insertarán en la tabla

dado este esquema general, escribe más código.,

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 instrucción insert sigue la sintaxis SQL estándar excepto para el bit ?. Los ? son en realidad marcadores de posición en lo que se conoce como una «consulta parametrizada».

Las consultas parametrizadas son una característica importante de esencialmente todas las interfaces de base 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 al método execute del objeto cursor para evitar entradas nefastas que conducen a la inyección SQL. El siguiente es un cómic de la popular xkcd.com blog que describe los peligros de la inyección SQL.

Para rellenar 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. insertar el nuevo cliente en la tabla Clientes y recuperar su ID de clave primaria
  2. Crear una entrada de pedido basada en el id de cliente y la fecha de compra luego recuperar su ID de clave primaria
  3. Para cada producto en el pedido determinar su id de clave primaria y crear una entrada de elemento de línea asociando el pedido y el producto

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

el primer pedido fue realizado el 22 de febrero de 1944 por Alan Turing quien compró Introduction to Combinatorics por 7 7.99.

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

Ahora podemos crear una entrada de pedido, recopilar el nuevo valor de ID de pedido y asociarlo a una entrada de elemento de línea junto con el producto que Mr.Turing ordenó.,

los registros restantes se cargan exactamente igual, excepto para el pedido realizado a Donald Knuth, que recibirá dos entradas de elementos de línea. Sin embargo, la naturaleza repetitiva de tal tarea está clamando por la necesidad de envolver estas funcionalidades en funciones reutilizables. En el db_utils.py módulo añadir el siguiente código:

Awh, ahora podemos trabajar con un poco de eficiencia!

necesitará exit() su intérprete de Python y recargarlo para que sus nuevas funciones sean accesibles en el intérprete.,

Me siento obligado a dar un consejo adicional como estudiante de artesanía de software. Cuando se encuentre haciendo varias manipulaciones de la base de datos (inserciones 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 y luego elementos de línea) en una sola transacción de la base de datos para que pueda confirmar el éxito o revertir si se produce un error en el camino.,

esto se vería algo como esto:

Quiero terminar esta sección con una demostración rápida de cómo actualizar un registro existente en la base de datos. Vamos a actualizar el precio de la guía para escribir historias cortas a 10.99 (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 realizada 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 voy a demostrar cómo utilizar la interfaz sqlite3 para realizar consultas de selección simples.,

para realizar una consulta básica multirow de la tabla customers, pase una instrucción SELECT al método execute(...) del objeto cursor. Después de esto, puede iterar sobre los resultados de la consulta llamando al método fetchall() del mismo objeto cursor.

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

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

ver cómo la Fila individual de cada resultado es en forma de una tupla? Bueno, mientras que las tuplas son una estructura de datos pitónica muy útil para algunos casos de uso de programación, muchas personas las encuentran un poco dificultosas cuando se trata de la tarea de recuperación de datos. Resulta 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 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.

conclusión

en este artículo di una breve demostración de lo que creo que son las características y funcionalidades más importantes de la interfaz Python sqlite3 a la base de datos SQLite de un solo archivo ligera que viene pre-incluido con la mayoría de las instalaciones de Python., También traté de dar algunos consejos con respecto a 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 uno de los más propensos a los agujeros de seguridad a nivel empresarial y es necesario un mayor conocimiento antes de tal empresa.