Databases テーブルの作成、データ型、CREATE、INSERT、SELECT、SQLite、connect、cursor、execute、fetchall

開発環境

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

コード

#!/usr/bin/env python3
import sqlite3 as sql
print('1.')
print('a.')
con = sql.connect('census.db')
cur = con.cursor()

print('b.')
cur.execute('''
CREATE TABLE Density
(ProvinceOrTerritory TEXT, Population INTGER, LandArea REAL)
''')
con.commit()
print('c.')
data = [
    ('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),
]
for row in data:
    cur.execute('INSERT INTO Density VALUES(?, ?, ?)', row)
con.commit()

print('d.')
cur.execute('SELECT * FROM Density')
for row in cur.fetchall():
    print(row)
con.close()

入出力結果

% ./sample1.py 
1.
a.
b.
c.
d.
('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)
%