Pythonで家計簿アプリを作る その3、データベース設計と実装

今回はデータベースに設計と実装について。家計簿アプリは家計簿データを中心に据えたプログラムであるので、これにはそれなりに時間を割きました。

DB設計方針

前回紹介したPrimary Key、データの型、制約、Foreign Keyに注意しつつ、家計簿アプリのDBデザインをしようと思います。迷った点や方針などを以下に記します。

• 金額データは外貨の使用も想定してInt型ではなくReal型で保管。ただし円のみを使う当面の間、Python側で小数点以下がないことを確認してからDBに渡す
• 金額データのマイナスを許容する。収入は正の、支出は負の金額として保管することでDB側で特殊な場合分けが不要になる。（ユーザ側でプラマイを入力するのは面倒なので、カテゴリによってPythonが自動的に正負を判断する）
• 収入・支出を分けるフィールドをDBに作るか、Python側がカテゴリフィールドから自動で場合分けするか迷ったが、DBのシンプルさを優先して後者をとった
• 収入支出と資産（フローとストック）を同一のDBに保管する必要は今のところないが、将来的につなげる可能性もなくもないので同一のDBに保管する
• 詳細フィールドにはカテゴリよりも詳細な記述をするためのもので、必須でもよかったが、SQLite側では入力制限無しとした
• タグフィールドは今のところ何に使うかわからないが、将来の分析機能が拡充した時に使うかもしれないので実装。使用想定:”#credit_card #electricity #auto-generated”等カテゴリではカバーできないデータをタグとしてテキスト化し、後に分析用の関数などが検索できるようにする

以上、何個か迷った点は大体プログラムの複雑化←→DBの複雑化のトレードオフ関係にあったので全て前者の方を選びました。

DB設計

前述の方針をもとに家計簿データを以下のように定義しました。

収入支出データ

資産データ

ここで（Autogenerated）とかいてある行は前回紹介した通り、SQLiteが自動で付け足してくれるのでこちらから定義する必要はありませんでした。収入と支出はDB側では分けないとの方針だったのですが、簡単のためにカテゴリテーブルのIDを1,2,3..を支出、101,102,103...を収入に当てて区分しました。今から考えるとIDにそんなカラクリを使うよりはカテゴリテーブルに新しく収入/出費フィールドを設けた方が他の人のReadabilityが向上しますね。

コードの実装

では実際にこのDBを作成、書き込み、読み出しする関数を書いていきます。収入支出と資産のデータ構造は一緒なので収入支出テーブルのみ表示します。まずはconnectionオブジェクトを生成します。

 c = sqlite3.connect('test.db')
c.execute('PRAGMA foreign_keys = 1;')

ここで前回紹介したForeign Key制約を有効化するためにpragmaコマンドを使います。Connectionオブジェクト毎に発行しなければいけないので注意しましょう。僕は忘れていました。

DB定義

次に先ほどの設計通りにテーブルを作成します。

# flow_cateogry table
sql = '''
CREATE TABLE flow_category
(
	category_code INTEGER PRIMARY KEY,
	category_name TEXT NOT NULL UNIQUE
);
'''
c.execute(sql)

# flow_data table
sql = '''
CREATE TABLE flow_data
( 
	date DATE NOT NULL,
	amount REAL,
	category_code INTEGER NOT NULL,
	detail TEXT,
	tags TEXT,
	FOREIGN KEY(category_code) REFERENCES flow_category(category_code)
);
'''
c.execute(sql)

SQLステートメントをstringとして保管し、これをc.execute()に渡すことで実行します。どうやらexecuteは一つの文（セミコロン一個分）づつしか実行できないみたいで、複数文をまとめて実行するためにはexecutescript()という関数があるみたいです。あとexecute()を実行してもdml（INSERTやDELETEなど）であれば仮書き込み状態となることに注意してください。本書き込みをするにはc.commit()でコミットすることが必要となります。今回はddl(CREATEなど)なのでどちらにせよauto-commitされるようです。

DB書き込み

テーブルの作成ができたので、カテゴリテーブルにカテゴリを書き込んでいきます。

flow_category_dict = {# 1,2-digits id reserved for expense
	1:'家賃関連',
	2:'食費',
	3:'日用品',
	# 3-digits id reserved for revenue
	101:'給料',
	102:'運用利益'}
	
# register flow_category 
for idx in flow_category_dict:
	sql = f''' 
	INSERT INTO flow_category VALUES({idx},'{flow_category_dict[idx]}');
	'''
	c.execute(sql)
c.commit()

ここまでは初期設定なのでUIを作る必要もなく、このままソースコードに実装しました。次に家計簿データの書き込みです。

date = '\'2022-02-01\''
amount = 0
category = 2
detail = 'ダミー'
tag = '#test'
sql = f'''
INSERT INTO flow_data VALUES({date},{amount},{category},'{detail}','{tag}');
'''
c.execute(sql)    

date = '2022-02-04'
amount = 0
category = 101
detail = 'ダミー2'
tag = '#test'
sql = f'''
INSERT INTO flow_data VALUES('{date}',{amount},{category},'{detail}','{tag}');
'''
c.execute(sql)
c.commit()

ここで若干の注意ですが、本来得るべきSQL構文は以下のようなもので、

sql='''
INSERT INTO flow_data VALUES('2022-02-04',0,2,'ダミー','#test');
'''

SQL内でstring型となるものには''が付いています。しかしsqlという変数はそれ自身がpythonのstr型なので、単にstr型を代入すると''が失われてしまいます。

var = 'apple'
f'this is an {var}'
>>'this is an apple'

そこで''記号を変数の方に埋め込めます。単に二重に入れるとstringをエスケープしてしまうのでバックスラッシュでリテラルであることを明示し、

var = '\'apple\''
f'this is an {var}'
>>"this is an 'apple'"

とする解決策と、もう一つは入れ込む方のstringに既に''をつけちゃう方法があります

var = 'apple
f"this is an '{var}'"
>>"this is an 'apple'"

pythonはstringを表す記号が""と''の二つあるからこそですね。いづれにせよ直接SQLのステートメントをstring formattingするべきでないと公式に書いています。それは以下のような事例を防ぐためでもあると。

一応訳しますと、

(学校)こんにちは、XX君の小学校ですがコンピュータ関連のトラブルがありまして…

(親)あらら、うちの息子が何か壊しました？

(学校)ある意味そうなのですが…本当に息子をRobert') DROP TABLE Students; --と名付けたのですか？

(親)あぁはいそうです、ボビーテーブルちゃんって呼んでいるんですよ。

(学校)どちらにせよ今年度の全生徒データを失いました、せめて貴方が楽しんだことを願いますよ。

(親)そして私は貴方がデータベース入力をしっかりフィルターするべきと学んだことを願います。

なかなキレのある面白い4コマですね、公式docに貼り付けてあるだけあります。

ではどうやるべきなのかについても公式は丁寧に書いてくれていますが、そもそもこれに気付いたのがこの記事を執筆している時だったので、その説明と自分のコードの改修はまたいつかやります。

DB読み出し

最後にDBにしっかり書き込めたのかの確認をします。SQL文がSELECTだった場合execute()関数はクエリ結果をイテレータとして返してくれるようです。このように1行づつFor文で取り出せます。

for row in c.execute('SELECT * FROM flow_data'):
	print(row)
>>('2022-02-01', 0.0, 2, 'ダミー', '#test')
>>('2022-02-04', 0.0, 101, 'ダミー2', '#test')

また”0”とint型っぽく渡した金額はちゃんと0.0とreal型で保管されていることも確認できます。実際のコードの方ではクエリ結果の取り出しはfor文ではなくpandasのread_sql_query()関数で行うのですが、それについてはまた後ほど。

全部合わせて

ここまでのDB作成、書き込み、読み出しまでをまとめたコードがこちらです。

from os import path
import sqlite3

def define_db(c):
	flow_category_dict = {# 1,2-digits id reserved for expense
		1:'家賃関連',
		2:'食費',
		3:'日用品',
		# 3-digits id reserved for revenue
		101:'給料',
		102:'運用利益'}

	# flow_cateogry table
	sql = '''
	CREATE TABLE flow_category
	(
		category_code INTEGER PRIMARY KEY,
		category_name TEXT NOT NULL UNIQUE
	);
	'''
	c.execute(sql)

	# flow_data table
	sql = '''
	CREATE TABLE flow_data
	( 
		date DATE NOT NULL,
		amount REAL,
		category_code INTEGER NOT NULL,
		detail TEXT,
		tags TEXT,
		FOREIGN KEY(category_code) REFERENCES flow_category(category_code)
	);
	'''
	c.execute(sql)
	# register flow_category 
	for idx in flow_category_dict:
		sql = f''' 
		INSERT INTO flow_category VALUES({idx},'{flow_category_dict[idx]}');
		'''
		c.execute(sql)
	c.commit()


def write_db(c):
	date = '\'2022-02-01\''
	amount = 0
	category = 2
	detail = 'ダミー'
	tag = '#test'
	sql = f'''
	INSERT INTO flow_data VALUES({date},{amount},{category},'{detail}','{tag}');
	'''
	c.execute(sql)    

	date = '2022-02-04'
	amount = 0
	category = 101
	detail = 'ダミー2'
	tag = '#test'
	sql = f'''
	INSERT INTO flow_data VALUES('{date}',{amount},{category},'{detail}','{tag}');
	'''
	c.execute(sql)
	c.commit()

def read_db(c):
	for row in c.execute('SELECT * FROM flow_data'):
		print(row)

if __name__=='__main__':
	assert not path.exists('test.db') # ensure .db is a new file each run
	c = sqlite3.connect('test.db')
	c.execute('PRAGMA foreign_keys = 1;')
	define_db(c)
	write_db(c)
	read_db(c)
	c.close()

今回はデータベース設計方針と設計、そしてテストコードへの実装を紹介しました。次回は何を書くか未定です、Pandasの使用法な気がします。

次回：TBD

前回：Pythonで家計簿アプリを作る その3の脇道、SQLとデータベースの勉強 - Thought Log