PostgreSQL - Python - 賢いテーブル設計 - 正規化の理由(where、text(文字型)、like、ワイルドカード(%))

Head First SQL
原著
楽天ブックス(Kobo) Yahoo!

開発環境

• macOS Mojave - Apple (OS)
• Emacs (Text Editor)
• Windows 10 Pro (OS)
• Visual Studio Code (Text Editor)
• PostgreSQL (ORDBMS(object-relational database management system))
• Python 3.7 (プログラミング言語)
• psycopg2 (パッケージ)

Head First SQL ―頭とからだで覚えるSQLの基本 (Lynn Beighley (著), 佐藤 直生 (監訳)、松永 多苗子 (翻訳)、オライリージャパン)の4章(賢いテーブル設計 - 正規化の理由)、自分で考えてみよう(p. 161).を取り組んでみる。

コード(Emacs)

Python 3

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

conn = sql.connect(database='gregs_list', user='kamimura')
cursor = conn.cursor()


def p(table):
    _sql = f'select * from {table}'
    cursor.execute(_sql)
    print(', '.join([column[0] for column in cursor.description]))
    for row in cursor.fetchall():
        print(row)


_sql = 'drop table fish_info'
cursor.execute(_sql)

_sql = '''
create table if not exists fish_info(
common text,
species text,
location text,
weight real)
'''
cursor.execute(_sql)

_sql = 'insert into fish_info values(%s, %s, %s, %s)'
values = [('バス、オオクチバス',
           'M. solmoides',
           'ジョージア州モンゴメリー湖',
           10.2),
          ('スズキ、イエローパーチ',
           'P. Flavescens',
           'ニュージャージー州ボーデンタウン',
           1.8)]

cursor.executemany(_sql, values)
conn.commit()

_sql = 'drop table fish_records'
cursor.execute(_sql)
_sql = '''
create table if not exists fish_records(
first_name text,
last_name text,
common text,
location text,
state text,
weight real,
date date)
'''

cursor.execute(_sql)

_sql = 'insert into fish_records values(%s, %s, %s, %s, %s, %s, %s)'
values = [('ジョージ',
           'ペリー',
           'バス、オオクチバス',
           'モンゴメリー湖',
           'ジョージア州',
           10.2,
           '1932-06-02'),
          ('C.C.',
           'アボット',
           'スズキ、イエローパーチ',
           'ボーデンタウン',
           'ニュージャージー州',
           1.8,
           '1865-05-01')
          ]

cursor.executemany(_sql, values)
conn.commit()

_sqls = [('fish_info',
          '''
select * from
fish_info
where location LIKE 'ニュージャージー州%'
'''),
         ('fish_records',
          '''
select * from
fish_records
where state = 'ニュージャージー州'
''')]

for table, _sql in _sqls:
    print(table)
    p(table)
    cursor.execute(_sql)
    print(_sql)
    for row in cursor.fetchall():
        print(row)
    print()

cursor.close()
conn.close()

入出力結果(Terminal, cmd(コマンドプロンプト), Jupyter(IPython))

$ ./sample1.py
fish_info
common, species, location, weight
('バス、オオクチバス', 'M. solmoides', 'ジョージア州モンゴメリー湖', 10.2)
('スズキ、イエローパーチ', 'P. Flavescens', 'ニュージャージー州ボーデンタウン', 1.8)

select * from
fish_info
where location LIKE 'ニュージャージー州%'

('スズキ、イエローパーチ', 'P. Flavescens', 'ニュージャージー州ボーデンタウン', 1.8)

fish_records
first_name, last_name, common, location, state, weight, date
('ジョージ', 'ペリー', 'バス、オオクチバス', 'モンゴメリー湖', 'ジョージア州', 10.2, datetime.date(1932, 6, 2))
('C.C.', 'アボット', 'スズキ、イエローパーチ', 'ボーデンタウン', 'ニュージャージー州', 1.8, datetime.date(1865, 5, 1))

select * from
fish_records
where state = 'ニュージャージー州'

('C.C.', 'アボット', 'スズキ、イエローパーチ', 'ボーデンタウン', 'ニュージャージー州', 1.8, datetime.date(1865, 5, 1))

$