データベースの実装【python tkinter sqlite3で家計簿を作る】

③ データベースの実装【python tkinter sqlite3で家計簿を作る】

今回は、sqlite3のデータベース部分を実装をする。

スキーマ設計

今回のアプリのデータベースとして２つのテーブルを設計する。
f:id:memopy:20170601211307p:plain

データベースの接続

pythonからSqlite3を使用するには、モジュールをインポートして、データベースに接続するだけで使える。
2017/5/31追記：外部キー制約のオプションはデフォルトで無効になっているため、データベース接続後に"PRAGMA foreign_keys = 1"を発行しなければならない。
2017/6/11追記：sqlite3には、DATE（日付）型というものは存在しない。DATE型で宣言してもTEXT型に変換される。また、日付はYYYY-MM-DD HH:MM:SSを始めとしたいくつかの形式しかサポートされない。
このサイトに整理されていたので参考にした。
SQLiteのデータ型 | SQLite

# -*- coding: utf-8 -*-
# filename: database.py

import sqlite3

# 空のデータベースを作成して接続する
dbname = "database.db"
c = sqlite3.connect(dbname)
# 外部キー制約のオプションは、デフォルトでは無効になっているため、これを有効にする
c.execute("PRAGMA foreign_keys = 1")

SQLの発行

SQLを発行するには、executeメソッドを使用して、SQLを発行する。
SQLは予め変数に格納しておくと、見やすく記載できる。

# itemテーブルの定義
ddl = """
CREATE TABLE item
(
   item_code INTEGER PRIMARY KEY,
   item_name TEXT NOT NULL UNIQUE
);
 """
# SQLの発行
c.execute(ddl)

# acc_dataテーブルの定義    
ddl = """
CREATE TABLE acc_data
( 
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    acc_date DATE NOT NULL,
    item_code INTEGER NOT NULL,
    amount INTEGER,
    FOREIGN KEY(item_code) REFERENCES item(item_code)
);
"""
#sqlの発行
c.execute(ddl)

データベースのテスト

上記のデータベースに情報が登録できるかテストしてみる。

# -*- coding: utf-8 -*-
# filename: database_test.py

import sqlite3

# 空のデータベースを作成して接続する
dbname = "database.db"
c = sqlite3.connect(dbname)
# 外部キー制約の有効化
c.execute("PRAGMA foreign_keys = 1")

# itemテーブルの定義
ddl = """
CREATE TABLE item 
(
    item_code INTEGER PRIMARY KEY,
    item_name TEXT NOT NULL UNIQUE
);
"""
# SQLの発行
c.execute(ddl)

# acc_dataテーブルの定義    
ddl = """
CREATE TABLE acc_data
( 
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   acc_date DATE NOT NULL,
   item_code INTEGER NOT NULL,
   amount INTEGER,
   FOREIGN KEY(item_code) REFERENCES item(item_code)
);
"""
#sqlの発行
c.execute(ddl)

# itemテーブル登録のテスト
c.execute("INSERT INTO item VALUES(1,'食費');")
c.execute("INSERT INTO item VALUES(2,'住宅費');")
c.execute("INSERT INTO item VALUES(3,'光熱費');")
c.execute("COMMIT;")

# acc_dateテーブル登録のテスト
c.execute("""
    INSERT INTO acc_data(acc_date,item_code,amount)
    VALUES('2017-3-1',1,1000);
""")
c.execute("COMMIT;")

# acc_dateテーブル登録のテスト（次は変数を使った登録）
date = "'{}-{}-{}'".format(2017,3,5)
code = 2
amount = 2000

c.execute("""
    INSERT INTO acc_data(acc_date,item_code,amount)
    VALUES({},{},{});""".format(date,code,amount)
    )
c.execute("COMMIT;")

# 最後に登録されているデータの表示して確認する。
# itemテーブルの表示
result = c.execute("SELECT * FROM item;")
for row in result:
    print(row)
# 結果として３レコード登録されているのが確認できた。
# (1, '食費')
# (2, '住宅費')
# (3, '光熱費')

# acc_dataテーブルの表示
result = c.execute("SELECT * FROM acc_data;")
for row in result:
    print(row)
# 結果として２レコード登録されているのが確認できた。
# (1, '2017-3-1', 1, 1000)
# (2, '2017-3-5', 2, 2000)

# acc_dataテーブルとitemテーブルを結合して表示する
result = c.execute("""
    SELECT a.acc_date, i.item_name, a.amount
     FROM acc_data as a,item as i
      WHERE a.item_code = i.item_code;
)"""
for row in result:
   print(row)
# ２つのテーブルが結合して表示されているのが確認できた。
# ('2017-3-1', '食費', 1000)
# ('2017-3-5', '住宅費', 2000)

python2でsqlite3を使う場合（python2とpython3の違い）

2017.6.8追記：
sqlite3は、python2とpython3で振る舞いが少し異なる。代表的な部分を次に記述する。

ユニコード文字列

変数またはプレースフォルダを用いて文字列を登録する場合、ユニコード文字列で渡さなければならない。

# acc_dateテーブル登録のテスト（次は変数を使った登録）
date = "'{}-{}-{}'".format(2017,3,5)
code = 2
amount = 2000

c.execute("""
    INSERT INTO acc_data(acc_date.decode("utf-8"),item_code,amount)
    VALUES({},{},{});""".format(date,code,amount)
    )