Association Proxy

associationproxy は、関係全体にわたってターゲット属性の読み取り/書き込みビューを作成するために使用されます。これは基本的に、2つのエンドポイント間の「中間」属性の使用を隠蔽し、関連するオブジェクトのコレクションまたはスカラー関係の両方からフィールドを選択するために使用できます。または、関連オブジェクトパターンを使用する際の冗長性を減らすために使用できます。創造的に適用されると、関連プロキシは、標準的で透過的に構成されたリレーショナルパターンを使用してデータベースに保持された、事実上すべてのジオメトリの洗練されたコレクションとディクショナリビューの構築を可能にします。

Simplifying Scalar Collections

上の例では、 association_proxy() を User クラスに適用して、それぞれの Keyword オブジェクトに関連付けられた .keyword の文字列値を公開する kw 関係の”ビュー”を生成します。また、文字列がコレクションに追加されると、新しい Keyword オブジェクトを透過的に作成します:

>>> user = User("jek")
>>> user.keywords.append("cheese-inspector")
>>> user.keywords.append("snack-ninja")
>>> print(user.keywords)
['cheese-inspector', 'snack-ninja']

この仕組みを理解するには、まず関連付けプロキシの .keywords を使わずに User と Keyword の振舞いを見てみましょう。通常、 User に関連付けられた keyword 文字列のコレクションを読み取ったり操作したりするには、各コレクション要素から .keyword 属性までトラバースする必要がありますが、これは厄介です。以下の例は、関連付けプロキシを使わずに適用される同一の一連の操作を示しています。:

>>> # identical operations without using the association proxy
>>> user = User("jek")
>>> user.kw.append(Keyword("cheese-inspector"))
>>> user.kw.append(Keyword("snack-ninja"))
>>> print([keyword.keyword for keyword in user.kw])
['cheese-inspector', 'snack-ninja']

association_proxy() 関数によって生成された AssociationProxy オブジェクトは、 Python descriptor のインスタンスであり、 Mapper によって「マップされている」とはみなされません。したがって、宣言型マッピングと命令型マッピングのどちらが使用されているかに関係なく、マップされたクラスのクラス定義内で常にインラインで示されます。

プロキシは、操作に応じて、基礎となるマップされた属性またはコレクションを操作することによって機能します。プロキシを介して行われた変更は、マップされた属性にすぐに反映されます。その逆も同様です。基礎となる属性は、完全にアクセス可能なままです。

最初にアクセスされると、アソシエーションプロキシはターゲットコレクションに対してイントロスペクション操作を実行して、その動作が正しく対応するようにします。ローカルにプロキシされた属性がコレクション(一般的)かスカラ参照か、コレクションがセット、リスト、ディクショナリのように動作するかなどの詳細が考慮されるため、プロキシは基礎となるコレクションまたは属性と同様に動作する必要があります。

Creation of New Values

リストの append() イベント(またはset add() イベント、dictionary __setitem__() イベント、scalar assignmentイベント)が関連付けプロキシによってインターセプトされると、コンストラクタを使用して 仲介 オブジェクトの新しいインスタンスがインスタンス化され、指定された値が単一の引数として渡されます。上記の例では、次のような操作が行われます。:

user.keywords.append("cheese-inspector")

アソシエーション・プロキシーによって次の操作に変換されます。:

user.kw.append(Keyword("cheese-inspector"))

この例がここで動作するのは、 Keyword のコンストラクタが単一の位置引数 keyword を受け入れるように設計されているからです。単一引数のコンストラクタが実現できない場合、関連プロキシの作成動作は、 association_proxy.creator 引数を使用してカスタマイズできます。この引数は、特異引数を指定して新しいオブジェクトインスタンスを生成する呼び出し可能オブジェクト(つまりPython関数)を参照します。以下では、一般的なラムダを使用してこれを説明します:

class User(Base):
    ...

    # use Keyword(keyword=kw) on append() events
    keywords: AssociationProxy[List[str]] = association_proxy(
        "kw", "keyword", creator=lambda kw: Keyword(keyword=kw)
    )

リストベースまたはセットベースのコレクション、あるいはスカラー属性の場合、 creator 関数は単一の引数を受け付けます。辞書ベースのコレクションの場合、”key”と”value”の2つの引数を受け付けます。この例を以下の Proxying to Dictionary Based Collections に示します。

Simplifying Association Objects

“association object”パターンは、多対多の関係を拡張したもので、 Association Object で説明されています。関連付けプロキシは、通常の使用時に”association objects”を邪魔しないようにするのに便利です。

上記の user_keyword テーブルに、明示的にマップしたい追加の列があったとします。しかし、ほとんどの場合、これらの属性に直接アクセスする必要はありません。以下では、前に説明した user_keyword テーブルにマップされる UserKeywordAssociation クラスを導入する新しいマッピングを説明します。このクラスは、追加の列 special_key を追加します。この値は、時々アクセスしたいと思いますが、通常はアクセスしません。 keywords と呼ばれる User クラスに関連付けプロキシを作成します。これは、 User の user_keyword_associations コレクションと各 UserKeywordAssociation に存在する .keyword 属性とのギャップを埋めるものです。:

from __future__ import annotations

from typing import List
from typing import Optional

from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.associationproxy import AssociationProxy
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64))

    user_keyword_associations: Mapped[List[UserKeywordAssociation]] = relationship(
        back_populates="user",
        cascade="all, delete-orphan",
    )

    # association proxy of "user_keyword_associations" collection
    # to "keyword" attribute
    keywords: AssociationProxy[List[Keyword]] = association_proxy(
        "user_keyword_associations",
        "keyword",
        creator=lambda keyword_obj: UserKeywordAssociation(keyword=keyword_obj),
    )

    def __init__(self, name: str):
        self.name = name

class UserKeywordAssociation(Base):
    __tablename__ = "user_keyword"
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"), primary_key=True)
    keyword_id: Mapped[int] = mapped_column(ForeignKey("keyword.id"), primary_key=True)
    special_key: Mapped[Optional[str]] = mapped_column(String(50))

    user: Mapped[User] = relationship(back_populates="user_keyword_associations")

    keyword: Mapped[Keyword] = relationship()

class Keyword(Base):
    __tablename__ = "keyword"
    id: Mapped[int] = mapped_column(primary_key=True)
    keyword: Mapped[str] = mapped_column("keyword", String(64))

    def __init__(self, keyword: str):
        self.keyword = keyword

    def __repr__(self) -> str:
        return f"Keyword({self.keyword!r})"

上記の設定では、各 User オブジェクトの .keywords コレクションを操作することができます。各オブジェクトは、基礎となる UserKeywordAssociation 要素から取得された Keyword オブジェクトのコレクションを公開します。:

>>> user = User("log")
>>> for kw in (Keyword("new_from_blammo"), Keyword("its_big")):
...     user.keywords.append(kw)
>>> print(user.keywords)
[Keyword('new_from_blammo'), Keyword('its_big')]

この例は、以前に Simplifying Scalar Collections で説明した例とは対照的です。この例では、関連付けプロキシは、構成されたオブジェクトのコレクションではなく、文字列のコレクションを公開していました。この場合、それぞれの .keywords.append() 操作は次のようになります。

>>> user.user_keyword_associations.append(
...     UserKeywordAssociation(keyword=Keyword("its_heavy"))
... )

関連付けプロキシは、以下のすべての操作で表現される キーワード オブジェクトのコレクションを返します。:

>>> print(user.keywords)
[Keyword('new_from_blammo'), Keyword('its_big'), Keyword('its_heavy'), Keyword('its_wood')]

Proxying to Dictionary Based Collections

関連付けプロキシは、辞書ベースのコレクションにプロキシすることもできます。SQLAlchemyマッピングは通常、辞書コレクションを作成するために attribute_keyed_dict() コレクション型と、 Custom Dictionary-Based Collections で説明されている拡張テクニックを使用します。

アソシエーションプロキシは、辞書ベースのコレクションの使用を検出すると、その動作を調整します。新しい値が辞書に追加されると、アソシエーションプロキシは、キーと値の1つではなく2つの引数を作成関数に渡すことによって、中間オブジェクトをインスタンス化します。いつものように、この作成関数は中間クラスのコンストラクタにデフォルト設定され、 creator 引数を使用してカスタマイズできます。

以下では、 UserKeywordAssociation の例を変更して、 User.user_keyword_associations コレクションが辞書を使用してマップされるようにします。ここでは、 UserKeywordAssociation.special_key 引数が辞書のキーとして使用されます。また、 User.keywords プロキシに creator 引数を適用して、新しい要素が辞書に追加されたときにこれらの値が適切に割り当てられるようにします。:

from __future__ import annotations
from typing import Dict

from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.associationproxy import AssociationProxy
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy.orm.collections import attribute_keyed_dict

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64))

    # user/user_keyword_associations relationship, mapping
    # user_keyword_associations with a dictionary against "special_key" as key.
    user_keyword_associations: Mapped[Dict[str, UserKeywordAssociation]] = relationship(
        back_populates="user",
        collection_class=attribute_keyed_dict("special_key"),
        cascade="all, delete-orphan",
    )
    # proxy to 'user_keyword_associations', instantiating
    # UserKeywordAssociation assigning the new key to 'special_key',
    # values to 'keyword'.
    keywords: AssociationProxy[Dict[str, Keyword]] = association_proxy(
        "user_keyword_associations",
        "keyword",
        creator=lambda k, v: UserKeywordAssociation(special_key=k, keyword=v),
    )

    def __init__(self, name: str):
        self.name = name

class UserKeywordAssociation(Base):
    __tablename__ = "user_keyword"
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"), primary_key=True)
    keyword_id: Mapped[int] = mapped_column(ForeignKey("keyword.id"), primary_key=True)
    special_key: Mapped[str]

    user: Mapped[User] = relationship(
        back_populates="user_keyword_associations",
    )
    keyword: Mapped[Keyword] = relationship()

class Keyword(Base):
    __tablename__ = "keyword"
    id: Mapped[int] = mapped_column(primary_key=True)
    keyword: Mapped[str] = mapped_column(String(64))

    def __init__(self, keyword: str):
        self.keyword = keyword

    def __repr__(self) -> str:
        return f"Keyword({self.keyword!r})"

Composite Association Proxies

複合関連プロキシ

関係からスカラー属性へのプロキシ、関連付けオブジェクト全体のプロキシ、および辞書のプロキシのこれまでの例を考えると、これら3つのテクニックを組み合わせて、文字列 keyword にマップされた special_key の文字列値を厳密に処理する keywords 辞書を User に与えることができます。 UserKeywordAssociation クラスと Keyword クラスは完全に隠されています。これは、 UserKeywordAssociation に存在する関連付けプロキシを参照する関連付けプロキシを User に構築することによって実現されます:

from __future__ import annotations

from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.associationproxy import AssociationProxy
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy.orm.collections import attribute_keyed_dict

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64))

    user_keyword_associations: Mapped[Dict[str, UserKeywordAssociation]] = relationship(
        back_populates="user",
        collection_class=attribute_keyed_dict("special_key"),
        cascade="all, delete-orphan",
    )
    # the same 'user_keyword_associations'->'keyword' proxy as in
    # the basic dictionary example.
    keywords: AssociationProxy[Dict[str, str]] = association_proxy(
        "user_keyword_associations",
        "keyword",
        creator=lambda k, v: UserKeywordAssociation(special_key=k, keyword=v),
    )

    def __init__(self, name: str):
        self.name = name

class UserKeywordAssociation(Base):
    __tablename__ = "user_keyword"
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"), primary_key=True)
    keyword_id: Mapped[int] = mapped_column(ForeignKey("keyword.id"), primary_key=True)
    special_key: Mapped[str] = mapped_column(String(64))
    user: Mapped[User] = relationship(
        back_populates="user_keyword_associations",
    )

    # the relationship to Keyword is now called
    # 'kw'
    kw: Mapped[Keyword] = relationship()

    # 'keyword' is changed to be a proxy to the
    # 'keyword' attribute of 'Keyword'
    keyword: AssociationProxy[Dict[str, str]] = association_proxy("kw", "keyword")

class Keyword(Base):
    __tablename__ = "keyword"
    id: Mapped[int] = mapped_column(primary_key=True)
    keyword: Mapped[str] = mapped_column(String(64))

    def __init__(self, keyword: str):
        self.keyword = keyword

上記の例で注意すべき点は、 Keyword オブジェクトは辞書の設定操作ごとに作成されるため、この例では Keyword オブジェクトの文字列名の一意性を維持できないことです。これは、このようなタグ付けシナリオの典型的な要件です。このユースケースでは、レシピ UniqueObject 、または同等の作成方法をお勧めします。この方法では、 最初に検索してから作成 方法を Keyword クラスのコンストラクタに適用し、指定された名前がすでに存在する場合は、既存の Keyword が返されるようにします。

Querying with Association Proxies

AssociationProxy は、他のORMマップ属性と同様にクラスレベルで動作する単純なSQL構築機能を特徴とし、主にSQLの EXISTS キーワードに基づいた基本的なフィルタリングサポートを提供します。

Note

アソシエーションプロキシ拡張の主な目的は、すでにロードされているマップされたオブジェクトインスタンスを使用して、永続性とオブジェクトアクセスパターンを改善できるようにすることである。クラスバインドクエリ機能の使用は制限されており、JOINやEager Loadingオプションなどを使用してSQLクエリを構築するときに、基礎となる属性を参照する必要性に取って代わるものではない。

このセクションでは、以下のマッピング例のように、カラムを参照するアソシエーションプロキシと、関連するオブジェクトを参照するアソシエーションプロキシの両方を持つクラスを想定します。:

from __future__ import annotations
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.associationproxy import association_proxy, AssociationProxy
from sqlalchemy.orm import DeclarativeBase, relationship
from sqlalchemy.orm.collections import attribute_keyed_dict
from sqlalchemy.orm.collections import Mapped

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64))

    user_keyword_associations: Mapped[UserKeywordAssociation] = relationship(
        cascade="all, delete-orphan",
    )

    # object-targeted association proxy
    keywords: AssociationProxy[List[Keyword]] = association_proxy(
        "user_keyword_associations",
        "keyword",
    )

    # column-targeted association proxy
    special_keys: AssociationProxy[List[str]] = association_proxy(
        "user_keyword_associations", "special_key"
    )

class UserKeywordAssociation(Base):
    __tablename__ = "user_keyword"
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"), primary_key=True)
    keyword_id: Mapped[int] = mapped_column(ForeignKey("keyword.id"), primary_key=True)
    special_key: Mapped[str] = mapped_column(String(64))
    keyword: Mapped[Keyword] = relationship()

class Keyword(Base):
    __tablename__ = "keyword"
    id: Mapped[int] = mapped_column(primary_key=True)
    keyword: Mapped[str] = mapped_column(String(64))

生成されるSQLは、EXISTS SQL等価演算子に対する相関サブクエリの形式をとるため、WHERE句の中で使用できます。外部のクエリを変更する必要はありません。アソシエーション・プロキシの直接のターゲットが マップされたカラム式 である場合、サブクエリに埋め込まれる標準のカラム演算子を使用できます。たとえば、次のような直接的な演算子があります。

>>> print(session.scalars(select(User).where(User.special_keys == "jek")))
SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE EXISTS (SELECT 1
FROM user_keyword
WHERE "user".id = user_keyword.user_id AND user_keyword.special_key = :special_key_1)

a LIKE operator:

LIKE演算子:

>>> print(session.scalars(select(User).where(User.special_keys.like("%jek"))))
SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE EXISTS (SELECT 1
FROM user_keyword
WHERE "user".id = user_keyword.user_id AND user_keyword.special_key LIKE :special_key_1)

直接のターゲットが**関連するオブジェクトやコレクション、または関連するオブジェクト上の別の関連プロキシや属性**である関連プロキシの場合、代わりに PropComparator.has() や PropComparator.any() のような関係指向の演算子を使うことができます。実際、 User.keywords 属性は2つの関連プロキシがリンクされているので、このプロキシを使ってSQLフレーズを生成すると、2つのレベルのEXISTSサブクエリが得られます。

>>> print(session.scalars(select(User).where(User.keywords.any(Keyword.keyword == "jek"))))
SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE EXISTS (SELECT 1
FROM user_keyword
WHERE "user".id = user_keyword.user_id AND (EXISTS (SELECT 1
FROM keyword
WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1)))

これはSQLの最も効率的な形式ではありません。そのため、関連付けプロキシはWHERE条件を迅速に生成するのに便利ですが、特に関連付けプロキシを一緒にチェーニングする場合は、SQLの結果を検査し、明示的なJOIN条件に「展開」して最適に使用する必要があります。

Cascading Scalar Deletes

New in version 1.3.

マッピングを次のように指定します。:

from __future__ import annotations
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.associationproxy import association_proxy, AssociationProxy
from sqlalchemy.orm import DeclarativeBase, relationship
from sqlalchemy.orm.collections import attribute_keyed_dict
from sqlalchemy.orm.collections import Mapped

class Base(DeclarativeBase):
    pass

class A(Base):
    __tablename__ = "test_a"
    id: Mapped[int] = mapped_column(primary_key=True)
    ab: Mapped[AB] = relationship(uselist=False)
    b: AssociationProxy[B] = association_proxy(
        "ab", "b", creator=lambda b: AB(b=b), cascade_scalar_deletes=True
    )

class B(Base):
    __tablename__ = "test_b"
    id: Mapped[int] = mapped_column(primary_key=True)

class AB(Base):
    __tablename__ = "test_ab"
    a_id: Mapped[int] = mapped_column(ForeignKey(A.id), primary_key=True)
    b_id: Mapped[int] = mapped_column(ForeignKey(B.id), primary_key=True)

    b: Mapped[B] = relationship()

An assignment to A.b will generate an AB object:

``A.b`` に代入すると、 ``AB`` オブジェクトが生成されます。

   a.b = B()

A.b 関連付けはスカラーであり、パラメータ AssociationProxy.cascade_scalar_deletes の使用を含みます。このパラメータが有効になっている場合、 A.b を None に設定すると A.ab も削除されます:

a.b = None
assert a.ab is None

AssociationProxy.cascade_scalar_deletes が設定されていない場合、上記の関連付けオブジェクト a.ab はそのまま残ります。

これはコレクションベースの関連付けプロキシの動作ではないことに注意してください。この場合、プロキシされたコレクションのメンバが削除されると、中間の関連付けオブジェクトは常に削除されます。行が削除されるかどうかは、関連のカスケード設定によって異なります。

See also

Cascades

Scalar Relationships

以下の例は、1対多の関係の多側で、スカラー・オブジェクトの属性を処理するアソシエーション・プロキシーの使用方法を示しています。:

from __future__ import annotations

from typing import List

from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.associationproxy import AssociationProxy
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Base(DeclarativeBase):
    pass

class Recipe(Base):
    __tablename__ = "recipe"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64))

    steps: Mapped[List[Step]] = relationship(back_populates="recipe")
    step_descriptions: AssociationProxy[List[str]] = association_proxy(
        "steps", "description"
    )

class Step(Base):
    __tablename__ = "step"
    id: Mapped[int] = mapped_column(primary_key=True)
    description: Mapped[str]
    recipe_id: Mapped[int] = mapped_column(ForeignKey("recipe.id"))
    recipe: Mapped[Recipe] = relationship(back_populates="steps")

    recipe_name: AssociationProxy[str] = association_proxy("recipe", "name")

    def __init__(self, description: str) -> None:
        self.description = description

my_snack = Recipe(
    name="afternoon snack",
    step_descriptions=[
        "slice bread",
        "spread peanut butted",
        "eat sandwich",
    ],
)

my_snack の手順の要約は次のように出力できます。:

>>> for i, step in enumerate(my_snack.steps, 1):
...     print(f"Step {i} of {step.recipe_name!r}: {step.description}")
Step 1 of 'afternoon snack': slice bread
Step 2 of 'afternoon snack': spread peanut butted
Step 3 of 'afternoon snack': eat sandwich

API Documentation

Object Name	Description
association_proxy(target_collection, attr, *, [creator, getset_factory, proxy_factory, proxy_bulk_set, info, cascade_scalar_deletes, create_on_none_assignment, init, repr, default, default_factory, compare, kw_only])	Return a Python property implementing a view of a target attribute which references an attribute on members of the target.
AssociationProxy	A descriptor that presents a read/write view of an object attribute.
AssociationProxyExtensionType
AssociationProxyInstance	A per-class object that serves class- and object-specific results.
ColumnAssociationProxyInstance	an AssociationProxyInstance that has a database column as a target.
ObjectAssociationProxyInstance	an AssociationProxyInstance that has an object as a target.

function sqlalchemy.ext.associationproxy.association_proxy(target_collection: str, attr: str, *, creator: _CreatorProtocol | None = None, getset_factory: _GetSetFactoryProtocol | None = None, proxy_factory: _ProxyFactoryProtocol | None = None, proxy_bulk_set: _ProxyBulkSetProtocol | None = None, info: _InfoType | None = None, cascade_scalar_deletes: bool = False, create_on_none_assignment: bool = False, init: _NoArg | bool = _NoArg.NO_ARG, repr: _NoArg | bool = _NoArg.NO_ARG, default: Any | None = _NoArg.NO_ARG, default_factory: _NoArg | Callable[[], _T] = _NoArg.NO_ARG, compare: _NoArg | bool = _NoArg.NO_ARG, kw_only: _NoArg | bool = _NoArg.NO_ARG) → AssociationProxy[Any]

Return a Python property implementing a view of a target attribute which references an attribute on members of the target.

The returned value is an instance of AssociationProxy.

Implements a Python property representing a relationship as a collection of simpler values, or a scalar value. The proxied property will mimic the collection type of the target (list, dict or set), or, in the case of a one to one relationship, a simple scalar value.

Parameters:

• target_collection – Name of the attribute that is the immediate target. This attribute is typically mapped by relationship() to link to a target collection, but can also be a many-to-one or non-scalar relationship.

• attr – Attribute on the associated instance or instances that are available on instances of the target object.

• creator –

  optional.

  Defines custom behavior when new items are added to the proxied collection.

  By default, adding new items to the collection will trigger a construction of an instance of the target object, passing the given item as a positional argument to the target constructor. For cases where this isn’t sufficient, association_proxy.creator can supply a callable that will construct the object in the appropriate way, given the item that was passed.

  For list- and set- oriented collections, a single argument is passed to the callable. For dictionary oriented collections, two arguments are passed, corresponding to the key and value.

  The association_proxy.creator callable is also invoked for scalar (i.e. many-to-one, one-to-one) relationships. If the current value of the target relationship attribute is None, the callable is used to construct a new object. If an object value already exists, the given attribute value is populated onto that object.

  See also

  Creation of New Values

• cascade_scalar_deletes –

  when True, indicates that setting the proxied value to None, or deleting it via del, should also remove the source object. Only applies to scalar attributes. Normally, removing the proxied target will not remove the proxy source, as this object may have other state that is still to be kept.

  New in version 1.3.

  See also

  Cascading Scalar Deletes - complete usage example

• create_on_none_assignment –

  when True, indicates that setting the proxied value to None should create the source object if it does not exist, using the creator. Only applies to scalar attributes. This is mutually exclusive vs. the assocation_proxy.cascade_scalar_deletes.

  New in version 2.0.18.

• init –

  Specific to Declarative Dataclass Mapping, specifies if the mapped attribute should be part of the __init__() method as generated by the dataclass process.

  New in version 2.0.0b4.

• repr –

  Specific to Declarative Dataclass Mapping, specifies if the attribute established by this AssociationProxy should be part of the __repr__() method as generated by the dataclass process.

  New in version 2.0.0b4.

• default_factory –

  Specific to Declarative Dataclass Mapping, specifies a default-value generation function that will take place as part of the __init__() method as generated by the dataclass process.

  New in version 2.0.0b4.

• compare –

  Specific to Declarative Dataclass Mapping, indicates if this field should be included in comparison operations when generating the __eq__() and __ne__() methods for the mapped class.

  New in version 2.0.0b4.

• kw_only –

  Specific to Declarative Dataclass Mapping, indicates if this field should be marked as keyword-only when generating the __init__() method as generated by the dataclass process.

  New in version 2.0.0b4.

• info – optional, will be assigned to AssociationProxy.info if present.

The following additional parameters involve injection of custom behaviors within the AssociationProxy object and are for advanced use only:

Parameters:

• getset_factory –

  Optional. Proxied attribute access is automatically handled by routines that get and set values based on the attr argument for this proxy.

  If you would like to customize this behavior, you may supply a getset_factory callable that produces a tuple of getter and setter functions. The factory is called with two arguments, the abstract type of the underlying collection and this proxy instance.

• proxy_factory – Optional. The type of collection to emulate is determined by sniffing the target collection. If your collection type can’t be determined by duck typing or you’d like to use a different collection implementation, you may supply a factory function to produce those collections. Only applicable to non-scalar relationships.

• proxy_bulk_set – Optional, use with proxy_factory.

class sqlalchemy.ext.associationproxy.AssociationProxy

A descriptor that presents a read/write view of an object attribute.

Members

__init__(), cascade_scalar_deletes, create_on_none_assignment, creator, extension_type, for_class(), getset_factory, info, is_aliased_class, is_attribute, is_bundle, is_clause_element, is_instance, is_mapper, is_property, is_selectable, key, proxy_bulk_set, proxy_factory, target_collection, value_attr

Class signature

class sqlalchemy.ext.associationproxy.AssociationProxy (sqlalchemy.orm.base.InspectionAttrInfo, sqlalchemy.orm.base.ORMDescriptor, sqlalchemy.orm._DCAttributeOptions, sqlalchemy.ext.associationproxy._AssociationProxyProtocol)

method sqlalchemy.ext.associationproxy.AssociationProxy.__init__(target_collection: str, attr: str, *, creator: _CreatorProtocol | None = None, getset_factory: _GetSetFactoryProtocol | None = None, proxy_factory: _ProxyFactoryProtocol | None = None, proxy_bulk_set: _ProxyBulkSetProtocol | None = None, info: _InfoType | None = None, cascade_scalar_deletes: bool = False, create_on_none_assignment: bool = False, attribute_options: _AttributeOptions | None = None)

Construct a new AssociationProxy.

The AssociationProxy object is typically constructed using the association_proxy() constructor function. See the description of association_proxy() for a description of all parameters.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.cascade_scalar_deletes: bool

attribute sqlalchemy.ext.associationproxy.AssociationProxy.create_on_none_assignment: bool

attribute sqlalchemy.ext.associationproxy.AssociationProxy.creator: _CreatorProtocol | None

attribute sqlalchemy.ext.associationproxy.AssociationProxy.extension_type: InspectionAttrExtensionType = 'ASSOCIATION_PROXY'

The extension type, if any. Defaults to NotExtension.NOT_EXTENSION

See also

HybridExtensionType

AssociationProxyExtensionType

method sqlalchemy.ext.associationproxy.AssociationProxy.for_class(class_: Type[Any], obj: object | None = None) → AssociationProxyInstance[_T]

Return the internal state local to a specific mapped class.

E.g., given a class User:

class User(Base):
    # ...

    keywords = association_proxy('kws', 'keyword')

If we access this AssociationProxy from Mapper.all_orm_descriptors, and we want to view the target class for this proxy as mapped by User:

inspect(User).all_orm_descriptors["keywords"].for_class(User).target_class

This returns an instance of AssociationProxyInstance that is specific to the User class. The AssociationProxy object remains agnostic of its parent class.

Parameters:

• class_ – the class that we are returning state for.

• obj – optional, an instance of the class that is required if the attribute refers to a polymorphic target, e.g. where we have to look at the type of the actual destination object to get the complete path.

New in version 1.3: - AssociationProxy no longer stores any state specific to a particular parent class; the state is now stored in per-class AssociationProxyInstance objects.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.getset_factory: _GetSetFactoryProtocol | None

attribute sqlalchemy.ext.associationproxy.AssociationProxy.info

inherited from the InspectionAttrInfo.info attribute of InspectionAttrInfo

Info dictionary associated with the object, allowing user-defined data to be associated with this InspectionAttr.

The dictionary is generated when first accessed. Alternatively, it can be specified as a constructor argument to the column_property(), relationship(), or composite() functions.

See also

QueryableAttribute.info

SchemaItem.info

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_aliased_class = False

inherited from the InspectionAttr.is_aliased_class attribute of InspectionAttr

True if this object is an instance of AliasedClass.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_attribute = True

True if this object is a Python descriptor.

This can refer to one of many types. Usually a QueryableAttribute which handles attributes events on behalf of a MapperProperty. But can also be an extension type such as AssociationProxy or hybrid_property. The InspectionAttr.extension_type will refer to a constant identifying the specific subtype.

See also

Mapper.all_orm_descriptors

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_bundle = False

inherited from the InspectionAttr.is_bundle attribute of InspectionAttr

True if this object is an instance of Bundle.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_clause_element = False

inherited from the InspectionAttr.is_clause_element attribute of InspectionAttr

True if this object is an instance of ClauseElement.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_instance = False

inherited from the InspectionAttr.is_instance attribute of InspectionAttr

True if this object is an instance of InstanceState.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_mapper = False

inherited from the InspectionAttr.is_mapper attribute of InspectionAttr

True if this object is an instance of Mapper.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_property = False

inherited from the InspectionAttr.is_property attribute of InspectionAttr

True if this object is an instance of MapperProperty.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.is_selectable = False

inherited from the InspectionAttr.is_selectable attribute of InspectionAttr

Return True if this object is an instance of Selectable.

attribute sqlalchemy.ext.associationproxy.AssociationProxy.key: str

attribute sqlalchemy.ext.associationproxy.AssociationProxy.proxy_bulk_set: _ProxyBulkSetProtocol | None

attribute sqlalchemy.ext.associationproxy.AssociationProxy.proxy_factory: _ProxyFactoryProtocol | None

attribute sqlalchemy.ext.associationproxy.AssociationProxy.target_collection: str

attribute sqlalchemy.ext.associationproxy.AssociationProxy.value_attr: str

class sqlalchemy.ext.associationproxy.AssociationProxyInstance

A per-class object that serves class- and object-specific results.

This is used by AssociationProxy when it is invoked in terms of a specific class or instance of a class, i.e. when it is used as a regular Python descriptor.

When referring to the AssociationProxy as a normal Python descriptor, the AssociationProxyInstance is the object that actually serves the information. Under normal circumstances, its presence is transparent:

>>> User.keywords.scalar
False

In the special case that the AssociationProxy object is being accessed directly, in order to get an explicit handle to the AssociationProxyInstance, use the AssociationProxy.for_class() method:

proxy_state = inspect(User).all_orm_descriptors["keywords"].for_class(User)

# view if proxy object is scalar or not
>>> proxy_state.scalar
False

New in version 1.3.

Members

__eq__(), __le__(), __lt__(), __ne__(), all_(), any(), any_(), asc(), attr, between(), bitwise_and(), bitwise_lshift(), bitwise_not(), bitwise_or(), bitwise_rshift(), bitwise_xor(), bool_op(), collate(), collection_class, concat(), contains(), delete(), desc(), distinct(), endswith(), for_proxy(), get(), has(), icontains(), iendswith(), ilike(), in_(), info, is_(), is_distinct_from(), is_not(), is_not_distinct_from(), isnot(), isnot_distinct_from(), istartswith(), like(), local_attr, match(), not_ilike(), not_in(), not_like(), notilike(), notin_(), notlike(), nulls_first(), nulls_last(), nullsfirst(), nullslast(), op(), operate(), parent, regexp_match(), regexp_replace(), remote_attr, reverse_operate(), scalar, set(), startswith(), target_class, timetuple

Class signature

class sqlalchemy.ext.associationproxy.AssociationProxyInstance (sqlalchemy.orm.base.SQLORMOperations)

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.__eq__(other: Any) → ColumnOperators

inherited from the sqlalchemy.sql.expression.ColumnOperators.__eq__ method of ColumnOperators

Implement the == operator.

In a column context, produces the clause a = b. If the target is None, produces a IS NULL.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.__le__(other: Any) → ColumnOperators

inherited from the sqlalchemy.sql.expression.ColumnOperators.__le__ method of ColumnOperators

Implement the <= operator.

In a column context, produces the clause a <= b.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.__lt__(other: Any) → ColumnOperators

inherited from the sqlalchemy.sql.expression.ColumnOperators.__lt__ method of ColumnOperators

Implement the < operator.

In a column context, produces the clause a < b.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.__ne__(other: Any) → ColumnOperators

inherited from the sqlalchemy.sql.expression.ColumnOperators.__ne__ method of ColumnOperators

Implement the != operator.

In a column context, produces the clause a != b. If the target is None, produces a IS NOT NULL.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.all_() → ColumnOperators

inherited from the ColumnOperators.all_() method of ColumnOperators

Produce an all_() clause against the parent object.

See the documentation for all_() for examples.

Note

be sure to not confuse the newer ColumnOperators.all_() method with the legacy version of this method, the Comparator.all() method that’s specific to ARRAY, which uses a different calling style.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.any(criterion: _ColumnExpressionArgument[bool] | None = None, **kwargs: Any) → ColumnElement[bool]

Produce a proxied ‘any’ expression using EXISTS.

This expression will be a composed product using the Comparator.any() and/or Comparator.has() operators of the underlying proxied attributes.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.any_() → ColumnOperators

inherited from the ColumnOperators.any_() method of ColumnOperators

Produce an any_() clause against the parent object.

See the documentation for any_() for examples.

Note

be sure to not confuse the newer ColumnOperators.any_() method with the legacy version of this method, the Comparator.any() method that’s specific to ARRAY, which uses a different calling style.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.asc() → ColumnOperators

inherited from the ColumnOperators.asc() method of ColumnOperators

Produce a asc() clause against the parent object.

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.attr

Return a tuple of (local_attr, remote_attr).

This attribute was originally intended to facilitate using the Query.join() method to join across the two relationships at once, however this makes use of a deprecated calling style.

To use select.join() or Query.join() with an association proxy, the current method is to make use of the AssociationProxyInstance.local_attr and AssociationProxyInstance.remote_attr attributes separately:

stmt = (
    select(Parent).
    join(Parent.proxied.local_attr).
    join(Parent.proxied.remote_attr)
)

A future release may seek to provide a more succinct join pattern for association proxy attributes.

See also

AssociationProxyInstance.local_attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.between(cleft: Any, cright: Any, symmetric: bool = False) → ColumnOperators

inherited from the ColumnOperators.between() method of ColumnOperators

Produce a between() clause against the parent object, given the lower and upper range.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.bitwise_and(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_and() method of ColumnOperators

Produce a bitwise AND operation, typically via the & operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.bitwise_lshift(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_lshift() method of ColumnOperators

Produce a bitwise LSHIFT operation, typically via the << operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.bitwise_not() → ColumnOperators

inherited from the ColumnOperators.bitwise_not() method of ColumnOperators

Produce a bitwise NOT operation, typically via the ~ operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.bitwise_or(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_or() method of ColumnOperators

Produce a bitwise OR operation, typically via the | operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.bitwise_rshift(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_rshift() method of ColumnOperators

Produce a bitwise RSHIFT operation, typically via the >> operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.bitwise_xor(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_xor() method of ColumnOperators

Produce a bitwise XOR operation, typically via the ^ operator, or # for PostgreSQL.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.bool_op(opstring: str, precedence: int = 0, python_impl: Callable[[...], Any] | None = None) → Callable[[Any], Operators]

inherited from the Operators.bool_op() method of Operators

Return a custom boolean operator.

This method is shorthand for calling Operators.op() and passing the Operators.op.is_comparison flag with True. A key advantage to using Operators.bool_op() is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.

See also

Operators.op()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.collate(collation: str) → ColumnOperators

inherited from the ColumnOperators.collate() method of ColumnOperators

Produce a collate() clause against the parent object, given the collation string.

See also

collate()

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.collection_class: Type[Any] | None

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.concat(other: Any) → ColumnOperators

inherited from the ColumnOperators.concat() method of ColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b, or uses the concat() operator on MySQL.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.contains(other: Any, **kw: Any) → ColumnOperators

inherited from the ColumnOperators.contains() method of ColumnOperators

Implement the ‘contains’ operator.

Produces a LIKE expression that tests against a match for the middle of a string value:

column LIKE '%' || <other> || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.contains("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.contains.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.contains.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.contains.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.contains("foo%bar", autoescape=True)

  Will render as:

  somecolumn LIKE '%' || :param || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.contains("foo/%bar", escape="^")

  Will render as:

  somecolumn LIKE '%' || :param || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.contains.autoescape:

  somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.endswith()

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.delete(obj: Any) → None

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.desc() → ColumnOperators

inherited from the ColumnOperators.desc() method of ColumnOperators

Produce a desc() clause against the parent object.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.distinct() → ColumnOperators

inherited from the ColumnOperators.distinct() method of ColumnOperators

Produce a distinct() clause against the parent object.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.endswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.endswith() method of ColumnOperators

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the end of a string value:

column LIKE '%' || <other>

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.endswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.endswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.endswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.endswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.endswith("foo%bar", autoescape=True)

  Will render as:

  somecolumn LIKE '%' || :param ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.endswith("foo/%bar", escape="^")

  Will render as:

  somecolumn LIKE '%' || :param ESCAPE '^'

  The parameter may also be combined with ColumnOperators.endswith.autoescape:

  somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.contains()

ColumnOperators.like()

classmethod sqlalchemy.ext.associationproxy.AssociationProxyInstance.for_proxy(parent: AssociationProxy[_T], owning_class: Type[Any], parent_instance: Any) → AssociationProxyInstance[_T]

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.get(obj: Any) → _T | None | AssociationProxyInstance[_T]

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.has(criterion: _ColumnExpressionArgument[bool] | None = None, **kwargs: Any) → ColumnElement[bool]

Produce a proxied ‘has’ expression using EXISTS.

This expression will be a composed product using the Comparator.any() and/or Comparator.has() operators of the underlying proxied attributes.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.icontains(other: Any, **kw: Any) → ColumnOperators

inherited from the ColumnOperators.icontains() method of ColumnOperators

Implement the icontains operator, e.g. case insensitive version of ColumnOperators.contains().

Produces a LIKE expression that tests against an insensitive match for the middle of a string value:

lower(column) LIKE '%' || lower(<other>) || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.icontains("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.icontains.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.icontains.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.icontains.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.icontains("foo%bar", autoescape=True)

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.icontains("foo/%bar", escape="^")

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.contains.autoescape:

  somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.contains()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.iendswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.iendswith() method of ColumnOperators

Implement the iendswith operator, e.g. case insensitive version of ColumnOperators.endswith().

Produces a LIKE expression that tests against an insensitive match for the end of a string value:

lower(column) LIKE '%' || lower(<other>)

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.iendswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.iendswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.iendswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.iendswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.iendswith("foo%bar", autoescape=True)

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.iendswith("foo/%bar", escape="^")

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'

  The parameter may also be combined with ColumnOperators.iendswith.autoescape:

  somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.endswith()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.ilike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.ilike() method of ColumnOperators

Implement the ilike operator, e.g. case insensitive LIKE.

In a column context, produces an expression either of the form:

lower(a) LIKE lower(other)

Or on backends that support the ILIKE operator:

a ILIKE other

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.ilike("%foobar%"))

Parameters:

• other – expression to be compared

• escape –

  optional escape character, renders the ESCAPE keyword, e.g.:

  somecolumn.ilike("foo/%bar", escape="/")

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.in_(other: Any) → ColumnOperators

inherited from the ColumnOperators.in_() method of ColumnOperators

Implement the in operator.

In a column context, produces the clause column IN <other>.

The given parameter other may be:

• A list of literal values, e.g.:

  stmt.where(column.in_([1, 2, 3]))

  In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:

  WHERE COL IN (?, ?, ?)

• A list of tuples may be provided if the comparison is against a tuple_() containing multiple expressions:

  from sqlalchemy import tuple_
  stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))

• An empty list, e.g.:

  stmt.where(column.in_([]))

  In this calling form, the expression renders an “empty set” expression. These expressions are tailored to individual backends and are generally trying to get an empty SELECT statement as a subquery. Such as on SQLite, the expression is:

  WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

  Changed in version 1.4: empty IN expressions now use an execution-time generated SELECT subquery in all cases.

• A bound parameter, e.g. bindparam(), may be used if it includes the bindparam.expanding flag:

  stmt.where(column.in_(bindparam('value', expanding=True)))

  In this calling form, the expression renders a special non-SQL placeholder expression that looks like:

  WHERE COL IN ([EXPANDING_value])

  This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:

  connection.execute(stmt, {"value": [1, 2, 3]})

  The database would be passed a bound parameter for each value:

  WHERE COL IN (?, ?, ?)

  New in version 1.2: added “expanding” bound parameters

  If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:

  WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

  New in version 1.3: “expanding” bound parameters now support empty lists

• a select() construct, which is usually a correlated scalar select:

  stmt.where(
      column.in_(
          select(othertable.c.y).
          where(table.c.x == othertable.c.x)
      )
  )

  In this calling form, ColumnOperators.in_() renders as given:

  WHERE COL IN (SELECT othertable.y
  FROM othertable WHERE othertable.x = table.x)

Parameters:

other – a list of literals, a select() construct, or a bindparam() construct that includes the bindparam.expanding flag set to True.

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.info

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.is_(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_() method of ColumnOperators

Implement the IS operator.

Normally, IS is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS may be desirable if comparing to boolean values on certain platforms.

See also

ColumnOperators.is_not()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.is_distinct_from(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_distinct_from() method of ColumnOperators

Implement the IS DISTINCT FROM operator.

Renders “a IS DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS NOT b”.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.is_not(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_not() method of ColumnOperators

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.is_()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.is_not_distinct_from(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_not_distinct_from() method of ColumnOperators

Implement the IS NOT DISTINCT FROM operator.

Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.

Changed in version 1.4: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.isnot(other: Any) → ColumnOperators

inherited from the ColumnOperators.isnot() method of ColumnOperators

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.is_()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.isnot_distinct_from(other: Any) → ColumnOperators

inherited from the ColumnOperators.isnot_distinct_from() method of ColumnOperators

Implement the IS NOT DISTINCT FROM operator.

Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.

Changed in version 1.4: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.istartswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.istartswith() method of ColumnOperators

Implement the istartswith operator, e.g. case insensitive version of ColumnOperators.startswith().

Produces a LIKE expression that tests against an insensitive match for the start of a string value:

lower(column) LIKE lower(<other>) || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.istartswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.istartswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.istartswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.istartswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.istartswith("foo%bar", autoescape=True)

  Will render as:

  lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.istartswith("foo/%bar", escape="^")

  Will render as:

  lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.istartswith.autoescape:

  somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.like(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.like() method of ColumnOperators

Implement the like operator.

In a column context, produces the expression:

a LIKE other

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.like("%foobar%"))

Parameters:

• other – expression to be compared

• escape –

  optional escape character, renders the ESCAPE keyword, e.g.:

  somecolumn.like("foo/%bar", escape="/")

See also

ColumnOperators.ilike()

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.local_attr

The ‘local’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.match(other: Any, **kwargs: Any) → ColumnOperators

inherited from the ColumnOperators.match() method of ColumnOperators

Implements a database-specific ‘match’ operator.

ColumnOperators.match() attempts to resolve to a MATCH-like function or operator provided by the backend. Examples include:

• PostgreSQL - renders x @@ plainto_tsquery(y)

  Changed in version 2.0: plainto_tsquery() is used instead of to_tsquery() for PostgreSQL now; for compatibility with other forms, see Full Text Search.

• MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

  See also

  match - MySQL specific construct with additional features.

• Oracle - renders CONTAINS(x, y)

• other backends may provide special implementations.

• Backends without any special implementation will emit the operator as “MATCH”. This is compatible with SQLite, for example.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.not_ilike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.not_ilike() method of ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with ColumnOperators.ilike(), i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.not_in(other: Any) → ColumnOperators

inherited from the ColumnOperators.not_in() method of ColumnOperators

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. The create_engine.empty_in_strategy may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and ColumnOperators.not_in() operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.not_like(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.not_like() method of ColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.notilike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.notilike() method of ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with ColumnOperators.ilike(), i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.notin_(other: Any) → ColumnOperators

inherited from the ColumnOperators.notin_() method of ColumnOperators

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. The create_engine.empty_in_strategy may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and ColumnOperators.not_in() operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.notlike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.notlike() method of ColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.nulls_first() → ColumnOperators

inherited from the ColumnOperators.nulls_first() method of ColumnOperators

Produce a nulls_first() clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.nulls_last() → ColumnOperators

inherited from the ColumnOperators.nulls_last() method of ColumnOperators

Produce a nulls_last() clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.nullsfirst() → ColumnOperators

inherited from the ColumnOperators.nullsfirst() method of ColumnOperators

Produce a nulls_first() clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.nullslast() → ColumnOperators

inherited from the ColumnOperators.nullslast() method of ColumnOperators

Produce a nulls_last() clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.op(opstring: str, precedence: int = 0, is_comparison: bool = False, return_type: Type[TypeEngine[Any]] | TypeEngine[Any] | None = None, python_impl: Callable[..., Any] | None = None) → Callable[[Any], Operators]

inherited from the Operators.op() method of Operators

Produce a generic operator function.

e.g.:

somecolumn.op("*")(5)

produces:

somecolumn * 5

This function can also be used to make bitwise operators explicit. For example:

somecolumn.op('&')(0xff)

is a bitwise AND of the value in somecolumn.

Parameters:

• opstring – a string which will be output as the infix operator between this element and the expression passed to the generated function.

• precedence –

  precedence which the database is expected to apply to the operator in SQL expressions. This integer value acts as a hint for the SQL compiler to know when explicit parenthesis should be rendered around a particular operation. A lower number will cause the expression to be parenthesized when applied against another operator with higher precedence. The default value of 0 is lower than all operators except for the comma (,) and AS operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.

  See also

  I’m using op() to generate a custom operator and my parenthesis are not coming out correctly - detailed description of how the SQLAlchemy SQL compiler renders parenthesis

• is_comparison –

  legacy; if True, the operator will be considered as a “comparison” operator, that is which evaluates to a boolean true/false value, like ==, >, etc. This flag is provided so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition.

  Using the is_comparison parameter is superseded by using the Operators.bool_op() method instead; this more succinct operator sets this parameter automatically, but also provides correct PEP 484 typing support as the returned object will express a “boolean” datatype, i.e. BinaryExpression[bool].

• return_type – a TypeEngine class or object that will force the return type of an expression produced by this operator to be of that type. By default, operators that specify Operators.op.is_comparison will resolve to Boolean, and those that do not will be of the same type as the left-hand operand.

• python_impl –

  an optional Python function that can evaluate two Python values in the same way as this operator works when run on the database server. Useful for in-Python SQL expression evaluation functions, such as for ORM hybrid attributes, and the ORM “evaluator” used to match objects in a session after a multi-row update or delete.

  e.g.:

  >>> expr = column('x').op('+', python_impl=lambda a, b: a + b)('y')

  The operator for the above expression will also work for non-SQL left and right objects:

  >>> expr.operator(5, 10)
  15

  New in version 2.0.

See also

Operators.bool_op()

Redefining and Creating New Operators

Using custom operators in join conditions

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.operate(op: OperatorType, *other: Any, **kwargs: Any) → Operators

inherited from the Operators.operate() method of Operators

Operate on an argument.

This is the lowest level of operation, raises NotImplementedError by default.

Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding ColumnOperators to apply func.lower() to the left and right side:

class MyComparator(ColumnOperators):
    def operate(self, op, other, **kwargs):
        return op(func.lower(self), func.lower(other), **kwargs)

Parameters:

• op – Operator callable.

• *other – the ‘other’ side of the operation. Will be a single scalar for most operations.

• **kwargs – modifiers. These may be passed by special operators such as ColumnOperators.contains().

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.parent: _AssociationProxyProtocol[_T]

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.regexp_match(pattern: Any, flags: str | None = None) → ColumnOperators

inherited from the ColumnOperators.regexp_match() method of ColumnOperators

Implements a database-specific ‘regexp match’ operator.

E.g.:

stmt = select(table.c.some_column).where(
    table.c.some_column.regexp_match('^(b|c)')
)

ColumnOperators.regexp_match() attempts to resolve to a REGEXP-like function or operator provided by the backend, however the specific regular expression syntax and flags available are not backend agnostic.

Examples include:

• PostgreSQL - renders x ~ y or x !~ y when negated.

• Oracle - renders REGEXP_LIKE(x, y)

• SQLite - uses SQLite’s REGEXP placeholder operator and calls into the Python re.match() builtin.

• other backends may provide special implementations.

• Backends without any special implementation will emit the operator as “REGEXP” or “NOT REGEXP”. This is compatible with SQLite and MySQL, for example.

Regular expression support is currently implemented for Oracle, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary.

Parameters:

• pattern – The regular expression pattern string or column clause.

• flags – Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern. When using the ignore case flag ‘i’ in PostgreSQL, the ignore case regexp match operator ~* or !~* will be used.

New in version 1.4.

Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.

See also

ColumnOperators.regexp_replace()

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.regexp_replace(pattern: Any, replacement: Any, flags: str | None = None) → ColumnOperators

inherited from the ColumnOperators.regexp_replace() method of ColumnOperators

Implements a database-specific ‘regexp replace’ operator.

E.g.:

stmt = select(
    table.c.some_column.regexp_replace(
        'b(..)',
        'X
Y',
        flags='g'
    )
)

ColumnOperators.regexp_replace() attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the function REGEXP_REPLACE(). However, the specific regular expression syntax and flags available are not backend agnostic.

Regular expression replacement support is currently implemented for Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary.

Parameters:

• pattern – The regular expression pattern string or column clause.

• pattern – The replacement string or column clause.

• flags – Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern.

New in version 1.4.

Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.

See also

ColumnOperators.regexp_match()

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.remote_attr

The ‘remote’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.local_attr

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.reverse_operate(op: OperatorType, other: Any, **kwargs: Any) → Operators

inherited from the Operators.reverse_operate() method of Operators

Reverse operate on an argument.

Usage is the same as operate().

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.scalar

Return True if this AssociationProxyInstance proxies a scalar relationship on the local side.

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.set(obj: Any, values: _T) → None

method sqlalchemy.ext.associationproxy.AssociationProxyInstance.startswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.startswith() method of ColumnOperators

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the start of a string value:

column LIKE <other> || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.startswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.startswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.startswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.startswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.startswith("foo%bar", autoescape=True)

  Will render as:

  somecolumn LIKE :param || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.startswith("foo/%bar", escape="^")

  Will render as:

  somecolumn LIKE :param || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.startswith.autoescape:

  somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.endswith()

ColumnOperators.contains()

ColumnOperators.like()

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.target_class: Type[Any]

The intermediary class handled by this AssociationProxyInstance.

Intercepted append/set/assignment events will result in the generation of new instances of this class.

attribute sqlalchemy.ext.associationproxy.AssociationProxyInstance.timetuple: Literal[None] = None

inherited from the ColumnOperators.timetuple attribute of ColumnOperators

Hack, allows datetime objects to be compared on the LHS.

class sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance

an AssociationProxyInstance that has an object as a target.

Members

__le__(), __lt__(), all_(), any(), any_(), asc(), attr, between(), bitwise_and(), bitwise_lshift(), bitwise_not(), bitwise_or(), bitwise_rshift(), bitwise_xor(), bool_op(), collate(), concat(), contains(), desc(), distinct(), endswith(), has(), icontains(), iendswith(), ilike(), in_(), is_(), is_distinct_from(), is_not(), is_not_distinct_from(), isnot(), isnot_distinct_from(), istartswith(), like(), local_attr, match(), not_ilike(), not_in(), not_like(), notilike(), notin_(), notlike(), nulls_first(), nulls_last(), nullsfirst(), nullslast(), op(), operate(), regexp_match(), regexp_replace(), remote_attr, reverse_operate(), scalar, startswith(), target_class, timetuple

Class signature

class sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance (sqlalchemy.ext.associationproxy.AssociationProxyInstance)

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.__le__(other: Any) → ColumnOperators

inherited from the sqlalchemy.sql.expression.ColumnOperators.__le__ method of ColumnOperators

Implement the <= operator.

In a column context, produces the clause a <= b.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.__lt__(other: Any) → ColumnOperators

inherited from the sqlalchemy.sql.expression.ColumnOperators.__lt__ method of ColumnOperators

Implement the < operator.

In a column context, produces the clause a < b.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.all_() → ColumnOperators

inherited from the ColumnOperators.all_() method of ColumnOperators

Produce an all_() clause against the parent object.

See the documentation for all_() for examples.

Note

be sure to not confuse the newer ColumnOperators.all_() method with the legacy version of this method, the Comparator.all() method that’s specific to ARRAY, which uses a different calling style.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.any(criterion: _ColumnExpressionArgument[bool] | None = None, **kwargs: Any) → ColumnElement[bool]

inherited from the AssociationProxyInstance.any() method of AssociationProxyInstance

Produce a proxied ‘any’ expression using EXISTS.

This expression will be a composed product using the Comparator.any() and/or Comparator.has() operators of the underlying proxied attributes.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.any_() → ColumnOperators

inherited from the ColumnOperators.any_() method of ColumnOperators

Produce an any_() clause against the parent object.

See the documentation for any_() for examples.

Note

be sure to not confuse the newer ColumnOperators.any_() method with the legacy version of this method, the Comparator.any() method that’s specific to ARRAY, which uses a different calling style.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.asc() → ColumnOperators

inherited from the ColumnOperators.asc() method of ColumnOperators

Produce a asc() clause against the parent object.

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.attr

inherited from the AssociationProxyInstance.attr attribute of AssociationProxyInstance

Return a tuple of (local_attr, remote_attr).

This attribute was originally intended to facilitate using the Query.join() method to join across the two relationships at once, however this makes use of a deprecated calling style.

To use select.join() or Query.join() with an association proxy, the current method is to make use of the AssociationProxyInstance.local_attr and AssociationProxyInstance.remote_attr attributes separately:

stmt = (
    select(Parent).
    join(Parent.proxied.local_attr).
    join(Parent.proxied.remote_attr)
)

A future release may seek to provide a more succinct join pattern for association proxy attributes.

See also

AssociationProxyInstance.local_attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.between(cleft: Any, cright: Any, symmetric: bool = False) → ColumnOperators

inherited from the ColumnOperators.between() method of ColumnOperators

Produce a between() clause against the parent object, given the lower and upper range.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.bitwise_and(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_and() method of ColumnOperators

Produce a bitwise AND operation, typically via the & operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.bitwise_lshift(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_lshift() method of ColumnOperators

Produce a bitwise LSHIFT operation, typically via the << operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.bitwise_not() → ColumnOperators

inherited from the ColumnOperators.bitwise_not() method of ColumnOperators

Produce a bitwise NOT operation, typically via the ~ operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.bitwise_or(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_or() method of ColumnOperators

Produce a bitwise OR operation, typically via the | operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.bitwise_rshift(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_rshift() method of ColumnOperators

Produce a bitwise RSHIFT operation, typically via the >> operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.bitwise_xor(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_xor() method of ColumnOperators

Produce a bitwise XOR operation, typically via the ^ operator, or # for PostgreSQL.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.bool_op(opstring: str, precedence: int = 0, python_impl: Callable[[...], Any] | None = None) → Callable[[Any], Operators]

inherited from the Operators.bool_op() method of Operators

Return a custom boolean operator.

This method is shorthand for calling Operators.op() and passing the Operators.op.is_comparison flag with True. A key advantage to using Operators.bool_op() is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.

See also

Operators.op()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.collate(collation: str) → ColumnOperators

inherited from the ColumnOperators.collate() method of ColumnOperators

Produce a collate() clause against the parent object, given the collation string.

See also

collate()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.concat(other: Any) → ColumnOperators

inherited from the ColumnOperators.concat() method of ColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b, or uses the concat() operator on MySQL.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.contains(other: Any, **kw: Any) → ColumnElement[bool]

Produce a proxied ‘contains’ expression using EXISTS.

This expression will be a composed product using the Comparator.any(), Comparator.has(), and/or Comparator.contains() operators of the underlying proxied attributes.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.desc() → ColumnOperators

inherited from the ColumnOperators.desc() method of ColumnOperators

Produce a desc() clause against the parent object.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.distinct() → ColumnOperators

inherited from the ColumnOperators.distinct() method of ColumnOperators

Produce a distinct() clause against the parent object.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.endswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.endswith() method of ColumnOperators

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the end of a string value:

column LIKE '%' || <other>

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.endswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.endswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.endswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.endswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.endswith("foo%bar", autoescape=True)

  Will render as:

  somecolumn LIKE '%' || :param ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.endswith("foo/%bar", escape="^")

  Will render as:

  somecolumn LIKE '%' || :param ESCAPE '^'

  The parameter may also be combined with ColumnOperators.endswith.autoescape:

  somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.contains()

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.has(criterion: _ColumnExpressionArgument[bool] | None = None, **kwargs: Any) → ColumnElement[bool]

inherited from the AssociationProxyInstance.has() method of AssociationProxyInstance

Produce a proxied ‘has’ expression using EXISTS.

This expression will be a composed product using the Comparator.any() and/or Comparator.has() operators of the underlying proxied attributes.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.icontains(other: Any, **kw: Any) → ColumnOperators

inherited from the ColumnOperators.icontains() method of ColumnOperators

Implement the icontains operator, e.g. case insensitive version of ColumnOperators.contains().

Produces a LIKE expression that tests against an insensitive match for the middle of a string value:

lower(column) LIKE '%' || lower(<other>) || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.icontains("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.icontains.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.icontains.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.icontains.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.icontains("foo%bar", autoescape=True)

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.icontains("foo/%bar", escape="^")

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.contains.autoescape:

  somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.contains()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.iendswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.iendswith() method of ColumnOperators

Implement the iendswith operator, e.g. case insensitive version of ColumnOperators.endswith().

Produces a LIKE expression that tests against an insensitive match for the end of a string value:

lower(column) LIKE '%' || lower(<other>)

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.iendswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.iendswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.iendswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.iendswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.iendswith("foo%bar", autoescape=True)

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.iendswith("foo/%bar", escape="^")

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'

  The parameter may also be combined with ColumnOperators.iendswith.autoescape:

  somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.endswith()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.ilike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.ilike() method of ColumnOperators

Implement the ilike operator, e.g. case insensitive LIKE.

In a column context, produces an expression either of the form:

lower(a) LIKE lower(other)

Or on backends that support the ILIKE operator:

a ILIKE other

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.ilike("%foobar%"))

Parameters:

• other – expression to be compared

• escape –

  optional escape character, renders the ESCAPE keyword, e.g.:

  somecolumn.ilike("foo/%bar", escape="/")

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.in_(other: Any) → ColumnOperators

inherited from the ColumnOperators.in_() method of ColumnOperators

Implement the in operator.

In a column context, produces the clause column IN <other>.

The given parameter other may be:

• A list of literal values, e.g.:

  stmt.where(column.in_([1, 2, 3]))

  In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:

  WHERE COL IN (?, ?, ?)

• A list of tuples may be provided if the comparison is against a tuple_() containing multiple expressions:

  from sqlalchemy import tuple_
  stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))

• An empty list, e.g.:

  stmt.where(column.in_([]))

  In this calling form, the expression renders an “empty set” expression. These expressions are tailored to individual backends and are generally trying to get an empty SELECT statement as a subquery. Such as on SQLite, the expression is:

  WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

  Changed in version 1.4: empty IN expressions now use an execution-time generated SELECT subquery in all cases.

• A bound parameter, e.g. bindparam(), may be used if it includes the bindparam.expanding flag:

  stmt.where(column.in_(bindparam('value', expanding=True)))

  In this calling form, the expression renders a special non-SQL placeholder expression that looks like:

  WHERE COL IN ([EXPANDING_value])

  This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:

  connection.execute(stmt, {"value": [1, 2, 3]})

  The database would be passed a bound parameter for each value:

  WHERE COL IN (?, ?, ?)

  New in version 1.2: added “expanding” bound parameters

  If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:

  WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

  New in version 1.3: “expanding” bound parameters now support empty lists

• a select() construct, which is usually a correlated scalar select:

  stmt.where(
      column.in_(
          select(othertable.c.y).
          where(table.c.x == othertable.c.x)
      )
  )

  In this calling form, ColumnOperators.in_() renders as given:

  WHERE COL IN (SELECT othertable.y
  FROM othertable WHERE othertable.x = table.x)

Parameters:

other – a list of literals, a select() construct, or a bindparam() construct that includes the bindparam.expanding flag set to True.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.is_(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_() method of ColumnOperators

Implement the IS operator.

Normally, IS is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS may be desirable if comparing to boolean values on certain platforms.

See also

ColumnOperators.is_not()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.is_distinct_from(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_distinct_from() method of ColumnOperators

Implement the IS DISTINCT FROM operator.

Renders “a IS DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS NOT b”.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.is_not(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_not() method of ColumnOperators

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.is_()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.is_not_distinct_from(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_not_distinct_from() method of ColumnOperators

Implement the IS NOT DISTINCT FROM operator.

Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.

Changed in version 1.4: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.isnot(other: Any) → ColumnOperators

inherited from the ColumnOperators.isnot() method of ColumnOperators

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.is_()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.isnot_distinct_from(other: Any) → ColumnOperators

inherited from the ColumnOperators.isnot_distinct_from() method of ColumnOperators

Implement the IS NOT DISTINCT FROM operator.

Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.

Changed in version 1.4: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.istartswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.istartswith() method of ColumnOperators

Implement the istartswith operator, e.g. case insensitive version of ColumnOperators.startswith().

Produces a LIKE expression that tests against an insensitive match for the start of a string value:

lower(column) LIKE lower(<other>) || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.istartswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.istartswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.istartswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.istartswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.istartswith("foo%bar", autoescape=True)

  Will render as:

  lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.istartswith("foo/%bar", escape="^")

  Will render as:

  lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.istartswith.autoescape:

  somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.like(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.like() method of ColumnOperators

Implement the like operator.

In a column context, produces the expression:

a LIKE other

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.like("%foobar%"))

Parameters:

• other – expression to be compared

• escape –

  optional escape character, renders the ESCAPE keyword, e.g.:

  somecolumn.like("foo/%bar", escape="/")

See also

ColumnOperators.ilike()

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.local_attr

inherited from the AssociationProxyInstance.local_attr attribute of AssociationProxyInstance

The ‘local’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.match(other: Any, **kwargs: Any) → ColumnOperators

inherited from the ColumnOperators.match() method of ColumnOperators

Implements a database-specific ‘match’ operator.

ColumnOperators.match() attempts to resolve to a MATCH-like function or operator provided by the backend. Examples include:

• PostgreSQL - renders x @@ plainto_tsquery(y)

  Changed in version 2.0: plainto_tsquery() is used instead of to_tsquery() for PostgreSQL now; for compatibility with other forms, see Full Text Search.

• MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

  See also

  match - MySQL specific construct with additional features.

• Oracle - renders CONTAINS(x, y)

• other backends may provide special implementations.

• Backends without any special implementation will emit the operator as “MATCH”. This is compatible with SQLite, for example.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.not_ilike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.not_ilike() method of ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with ColumnOperators.ilike(), i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.not_in(other: Any) → ColumnOperators

inherited from the ColumnOperators.not_in() method of ColumnOperators

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. The create_engine.empty_in_strategy may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and ColumnOperators.not_in() operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.not_like(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.not_like() method of ColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.notilike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.notilike() method of ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with ColumnOperators.ilike(), i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.notin_(other: Any) → ColumnOperators

inherited from the ColumnOperators.notin_() method of ColumnOperators

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. The create_engine.empty_in_strategy may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and ColumnOperators.not_in() operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.notlike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.notlike() method of ColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.nulls_first() → ColumnOperators

inherited from the ColumnOperators.nulls_first() method of ColumnOperators

Produce a nulls_first() clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.nulls_last() → ColumnOperators

inherited from the ColumnOperators.nulls_last() method of ColumnOperators

Produce a nulls_last() clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.nullsfirst() → ColumnOperators

inherited from the ColumnOperators.nullsfirst() method of ColumnOperators

Produce a nulls_first() clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.nullslast() → ColumnOperators

inherited from the ColumnOperators.nullslast() method of ColumnOperators

Produce a nulls_last() clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.op(opstring: str, precedence: int = 0, is_comparison: bool = False, return_type: Type[TypeEngine[Any]] | TypeEngine[Any] | None = None, python_impl: Callable[..., Any] | None = None) → Callable[[Any], Operators]

inherited from the Operators.op() method of Operators

Produce a generic operator function.

e.g.:

somecolumn.op("*")(5)

produces:

somecolumn * 5

This function can also be used to make bitwise operators explicit. For example:

somecolumn.op('&')(0xff)

is a bitwise AND of the value in somecolumn.

Parameters:

• opstring – a string which will be output as the infix operator between this element and the expression passed to the generated function.

• precedence –

  precedence which the database is expected to apply to the operator in SQL expressions. This integer value acts as a hint for the SQL compiler to know when explicit parenthesis should be rendered around a particular operation. A lower number will cause the expression to be parenthesized when applied against another operator with higher precedence. The default value of 0 is lower than all operators except for the comma (,) and AS operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.

  See also

  I’m using op() to generate a custom operator and my parenthesis are not coming out correctly - detailed description of how the SQLAlchemy SQL compiler renders parenthesis

• is_comparison –

  legacy; if True, the operator will be considered as a “comparison” operator, that is which evaluates to a boolean true/false value, like ==, >, etc. This flag is provided so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition.

  Using the is_comparison parameter is superseded by using the Operators.bool_op() method instead; this more succinct operator sets this parameter automatically, but also provides correct PEP 484 typing support as the returned object will express a “boolean” datatype, i.e. BinaryExpression[bool].

• return_type – a TypeEngine class or object that will force the return type of an expression produced by this operator to be of that type. By default, operators that specify Operators.op.is_comparison will resolve to Boolean, and those that do not will be of the same type as the left-hand operand.

• python_impl –

  an optional Python function that can evaluate two Python values in the same way as this operator works when run on the database server. Useful for in-Python SQL expression evaluation functions, such as for ORM hybrid attributes, and the ORM “evaluator” used to match objects in a session after a multi-row update or delete.

  e.g.:

  >>> expr = column('x').op('+', python_impl=lambda a, b: a + b)('y')

  The operator for the above expression will also work for non-SQL left and right objects:

  >>> expr.operator(5, 10)
  15

  New in version 2.0.

See also

Operators.bool_op()

Redefining and Creating New Operators

Using custom operators in join conditions

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.operate(op: OperatorType, *other: Any, **kwargs: Any) → Operators

inherited from the Operators.operate() method of Operators

Operate on an argument.

This is the lowest level of operation, raises NotImplementedError by default.

Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding ColumnOperators to apply func.lower() to the left and right side:

class MyComparator(ColumnOperators):
    def operate(self, op, other, **kwargs):
        return op(func.lower(self), func.lower(other), **kwargs)

Parameters:

• op – Operator callable.

• *other – the ‘other’ side of the operation. Will be a single scalar for most operations.

• **kwargs – modifiers. These may be passed by special operators such as ColumnOperators.contains().

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.regexp_match(pattern: Any, flags: str | None = None) → ColumnOperators

inherited from the ColumnOperators.regexp_match() method of ColumnOperators

Implements a database-specific ‘regexp match’ operator.

E.g.:

stmt = select(table.c.some_column).where(
    table.c.some_column.regexp_match('^(b|c)')
)

ColumnOperators.regexp_match() attempts to resolve to a REGEXP-like function or operator provided by the backend, however the specific regular expression syntax and flags available are not backend agnostic.

Examples include:

• PostgreSQL - renders x ~ y or x !~ y when negated.

• Oracle - renders REGEXP_LIKE(x, y)

• SQLite - uses SQLite’s REGEXP placeholder operator and calls into the Python re.match() builtin.

• other backends may provide special implementations.

• Backends without any special implementation will emit the operator as “REGEXP” or “NOT REGEXP”. This is compatible with SQLite and MySQL, for example.

Regular expression support is currently implemented for Oracle, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary.

Parameters:

• pattern – The regular expression pattern string or column clause.

• flags – Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern. When using the ignore case flag ‘i’ in PostgreSQL, the ignore case regexp match operator ~* or !~* will be used.

New in version 1.4.

Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.

See also

ColumnOperators.regexp_replace()

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.regexp_replace(pattern: Any, replacement: Any, flags: str | None = None) → ColumnOperators

inherited from the ColumnOperators.regexp_replace() method of ColumnOperators

Implements a database-specific ‘regexp replace’ operator.

E.g.:

stmt = select(
    table.c.some_column.regexp_replace(
        'b(..)',
        'X
Y',
        flags='g'
    )
)

ColumnOperators.regexp_replace() attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the function REGEXP_REPLACE(). However, the specific regular expression syntax and flags available are not backend agnostic.

Regular expression replacement support is currently implemented for Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary.

Parameters:

• pattern – The regular expression pattern string or column clause.

• pattern – The replacement string or column clause.

• flags – Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern.

New in version 1.4.

Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.

See also

ColumnOperators.regexp_match()

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.remote_attr

inherited from the AssociationProxyInstance.remote_attr attribute of AssociationProxyInstance

The ‘remote’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.local_attr

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.reverse_operate(op: OperatorType, other: Any, **kwargs: Any) → Operators

inherited from the Operators.reverse_operate() method of Operators

Reverse operate on an argument.

Usage is the same as operate().

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.scalar

inherited from the AssociationProxyInstance.scalar attribute of AssociationProxyInstance

Return True if this AssociationProxyInstance proxies a scalar relationship on the local side.

method sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.startswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.startswith() method of ColumnOperators

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the start of a string value:

column LIKE <other> || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.startswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.startswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.startswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.startswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.startswith("foo%bar", autoescape=True)

  Will render as:

  somecolumn LIKE :param || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.startswith("foo/%bar", escape="^")

  Will render as:

  somecolumn LIKE :param || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.startswith.autoescape:

  somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.endswith()

ColumnOperators.contains()

ColumnOperators.like()

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.target_class: Type[Any]

The intermediary class handled by this AssociationProxyInstance.

Intercepted append/set/assignment events will result in the generation of new instances of this class.

attribute sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.timetuple: Literal[None] = None

inherited from the ColumnOperators.timetuple attribute of ColumnOperators

Hack, allows datetime objects to be compared on the LHS.

class sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance

an AssociationProxyInstance that has a database column as a target.

Members

__le__(), __lt__(), __ne__(), all_(), any(), any_(), asc(), attr, between(), bitwise_and(), bitwise_lshift(), bitwise_not(), bitwise_or(), bitwise_rshift(), bitwise_xor(), bool_op(), collate(), concat(), contains(), desc(), distinct(), endswith(), has(), icontains(), iendswith(), ilike(), in_(), is_(), is_distinct_from(), is_not(), is_not_distinct_from(), isnot(), isnot_distinct_from(), istartswith(), like(), local_attr, match(), not_ilike(), not_in(), not_like(), notilike(), notin_(), notlike(), nulls_first(), nulls_last(), nullsfirst(), nullslast(), op(), operate(), regexp_match(), regexp_replace(), remote_attr, reverse_operate(), scalar, startswith(), target_class, timetuple

Class signature

class sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance (sqlalchemy.ext.associationproxy.AssociationProxyInstance)

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.__le__(other: Any) → ColumnOperators

inherited from the sqlalchemy.sql.expression.ColumnOperators.__le__ method of ColumnOperators

Implement the <= operator.

In a column context, produces the clause a <= b.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.__lt__(other: Any) → ColumnOperators

inherited from the sqlalchemy.sql.expression.ColumnOperators.__lt__ method of ColumnOperators

Implement the < operator.

In a column context, produces the clause a < b.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.__ne__(other: Any) → ColumnOperators

inherited from the sqlalchemy.sql.expression.ColumnOperators.__ne__ method of ColumnOperators

Implement the != operator.

In a column context, produces the clause a != b. If the target is None, produces a IS NOT NULL.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.all_() → ColumnOperators

inherited from the ColumnOperators.all_() method of ColumnOperators

Produce an all_() clause against the parent object.

See the documentation for all_() for examples.

Note

be sure to not confuse the newer ColumnOperators.all_() method with the legacy version of this method, the Comparator.all() method that’s specific to ARRAY, which uses a different calling style.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.any(criterion: _ColumnExpressionArgument[bool] | None = None, **kwargs: Any) → ColumnElement[bool]

inherited from the AssociationProxyInstance.any() method of AssociationProxyInstance

Produce a proxied ‘any’ expression using EXISTS.

This expression will be a composed product using the Comparator.any() and/or Comparator.has() operators of the underlying proxied attributes.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.any_() → ColumnOperators

inherited from the ColumnOperators.any_() method of ColumnOperators

Produce an any_() clause against the parent object.

See the documentation for any_() for examples.

Note

be sure to not confuse the newer ColumnOperators.any_() method with the legacy version of this method, the Comparator.any() method that’s specific to ARRAY, which uses a different calling style.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.asc() → ColumnOperators

inherited from the ColumnOperators.asc() method of ColumnOperators

Produce a asc() clause against the parent object.

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.attr

inherited from the AssociationProxyInstance.attr attribute of AssociationProxyInstance

Return a tuple of (local_attr, remote_attr).

This attribute was originally intended to facilitate using the Query.join() method to join across the two relationships at once, however this makes use of a deprecated calling style.

To use select.join() or Query.join() with an association proxy, the current method is to make use of the AssociationProxyInstance.local_attr and AssociationProxyInstance.remote_attr attributes separately:

stmt = (
    select(Parent).
    join(Parent.proxied.local_attr).
    join(Parent.proxied.remote_attr)
)

A future release may seek to provide a more succinct join pattern for association proxy attributes.

See also

AssociationProxyInstance.local_attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.between(cleft: Any, cright: Any, symmetric: bool = False) → ColumnOperators

inherited from the ColumnOperators.between() method of ColumnOperators

Produce a between() clause against the parent object, given the lower and upper range.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.bitwise_and(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_and() method of ColumnOperators

Produce a bitwise AND operation, typically via the & operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.bitwise_lshift(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_lshift() method of ColumnOperators

Produce a bitwise LSHIFT operation, typically via the << operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.bitwise_not() → ColumnOperators

inherited from the ColumnOperators.bitwise_not() method of ColumnOperators

Produce a bitwise NOT operation, typically via the ~ operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.bitwise_or(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_or() method of ColumnOperators

Produce a bitwise OR operation, typically via the | operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.bitwise_rshift(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_rshift() method of ColumnOperators

Produce a bitwise RSHIFT operation, typically via the >> operator.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.bitwise_xor(other: Any) → ColumnOperators

inherited from the ColumnOperators.bitwise_xor() method of ColumnOperators

Produce a bitwise XOR operation, typically via the ^ operator, or # for PostgreSQL.

New in version 2.0.2.

See also

Bitwise Operators

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.bool_op(opstring: str, precedence: int = 0, python_impl: Callable[[...], Any] | None = None) → Callable[[Any], Operators]

inherited from the Operators.bool_op() method of Operators

Return a custom boolean operator.

This method is shorthand for calling Operators.op() and passing the Operators.op.is_comparison flag with True. A key advantage to using Operators.bool_op() is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.

See also

Operators.op()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.collate(collation: str) → ColumnOperators

inherited from the ColumnOperators.collate() method of ColumnOperators

Produce a collate() clause against the parent object, given the collation string.

See also

collate()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.concat(other: Any) → ColumnOperators

inherited from the ColumnOperators.concat() method of ColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b, or uses the concat() operator on MySQL.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.contains(other: Any, **kw: Any) → ColumnOperators

inherited from the ColumnOperators.contains() method of ColumnOperators

Implement the ‘contains’ operator.

Produces a LIKE expression that tests against a match for the middle of a string value:

column LIKE '%' || <other> || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.contains("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.contains.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.contains.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.contains.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.contains("foo%bar", autoescape=True)

  Will render as:

  somecolumn LIKE '%' || :param || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.contains("foo/%bar", escape="^")

  Will render as:

  somecolumn LIKE '%' || :param || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.contains.autoescape:

  somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.endswith()

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.desc() → ColumnOperators

inherited from the ColumnOperators.desc() method of ColumnOperators

Produce a desc() clause against the parent object.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.distinct() → ColumnOperators

inherited from the ColumnOperators.distinct() method of ColumnOperators

Produce a distinct() clause against the parent object.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.endswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.endswith() method of ColumnOperators

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the end of a string value:

column LIKE '%' || <other>

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.endswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.endswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.endswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.endswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.endswith("foo%bar", autoescape=True)

  Will render as:

  somecolumn LIKE '%' || :param ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.endswith("foo/%bar", escape="^")

  Will render as:

  somecolumn LIKE '%' || :param ESCAPE '^'

  The parameter may also be combined with ColumnOperators.endswith.autoescape:

  somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.contains()

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.has(criterion: _ColumnExpressionArgument[bool] | None = None, **kwargs: Any) → ColumnElement[bool]

inherited from the AssociationProxyInstance.has() method of AssociationProxyInstance

Produce a proxied ‘has’ expression using EXISTS.

This expression will be a composed product using the Comparator.any() and/or Comparator.has() operators of the underlying proxied attributes.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.icontains(other: Any, **kw: Any) → ColumnOperators

inherited from the ColumnOperators.icontains() method of ColumnOperators

Implement the icontains operator, e.g. case insensitive version of ColumnOperators.contains().

Produces a LIKE expression that tests against an insensitive match for the middle of a string value:

lower(column) LIKE '%' || lower(<other>) || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.icontains("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.icontains.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.icontains.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.icontains.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.icontains("foo%bar", autoescape=True)

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.icontains("foo/%bar", escape="^")

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.contains.autoescape:

  somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.contains()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.iendswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.iendswith() method of ColumnOperators

Implement the iendswith operator, e.g. case insensitive version of ColumnOperators.endswith().

Produces a LIKE expression that tests against an insensitive match for the end of a string value:

lower(column) LIKE '%' || lower(<other>)

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.iendswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.iendswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.iendswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.iendswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.iendswith("foo%bar", autoescape=True)

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.iendswith("foo/%bar", escape="^")

  Will render as:

  lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'

  The parameter may also be combined with ColumnOperators.iendswith.autoescape:

  somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.endswith()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.ilike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.ilike() method of ColumnOperators

Implement the ilike operator, e.g. case insensitive LIKE.

In a column context, produces an expression either of the form:

lower(a) LIKE lower(other)

Or on backends that support the ILIKE operator:

a ILIKE other

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.ilike("%foobar%"))

Parameters:

• other – expression to be compared

• escape –

  optional escape character, renders the ESCAPE keyword, e.g.:

  somecolumn.ilike("foo/%bar", escape="/")

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.in_(other: Any) → ColumnOperators

inherited from the ColumnOperators.in_() method of ColumnOperators

Implement the in operator.

In a column context, produces the clause column IN <other>.

The given parameter other may be:

• A list of literal values, e.g.:

  stmt.where(column.in_([1, 2, 3]))

  In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:

  WHERE COL IN (?, ?, ?)

• A list of tuples may be provided if the comparison is against a tuple_() containing multiple expressions:

  from sqlalchemy import tuple_
  stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))

• An empty list, e.g.:

  stmt.where(column.in_([]))

  In this calling form, the expression renders an “empty set” expression. These expressions are tailored to individual backends and are generally trying to get an empty SELECT statement as a subquery. Such as on SQLite, the expression is:

  WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

  Changed in version 1.4: empty IN expressions now use an execution-time generated SELECT subquery in all cases.

• A bound parameter, e.g. bindparam(), may be used if it includes the bindparam.expanding flag:

  stmt.where(column.in_(bindparam('value', expanding=True)))

  In this calling form, the expression renders a special non-SQL placeholder expression that looks like:

  WHERE COL IN ([EXPANDING_value])

  This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:

  connection.execute(stmt, {"value": [1, 2, 3]})

  The database would be passed a bound parameter for each value:

  WHERE COL IN (?, ?, ?)

  New in version 1.2: added “expanding” bound parameters

  If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:

  WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

  New in version 1.3: “expanding” bound parameters now support empty lists

• a select() construct, which is usually a correlated scalar select:

  stmt.where(
      column.in_(
          select(othertable.c.y).
          where(table.c.x == othertable.c.x)
      )
  )

  In this calling form, ColumnOperators.in_() renders as given:

  WHERE COL IN (SELECT othertable.y
  FROM othertable WHERE othertable.x = table.x)

Parameters:

other – a list of literals, a select() construct, or a bindparam() construct that includes the bindparam.expanding flag set to True.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.is_(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_() method of ColumnOperators

Implement the IS operator.

Normally, IS is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS may be desirable if comparing to boolean values on certain platforms.

See also

ColumnOperators.is_not()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.is_distinct_from(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_distinct_from() method of ColumnOperators

Implement the IS DISTINCT FROM operator.

Renders “a IS DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS NOT b”.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.is_not(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_not() method of ColumnOperators

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.is_()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.is_not_distinct_from(other: Any) → ColumnOperators

inherited from the ColumnOperators.is_not_distinct_from() method of ColumnOperators

Implement the IS NOT DISTINCT FROM operator.

Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.

Changed in version 1.4: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.isnot(other: Any) → ColumnOperators

inherited from the ColumnOperators.isnot() method of ColumnOperators

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.is_()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.isnot_distinct_from(other: Any) → ColumnOperators

inherited from the ColumnOperators.isnot_distinct_from() method of ColumnOperators

Implement the IS NOT DISTINCT FROM operator.

Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.

Changed in version 1.4: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.istartswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.istartswith() method of ColumnOperators

Implement the istartswith operator, e.g. case insensitive version of ColumnOperators.startswith().

Produces a LIKE expression that tests against an insensitive match for the start of a string value:

lower(column) LIKE lower(<other>) || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.istartswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.istartswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.istartswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.istartswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.istartswith("foo%bar", autoescape=True)

  Will render as:

  lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.istartswith("foo/%bar", escape="^")

  Will render as:

  lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.istartswith.autoescape:

  somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.like(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.like() method of ColumnOperators

Implement the like operator.

In a column context, produces the expression:

a LIKE other

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.like("%foobar%"))

Parameters:

• other – expression to be compared

• escape –

  optional escape character, renders the ESCAPE keyword, e.g.:

  somecolumn.like("foo/%bar", escape="/")

See also

ColumnOperators.ilike()

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.local_attr

inherited from the AssociationProxyInstance.local_attr attribute of AssociationProxyInstance

The ‘local’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.remote_attr

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.match(other: Any, **kwargs: Any) → ColumnOperators

inherited from the ColumnOperators.match() method of ColumnOperators

Implements a database-specific ‘match’ operator.

ColumnOperators.match() attempts to resolve to a MATCH-like function or operator provided by the backend. Examples include:

• PostgreSQL - renders x @@ plainto_tsquery(y)

  Changed in version 2.0: plainto_tsquery() is used instead of to_tsquery() for PostgreSQL now; for compatibility with other forms, see Full Text Search.

• MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

  See also

  match - MySQL specific construct with additional features.

• Oracle - renders CONTAINS(x, y)

• other backends may provide special implementations.

• Backends without any special implementation will emit the operator as “MATCH”. This is compatible with SQLite, for example.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.not_ilike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.not_ilike() method of ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with ColumnOperators.ilike(), i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.not_in(other: Any) → ColumnOperators

inherited from the ColumnOperators.not_in() method of ColumnOperators

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. The create_engine.empty_in_strategy may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and ColumnOperators.not_in() operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.not_like(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.not_like() method of ColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.notilike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.notilike() method of ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with ColumnOperators.ilike(), i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.notin_(other: Any) → ColumnOperators

inherited from the ColumnOperators.notin_() method of ColumnOperators

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. The create_engine.empty_in_strategy may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and ColumnOperators.not_in() operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.notlike(other: Any, escape: str | None = None) → ColumnOperators

inherited from the ColumnOperators.notlike() method of ColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.like()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.nulls_first() → ColumnOperators

inherited from the ColumnOperators.nulls_first() method of ColumnOperators

Produce a nulls_first() clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.nulls_last() → ColumnOperators

inherited from the ColumnOperators.nulls_last() method of ColumnOperators

Produce a nulls_last() clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.nullsfirst() → ColumnOperators

inherited from the ColumnOperators.nullsfirst() method of ColumnOperators

Produce a nulls_first() clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.nullslast() → ColumnOperators

inherited from the ColumnOperators.nullslast() method of ColumnOperators

Produce a nulls_last() clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.op(opstring: str, precedence: int = 0, is_comparison: bool = False, return_type: Type[TypeEngine[Any]] | TypeEngine[Any] | None = None, python_impl: Callable[..., Any] | None = None) → Callable[[Any], Operators]

inherited from the Operators.op() method of Operators

Produce a generic operator function.

e.g.:

somecolumn.op("*")(5)

produces:

somecolumn * 5

This function can also be used to make bitwise operators explicit. For example:

somecolumn.op('&')(0xff)

is a bitwise AND of the value in somecolumn.

Parameters:

• opstring – a string which will be output as the infix operator between this element and the expression passed to the generated function.

• precedence –

  precedence which the database is expected to apply to the operator in SQL expressions. This integer value acts as a hint for the SQL compiler to know when explicit parenthesis should be rendered around a particular operation. A lower number will cause the expression to be parenthesized when applied against another operator with higher precedence. The default value of 0 is lower than all operators except for the comma (,) and AS operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.

  See also

  I’m using op() to generate a custom operator and my parenthesis are not coming out correctly - detailed description of how the SQLAlchemy SQL compiler renders parenthesis

• is_comparison –

  legacy; if True, the operator will be considered as a “comparison” operator, that is which evaluates to a boolean true/false value, like ==, >, etc. This flag is provided so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition.

  Using the is_comparison parameter is superseded by using the Operators.bool_op() method instead; this more succinct operator sets this parameter automatically, but also provides correct PEP 484 typing support as the returned object will express a “boolean” datatype, i.e. BinaryExpression[bool].

• return_type – a TypeEngine class or object that will force the return type of an expression produced by this operator to be of that type. By default, operators that specify Operators.op.is_comparison will resolve to Boolean, and those that do not will be of the same type as the left-hand operand.

• python_impl –

  an optional Python function that can evaluate two Python values in the same way as this operator works when run on the database server. Useful for in-Python SQL expression evaluation functions, such as for ORM hybrid attributes, and the ORM “evaluator” used to match objects in a session after a multi-row update or delete.

  e.g.:

  >>> expr = column('x').op('+', python_impl=lambda a, b: a + b)('y')

  The operator for the above expression will also work for non-SQL left and right objects:

  >>> expr.operator(5, 10)
  15

  New in version 2.0.

See also

Operators.bool_op()

Redefining and Creating New Operators

Using custom operators in join conditions

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.operate(op: OperatorType, *other: Any, **kwargs: Any) → ColumnElement[Any]

Operate on an argument.

This is the lowest level of operation, raises NotImplementedError by default.

Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding ColumnOperators to apply func.lower() to the left and right side:

class MyComparator(ColumnOperators):
    def operate(self, op, other, **kwargs):
        return op(func.lower(self), func.lower(other), **kwargs)

Parameters:

• op – Operator callable.

• *other – the ‘other’ side of the operation. Will be a single scalar for most operations.

• **kwargs – modifiers. These may be passed by special operators such as ColumnOperators.contains().

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.regexp_match(pattern: Any, flags: str | None = None) → ColumnOperators

inherited from the ColumnOperators.regexp_match() method of ColumnOperators

Implements a database-specific ‘regexp match’ operator.

E.g.:

stmt = select(table.c.some_column).where(
    table.c.some_column.regexp_match('^(b|c)')
)

ColumnOperators.regexp_match() attempts to resolve to a REGEXP-like function or operator provided by the backend, however the specific regular expression syntax and flags available are not backend agnostic.

Examples include:

• PostgreSQL - renders x ~ y or x !~ y when negated.

• Oracle - renders REGEXP_LIKE(x, y)

• SQLite - uses SQLite’s REGEXP placeholder operator and calls into the Python re.match() builtin.

• other backends may provide special implementations.

• Backends without any special implementation will emit the operator as “REGEXP” or “NOT REGEXP”. This is compatible with SQLite and MySQL, for example.

Regular expression support is currently implemented for Oracle, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary.

Parameters:

• pattern – The regular expression pattern string or column clause.

• flags – Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern. When using the ignore case flag ‘i’ in PostgreSQL, the ignore case regexp match operator ~* or !~* will be used.

New in version 1.4.

Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.

See also

ColumnOperators.regexp_replace()

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.regexp_replace(pattern: Any, replacement: Any, flags: str | None = None) → ColumnOperators

inherited from the ColumnOperators.regexp_replace() method of ColumnOperators

Implements a database-specific ‘regexp replace’ operator.

E.g.:

stmt = select(
    table.c.some_column.regexp_replace(
        'b(..)',
        'X
Y',
        flags='g'
    )
)

ColumnOperators.regexp_replace() attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the function REGEXP_REPLACE(). However, the specific regular expression syntax and flags available are not backend agnostic.

Regular expression replacement support is currently implemented for Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary.

Parameters:

• pattern – The regular expression pattern string or column clause.

• pattern – The replacement string or column clause.

• flags – Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern.

New in version 1.4.

Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.

See also

ColumnOperators.regexp_match()

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.remote_attr

inherited from the AssociationProxyInstance.remote_attr attribute of AssociationProxyInstance

The ‘remote’ class attribute referenced by this AssociationProxyInstance.

See also

AssociationProxyInstance.attr

AssociationProxyInstance.local_attr

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.reverse_operate(op: OperatorType, other: Any, **kwargs: Any) → Operators

inherited from the Operators.reverse_operate() method of Operators

Reverse operate on an argument.

Usage is the same as operate().

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.scalar

inherited from the AssociationProxyInstance.scalar attribute of AssociationProxyInstance

Return True if this AssociationProxyInstance proxies a scalar relationship on the local side.

method sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.startswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

inherited from the ColumnOperators.startswith() method of ColumnOperators

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the start of a string value:

column LIKE <other> || '%'

E.g.:

stmt = select(sometable).\
    where(sometable.c.column.startswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.startswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.startswith.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

Parameters:

• other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.startswith.autoescape flag is set to True.

• autoescape –

  boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

  An expression such as:

  somecolumn.startswith("foo%bar", autoescape=True)

  Will render as:

  somecolumn LIKE :param || '%' ESCAPE '/'

  With the value of :param as "foo/%bar".

• escape –

  a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

  An expression such as:

  somecolumn.startswith("foo/%bar", escape="^")

  Will render as:

  somecolumn LIKE :param || '%' ESCAPE '^'

  The parameter may also be combined with ColumnOperators.startswith.autoescape:

  somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)

  Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.endswith()

ColumnOperators.contains()

ColumnOperators.like()

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.target_class: Type[Any]

The intermediary class handled by this AssociationProxyInstance.

Intercepted append/set/assignment events will result in the generation of new instances of this class.

attribute sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.timetuple: Literal[None] = None

inherited from the ColumnOperators.timetuple attribute of ColumnOperators

Hack, allows datetime objects to be compared on the LHS.

class sqlalchemy.ext.associationproxy.AssociationProxyExtensionType

Members

ASSOCIATION_PROXY

Class signature

class sqlalchemy.ext.associationproxy.AssociationProxyExtensionType (sqlalchemy.orm.base.InspectionAttrExtensionType)

attribute sqlalchemy.ext.associationproxy.AssociationProxyExtensionType.ASSOCIATION_PROXY = 'ASSOCIATION_PROXY'

Symbol indicating an InspectionAttr that’s of type AssociationProxy.

Is assigned to the InspectionAttr.extension_type attribute.