Python - Databases(INNER JOIN, WHERE, AND, sqlite3 module, connectmethod, 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-d.を解いてみる。

17.10(Exercises) 2-d.

コード(BBEdit)

sample2_d.py

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

import sqlite3

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

cur.execute('''
SELECT Capitals.province_territory FROM Capitals
INNER JOIN Density
WHERE Capitals.province_territory = Density.province_territory
AND Density.population / Density.area < 2
AND Capitals.population > 500000
''')
for row in cur.fetchall():
    print(row)

connection.commit()
connection.close()

入出力結果(Terminal, IPython)

$ ./sample2_d.py
$