entertainment-lab: 初めてのコンピュータサイエンス第15章解答

いよいよ最終章、第15章練習問題の解答です。以下ネタバレなので、読みたくない方は立ち去りましょう。問題文は載せませんので悪しからず(英語版はpdfで公開されている模様)。日本語版が必要なら買いましょう。その前にこの章の個人的なメモ。

Pythonの標準ライブラリにはSQLite3というSQLiteというDBを操作するためのライブラリがある。SQLiteは大規模なものには向かないが、小規模なものであれば十分利用に耐えうる。
データベースにプログラムが直接手を加えるということはなく、DBMSが操作の仲介の役割を果たす。
データベースのキーにID(つまり整数)を持たせることには重要な意味がある。整数の方がソートが高速であるということ、同じ名前の物、人物の区別に便利だからである。
NULLの取り扱いはデータベースによって異なる。データベースはSQLite以外にもMySQL、OrableDB、Accessなどがある。
クエリーはネストできる。特定の条件Aに合うものだけ抽出し、その抽出したものに対し、別の条件Bに合うものだけを抽出するといったことができる。

SQLの基本的な決まり文句の一覧には以下のようなものがある。

コマンド	意味
CREATE TABLE Men(Name TEXT, Age INTEGER)	Name(人物名)という文字列型の列、Age(年齢)という整数型の列からなるMenというデータベースを作成する
INSERT INTO Men VALUES("Taro", 30)	MenというDBに("Taro", 30)という値のレコードを挿入する
SELECT Name FROM Men WHERE Age >= 25	年齢が25歳以上の人物の名前を抽出する
SELECT Name FROM Men WHERE Age >= 25 AND Age <=40	年齢が25歳以上40歳以下の人物の名前を抽出する
SELECT * FROM Men WHERE Name="Taro"	名前が"Taro"という人をすべて抽出する
UPDATE Men SET Age=31 WHERE Name="Taro"	TaroのAgeを31に更新する
DELETE FROM Men WHERE Name="Taro"	NameがTaroというレコードを削除する。TaroというNameのものがすべて消える
CREATE TABLE Men(Name TEXT NOT NULL, Age INTEGER)	NameがNULLのレコードを認めないデータベースを作成する。
INNER JOIN	直積を作成する(クエリー間で比較するときに使える)
PRIMARY KEY	一意になるキー列を設定する(キーを複数入れないようにするエラーチェックに使える)
SELECT AVG(Age) FROM Men	平均年齢を計算する
SELECT MAX(Age) FROM Men GROUP BY Name	Nameごとに年齢の最大値を計算する
SELECT DISTINCT Name FROM Men	Nameの重複を排除してNameを抽出

では解答に移ることにする。

a)～c)の解答。DBへのデータの挿入はFor文とタプルを利用した方法でもよいでしょう。

# -*- encoding: utf8 -*-
import sqlite3 as dbapi

con = dbapi.connect('census.db')
cur = con.cursor()

cur.execute('CREATE TABLE Density(State TEXT, Population INTEGER, Area REAL)')

cur.execute('INSERT INTO Density VALUES("Newfoundland and Labrador", 512930, 370501.69)')
cur.execute('INSERT INTO Density VALUES("Prince Edward Island", 135294, 5684.39)')
cur.execute('INSERT INTO Density VALUES("Nova Scotia", 908007, 52917.43)')
cur.execute('INSERT INTO Density VALUES("New Brunswick", 729498, 71355.67)')
cur.execute('INSERT INTO Density VALUES("Quebec", 7237479, 1357743.08)')
cur.execute('INSERT INTO Density VALUES("Ontario", 11410046, 907655.59)')
cur.execute('INSERT INTO Density VALUES("Manitoba", 1119583, 551937.87)')
cur.execute('INSERT INTO Density VALUES("Saskatchewan", 978933, 586561.35)')
cur.execute('INSERT INTO Density VALUES("Alberta", 2974807, 639987.12)')
cur.execute('INSERT INTO Density VALUES("British Columbia", 3907738, 926492.48)')
cur.execute('INSERT INTO Density VALUES("Yukon Territory", 28674, 474706.97)')
cur.execute('INSERT INTO Density VALUES("Northwest Territories", 37360, 1141108.37)')
cur.execute('INSERT INTO Density VALUES("Nunavut", 26745, 1925460.18)')

con.commit()

d)～j)の解答はこちら。基本的なSQL文の練習問題です。j)の計算結果を列として並べて表示するという方法は調べて納得。

# -*- encoding: utf8 -*-

import sqlite3 as dbapi

con = dbapi.connect('census.db')
cur = con.cursor()

con.text_factory = str

# d)の解答
cur.execute('SELECT * FROM Density')
ans_d = cur.fetchall()
print ans_d

# e)の解答
cur.execute('SELECT Population FROM Density')
ans_e = cur.fetchall()
print ans_e # リストの中身は要素1のタプルである

# f)の解答
cur.execute('SELECT State FROM Density WHERE Population < 1000000')
ans_f = cur.fetchall()
print ans_f

# g)の解答
cur.execute('SELECT State FROM Density WHERE \
             Population < 1000000 OR Population > 5000000')
ans_g = cur.fetchall()
print ans_g

# h)の解答
cur.execute('SELECT State FROM Density WHERE \
             NOT(Population < 1000000 OR Population > 5000000)')
ans_h = cur.fetchall()
print ans_h

# i)の解答
cur.execute('SELECT Population FROM Density WHERE Area > 200000')
ans_i = cur.fetchall()
print ans_i

# j)の解答 州の名前と人口密度を表示
cur.execute('SELECT State, Population / Area FROM Density')
ans_j = cur.fetchall()
print ans_j

1のDBも使って解答します。まずは次のコードで、データを挿入します。

# -*- encoding: utf8 -*-

import sqlite3 as dbapi

con = dbapi.connect('census.db')
cur = con.cursor()

con.text_factory = str

cur.execute('CREATE TABLE Capitals(State TEXT, Capital TEXT, Population INTEGER)')

# Province/Territory Capital Population
data =(
("Newfoundland and Labrador", "St. John’s", 172918),
("Prince Edward Island", "Charlottetown", 58358),
("Nova Scotia", "Halifax", 359183),
("New Brunswick", "Fredericton", 81346),
("Quebec", "Quebec", 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 d in data:
    cur.execute('INSERT INTO Capitals VALUES(?, ?, ?)', (d[0], d[1], d[2]))

con.commit()

以下、a)～i)までの解答です。

# -*- encoding: utf8 -*-

import sqlite3 as dbapi

con = dbapi.connect('census.db')
cur = con.cursor()

con.text_factory = str

cur.execute('SELECT * FROM Capitals')
ans_a = cur.fetchall()
print ans_a

cur.execute('SELECT Density.Population, Capitals.Population \
FROM Density INNER JOIN Capitals WHERE Density.State = Capitals.State')
ans_b = cur.fetchall()
print ans_b

cur.execute('SELECT Density.Area FROM Density INNER JOIN Capitals \
WHERE Capitals.Population > 100000 AND Density.State = Capitals.State')
ans_c = cur.fetchall()
print ans_c

cur.execute('SELECT Density.Area FROM Density INNER JOIN Capitals \
WHERE Capitals.Population > 500000 AND \
Density.Population *1.0 / Density.Area < 2.0 AND \
Density.State = Capitals.State')
ans_d = cur.fetchall()
print ans_d

cur.execute('SELECT SUM(Area) FROM Density')
ans_e = cur.fetchall()
print ans_e

cur.execute('SELECT AVG(Population) FROM Capitals')
ans_f = cur.fetchall()
print ans_f

cur.execute('SELECT MIN(Capitals.Population) FROM Capitals')
ans_g = cur.fetchall()
print ans_g

# 著者による解答は間違ってそうな気がします。著者はもっとも多い人口の値を求めていますが、
# 求める必要があるのは、人口最大の州の名前です。
cur.execute('SELECT Density.State FROM Density \
WHERE Density.Population IN \
    (SELECT MAX(Population) FROM Density)')
ans_h = cur.fetchall()
print ans_h

cur.execute('SELECT A.State, B.State FROM \
Density A INNER JOIN Density B WHERE \
ABS(A.Population * 1.0 / A.Area - B.Population * 1.0 / B.Area) < 0.5 \
AND A.State < B.State')
ans_i = cur.fetchall()
print ans_i

SELECT文の結果を表示すると、空のリストになる。Pythonで1/0を含めるとZeroDivisionErrorになる。

>>> 1/0
Traceback (most recent call last):
  File "<interactive input>"line 1, in <module>
ZeroDivisionError: integer division or modulo by zero
>>> 1 > 0 and 1 / 0
Traceback (most recent call last):
  File "<interactive input>"line 1, in <module>
ZeroDivisionError: integer division or modulo by zero
>>> 1 / 0 and 1 > 0
Traceback (most recent call last):
  File "<interactive input>"line 1, in <module>
ZeroDivisionError: integer division or modulo by zero

デフォルト引数を利用することで、外部からパラメータを渡すことができるようになる。もっとチェックするのであれば、query中の?の数とタプルの要素数が一致していることを確認するということが挙げられます。問題文の趣旨から外れていきそうなので、そこまではしませんが。

# -*- encoding: utf8 -*-

def run_query(db, query, param=None):
    '''データベースdbに対してqueryを実行して結果を返す '''

    con = sqlite.connect()
    cur = con.cursor()
    if param != None:
        cur.execute(query)
    else:
        if type(param) == tuple: # タプルのみを受け付ける
            cur.execute(query, param)
    data = cur.fetchall()
    cur.close()
    con.close()
    return data

# example
run_query(db, 'SELECT * FROM Precipitation')
run_query(db, 'SELECT City, Temp FROM Precipitation \
    WHERE Snow >= (?) and Temp > (?)', (s, t))

entertainment-lab

2012年6月22日金曜日

初めてのコンピュータサイエンス第15章解答

0 件のコメント:

コメントを投稿

フォロワー

ブログアーカイブ

profile

ページビューの合計