Content description:
In the current post, I create a Car class that describes the user's vehicles. The class method allows you to easily create instances using a list.
Additionally, I create a file containing SQL code that creates tables in the database and CRUD methods.
In this post, I will describe a simple Python GUI application for recording car repair notes.
No ORM, just simple Python classes with a Tkinter GUI and a bit of SQL (SQLite).
You can download the project code from my Github repository
First, I create a car.py file containing a class describing vehicles.
class Car():
"""sample car
Arguments:
make: car make e.g. Honda
model: car model e.g. Civic
year: year of production
vrn: vehicle registration number
vin: VIN number
sold: is car sold? (default: false)
"""
def __init__(self, make, model, year, vrn, vin, sold=False):
self.make = make
self.model = model
self.year = year
self.vrn = vrn
self.vin = vin
self.sold = sold
@classmethod
def from_list(cls, list):
make, model, year, vrn, vin, sold = list
return cls(make, model, year, vrn, vin, sold)
In the second file cars_sql_scheme.py I describe the structure of the database (two tables containing data on vehicles and repairs).
create_table_cars = """CREATE TABLE IF NOT EXISTS cars(
make TEXT,
model TEXT,
year TEXT,
vrn TEXT,
vin TEXT,
sold INTEGER
)"""
create_table_repairs = """CREATE TABLE IF NOT EXISTS repairs(
date TEXT,
car INTEGER,
description TEXT,
FOREIGN KEY(car) REFERENCES cars(ROWID)
)"""
The third file, sql_helper.py, contains a Helper class with methods:
- add_car() - add a car to database
- del_car() - remove a car from database
- search_by_vrn() - search for a car by registration number
- show_all_cars() - return all cars
- set_sold() - set car as sold
- add_repair() - add repair note
- show_repairs() - display all repairs
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()
self.c.execute(create_table_cars)
self.c.execute(create_table_repairs)
...
The init() method creates a connection to the database, then a query cursor and the car and repair tables (if not already created).
The add_car() method of the Helper class takes vehicle make, model, year of production, registration number and VIN as arguments and returns a new instance of Car class. Adding a new car to the database is done using the context manager, so the transaction is auto-committed(no need to confirm).
def add_car(self, make, model, year, vrn, vin):
"""Adds new car to database
Arguments:
make - car make e.g. Honda
model - car model e.g. Civic
year - year of production
vrn - vehicle registration number
vin - VIN number
Returns:
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. The method deletes the selected car and its repair notes.
def del_car(self, car):
"""Deletes car from database
Arguments:
car - car instance
Returns:
None
"""
with self.conn:
self.c.execute("SELECT ROWID FROM cars WHERE vin=:vin",
{'vin': car.vin})
car_id = self.c.fetchone()
self.c.execute("DELETE FROM repairs WHERE car=?",
(car_id))
self.c.execute("DELETE FROM cars WHERE vin=:vin", {'vin': car.vin})
The search function does not require to commit transaction, so I don't use the context manager.
def search_by_vrn(self, vrn):
"""Search car by vehicle registration number.
Arguments:
vrn - vehicle registration number
Returns:
search result tuple
"""
self.c.execute("SELECT * FROM CARS WHERE vrn=:vrn", {'vrn': vrn})
return self.c.fetchone()
Similarly, the method that returns all saved cars.
def show_all_cars(self):
"""Search availale cars
Returns:
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 the value 1.
def set_sold(self, car):
"""Mark car as sold
Arguments:
car - Car instance
Returns:
None
"""
car.sold = True
with self.conn:
self.c.execute("UPDATE cars SET sold=True WHERE vin=:vin", {
'vin': car.vin})
The function below adds a repair note
def add_repair(self, car, date, description):
"""Adds repair note.
Arguments:
car - Car instance
date - repair date
description - repair description
Returns:
None
"""
self.c.execute("SELECT ROWID FROM cars WHERE vin=:vin",
{'vin': car.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})
The following function returns all repairs for a given car.
def show_repairs(self, car):
"""Shows car repairs notes.
Arguments:
car - Car instance
Returns:
search result - list of tuples
"""
self.c.execute("SELECT ROWID FROM cars WHERE vin=:vin",
{'vin': car.vin})
car_id = self.c.fetchone()
self.c.execute("SELECT * FROM repairs WHERE car=?",
(car_id))
return self.c.fetchall()