Databases 2つのテーブル、結合、INNER JOIN

開発環境

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

コード

#!/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('b.')
cur.execute('''
select A.population, B.population
from density A inner join Capitals B
where A.ProvinceOrTerritory == B.ProvinceOrTerritory
''')
for row in cur.fetchall():
    print(row)
print('c.')
cur.execute('''
select b.LandArea
from capitals a inner join density b
where a.ProvinceOrTerritory == b.ProvinceOrTerritory
and a.Population > 100000
''')
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)
b.
(512930, 172918)
(135294, 58358)
(908007, 359183)
(729498, 81346)
(7237479, 682757)
(11410046, 4682897)
(1119583, 671274)
(978933, 192800)
(2974807, 937845)
(3907738, 311902)
(28674, 21405)
(37360, 16541)
(26745, 5236)
c.
(370501.69,)
(52917.43,)
(1357743.08,)
(907655.59,)
(551937.87,)
(586561.35,)
(639987.12,)
(926492.48,)
%