SQL Expressions as Mapped Attributes

マップされたクラスの属性は、クエリで使用できるSQL式にリンクできます。

Using a Hybrid

比較的単純なSQL式をクラスにリンクする最も簡単で柔軟な方法は、 Hybrid Attributes 節で説明されている、いわゆる ハイブリッド属性 を使用することです。ハイブリッドは、PythonレベルとSQL式レベルの両方で機能する式を提供します。たとえば、以下では、属性 firstname と lastname を含むクラス User をマップし、この2つの文字列連結である fullname を提供するハイブリッドを含めます。:

from sqlalchemy.ext.hybrid import hybrid_property

class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))

    @hybrid_property
    def fullname(self):
        return self.firstname + " " + self.lastname

上記では、 fullname 属性はインスタンスとクラスの両方のレベルで解釈され、インスタンスから利用できるようになっています。:

some_user = session.scalars(select(User).limit(1)).first()
print(some_user.fullname)

クエリ内でも使用できます:

some_user = session.scalars(
    select(User).where(User.fullname == "John Smith").limit(1)
).first()

文字列連結の例は単純なもので、Python式はインスタンスレベルとクラスレベルで二重の目的を持つことができます。多くの場合、SQL式はPython式と区別する必要があります。これは hybrid_property.expression() を使用して実現できます。以下では、Pythonの if 文とSQL式の case() 構文を使用して、条件がハイブリッド内に存在する必要がある場合を説明します:

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import case

class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))

    @hybrid_property
    def fullname(self):
        if self.firstname is not None:
            return self.firstname + " " + self.lastname
        else:
            return self.lastname

    @fullname.expression
    def fullname(cls):
        return case(
            (cls.firstname != None, cls.firstname + " " + cls.lastname),
            else_=cls.lastname,
        )

Using column_property

column_property() 関数は、正規にマップされた Column と同様の方法でSQL式をマップするために使用することができます。このテクニックでは、属性はロード時に他のすべての列にマップされた属性と共にロードされます。これは、ハイブリッドを使用するよりも有利な場合があります。なぜなら、特に、既にロードされたオブジェクトでは通常利用できないデータにアクセスするために、他のテーブルに(通常は相関サブクエリとして)リンクする式の場合、値はオブジェクトの親行と同時に前面にロードできるからです。

SQL式に column_property() を使用することの欠点は、その式がクラス全体に対して発行されるSELECT文と互換性がなければならないことです。また、宣言型ミックスインから column_property() を使用すると発生する可能性のある設定上の問題もあります。

“fullname”の例は column_property() を使って次のように表現できます:

from sqlalchemy.orm import column_property

class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))
    fullname = column_property(firstname + " " + lastname)

相関サブクエリも同様に使うことができます。以下では select() 構文を使って ScalarSelect を作成します。これは列指向のSELECT文を表し、特定の User で利用可能な Address オブジェクトの数をリンクします:

from sqlalchemy.orm import column_property
from sqlalchemy import select, func
from sqlalchemy import Column, Integer, String, ForeignKey

from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class Address(Base):
    __tablename__ = "address"
    id = mapped_column(Integer, primary_key=True)
    user_id = mapped_column(Integer, ForeignKey("user.id"))

class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    address_count = column_property(
        select(func.count(Address.id))
        .where(Address.user_id == id)
        .correlate_except(Address)
        .scalar_subquery()
    )

上の例では、以下のように ScalarSelect() 構文を定義します:

stmt = (
    select(func.count(Address.id))
    .where(Address.user_id == id)
    .correlate_except(Address)
    .scalar_subquery()
)

上記では、まず select() を使用して Select 構文を作成し、それを Select.scalar_subquery() メソッドを使用して scalar subquery に変換します。これは、この Select 文を列式のコンテキストで使用する意図を示しています。

Select 自体の中で、 Address.id 行のカウントを選択します。ここで、 Address.user_id カラムは id と等しくなります。 User クラスのコンテキストでは、 id は id という名前の Column です( id はパイソン組み込み関数の名前でもありますが、ここでは使用しません。 User クラス定義の外にいる場合は、 User.id を使用します)。

Select.correlate_except() メソッドは、この select() のFROM句の各要素がFROMリストから省略できることを示します(つまり、 User に対するSELECT文に関連付けられます)。ただし、 Address に対応するものは除きます。これは厳密には必要ではありませんが、 Address に対するSELECT文がネストされている User テーブルと Address テーブルの間の長い結合文字列の場合に、 Address が誤ってFROMリストから省略されるのを防ぎます。

多対多の関係からリンクされた列を参照する column_property() の場合、 and_() を使用して、関連テーブルのフィールドを関係内の両方のテーブルに結合します:

from sqlalchemy import and_

class Author(Base):
    # ...

    book_count = column_property(
        select(func.count(books.c.id))
        .where(
            and_(
                book_authors.c.author_id == authors.c.id,
                book_authors.c.book_id == books.c.id,
            )
        )
        .scalar_subquery()
    )

Adding column_property() to an existing Declarative mapped class

インポートの問題で column_property() がクラスとインラインで定義されない場合、両方が設定された後にクラスに割り当てることができます。宣言型基底クラス(すなわち DeclarativeBase スーパークラスまたは declarative_base() のようなレガシー関数によって生成される)を利用するマッピングを使用する場合、この属性の割り当ては Mapper.add_property() を呼び出して、ファクトの後に追加のプロパティを追加する効果があります:

# only works if a declarative base class is in use
User.address_count = column_property(
    select(func.count(Address.id)).where(Address.user_id == User.id).scalar_subquery()
)

registry.mapped() デコレータなど、宣言型基底クラスを使用しないマッピングスタイルを使用する場合、 Mapper.add_property() メソッドは、基礎となる Mapper オブジェクトに対して明示的に呼び出すことができます。これは inspect() を使用して取得できます:

from sqlalchemy.orm import registry

reg = registry()

@reg.mapped
class User:
    __tablename__ = "user"

    # ... additional mapping directives

# later ...

# works for any kind of mapping
from sqlalchemy import inspect

inspect(User).add_property(
    column_property(
        select(func.count(Address.id))
        .where(Address.user_id == User.id)
        .scalar_subquery()
    )
)

See also

Appending additional columns to an existing Declarative mapped class

Composing from Column Properties at Mapping Time

複数の ColumnProperty オブジェクトを組み合わせたマッピングを作成することができます。 ColumnProperty は、既存の式オブジェクトによってターゲットにされている場合、Core式のコンテキストで使用されるとSQL式として解釈されます。これは、オブジェクトがSQL式を返す __clause_element__() メソッドを持っていることをCoreが検出することによって機能します。ただし、 ColumnProperty が、それをターゲットにする他のCore SQL式オブジェクトがない式でリードオブジェクトとして使用されている場合、 ColumnProperty.expression 属性は、SQL式を一貫して構築するために使用できるように、基礎となるSQL式を返します。以下では、File`クラスには、それ自体が :class:.ColumnProperty` である`File.filename`属性に文字列トークンを連結する属性`File.path`が含まれています:

class File(Base):
    __tablename__ = "file"

    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String(64))
    extension = mapped_column(String(8))
    filename = column_property(name + "." + extension)
    path = column_property("C:/" + filename.expression)

通常、式の中で File クラスを使用する場合、 filename と path に割り当てられた属性を直接使用することができます。 ColumnProperty.expression 属性を使用する必要があるのは、 ColumnProperty をマッピング定義内で直接使用する場合のみです。:

stmt = select(File.path).where(File.filename == "foo.txt")

Using Column Deferral with column_property()

Limiting which Columns Load with Column Deferral の ORM Querying Guide で導入された列遅延機能は、 column_property() によってマップされたSQL式へのマッピング時に、 column_property() の代わりに deferred() 関数を使用することで適用できます:

from sqlalchemy.orm import deferred

class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    firstname: Mapped[str] = mapped_column()
    lastname: Mapped[str] = mapped_column()
    fullname: Mapped[str] = deferred(firstname + " " + lastname)

See also

Using deferred() for imperative mappers, mapped SQL expressions

Using a plain descriptor

column_property() や hybrid_property が提供するものよりも複雑なSQLクエリを発行する必要がある場合、属性としてアクセスされる通常のPython関数を使用することができます。ただし、その式は既にロードされているインスタンスでのみ使用可能である必要があります。この関数は、読み取り専用属性としてマークするために、Python独自の @property デコレータで装飾されています。関数内では、 object_session() を使用して現在のオブジェクトに対応する Session を見つけ、それを使用してクエリを発行します:

from sqlalchemy.orm import object_session
from sqlalchemy import select, func

class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))

    @property
    def address_count(self):
        return object_session(self).scalar(
            select(func.count(Address.id)).where(Address.user_id == self.id)
        )

プレーン記述子アプローチは最後の手段として有用ですが、通常のケースでは、アクセスのたびにSQLクエリを発行する必要があるという点で、ハイブリッドとカラムプロパティの両方のアプローチよりもパフォーマンスが低くなります。

Query-time SQL expressions as mapped attributes

マップされたクラスに固定のSQL式を設定できることに加えて、SQLAlchemy ORMには、クエリ時に状態の一部として設定された任意のSQL式の結果をオブジェクトにロードできる機能も含まれています。この動作は、 query_expression() を使用してORMマップ属性を設定し、クエリ時に with_expression() ローダオプションを使用することで利用できます。マッピングと使用方法の例については、 Loading Arbitrary SQL Expressions onto Objects を参照してください。