Databases テーブル、自己結合、重複の回避

開発環境

Practical Programming: An Introduction to Computer Science Using Python 3.6 (Paul Gries(著)、Jennifer Campbell(著)、Jason Montojo(著)、Pragmatic Bookshelf)のChapter 17(Databases)、Exercise 2-i.の解答を求めてみる。

コード

#!/usr/bin/env python3
import sqlite3 as sql

# cur.execute('''
# CREATE TABLE Density
# (ProvinceOrTerritory TEXT, Population INTGER, LandArea REAL)
# ''')
# cur.execute('''
# CREATE TABLE Capitals
# (ProvinceOrTerritory TEXT, Capital TEXT, Population Integer)
# ''')
print('2.')
con = sql.connect('census.db')
cur = con.cursor()
print('Density')
cur.execute('''select * from density''')
for row in cur.fetchall():
    print(row)
print('Capitals')
cur.execute('''select * from capitals''')
for row in cur.fetchall():
    print(row)

print('i.')
cur.execute('''
select a.ProvinceOrTerritory, b.ProvinceOrTerritory
from density a inner join density b
where a.ProvinceOrTerritory < b.ProvinceOrTerritory
and abs(a.population / a.landarea - b.population / b.landarea) <= 0.5
''')
for row in cur.fetchall():
    print(row)
cur.close()
con.close()

入出力結果

% ./sample2.py
2.
Density
('Newfoundland and Labrador', 512930, 370501.69)
('Prince Edward Island', 135294, 5684.39)
('Nova Scotia', 908007, 52917.43)
('New Brunswick', 729498, 71355.67)
('Quebec', 7237479, 1357743.08)
('Ontario', 11410046, 907655.59)
('Manitoba', 1119583, 551937.87)
('Saskatchewan', 978933, 586561.35)
('Alberta', 2974807, 639987.12)
('British Columbia', 3907738, 926492.48)
('Yukon Territory', 28674, 474706.97)
('Northwest Territories', 37360, 1141108.37)
('Nunavut', 26745, 1925460.18)
Capitals
('Newfoundland and Labrador', "St. John's", 172918)
('Prince Edward Island', 'Charlottetown', 58358)
('Nova Scotia', 'Halifax', 359183)
('New Brunswick', 'Fredericton', 81346)
('Quebec', 'Qeubec City', 682757)
('Ontario', 'Toronto', 4682897)
('Manitoba', 'Winnipeg', 671274)
('Saskatchewan', 'Regina', 192800)
('Alberta', 'Edmonton', 937845)
('British Columbia', 'Victoria', 311902)
('Yukon Territory', 'Whitehorse', 21405)
('Northwest Territories', 'Yellowknife', 16541)
('Nunavut', 'Iqaluit', 5236)
i.
('Newfoundland and Labrador', 'Saskatchewan')
('Manitoba', 'Saskatchewan')
('Alberta', 'British Columbia')
('Northwest Territories', 'Yukon Territory')
('Northwest Territories', 'Nunavut')
('Nunavut', 'Yukon Territory')
%