5.1. SQL

Do obsługi bazy danych wykorzystywany jest strukturalny i deklaratywny język zapytań SQL. Jest on m.in. przedmiotem nauki na lekcjach informatyki w szkołach ponadpodstawowych na poziomie rozszerzonym. Korzystając z Pythona można łatwo i efektywnie pokazać używanie SQL-a, zarówno w skryptach, jak również w aplikacjach desktopowych i internetowych. W poniższym materiale pokażemy, jak wykorzystywać język SQL w skryptach wykonywanych w wierszu poleceń.

Informacja

Do kodowania i uruchamiania skryptu możesz użyć dowolnych narzędzi, np. ulubionego edytora kodu i terminala. Sugerujemy jednak wykorzystanie środowiska typu PyCharm lub innego, ponieważ ułatwiają przygotowania i pracę nad projektami w języku Python.

5.1.1. Połączenie z bazą

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

Kod nr 
1import sqlite3
2
3# utworzenie połączenia z bazą przechowywaną w pamięci RAM
4con = sqlite3.connect('baza.db')
5# dostęp do pól rekordów przez indeksy i przez nazwy pól
6con.row_factory = sqlite3.Row
7# utworzenie obiektu kursora
8cur = con.cursor()
9

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

Do wykonywania operacji na bazie potrzebujemy obiektu tzw. kursora. Tworzymy go poleceniem cur = con.cursor(). Skrypt możemy uruchomić poleceniem podanym niżej, co spowoduje utworzenie pustej bazy danych w podanym pliku (lub pamięci RAM).

~$ python sql_raw.py

5.1.2. Model bazy

Zanim będziemy mogli wykonywać podstawowe operacje na bazie danych określane skrótem CRUD – Create (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 
10# tworzenie tabel
11cur.execute("DROP TABLE IF EXISTS klasa")
12cur.execute("DROP TABLE IF EXISTS uczen")
13cur.executescript("""
14    CREATE TABLE IF NOT EXISTS klasa (
15        id INTEGER PRIMARY KEY,
16        nazwa varchar(250) NOT NULL,
17        profil varchar(250) DEFAULT ''
18    );
19    CREATE TABLE IF NOT EXISTS uczen (
20        id INTEGER PRIMARY KEY,
21        imie varchar(250) NOT NULL,
22        nazwisko varchar(250) NOT NULL,
23        klasa_id INTEGER NOT NULL,
24        FOREIGN KEY(klasa_id) REFERENCES klasa(id)
25    )""")
26

Proste polecenia SQL-a umieszczamy w cudzysłowach podwójnych i wykonujemy za pomocą metody .execute() obiektu kursora. Bardziej skomplikowane możemy zapisywać w cudzysłowach potrójnych. Jeżeli chcemy wykonać wiele poleceń na raz, używmy metody .executescript(). Metoda ta akceptuje również polecenia SQL odczytywane z pliku tekstowego zapisanego np. na dysku.

Polecenia SQL-a umieszczone w naszym skrypcie 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”), czyli 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 skryptu w katalogu ze skryptem powinien pojawić się plik bazy danych baza.db, zawierający puste tabele klasa i uczen. Możemy to sprawdzić np. przy użyciu interpretera interpretera sqlite3 lub dowolnego innego programu odczytującego bazy SQLite3.

5.1.3. Dodawanie danych (INSERT)

Do skryptu dopisujemy poniższy kod:

Kod nr 
27# dodajemy dwie klasy
28cur.execute('INSERT INTO klasa VALUES(NULL,?,?)', ('1A', 'matematyczny'))
29klasa1_id = cur.lastrowid
30cur.execute('INSERT INTO klasa VALUES(NULL,?,?)', ('1B', 'humanistyczny'))
31klasa2_id = cur.lastrowid
32
33# lista uczniów, których dane zapisane są w krotkach
34uczniowie = [
35    ('Tomasz', 'Nowak', klasa1_id),
36    ('Jan', 'Kos', klasa2_id),
37    ('Piotr', 'Kowalski', klasa2_id)
38]
39# dodajemy dane wielu uczniów
40cur.executemany('INSERT INTO uczen VALUES(NULL,?,?,?)', uczniowie)
41con.commit()
42

Do dodania jednego rekordu używamy polecenia INSERT SQL-a jako argumentu wspominanej metody .execute(). Jeżeli chcemy dodać wiele rekordów na raz, posługujemy się metodą .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 liście tupli jako drugi argument omawianych metod.

Warto zwrócić uwagę, na trudności wynikające z relacyjnej struktury bazy danych. Aby dopisać informacje o uczniach do tabeli uczen, 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 uzyskanie identyfikatora wymaga wykonania kwerendy wybierającej SQL z odpowiednim warunkiem, np.: SELECT id FORM klasa WHERE nazwa = ? AND profil = ? (zob. poniżej).

Informacja

Wartość NULL (czyli wartość pusta) w poleceniach SQL-a odpowiada kluczom głównym, które baza SQLite utworzy automatycznie. Można by je pominąć, ale wtedy w poleceniu musimy wymienić nazwy pól, np. INSERT INTO klasa (nazwa, profil) VALUES (?, ?), ('1C', 'biologiczny').

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

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 
43def wypisz_listę_uczniow():
44    """ Odczytujemy i wypisujemy dane uczniów, w tym klasę """
45    cur.execute("SELECT count(*) FROM uczen")
46    if (cur.fetchone()[0]):
47        print('Uczniowie:')
48        cur.execute(
49            """
50            SELECT uczen.id, imie, nazwisko, nazwa FROM uczen
51            INNER JOIN klasa
52            WHERE uczen.klasa_id=klasa.id
53            """)
54        uczniowie = cur.fetchall()
55        for uczen in uczniowie:
56            print(uczen['id'], uczen['imie'], uczen['nazwisko'], uczen['nazwa'])
57        print()
58
59wypisz_listę_uczniow()
60

Funkcja wypisz_liste_uczniow() na początku wykonuje zapytanie zliczające przy użyciu funkcji count() liczbę rekordów, czyli uczniów, zapisanych w tabeli uczen. Następnie metoda .fetchone() kursora zwraca listę zawierającą pola pierwszego (i jedynego w tym przypadku) wybranego rekordu. Dlatego dopisujemy [0].

Jeżeli w bazie są zapisane dane jakichś uczniów, wykonujemy zapytanie SQL pobierające wszystkie dane z dwóch powiązanych tabel: uczen i klasa. Odczytujemy pola id ucznia, imie i nazwisko, a także nazwa klasy na podstawie warunku w klauzuli WHERE. Wynik, czyli wszystkie pasujące rekordy zwrócone przez metodę .fetchall() w postaci krotki, zapisujemy w zmiennej uczniowie. Jej elementy, tzn. listy pól, odczytujemy w pętli for w zmiennej 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 można umieścić w nich opis jej działania, dalej wykorzystujemy je do zapisania rozbudowanego zapytania SQL.

5.1.5. Modyfikacja danych (UPDATE)

Do skryptu dodajemy kod, który przepisze ucznia do innej klasy:

Kod nr 
61# przepisanie wybranego ucznia do innej klasy
62cur.execute('SELECT id FROM uczen WHERE nazwisko=?', ('Nowak',))
63uczen_id = cur.fetchone()[0]
64cur.execute('SELECT id FROM klasa WHERE nazwa=?', ('1B',))
65klasa_id = cur.fetchone()[0]
66cur.execute('UPDATE uczen SET klasa_id=? WHERE id=?', (klasa_id, uczen_id))
67wypisz_listę_uczniow()
68

Na początku pobieramy identyfikator ucznia za pomocą polecenia SQL SELECT id FROM uczen WHERE nazwisko=? wykonywanego przez metodę .execute(). Podobnie odczytujemy identyfikator nowej klasy.

Informacja

Jeżeli polecenie SQL wymaga jednej wartości i podajemy ją w krotce jako argument metody execute(), musimy pamiętać o umieszczeniu dodatkowego przecinka, np. ('Nowak',), ponieważ w ten sposób tworzymy w Pythonie 1-elementowe krotkę. W przypadku wielu wartości przecinek nie jest wymagany.

Następnie konstruujemy zapytanie UPDATE uczen SET klasa_id=? WHERE id=? wykorzystując zastępniki, dla których wartości podajemy w krotce.

5.1.6. Usuwanie danych (DELETE)

Do skryptu dodamy jeszcze kod, który usunie wskazany rekord z tabeli uczen:

Kod nr 
69# usunięcie ucznia o podanym imieniu i nazwisku
70cur.execute('DELETE FROM uczen WHERE imie=? and nazwisko=?', ('Jan', 'Kos'))
71wypisz_listę_uczniow()
72
73con.close()

Do usuwania rekordów używamy polecenie SQL DELETE, które wymaga wskazania rekordów do usunięcia w klauzuli WHERE. W naszym przypadku podajemy w krotce imię i nazwisko ucznia, który ma być usunięty z bazy.

Informacja

Powyższy przykład jest bardzo uproszczony, ponieważ w codziennych zastosowaniach wskazywanie ucznia do usunięcia tylko po imieniu i nazwisku mogłoby spowodować usunięcie danych wielu uczniów, którzy mają takie same imiona i nazwiska jak podane.

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

5.1.7. Zadania

  • Przeczytaj opis przykładowej funkcji pobierającej dane z pliku tekstowego w formacie CSV. W skrypcie sql_raw.py zaimportuj tę funkcję i wykorzystaj do pobrania i wstawienia danych do bazy.

  • Dodaj do skryptu 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.

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:

2026-04-19 o 17:41 w Sphinx 7.3.7

Autorzy:

Robert Bednarz