5.1. SQL

Jak wiadomo, do obsługi bazy danych wykorzystywany jest strukturalny język zapytań SQL. Jest on m.in. przedmiotem nauki na lekcjach informatyki na poziomie rozszerzonym w szkołach ponadgimnazjalnych. Używając Pythona można łatwo i efektywnie pokazać używanie SQL-a, zarówno z poziomu wiersza poleceń, jak również z poziomu aplikacji internetowych WWW. Na początku zajmiemy się skryptem konsolowym, co pozwala przećwiczyć “surowe” polecenia SQL-a.

5.1.1. Połączenie z bazą

W ulubionym edytorze tworzymy plik sqlraw.py i umieszczamy w nim poniższy kod:

Kod nr
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
#! /usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3

# utworzenie połączenia z bazą przechowywaną na dysku
# lub w pamięci (':memory:')
con = sqlite3.connect('test.db')

# dostęp do kolumn przez indeksy i przez nazwy
con.row_factory = sqlite3.Row

# utworzenie obiektu kursora
cur = con.cursor()

Przede wszystkim importujemy moduł sqlite3 do obsługi baz SQLite3. Następnie w zmiennej con tworzymy połączenie z bazą danych przechowywaną w pliku na dysku (test.db, nazwa pliku jest dowolona) lub w pamięci, jeśli podamy ':memory:'. Kolejna instrukcja ustawia właściwość row_factory na wartość sqlite3.Row, aby możliwy był dostęp do kolumn (pól tabel) nie tylko przez indeksy, ale również przez nazwy. Jest to bardzo przydatne podczas odczytu danych.

Aby móc wykonywać operacje na bazie, potrzebujemy obiektu tzw. kursora, tworzymy go poleceniem cur = con.cursor(). I tyle potrzeba, żeby rozpocząć pracę z bazą. Skrypt możemy uruchomić poleceniem podanym niżej, ale na razie nic się jeszcze nie stanie...

~$ python sqlraw.py

5.1.2. Model bazy

Zanim będziemy mogli wykonywać podstawowe operacje na bazie danych określane skrótem CRUDCreate (tworzenie), Read (odczyt), Update (aktualizacja), Delete (usuwanie) - musimy utworzyć tabele i relacje między nimi według zaprojektowanego schematu. Do naszego pliku dopisujemy więc następujący kod:

Kod nr
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# tworzenie tabel
cur.execute("DROP TABLE IF EXISTS klasa;")

cur.execute("""
    CREATE TABLE IF NOT EXISTS klasa (
        id INTEGER PRIMARY KEY ASC,
        nazwa varchar(250) NOT NULL,
        profil varchar(250) DEFAULT ''
    )""")

cur.executescript("""
    DROP TABLE IF EXISTS uczen;
    CREATE TABLE IF NOT EXISTS uczen (
        id INTEGER PRIMARY KEY ASC,
        imie varchar(250) NOT NULL,
        nazwisko varchar(250) NOT NULL,
        klasa_id INTEGER NOT NULL,
        FOREIGN KEY(klasa_id) REFERENCES klasa(id)
    )""")

Jak widać pojedyncze polecenia SQL-a wykonujemy za pomocą metody .execute() obiektu kursora. Warto zwrócić uwagę, że w zależności od długości i stopnia skomplikowania instrukcji SQL, możemy je zapisywać w różny sposób. Proste polecenia podajemy w cudzysłowach, bardziej rozbudowane lub kilka instrukcji razem otaczamy potrójnymi cudzysłowami. Ale uwaga: wiele instrukcji wykonujemy za pomocą metody .executescript().

Powyższe polecenia SQL-a tworzą dwie tabele. Tabela “klasa” przechowuje nazwę i profil klasy, natomiast tabela “uczen” zawiera pola przechowujące imię i nazwisko ucznia oraz identyfikator klasy (pole “klasa_id”, tzw. klucz obcy), do której należy uczeń. Między tabelami zachodzi relacja jeden-do-wielu, tzn. do jednej klasy może chodzić wielu uczniów.

Po wykonaniu wprowadzonego kodu w katalogu ze skryptem powinien pojawić się plik test.db, czyli nasza baza danych. Możemy sprawdzić jej zawartość przy użyciu interpretera interpretera sqlite3.

5.1.3. Wstawianie danych

Do skryptu dopisujemy poniższy kod:

Kod nr
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
# wstawiamy jeden rekord danych
cur.execute('INSERT INTO klasa VALUES(NULL, ?, ?);', ('1A', 'matematyczny'))
cur.execute('INSERT INTO klasa VALUES(NULL, ?, ?);', ('1B', 'humanistyczny'))

# wykonujemy zapytanie SQL, które pobierze id klasy "1A" z tabeli "klasa".
cur.execute('SELECT id FROM klasa WHERE nazwa = ?', ('1A',))
klasa_id = cur.fetchone()[0]

# tupla "uczniowie" zawiera tuple z danymi poszczególnych uczniów
uczniowie = (
    (None, 'Tomasz', 'Nowak', klasa_id),
    (None, 'Jan', 'Kos', klasa_id),
    (None, 'Piotr', 'Kowalski', klasa_id)
)

# wstawiamy wiele rekordów
cur.executemany('INSERT INTO uczen VALUES(?,?,?,?)', uczniowie)

# zatwierdzamy zmiany w bazie
con.commit()

Do wstawiania pojedynczych rekordów używamy odpowiednich poleceń SQL-a jako argumentów wspominanej metody .execute(), możemy też dodawać wiele rekordów na raz posługując się funkcją .executemany(). Zarówno w jednym, jak i drugim przypadku wartości pól nie należy umieszczać bezpośrednio w zapytaniu SQL ze względu na możliwe błędy lub ataki typu SQL injection (“wstrzyknięcia” kodu SQL). Zamiast tego używamy zastępników (ang. placeholder) w postaci znaków zapytania. Wartości przekazujemy w tupli lub tuplach jako drugi argument.

Warto zwrócić uwagę, na trudności wynikające z relacyjnej struktury bazy danych. Aby dopisać informacje o uczniach do tabeli “Uczeń”, musimy znać identyfikator (klucz podstawowy) klasy. Bezpośrednio po zapisaniu danych klasy, możemy go uzyskać dzięki funkcji .lastrowid(), która zwraca ostatni rowid (unikalny identyfikator rekordu), ale tylko po wykonaniu pojedynczego polecenia INSERT. W innych przypadkach trzeba wykonać kwerendę SQL z odpowiednim warunkiem WHERE, w którym również stosujemy zastępniki.

Metoda .fechone() kursora zwraca listę zawierającą pola wybranego rekordu. Jeżeli interesuje nas pierwszy, i w tym wypadku jedyny, element tej listy dopisujemy [0].

Informacja

  • Wartość NULL w poleceniach SQL-a i None w tupli z danymi uczniów odpowiadające kluczom głównym umieszczamy po to, aby baza danych utworzyła je automatycznie. Można by je pominąć, ale wtedy w poleceniu wstawiania danych musimy wymienić nazwy pól, np. INSERT INTO klasa (nazwa, profil) VALUES (?, ?), ('1C', 'biologiczny').
  • Jeżeli podajemy jedną wartość w tupli jako argument metody .execute(), musimy pamiętać o umieszczeniu dodatkowgo przecinka, np. ('1A',), ponieważ w ten sposób tworzymy w Pythonie 1-elementowe tuple. W przypadku wielu wartości przecinek nie jest wymagany.

Metoda .commit() zatwierdza, tzn. zapisuje w bazie danych, operacje danej transakcji, czyli grupy operacji, które albo powinny zostać wykonane razem, albo powinny zostać odrzucone ze względu na naruszenie zasad ACID (Atomicity, Consistency, Isolation, Durability – Atomowość, Spójność, Izolacja, Trwałość).

5.1.4. Pobieranie danych

Pobieranie danych (czyli kwerenda) wymaga polecenia SELECT języka SQL. Dopisujemy więc do naszego skryptu funkcję, która wyświetli listę uczniów oraz klas, do których należą:

Kod nr
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
# pobieranie danych z bazy
def czytajdane():
    """Funkcja pobiera i wyświetla dane z bazy."""
    cur.execute(
        """
        SELECT uczen.id,imie,nazwisko,nazwa FROM uczen,klasa
        WHERE uczen.klasa_id=klasa.id
        """)
    uczniowie = cur.fetchall()
    for uczen in uczniowie:
        print(uczen['id'], uczen['imie'], uczen['nazwisko'], uczen['nazwa'])
    print()


czytajdane()

Funkcja czytajdane() wykonuje zapytanie SQL pobierające wszystkie dane z dwóch powiązanych tabel: “uczen” i “klasa”. Wydobywamy id ucznia, imię i nazwisko, a także nazwę klasy na podstawie warunku w klauzuli WHERE. Wynik, czyli wszystkie pasujące rekordy zwrócone przez metodę .fetchall(), zapisujemy w zmiennej uczniowie w postaci tupli. Jej elementy odczytujemy w pętli for jako listę uczen. Dzięki ustawieniu właściwości .row_factory połączenia z bazą na sqlite3.Row odczytujemy poszczególne pola podając nazwy zamiast indeksów, np. uczen['imie'].

Informacja

Warto zwrócić uwagę na wykorzystanie w powyższym kodzie potrójnych cudzysłowów ("""..."""). Na początku funkcji umieszczono w nich opis jej działania, dalej wykorzystano do zapisania długiego zapytania SQL-a.

5.1.5. Modyfikacja i usuwanie danych

Do skryptu dodajemy jeszcze kilka linii:

Kod nr
74
75
76
77
78
79
80
81
82
83
# zmiana klasy ucznia o identyfikatorze 2
cur.execute('SELECT id FROM klasa WHERE nazwa = ?', ('1B',))
klasa_id = cur.fetchone()[0]
cur.execute('UPDATE uczen SET klasa_id=? WHERE id=?', (klasa_id, 2))

# usunięcie ucznia o identyfikatorze 3
cur.execute('DELETE FROM uczen WHERE id=?', (3,))

czytajdane()

Aby zmienić przypisanie ucznia do klasy, pobieramy identyfikor klasy za pomocą metody .execute() i polecenia SELECT SQL-a z odpowiednim warunkiem. Póżniej konstruujemy zapytanie UPDATE wykorzystując zastępniki i wartości przekazywane w tupli (zwróć uwagę na dodatkowy przecinek(!)) – w efekcie zmieniamy przypisanie ucznia do klasy.

Następnie usuwamy dane ucznia o identyfikatorze 3, używając polecenia SQL DELETE. Wywołanie funkcji czytajdane() wyświetla zawartość bazy po zmianach.

Na koniec zamykamy połącznie z bazą, wywołując metodę .close(), dzięki czemu zapisujemy dokonane zmiany i zwalniamy zarezerwowane przez skrypt zasoby.

5.1.6. Zadania

  • Przeczytaj opis przykładowej funkcji pobierającej dane z pliku tekstowego w formacie csv. W skrypcie sqlraw.py zaimportuj tę funkcję i wykorzystaj do pobrania i wstawienia danych do bazy.
  • Postaraj się przedstawioną aplikację wyposażyć w konsolowy interfejs, który umożliwi operacje odczytu, zapisu, modyfikowania i usuwania rekordów. Dane powinny być pobierane z klawiatury od użytkownika.
  • Zobacz, jak zintegrować obsługę bazy danych przy użyciu modułu sqlite3 Pythona z aplikacją internetową na przykładzie scenariusza “ToDo”.

5.1.7. Źródła


Licencja Creative Commons Materiały Python 101 udostępniane przez Centrum Edukacji Obywatelskiej na licencji Creative Commons Uznanie autorstwa-Na tych samych warunkach 4.0 Międzynarodowa.

Utworzony:2017-09-08 o 18:17 w Sphinx 1.5.3
Autorzy:Patrz plik “Autorzy”