Python - Databases(INNER JOIN, WHERE, sqlite3 module, connect method,cursor method, 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-c.を解いてみる。

17.10(Exercises) 2-c.

コード(BBEdit)

sample2_c.py

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

import sqlite3

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

cur.execute('''
SELECT Capitals.province_territory, Density.area FROM Capitals
INNER JOIN Density
WHERE Capitals.province_territory = Density.province_territory
AND Capitals.population > 100000
''')

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

connection.commit()
connection.close()

入出力結果(Terminal, IPython)

$ ./sample2_c.py
('Newfoundland and Labrador', 370501.69)
('Nova Scotia', 52917.43)
('Quebec', 1357743.08)
('Ontario', 907655.59)
('Manitoba', 551937.87)
('Saskatchewan', 586561.35)
('Alberta', 639987.12)
('British Columbia', 926492.48)
$