Flask入門｜内部結合と外部結合を理解しよう【チャプター5-05】

import os
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# ====================================
# データベースのパス（保存場所）を設定
# ====================================
base_dir = os.path.dirname(__file__) # 現在のファイルがあるディレクトリのパスを取得して、変数base_dirに代入
database = 'sqlite:///' + os.path.join(base_dir, 'data.sqlite')
    # base_dir内のdata.sqliteというファイルへのパスを作成し、
    # SQLite用の接続文字列（URL形式）を追加して変数databaseに代入
## データベースエンジンを作成
db_engine = create_engine(database, echo=True) # create_engine関数を使って、DB接続用のエンジンを作成
Base = declarative_base() # SQLAlchemyに対応した新しい“親クラス”を生成し、変数Baseに代入
    # declarative_baseは「クラスを返す」という特殊な関数

# ====================================
# モデル（テーブルを作成するクラス）
# ====================================
## 商品書籍テーブル
class Book(Base): # Baseクラスを継承したBookクラスの定義
    # テーブル名
    __tablename__ = 'books'
    # 書籍ID
    book_id = Column(Integer, primary_key=True)
    # 書籍名
    book_name = Column(String(255), nullable=False, unique=True)
    # 著者名
    author = Column(String(255), nullable=False)

## 図書館テーブル
class Library(Base): # Baseクラスを継承したLibraryクラスの定義
    # テーブル名
    __tablename__ = 'libraries'
    # 図書館ID
    library_id = Column(Integer, nullable=False, primary_key=True)
    # 図書館名
    library_name = Column(String(255), nullable=False, unique=True)

## 蔵書冊数テーブル
class Stock(Base): # Baseクラスを継承したStockクラスの定義
    # テーブル名
    __tablename__ = 'stocks'
    # 図書館id
    library_id = Column(Integer, primary_key=True)
    # 書籍ID
    book_id = Column(Integer, primary_key=True)
    # 蔵書冊数
    stock = Column(Integer)

# ====================================
# テーブル操作
# ====================================
print('テーブルを削除してから作成')
Base.metadata.drop_all(db_engine)   # データベースの削除（初期化）
Base.metadata.create_all(db_engine) # データベースの作成 ＆ テーブルの作成

## セッションの生成
session_maker = sessionmaker(bind=db_engine) # sessionmakerクラスからセッションファクトリを作成し、変数session_makerに代入
session = session_maker() # session_makerをインスタンス生成し、変数sessionに代入

## データ作成
print('データ登録：実行')
# 書籍データ
book01 = Book(book_id=1, book_name='吾輩は猫である', author='夏目漱石') # Bookクラス（モデル）をインスタンス生成し、book01に代入
book02 = Book(book_id=2, book_name='走れメロス', author='太宰治')
book03 = Book(book_id=3, book_name='銀河鉄道の夜', author='宮沢賢治')
book04 = Book(book_id=4, book_name='人間失格', author='太宰治')
session.add_all([book01, book02, book03, book04]) # .add_allメソッドにて、リストをテーブルに一括登録
session.commit() # 上の操作をDBに書き込み・保存
# 図書館データ
library01 = Library(library_id=1, library_name='第１図書館')
library02 = Library(library_id=2, library_name='第２図書館')
session.add_all([library01, library02])
session.commit()
# 蔵書冊数データ
stock01 = Stock(library_id=1, book_id=1, stock=1)
stock02 = Stock(library_id=1, book_id=2, stock=2)
stock03 = Stock(library_id=1, book_id=3, stock=0)
stock04 = Stock(library_id=2, book_id=1, stock=0)
stock05 = Stock(library_id=2, book_id=2, stock=1)
stock06 = Stock(library_id=2, book_id=3, stock=1)
session.add_all([stock01, stock02, stock03, stock04, stock05, stock06])
session.commit()

## 内部結合
# ここに内部結合のコード例を追記

## 外部結合
# ここに外部結合のコード例を追記

## 練習問題
# ここに練習問題の解答を追記

session.query(テーブルA, テーブルB.カラム名).join(テーブルB, 結合条件).all()

print('データ参照：実行')
print('■：内部結合')
join_3tables_all = session.query( # ORMによるクエリの構築 ⇒ 変数join_3tables_allに代入
    Library,        # 図書館テーブルの全カラム（カラム＝列）を取得
    Book.book_name, # 書籍テーブルのbook_nameカラムを取得
    Stock.stock     # 蔵書冊数テーブルのstockカラムを取得
).join(Stock, Library.library_id == Stock.library_id # Library と Stock を library_id で内部結合
).join(Book, Book.book_id == Stock.book_id           # Stock と Book を book_id で内部結合
).all() # クエリを実行し、すべての結果をリストとして取得

for row in join_3tables_all:
    print(f"図書館：{row.Library.library_name} -> 書籍名：{row.book_name} -> 蔵書冊数：{row.stock}")

session.query(テーブルA, テーブルB.カラム名).outerjoin(テーブルB, 結合条件).all()

## リストの追加
print('■' * 20)
print('■：外部結合')
outerjoin_2tables_all = session.query( # ORMによるクエリの構築 ⇒ 変数outerjoin_2tables_allに代入
    Book,        # 書籍テーブルの全カラムを取得
    Stock.stock  # 蔵書冊数テーブルのstockカラムを取得
).outerjoin(Stock, Book.book_id == Stock.book_id # Bookを基点にStockを外部結合
).all() # クエリを実行し、すべての結果をリストとして取得

for row in outerjoin_2tables_all:
    print(f"書籍名：{row.Book.book_name} -> 在庫数：{row.stock}")

▼ 書籍テーブルの全データ
ID：1｜書籍名：吾輩は猫である｜著者：夏目漱石
ID：2｜書籍名：走れメロス｜著者：太宰治
ID：3｜書籍名：銀河鉄道の夜｜著者：宮沢賢治
ID：4｜書籍名：人間失格｜著者：太宰治

▼ 図書館テーブルの全データ
ID：1｜図書館名：第１図書館
ID：2｜図書館名：第２図書館

▼ 蔵書冊数テーブルの全データ
図書館ID：1｜書籍ID：1｜在庫数：1
図書館ID：1｜書籍ID：2｜在庫数：2
図書館ID：1｜書籍ID：3｜在庫数：0
図書館ID：2｜書籍ID：1｜在庫数：0
図書館ID：2｜書籍ID：2｜在庫数：1
図書館ID：2｜書籍ID：3｜在庫数：1