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.

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

.. literalinclude:: sqlraw01.py
    :linenos:

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

Zanim będziemy mogli wykonywać podstawowe operacje na bazie danych określane skrótem :term:`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:

.. literalinclude:: sqlraw02.py
    :linenos:
    :lineno-start: 16
    :lines: 16-

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 wspomnianego interpretera sqlite3 (sqlite3.exe w Windows).

Do skryptu dopisujemy poniższy kod:

.. literalinclude:: sqlraw03.py
    :linenos:
    :lineno-start: 36
    :lines: 36-

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 <http://pl.wikipedia.org/wiki/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].

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ść).

Pobieranie danych (czyli :term:`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żą:

.. literalinclude:: sqlraw04.py
    :linenos:
    :lineno-start: 57
    :lines: 57-

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'].

Do skryptu dodajemy jeszcze kilka linii:

.. literalinclude:: sqlraw05.py
    :linenos:
    :lineno-start: 67
    :lines: 67-

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.

Dane z tabel w bazach MS Accessa lub LibreOffice Base'a możemy eksportować do formatu csv, czyli pliku tekstowego, w którym każda linia repreazentuje pojedynczy rekord, a wartości pól oddzielone są jakimś separatorem, najczęściej przecinkiem. Załóżmy więc, że mamy plik uczniowie.csv zawierający dane uczniów w formacie: Jan,Nowak,2.

Przed metodą .close() zamykającą połączenie z bazą dopiszmy następujący kod:

.. literalinclude:: sqlraw06.py
    :linenos:
    :lineno-start: 77
    :lines: 77-95

Na początku funkcji czytaj_dane() sprawdzamy, czy istnieje plik podany jako argumet. Wykorzystujemy metodę isfile() z modułu os, który należy wcześniej zaimportować. Następnie w konstrukci with otwieramy plik i wczytujemy jego treść do zmiennej zawartosc. Pętla for pobiera kolejne linie, które oczyszczamy ze znaków końca linii (.replace('\n','')) i dekodujemy jako zapisane w standardzie utf-8. Poszczególne wartości oddzielone przecinkiem wyodrębniamy (.split(',')) do tupli, którą dodajemy do zdefiniowanej wcześniej tablicy (dane.append()). Na koniec funkcja zwraca listę przekształconą na tuplę (a więc zagnieżdzone tuple), która po przypisaniu do zmiennej uczniowie użyta zostanie, dokładnie tak jak robiliśmy to już wczesniej, jako argument metody .executemany().

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

• :download:`sqlraw.zip <sqlraw.zip>`

Kolejne wersje tworzenego kodu znajdziesz w katalogu ~/python101/docs/bazy. Uruchamiamy je wydając polecenia:

~/python101$ cd docs/bazy
~/python101/docs/bazy$ python sqlraw0x.py

- gdzie x jest numerem kolejnej wersji kodu.