PostgreSQL - Python - 賢いテーブル設計 - 正規化の理由(primary key、serial型(数値)、連番)

開発環境

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. 189)の解答を求めてみる。

コード

Python 3

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

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


def p(table='your_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)
    print()


_sql = '''drop table if exists your_table'''
cursor.execute(_sql)
conn.commit()

print('1.')
_sql = '''
create table your_table (
id serial primary key,
first_name text,
last_name  text
)
'''
print(_sql)

print('2.')
cursor.execute(_sql)
conn.commit()

print('3.')
_sql = '''
insert into your_table values (%s, %s, %s)
'''

values_lisst = [(None, 'マルシア', 'ブレイディ'),
                # エラー(シリアルがNone(Null))
                (1, 'ジャン', 'ブレイディ'),
                (2, 'ボビー', 'ブレイディ'),
                (99, 'ピーター', 'ブレイディ')]

for values in values_lisst:
    print(values)
    try:
        cursor.execute(_sql, values)
    except Exception as err:
        print(type(err), err)
    finally:
        conn.commit()
        p()

_sql = '''
insert into your_table (first_name, last_name) values (%s, %s)
'''
values = ('シンディ', 'プレディ')
try:
    print(_sql, values)
    cursor.execute(_sql, values)
except Exception as err:
    print(err)
finally:
    conn.commit()
    p()

print('4.')
p()

cursor.close()
conn.close()

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

$ ./sample2.py
1.

create table your_table (
id serial primary key,
first_name text,
last_name  text
)

2.
3.
(None, 'マルシア', 'ブレイディ')
<class 'psycopg2.IntegrityError'> null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, マルシア, ブレイディ).

id, first_name, last_name

(1, 'ジャン', 'ブレイディ')
id, first_name, last_name
(1, 'ジャン', 'ブレイディ')

(2, 'ボビー', 'ブレイディ')
id, first_name, last_name
(1, 'ジャン', 'ブレイディ')
(2, 'ボビー', 'ブレイディ')

(99, 'ピーター', 'ブレイディ')
id, first_name, last_name
(1, 'ジャン', 'ブレイディ')
(2, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')


insert into your_table (first_name, last_name) values (%s, %s)
 ('シンディ', 'プレディ')
duplicate key value violates unique constraint "your_table_pkey"
DETAIL:  Key (id)=(1) already exists.

id, first_name, last_name
(1, 'ジャン', 'ブレイディ')
(2, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')

4.
id, first_name, last_name
(1, 'ジャン', 'ブレイディ')
(2, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')

$

PostgreSQLにはMySQLのauto incrementがないから代わりにSerialを使って自動で番号を割り当てようとしたけど、最初は1番から割り当てていくみたいで、既に割り当てられてる1を割り当てようとして、プライマリーキーの重複でエラーになるみたい。

idの1番は開けておいてから自動で割り当ててみる。

コード

Python 3

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

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


def p(table='your_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)
    print()


_sql = '''drop table if exists your_table'''
cursor.execute(_sql)
conn.commit()

print('1.')
_sql = '''
create table your_table (
id serial primary key,
first_name text,
last_name  text
)
'''
print(_sql)

print('2.')
cursor.execute(_sql)
conn.commit()

print('3.')
_sql = '''
insert into your_table values (%s, %s, %s)
'''

values_lisst = [(None, 'マルシア', 'ブレイディ'),
                # エラー(シリアルがNone(Null))
                (2, 'ジャン', 'ブレイディ'),
                (3, 'ボビー', 'ブレイディ'),
                (99, 'ピーター', 'ブレイディ')]

for values in values_lisst:
    print(values)
    try:
        cursor.execute(_sql, values)
    except Exception as err:
        print(type(err), err)
    finally:
        conn.commit()
        p()

_sql = '''
insert into your_table (first_name, last_name) values (%s, %s)
'''
values = ('シンディ', 'プレディ')
try:
    print(_sql, values)
    cursor.execute(_sql, values)
except Exception as err:
    print(err)
finally:
    conn.commit()
    p()

print('4.')
p()

cursor.close()
conn.close()

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

$ ./sample2.py
1.

create table your_table (
id serial primary key,
first_name text,
last_name  text
)

2.
3.
(None, 'マルシア', 'ブレイディ')
<class 'psycopg2.IntegrityError'> null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, マルシア, ブレイディ).

id, first_name, last_name

(2, 'ジャン', 'ブレイディ')
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')

(3, 'ボビー', 'ブレイディ')
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')
(3, 'ボビー', 'ブレイディ')

(99, 'ピーター', 'ブレイディ')
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')
(3, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')


insert into your_table (first_name, last_name) values (%s, %s)
 ('シンディ', 'プレディ')
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')
(3, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')
(1, 'シンディ', 'プレディ')

4.
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')
(3, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')
(1, 'シンディ', 'プレディ')

$

うまくいった。serial型のidに1を割り当てた後どうなるかも確認してみる。

コード

Python 3

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

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


def p(table='your_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)
    print()


_sql = '''drop table if exists your_table'''
cursor.execute(_sql)
conn.commit()

print('1.')
_sql = '''
create table your_table (
id serial primary key,
first_name text,
last_name  text
)
'''
print(_sql)

print('2.')
cursor.execute(_sql)
conn.commit()

print('3.')
_sql = '''
insert into your_table values (%s, %s, %s)
'''

values_lisst = [(None, 'マルシア', 'ブレイディ'),
                # エラー(シリアルがNone(Null))
                (2, 'ジャン', 'ブレイディ'),
                (3, 'ボビー', 'ブレイディ'),
                (99, 'ピーター', 'ブレイディ')]

for values in values_lisst:
    print(values)
    try:
        cursor.execute(_sql, values)
    except Exception as err:
        print(type(err), err)
    finally:
        conn.commit()
        p()

_sql = '''
insert into your_table (first_name, last_name) values (%s, %s)
'''
values = ('シンディ', 'プレディ')
for _ in range(2):
    try:
        print(_sql, values)
        cursor.execute(_sql, values)
    except Exception as err:
        print(err)
    finally:
        conn.commit()
        p()

print('4.')
p()

cursor.close()
conn.close()

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

$ ./sample2.py
1.

create table your_table (
id serial primary key,
first_name text,
last_name  text
)

2.
3.
(None, 'マルシア', 'ブレイディ')
<class 'psycopg2.IntegrityError'> null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, マルシア, ブレイディ).

id, first_name, last_name

(2, 'ジャン', 'ブレイディ')
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')

(3, 'ボビー', 'ブレイディ')
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')
(3, 'ボビー', 'ブレイディ')

(99, 'ピーター', 'ブレイディ')
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')
(3, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')


insert into your_table (first_name, last_name) values (%s, %s)
 ('シンディ', 'プレディ')
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')
(3, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')
(1, 'シンディ', 'プレディ')


insert into your_table (first_name, last_name) values (%s, %s)
 ('シンディ', 'プレディ')
duplicate key value violates unique constraint "your_table_pkey"
DETAIL:  Key (id)=(2) already exists.

id, first_name, last_name
(2, 'ジャン', 'ブレイディ')
(3, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')
(1, 'シンディ', 'プレディ')

4.
id, first_name, last_name
(2, 'ジャン', 'ブレイディ')
(3, 'ボビー', 'ブレイディ')
(99, 'ピーター', 'ブレイディ')
(1, 'シンディ', 'プレディ')

$

serialは自動では1の次に2と順番に割り当てていくみたいで、既にテーブルに存在する番号の確認は行わないみたい。(もしかしたら何か制御する方法はあるのかも)

ということで、(制御する方法がないなら)serial型は全て自動で割り当てるようにした方が良さそう。

Kamimura's blog

2019年1月8日火曜日

PostgreSQL - Python - 賢いテーブル設計 - 正規化の理由(primary key、serial型(数値)、連番)

0 コメント:

コメントを投稿