このページではSQLAlchemyのORM上でJOINを行うクエリの実行方法について学習します。
リレーションがない場合のJOIN
JOINの方法はいくつかあるのですが、まずはテーブル間のリレーションを無視した一番単純な方法を紹介します。一般的にORMはJOINするとテーブルのリレーションが考慮された階層構造のオブジェクトを取得できるのですが、その方法については次回にリレーションと合わせて紹介します。
FULL OUTER JOIN
session.queryで複数テーブルを指定すると、FULL OUTER JOINされた結果を取得することができます。
session.query(テーブル1, テーブル2).all()
INNER JOIN
FULL OUTER JOINの結果をフィルタリング、もしくはjoinすることでINNER JOINの結果を取得することができます。
# FULL OUTER JOINの結果をフィルタリング session.query(テーブル1, テーブル2).filter(テーブル1.id == テーブル2.hoge_id) # FULL OUTER JOINの結果をさらにjoin session.query(テーブル1, テーブル2).join(テーブル1, テーブル1.id == テーブル2.hoge_id)
LEFT OUTER JOIN
同様に、FULL OUTER JOINの結果を再度LEFT OUTER JOINすると、LEFT OUTER JOINの結果を得ることができます。
# FULL OUTER JOINの結果をさらにjoin session.query(テーブル1, テーブル2).join(テーブル1, テーブル1.id == テーブル2.hoge_id)
サンプル
ユーザーテーブルと、ユーザーに紐づく投稿テーブルに対してINNER JOINとLEFT OUTER JOINを利用したサンプルです。
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy import ForeignKey # DB接続 engine = create_engine('sqlite:///:memory:') # Base Base = declarative_base() # テーブルクラスを定義 class User(Base): """ Userテーブルクラス """ # テーブル名 __tablename__ = 'users' # 個々のカラムを定義 id = Column(Integer, primary_key=True) name = Column(String) age = Column(Integer) class Post(Base): """ Postテーブルクラス """ # テーブル名 __tablename__ = 'posts' # 個々のカラムを定義 id = Column(Integer, primary_key=True) users_id = Column(Integer, ForeignKey('users.id')) title = Column(String) body = Column(Integer) # テーブルクラスのテーブルを生成 Base.metadata.create_all(engine) # セッション生成 Session = sessionmaker(bind=engine) session = Session() # サンプルデータ挿入 session.add(User(id=1, name="Suzuki", age=19)) session.add(User(id=2, name="Tanaka", age=21)) session.add(User(id=3, name="Sato", age=21)) session.add(Post(users_id=1, title="朝の体操", body="ラジオ体操で元気いっぱい")) session.add(Post(users_id=1, title="今日の夕食", body="カレーラスがとても美味しかった。")) session.add(Post(users_id=2, title="仕事", body="今日はDjangoでAPI作成。")) session.add(Post(users_id=2, title="Python楽しい", body="Python楽しいですよね!!")) session.commit() # inner joinのサンプル users_posts = session.query(User, Post).join(Post, User.id == Post.users_id).all() for user_posts in users_posts: print("%sさんの投稿 タイトル:%s" % (user_posts.User.name, user_posts.Post.title,)) print('*****') # left outer joinのサンプル users_posts = session.query(User, Post).outerjoin(Post, User.id == Post.users_id).all() for user_posts in users_posts: if user_posts.Post is not None: print("%sさんの投稿 タイトル:%s" % (user_posts.User.name, user_posts.Post.title,)) else: pass print("%sさんの投稿 なし" % (user_posts.User.name,))
以下の結果を取得することができます。テーブルのリレーションが定義されていないため、結果のオブジェクトは階層構造になっておらず冗長になっている点に注目してください。
Suzukiさんの投稿 タイトル:朝の体操 Suzukiさんの投稿 タイトル:今日の夕食 Tanakaさんの投稿 タイトル:仕事 Tanakaさんの投稿 タイトル:Python楽しい ***** Suzukiさんの投稿 タイトル:今日の夕食 Suzukiさんの投稿 タイトル:朝の体操 Tanakaさんの投稿 タイトル:Python楽しい Tanakaさんの投稿 タイトル:仕事 Satoさんの投稿 なし