/Cw9

TI:WTBD/Ćwiczenia 9

• przykład użycia SQLite w Pythonie: własnej roboty narzędzie do interaktywnych zapytań
• zapytania (SELECT) ze złączeniami (wewnętrznymi)
• zapytania agregujące
• tworzymy i wypełniamy danymi własne tabele (INSERT)

ćwiczymy zapytania

Przykładową bazę do zabawy można pobrać stąd: [1] (wybierz ,,zapisz jako", plik ten nie wyświetli się sensownie w przeglądarce). Zawiera ona kilka tabel z danymi dotyczącymi podziału administracyjnego RP (województwa i powiaty), oraz pocztowych numerów adresowych (zwanych popularnie kodami pocztowymi). Nie jest to przykład porządnie zaprojektowanej bazy służącej bezpośrednio jakiejś aplikacji, ale materiał roboczy który był użyty do zadania ustalenia mapowania pomiędzy kodem pocztowym adresu a powiatem (i województwem), na terenie którego adres ten się znajduje. Co nota bene okazało się dość nietrywialnym zadaniem. Tu dane te posłużą wyłącznie do konstrukcji przykładów.

• ile jest wierszy w tabeli pna?

   select count(*) from pna;

• ile różnych wartości pna (kodów pocztowych) występuje w tabeli pna?

   select count(distinct pna) from pna;

• znaleźć rekord(y) w tabeli pna opisujący(e) dany kod pocztowy:

   select * from pna where pna='00-681';

nb. pierwsze 'pna' jest nazwą tabeli, a drugie -- jednej z jej kolumn

• znaleźć wszystkie rekordy dotyczące adresów przy ulicy Hożej (dowolnego miasta!);

   select * from pna where okolica like '%ul. Hoża%';

• znaleźć rekordy opisujące ulicę Hożą, ale tylko tę w Warszawie:

   select * from pna where okolica like '%ul. Hoża%' and miejscowosc='Warszawa';

• nie wszystkie kolumny wyniku są nam potrzebne, powinniśmy więc raczej wymienić te, które naprawdę chcemy widzieć:

   select pna, okolica from from pna where okolica like '%ul. Hoża%' and miejscowosc='Warszawa';

• ile jest różnych wartości 'pna' w mieście Warszawa?

   select count(distinct pna) from pna where miejscowosc='Warszawa';

• ile jest powiatów w województwie mazowieckim?

   select kod from wojewodztwa where opis='mazowieckie';
   select count(*) from powiaty where wj_kod=14;

• to samo w jednym zapytaniu (w dwu wersjach):

   select count(*) from powiaty p join wojewodztwa w on w.kod=p.wj_kod and w.opis='mazowieckie';
   select count(*) from powiaty 
   where wj_kod=(select kod from wojewodztwa where opis='mazowieckie');

• ile powiatów ma każde województwo, w kolejności wg. malejącej liczby powiatów?

   select w.kod, w.opis, count(*) 
   from powiaty p join wojewodztwa w on w.kod=p.wj_kod 
   group by w.kod, w.opis order by count(*) desc;

definicja danych i wypełnienie tabel

Korzystaliśmy z plików z danymi stanowiącymi zawartość kilku powiązanych tabel, zawierających informacje, na podstawie których można zrekonstruować rozkład zajęć Wydziału Fizyki (dla pewnego okresu czasu). Pliki te zawierały zrzut danych z tych tabel (w sumie sześciu) w określonym formacie tekstowym. Zadanie polegało na odtworzeniu definicji danych (wraz z więzami kluczy obcych pomiędzy tabelami) i wyrażeniu jej w postaci ciągu instrukcji CREATE TABLE dla SQLite, oraz (za pomocą programu w Pythonie) wypełnieniu tych tabel danymi uzyskanymi poprzez parsowanie plików tekstowych.

Pliki z danymi (budynki.txt, sale.txt, przedmioty.txt, terminy.txt, zajecia_cykli.txt, terminy_grup.txt) zostały udostępnione na zajęciach, jeśli ktoś je zgubił i potrzebuje świeżej kopii -- proszę o kontakt. Wyglądają one typowo tak:

*************************** 132. row ***************************
  KOD: 1100-3BN15
NAZWA: Wstęp do technologii baz danych
 OPIS: <ol>
<li>Podstawy tekstowej reprezentacji danych; kodowania i obsługa operacji wejścia/wyjścia
<li>Ustrukturyzowane dane tekstowe: CSV i pokrewne
<li>Wyrażenia regularne jako narzędzie przetwarzania danych
<li>Główne pojęcia i zasady modelu relacyjnego
<li>Język SQL, podstawy składni; przykład implementacji: Sqlite
<li>Zaawansowany SQL, reguły normalizacji danych
<li>Oprogramowanie bazodanowe klient-serwer
<li>Rozwiązania nierelacyjne: bazy klucz-wartość i bazy "dokumentowe"
<li>XML jako format wymiany danych (opcjonalnie)
</ol>

a więc rekord (zawartość jednego wiersza tabeli) zaczyna się od linii nagłówkowej (tej z gwiazdkami, następnie kolejno podane są nazwy pól (kolumn tabeli) i ich zawartość dla danego rekordu. Treść każdego pliku to ciąg takich rekordów, odpowiadających zawartości jednej z tabel. Format ten nie jest idealny -- odtworzenie zawartości tabeli na jego podstawie nie jest w 100% jednoznaczne; tak jednak często bywa w realnych problemach obróbki danych.

Jak widać, treści niektórych pól (znakowych) mogą (ale nie muszą) zawierać znaczniki HTML, co akurat dla wykonywanego zadania jest raczej bez znaczenia.

Przykładowa realizacja programu odtwarzającego bazę (definicje tabel i dane w nich) podana jest poniżej.

#! /usr/bin/python
# coding: utf-8
# planzajec.py

import re 

TABELE = {'PRZEDMIOTY' : ('KOD PRIMARY KEY', 'NAZWA', 'OPIS')
        , 'ZAJECIA_CYKLI' : ('ID INT PRIMARY KEY', 'PRZ_KOD REFERENCES PRZEDMIOTY'
                    , 'CDYD_KOD', 'TZAJ_KOD', 'LICZBA_GODZ INT')
        , 'TERMINY_GRUP' : ('ID INT PRIMARY KEY', 'CZESTOTLIWOSC', 'GR_NR'
                    , 'TRM_ID INT REFERENCES TERMINY', 'SL_ID INT REFERENCES SALE'
                    , 'ZAJ_CYK_ID INT REFERENCES ZAJECIA_CYKLI')
        , 'TERMINY' : ('ID INT PRIMARY KEY', 'DZIEN_TYGODNIA'
                    , 'GODZINA_POCZATKU INT', 'MINUTA_POCZATKU INT'
                    , 'GODZINA_KONCA INT', 'MINUTA_KONCA INT')
        , 'SALE' : ('ID INT PRIMARY KEY', 'LICZBA_MIEJSC INT'
                    , 'BUD_KOD REFERENCES BUDYNKI', 'NUMER', 'TYP')
        , 'BUDYNKI' : ('KOD PRIMARY KEY', 'NAZWA', 'DLUGOSC_GEO', 'SZEROKOSC_GEO')
        }

def destroy(conn):
    for tab in TABELE:
        command = 'drop table if exists {}'.format(tab)
        conn.execute(command)

def create(conn):
    for tab in TABELE:
        command = 'create table if not exists {} '.format(tab)
        kolumny = '({})'.format(','.join(TABELE[tab]))
        conn.execute(command + kolumny)

headlrx = re.compile(r'^[*]{5,} \d+[.] row [*]{5,}$')
attrlx = re.compile(r'^ *([_0-9A-Z]+): (.*)$')
  
def tab_rows(plik):
    f = open(plik)
    encja = dict()
    for line in f:
        if headlrx.match(line):
            if encja:
                yield encja
                encja = dict()
            continue
        m = attrlx.match(line)
        if m:
            attr_name, attr_val = m.groups()
            encja[attr_name] = attr_val.decode('utf-8')
            if attr_val.strip() == 'NULL':
                encja[attr_name] = None
        else:
            encja[attr_name] += line.decode('utf-8')
    f.close()
    yield encja
    
def insert_rows(conn, tabela):
    plik = tabela.lower() + '.txt'
    kolumny = tuple(s.split()[0] for s in TABELE[tabela])
    command = u'insert into {0} ({1}) values '.format(tabela, ','.join(kolumny))
    command += u'({})'.format(','.join(':{}'.format(s) for s in kolumny))
    rows = tab_rows(plik)
    conn.executemany(command, rows)
    conn.commit()
    
def init_db(nazwa_bazy):
    from sqlite3 import connect
    from os import path
    plik = nazwa_bazy + '.db'
    do_destroy = False or path.lexists(plik)
    conn = connect(nazwa_bazy + '.db')
    if do_destroy:
        destroy(conn)
    create(conn)
    for tab in TABELE:
        insert_rows(conn, tab)
    conn.close()

def report(conn):
    c = conn.cursor()
    for tab in TABELE:
        c.execute('select count(*) from {}'.format(tab))
        ile = c.fetchall()[0][0]
        print '{}: {} wierszy'.format(tab, ile)

if __name__ == '__main__':
    from sqlite3 import connect
    init_db('plan')
    conn = connect('plan.db')
    report(conn)
    conn.close()

Schemat danych (definicje tabel) ujęto w tym programie w postaci słownika TABELE, mapującego nazwy tabel na krotki zawierające definicje kolumn danej tabeli. Nie jest to idealna reprezentacja, np. nie ma w niej miejsca na klucze czy indeksy wielokolumnowe -- jest jednak wystarczająca dla obecnego celu i wygodniejsza, niż wypisywanie w postaci stałych napisowych w programie pełnych treści odpowiednich instrukcji CREATE TABLE.

Na zakończenie swojego działania program testuje zawartość stworzonej bazy, wypisując liczbę wierszy jakie ostatecznie znalazły się w każdej tabeli. Do tego celu otwiera bazę na nowo, aby zweryfikować, że transakcja w której wstawiano dane została poprawnie zatwierdzona.

Dla czytelności program nie próbuje nawet obsługiwać jakichkolwiek wyjątków, nie będzie więc działać, jeśli postać danych wejściowych okaże się gdziekolwiek niezgodna z założeniami.