Tkinter GUI for CRUD app #2

In an earlier post, I created the main application window. Then I will complete the methods that handle events initiated by interface elements.

First, I will complete the show_cars () method, which is activated after loading widgets into the main window and each time when adding or removing a car from the database.

    def show_cars(self):
        all_cars = self.helper.show_all_cars()
        for i, car in enumerate(all_cars, start=1):
            car = list(car)
            car.insert(0, i)
            self.car_tview.insert('', 'end', values=car)

The show_all_cars () method of the Helper class runs first. It returns all cars in the database as a list of tuples. Then all items in the TreeView widget are removed to reload the car list after the changes.

To simplify handling of the del_car (), sell_car (), show_repairs () methods, I will extract a private method: __get_car_from_selection (), which returns an instance of the Car class based on the selected line, i.e.

    def __get_car_from_selection(self):
        selected_item = self.car_tview.focus()
        if selected_item == '':
            messagebox.showinfo('Information', 'No item selected')
            selection_dict = self.car_tview.item(selected_item)
            selection = selection_dict.get('values')
            i, *args = selection
            car = Car.from_list(args)
            return car

First, the above method checks if any line containing data about the car is selected. If so, the TreeView element’s dictionary is returned.

Data about the selected car is present as a value for the ‘values’ key. The first element that is the row ordinal is removed from the list.

The modified list is an argument of the from_list () method of the Car class.

Del_car () method code:

    def del_car(self):
        car = self.__get_car_from_selection()
        if car and messagebox.askyesno('Delete', 'Delete selected car?'):

The above method checks whether the variable car is not of the None type and whether the user has also confirmed the deletion of data about the car in the dialog box. If both of these conditions are met, the Helper class method is run, deleting the car from the database.

Then the car entries in the database are updated using the show_cars () method of the CarManager class.

    def sell_car(self):
        car = self.__get_car_from_selection()
        if car and car.sold:
            messagebox.showinfo('Information', 'Already marked as sold')
        elif car and messagebox.askyesno('Sell', 'Mark car as sold?'):

In the above method, it is checked whether the car has already been marked as sold. If not, a confirmation dialog is displayed.

If the user accepts it, the set_sold () method of the Helper class is called and then the show_cars () method of the CarManager object.

Show_repairs () method code:

    def show_repairs(self):
        car = self.__get_car_from_selection()
        if car:
            repairs = RepairsWindow(self.root, car)

In the show_repairs () method, when the variable car is not of type None, a new child window is created that is described by the RepairsWindow class.

The last method described in this post is the add_car () method, which is activated after pressing the add_car_button button.

def add_car(self):
        add_car = NewCarWindow(self.root, self)

The add_car () method creates a new child window that is described by the NewCarWindow class.

I will describe the codes of both classes in the next post.

The methods related to the search_entry input field still need to be added, i.e. on_entry_in (), on_entry_out () and on_entry_return (), i.e.

    def on_entry_in(self, event):

    def on_entry_out(self, event):
        self.search_variable.set('Search car by VRN')

Gaining focus on the search_entry search box clears the search box and changes the font color. The loss of focus causes the display of gray text ‘Search car by VRN’ in the search_entry field.

def on_entry_return(self, event):
    vrn = self.search_variable.get()
    if vrn == '':
        car = self.helper.search_by_vrn(vrn)
        if car:
            car = list(car)
            car.insert(0, 1)
            self.car_tview.insert('', 'end', values=car)

The on_entry_return method code above checks if the search field is empty after pressing Enter. If so, data on all available cars from the database are displayed.

If a phrase is entered in the search box, the search_by_vrn () method of the Helper class is run, followed by the updated car_tview widget to display the search results.

Tkinter GUI for CRUD app #1

In the >>previous post<<, I described a simple CRUD program that modifies a database (SQLite in this case).

For the program I created earlier, I will create GUI in the tkinter library. The interface will be developed gradually, making it easier to explain the methods used.

Program code can be found here.

I will slightly modify the files created in the previous post, i.e. for the class Car() I’ll add a class method that creates an instance when from a list. In addition, in the Helper () class, the del_car () method, apart from deleting the car from the database, also deletes all data about the entered repairs related to this car. The code of the new fragments looks like this:

class Car():
    def from_list(cls, list):
        make, model, year, vrn, vin, sold = list
        return cls(make, model, year, vrn, vin, sold)

class Helper():
    def del_car(self, car):
        with self.conn:
            self.c.execute("SELECT ROWID FROM cars WHERE vin=:vin",
            car_id = self.c.fetchone()
            self.c.execute("DELETE FROM repairs WHERE car=?",
            self.c.execute("DELETE FROM cars WHERE vin=:vin", {'vin':})

Here is my CarManager() class describing the graphical interface.

import tkinter as tk
from tkinter import messagebox, ttk
from sql_helper import Helper
from car import Car

class CarManager(tk.Frame):
    def __init__(self, root):
        self.root = root
        self.helper = Helper()
        root.title('Car manager')
        root.iconphoto(True, tk.PhotoImage(file='Resources/car_logo.png'))
        root.bind('<Control-x>', self.close_app)
        root.columnconfigure(1, weight=1)
        root.rowconfigure(2, weight=1)

if __name__ == '__main__':
    root = tk.Tk()
    cm = CarManager(root)

When creating an instance of the CarManager class, I create instance of the Helper class, set the name of the created window to ‘Car Manager’ and add the program icon from the Resources directory. In addition, I set a hotkey to end the program (Ctrl + x). Then I set the way the widgets are to be scaled when the window is resized.

The last two lines of the init () method run the methods responsible for creating all widgets in the main window and for displaying data from the database about the saved cars.

Here is my close_app () method:

    def close_app(self, event):
        result = tk.messagebox.askyesno('Exit', 'Close application?')
        if result:

The result is True when selected the close app button, the main application window is closed.

Code of my create_widgets () method responsible for displaying interface elements:

def create_widgets(self):
        # create menu
        menubar = tk.Menu(self.root)
        file_menu = tk.Menu(menubar, tearoff=0)
        file_menu.add_command(label='Exit', command=exit, accelerator="Ctrl+x")
        menubar.add_cascade(label="File", menu=file_menu)

        # create toolbar
        tbar_frame = tk.Frame(root, height=10)
        tbar_frame.grid(row=0, column=0)

        self.add_car_img = tk.PhotoImage(file='Resources/add_car.gif')
        self.remove_car_img = tk.PhotoImage(file='Resources/remove_car.gif')
        self.repairs_img = tk.PhotoImage(file='Resources/repairs.gif')
        self.sold_img = tk.PhotoImage(file='Resources/sold.gif')

        add_car_button = tk.Button(tbar_frame, image=self.add_car_img,
                                   command=self.add_car).grid(row=0, column=0, sticky='W')
        del_car_button = tk.Button(tbar_frame, image=self.remove_car_img,
                                   command=self.del_car).grid(row=0, column=0, sticky='W', padx=30)
        repairs_button = tk.Button(tbar_frame, image=self.repairs_img,
                                   command=self.show_repairs).grid(row=0, column=0, sticky='W', padx=60)
        sold_button = tk.Button(tbar_frame, image=self.sold_img,
                                command=self.sell_car).grid(row=0, column=0, sticky='W', padx=90)

        # create search entry
        self.search_variable = tk.StringVar()
        self.search_entry = tk.Entry(
            root, textvariable=self.search_variable)
        self.search_variable.set("Search car by VRN")
        self.search_entry.bind('<FocusIn>', self.on_entry_in)
        self.search_entry.bind('<FocusOut>', self.on_entry_out)
        self.search_entry.bind('<Return>', self.on_entry_return)
        self.search_entry.bind('<KP_Enter>', self.on_entry_return)
        self.search_entry.grid(row=0, column=1, sticky='E', ipadx=20)

        # create TreeView with Scrollbar
        col_headers = ('No', 'Make', 'Model', 'Year', 'VRN', 'VIN', 'Sold')
        self.car_tview = ttk.Treeview(self.root, columns=col_headers,
                                      show='headings', selectmode='browse')
        self.car_tview.columnconfigure(0, weight=1)
        self.car_tview.rowconfigure(0, weight=1)
        # set column headers
        for i, col in enumerate(col_headers):
            self.car_tview.heading(col, text=col)
            self.car_tview.column(col, anchor='center')
            if i == 0:
                self.car_tview.column(col, width=50, stretch='NO')
        self.car_tview.grid(row=2, column=0, columnspan=2, sticky='NSWE')

        scrollbar = tk.Scrollbar(self.root, command=self.car_tview.yview)
        scrollbar.grid(column=3, row=2, sticky='NS')

Successively, the program menu is created, then a toolbar with buttons for adding a car to the database, removing a car, adding data about car repairs and marking the sale of the car.

Then, a car search by the registration number field is created to which methods are attached to react to the events of obtaining / losing focus and confirming data by pressing the ENTER key on: numeric keypad (<KP_Enter>) or main keypad (<Return>).

The next few lines define a TreeView widget containing cars data and a scroll bar, if the data does not fit on the page.

After the changes, the main program window should look like this:

The initial window of the program. For now, no data …

Python and SQLite

Python includes the sqlite3 module in the standard library. To present the capabilities of the sqlite3 library, I’ll make a simple CRUD application for vehicle service and repair registration.

First, I’ll create two support files: and The first one will define the Car class – describing the auto, and the second one will define the database schema (or in fact, queries creating two tables in the database). file listing

class Car():
    """Represents a sample car.

    make - car make e.g. Honda
    model - car model e.g. Civic
    year - year of production
    vrn - vehicle registration number
    vin - VIN number
    sold - if car is still our property 


    def __init__(self, make, model, year, vrn, vin, sold=False):
        self.make = make
        self.model = model
        self.year = year
        self.vrn = vrn = vin
        self.sold = sold file listing:

create_table_cars = """CREATE TABLE IF NOT EXISTS cars(
	make TEXT,
	model TEXT,
	year TEXT,
	vrn TEXT,
	vin TEXT,

create_table_repairs = """CREATE TABLE IF NOT EXISTS repairs(
	date TEXT,
	description TEXT,

In the third file,, I define the Helper class, which will contain the methods:

  • add_car() – adding the car to the database
  • del_car() – removes the car from the database
  • search_by_vrn() – searches for a car by the registration number
  • show_all_cars() – returns all cars in the base
  • set_sold() – updates car data when the car has been sold
  • add_repair() – adds an annotation about the repair of the car
  • show_repairs() – displays all car repairs

The script imports the sqlite3 module, the Car class from and the variables create_table_cars and create_table_repairs from, i.e.

import sqlite3
from car import Car
from cars_sql_scheme import create_table_cars, create_table_repairs

class Helper():
    def __init__(self):
        self.conn = sqlite3.connect('cars.db')
        self.c = self.conn.cursor()

The init () method creates a connection to the database named cars.db, creates a query cursor, and creates the cars and repairs tables (if not already created).

The add_car () method takes the vehicle make, model, year of construction, registration number, and VIN as arguments. The function returns a new instance of the Car class. Adding a new car to the database is carried out using the context manager, so the transaction is automatically committed (no need to commit () after each database change), i.e.

def add_car(self, make, model, year, vrn, vin):
    	"""Adds new car to database.

    	make - car make e.g. Honda
    	model - car model e.g. Civic
    	year - year of production
    	vrn - vehicle registration number
    	vin - VIN number

    	new Car instance
        with self.conn:
            self.c.execute("INSERT INTO cars VALUES (:make, :model, :year, :vrn, :vin, :sold)", {
                'make': make, 'model': model, 'year': year, 'vrn': vrn, 'vin': vin, 'sold': False})
            return Car(make, model, year, vrn, vin)

Similarly, the del_car () method looks like this:

def del_car(self, car):
    	"""Deletes car from database.

    	car  - car instance

        with self.conn:
            self.c.execute("DELETE FROM cars WHERE vin=:vin", {'vin':})

The search function does not require to commit transaction, so I do not use the context manager, i.e.

def search_by_vrn(self, vrn):
        """Search car by vehicle registration number.

        vrn  - vehicle registration number

        search result tuple
        self.c.execute("SELECT * FROM CARS WHERE vrn=:vrn", {'vrn': vrn})
        return self.c.fetchone()

    def show_all_cars(self):
    	"""Search availale cars.

        search result  - list of tuples
        self.c.execute("SELECT * FROM CARS")
        return self.c.fetchall()

The set_sold () function sets the sold column in the database to True, represented by a value of 1.

def set_sold(self, car):
    	"""Mark car as sold.

        car  - Car instance

        car.sold = True
        with self.conn:
            self.c.execute("UPDATE cars SET sold=True WHERE 

The last two methods allow you to add a note about the car’s repair and display the repair list. ie.

def add_repair(self, car, date, description):
    	"""Adds repair note.

        car  - Car instance
        date  - repair date
        description  - repair description

        self.c.execute("SELECT ROWID FROM cars WHERE vin=:vin",
        car_id = self.c.fetchone()[0]
        with self.conn:
            self.c.execute("INSERT INTO repairs VALUES (:date, :car, :description)", {
                'date': date, 'car': car_id, 'description': description})
def show_repairs(self, car):
        """Shows car repairs notes.

        car  - Car instance

        search result  - list of tuples
        self.c.execute("SELECT ROWID FROM cars WHERE vin=:vin",
        car_id = self.c.fetchone()
        self.c.execute("SELECT * FROM repairs WHERE car=?",
        return self.c.fetchall()

Venv – how to use

The venv library present as a built-in module starting with python 3.3 allows you to create virtual environments.

The virtual environment allows the use of specific versions of libraries, regardless of those that are installed in the default system locations. This prevents errors when the new, updated version of the library does not work well with the rest of the project (the project requires an earlier version).

To create a virtual environment for the current project, type:

python -m venv project_name/env_name

A env_name directory is created in the project directory project_name that contains a virtual environment.

The virtual environment directory contains several subdirectories, including / bin directory containing a link to the python interpreter, as well as an activate file to start the environment. In addition, the env_name directory also has a /lib directory containing installed modules (site-packages).

To run the created environment named env_name, enter in the shell:

source project_name/env_name/bin/activate

You can close the environment with the deactivate command.

When we have a virtual environment running, the newly installed libraries are available only in the selected virtual environment. You can save the library required for the project in a text file with:

pip freeze > requirements.txt

If you have a requirements.txt file describing the project requirements for libraries and their versions, you can install them with:

pip install -r requirements.txt

Bash prompt, venv and ArchLabs

If you want to change the command prompt displayed by bash in such a way as to display information about the currently used virtual environment created by venv, you need to modify the PS1 variable in the .bashrc file.

In the ArchLabs distribution, the PS1 variable settings are also modified in the hidden .bash directory in the user’s directory and therefore instead of directly modifying the PS1 variable, the PROMPT_USERFMT variable should be modified, i.e.


Then, the created variable should be exported in the .bashrc file, i.e.


To reload bash settings, you need to reload the .bashrc file, i.e.

source .bashrc

Unit Testing in Python – using unittest module

In order to check the correctness of the written code, we can write verification tests using the unittest module.

Once written tests also help with subsequent changes to the code – if the changes did not cause errors.

We can also use the TDD (Test Driven Development) technique, in which writing tests precedes the creation of the actual program.

I’m going to show a class that tests the correctness of the methods present in the Employee class of the module. A simple Employee class will contain, in addition to the init () method, an alternative constructor (class method) and methods: set_bonus () – setting a bonus for an employee, get_full_salary () – returns salary (basis + bonus), get_email () – returns employee’s email address.

For this purpose, I’m writing a script containing a class that inherits from unittest.TestCase. Of course, the script has to import the unittest module and the tested module, i.e. the listing of will look like this:

import unittest
from employee import Employee

class EmployeeTestCase(unittest.TestCase):
    def setUp(self):
        self.john_doe = Employee('John', 5000)
        self.jane_doe = Employee.from_full_name('Jane', 'Doe', 6000)

    def test_get_email(self):
        self.assertEqual(self.john_doe.get_email(), '')
        self.assertEqual(self.jane_doe.get_email(), '')

    def test_get_full_salary(self):
        self.assertEqual(self.john_doe.get_full_salary(), 5000)
        self.assertEqual(self.jane_doe.get_full_salary(), 6000)
        self.assertEqual(self.john_doe.get_full_salary(), 5500)
        self.assertEqual(self.jane_doe.get_full_salary(), 6900)

To run the above test script correctly, it must be loaded as a module, i.e.

python -m unittest

or attach the call to unittest.main () to the test script, i.e.

if __name__ == '__main__':

Based on the written test script, we can write the file, i.e.

class Employee():
    bonus = 0

    def __init__(self, name, salary): = name
        self.salary = salary
    def get_email(self):

    def set_bonus(self, bonus):

    def get_full_salary(self):

    def from_full_name(cls, first_name, last_name, salary):
        name = '{}.{}'.format(first_name, last_name)
        return cls(name, salary)

The stub of the Employee class created in this way, after running the tests, generates 2 failures in the performed tests (despite the presence of several assertions in one test).

Writing the Employee class methods will result in the correct passing of 2 tests, i.e.

class Employee():
    bonus = 0

    def __init__(self, name, salary): = name
        self.salary = salary

    def get_email(self):
        return '{}'.format(

    def set_bonus(self, bonus):
        self.bonus = bonus

    def get_full_salary(self):
        return self.salary + self.salary * self.bonus / 100

    def from_full_name(cls, first_name, last_name, salary):
        name = '{}.{}'.format(first_name, last_name)
        return cls(name, salary)

In addition to checking the equality of assertEqual (x, y), we can check various types of assertions, e.g.

  • assertNotEqual(x, y) # checks if x != y
  • assertIsNone(x) # checks if x is None
  • assertTrue(x) # checks if bool (x) is True

The setUp () method from the unittest module is run each time before the next test is performed. Likewise, there is a tearDown () method that executes after each test completes.

There are also setUpClass () and tearDownClass () class methods, which are run before starting and after completing all tests, eg to initiate and then close the database connection.

Running the program from a script

To run another program in Python, we can use the system () function from the os module. The problem is when we want to read the result of the running program, i.e.

import os
result = os.system('df -h')

The above program will display the result of the df program in the console, but the value of the result variable is the error code of the os.system () function execution.

If we want the result variable to store the result returned by the program, then instead of the system () function we should use the popen () function or its newer equivalent – the Popen class from the subprocess module. ie.

# 1 first option - function popen()
import os
result = os.popen('df -h').read()

# 2 second option - Popen class
import subprocess
command = subprocess.Popen('df -h', shell=True, stdout=subprocess.PIPE)
result ='utf-8')

Parsing the CSV file

CSV files are text files in which each line represents one data record, and the individual data in the line is separated by a delimiter, usually a comma.

In the example below, we are parsing a refueling report from a gas station. The first line is the header and contains the data: Contractor’s data; Name; Surname; Correction number; WZ number; Date; Time; Counter; Station; Registration number; Card number; Product name; VAT percentage; Price at the station; Net price; Gross price; Discount value; Quantity; Net; VAT; Gross.

In this particular case, the delimiter is the semicolon character. The following lines will contain entries about the next refueling. We want to obtain from the source file data on the date of refueling, the registration number of the car and the number of liters of fuel taken.

import csv

with open('report.csv') as csv_file:
    csv_reader = csv.DictReader(csv_file, delimiter=';')
    total = 0
    for line in csv_reader:
        print('{}  {}  {} ltr'.format(
            line['Data'], line['Registration number'], line['Quantity']))

        total += float(line['Quantity'])
    print('Total: ', total, 'ltr')

    with open('new-report.csv', 'w') as new_csv_file:
        field_names = ['Date', 'Auto', 'Refueling']
        csv_writer = csv.DictWriter(
            new_csv_file, fieldnames=field_names, delimiter=';')
        for line in csv_reader:
            dict = {}
            dict['Date'] = line['Date']
            dict['Auto'] = line['Registration number']
            dict['Refueling] = line['Quantity']

We perform parsing using the csv module. Then, using the context manager, open the report.csv file for reading. We use the DictReader object for reading, thanks to which it will be possible to refer to the value by specifying the keys from the csv file header.
Then the total value is calculated – the total amount of fuel taken.

We save the obtained data on refueling in the new-report.csv file. In this case, we’re using a DictWriter object. To use the iterator again, set the file content pointer to the beginning of the file – (0). We replace the default headers with the new field_names contained in the list, so it is necessary to jump to the new iterator value by executing next (csv_reader). We save the new line in the file with the writerow () method of the csv.DictWriter object.

PySimpleGUI – faster GUI creation

PySimpleGUI is a wrapper that facilitates and speeds up the creation of Python window applications. There are 4 ports, based on the following libraries: tkinter, Qt, WxPython, Remi.

Changing the port eg. from PySimpleGUI (based on tkinter) to PySimpleGUIQt (using Qt) does not require any further code changes! More details can be found >>here<<.

A sample program that collects data from the user and displays the data in a second window:

import PySimpleGUIQt as sg

layout = [[sg.Text('Please enter some sample text')],
          [sg.Submit('Apply'), sg.Cancel('Cancel')]]

window = sg.Window('Data source window', layout)

event, values = window.Read()


if event == 'Apply':
    text_input = values[0]
    sg.Popup('Text entered:', text_input, title='Data display window')

The first line of the script imports the wrapper module (it must be installed in the system e.g. via pip, as well as the framework that is used by the port – in this case Qt).

The next line defines the layout, which is a list of lists. Each subsequent list defines the next line of the template – the Text element on the first line that writes ‘Please enter some sample text’ , the InputText field in the second, and the Apply and Cancel buttons on the third line.

Then we create a window and assign the previously created layout as the second argument. Pressing any button or closing the window will switch to the window status reading – a tuple consisting of: the event element (e.g. button name or None when the window closing button is pressed) and a dictionary containing keys describing the window input fields and the values ​​entered in these fields is returned.

When the key argument is not given for an input field when creating a layout, by default the dictionary keys are consecutive integer values. In the above example, the text entered, which is saved in the values ​​dictionary as the key value 0, is passed as an element of the string displayed in the popup window (Window displaying data).

Window applications in Python and PyQt5 – using UI files

In Python, we can use .ui files describing the appearance of the interface generated in the Qt Designer tool. To run a window application that uses the xml ui file containing the interface appearance and uses the Qt library, install the PyQt5 overlay, i.e.

pip install PyQt5

Then we can use the following code which loads the ui file and sets the title of the application window.

import sys
from PyQt5.QtWidgets import QApplication, QMainWindow
from PyQt5 import uic

class MyApp(QMainWindow):
    def __init__(self):
        QMainWindow.setWindowTitle(self, 'App Window Title')
        uic.loadUi('app.ui', self)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = MyApp()

The above code does nothing, except to display a window with selected buttons, labels, etc. In order for these elements to respond to e.g. a click, you must define signals and slots for the GUI element e.g. for a button named calculateButton we create a method which will be the slot corresponding to the clicked signal. A list of signals that a given element can respond to can be found in the Qt library documentation. We place the assignment of the method responding to the signal in the init () method, i.e.


and then define the method that will be run when the button is pressed (in this case, the pass statement):

def my_calculate_function(self):