はじめに
SQLAlchemyで2つのテーブルを結合させることはできるのかにゃ？
もちろんできるよ！
joinの使用方法について解説するよ！
Joinとは
Joinとは2つのテーブルを結合することを言います。
結合方法にはクロス結合（Cross Join）、内部結合（Inner Join）、外部結合（Outer Join）があります。
それぞれの結合方法をサンプルデータで確認して、SQLAlchemyでの記載方法を見ていきましょう。
前準備
まずはサンプルデータを準備します。
サンプルでは、商品の名前と価格を管理するShohinテーブルと、店ごとの商品在庫を管理するZaikoテーブルを用います。
from ntpath import join
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# エンジンオブジェクトの作成
eng = create_engine('sqlite:///Test.db')
# ベースクラスの作成
base = declarative_base()
# モデルクラスの作成
class Shohin(base):
__tablename__ = 'shohin_table'
shohin_no = Column(Integer, primary_key=True)
name = Column(String(255))
price = Column(Integer)
class Zaiko(base):
__tablename__ = 'zaiko_table'
shop_no = Column(Integer, primary_key=True)
shohin_no = Column(Integer, primary_key=True)
suryo = Column(Integer)
# Sessionの作成
mak = sessionmaker(bind=eng)
ses = mak()
# テーブル作成
base.metadata.create_all(eng)
# レコード作成
ses.add(Shohin(shohin_no = 1, name='アイス', price=280))
ses.add(Shohin(shohin_no = 2, name='ケーキ', price=350))
ses.add(Shohin(shohin_no = 3, name='パフェ', price=780))
ses.add(Shohin(shohin_no = 4, name='モンブラン', price=420))
ses.add(Zaiko(shop_no = 1, shohin_no = 1, suryo = 30))
ses.add(Zaiko(shop_no = 1, shohin_no = 2, suryo = 20))
ses.add(Zaiko(shop_no = 1, shohin_no = 3, suryo = 15))
ses.add(Zaiko(shop_no = 2, shohin_no = 1, suryo = 40))
ses.add(Zaiko(shop_no = 2, shohin_no = 2, suryo = 10))
ses.add(Zaiko(shop_no = 2, shohin_no = 3, suryo = 25))
# コミット
ses.commit()作成したサンプルを用いてそれぞれの結合方法を見ていきましょう。
内部結合(Inner Join)
内部結合はテーブルAに対してテーブルBを、関連する値を基準に結合します。
結合できないレコードは出力対象外となります。
クエリ文のサンプルでは下記のようになります。
select *
from shohin_table
inner join zaiko_table ON zaiko_table.shohin_no = shohin_table.shohin_no実行結果は下記となります。
ShohinテーブルにはShohin_no=4としてモンブランがありますが、ZaikoテーブルにはShohin_no=4のレコードが無いため、出力対象外となりました。
SQLAlchemyでは、query(…)の後にjoin(…)を記載することでテーブル同士を結合できます。
出力結果 = session.query(テーブル名, 出力するフィールド).join(結合するテーブル名, テーブル同士の関連付け)
サンプルでは下記のような記載となります。
ShohinクラスとZaikoクラスをshohin_noで関連付けしています。
datas = ses.query(Shohin, Shohin.shohin_no, Shohin.name, Zaiko.shop_no, Zaiko.suryo
).join(Zaiko, Zaiko.shohin_no == Shohin.shohin_no)
for data in datas:
print("店No：{} 商品No：{} 商品名：{} 在庫数：{}".format(data.shop_no, data.shohin_no, data.name, data.suryo))
# 実行結果
# 店No：1 商品No：1 商品名：アイス 在庫数：30
# 店No：2 商品No：1 商品名：アイス 在庫数：40
# 店No：1 商品No：2 商品名：ケーキ 在庫数：20
# 店No：2 商品No：2 商品名：ケーキ 在庫数：10
# 店No：1 商品No：3 商品名：パフェ 在庫数：15
# 店No：2 商品No：3 商品名：パフェ 在庫数：25外部結合(Outer Join)
外部結合はテーブルAが持つ関連する値が、テーブルBに存在すればそのレコード同士を結合します。
内部結合と異なる点は結合できないレコードはNULL表示になります。
まずはクエリ文のサンプルを見てみましょう。
先ほどのinner joinがleft joinに変わっただけですね。
select *
from shohin_table
left join zaiko_table ON zaiko_table.shohin_no = shohin_table.shohin_no実行結果は下記のようになります。
注目はモンブランです。ZaikoテーブルにはShohin_no=4に該当するレコードが無いためNULLで表示されています。
ではこれをSQLAlchemyで記載してみます。外部結合の場合isouter = Trueと指定します。
因みにFalseにすると内部結合になります。
出力結果 = session.query(テーブル名, 出力するフィールド).join(結合するテーブル名, テーブル同士の関連付け, isouter = True)
サンプルデータで見てみましょう。モンブランはZaikoテーブルが無いためNoneで表示されています。
datas = ses.query(Shohin, Shohin.shohin_no, Shohin.name, Zaiko.shop_no, Zaiko.suryo
).join(Zaiko, Zaiko.shohin_no == Shohin.shohin_no, isouter = True)
for data in datas:
print("店No：{} 商品No：{} 商品名：{} 在庫数：{}".format(data.shop_no, data.shohin_no, data.name, data.suryo))
# 実行結果
# 店No：1 商品No：1 商品名：アイス 在庫数：30
# 店No：2 商品No：1 商品名：アイス 在庫数：40
# 店No：1 商品No：2 商品名：ケーキ 在庫数：20
# 店No：2 商品No：2 商品名：ケーキ 在庫数：10
# 店No：1 商品No：3 商品名：パフェ 在庫数：15
# 店No：2 商品No：3 商品名：パフェ 在庫数：25
# 店No：None 商品No：4 商品名：モンブラン 在庫数：None
joinをouterjoinで記載する方法もあります。実行結果はisouter=Trueと同様の結果になります。
出力結果 = session.query(テーブル名, 出力するフィールド).outerjoin(結合するテーブル名, テーブル同士の関連付け)
クロス結合（Cross Join）
クロス結合は、テーブル同士の関連付けを行わず、テーブル同士の全ての組み合わせで結合します。
クエリ文では下記となります。
select *
from shohin_table, zaiko_table実行結果は下記です。
SQLAlchemyだと下記のような記載となります。
出力結果 = session.query(テーブル名A, テーブル名B, 出力するフィールド)
サンプルで見てみましょう。
datas = ses.query(Shohin, Zaiko, Shohin.shohin_no, Shohin.name, Zaiko.shop_no, Zaiko.suryo)
for data in datas:
print("店No：{} 商品No：{} 商品名：{} 在庫数：{}".format(data.shop_no, data.shohin_no, data.name, data.suryo))
# 実行結果
店No：1 商品No：1 商品名：アイス 在庫数：30
店No：1 商品No：1 商品名：アイス 在庫数：20
店No：1 商品No：1 商品名：アイス 在庫数：15
店No：2 商品No：1 商品名：アイス 在庫数：40
店No：2 商品No：1 商品名：アイス 在庫数：10
店No：2 商品No：1 商品名：アイス 在庫数：25
店No：1 商品No：2 商品名：ケーキ 在庫数：30
店No：1 商品No：2 商品名：ケーキ 在庫数：20
店No：1 商品No：2 商品名：ケーキ 在庫数：15
店No：2 商品No：2 商品名：ケーキ 在庫数：40
店No：2 商品No：2 商品名：ケーキ 在庫数：10
店No：2 商品No：2 商品名：ケーキ 在庫数：25
店No：1 商品No：3 商品名：パフェ 在庫数：30
店No：1 商品No：3 商品名：パフェ 在庫数：20
店No：1 商品No：3 商品名：パフェ 在庫数：15
店No：2 商品No：3 商品名：パフェ 在庫数：40
店No：2 商品No：3 商品名：パフェ 在庫数：10
店No：2 商品No：3 商品名：パフェ 在庫数：25
店No：1 商品No：4 商品名：モンブラン 在庫数：30
店No：1 商品No：4 商品名：モンブラン 在庫数：20
店No：1 商品No：4 商品名：モンブラン 在庫数：15
店No：2 商品No：4 商品名：モンブラン 在庫数：40
店No：2 商品No：4 商品名：モンブラン 在庫数：10
店No：2 商品No：4 商品名：モンブラン 在庫数：25
クロス結合は実用的ではない為使用する機会は少ないと思います。
複数の条件でjoinする and条件
joinする際の関連する値が一つだけではなく二つや三つになる場合もあります。
クエリ文では下記のように記載します。
select *
from shohin_table
inner join zaiko_table on zaiko_table.shohin_no = shohin_table.shohin_no
and zaiko_table.shop_no = 2実行結果は下記となります。
SQLAlchemyで記載する場合はand_importします。
from sqlalchemy import and_
出力結果 = session.query(テーブル名, 出力するフィールド).join(結合するテーブル名, and_(テーブル同士の関連付け1, テーブル同士の関連付け2) )
サンプルを見てみましょう。
from sqlalchemy import and_
datas = ses.query(Shohin, Shohin.shohin_no, Shohin.name, Zaiko.shop_no, Zaiko.suryo
).join(Zaiko, and_(Zaiko.shohin_no == Shohin.shohin_no, Zaiko.shop_no == 2))
for data in datas:
print("店No：{} 商品No：{} 商品名：{} 在庫数：{}".format(data.shop_no, data.shohin_no, data.name, data.suryo))
# 実行結果
# 店No：2 商品No：1 商品名：アイス 在庫数：40
# 店No：2 商品No：2 商品名：ケーキ 在庫数：10
# 店No：2 商品No：3 商品名：パフェ 在庫数：25※本来ならばZaiko.shop_no==2は固定の値なのでWhere文として記載するべきですが、簡単な分かりやすいサンプルでand条件をお伝えするためにこのように記載しました。通常joinに記載するのは2といった固定の値ではなくテーブルに存在する値同士を関連付けするべきです。
複数テーブルのjoin
joinした後、更にjoinすることで3つ以上のテーブルを結合することもできます。
サンプルではテーブルが2つしか準備してなかったので、3つ目のテーブルとしてShopテーブルを追加して見ましょう。
class Shop(base):
__tablename__ = 'shop_table'
shop_no = Column(Integer, primary_key=True)
shop_name = Column(String(255))
レコードにはTokyoとOkayamaの店名を登録します。
ses.add(Shop(shop_no = 1, shop_name = 'Tokyo'))
ses.add(Shop(shop_no = 2, shop_name = 'Okayama'))
先ほどまで紹介したサンプルデータの取得に店名も加えて表示してみます。
datas = ses.query(Shohin, Shohin.shohin_no, Shohin.name, Zaiko.shop_no, Zaiko.suryo, Shop.shop_name
).join(Zaiko, Zaiko.shohin_no == Shohin.shohin_no
).join(Shop, Shop.shop_no == Zaiko.shop_no)
for data in datas:
print("店No：{} 店名：{} 商品No：{} 商品名：{} 在庫数：{}".format(data.shop_no, data.shop_name, data.shohin_no, data.name, data.suryo))
# 実行結果
# 店No：1 店名：Tokyo 商品No：1 商品名：アイス 在庫数：30
# 店No：1 店名：Tokyo 商品No：2 商品名：ケーキ 在庫数：20
# 店No：1 店名：Tokyo 商品No：3 商品名：パフェ 在庫数：15
# 店No：2 店名：Okayama 商品No：1 商品名：アイス 在庫数：40
# 店No：2 店名：Okayama 商品No：2 商品名：ケーキ 在庫数：10
# 店No：2 店名：Okayama 商品No：3 商品名：パフェ 在庫数：25以上、SQLAlchemyのjoinについて解説しました。
最後まで見てくれてありがとうだにゃあ
