Databases テーブルの作成、データの挿入、データの表示、例外処理、OperationalError

開発環境

Practical Programming: An Introduction to Computer Science Using Python 3.6 (Paul Gries(著)、Jennifer Campbell(著)、Jason Montojo(著)、Pragmatic Bookshelf)のChapter 17(Databases)、Exercise 2-a.の解答を求めてみる。

コード

#!/usr/bin/env python3
import sqlite3 as sql

# cur.execute('''
# CREATE TABLE Density
# (ProvinceOrTerritory TEXT, Population INTGER, LandArea REAL)
# ''')

print('2.')
con = sql.connect('census.db')
cur = con.cursor()
try:
    cur.execute('''
    CREATE TABLE Capitals
    (ProvinceOrTerritory TEXT, Capital TEXT, Population Integer)
    ''')
    table = [
        ('Newfoundland and Labrador', "St. John's", 172918),
        ('Prince Edward Island', 'Charlottetown', 58358),
        ('Nova Scotia', 'Halifax', 359183),
        ('New Brunswick', 'Fredericton', 81346),
        ('Quebec', 'Qeubec City', 682757),
        ('Ontario', 'Toronto', 4682897),
        ('Manitoba', 'Winnipeg', 671274),
        ('Saskatchewan', 'Regina', 192800),
        ('Alberta', 'Edmonton', 937845),
        ('British Columbia', 'Victoria', 311902),
        ('Yukon Territory', 'Whitehorse', 21405),
        ('Northwest Territories', 'Yellowknife', 16541),
        ('Nunavut', 'Iqaluit', 5236),
    ]
    for row in table:
        cur.execute('insert into Capitals values(?, ?, ?)', row)
except sql.OperationalError as err:
    print(err)
except Exception as err:
    print(err)

print('a.')
cur.execute('select * from capitals')
for row in cur.fetchall():
    print(row)
con.commit()
cur.close()
con.close()

入出力結果

% ./sample2.py
2.
a.
('Newfoundland and Labrador', "St. John's", 172918)
('Prince Edward Island', 'Charlottetown', 58358)
('Nova Scotia', 'Halifax', 359183)
('New Brunswick', 'Fredericton', 81346)
('Quebec', 'Qeubec City', 682757)
('Ontario', 'Toronto', 4682897)
('Manitoba', 'Winnipeg', 671274)
('Saskatchewan', 'Regina', 192800)
('Alberta', 'Edmonton', 937845)
('British Columbia', 'Victoria', 311902)
('Yukon Territory', 'Whitehorse', 21405)
('Northwest Territories', 'Yellowknife', 16541)
('Nunavut', 'Iqaluit', 5236)
kamimura@iMac ch17 % ./sample2.py
2.
table Capitals already exists
a.
('Newfoundland and Labrador', "St. John's", 172918)
('Prince Edward Island', 'Charlottetown', 58358)
('Nova Scotia', 'Halifax', 359183)
('New Brunswick', 'Fredericton', 81346)
('Quebec', 'Qeubec City', 682757)
('Ontario', 'Toronto', 4682897)
('Manitoba', 'Winnipeg', 671274)
('Saskatchewan', 'Regina', 192800)
('Alberta', 'Edmonton', 937845)
('British Columbia', 'Victoria', 311902)
('Yukon Territory', 'Whitehorse', 21405)
('Northwest Territories', 'Yellowknife', 16541)
('Nunavut', 'Iqaluit', 5236)
%