Kategoria: SQLite

Python zawiera moduł sqlite3 w bibliotece standardowej. Aby przedstawić możliwości biblioteki sqlite3 przedstawię prostą aplikację CRUD, do obsługi pojazdów i rejestracji napraw.

Na początek utworzę dwa pliki pomocnicze: car.py i cars_sql_scheme.py. Pierwszy z nich będzie definiował klasę Car – opisującą auto, a drugi będzie definiował schemat bazy danych (a w zasadzie zapytania tworzące dwie tabele w bazie danych).

plik: car.py

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

plik cars_sql_scheme.py

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

W trzecim pliku sql_helper.py definiuję klasę Helper, która będzie zawierać metody:

• add_car() – dodanie auta do bazy danych
• del_car() – usuwa auto z bazy
• search_by_vrn() – wyszukuje auto po numerze rejestracyjnym
• show_all_cars() – zwraca wszystkie auta w bazie
• set_sold() – aktualizuje dane auta, gdy auto zostało sprzedane
• add_repair() – dodaje adnotację o naprawie auta
• show_repairs() – wyświetla wszystkie naprawy auta

Skrypt importuje moduł sqlite3, klasę Car z pliku car.py oraz zmienne create_table_cars i create_table_repairs z pliku cars_sql_scheme.py tzn.

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)

Metoda __init__() stwarza połączenie z bazą o nazwie cars.db, tworzy kursor do obsługi zapytań oraz tworzy tabele cars i repairs (jeśli nie są już utworzone).

Metoda add_car() jako argumenty pobiera markę pojazdu, model, rok produkcji, nr rejestracyjny i numer VIN. Funkcja zwraca nową instancję klasy Car. Dodanie nowego samochodu do bazy jest przeprowadzone przy użyciu menadżera kontekstu, dzięki czemu automatycznie zatwierdzana jest transakcja (nie trzeba wpisywać commit() po każdej zmianie bazy) tzn.

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)

Analogicznie metoda del_car() wygląda następująco:

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

Wyszukiwanie nie wymaga zatwierdzania transakcji, więc nie stosuję menadżera kontekstu tzn.

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

Funkcja set_sold() ustawia kolumnę sold w bazie danych na wartość True, reprezentowaną przez wartość równą 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})

Ostatnie dwie metody umożliwiają dodanie notatki o naprawie auta oraz wyświetlenie listy napraw. tzn.

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