Databases データの取得、select、条件、where、and、or、演算子、大小
Practical Programming: An Introduction to Computer Science Using Python 3.6 (Paul Gries(著)、Jennifer Campbell(著)、Jason Montojo(著)、Pragmatic Bookshelf)のChapter 17(Databases)、Exercise 1-e、f、g、h、i、j.の解答を求めてみる。
コード
#!/usr/bin/env python3
import sqlite3 as sql
# cur.execute('''
# CREATE TABLE Density
# (ProvinceOrTerritory TEXT, Population INTGER, LandArea REAL)
# ''')
print('1.')
con = sql.connect('census.db')
cur = con.cursor()
cur.execute('select * from density')
for row in cur.fetchall():
print(row)
print('e.')
cur.execute('select Population from Density')
for row in cur.fetchall():
print(row)
print('f.')
cur.execute(
'select ProvinceOrTerritory from Density where Population < ?', (1000000,))
for row in cur.fetchall():
print(row)
print('g.')
cur.execute(
'''select ProvinceOrTerritory from Density
where population < 1000000 OR
population > 5000000'''
)
for row in cur.fetchall():
print(row)
print('h.')
cur.execute(
'''select ProvinceOrTerritory from Density
where population >= 1000000 AND
population <= 5000000'''
)
for row in cur.fetchall():
print(row)
print('i.')
cur.execute(
'''select Population from Density
where LandArea > 200000'''
)
for row in cur.fetchall():
print(row)
print('j.')
cur.execute(
'''select Population, Population / LandArea from Density'''
)
for row in cur.fetchall():
print(row)
入出力結果
% ./sample1.py
1.
('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)
e.
(512930,)
(135294,)
(908007,)
(729498,)
(7237479,)
(11410046,)
(1119583,)
(978933,)
(2974807,)
(3907738,)
(28674,)
(37360,)
(26745,)
f.
('Newfoundland and Labrador',)
('Prince Edward Island',)
('Nova Scotia',)
('New Brunswick',)
('Saskatchewan',)
('Yukon Territory',)
('Northwest Territories',)
('Nunavut',)
g.
('Newfoundland and Labrador',)
('Prince Edward Island',)
('Nova Scotia',)
('New Brunswick',)
('Quebec',)
('Ontario',)
('Saskatchewan',)
('Yukon Territory',)
('Northwest Territories',)
('Nunavut',)
h.
('Manitoba',)
('Alberta',)
('British Columbia',)
i.
(512930,)
(7237479,)
(11410046,)
(1119583,)
(978933,)
(2974807,)
(3907738,)
(28674,)
(37360,)
(26745,)
j.
(512930, 1.384420135843375)
(135294, 23.8009707286094)
(908007, 17.15893988048928)
(729498, 10.223406212848959)
(7237479, 5.330521736115201)
(11410046, 12.570898175154742)
(1119583, 2.0284583842743027)
(978933, 1.6689353978062142)
(2974807, 4.648229483118348)
(3907738, 4.217776273802028)
(28674, 0.060403579075318826)
(37360, 0.03274009812056676)
(26745, 0.013890185981410428)
%