Lab 02 - modyfikacja i augmentacja, bazy danych, REST API

Bazy danych SQL

Wiele systemów informatycznych przechowuje informacje w formie baz danych - m.in. portale internetowe, systemy magazynowe, systemy logowania danych (np. automatyki budynkowej) itp.

Najpopularniejszą dziś grupę stanowią bazy danych wykorzystujące język zapytań SQL (ang. Structured Query Language) - dostępne są różne implementacje takie jak MySQL, Oracle, PostgreSQL. Każda z nich stanowi oddzielną, niezależną implementację i ma własny format zapisu oraz specyficzne rozwiązania, ale łączy je wspomniany wcześniej, w dużej mierze kompatybilny język zapytań.

We wspomnianych wcześniej rozwiązaniach, gdzie do bazy musi mieć dostęp wielu klientów jednocześnie, w dostępie pośredniczy zazwyczaj serwer, z którym klienty komunikują się poprzez protokoły sieciowe. W ramach zajęć dla uproszczenia wykorzystamy lekką implementację SQLite i będziemy operować bezpośrednio na pliku zawierającym całą bazę, jednak te same polecenia łatwo przeportować na pełnoprawny system z dostępem równoległym.

W poniższych przykładach zostaną zaprezentowane podstawowe, najczęściej wykorzystywane zapytania SQL. Po bardziej rozbudowane przykłady i opisy komend warto sięgnąć do tutoriala o SQL, np. https://www.sqlitetutorial.net

Przeglądarki SQLite

Często aby poznać strukturę czy zweryfikować zawartość bazy warto użyć przeglądarki wyposażonej w GUI. Dostępne są różne programy, m.in. darmowe:

• DB Browser for SQLite (Windows/macOS/Linux): https://sqlitebrowser.org/dl/
• Datum - Lite (Windows/macOS)

SQLite w Python - sqlite3

Do komunikacji z bazą danych wykorzystamy pakiet sqlite3. Pełną dokumentację wraz z przykładami można znaleźć pod poniższym linkiem: https://docs.python.org/3/library/sqlite3.html

Podczas kursu skupimy się na odczycie danych z bazy, ale biblioteka pozwala też oczywiście na zapis.

Pobierz przykładową bazę danych ze sklepu sprzedającego albumy muzyczne: https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources

Pobierz z sekcji Downloads plik Chinook_Sqlite.sqlite. Otwórz pobrany plik w wybranej przeglądarce SQLite i pobieżnie przejrzyj strukturę bazy danych, zawarte w niej tabele i informacje.

Stwórz skrypt Pythona i zaimportuj bibliotekę sqlite3. Następnie wykonaj poniższy kod - przykładową operację odczytu wszystkich danych z tabeli Album

conn = sqlite3.connect("Chinook_Sqlite.sqlite")  # połączenie do bazy danych - pliku
df = pd.read_sql_query("SELECT * FROM Album", conn)
conn.close()

SELECT, WHERE, ORDER BY

Powyższy przykład uruchamia najczęściej wykorzystywaną w SQL komendę SELECT, służącą do pobierania danych i odczytuje wszystkie kolumny (*) z tabeli Album. W tym przypadku read_sql_query zwraca DataFrame reprezentujący tabelę.

Ponieważ zmiana układu tabeli w bazie danych spowodowałaby, że dane zostaną zwrócone w innym układzie. W związku z tym w kodzie innym niż testowy nigdy nie powinno pojawić się tego typu zapytanie. Zamiast tego możemy podać jawnie nazwy kolumn:

SELECT AlbumID, ArtistID, Title FROM Album

Komendę SELECT można rozbudować o klauzulę WHERE, która pozwala odfiltrować dane po wybranym kryterium, zgodnie z wzorcem SELECT column_list FROM table WHERE search_condition;, np:

WHERE column = 100;
WHERE column IN (1,2,3) AND other_column = 50;
WHERE column LIKE 'An%';
WHERE column BETWEEN 10 AND 20;

Inną klauzulą, jaką można dodać do komendy SELECT jest ORDER BY powodujące sortowanie po określonej kolumnie/kolumnach.

ORDER BY column;

Opcjonalnie można podać kierunek sortowania (domyślnie rosnąco - ASC):

ORDER BY column ASC;
ORDER BY column DESC;

Klauzule można łączyć w złożone zapytania, np:

SELECT ArtistId, Name FROM Artist WHERE ArtistID BETWEEN 20 and 40 ORDER BY Name;

🔥 Zadanie 1 🔥

Pobierz z tabeli Invoice listę transakcji (zawierającą InvoiceId, CustomerId, BillingCity oraz Total z kraju USA, sortując wpisy po nazwie miasta, w kolejności odwrotnej do alfabetycznej.

Wyświetl wpisy w konsoli w postaci:

invoice: 14, customer: 17, city: Redmond, total: 1.98
invoice: 13, customer: 16, city: Mountain View, total: 0.99
invoice: 5, customer: 23, city: Boston, total: 13.86

JOIN

Ponieważ dane w bazach SQL przechowywane są w tylko formacie dwuwymiarowych tabel, konieczna jest możliwość tworzenia relacji pomiędzy poszczególnymi tabelami oraz ekstrakcji danych zawartych w kilku tabelach. Przykładowo, w omawianej bazie danych tabela Invoice nie zawiera informacji o pozycjach na paragonie, ale możemy je odnaleźć w tabeli InvoiceLine, wiążąc pozycje z obu tabeli za pomocą InvoiceId, które jest unikalne dla każdego paragonu. Analogicznie, w tabeli z albumami nie ma zawartej nazwy wykonawcy (która nie musi być unikalna), znajdują się tam jedynie indeksy ArtistId korespondujące z wierszami w tabeli Artist.

Do połączenia informacji z różnych tabel wykorzystamy komendę JOIN. Komenda może pracować w różnych trybach, dwa które będziemy wykorzystywać to INNER JOIN oraz LEFT JOIN.

INNER JOIN pobiera część wspólną tabel, łącząc wpisy po wybranym kryterium:

Table_A:

Table_B:

SELECT foos, bars FROM Table_A INNER JOIN Table_B ON Table_A.id = Table_B.id

Wynik:

Przykładowo, aby uzyskać nazwy wykonawców przyporządkowane do tytułów albumów:

SELECT Track.Name, Album.Title FROM Track INNER JOIN Album ON Track.AlbumId = Album.AlbumId

Zwrócona tabela będzie zawierała tytuły piosenek pobrane z tabeli Track, tytuły albumów z tabeli Album, a wpisy zostaną dopasowane na podstawie kolumny AlbumId - kryterium Track.AlbumId = Album.AlbumId. Zwrócone zostaną tylko te wpisy, które istnieją w obu tabelach.

Po bardziej rozbudowane przykłady zajrzyj do https://www.sqlitetutorial.net/sqlite-inner-join/

LEFT JOIN zwróci wszystkie wpisy znajdujące się w pierwszym argumencie. Brakujące wartości zostaną wypełnione wartościami NULL:

Table_A:

Table_B:

SELECT foos, bars FROM Table_A LEFT JOIN Table_B ON Table_A.id = Table_B.id

Wynik:

🔥 Zadanie 2 🔥

Stwórz zapytanie SQL do bazy, dzięki któremu uzyskasz listę albumów wraz z ich wykonawcą.

Przetestuj różne formy JOIN (INNER oraz LEFT), dla wariantu LEFT sprawdź wynik w sytuacjach kiedy pierwszym argumentem jest tablica z albumami oraz tablica z wykonawcami. Zastanów się z czego wynikają różnice.

REST API, usługi RESTful

REST to koncepcja architektury oprogramowania usług internetowych. Głównym założeniem jest to, że usługi takie są bezstanowe, tzn. każdy pakiet transmisji stanowi samodzielną całość, która może być zrozumiana bez informacji z poprzednich pakietów - pakiety nie układają się w “rozmowę”.

Koncepcja nie definiuje standardu protokołu, ale najczęściej wykorzystywany protokół do komunikacji to HTTP (z metodami GET, POST, PUT itd.), a sama zawartość wiadomości sformatowana jest jako najczęściej jako JSON, XML lub HTML.

Do obsługi zapytań HTTP w Python wykorzystamy bibliotekę requests.

W ramach pierwszego przykładu sprawdźmy aktualne kursy Bitcoin korzystając z API opisanego na stronie https://www.blockchain.com/api/exchange_rates_api

response = requests.get("https://blockchain.info/ticker")  # wysłanie zapytania GET pod odpowiedni adres, zapisanie odpowiedzi
print(response.text)  # zawartość odpowiedzi znajduje się w polu text

Możliwe jest żeby w zapytaniu zmodyfikować również nagłowek zapytania przesyłając dodatkowe informacje wymagane przez protokół:

headers = {
    "Pole":"wartosc"
}
response = requests.get(url, headers=headers)

Odpowiedź zwracana jest w formacie JSON (JavaScript Object Notation). Format wywodzi się z JavaScript i bardzo często wykorzystywany w zastosowaniach webowych czy IoT, niekoniecznie w powiązaniu z JS. JSON pozwala na przesłanie danych w formie par klucz-wartość jako ciągłego tekstu, która jednocześnie jest czytelna dla człowieka i może być sparsowana przez program. W Python format ten bardzo dobrze tłumaczy się z/na natywne słowniki przez moduł json:

bitcoin_dict = json.loads(req.text)

Z tak skonwertowanego słownika możemy już korzystać w standardowy sposób:

print(bitcoin_dict['USD']['last'])

Wykorzystanie modułu json sprowadza się do znajomości czterech metod:

• load: plik JSON → słownik Python
• loads: string JSON → słownik Python
• dump: słownik Python → plik JSON
• dumps: słownik Python → string JSON

🔥 Zadanie 3 🔥

Stwórz na podstawie powyższego zapytania DataFrame, w którym umieścisz wszystkie odebrane dane. Indeksem powinny być trzyznakowe symbole waluty, a kolumnami wartości kursów oraz symbol waluty (15m, last, buy, sell, symbol).

🔥 Zadanie 4 🔥

Świadczenie usług webowych wymaga oczywiście utrzymania infrastruktury serwerowej, w związku z czym wiele serwisów prowadzonych jest jako działalność komercyjna, gdzie część lub całość dostępu wymaga wykupienia dostępu lub przynajmniej utworzenia konta. Autoryzacja odbywa się często z wykorzystaniem tokenu, indywidualnego dla użytkownika.

Jako przykładowe API wymagające autoryzacji wykorzystamy dedykowany serwis przechowujący tytuły i autorów książek. Opis api można zależć tutaj

1. Odczytaj swój token z informacji umieszczonych na ekursach i przekaż go w nagłówku jako wartość pola “Authorization”
2. Spróbuj połączyć się z serwerem i pobrać pełną listę książek (endpoint /books, metoda GET). Zapisz ją do DataFrame, ile tych książek jest. Wyświetl pierwszy i ostatni tytuł na liście posortowanej alfabetycznie.
3. Dodaj do listy nowy tytuł ulubionej książki (endpoint /books, metoda POST).
4. Zweryfikuj czy pojawił się na liście
5. Możesz przejrzeć dokumentację w zakresie innych metod obsługiwanych przez server

Autorzy: Piotr Kaczmarek