Schemat bazy danych

Na początku pobieramy pliki w formacie CSV z danymi i zapisujemy w katalogu uczniowie_baza.

Pliki otwieramy w edytorze tekstu, który ma możliwość pokazywania białych znaków – w ten sposób łatwiej nam będzie ustalić, jakim separatorem oddzielone są wartości pól w wierszach.

Rl4sYNqVXV2TY

Ilustracja przedstawia zawartość pliku: uczniowie.csv. Separatorem jest znak tabulacji, czyli pozioma strzałka skierowana w prawo, dane składają się z identyfikatora ucznia, imienia, nazwiska oraz klasy.

Na zrzucie widać, że w pliku uczniowie.csv separatorem jest znak tabulacji, a dane składają się z identyfikatora ucznia, imienia i nazwiska oraz klasy.

Instrukcja SQL, która utworzy nam odpowiednią tabelę, może wyglądać następująco:

DROP TABLE IF EXISTS uczniowie;
CREATE TABLE uczniowie (
    id CHAR(8) PRIMARY KEY,
    imie VARCHAR(20) NOT NULL CHECK(imie <> ''),
    nazwisko VARCHAR(30) NOT NULL CHECK(nazwisko <> ''),
    klasa CHAR(5) DEFAULT ''
);

Na uwagę zasługuję fakt, że identyfikator ucznia, czyli klucz główny tabeli, nie jest liczbą całkowitą, ale 8‑znakowym ciągiem znaków. Ograniczenia nałożone na pola imie i nazwisko nie dopuszczają braku wartości oraz pustych ciągów znaków. Dla pola zdefiniowano wartość domyślną – pusty ciąg znaków.

RXwckbTQk7Pgj

ilustracja przedstawia zawartość pliku: przedmioty.csv. Wiersze zawierają dwie informacje: identyfikator oraz nazwę przedmiotu. Za identyfikatorem będącym liczbą porządkową postawiono przecinek.

Separatorem w pliku przedmioty.csv jest przecinek, wiersze zawierają tylko dwie informacje: identyfikator oraz nazwę przedmiotu. Dla tych danych utworzymy prostą tabelę słownikowątabela słownikowatabelę słownikową, za pomocą polecenia:

DROP TABLE IF EXISTS przedmioty;
CREATE TABLE przedmioty (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nazwa VARCHAR(60) NOT NULL CHECK(nazwa <> '')
);

RAYJNWc7Ghxzu

Ilustracja przedstawia zawartość pliku: oceny.csv. Separatorem danych jest znak tabulacji, czyli pozioma strzałka skierowana w prawo. Plik zawiera następujące informacje: identyfikator oceny, datę otrzymania, identyfikator ucznia, identyfikator przedmiotu oraz ocenę.

W pliku oceny.csv separatorem danych ponownie jest znak tabulacji, plik zawiera następujące informacje: identyfikator oceny, datę otrzymania, identyfikator ucznia, identyfikator przedmiotu oraz ocenę. Z tego wynika, że odpowiednia klauzula SQL powinna utworzyć nie tylko pola do przechowywania danych, ale również relacje łączące tabelę ocen z tabelami uczniowie i przedmioty.

DROP TABLE IF EXISTS oceny;
CREATE TABLE oceny (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    data DATE NOT NULL,
    id_ucznia CHAR(8) NOT NULL,
    id_przedm INTEGER,
    ocena DECIMAL(3,2) NOT NULL CHECK(ocena < 7),
    FOREIGN KEY (id_ucznia) REFERENCES uczniowie(id)
    ON DELETE CASCADE,
    FOREIGN KEY (id_przedm) REFERENCES przedmioty(id)
    ON DELETE SET NULL
);

Definiując pola, w których przechowywane będą wartości z innych tabel, to znaczy id_ucznia oraz id_przedm, pamiętać musimy, aby miały takie same typy danych, jak odpowiadające im pola w tabelach źródłowych.

Do tworzenia relacji używamy klauzul FOREIGN KEY...REFERENCES... umieszczonych na końcu definicji pól. W nawiasach okrągłych podajemy nazwę klucza obcego, a następnie nazwę tabeli źródłowej i klucza głównego w nawiasach okrągłych.

Na koniec wszystkie podane klauzule zapisujemy w pliku uczniowie.sql, w katalogu o nazwie uczniowie_baza.

Tworzenie bazy i tabel w języku Python

Język Python ułatwia obsługę baz danych dzięki standardowym modułom, takim jak np. sqlite3 czy mysql. Zacznijmy od kodu, który umożliwi nawiązanie połączenia z bazą oraz wykonywanie na niej operacji. W pliku uczniowie.py, który umieszczamy w katalogu uczniowie_baza, wpisujemy początkowy kod:

import sqlite3, os


def main(args):
    baza = 'uczniowie.db'
    con = sqlite3.connect(baza)  # połączenie z bazą
    cur = con.cursor()  # utworzenie obiektu kursora

    utworz_tabele(con, 'uczniowie.sql')  # tworzenie tabel
    zbadaj_baze(cur)  # sprawdzenie schematu bazy

    con.commit()  # zatwierdzenie zmian w bazie
    con.close()  # zamknięcie połączenia z bazą
    return 0


if __name__ == '__main__':
    import sys
    sys.exit(main(sys.argv))

Baza danych SQLite3 zawarta jest w jednym pliku o nazwie uczniowie.db. Metoda connect() łączy się z bazą i zwraca obiekt połączenia. Ma on następujące metody:

• cursor() – tworzy obiekt kursorakursorkursora, który pozwala na wykonywanie zapytań i odczytywanie ich wyników,

• commit() – zatwierdza zmiany w bazie,

• close() – zamyka połączenie z bazą.

W funkcji głównej umieściliśmy wywołanie funkcji utworz_tabele() oraz zbadaj_baze(), których kod (przedstawiony w Przykładzie 5) umieszczamy przed funkcją główną.

def zbadaj_baze(cur):
    cur.execute("SELECT * FROM sqlite_master;")
    for t in cur.fetchall():
        print(t[4])


def utworz_tabele(cur, plik_sql):
    if os.path.isfile(plik_sql):
        with open(plik_sql, newline='', encoding='utf-8') as sql:
            cur.executescript(sql.read())
    else:
        print("Brak pliku SQL na dysku!")

W funkcji utworz_tabele() sprawdzamy, czy podany plik zawierający instrukcje SQL istnieje na dysku i jeżeli tak, otwieramy go przy użyciu instrukcji with open() as, dzięki czemu zostanie on automatycznie zamknięty. Treść pliku odczytujemy za pomocą metody read(), która zwraca ciąg znaków, w tym wypadku polecenia SQL. Ostatecznie przekazujemy je do wykonania metodzie executescript().

Zadaniem funkcji zbadaj_baze() jest odczytanie wewnętrznej tabeli bazy SQLite3 o nazwie sqlite_master, która przechowuje schemat bazy. Jeżeli po wykonaniu skryptu zobaczymy klauzule SQL, które posłużyły do utworzenia tabel, będzie to znaczyło, że baza utworzona została poprawnie.

Dodawanie danych w języku Python

Do wprowadzenia danych wykorzystamy podane na wstępie pliki w formacie CSVformat CSVw formacie CSV oraz drugi skrypt języka Python. W katalogu z poprzednimi plikami zapisujemy plik o nazwie uczniowie_dane.py, którego początkowa zawartość jest identyczna jak w poprzednim skrypcie, poza dodatkowym importem oraz wywoływaną funkcją.

import sqlite3
import os
import csv


def dodaj_dane(cur, tabela, sep):
    plik_csv = tabela + '.csv'
    if not os.path.isfile(plik_csv):
        print(f"Brak pliku {plik_csv} na dysku!")
        return False

    dane = []  # lista rekordów
    with open(plik_csv, newline='', encoding='utf-8') as plik::
        for wiersz in csv.reader(plik, delimiter=sep):
            dane.append(wiersz)
    for rekord in dane:
        print(rekord)


def main(args):
    baza = 'uczniowie.db'
    con = sqlite3.connect(baza)  # połączenie z bazą
    cur = con.cursor()  # utworzenie obiektu kursora

    dodaj_dane(cur, 'uczniowie', '\t')  # dodawanie danych

    con.commit()  # zatwierdzenie zmian w bazie
    con.close()  # zamknięcie połączenia z bazą
    return 0


if __name__ == '__main__':
    import sys
    sys.exit(main(sys.argv))

Funkcja dodaj_dane() otrzymuje jako argumenty kursor, nazwę tabeli i separator danych. Na początku funkcji tworzymy nazwę pliku z danymi, dodając do nazwy tabeli rozszerzenie .csv. Jeżeli pliku nie ma na dysku, wypisujemy komunikat o jego braku, w przeciwnym razie otwieramy plik, używając wspominanej instrukcji with open() as.

Do obsługi plików CSV Python dysponuje dedykowanym modułem csv, który zaimportowaliśmy na początku pliku. Funkcja reader() rozbija na części każdy przeczytany wiersz pliku, używając podanego separatora. Części zwraca jako listę wartości dla danego rekordu. Kolejne rekordy zapisujemy w liście dane. Na końcu w pętli drukujemy zapisane rekordy.

Ćwiczenie 2

Uruchom skrypt, zwróć uwagę na wypisane listy wartości.

Pokaż rozwiązanie

Zrzuty pokazują wyniki działania skryptu uruchomionego w terminalu systemu Linux i w wierszu poleceń systemu Windows.

R1819iybNQRM4

Ilustracja przedstawia wynik działania skryptu: uczniowie.dane.py uruchomionego w terminalu systemu Linux. W górnej części okna są zakładki: Plik, Edycja, Widok, Wyszukiwanie, Terminal, Pomoc. W wierszach są dane: identyfikator, imię, nazwisko, klasa. Pomiędzy kolejnymi danymi wstawiono przecinki. Każdy wiersz zaczyna się i kończy nawiasem kwadratowym. Wiersze są na szarym tle.

RCBQKj3B4KDoa

Ilustracja przedstawia wynik działania skryptu: uczniowie.dane.py uruchomionego w wierszu poleceń systemu Windows. W górnej części okna jest pole o nazwie: Wiersz polecenia. W wierszach są dane: identyfikator, imię, nazwisko, klasa. Pomiędzy kolejnymi danymi wstawiono przecinki. Każdy wiersz zaczyna się i kończy nawiasem kwadratowym. Wiersze są na czarnym tle.

Obiekt kursora oferuje kilka metod pozwalających dodawać dane do tabel. Zacznijmy od najprostszej wersji:

cur.execute("INSERT INTO uczniowie VALUES ('123/2011', 'Wojciech', 'Banasik', 'IV E')")

Metoda execute() pozwala wykonać jedno zapytanie SQL na raz. W przedstawionej wersji wartości pól zostały podane bezpośrednio jako ciągi znaków. W praktyce ze względów bezpieczeństwa częściej wykorzystuje się wersję zawierającą tak zwane zastępniki (ang. placeholders ), czyli umowne znaki, które zostają zastąpione przez wartości podane w tupli lub liście. Prześledźmy przykłady.

cur.execute("INSERT INTO uczniowie VALUES (?, ?, ?, ?)", ('123/2011', 'Wojciech', 'Banasik', 'IV E'))

for rekord in dane:
    cur.execute("INSERT INTO uczniowie VALUES (?, ?, ?, ?)", rekord)

W naszym przypadku, kiedy chcemy dodać do tabel wiele rekordów na raz, skorzystamy z metody executemany(), która wymaga wspomnianych zastępników w zapytaniu SQL oraz listy (lub tupli) zawierającej wartości rekordów:

dane = [
    ['123/2011', 'Wojciech', 'Banasik', 'IV E'],
    ['124/2011', 'Monika', 'Baranowska', 'IV E'],
]
cur.executemany("INSERT INTO uczniowie VALUES (?, ?, ?, ?)", dane)

    zastepniki = ','.join(['?'] * len(dane[0]))
    zapytanie = 'INSERT INTO ' + tabela + ' VALUES(' + zastepniki + ')'
    cur.executemany(zapytanie, dane)
    return cur.rowcount

def dodaj_dane(cur, tabela, sep):
    plik_csv = tabela + '.csv'
    if not os.path.isfile(plik_csv):
        print(f"Brak pliku {plik_csv} na dysku!")
        return False
    dane = []  # lista na listy rekordów
    with open(plik_csv, newline='', encoding='utf-8') as plik:
        for wiersz in csv.reader(plik, delimiter=sep):
            # wiersz = [x.strip() for x in wiersz]
            dane.append(wiersz)
    # for rekord in dane:
    #     print(dane)

    zastepniki = ','.join(['?'] * len(dane[0]))
    zapytanie = 'INSERT INTO ' + tabela + ' VALUES(' + zastepniki + ')'
    cur.executemany(zapytanie, dane)
    return cur.rowcount

Instrukcja zastepniki = ','.join(['?'] * len(dane[0])) tworzy ciąg znakowy zastępników. Wyrażenie ['?'] * len(dane[0]) zwraca listę zawierającą tyle znaków zapytania, ile wartości ma pierwszy dodawany rekord. Metoda join() zwraca elementy listy połączone przecinkiem, np. '?,?,?,?', jeżeli dodawany rekord zawiera 4 pola (wartości).

Ostatnia instrukcja return cur.rowcount zwraca liczbę zmodyfikowanych rekordów, co możemy wykorzystać jako potwierdzenie, że operacja się udała.

    cur.execute('PRAGMA FOREIGN_KEYS = ON')
    print(dodaj_dane(cur, 'uczniowie', '\t'))  # dodawanie danych
    print(dodaj_dane(cur, 'przedmioty', ','))  # dodawanie danych
    print(dodaj_dane(cur, 'oceny', '\t'))  # dodawanie danych

def main(args):
    baza = 'uczniowie.db'	
    con = sqlite3.connect(baza)  # połączenie z bazą
    cur = con.cursor()  # utworzenie obiektu kursora

    cur.execute('PRAGMA FOREIGN_KEYS = ON')
    print(dodaj_dane(cur, 'uczniowie', '\t'))  # dodawanie danych
    print(dodaj_dane(cur, 'przedmioty', ','))  # dodawanie danych
    print(dodaj_dane(cur, 'oceny', '\t'))  # dodawanie danych

    con.commit()  # zatwierdzenie zmian w bazie
    con.close()  # zamknięcie połączenia z bazą
    return 0

W przedstawionym kodzie warto zauważyć, że aby poprawnie dodać dane do tabel, musimy pamiętać o podawaniu odpowiedniego separatora danych oraz o kolejności dodawania danych – przy założeniu, że baza sprawdza zdefiniowane ograniczenia.

W bazach SQLite3 ograniczenia FOREIGN KEY są domyślnie wyłączone, włączamy je więc, wykonując zapytanie PRAGMA FOREIGN_KEYS = ON. Tabela oceny zawiera klucze obce, czyli pola id_ucznia i id_przedm, w których wartości muszą odpowiadać wartościom z tabel i pól powiązanych relacjami, a więc dane powinny zostać dodane najpierw do tabel uczniowie i przedmioty.

762
27
13195

Słownik