/Cw14
Spis treści
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()
