Praca z bazą z wykorzystaniem skryptu

W bazie SQLite3 pracownicy.db znajdują się dane dotyczące osób zatrudnionych w pewnej firmie. Plik pobierz i zapisz w wybranym katalogu.

Język SQL może być używany nie tylko w aplikacjach desktopowychaplikacja desktopowaaplikacjach desktopowych wyposażonych w edytor SQL, jak np. program SQLiteStudio. Równie często z zapytań języka SQL korzysta się w kodzie źródłowym aplikacji internetowychaplikacja internetowaaplikacji internetowych. Dlatego tym razem do operacji na bazie wykorzystamy skrypt i moduł sqlite3 języka Python.

Na początku poznamy schemat bazy, a następnie odczytamy z niej różne informacje za pomocą zapytań języka SQL i złączeń.

W wybranym edytorze kodu tworzymy szkielet skryptu z podaną niżej zawartością. Skrypt zapisujemy pod nazwą kwerendy.py w katalogu zawierającym pobraną wcześniej bazę pracownicy.db.

import sqlite3

def wykonaj_zapytanie(zapytanie):
    kursor.execute(zapytanie)
    dane = kursor.fetchall()
    for rekord in dane:
        print(*rekord)
    print("Liczba zwróconych rekordów:", len(dane))
    return dane


baza = 'pracownicy.db'
pol = sqlite3.connect(baza)  # połączenie z bazą
kursor = pol.cursor()  # utworzenie obiektu kursora

kwerenda = ""

wykonaj_zapytanie(kwerenda)

Po zaimportowaniu modułu sqlite3 nawiązujemy połączenie z bazą przy użyciu metody connect(), która wymaga podania nazwy pliku z bazą danych. Następnie tworzymy obiekt tzw. kursorakursorkursora, za pomocą którego będziemy wykonywać operacje na bazie.

Do wykonania zapytań wykorzystamy funkcję pomocniczą wykonaj_zapytanie(), która będzie otrzymywała polecenie SQL jako argument.

Zapytania SQL uruchamiamy za pomocą metody execute() kursora. Jako argument podajemy ciąg tekstowy zawierający instrukcję SQL. Po wykonaniu zapytania SQL obiekt kursora przechowuje wynikowy zbiór rekordów. Możemy go odczytać w pętli for przy użyciu wybranej metody kursora:

• fetchall() – zwraca wszystkie wynikowe rekordy w formie listy, co wykorzystujemy w funkcji,

• fetchone() – zwraca kolejny pojedynczy rekord z wyniku zapytania.

Zwrócone rekordy (tuple) odczytujemy z listy i wypisujemy za pomocą instrukcji print(rekord). Symbol * (gwiazdka) powoduje rozpakowanie tupli. Następnie wypisujemy, ile rekordów zostało zwróconych. Na koniec zwracamy listę zwróconych rekordów (tupli).

Schemat bazy

Schemat bazyschemat bazySchemat bazy to informacje o tabelach znajdujących się w bazie, o ich polach i relacjach, a także o sposobie ich połączenia. Do uzyskania tych informacji wykorzystamy fakt, że baza SQLite3 w specjalnej tabeli o nazwie sqlite_master zapisuje polecenia SQL użyte do tworzenia tabel. W zmiennej skryptu kwerenda umieszczamy polecenie:

SELECT sql FROM sqlite_master;

W powyższym kodzie instrukcja SELECT wybiera pole sql z tabeli sqlite_master, które zawiera klauzulę CREATE wykorzystaną do stworzenia danej tabeli. Dzięki wypisaniu tych klauzul możemy przeanalizować schemat bazy.

Skrypt kwerendy.py wykonujemy z poziomu edytora kodu (jeżeli dysponuje taką funkcjonalnością) lub w wierszu poleceń przy użyciu interpretera języka Python 3.

R1WfUKjL9QhbM

Kod w języku SQL CREATE TABLE pracownicy( id INTEGER PRIMARY KEY AUTOINCREMENT, imie TEXT, nazwisko TEXT, data DATE, ulica TEXT, kod TEXT, miasto TEXT ); CREATE TABLE sqlite_sequence(name, seq); CREATE TABLE kontakty( id INTEGER PRIMARY KEY AUTOINCREMENT, id_pracownika INTEGER NOT NULL, telefon TEXT, email TEXT FOREIGN KEY (id_pracownika) REFERENCES pracownicy(id), ); CREATE TABLE stanowiska( id INTEGER PRIMARY KEY AUTOINCREMENT, stanowisko TEXT, ); CREATE TABLE place( id_pracownika INTEGER REFERENCES pracownicy(id), id_stanowiska INTEGER REFERENCES stanowiska(id), data DATE, placa FLOAT, FOREIGN KEY (id_pracownika) REFERENCES pracownicy(id), FOREIGN KEY (id_stanowiska) REFERENCES stanowiska(id) );

CREATE TABLE sqlite_sequence(name, sec)

W badanej bazie informacje osobowe o pracownikach znajdują się w tabeli pracownicy. Każdy pracownik jest jednoznacznie identyfikowany poprzez liczbę zapisaną w kluczu głównym, czyli polu id. Tabela stanowiska zawiera listę stanowisk, każdemu przypisano unikalny identyfikator w kluczu głównym o nazwie id. Tabela place pozwala połączyć pracownika ze stanowiskiem. Klucze obce, to znaczy: pola id_pracownika oraz id_stanowiska, przechowują odpowiednie identyfikatory pracowników i zajmowanych przez nich stanowisk. Tabela kontakty zawiera dodatkowe informacje na temat pracownika wskazywanego przez jego identyfikator zapisany w polu id_pracownika.

Poniżej graficzne przedstawienie tabel, pól i łączących je relacji:

RxIN6PmO676YJ

Ilustracja przedstwia 3 tabelki połączone ze sobą. Tabela place posiada pola id_pracownika, id_stanowiska, data_zatr, placa, podłączone do niej strzałkami są tabele pracownicy i stanowiska. Tabela stanowiska posiada pola id oraz stanowisko. Tabela pracownicy posiada pola id, imie, nazwisko, data, ulica, kod oraz miasto.

Złączenia

Po przeanalizowaniu schematu bazy zauważymy, że wykonując zapytania, będziemy pobierać informacje z różnych tabel jednocześnie. Do tego celu użyjemy złączeńzłączeniazłączeń.

1. Jakie stanowiska zajmują pracownicy?

Potrzebne informacje: imię, nazwisko i stanowisko, pobierzemy przy użyciu zapytania, które na początku przyjmie następującą postać:

SELECT imie, nazwisko, id_stanowiska
FROM pracownicy
INNER JOIN place;

   kwerenda = "SELECT sql FROM sqlite_master;"
   kwerenda = "SELECT imie, nazwisko, id_stanowiska FROM pracownicy INNER JOIN place;"

    kwerenda = """
        SELECT imie, nazwisko, id_stanowiska
        FROM pracownicy
        INNER JOIN place;
    """

Wykonaj skrypt – wynik kwerendy prawdopodobnie cię zaskoczy.

R1G37uyFQN2vS

Zdjęcie ekranu pokazuje wynik działania programu. Wielokrotnie powtórzony jest napis ('Jan', 'Kowalski', 7) ('Anna', 'Nowak', 7) ('Ewa', 'Pasterniak', 7) ('Iwona', 'Miśkiewicz', 7) Każdy taki zestaw powtórzony jest dla zmieniających się liczb w ciągu 7, 3, 2, 4, 5, 4, 6 oraz 1.

Do pobierania danych z dwóch lub więcej tabel używamy mechanizmu łączenia (ang. join), polegającego na umieszczeniu w zapytaniu klauzuli JOIN lub INNER JOIN, po której następuje nazwa związanej relacją tabeli. Podczas przetwarzania takiego zapytania baza tworzy jedną wielką tabelę, która zawiera wszystkie kombinacje rekordów obu tabel. Nazywamy to iloczynem kartezjańskimiloczyn kartezjańskiiloczynem kartezjańskim.

SELECT imie, nazwisko, id_stanowiska
FROM pracownicy
CROSS JOIN place;

Klauzula INNER JOIN użyta tak jak do tej pory w skrypcie jest raczej bezużyteczna. Nie chcemy wszystkich możliwych kombinacji rekordów z dwóch tabel, tylko dopasowania, tzn. chcemy znaleźć dla każdego rekordu z tabeli pracownicy odpowiadający mu rekord w tabeli place.

Musimy zatem uzupełnić zapytanie o warunek złączenia (ang. join condition), czyli wskazać pola tworzące relacje. Służy do tego klauzula ON. Kod polecenia SQL przyjmie postać:

SELECT imie, nazwisko, id_stanowiska
FROM pracownicy
INNER JOIN place
ON pracownicy.id=place.id_pracownika;

Po wykonaniu polecenia zobaczymy imiona, nazwiska i identyfikatory stanowisk tylko tych pracowników, którym w tabeli place przypisano jakieś stanowisko.

Dane z dwóch i więcej tabel

Uzyskane wyniki z poprzedniej kwerendy nie są zadowalające, zamiast stanowisk mamy ich identyfikatory. Nazwy znajdują się w tabeli stanowiska. Jest to tzw. tabela słownikowa, którą tworzy się między innymi po to, żeby nie powtarzać wartości w rekordach innej tabeli, w tym wypadku place.

Pobieranie danych z trzech tabel jest równie proste jak ta sama operacja na dwóch tabelach. Po prostu dodajemy kolejną klauzulę INNER JOIN i warunek łączenia.

SELECT imie, nazwisko, stanowisko
FROM pracownicy
INNER JOIN place
ON pracownicy.id=place.id_pracownika
INNER JOIN stanowiska
ON stanowiska.id=place.id_stanowiska;

Alternatywna składnia

Zapytania wykorzystujące złączenia można zapisywać również w inny sposób, kod ostatniego polecenia SQL w wersji alternatywnej prezentuje się następująco:

SELECT imie, nazwisko, stanowisko
FROM pracownicy, place, stanowiska
WHERE pracownicy.id=place.id_pracownika
AND stanowiska.id=place.id_stanowiska;

Nie używamy klauzuli INNER JOIN, ponieważ zastępuje ją przecinek oddzielający nazwy tablic w klauzuli FROM. Zamiast klauzuli ON korzystamy z klauzuli WHERE, w której łączymy wymagane warunki złączenia operatorem AND.

Aliasy

Czasami wygodne (a niekiedy niezbędne) jest posłużenie się aliasami tabel, czyli ich skrótowymi nazwami. W rozbudowanych zapytaniach wpływa to na ich czytelność. Do tworzenia aliasów służy klauzula AS. Ostatnia kwerenda mogłaby więc wyglądać tak:

SELECT imie, nazwisko, stanowisko
FROM pracownicy AS pr, place AS pl, stanowiska AS st
WHERE pr.id=pl.id_pracownika
AND st.id=pl.id_stanowiska;

2. Którzy pracownicy mieszkający w tych samych miastach mogą tworzyć dwuosobowe zespoły?

Odpowiedź na to pytanie wymaga złączenia tabeli pracownicy z nią samą, ponieważ w niej zawarte są informacje o miejscu zamieszkania pracowników.

Zacznijmy od wybrania wszystkich możliwych par bez względu na miasto:

SELECT p1.imie, p1.nazwisko, p1.miasto, p2.imie, p2.nazwisko, p2.miasto
FROM pracownicy AS p1, pracownicy AS p2
WHERE p1.id != p2.id;

Kwerenda zwróci 132 rekordy. Dzięki aliasom źródłem zapytania są dwie tabele p1 i p2 o tej samej zawartości. Dlatego stosujemy warunek p1.id != p2.id, aby z zestawienia wyeliminować dopasowania tych samych osób, czyli rekordy typu:

Jan Kowalski Sandomierz Jan Kowalski Sandomierz

Teraz dodamy jeszcze jeden warunek, który usunie z wyników pary pracowników z innych miast:

SELECT p1.imie, p1.nazwisko, p1.miasto, p2.imie, p2.nazwisko, p2.miasto
FROM pracownicy AS p1, pracownicy AS p2
WHERE p1.id != p2.id
AND p1.miasto = p2.miasto;

Kwerenda zwróci 26 rekordów. Trzeba zauważyć, że nie jest to liczba unikalnych par, ponieważ dane pracowników występują w obu tabelach. W wynikach znajdziemy więc powtarzające się pary, np.:

Jan Kowalski Sandomierz Edward Kowalski Sandomierz
Edward Kowalski Sandomierz Jan Kowalski Sandomierz

W celu uzyskania listy niepowtarzalnych par, przeformułujemy zapytanie w taki sposób, aby złączenie następowało według pola miasto:

SELECT p1.imie, p1.nazwisko, p1.miasto, p2.imie, p2.nazwisko, p2.miasto
FROM pracownicy p1
JOIN pracownicy p2 ON p1.miasto = p2.miasto
WHERE p1.id < p2.id;

Tym razem do złączenia tabel użyliśmy klauzuli JOIN ON. Kluczowy jest jednak końcowy warunek w klauzuli WHERE, który eliminuje powtarzające się pary.

Zapytanie zwróci 13 rekordów:

Jan     Kowalski    Sandomierz  Edward  Kowalski   Sandomierz
Jan     Kowalski    Sandomierz  Ewa     Pasternak  Sandomierz
Jan     Kowalski    Sandomierz  Iwona   Miśkiewicz Sandomierz
Jan     Kowalski    Sandomierz  Piotr   Zamojski   Sandomierz
Ewa     Pasternak   Sandomierz  Edward  Kowalski   Sandomierz
Ewa     Pasternak   Sandomierz  Iwona   Miśkiewicz Sandomierz
Ewa     Pasternak   Sandomierz  Piotr   Zamojski   Sandomierz
Iwona   Miśkiewicz  Sandomierz  Edward  Kowalski   Sandomierz
Iwona   Miśkiewicz  Sandomierz  Piotr   Zamojski   Sandomierz
Jan     Zamojski    Tarnobrzeg  Andrzej Zamojski   Tarnobrzeg
Jan     Zamojski    Tarnobrzeg  Janusz  Sarna      Tarnobrzeg
Andrzej Zamojski    Tarnobrzeg  Janusz  Sarna      Tarnobrzeg
Piotr   Zamojski    Sandomierz  Edward  Kowalski   Sandomierz

Dane niepołączone

Do tej pory wyszukiwaliśmy rekordy, które pasują do siebie w dwóch lub większej liczbie tabel. Czasem będziemy chcieli wiedzieć, które rekordy nie mają dopasowania w innej tabeli.

3. Czy mamy dane kontaktowe każdego pracownika?

Tabela kontakty połączona jest z tabelą pracownicy poprzez pola z identyfikatorem pracownika. Możemy użyć następującego kodu SQL:

SELECT imie, nazwisko, id_pracownika
FROM pracownicy
LEFT JOIN kontakty
ON pracownicy.id=kontakty.id_pracownika;

Działanie tej kwerendy polega na wybraniu wszystkich rekordów z tabeli pracownicy (po lewej stronie), następnie dołączeniu do nich powiązanych danych z tabeli kontakty (po prawej stronie), przy czym w przypadku braku powiązanych wierszy w kolumnie z prawej tabeli umieszczane są wartości NULL.

R1VGUHMtBF6hl

Zdjęcie ekranu pokazuje wynik działania programu. Wyświetlono wielokrotnie napis ('Jan', 'Kowalski', 1) Dla różnych imion i nazwisk czy też liczb. Po stopniowym wzrastaniu od 1 do 8 liczby zmieniają się na None.

Ciekawostka

Standard SQL definiuje jeszcze łączenie RIGHT JOIN, które działa podobnie jak omówione wyżej. Różnica polega na tym, że wartości NULL znajdą się w polach tabeli z lewej strony, której nazwa występuje po klauzuli FROM, dla rekordów niepowiązanych z tabeli po stronie prawej. Ostatnie zapytanie wyglądałoby wtedy następująco:

Linia 1. SELECT imie przecinek nazwisko przecinek id podkreślnik pracownika. Linia 2. FROM kontakty. Linia 3. RIGHT JOIN pracownicy. Linia 4. ON pracownicy kropka id znak równości kontakty kropka id podkreślnik pracownika średnik.

SELECT imie, nazwisko, id_pracownika
FROM kontakty
RIGHT JOIN pracownicy
ON pracownicy.id=kontakty.id_pracownika;

Baza SQLite3 nie obsługuje złączeń RIGHT JOIN, ponieważ w praktyce łatwo je zastąpić złączeniami LEFT JOIN. Powyższe zapytanie da się natomiast wykonać w aplikacji SQLiteStudio (od wersji 3.4.3).

RTKhNkxmzrcTr

Ilustracja przedstawia wynik zapytania wykorzystującego złączenie prawostronne w programie SQLiteStudio. Poniżej Zapytania jest Widok siatki. Tam imiona, nazwiska, id_pracownika.

Gdybyśmy chcieli otrzymać imiona i nazwiska tylko tych pracowników, którzy nie podali danych kontaktowych, wystarczy, że do kwerendy wykorzystującej domyślne złączenie lewostronne dodamy warunek w klauzuli WHERE:

SELECT imie, nazwisko, id_pracownika
FROM pracownicy
LEFT JOIN kontakty
ON pracownicy.id=kontakty.id_pracownika
WHERE id_pracownika IS NULL;

Słownik