Python - Databases (sqlite3 module, connect method, cursor method, execute method, SQL)

Practical Programming
An Introduction to Computer Science
Using Python 3
(Pragmatic Programmers)
(Pragmatic Bookshelf)
Paul Gries (著) Jennifer Campbell (著)
Jason Montojo (著) Lynn Beighley (編集)

開発環境

• OS X Yosemite - Apple (OS)
• Emacs (CUI)、BBEdit - Bare Bones Software, Inc. (GUI) (Text Editor)
• Python 3.4 (プログラミング言語)

Practical Programming: An Introduction to Computer Science Using Python 3 (Pragmatic Programmers) (Paul Gries (著)、Jennifer Campbell (著)、Jason Montojo (著)、Lynn Beighley (編集)、Pragmatic Bookshelf)のChapter 17(Databases)、17.10(Exercises) 1-a, b, c, d, e, f, g, h, i, j.を解いてみる。

17.10(Exercises) 1-a, b, c, d, e, f, g, h, i, j.

コード(BBEdit)

sample1.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3

print('a.')
connection = sqlite3.connect('census.db')
cur = connection.cursor()

print('b.')
cur.execute('''
CREATE TABLE Density(
Province_or_territory TEXT NOT NULL,
Population INTEGER NOT NULL,
Area REAL NOT NULL)
''')

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)

connection.commit()

print('e.')
cur.execute('SELECT Province_or_territory FROM Density')
print(cur.fetchall())

print('f.')
cur.execute('SELECT Population FROM DENSITY')
print(cur.fetchall())

print('g.')
cur.execute('''
SELECT Province_or_territory FROM Density
WHERE Population < 1000000
OR Population > 5000000''')
print(cur.fetchall())

print('h.')
cur.execute('''
SELECT Province_or_territory FROM Density
WHERE NOT (Population < 1000000
OR Population > 5000000)''')
print(cur.fetchall())

print('i')
cur.execute('''
SELECT Population FROM Density
WHERE Area > 200000''')
print(cur.fetchall())

print('j.')
cur.execute('''SELECT Province_or_territory, Population / Area FROM Density''')
print(cur.fetchall())

connection.close()

入出力結果(Terminal, IPython)

$ ./sample1.py
a.
b.
c.
e.
[('Newfoundland and Labrador',), ('Prince Edward Island',), ('Nova Scotia',), ('New Brunswick',), ('Quebec',), ('Ontario',), ('Manitoba',), ('Saskatchewan',), ('Alberta',), ('British Columbia',), ('Yukon Territory',), ('Northwest Territories',), ('Nunavut',)]
f.
[(512930,), (135294,), (908007,), (729498,), (7237479,), (11410046,), (1119583,), (978933,), (2974807,), (3907738,), (28674,), (37360,), (26745,)]
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.
[('Newfoundland and Labrador', 1.384420135843375), ('Prince Edward Island', 23.8009707286094), ('Nova Scotia', 17.15893988048928), ('New Brunswick', 10.223406212848959), ('Quebec', 5.330521736115201), ('Ontario', 12.570898175154742), ('Manitoba', 2.0284583842743027), ('Saskatchewan', 1.6689353978062142), ('Alberta', 4.648229483118348), ('British Columbia', 4.217776273802028), ('Yukon Territory', 0.060403579075318826), ('Northwest Territories', 0.03274009812056676), ('Nunavut', 0.013890185981410428)]
$