Tkinter and SQLite database with Python

• The document is about creating a visual interface with Tkinter and accessing an SQLITE database with Python

Introduction

• It is often necessary to access an SQLite database from an application with a visual interface

Problem

• To develop a visual application with the Tkinter library that can implement algorithms for loading articles, consulting by code, and complete listing

Solution

• Work with the Data Base Management System (DBMS) using BD Browser from SQLite
• The creation of the database named “db_bodega.db” will take place

Table Creation

• Creating a table called ‘artículos’
• Table fields include codigo, descripcion, and precio

Visual Interface

• Visual interface includes loading items, a search function and finally a complete list

Project Organization

• To organize create two modules named ‘formularioarticulos.py’ and ‘articulos.py’
• The primary step is to create the directory “proyecto4” where the two modules reside

Module formularioarticulos.py

• The module ‘formularioarticulos.py’ handles the data presentation and interacts with the second module to access the SQLite database
• The main steps of the module are: importing the correct tkinter components, creation of the “Articulos” class and the presentation of the loading screen, search and lists

Module articulos.py

• The module ‘articulos.py’ handles the logic to access the SQLite database
• The main steps of the module are: importing the correct sqlite components, creation of the “Articulos” class and defining the methods to open, search, add and list the information held in the SQLITE database.

Main

• Ensure the main module located in the file ‘formularioarticulos.py’ is executed

Code Analysis

• import tkinter as tk, from tkinter import ttk, from tkinter import messagebox as mb, from tkinter import scrolledtext as st are the modules needed to implement the visual interface.
• import articulos; the fundamental import is the ‘articulos.py’ module, where the ‘Articulos’ class, the database connection, is implemented.
• The visual class is called 'FormularioArticulos'. Within the init constructor, an object of the 'Articulos' class is instantiated

Class Notebook

• The calls in init create each of the pages of the 'Notebook' class object.
• Create each notebook option for the GUI with the Tkinter framework
• self.ventana1=tk.Tk()
• self.ventana1.title("Mantenimiento de artículos")
• self.cuaderno1 = ttk.Notebook(self.ventana1)
• self.carga_articulos()
• self.consulta_por_codigo()
• self.listado_completo()
• self.cuaderno1.grid(column=0, row=0, padx=10, pady=10)
• self.ventana1.mainloop()
• When the "Carga de artículos"(add articles) tab is selected followed by the "Confirmar" button, the first action is the creation of a tuple, composed of the two data entered in the "Entry" control:
• def agregar(self):
• datos=(self.descripcioncarga.get(), self.preciocarga.get())
• After the collection of data, the method alta of the "articulo1" object is called, passing a tuple with the information to be added.
• self.articulo1.alta(datos)
• mb.showinfo("Información", "Los datos fueron cargados")
• self.descripcioncarga.set("")
• self.preciocarga.set('')
• It is appropriate at this moment to analyze the "alta" method of the 'Articulos' class, located in a different module.
• def alta(self, datos):
• cone=self.abrir()
• cursor=cone.cursor()
• sql="insert into articulos(descripcion, precio) values (?,?)"
• cursor.execute(sql, datos)
• cone.commit()
• cone.close()
• In the method 'alta', the SQLite connection is opened, a cursor is created and the execute method called to execute the SQL 'insert' command and the information to be added. The connection needs to commit for the data to be added.
• The connection is openned with: -def abrir(self): -conexion=sqlite3.connect(“bd_bodega.db") -return conexión
• Specifying the entire address where the "bd_bodega.db" is located is of utmost importance in any Python program where the other folder is located.

Consulting the code

• When the "Consultar" button is pressed, the following method is executed:
• def consultar(self):
• datos=(self.codigo.get(), )
• respuesta=self.articulo1.consulta(datos)
• if len(respuesta)>0:
• self.descripcion.set(respuesta[0][0])
• self.precio.set(respuesta[0][1])
• else:
• self.descripcion.set('')
• self.precio.set('')
• mb.showinfo("Información", "No existe un artículo con dicho código")
• A tuple with only one element consisting of the value is created; it is mandatory that a comma (,) is employed so Python can interpret the information as a tuple.
• datos=(self.codigo.get(), )
• The method ‘consulta’ that is related to the ‘Articulos’ class that can be found in another module is executed. The method is expected to list any code that is either empty or with the tuple inside.
• The method ‘consulta’ that is related to the class ‘Articulos’ calls ‘fetchall’ from the respective cursor
• def consulta(self, datos):
• try:
• cone=self.abrir()
• cursor=cone.cursor()
• sql="select descripcion, precio from articulos where codigo=?"
• cursor.execute(sql, datos)
• return cursor.fetchall()
• finally:
• cone.close()

Complete listing

• To show all the rows in the table "articulos", an object of the class "scrolledtext" needs to be in place.
• def listar(self):
• respuesta=self.articulo1.recuperar_todos()
• self.scrolledtext1.delete("1.0", tk.END)
• for fila in respuesta:
• self.scrolledtext1.insert(tk.END, "código:"+str(fila[0])+
• "\ndescripción:"+fila[1]+
• "\nprecio:"+str(fila[2])+"\n\n")
• The method "recuperar_todos" that is related to the "Articulos" class is called with the information retrieved from the data table.
• def recuperar_todos(self):
• try:
• cone=self.abrir()
• cursor=cone.cursor()
• sql="select codigo, descripcion, precio from articulos"
• cursor.execute(sql)
• return cursor.fetchall()
• finally:
• cone.close()

Proposed Problem

• Add two tabs to the article management program to allow deleting an article by entering its code and another option to query and modify the description and price of an article.
• The visual interfaces to implement are:
  • A maintenance view that allows for deleting articles. Requires an input for article code and a "Borrar" button.
• A maintenance section for modification to the existing articles where the code, description and price can be altered. Requires a “consultar” and “modificar” button