SQL - 制約、ビュー、トランザクション: 料理人が多すぎると、データベースがダメになる(BEGIN TRANSACTION, COMMIT, ROLLBACK)

Head First SQL
頭とからだで覚えるSQLの基本
(オライリージャパン)
Lynn Beighley (著)
佐藤 直生 (監訳), 松永 多苗子 (翻訳)

開発環境

• OS X Mavericks - Apple、たまにFreeBSD 10(OS)
• Emacs (CUI)、BBEdit - Bare Bones Software, Inc. (GUI) (Text Editor)
• Python 3.4(プログラミング言語)
• SQLite (RDBMS(Relational Database Management System))

Head First SQL ―頭とからだで覚えるSQLの基本 (Lynn Beighley(著)、 佐藤 直生 (監訳)、 松永 多苗子 (翻訳)、オライリージャパン)の11章(制約、ビュー、トランザクション: 料理人が多すぎると、データベースがダメになる)、自分で考えてみよう(p.488)を解いてみる。

自分で考えてみよう(p.488)

piggy_bank

id	coin	coin_year
1	Q	1950
2	P	1972
3	N	2005
4	Q	1999

piggy_bank

id	coin	coin_year
1	Q	1950
2	P	1972
3	N	2005
4	Q	1999

piggy_bank

id	coin	coin_year
1	Q	1950
2	P	1972
3	N	2005
4	Q	1999

piggy_bank

id	coin	coin_year
1	Q	1950
2	P	1972
3	N	2005
4	Q	1999

piggy_bank

id	coin	coin_year
1	Q	1950
2	P	1972
3	N	2005
4	D	1999

piggy_bank

id	coin	coin_year
1	Q	1950
2	P	1972
3	P	2005
4	D	1999

SQLiteでは、START TRANSACTIONではなく、BEGIN TRANSACTION。

SQL文(BBEdit, Emacs)

-- Loading resources from /Users/kamimura/.sqliterc

SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite> DELETE FROM piggy_bank;
DELETE FROM piggy_bank;
sqlite> INSERT INTO piggy_bank VALUES
(1, 'Q', 1950),
(2, 'P', 1972),
(3, 'N', 2005),
(4, 'Q', 1999);
INSERT INTO piggy_bank VALUES
(1, 'Q', 1950),
(2, 'P', 1972),
(3, 'N', 2005),
(4, 'Q', 1999);
   ...> sqlite> 

sqlite> BEGIN TRANSACTION;
BEGIN TRANSACTION;
sqlite> UPDATE piggy_bank
SET coin = 'Q'
WHERE coin ='P'
AND coin_year < 1970;
UPDATE piggy_bank
SET coin = 'Q'
WHERE coin ='P'
AND coin_year < 1970;
   ...> sqlite> 

sqlite> COMMIT;
COMMIT;
sqlite> SELECT * FROM piggy_bank;
SELECT * FROM piggy_bank;
id          coin        coin_year 
----------  ----------  ----------
1           Q           1950      
2           P           1972      
3           N           2005      
4           Q           1999      
sqlite> BEGIN TRANSACTION;
BEGIN TRANSACTION;
sqlite> UPDATE piggy_bank
SET coin = 'N'
WHERE coin = 'Q';
UPDATE piggy_bank
SET coin = 'N'
WHERE coin = 'Q';
   ...> sqlite> 

sqlite> ROLLBACK;
ROLLBACK;
sqlite> SELECT * FROM piggy_bank;
SELECT * FROM piggy_bank;
id          coin        coin_year 
----------  ----------  ----------
1           Q           1950      
2           P           1972      
3           N           2005      
4           Q           1999      
sqlite> BEGIN TRANSACTION;
BEGIN TRANSACTION;
sqlite> UPDATE piggy_bank
SET coin = 'Q'
WHERE coin = 'N'
AND coin_year > 1950

UPDATE piggy_bank
SET coin = 'Q'
WHERE coin = 'N'
AND coin_year > 1950

   ...> ;
;
sqlite> ROLLBACK;
ROLLBACK;
sqlite> SELECT * FROM piggy_bank;
SELECT * FROM piggy_bank;
id          coin        coin_year 
----------  ----------  ----------
1           Q           1950      
2           P           1972      
3           N           2005      
4           Q           1999      
sqlite> BEGIN TRANSACTION;
BEGIN TRANSACTION;
sqlite> UPDATE piggy_bank
SET coin = 'D'
WHERE coin = 'Q'
AND coin_year > 1980;
UPDATE piggy_bank
SET coin = 'D'
WHERE coin = 'Q'
AND coin_year > 1980;
   ...> sqlite> 

sqlite> COMMIT;
COMMIT;
sqlite> SELECT * FROM piggy_bank;
SELECT * FROM piggy_bank;
id          coin        coin_year 
----------  ----------  ----------
1           Q           1950      
2           P           1972      
3           N           2005      
4           D           1999      
sqlite> BEGIN TRANSACTION;
BEGIN TRANSACTION;
sqlite> UPDATE piggy_bank
SET coin = 'P'
WHERE coin = 'N'
AND coin_year > 1970;
UPDATE piggy_bank
SET coin = 'P'
WHERE coin = 'N'
AND coin_year > 1970;
   ...> sqlite> 

sqlite> COMMIT;
COMMIT;
sqlite> SELECT * FROM piggy_bank;
SELECT * FROM piggy_bank;
id          coin        coin_year 
----------  ----------  ----------
1           Q           1950      
2           P           1972      
3           P           2005      
4           D           1999      
sqlite> .quit
.quit

コード(BBEdit, Emacs)

sample473.py

#!/usr/bin/env python3
#-*- coding: utf-8 -*-

import sqlite3

connection = sqlite3.connect('chapter11.sqlite')
cursor = connection.cursor()

table = 'piggy_bank'

def p_all(table):
    print(table)
    cursor.execute("""SELECT * FROM {0}""".format(table))
    print(tuple(map(lambda header: header[0], cursor.description)))
    for row in cursor.fetchall():
        print(row)

cursor.execute("""
DELETE FROM piggy_bank
""")

p_all(table)

cursor.execute("""
INSERT INTO piggy_bank VALUES
(1, 'Q', 1950),
(2, 'P', 1972),
(3, 'N', 2005),
(4, 'Q', 1999)
""")
p_all(table)

cursor.execute('''
UPDATE piggy_bank
SET coin = 'Q'
WHERE coin ='P'
AND coin_year < 1970
''')
connection.commit()
p_all(table)

# cursor.execute('BEGIN TRANSACTION')
cursor.execute('''
UPDATE piggy_bank
SET coin = 'N'
WHERE coin = 'Q'
''')
connection.rollback()

p_all(table)

cursor.execute('''
UPDATE piggy_bank
SET coin = 'Q'
WHERE coin = 'N'
AND coin_year > 1950
''')
connection.rollback()
p_all(table)

cursor.execute('''
UPDATE piggy_bank
SET coin = 'D'
WHERE coin = 'Q'
AND coin_year > 1980
''')
connection.commit()
p_all(table)

cursor.execute('''
UPDATE piggy_bank
SET coin = 'P'
WHERE coin = 'N'
AND coin_year > 1970
''')
connection.commit()
p_all(table)

connection.commit()
connection.close()

入出力結果(Terminal, IPython)

$ ./sample488.py
piggy_bank
('id', 'coin', 'coin_year')
piggy_bank
('id', 'coin', 'coin_year')
(1, 'Q', '1950')
(2, 'P', '1972')
(3, 'N', '2005')
(4, 'Q', '1999')
piggy_bank
('id', 'coin', 'coin_year')
(1, 'Q', '1950')
(2, 'P', '1972')
(3, 'N', '2005')
(4, 'Q', '1999')
piggy_bank
('id', 'coin', 'coin_year')
(1, 'Q', '1950')
(2, 'P', '1972')
(3, 'N', '2005')
(4, 'Q', '1999')
piggy_bank
('id', 'coin', 'coin_year')
(1, 'Q', '1950')
(2, 'P', '1972')
(3, 'N', '2005')
(4, 'Q', '1999')
piggy_bank
('id', 'coin', 'coin_year')
(1, 'Q', '1950')
(2, 'P', '1972')
(3, 'N', '2005')
(4, 'D', '1999')
piggy_bank
('id', 'coin', 'coin_year')
(1, 'Q', '1950')
(2, 'P', '1972')
(3, 'P', '2005')
(4, 'D', '1999')
$