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: car.py and cars_sql_scheme.py. 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).

car.py file listing

class Car():
    """Represents a 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 - 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
        self.vin = vin
        self.sold = sold

cars_sql_scheme.py file listing:

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)
	)"""

In the third file, sql_helper.py, 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 car.py and the variables create_table_cars and create_table_repairs from cars_sql_scheme.py, 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()
        self.c.execute(create_table_cars)
        self.c.execute(create_table_repairs)

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.

    	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 looks like this:

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

    	Arguments:
    	car  - car instance

    	Returns:
    	None
    	"""
        with self.conn:
            self.c.execute("DELETE FROM cars WHERE vin=:vin", {'vin': car.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.

        Arguments:
        vrn  - vehicle registration number

        Returns:
        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.

        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 a value of 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 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.

        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})
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()

Leave a Reply

Your email address will not be published. Required fields are marked *