Python - Databases(compare, ABS, sqlite3 module, connect method, cursormethod, execute method, fetchall method)

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) 2-i.を解いてみる。

17.10(Exercises) 2-i.

コード(BBEdit)

sample2_i.py

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

import sqlite3

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

cur.execute('''
SELECT a.province_territory, b.province_territory
FROM Density a
INNER JOIN Density b
WHERE a.province_territory < b.province_territory
AND ABS(a.population / a.area - b.population / b.area) < 0.5
''')

for row in cur.fetchall():
    print(row)

connection.commit()
connection.close()

入出力結果(Terminal, IPython)

$ ./sample2_i.py
('Newfoundland and Labrador', 'Saskatchewan')
('Manitoba', 'Saskatchewan')
('Alberta', 'British Columbia')
('Northwest Territories', 'Yukon Territory')
('Northwest Territories', 'Nunavut')
('Nunavut', 'Yukon Territory')
$