/Cw14

TI:WTBD/Ćwiczenia 14

Sprawdzian 2 - SQL.

Grupa 1

Plik Pl_lud_2010.txt zawiera dane o ludności Polski (wg. GUS) na koniec 2010 r., w rozbiciu na powiaty. Dane powiatów pogrupowane są w bloki, odpowiadające województwom, do których należą.

1. Proszę napisac program, który na podstawie tego pliku tworzy bazę SQLite z tabelą POWIATY i tabelą WOJEWODZTWA, wraz ze związkiem opisującym przynależność każdego powiatu do jednego z województw, oraz informacją o populacji każdego z powiatów.

2. Napisać zapytania SQL odpowiadające na pytania:

a) ile wynosiła populacja najbardziej i najmniej ludnego z województw (oraz jakie były ich nazwy)

b) dla każdego województwa, ile wynosiła średnia liczba ludności dla jego powiatów

c) ile wynosił stosunek ludności powiatów miejskich (grodzkich; tj. nazwanych nazwą miasta) do całkowitej ludności Polski

3. Włączyć te zapytania do powyższego programu jako wykonywane w zależności od argumentu wywołania

Wynikiem powinien być pojedynczy plik 'ludnosc.py', którego wywołanie z argumentem create tworzy i wypełnia tabele, a z argumentami q1, q2 i q3 podaje odpowiednio wyniki zapytania a), b) i c).

przykład rozwiązania dla zadania gr. 1

#! /usr/bin/python
# coding: utf-8

DEF_TABEL = ('''
    create table if not exists WOJEWODZTWA
        (ID int primary key, NAZWA)''',
    '''
    create table if not exists POWIATY
        (ID int primary key, NAZWA, W_ID references WOJEWODZTWA, LUDNOSC int)'''
    )  

def wstaw_dane(conn, plik):
    lines = open(plik)
    w_id, p_id = 0, 0
    for l in lines:
        l = l.decode('utf-8')
        if not l.startswith(u'\t'):  # to jest nagłówek, początek danych dla kolejnego województwa
            w_id += 1
            w_nazwa = l.strip(u':\n')
            conn.execute('''
                insert into WOJEWODZTWA (ID, NAZWA) values (?,?)
                ''', (w_id, w_nazwa))
        else:      # to jest linijka opisująca powiat aktualnego województwa
            row = l.strip().split(u'|')
            if len(row) == 2:
                p_id += 1
                conn.execute('''
                    insert into POWIATY (ID, W_ID, NAZWA, LUDNOSC) values (?,?,?,?)
                ''', (p_id, w_id, row[0], int(row[1])))
    conn.commit()
    lines.close()

QUERIES = (  '''select w.NAZWA, sum(p.LUDNOSC) as POPULACJA
                from WOJEWODZTWA w 
                join POWIATY p on w.ID=p.W_ID
                group by w.NAZWA
                having POPULACJA=(
                    select sum(LUDNOSC)
                    from POWIATY
                    group by W_ID
                    order by sum(LUDNOSC) desc
                    limit 1)
                or POPULACJA=(
                    select sum(LUDNOSC)
                    from POWIATY
                    group by W_ID
                    order by sum(LUDNOSC) asc
                    limit 1)
                order by POPULACJA desc''', # zapytanie dla podpunktu a)

             '''select w.NAZWA, sum(p.LUDNOSC)/count(*) as SREDNIA_POWIATU
                from WOJEWODZTWA w
                join POWIATY p on w.ID=p.W_ID
                group by w.NAZWA
                order by w.NAZWA
                ''',  # zapytanie dla podpunktu b)

             '''select 1.*sum(p.LUDNOSC)/(select sum(LUDNOSC) from POWIATY) as ULAMEK_W_P_MIEJSKICH 
                from POWIATY p
                where p.NAZWA like 'm. %' '''  # zapytanie dla podpunktu c)
          )

if __name__ == '__main__':
    from sqlite3 import connect
    from sys import argv
    conn = connect('pl_lud.db')
    if argv[1] == 'create':
        for command in DEF_TABEL:
            print command
            conn.execute(command)
            print u'... wykonane'.encode('utf-8')
        wstaw_dane(conn, 'pl_lud_2010.txt')
    elif argv[1] == 'drop':
        conn.execute('drop table if exists POWIATY')
        conn.execute('drop table if exists WOJEWODZTWA')
    elif argv[1] in ('q1', 'q2', 'q3'):
        c = conn.execute(QUERIES[int(argv[1][1])-1])
        result = [list(x[0] for x in c.description)]
        result.extend(c.fetchall())
        for line in result: # nie silimy się na specjalnie wymyślną prezentację wyniku ..
            print u' | '.join(unicode(x) for x in line).encode('utf-8')
    else:
        print >> sys.stderr, u'Błędny argument.'.encode('utf-8')
    conn.close()

Grupa 2

Plik Pl_lud_2010.csv zawiera dane o ludności Polski (wg. GUS) na koniec 2010 r., w rozbiciu na powiaty. Linijki zaczynające się od '##' otwierają bloki odpowiadające poszczególnym województwom.

1. Proszę napisac program, który na podstawie tego pliku tworzy bazę SQLite z tabelą POWIATY i tabelą WOJEWODZTWA, wraz ze związkiem opisującym przynależność każdego powiatu do jednego z województw, oraz informacją o liczbie ludności każdego z powiatów.

2. Napisać zapytania SQL odpowiadające na pytania:

a) ile wynosiła liczba ludności każdego z województw (w kolejności malejącej liczby)

b) jaki był powiat o największej liczbie ludności w każdym z województw (jego nazwa i liczba ludności) w kolejności alfabetycznej wg. nazw województw

c) jaka część powiatów w kraju miała zaludnienie przekraczające średnią dla powiatu w kraju

3. Włączyć te zapytania do powyższego programu jako wykonywane w zależności od argumentu wywołania

Wynikiem powinien być pojedynczy plik 'ludnosc.py', którego wywołanie z argumentem 'create' tworzy i wypełnia tabele, a z argumentami 'q1', 'q2' i 'q3' podaje odpowiednio wyniki zapytania a), b) i c).

przykład rozwiązania dla gr. 2

#! /usr/bin/python
# coding: utf-8

DEF_TABEL = ('''
    create table if not exists WOJEWODZTWA
        (ID int primary key, NAZWA)''',
    '''
    create table if not exists POWIATY
        (ID int primary key, NAZWA, W_ID references WOJEWODZTWA, LUDNOSC int)'''
    )

def wstaw_dane(conn, plik):
    lines = open(plik)
    w_id, p_id = 0, 0
    for l in lines:
        row = tuple(x.strip() for x in l.decode('utf-8').split(','))
        if row[0].startswith('##'):  # początek kolejnego województwa
            w_id += 1
            conn.execute('''
                insert into WOJEWODZTWA (ID, NAZWA) values (?,?)
                ''', (w_id,) + row[1:])
        else:   # kolejny powiat z aktualnego województwa
            p_id += 1
            conn.execute('''
                insert into POWIATY (ID, W_ID, NAZWA, LUDNOSC) values (?,?,?,?)
                ''', (p_id, w_id, row[0], int(row[1])))
    conn.commit()
    lines.close()

QUERIES = ('''
    select w.NAZWA, sum(p.LUDNOSC) as SUMA_LUDNOSCI
    from POWIATY p join WOJEWODZTWA w on w.ID=p.W_ID
    group by w.NAZWA
    order by SUMA_LUDNOSCI desc''',  #  zapytanie dla podpunktu a)

    '''
    select w.NAZWA as NAZWA_W, p.NAZWA as NAZWA_P, p.LUDNOSC as LUDNOSC_MAX
    from WOJEWODZTWA w join POWIATY p on w.ID=p.W_ID
    where p.LUDNOSC=(
        select max(LUDNOSC) from POWIATY where W_ID=w.ID)
    order by w.NAZWA;
    ''',    #  zapytanie dla podpunktu b)

    '''
    select 1.*count(*)/(select count(*) from POWIATY) as DUZYCH_POWIATOW
    from POWIATY
    where LUDNOSC > (select sum(LUDNOSC)/count(*) from POWIATY)
    '''   #  zapytanie dla podpunktu c)
)

if __name__ == '__main__':
    from sqlite3 import connect
    from sys import argv
    conn = connect('pl_lud.db')
    if argv[1] == 'create':
        for command in DEF_TABEL:
            print command
            conn.execute(command)
        wstaw_dane(conn, 'pl_lud_2010_00_03.csv')
    elif argv[1] == 'drop':   # tego nie musiało być, ale się przydaje
        conn.execute('drop table if exists POWIATY')
        conn.execute('drop table if exists WOJEWODZTWA')
    elif argv[1] in ('q1', 'q2', 'q3'):
        c = conn.execute(QUERIES[int(argv[1][1])-1])
        result = [list(x[0] for x in c.description)]
        result.extend(c.fetchall())
        for line in result:   # nie silimy się nadmiernie w prezentacji wyniku ...
            print u' | '.join(unicode(x) for x in line).encode('utf-8')
    else:
        print >> sys.stderr, u'Błędny argument.'.encode('utf-8')
    conn.close()