スッキリわかるSQL入門第2版のデータ構築

参考書籍
スッキリわかるSQL入門第2版
中山清喬 (著)
飯田理恵子 (著)
株式会社フレアリンク (監修)
ISBN-13:‎978-4295005094

スッキリわかるSQL入門第2版のデータ構築を
Python/sqlite3環境下でしたので残しておく

make家計簿.py

import sqlite3

conn = sqlite3.connect("test.db", isolation_level=None)

#テーブル作成
sql = """
    CREATE TABLE IF NOT EXISTS 家計簿 (
        日付 TEXT,
        費目 VARCHAR(20),
        メモ VARCHAR(100),
        入金額 INTEGER,
        出金額 INTEGER
    );
    """
conn.execute(sql)


#データ挿入
sql = "INSERT INTO 家計簿 VALUES (?,?,?,?,?)"
data = [
        ("2018-02-03","食費","コーヒーを購入",0,380),
        ("2018-02-10","給料","1月の給料",28000,0),
        ("2018-02-11","教育娯楽費","書籍を購入",0,2800),
        ("2018-02-14","交際費","同期会の回避",0,5000),
        ("2018-02-18","水道光熱費","1月の電気代",0,7560)
        ]
conn.executemany(sql, data)


#データ確認
c=conn.cursor()
c.execute("SELECT * FROM 家計簿")

for row in c:
    print(row)
    

conn.close()

make家計簿アーカイブ.py

import sqlite3

conn = sqlite3.connect("test.db", isolation_level=None)

#テーブル作成
sql = """
    CREATE TABLE IF NOT EXISTS 家計簿アーカイブ (
        日付 TEXT,
        費目 VARCHAR(20),
        メモ VARCHAR(100),
        入金額 INTEGER,
        出金額 INTEGER
    );
    """
conn.execute(sql)


#データ挿入
sql = "INSERT INTO 家計簿アーカイブ VALUES (?,?,?,?,?)"
data = [
        ("2017-12-10","給料","11月の給料",280000,0),
        ("2017-12-18","水道光熱費","水道代",0,4200),
        ("2017-12-24","食費","レストランみやび",0,5000),
        ("2017-12-25","居住費","1月の家賃支払い",0,80000),
        ("2018-01-10","給料","12月の給料",280000,0),
        ("2018-01-13","教育娯楽費","スッキリシネマズ",0,1800),
        ("2018-01-13","食費","新年会",0,5000),
        ("2018-01-25","居住費","2月の家賃支払い",0,80000),
        ]
conn.executemany(sql, data)


#データ確認
c=conn.cursor()
c.execute("SELECT * FROM 家計簿アーカイブ")

for row in c:
    print(row)


conn.close()

make家計簿集計.py

import sqlite3

conn = sqlite3.connect("test.db", isolation_level=None)

#テーブル作成
sql = """
    CREATE TABLE IF NOT EXISTS 家計簿集計 (
        費目 VARCHAR(20),
        合計 INTEGER,
        平均 INTEGER,
        最大 INTEGER,
        最小 INTEGER,
        回数 INTEGER
    );
    """
conn.execute(sql)

#データ挿入
##出金データ
sql = """
    INSERT INTO 家計簿集計 
        SELECT 費目,SUM(出金額),AVG(出金額),MAX(出金額),MIN(出金額),COUNT(出金額)
        FROM (
            SELECT * FROM 家計簿
            UNION
            SELECT * FROM 家計簿アーカイブ
            )
        GROUP BY 費目
        HAVING SUM(出金額) > 0
    """
conn.execute(sql)

##入金データ
sql = """
    INSERT INTO 家計簿集計 
        SELECT 費目,SUM(入金額),AVG(入金額),MAX(入金額),MIN(入金額),COUNT(入金額)
        FROM (
            SELECT * FROM 家計簿
            UNION
            SELECT * FROM 家計簿アーカイブ
            )
        GROUP BY 費目
        HAVING SUM(入金額) > 0
    """
conn.execute(sql)

#データ確認
c=conn.cursor()
c.execute("SELECT * FROM 家計簿集計")

for row in c:
    print(row)

conn.close()

• 家計簿集計は家計簿と家計簿アーカイブを合わせたものだと思っていたが、微妙にデータが違うので修正する(家計簿には交際費の費目があるが家計簿集計にはないなど)

modify家計簿集計.py

import sqlite3

conn = sqlite3.connect("test.db", isolation_level=None)

#データ修正1
sql = """
    DELETE 
    FROM 家計簿集計 
    WHERE 費目 = '交際費'
    """
conn.execute(sql)

#データ修正2
sql = """
    UPDATE 家計簿集計 
    SET 回数=3
    WHERE 費目 = '居住費'
    """
conn.execute(sql)

#データ確認
c=conn.cursor()
c.execute("SELECT * FROM 家計簿集計")

for row in c:
    print(row)

conn.close()