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

17.10(Exercises) 2.

コード(BBEdit)

sample2.py

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

import sqlite3

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

cur.execute('''
CREATE TABLE Capitals(
province_territory TEXT NOT NULL,
capital TEXT NOT NULL,
population INTEGER NOT NULL)
''')

data = [
    ('Newfoundland and Labrador', "St. John’s", 172918),
    ('Prince Edward Island', 'Charlottetown', 58358),
    ('Nova Scotia', 'Halifax', 359183),
    ('New Brunswick', 'Fredericton', 81346),
    ('Quebec','Quebec City', 682757),
    ('Ontario', 'Toronto', 4682897),
    ('Manitoba', 'Winnipeg', 671274),
    ('Saskatchewan', 'Regina', 192800),
    ('Alberta', 'Victoria', 311902),
    ('British Columbia', 'Edmonton', 937845),
    ('Yukon Territory', 'Whitehorse', 21405),
    ('Northwest Territories', 'Yellowknife', 16541),
    ('Nunavut', 'Iqaluit', 5236)
    ]

for row in data:
    cur.execute('''
    INSERT INTO Capitals VALUES(?, ?, ?)
    ''', row)

connection.commit()
connection.close()

入出力結果(Terminal, IPython)

$ ./sample2.py
$