データベース ― バックエンドの心臓部

1. つないで、表を定義するdb1.py を作ります。

   import sqlite3
   
   con = sqlite3.connect("pan.db")
   cur = con.cursor()
   
   cur.execute("""
   CREATE TABLE IF NOT EXISTS items (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       name TEXT NOT NULL,
       price INTEGER NOT NULL
   )
   """)

   意味: 「itemsという表を作る。idは自動採番、nameは文字 (必須)、priceは整数 (必須)」。IF NOT EXISTS は「もうあれば作らない」の保険です。
2. 1行しまう

   cur.execute("INSERT INTO items (name, price) VALUES (?, ?)",
               ("メロンパン", 180))
   con.commit()

   ? は値の差し込み口 (プレースホルダ) です。値は必ず?で渡す ― 文字列の連結でSQLを組むと、悪意ある入力に乗っ取られる事故 (SQLインジェクション) の入り口になります。最初から正しい型で覚えましょう。
3. 取り出す

   for row in cur.execute("SELECT * FROM items"):
       print(row)
   con.close()

   実行すると (1, 'メロンパン', 180) が返ります。idの1は、データベースが自動でつけた番号です。
4. 2回実行してみるもう一度実行すると、メロンパンが2行になります (idは2)。「実行のたびに増えてよいのか?」― この違和感は正しいです。授業3で直します。

1. 引っ越しスクリプトを書くユニット2の知識 (csv.DictReader) で読み、1行ずつINSERTします。

   import csv, sqlite3
   
   con = sqlite3.connect("pan.db")
   cur = con.cursor()
   cur.execute("""
   CREATE TABLE IF NOT EXISTS sales (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       day TEXT, item TEXT, price INTEGER, count INTEGER, total INTEGER
   )
   """)
   with open("pan-uriage.csv", encoding="utf-8") as f:
       for row in csv.DictReader(f):
           cur.execute(
               "INSERT INTO sales (day, item, price, count, total) VALUES (?, ?, ?, ?, ?)",
               (row["日付"], row["商品名"], int(row["たんか"]),
                int(row["こすう"]), int(row["うりあげ"])))
   con.commit()

   CSV→データベースの引っ越しは、実務でもよくある仕事そのものです。
2. 絞り込む (WHERE)

   SELECT day, total FROM sales WHERE item = 'メロンパン';

3. 並べる (ORDER BY)

   SELECT day, item, total FROM sales ORDER BY total DESC LIMIT 5;

   「売上の大きい順に5件」。DESCが大きい順、LIMITが件数制限です。
4. 集計する (GROUP BY)

   SELECT item, SUM(total) FROM sales GROUP BY item;

   ユニット2で「数える辞書」で書いた商品別集計が、SQLなら1行です。手で書いた経験があるから、この1行の意味が読めるのです。

お題: salesテーブルに、SQLで質問します。①食パンの行だけ全部 ②売上3000円以上の行 ③5月10日の売上合計 ④商品別の販売個数合計 ⑤日別売上のベスト3 ⑥一番安い商品の名前 ⑦「パン」を名前に含む商品の行 (LIKEを調べます) ⑧行数の合計 (COUNT)。

1. 対象を先に見る

   SELECT * FROM items WHERE name = 'メロンパン';

   2行あることを確認します (授業1の手順4で増えた重複です)。
2. 重複を片づける (DELETE)

   DELETE FROM items WHERE id = 2;

   idを指定すれば1行だけ消せます。もしWHEREを忘れると全行消えます。怖さを知るために、消える前に pan.db をコピーしてバックアップを取り、練習用コピーの方でわざと DELETE FROM items; を体験しておきましょう。知識ではなく体験にしておくのが、事故防止の最強の薬です。
3. 値上げする (UPDATE)

   UPDATE items SET price = 200 WHERE name = 'メロンパン';

   打つ前のSELECT → UPDATE → もう一度SELECTで確認、の3点セットを必ず守ります。UPDATEもWHEREを忘れると全行が書きかわります。
4. Pythonからも同じcur.execute("UPDATE items SET price = ? WHERE id = ?", (200, 1)) のように、値は必ず ? で渡します。書き換え後の con.commit() を忘れると保存されません ― 「commitして初めて本当に書かれる」もここで体に入れます。

1. 情報を全部書き出す在庫管理に必要な情報: 商品名・価格・入庫した数と日付・出庫した数と日付。
2. 「もの」と「出来事」に分ける商品 (もの) は1回だけ登録するもの、入出庫 (出来事) は毎日増えるもの。性質が違うので表を分けます。

   products: id, name, price
   moves:    id, product_id, kind ('in'か'out'), count, day

3. つなぎ方を理解するmovesの product_id に、productsのidを入れます。「メロンパンが3個入庫」は (product_id=1, kind='in', count=3) になります。名前ではなく番号でつなぐから、商品名を変えても履歴は壊れません。
4. つないで読む (JOIN)

   SELECT products.name, moves.kind, moves.count
   FROM moves JOIN products ON moves.product_id = products.id;

   2つの表を貼り合わせて読むのがJOINです。まずこの1パターンだけ使えれば十分です。

お題: 事業所の本棚の貸し出し管理を設計します。必要な情報: 本の題名・借りた人・借りた日・返した日。テーブル構成を紙に書き、CREATE TABLEまで実行します。