SQL - Python - 賢いテーブル設計 - 正規化の理由 - select文, where句, like
Head First SQL ―頭とからだで覚えるSQLの基本、 Lynn Beighley(著)、 佐藤 直生(監訳)、 松永 多苗子(翻訳)、 オライリージャパンの 4章(賢いテーブル設計 - 正規化の理由)、p.161(自分で考えてみよう)の解答を求めてみる。
schema1.sql
drop table if exists fish_info;
create table fish_info (
common text,
species text,
location text,
weight text
);
insert into fish_info values
('バス、オオクチバス','M. salmoides','ジョージア州モンゴメリー湖','10.2kg'),
('ウォールアイ','S. vitreus','テネシー州オールドヒッコリ湖','11.3kg'),
('マス、カットスロートトラウト','O. Clarki','ネバタ州ピラミッド湖','18.5kg'),
('スズキ、イエローパーチ','P. Flavescens','ニュージャージー州ポーデンタウン','1.8kg'),
('ブルーギル','L. Macrochirus','アラバマ州カトナ湖','1.9kg'),
('ガー、ロングノーズガー','L. Osseus','テキサス州トリニティ川','22.9kg'),
('クラッピー、ホワイトクラッピー','P. annularis','ミシシッピ州エニッドダム','2.4kg'),
('カワカマス、クサカワカマス','E. americanus','インディアナ州デュワート湖','0.5kg'),
('ゴールドフィッシュ','C. auratus','カリフォルニア州ホッジス湖','2.8kg'),
('サケ、マスノスケ','O.Tshawytscha','アラスカ州ケナイ川','44.2kg');
drop table if exists fish_records;
create table fish_records (
first_name text,
last_name text,
common text,
location text,
state text,
weight text,
date text
);
insert into fish_records values
('ジョージ', 'ペリー', 'バス、オオクチバス','モンゴメリー湖','ジョージア州','10.2kg', '1932/6/2'),
('マブリー', 'ハーパー', 'ウォールアイ','オールドヒッコリ湖','テネシー州','11.3kg','1960/8/2'),
('ジョン', 'スキマホーン', 'マス、カットスロートトラウト','ピラミッド湖','ネバタ州','18.5kg', '1926/12/1'),
('C.C.', 'アボット', 'スズキ、イエローパーチ','ポーデンタウン','ニュージャージー州','1.8kg', '1865/5/1'),
('T.S.', 'ハドソン', 'ブルーギル','カトナ湖','アラバマ州', '1.9kg', '1950/4/9'),
('タウンセンド', 'ミラー', 'ガー、ロングノーズガー','テキサス州トリニティ川','アラバマ州','22.9kg', '1954/7/30'),
('ブレッド', 'ブライト', 'クラッピー、ホワイトクラッピー','エニッドダム','ミシシッピ州','2.4kg', '1957/7/31'),
('マイク', 'バーグ', 'カワカマス、クサカワカマス','デュワート湖','インディアナ州','0.5kg', '1990/6/9'),
('フローレンティーノ', 'アベナ', 'ゴールドフィッシュ','ホッジス湖','カリフォルニア州','2.8kg', '1996/4/17'),
('レス', 'アンダーソン', 'サケ、マスノスケ','ケナイ川','アラスカ州','44.2kg', '1985/5/17');
schema1_0.sql
select * from fish_info
where location like 'ニュージャージー州%';
schema1_1.sql
select * from fish_records
where state = 'ニュージャージー州';
コード
sample1.py
#! /usr/bin/env python3
import sqlite3
con = sqlite3.connect('sample.db')
cur = con.cursor()
with open('schema1.sql') as f:
cur.executescript(f.read())
con.commit()
for i in range(2):
with open(f'schema1_{i}.sql') as f:
cur.execute(f.read())
if (d := cur.description) is not None:
print([t[0] for t in d])
for row in cur.fetchall():
print(row)
cur.close()
con.close()
入出力結果(Terminal, Zsh)
% ./sample1.py
['common', 'species', 'location', 'weight']
('スズキ、イエローパーチ', 'P. Flavescens', 'ニュージャージー州ポーデンタウン', '1.8kg')
['first_name', 'last_name', 'common', 'location', 'state', 'weight', 'date']
('C.C.', 'アボット', 'スズキ、イエローパーチ', 'ポーデンタウン', 'ニュージャージー州', '1.8kg', '1865/5/1')
%