SQLAlchemy ORMでのJOINのやり方

SQLAlchemyのORMを使ったSELECT時のJOINのやり方がよくわからなかったので、調べてみた。

やりたいこと

studentテーブルのschool_idとschoolテーブルのidを結合して、 student.id,student,name,school.nameを取得したい。

テーブルのデータは以下のとおり。

studentテーブル

id	name	school_id
1	sato	1
2	tanaka	2
3	watanabe	3

schoolテーブル

id	name
1	あいうえお学園
2	かきくけこ学園
3	さしすせそ学校

Coreを使った結合

データクラスは以下のように定義。

class Base(DeclarativeBase):
    pass

class Student(Base):
    __tablename__ = "student"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    school_id: Mapped[int]

class School(Base):
    __tablename__ = "school"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))

JOINしたSQL文を生成するための処理を定義。

stmt = select(Student).join(School, Student.school_id == School.id).where(Student.id == 1)

生成されるSQL文は以下のようになる。
studentテーブルとschoolテーブルのnameカラムが同一名となっているので、AS句によって別名が付けられている。

SELECT student.id, student.name, school.name AS name_1 
FROM student JOIN school ON student.school_id = school.id 
WHERE student.id = %(id_1)s

生成したSQL文を実行する場合、executeを実行する。
データにアクセスするには、result[カラム名]になるため、AS句によって自動で別名をつけられた際に、意図したデータを参照することが出来なくなる。

results = session.execute(stmt).mappings().all()
for result in results:
    print(result)

意図した別名を付けるには、labelを使用する。

stmt = select(Student.id, Student.name, School.name.label("school_name")).join(School, Student.school_id == School.id).where(Student.id == 1)
print(stmt)

SELECT student.id, student.name, school.name AS school_name 
FROM student JOIN school ON student.school_id = school.id 
WHERE student.id = %(id_1)s

ORMを使った結合

ORMを使った結合をするためには、外部キーとリレーションシップの設定が必要になる。
まずは、school_idに外部キーを設定。

school_id: Mapped[int] = mapped_column(ForeignKey("school.id"))

次にschoolとのリレーションシップを設定。

school: Mapped["School"] = relationship()

全体のデータクラスの定義は以下のようになる。

class Student(Base):
    __tablename__ = "student"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    school_id: Mapped[int] = mapped_column(ForeignKey("school.id"))
    school: Mapped["School"] = relationship()

class School(Base):
    __tablename__ = "school"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))

これで実行する。

stmt = select(Student, School).join(School, Student.school_id == School.id).where(Student.id == 1)
results = session.scalars(stmt)

for result in results:
    print("student.id = " + str(result.id))
    print("student.name = " + str(result.name))
    print("student.school_id = " + str(result.school_id))
    print("student.school.id = " + str(result.school.id))
    print("student.school.name = " + str(result.school.name))

studentテーブルのidが1のデータが取得できた。

student.id = 1
student.name = sato
student.school_id = 1
student.school.id = 1
student.school.name = あいうえお学園

最後に

本当はstudent.nameと同じ階層でschool.nameの値も取得したかったが、少し期待はずれ。
自分のやり方が間違っているのかもしれないので、時間を見つけて調べてみようと思う。

参考

docs.sqlalchemy.org

docs.sqlalchemy.org