計算機科学のブログ

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)
%