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:
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 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:
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:
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 iNone
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żą:
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:
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¶
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: | 2022-05-22 o 19:52 w Sphinx 1.5.3 |
---|---|
Autorzy: | Patrz plik “Autorzy” |