关联代理¶
Association Proxy
associationproxy
用于创建跨关系的目标属性的读/写视图。它本质上隐藏了两个端点之间的“中间”属性的使用,可以用于从一组相关对象或标量关系中挑选字段,或减少使用关联对象模式的冗长。
通过创造性地应用,association proxy 允许构建几乎任何几何形状的复杂集合和字典视图,使用标准的、透明配置的关系模式持久化到数据库中。
associationproxy
is used to create a read/write view of a
target attribute across a relationship. It essentially conceals
the usage of a “middle” attribute between two endpoints, and
can be used to cherry-pick fields from both a collection of
related objects or scalar relationship. or to reduce the verbosity
of using the association object pattern.
Applied creatively, the association proxy allows
the construction of sophisticated collections and dictionary
views of virtually any geometry, persisted to the database using
standard, transparently configured relational patterns.
简化标量集合¶
Simplifying Scalar Collections
设想在两个类 User
和 Keyword
之间存在一个多对多的映射关系。
每个 User
可以拥有任意数量的 Keyword
对象,反之亦然
(多对多关系模式详见 多对多)。
下面的示例展示了该模式的实现方式,与标准用法的唯一区别是,
在 User
类中额外添加了一个属性 User.keywords
:
from __future__ import annotations
from typing import Final
from typing import List
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.associationproxy import AssociationProxy
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "user"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(64))
kw: Mapped[List[Keyword]] = relationship(secondary=lambda: user_keyword_table)
def __init__(self, name: str):
self.name = name
# 从 'kw' 关系中代理 'keyword' 属性
keywords: AssociationProxy[List[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
user_keyword_table: Final[Table] = Table(
"user_keyword",
Base.metadata,
Column("user_id", Integer, ForeignKey("user.id"), primary_key=True),
Column("keyword_id", Integer, ForeignKey("keyword.id"), primary_key=True),
)
在上述示例中,association_proxy()
应用于 User
类,用于构建一个基于 kw
关系的“视图”,
此视图可以直接访问与每个 Keyword
对象关联的 .keyword
字符串值。
当将字符串添加到该集合时,也会自动创建新的 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
属性,这种方式较为繁琐。
下面的例子演示了不使用代理时进行相同操作的写法:
>>> # 不使用 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 描述符 实例,
并不会被 Mapper
认为是“映射”属性。
因此,它总是直接内联在映射类的类定义中,无论使用的是声明式还是命令式映射方式。
代理通过操作底层已映射的属性或集合来实现功能, 通过代理进行的更改会立即反映到被映射的属性中,反之亦然。 底层属性仍然可以正常访问。
在首次访问时,association proxy 会对目标集合进行反射操作, 以确保其行为与底层集合一致。它会考虑代理的本地属性是集合还是标量引用, 以及集合是类似 set、list 还是 dict,从而使代理行为与底层属性保持一致。
Consider a many-to-many mapping between two classes, User
and Keyword
.
Each User
can have any number of Keyword
objects, and vice-versa
(the many-to-many pattern is described at 多对多).
The example below illustrates this pattern in the same way, with the
exception of an extra attribute added to the User
class called
User.keywords
:
from __future__ import annotations
from typing import Final
from typing import List
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.associationproxy import AssociationProxy
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "user"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(64))
kw: Mapped[List[Keyword]] = relationship(secondary=lambda: user_keyword_table)
def __init__(self, name: str):
self.name = name
# proxy the 'keyword' attribute from the 'kw' relationship
keywords: AssociationProxy[List[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
user_keyword_table: Final[Table] = Table(
"user_keyword",
Base.metadata,
Column("user_id", Integer, ForeignKey("user.id"), primary_key=True),
Column("keyword_id", Integer, ForeignKey("keyword.id"), primary_key=True),
)
In the above example, association_proxy()
is applied to the User
class to produce a “view” of the kw
relationship, which exposes the string
value of .keyword
associated with each Keyword
object. It also
creates new Keyword
objects transparently when strings are added to the
collection:
>>> user = User("jek")
>>> user.keywords.append("cheese-inspector")
>>> user.keywords.append("snack-ninja")
>>> print(user.keywords)
['cheese-inspector', 'snack-ninja']
To understand the mechanics of this, first review the behavior of
User
and Keyword
without using the .keywords
association proxy.
Normally, reading and manipulating the collection of “keyword” strings associated
with User
requires traversal from each collection element to the .keyword
attribute, which can be awkward. The example below illustrates the identical
series of operations applied without using the association proxy:
>>> # 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']
The AssociationProxy
object produced by the association_proxy()
function
is an instance of a Python descriptor,
and is not considered to be “mapped” by the Mapper
in any way. Therefore,
it’s always indicated inline within the class definition of the mapped class,
regardless of whether Declarative or Imperative mappings are used.
The proxy functions by operating upon the underlying mapped attribute or collection in response to operations, and changes made via the proxy are immediately apparent in the mapped attribute, as well as vice versa. The underlying attribute remains fully accessible.
When first accessed, the association proxy performs introspection operations on the target collection so that its behavior corresponds correctly. Details such as if the locally proxied attribute is a collection (as is typical) or a scalar reference, as well as if the collection acts like a set, list, or dictionary is taken into account, so that the proxy should act just like the underlying collection or attribute does.
创建新值¶
Creation of New Values
当 association proxy 拦截到一个列表的 append()
操作(或 set 的 add()
、
dict 的 __setitem__()
,或标量赋值操作)时,
它会使用中介对象的构造器创建一个新实例,并将所给值作为唯一参数传递。
在上面的例子中,像这样的操作:
user.keywords.append("cheese-inspector")
会被 association proxy 转换为如下操作:
user.kw.append(Keyword("cheese-inspector"))
这个例子之所以有效,是因为我们将 Keyword
的构造函数设计为接受一个位置参数 keyword
。
如果不能使用单参数构造器,可以使用 association proxy 的 association_proxy.creator
参数自定义对象创建逻辑。它接受一个可调用对象(如 Python 函数),用于根据传入参数创建新对象。
下面的示例展示了使用常见的 lambda 函数:
class User(Base):
...
# 在 append() 操作中使用 Keyword(keyword=kw)
keywords: AssociationProxy[List[str]] = association_proxy(
"kw", "keyword", creator=lambda kw: Keyword(keyword=kw)
)
creator
函数在 list 或 set 类型集合中接受一个参数,在标量属性中也是如此;
而在 dict 类型集合中,它会接收两个参数,即 “key” 和 “value”。
一个使用该形式的例子可见于 代理到基于字典的集合。
When a list append()
event (or set add()
, dictionary __setitem__()
,
or scalar assignment event) is intercepted by the association proxy, it
instantiates a new instance of the “intermediary” object using its constructor,
passing as a single argument the given value. In our example above, an
operation like:
user.keywords.append("cheese-inspector")
Is translated by the association proxy into the operation:
user.kw.append(Keyword("cheese-inspector"))
The example works here because we have designed the constructor for Keyword
to accept a single positional argument, keyword
. For those cases where a
single-argument constructor isn’t feasible, the association proxy’s creational
behavior can be customized using the association_proxy.creator
argument, which references a callable (i.e. Python function) that will produce
a new object instance given the singular argument. Below we illustrate this
using a lambda as is typical:
class User(Base):
...
# use Keyword(keyword=kw) on append() events
keywords: AssociationProxy[List[str]] = association_proxy(
"kw", "keyword", creator=lambda kw: Keyword(keyword=kw)
)
The creator
function accepts a single argument in the case of a list-
or set- based collection, or a scalar attribute. In the case of a dictionary-based
collection, it accepts two arguments, “key” and “value”. An example
of this is below in 代理到基于字典的集合.
简化关联对象¶
Simplifying Association Objects
“关联对象”模式是多对多关系的一种扩展形式,相关内容详见 关联对象。 在日常使用中,association proxy(关联代理)可以用于屏蔽“关联对象”的存在,让使用更简洁。
假设我们上面定义的 user_keyword
表中还有其他列需要显式映射,
但在大多数情况下我们并不需要直接访问这些额外属性。
下面我们演示一个新的映射方式,引入 UserKeywordAssociation
类,
它被映射到前面提到的 user_keyword
表。
该类增加了一个额外列 special_key
,它是一个我们偶尔会访问的值,但并非默认使用情形。
我们在 User
类上创建了一个名为 keywords
的 association proxy,
它将 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",
)
# 将 "user_keyword_associations" 集合代理到其内部的 "keyword" 属性
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
集合,
它提供了一个包含 Keyword
对象的集合,这些对象是通过底层的 UserKeywordAssociation
实例获取的:
>>> 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')]
这个例子与前面 简化标量集合 中的示例形成对比,
在那里 association proxy 暴露的是字符串集合,而这里是一个组合对象的集合。
在这种情况下,每次调用 .keywords.append()
实际等效于:
>>> user.user_keyword_associations.append(
... UserKeywordAssociation(keyword=Keyword("its_heavy"))
... )
UserKeywordAssociation
对象拥有两个在 association proxy 的 append()
操作中同时赋值的属性:
.keyword
,指向关联的 Keyword
对象;
以及 .user
,指向当前的 User
对象。
首先为 .keyword
赋值,因为 association proxy 会响应 .append()
操作创建一个新的 UserKeywordAssociation
实例,
并将传入的 Keyword
实例赋给其 .keyword
属性。
随后该 UserKeywordAssociation
实例被添加到 User.user_keyword_associations
集合中,
此时,由于我们在 UserKeywordAssociation.user
上配置了 back_populates
,
该属性会自动被赋值为当前执行 append
的父 User
实例。
而 special_key
参数则保持默认值 None
。
如果在某些情况下我们希望为 special_key
设置一个特定值,
可以显式创建 UserKeywordAssociation
对象。下面的例子中我们为所有三个属性赋值。
其中在构造过程中为 .user
赋值的同时,也将该对象添加到了 User.user_keyword_associations
集合中(通过关系实现):
>>> UserKeywordAssociation(
... keyword=Keyword("its_wood"), user=user, special_key="my special key"
... )
最终,association proxy 返回的是所有这些操作所代表的 Keyword
对象的集合:
>>> print(user.keywords)
[Keyword('new_from_blammo'), Keyword('its_big'), Keyword('its_heavy'), Keyword('its_wood')]
The “association object” pattern is an extended form of a many-to-many relationship, and is described at 关联对象. Association proxies are useful for keeping “association objects” out of the way during regular use.
Suppose our user_keyword
table above had additional columns
which we’d like to map explicitly, but in most cases we don’t
require direct access to these attributes. Below, we illustrate
a new mapping which introduces the UserKeywordAssociation
class, which
is mapped to the user_keyword
table illustrated earlier.
This class adds an additional column special_key
, a value which
we occasionally want to access, but not in the usual case. We
create an association proxy on the User
class called
keywords
, which will bridge the gap from the user_keyword_associations
collection of User
to the .keyword
attribute present on each
UserKeywordAssociation
:
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})"
With the above configuration, we can operate upon the .keywords
collection
of each User
object, each of which exposes a collection of Keyword
objects that are obtained from the underlying UserKeywordAssociation
elements:
>>> 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')]
This example is in contrast to the example illustrated previously at
简化标量集合, where the association proxy exposed
a collection of strings, rather than a collection of composed objects.
In this case, each .keywords.append()
operation is equivalent to:
>>> user.user_keyword_associations.append(
... UserKeywordAssociation(keyword=Keyword("its_heavy"))
... )
The UserKeywordAssociation
object has two attributes that are both
populated within the scope of the append()
operation of the association
proxy; .keyword
, which refers to the
Keyword
object, and .user
, which refers to the User
object.
The .keyword
attribute is populated first, as the association proxy
generates a new UserKeywordAssociation
object in response to the .append()
operation, assigning the given Keyword
instance to the .keyword
attribute. Then, as the UserKeywordAssociation
object is appended to the
User.user_keyword_associations
collection, the UserKeywordAssociation.user
attribute,
configured as back_populates
for User.user_keyword_associations
, is initialized
upon the given UserKeywordAssociation
instance to refer to the parent User
receiving the append operation. The special_key
argument above is left at its default value of None
.
For those cases where we do want special_key
to have a value, we
create the UserKeywordAssociation
object explicitly. Below we assign all
three attributes, wherein the assignment of .user
during
construction, has the effect of appending the new UserKeywordAssociation
to
the User.user_keyword_associations
collection (via the relationship):
>>> UserKeywordAssociation(
... keyword=Keyword("its_wood"), user=user, special_key="my special key"
... )
The association proxy returns to us a collection of Keyword
objects represented
by all these operations:
>>> print(user.keywords)
[Keyword('new_from_blammo'), Keyword('its_big'), Keyword('its_heavy'), Keyword('its_wood')]
代理到基于字典的集合¶
Proxying to Dictionary Based Collections
association proxy(关联代理)同样可以代理到基于字典的集合上。
在 SQLAlchemy 映射中,通常使用 attribute_keyed_dict()
集合类型来创建字典集合,
并且还可以使用 自定义基于字典的集合 中描述的扩展技术。
当 association proxy 检测到目标是基于字典的集合时,它会自动调整自身的行为。
当新值被添加到字典中时,association proxy 会在实例化中间对象时向创建函数传递两个参数(键和值),而不是一个参数。
这个创建函数默认是中间类的构造器,也可以通过 creator
参数进行自定义。
下面我们修改前面的 UserKeywordAssociation
示例,
将 User.user_keyword_associations
集合映射为一个字典,
该字典以 UserKeywordAssociation.special_key
字段作为键。
同时我们在 User.keywords
proxy 上应用了一个 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 关系,使用字典映射,
# 以 "special_key" 为字典中的键。
user_keyword_associations: Mapped[Dict[str, UserKeywordAssociation]] = relationship(
back_populates="user",
collection_class=attribute_keyed_dict("special_key"),
cascade="all, delete-orphan",
)
# proxy 到 'user_keyword_associations',在创建过程中实例化
# UserKeywordAssociation,并将键赋给 'special_key',值赋给 '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})"
我们可以将 .keywords
集合表现为一个字典,
其中将 UserKeywordAssociation.special_key
的值映射为 Keyword
对象:
>>> user = User("log")
>>> user.keywords["sk1"] = Keyword("kw1")
>>> user.keywords["sk2"] = Keyword("kw2")
>>> print(user.keywords)
{'sk1': Keyword('kw1'), 'sk2': Keyword('kw2')}
The association proxy can proxy to dictionary based collections as well. SQLAlchemy
mappings usually use the attribute_keyed_dict()
collection type to
create dictionary collections, as well as the extended techniques described in
自定义基于字典的集合.
The association proxy adjusts its behavior when it detects the usage of a
dictionary-based collection. When new values are added to the dictionary, the
association proxy instantiates the intermediary object by passing two
arguments to the creation function instead of one, the key and the value. As
always, this creation function defaults to the constructor of the intermediary
class, and can be customized using the creator
argument.
Below, we modify our UserKeywordAssociation
example such that the User.user_keyword_associations
collection will now be mapped using a dictionary, where the UserKeywordAssociation.special_key
argument will be used as the key for the dictionary. We also apply a creator
argument to the User.keywords
proxy so that these values are assigned appropriately
when new elements are added to the dictionary:
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})"
We illustrate the .keywords
collection as a dictionary, mapping the
UserKeywordAssociation.special_key
value to Keyword
objects:
>>> user = User("log")
>>> user.keywords["sk1"] = Keyword("kw1")
>>> user.keywords["sk2"] = Keyword("kw2")
>>> print(user.keywords)
{'sk1': Keyword('kw1'), 'sk2': Keyword('kw2')}
复合关联代理¶
Composite Association Proxies
考虑到我们之前关于从关系到标量属性的代理、跨关联对象的代理以及字典代理的示例,
我们可以将这三种技术结合起来,为 User
提供一个 keywords
字典,
该字典严格处理将 special_key
的字符串值映射到字符串 keyword
。
UserKeywordAssociation
和 Keyword
类完全被隐藏。
这是通过在 User
上构建一个关联代理来实现的,该代理指向
UserKeywordAssociation
上的一个关联代理:
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",
)
# 与基本字典示例中的 'user_keyword_associations'->'keyword' 相同的代理。
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",
)
# 与 Keyword 的关系现在称为 'kw'
kw: Mapped[Keyword] = relationship()
# 'keyword' 现在是指向 'Keyword' 中 '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
User.keywords
现在是一个从字符串到字符串的字典,
其中 UserKeywordAssociation
和 Keyword
对象会通过关联代理透明地创建和删除。
在下面的示例中,我们展示了赋值操作符的用法,该操作符也由关联代理适当处理,
以一次将字典值应用到集合中:
>>> user = User("log")
>>> user.keywords = {"sk1": "kw1", "sk2": "kw2"}
>>> print(user.keywords)
{'sk1': 'kw1', 'sk2': 'kw2'}
>>> user.keywords["sk3"] = "kw3"
>>> del user.keywords["sk2"]
>>> print(user.keywords)
{'sk1': 'kw1', 'sk3': 'kw3'}
>>> # 展示非代理使用
... print(user.user_keyword_associations["sk3"].kw)
<__main__.Keyword object at 0x12ceb90>
我们上面示例的一个警告是,因为 Keyword
对象是在每次字典设置操作时创建的,
该示例未能保持 Keyword
对象在其字符串名称上的唯一性,这是此类标记场景中的典型要求。
对于这种用例,推荐使用 UniqueObject 配方,或者
类似的创建策略,这将对 Keyword
类的构造函数应用 “先查找,后创建” 策略,
以便在给定名称已存在时返回已经存在的 Keyword
对象。
Given our previous examples of proxying from relationship to scalar
attribute, proxying across an association object, and proxying dictionaries,
we can combine all three techniques together to give User
a keywords
dictionary that deals strictly with the string value
of special_key
mapped to the string keyword
. Both the UserKeywordAssociation
and Keyword
classes are entirely concealed. This is achieved by building
an association proxy on User
that refers to an association proxy
present on UserKeywordAssociation
:
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
User.keywords
is now a dictionary of string to string, where
UserKeywordAssociation
and Keyword
objects are created and removed for us
transparently using the association proxy. In the example below, we illustrate
usage of the assignment operator, also appropriately handled by the
association proxy, to apply a dictionary value to the collection at once:
>>> user = User("log")
>>> user.keywords = {"sk1": "kw1", "sk2": "kw2"}
>>> print(user.keywords)
{'sk1': 'kw1', 'sk2': 'kw2'}
>>> user.keywords["sk3"] = "kw3"
>>> del user.keywords["sk2"]
>>> print(user.keywords)
{'sk1': 'kw1', 'sk3': 'kw3'}
>>> # illustrate un-proxied usage
... print(user.user_keyword_associations["sk3"].kw)
<__main__.Keyword object at 0x12ceb90>
One caveat with our example above is that because Keyword
objects are created
for each dictionary set operation, the example fails to maintain uniqueness for
the Keyword
objects on their string name, which is a typical requirement for
a tagging scenario such as this one. For this use case the recipe
UniqueObject, or
a comparable creational strategy, is
recommended, which will apply a “lookup first, then create” strategy to the constructor
of the Keyword
class, so that an already existing Keyword
is returned if the
given name is already present.
使用关联代理进行查询¶
Querying with Association Proxies
:class: .AssociationProxy 提供了简单的 SQL 构建功能,
这些功能在类级别工作,与其他 ORM 映射的属性类似,
并提供了主要基于 SQL EXISTS
关键字的基本过滤支持。
备注
关联代理扩展的主要目的是允许 改进对已经加载的映射对象实例的持久化和对象访问模式。 类绑定的查询功能用途有限,不会替代在构建包含 JOIN、预加载 选项等 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",
)
# 面向对象的关联代理
keywords: AssociationProxy[List[Keyword]] = association_proxy(
"user_keyword_associations",
"keyword",
)
# 面向列的关联代理
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)
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
属性实际上是两个关联代理的链接,
因此在使用此代理生成 SQL 语句时,我们得到两个层次的 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 条件, 特别是在将多个关联代理串联在一起时。
The AssociationProxy
features simple SQL construction capabilities
which work at the class level in a similar way as other ORM-mapped attributes,
and provide rudimentary filtering support primarily based on the
SQL EXISTS
keyword.
备注
The primary purpose of the association proxy extension is to allow for improved persistence and object-access patterns with mapped object instances that are already loaded. The class-bound querying feature is of limited use and will not replace the need to refer to the underlying attributes when constructing SQL queries with JOINs, eager loading options, etc.
For this section, assume a class with both an association proxy that refers to a column, as well as an association proxy that refers to a related object, as in the example mapping below:
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))
The SQL generated takes the form of a correlated subquery against the EXISTS SQL operator so that it can be used in a WHERE clause without the need for additional modifications to the enclosing query. If the immediate target of an association proxy is a mapped column expression, standard column operators can be used which will be embedded in the subquery. For example a straight equality operator:
>>> 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:
>>> 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)
For association proxies where the immediate target is a related object or collection,
or another association proxy or attribute on the related object, relationship-oriented
operators can be used instead, such as PropComparator.has()
and
PropComparator.any()
. The User.keywords
attribute is in fact
two association proxies linked together, so when using this proxy for generating
SQL phrases, we get two levels of EXISTS subqueries:
>>> 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)))
This is not the most efficient form of SQL, so while association proxies can be convenient for generating WHERE criteria quickly, SQL results should be inspected and “unrolled” into explicit JOIN criteria for best use, especially when chaining association proxies together.
级联标量删除¶
Cascading Scalar Deletes
给定如下映射:
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()
对 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
将保持不变。
请注意,这不是面向集合的关联代理的行为; 在这种情况下,当代理集合中的成员被移除时, 中介关联对象始终会被删除。是否删除行取决于关系的级联设置。
参见
Given a mapping as:
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 = B()
The A.b
association is scalar, and includes use of the parameter
AssociationProxy.cascade_scalar_deletes
. When this parameter
is enabled, setting A.b
to None
will remove A.ab
as well:
a.b = None
assert a.ab is None
When AssociationProxy.cascade_scalar_deletes
is not set,
the association object a.ab
above would remain in place.
Note that this is not the behavior for collection-based association proxies; in that case, the intermediary association object is always removed when members of the proxied collection are removed. Whether or not the row is deleted depends on the relationship cascade setting.
参见
标量关系¶
Scalar Relationships
下面的示例演示了在一对多关系的多方使用关联代理, 访问标量对象的属性:
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
The example below illustrates the use of the association proxy on the many side of of a one-to-many relationship, accessing attributes of a scalar object:
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",
],
)
A summary of the steps of my_snack
can be printed using:
>>> 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 文档¶
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, hash]) |
Return a Python property implementing a view of a target attribute which references an attribute on members of the target. |
A descriptor that presents a read/write view of an object attribute. |
|
A per-class object that serves class- and object-specific results. |
|
an |
|
an |
- 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, hash: _NoArg | bool | None = _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.
- 参数:
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 isNone
, the callable is used to construct a new object. If an object value already exists, the given attribute value is populated onto that object.参见
cascade_scalar_deletes¶ –
when True, indicates that setting the proxied value to
None
, or deleting it viadel
, 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.参见
级联标量删除 - 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. theassocation_proxy.cascade_scalar_deletes
.在 2.0.18 版本加入.
init¶ –
Specific to 声明式Dataclass映射, specifies if the mapped attribute should be part of the
__init__()
method as generated by the dataclass process.在 2.0.0b4 版本加入.
repr¶ –
Specific to 声明式Dataclass映射, specifies if the attribute established by this
AssociationProxy
should be part of the__repr__()
method as generated by the dataclass process.在 2.0.0b4 版本加入.
default_factory¶ –
Specific to 声明式Dataclass映射, specifies a default-value generation function that will take place as part of the
__init__()
method as generated by the dataclass process.在 2.0.0b4 版本加入.
compare¶ –
Specific to 声明式Dataclass映射, indicates if this field should be included in comparison operations when generating the
__eq__()
and__ne__()
methods for the mapped class.在 2.0.0b4 版本加入.
kw_only¶ –
Specific to 声明式Dataclass映射, indicates if this field should be marked as keyword-only when generating the
__init__()
method as generated by the dataclass process.在 2.0.0b4 版本加入.
hash¶ –
Specific to 声明式Dataclass映射, controls if this field is included when generating the
__hash__()
method for the mapped class.在 2.0.36 版本加入.
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:- 参数:
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 theassociation_proxy()
constructor function. See the description ofassociation_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
-
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
fromMapper.all_orm_descriptors
, and we want to view the target class for this proxy as mapped byUser
:inspect(User).all_orm_descriptors["keywords"].for_class(User).target_class
This returns an instance of
AssociationProxyInstance
that is specific to theUser
class. TheAssociationProxy
object remains agnostic of its parent class.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
getset_factory: _GetSetFactoryProtocol | None¶
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
info¶ inherited from the
InspectionAttrInfo.info
attribute ofInspectionAttrInfo
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()
, orcomposite()
functions.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_aliased_class = False¶ inherited from the
InspectionAttr.is_aliased_class
attribute ofInspectionAttr
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 aMapperProperty
. But can also be an extension type such asAssociationProxy
orhybrid_property
. TheInspectionAttr.extension_type
will refer to a constant identifying the specific subtype.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_bundle = False¶ inherited from the
InspectionAttr.is_bundle
attribute ofInspectionAttr
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 ofInspectionAttr
True if this object is an instance of
ClauseElement
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_instance = False¶ inherited from the
InspectionAttr.is_instance
attribute ofInspectionAttr
True if this object is an instance of
InstanceState
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_mapper = False¶ inherited from the
InspectionAttr.is_mapper
attribute ofInspectionAttr
True if this object is an instance of
Mapper
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_property = False¶ inherited from the
InspectionAttr.is_property
attribute ofInspectionAttr
True if this object is an instance of
MapperProperty
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxy.
is_selectable = False¶ inherited from the
InspectionAttr.is_selectable
attribute ofInspectionAttr
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¶
-
method
- 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, theAssociationProxyInstance
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 theAssociationProxyInstance
, use theAssociationProxy.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
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 ofColumnOperators
Implement the
==
operator.In a column context, produces the clause
a = b
. If the target isNone
, producesa IS NULL
.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
__le__(other: Any) ColumnOperators ¶ inherited from the
sqlalchemy.sql.expression.ColumnOperators.__le__
method ofColumnOperators
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 ofColumnOperators
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 ofColumnOperators
Implement the
!=
operator.In a column context, produces the clause
a != b
. If the target isNone
, producesa IS NOT NULL
.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
all_() ColumnOperators ¶ inherited from the
ColumnOperators.all_()
method ofColumnOperators
Produce an
all_()
clause against the parent object.See the documentation for
all_()
for examples.备注
be sure to not confuse the newer
ColumnOperators.all_()
method with the legacy version of this method, theComparator.all()
method that’s specific toARRAY
, 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/orComparator.has()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
any_() ColumnOperators ¶ inherited from the
ColumnOperators.any_()
method ofColumnOperators
Produce an
any_()
clause against the parent object.See the documentation for
any_()
for examples.备注
be sure to not confuse the newer
ColumnOperators.any_()
method with the legacy version of this method, theComparator.any()
method that’s specific toARRAY
, which uses a different calling style.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
asc() ColumnOperators ¶ inherited from the
ColumnOperators.asc()
method ofColumnOperators
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()
orQuery.join()
with an association proxy, the current method is to make use of theAssociationProxyInstance.local_attr
andAssociationProxyInstance.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.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
between(cleft: Any, cright: Any, symmetric: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.between()
method ofColumnOperators
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 ofColumnOperators
Produce a bitwise AND operation, typically via the
&
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
bitwise_lshift(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_lshift()
method ofColumnOperators
Produce a bitwise LSHIFT operation, typically via the
<<
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
bitwise_not() ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_not()
method ofColumnOperators
Produce a bitwise NOT operation, typically via the
~
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
bitwise_or(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_or()
method ofColumnOperators
Produce a bitwise OR operation, typically via the
|
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
bitwise_rshift(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_rshift()
method ofColumnOperators
Produce a bitwise RSHIFT operation, typically via the
>>
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
bitwise_xor(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_xor()
method ofColumnOperators
Produce a bitwise XOR operation, typically via the
^
operator, or#
for PostgreSQL.在 2.0.2 版本加入.
参见
-
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 ofOperators
Return a custom boolean operator.
This method is shorthand for calling
Operators.op()
and passing theOperators.op.is_comparison
flag with True. A key advantage to usingOperators.bool_op()
is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
collate(collation: str) ColumnOperators ¶ inherited from the
ColumnOperators.collate()
method ofColumnOperators
Produce a
collate()
clause against the parent object, given the collation string.参见
-
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 ofColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause
a || b
, or uses theconcat()
operator on MySQL.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
contains(other: Any, **kw: Any) ColumnOperators ¶ inherited from the
ColumnOperators.contains()
method ofColumnOperators
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, theColumnOperators.contains.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
delete(obj: Any) None ¶
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
desc() ColumnOperators ¶ inherited from the
ColumnOperators.desc()
method ofColumnOperators
Produce a
desc()
clause against the parent object.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
distinct() ColumnOperators ¶ inherited from the
ColumnOperators.distinct()
method ofColumnOperators
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 ofColumnOperators
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, theColumnOperators.endswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
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/orComparator.has()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
icontains(other: Any, **kw: Any) ColumnOperators ¶ inherited from the
ColumnOperators.icontains()
method ofColumnOperators
Implement the
icontains
operator, e.g. case insensitive version ofColumnOperators.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, theColumnOperators.icontains.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
iendswith(other: Any, escape: str | None = None, autoescape: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.iendswith()
method ofColumnOperators
Implement the
iendswith
operator, e.g. case insensitive version ofColumnOperators.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, theColumnOperators.iendswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
ilike(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.ilike()
method ofColumnOperators
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%"))
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
in_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.in_()
method ofColumnOperators
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)
在 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 thebindparam.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 (?, ?, ?)
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)
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)
- 参数:
other¶ – a list of literals, a
select()
construct, or abindparam()
construct that includes thebindparam.expanding
flag set to True.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
is_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_()
method ofColumnOperators
Implement the
IS
operator.Normally,
IS
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS
may be desirable if comparing to boolean values on certain platforms.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
is_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_distinct_from()
method ofColumnOperators
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 ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.在 1.4 版本发生变更: The
is_not()
operator is renamed fromisnot()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
is_not_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_not_distinct_from()
method ofColumnOperators
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”.
在 1.4 版本发生变更: The
is_not_distinct_from()
operator is renamed fromisnot_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 ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.在 1.4 版本发生变更: The
is_not()
operator is renamed fromisnot()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
isnot_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.isnot_distinct_from()
method ofColumnOperators
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”.
在 1.4 版本发生变更: The
is_not_distinct_from()
operator is renamed fromisnot_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 ofColumnOperators
Implement the
istartswith
operator, e.g. case insensitive version ofColumnOperators.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, theColumnOperators.istartswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
like(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.like()
method ofColumnOperators
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%"))
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
local_attr¶ The ‘local’ class attribute referenced by this
AssociationProxyInstance
.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
match(other: Any, **kwargs: Any) ColumnOperators ¶ inherited from the
ColumnOperators.match()
method ofColumnOperators
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)
在 2.0 版本发生变更:
plainto_tsquery()
is used instead ofto_tsquery()
for PostgreSQL now; for compatibility with other forms, see 全文搜索.MySQL - renders
MATCH (x) AGAINST (y IN BOOLEAN MODE)
参见
match
- MySQL specific construct with additional features.Oracle Database - 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 ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.在 1.4 版本发生变更: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
not_in(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.not_in()
method ofColumnOperators
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. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.在 1.4 版本发生变更: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
not_like(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.not_like()
method ofColumnOperators
implement the
NOT LIKE
operator.This is equivalent to using negation with
ColumnOperators.like()
, i.e.~x.like(y)
.在 1.4 版本发生变更: The
not_like()
operator is renamed fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
notilike(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.notilike()
method ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.在 1.4 版本发生变更: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
notin_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.notin_()
method ofColumnOperators
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. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.在 1.4 版本发生变更: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
notlike(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.notlike()
method ofColumnOperators
implement the
NOT LIKE
operator.This is equivalent to using negation with
ColumnOperators.like()
, i.e.~x.like(y)
.在 1.4 版本发生变更: The
not_like()
operator is renamed fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
nulls_first() ColumnOperators ¶ inherited from the
ColumnOperators.nulls_first()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.在 1.4 版本发生变更: The
nulls_first()
operator is renamed fromnullsfirst()
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 ofColumnOperators
Produce a
nulls_last()
clause against the parent object.在 1.4 版本发生变更: The
nulls_last()
operator is renamed fromnullslast()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
nullsfirst() ColumnOperators ¶ inherited from the
ColumnOperators.nullsfirst()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.在 1.4 版本发生变更: The
nulls_first()
operator is renamed fromnullsfirst()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
nullslast() ColumnOperators ¶ inherited from the
ColumnOperators.nullslast()
method ofColumnOperators
Produce a
nulls_last()
clause against the parent object.在 1.4 版本发生变更: The
nulls_last()
operator is renamed fromnullslast()
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 ofOperators
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
.- 参数:
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 (,
) andAS
operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.参见
我正在使用 op() 生成自定义运算符,但我的括号无法正确显示 - 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 theOperators.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 specifyOperators.op.is_comparison
will resolve toBoolean
, 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
在 2.0 版本加入.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
operate(op: OperatorType, *other: Any, **kwargs: Any) Operators ¶ inherited from the
Operators.operate()
method ofOperators
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 applyfunc.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)
-
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 ofColumnOperators
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
orx !~ y
when negated.Oracle Database - renders
REGEXP_LIKE(x, y)
SQLite - uses SQLite’s
REGEXP
placeholder operator and calls into the Pythonre.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 Database, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary.
- 参数:
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.
在 1.4 版本加入.
在 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.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
regexp_replace(pattern: Any, replacement: Any, flags: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.regexp_replace()
method ofColumnOperators
Implements a database-specific ‘regexp replace’ operator.
E.g.:
stmt = select( table.c.some_column.regexp_replace("b(..)", "XY", flags="g") )
ColumnOperators.regexp_replace()
attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the functionREGEXP_REPLACE()
. However, the specific regular expression syntax and flags available are not backend agnostic.Regular expression replacement support is currently implemented for Oracle Database, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary.
- 参数:
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.
在 1.4 版本加入.
在 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.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
remote_attr¶ The ‘remote’ class attribute referenced by this
AssociationProxyInstance
.
-
method
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
reverse_operate(op: OperatorType, other: Any, **kwargs: Any) Operators ¶ inherited from the
Operators.reverse_operate()
method ofOperators
Reverse operate on an argument.
Usage is the same as
operate()
.
-
attribute
sqlalchemy.ext.associationproxy.AssociationProxyInstance.
scalar¶ Return
True
if thisAssociationProxyInstance
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 ofColumnOperators
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, theColumnOperators.startswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
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 ofColumnOperators
Hack, allows datetime objects to be compared on the LHS.
-
method
- 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 ofColumnOperators
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 ofColumnOperators
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 ofColumnOperators
Produce an
all_()
clause against the parent object.See the documentation for
all_()
for examples.备注
be sure to not confuse the newer
ColumnOperators.all_()
method with the legacy version of this method, theComparator.all()
method that’s specific toARRAY
, 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 ofAssociationProxyInstance
Produce a proxied ‘any’ expression using EXISTS.
This expression will be a composed product using the
Comparator.any()
and/orComparator.has()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
any_() ColumnOperators ¶ inherited from the
ColumnOperators.any_()
method ofColumnOperators
Produce an
any_()
clause against the parent object.See the documentation for
any_()
for examples.备注
be sure to not confuse the newer
ColumnOperators.any_()
method with the legacy version of this method, theComparator.any()
method that’s specific toARRAY
, which uses a different calling style.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
asc() ColumnOperators ¶ inherited from the
ColumnOperators.asc()
method ofColumnOperators
Produce a
asc()
clause against the parent object.
-
attribute
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
attr¶ inherited from the
AssociationProxyInstance.attr
attribute ofAssociationProxyInstance
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()
orQuery.join()
with an association proxy, the current method is to make use of theAssociationProxyInstance.local_attr
andAssociationProxyInstance.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.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
between(cleft: Any, cright: Any, symmetric: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.between()
method ofColumnOperators
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 ofColumnOperators
Produce a bitwise AND operation, typically via the
&
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
bitwise_lshift(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_lshift()
method ofColumnOperators
Produce a bitwise LSHIFT operation, typically via the
<<
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
bitwise_not() ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_not()
method ofColumnOperators
Produce a bitwise NOT operation, typically via the
~
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
bitwise_or(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_or()
method ofColumnOperators
Produce a bitwise OR operation, typically via the
|
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
bitwise_rshift(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_rshift()
method ofColumnOperators
Produce a bitwise RSHIFT operation, typically via the
>>
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
bitwise_xor(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_xor()
method ofColumnOperators
Produce a bitwise XOR operation, typically via the
^
operator, or#
for PostgreSQL.在 2.0.2 版本加入.
参见
-
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 ofOperators
Return a custom boolean operator.
This method is shorthand for calling
Operators.op()
and passing theOperators.op.is_comparison
flag with True. A key advantage to usingOperators.bool_op()
is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
collate(collation: str) ColumnOperators ¶ inherited from the
ColumnOperators.collate()
method ofColumnOperators
Produce a
collate()
clause against the parent object, given the collation string.参见
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
concat(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.concat()
method ofColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause
a || b
, or uses theconcat()
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/orComparator.contains()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
desc() ColumnOperators ¶ inherited from the
ColumnOperators.desc()
method ofColumnOperators
Produce a
desc()
clause against the parent object.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
distinct() ColumnOperators ¶ inherited from the
ColumnOperators.distinct()
method ofColumnOperators
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 ofColumnOperators
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, theColumnOperators.endswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
has(criterion: _ColumnExpressionArgument[bool] | None = None, **kwargs: Any) ColumnElement[bool] ¶ inherited from the
AssociationProxyInstance.has()
method ofAssociationProxyInstance
Produce a proxied ‘has’ expression using EXISTS.
This expression will be a composed product using the
Comparator.any()
and/orComparator.has()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
icontains(other: Any, **kw: Any) ColumnOperators ¶ inherited from the
ColumnOperators.icontains()
method ofColumnOperators
Implement the
icontains
operator, e.g. case insensitive version ofColumnOperators.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, theColumnOperators.icontains.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
iendswith(other: Any, escape: str | None = None, autoescape: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.iendswith()
method ofColumnOperators
Implement the
iendswith
operator, e.g. case insensitive version ofColumnOperators.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, theColumnOperators.iendswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
ilike(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.ilike()
method ofColumnOperators
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%"))
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
in_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.in_()
method ofColumnOperators
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)
在 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 thebindparam.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 (?, ?, ?)
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)
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)
- 参数:
other¶ – a list of literals, a
select()
construct, or abindparam()
construct that includes thebindparam.expanding
flag set to True.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
is_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_()
method ofColumnOperators
Implement the
IS
operator.Normally,
IS
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS
may be desirable if comparing to boolean values on certain platforms.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
is_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_distinct_from()
method ofColumnOperators
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 ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.在 1.4 版本发生变更: The
is_not()
operator is renamed fromisnot()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
is_not_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_not_distinct_from()
method ofColumnOperators
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”.
在 1.4 版本发生变更: The
is_not_distinct_from()
operator is renamed fromisnot_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 ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.在 1.4 版本发生变更: The
is_not()
operator is renamed fromisnot()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
isnot_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.isnot_distinct_from()
method ofColumnOperators
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”.
在 1.4 版本发生变更: The
is_not_distinct_from()
operator is renamed fromisnot_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 ofColumnOperators
Implement the
istartswith
operator, e.g. case insensitive version ofColumnOperators.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, theColumnOperators.istartswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
like(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.like()
method ofColumnOperators
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%"))
-
attribute
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
local_attr¶ inherited from the
AssociationProxyInstance.local_attr
attribute ofAssociationProxyInstance
The ‘local’ class attribute referenced by this
AssociationProxyInstance
.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
match(other: Any, **kwargs: Any) ColumnOperators ¶ inherited from the
ColumnOperators.match()
method ofColumnOperators
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)
在 2.0 版本发生变更:
plainto_tsquery()
is used instead ofto_tsquery()
for PostgreSQL now; for compatibility with other forms, see 全文搜索.MySQL - renders
MATCH (x) AGAINST (y IN BOOLEAN MODE)
参见
match
- MySQL specific construct with additional features.Oracle Database - 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 ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.在 1.4 版本发生变更: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
not_in(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.not_in()
method ofColumnOperators
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. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.在 1.4 版本发生变更: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
not_like(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.not_like()
method ofColumnOperators
implement the
NOT LIKE
operator.This is equivalent to using negation with
ColumnOperators.like()
, i.e.~x.like(y)
.在 1.4 版本发生变更: The
not_like()
operator is renamed fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
notilike(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.notilike()
method ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.在 1.4 版本发生变更: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
notin_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.notin_()
method ofColumnOperators
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. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.在 1.4 版本发生变更: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
notlike(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.notlike()
method ofColumnOperators
implement the
NOT LIKE
operator.This is equivalent to using negation with
ColumnOperators.like()
, i.e.~x.like(y)
.在 1.4 版本发生变更: The
not_like()
operator is renamed fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
nulls_first() ColumnOperators ¶ inherited from the
ColumnOperators.nulls_first()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.在 1.4 版本发生变更: The
nulls_first()
operator is renamed fromnullsfirst()
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 ofColumnOperators
Produce a
nulls_last()
clause against the parent object.在 1.4 版本发生变更: The
nulls_last()
operator is renamed fromnullslast()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
nullsfirst() ColumnOperators ¶ inherited from the
ColumnOperators.nullsfirst()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.在 1.4 版本发生变更: The
nulls_first()
operator is renamed fromnullsfirst()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
nullslast() ColumnOperators ¶ inherited from the
ColumnOperators.nullslast()
method ofColumnOperators
Produce a
nulls_last()
clause against the parent object.在 1.4 版本发生变更: The
nulls_last()
operator is renamed fromnullslast()
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 ofOperators
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
.- 参数:
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 (,
) andAS
operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.参见
我正在使用 op() 生成自定义运算符,但我的括号无法正确显示 - 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 theOperators.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 specifyOperators.op.is_comparison
will resolve toBoolean
, 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
在 2.0 版本加入.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
operate(op: OperatorType, *other: Any, **kwargs: Any) Operators ¶ inherited from the
Operators.operate()
method ofOperators
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 applyfunc.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)
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
regexp_match(pattern: Any, flags: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.regexp_match()
method ofColumnOperators
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
orx !~ y
when negated.Oracle Database - renders
REGEXP_LIKE(x, y)
SQLite - uses SQLite’s
REGEXP
placeholder operator and calls into the Pythonre.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 Database, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary.
- 参数:
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.
在 1.4 版本加入.
在 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.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
regexp_replace(pattern: Any, replacement: Any, flags: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.regexp_replace()
method ofColumnOperators
Implements a database-specific ‘regexp replace’ operator.
E.g.:
stmt = select( table.c.some_column.regexp_replace("b(..)", "XY", flags="g") )
ColumnOperators.regexp_replace()
attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the functionREGEXP_REPLACE()
. However, the specific regular expression syntax and flags available are not backend agnostic.Regular expression replacement support is currently implemented for Oracle Database, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary.
- 参数:
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.
在 1.4 版本加入.
在 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.
-
attribute
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
remote_attr¶ inherited from the
AssociationProxyInstance.remote_attr
attribute ofAssociationProxyInstance
The ‘remote’ class attribute referenced by this
AssociationProxyInstance
.
-
method
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
reverse_operate(op: OperatorType, other: Any, **kwargs: Any) Operators ¶ inherited from the
Operators.reverse_operate()
method ofOperators
Reverse operate on an argument.
Usage is the same as
operate()
.
-
attribute
sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance.
scalar¶ inherited from the
AssociationProxyInstance.scalar
attribute ofAssociationProxyInstance
Return
True
if thisAssociationProxyInstance
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 ofColumnOperators
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, theColumnOperators.startswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
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 ofColumnOperators
Hack, allows datetime objects to be compared on the LHS.
-
method
- 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 ofColumnOperators
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 ofColumnOperators
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 ofColumnOperators
Implement the
!=
operator.In a column context, produces the clause
a != b
. If the target isNone
, producesa IS NOT NULL
.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
all_() ColumnOperators ¶ inherited from the
ColumnOperators.all_()
method ofColumnOperators
Produce an
all_()
clause against the parent object.See the documentation for
all_()
for examples.备注
be sure to not confuse the newer
ColumnOperators.all_()
method with the legacy version of this method, theComparator.all()
method that’s specific toARRAY
, 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 ofAssociationProxyInstance
Produce a proxied ‘any’ expression using EXISTS.
This expression will be a composed product using the
Comparator.any()
and/orComparator.has()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
any_() ColumnOperators ¶ inherited from the
ColumnOperators.any_()
method ofColumnOperators
Produce an
any_()
clause against the parent object.See the documentation for
any_()
for examples.备注
be sure to not confuse the newer
ColumnOperators.any_()
method with the legacy version of this method, theComparator.any()
method that’s specific toARRAY
, which uses a different calling style.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
asc() ColumnOperators ¶ inherited from the
ColumnOperators.asc()
method ofColumnOperators
Produce a
asc()
clause against the parent object.
-
attribute
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
attr¶ inherited from the
AssociationProxyInstance.attr
attribute ofAssociationProxyInstance
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()
orQuery.join()
with an association proxy, the current method is to make use of theAssociationProxyInstance.local_attr
andAssociationProxyInstance.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.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
between(cleft: Any, cright: Any, symmetric: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.between()
method ofColumnOperators
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 ofColumnOperators
Produce a bitwise AND operation, typically via the
&
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
bitwise_lshift(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_lshift()
method ofColumnOperators
Produce a bitwise LSHIFT operation, typically via the
<<
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
bitwise_not() ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_not()
method ofColumnOperators
Produce a bitwise NOT operation, typically via the
~
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
bitwise_or(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_or()
method ofColumnOperators
Produce a bitwise OR operation, typically via the
|
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
bitwise_rshift(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_rshift()
method ofColumnOperators
Produce a bitwise RSHIFT operation, typically via the
>>
operator.在 2.0.2 版本加入.
参见
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
bitwise_xor(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.bitwise_xor()
method ofColumnOperators
Produce a bitwise XOR operation, typically via the
^
operator, or#
for PostgreSQL.在 2.0.2 版本加入.
参见
-
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 ofOperators
Return a custom boolean operator.
This method is shorthand for calling
Operators.op()
and passing theOperators.op.is_comparison
flag with True. A key advantage to usingOperators.bool_op()
is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
collate(collation: str) ColumnOperators ¶ inherited from the
ColumnOperators.collate()
method ofColumnOperators
Produce a
collate()
clause against the parent object, given the collation string.参见
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
concat(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.concat()
method ofColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause
a || b
, or uses theconcat()
operator on MySQL.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
contains(other: Any, **kw: Any) ColumnOperators ¶ inherited from the
ColumnOperators.contains()
method ofColumnOperators
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, theColumnOperators.contains.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
desc() ColumnOperators ¶ inherited from the
ColumnOperators.desc()
method ofColumnOperators
Produce a
desc()
clause against the parent object.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
distinct() ColumnOperators ¶ inherited from the
ColumnOperators.distinct()
method ofColumnOperators
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 ofColumnOperators
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, theColumnOperators.endswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
has(criterion: _ColumnExpressionArgument[bool] | None = None, **kwargs: Any) ColumnElement[bool] ¶ inherited from the
AssociationProxyInstance.has()
method ofAssociationProxyInstance
Produce a proxied ‘has’ expression using EXISTS.
This expression will be a composed product using the
Comparator.any()
and/orComparator.has()
operators of the underlying proxied attributes.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
icontains(other: Any, **kw: Any) ColumnOperators ¶ inherited from the
ColumnOperators.icontains()
method ofColumnOperators
Implement the
icontains
operator, e.g. case insensitive version ofColumnOperators.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, theColumnOperators.icontains.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
iendswith(other: Any, escape: str | None = None, autoescape: bool = False) ColumnOperators ¶ inherited from the
ColumnOperators.iendswith()
method ofColumnOperators
Implement the
iendswith
operator, e.g. case insensitive version ofColumnOperators.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, theColumnOperators.iendswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
ilike(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.ilike()
method ofColumnOperators
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%"))
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
in_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.in_()
method ofColumnOperators
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)
在 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 thebindparam.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 (?, ?, ?)
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)
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)
- 参数:
other¶ – a list of literals, a
select()
construct, or abindparam()
construct that includes thebindparam.expanding
flag set to True.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
is_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_()
method ofColumnOperators
Implement the
IS
operator.Normally,
IS
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS
may be desirable if comparing to boolean values on certain platforms.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
is_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_distinct_from()
method ofColumnOperators
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 ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.在 1.4 版本发生变更: The
is_not()
operator is renamed fromisnot()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
is_not_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.is_not_distinct_from()
method ofColumnOperators
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”.
在 1.4 版本发生变更: The
is_not_distinct_from()
operator is renamed fromisnot_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 ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.在 1.4 版本发生变更: The
is_not()
operator is renamed fromisnot()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
isnot_distinct_from(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.isnot_distinct_from()
method ofColumnOperators
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”.
在 1.4 版本发生变更: The
is_not_distinct_from()
operator is renamed fromisnot_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 ofColumnOperators
Implement the
istartswith
operator, e.g. case insensitive version ofColumnOperators.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, theColumnOperators.istartswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
like(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.like()
method ofColumnOperators
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%"))
-
attribute
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
local_attr¶ inherited from the
AssociationProxyInstance.local_attr
attribute ofAssociationProxyInstance
The ‘local’ class attribute referenced by this
AssociationProxyInstance
.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
match(other: Any, **kwargs: Any) ColumnOperators ¶ inherited from the
ColumnOperators.match()
method ofColumnOperators
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)
在 2.0 版本发生变更:
plainto_tsquery()
is used instead ofto_tsquery()
for PostgreSQL now; for compatibility with other forms, see 全文搜索.MySQL - renders
MATCH (x) AGAINST (y IN BOOLEAN MODE)
参见
match
- MySQL specific construct with additional features.Oracle Database - 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 ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.在 1.4 版本发生变更: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
not_in(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.not_in()
method ofColumnOperators
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. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.在 1.4 版本发生变更: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
not_like(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.not_like()
method ofColumnOperators
implement the
NOT LIKE
operator.This is equivalent to using negation with
ColumnOperators.like()
, i.e.~x.like(y)
.在 1.4 版本发生变更: The
not_like()
operator is renamed fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
notilike(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.notilike()
method ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.在 1.4 版本发生变更: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
notin_(other: Any) ColumnOperators ¶ inherited from the
ColumnOperators.notin_()
method ofColumnOperators
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. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.在 1.4 版本发生变更: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
notlike(other: Any, escape: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.notlike()
method ofColumnOperators
implement the
NOT LIKE
operator.This is equivalent to using negation with
ColumnOperators.like()
, i.e.~x.like(y)
.在 1.4 版本发生变更: The
not_like()
operator is renamed fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
nulls_first() ColumnOperators ¶ inherited from the
ColumnOperators.nulls_first()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.在 1.4 版本发生变更: The
nulls_first()
operator is renamed fromnullsfirst()
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 ofColumnOperators
Produce a
nulls_last()
clause against the parent object.在 1.4 版本发生变更: The
nulls_last()
operator is renamed fromnullslast()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
nullsfirst() ColumnOperators ¶ inherited from the
ColumnOperators.nullsfirst()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.在 1.4 版本发生变更: The
nulls_first()
operator is renamed fromnullsfirst()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
nullslast() ColumnOperators ¶ inherited from the
ColumnOperators.nullslast()
method ofColumnOperators
Produce a
nulls_last()
clause against the parent object.在 1.4 版本发生变更: The
nulls_last()
operator is renamed fromnullslast()
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 ofOperators
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
.- 参数:
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 (,
) andAS
operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.参见
我正在使用 op() 生成自定义运算符,但我的括号无法正确显示 - 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 theOperators.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 specifyOperators.op.is_comparison
will resolve toBoolean
, 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
在 2.0 版本加入.
-
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 applyfunc.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)
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
regexp_match(pattern: Any, flags: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.regexp_match()
method ofColumnOperators
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
orx !~ y
when negated.Oracle Database - renders
REGEXP_LIKE(x, y)
SQLite - uses SQLite’s
REGEXP
placeholder operator and calls into the Pythonre.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 Database, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary.
- 参数:
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.
在 1.4 版本加入.
在 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.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
regexp_replace(pattern: Any, replacement: Any, flags: str | None = None) ColumnOperators ¶ inherited from the
ColumnOperators.regexp_replace()
method ofColumnOperators
Implements a database-specific ‘regexp replace’ operator.
E.g.:
stmt = select( table.c.some_column.regexp_replace("b(..)", "XY", flags="g") )
ColumnOperators.regexp_replace()
attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the functionREGEXP_REPLACE()
. However, the specific regular expression syntax and flags available are not backend agnostic.Regular expression replacement support is currently implemented for Oracle Database, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary.
- 参数:
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.
在 1.4 版本加入.
在 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.
-
attribute
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
remote_attr¶ inherited from the
AssociationProxyInstance.remote_attr
attribute ofAssociationProxyInstance
The ‘remote’ class attribute referenced by this
AssociationProxyInstance
.
-
method
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
reverse_operate(op: OperatorType, other: Any, **kwargs: Any) Operators ¶ inherited from the
Operators.reverse_operate()
method ofOperators
Reverse operate on an argument.
Usage is the same as
operate()
.
-
attribute
sqlalchemy.ext.associationproxy.ColumnAssociationProxyInstance.
scalar¶ inherited from the
AssociationProxyInstance.scalar
attribute ofAssociationProxyInstance
Return
True
if thisAssociationProxyInstance
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 ofColumnOperators
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, theColumnOperators.startswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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.- 参数:
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 theColumnOperators.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.
-
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 ofColumnOperators
Hack, allows datetime objects to be compared on the LHS.
-
method
- class sqlalchemy.ext.associationproxy.AssociationProxyExtensionType¶
Members
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 typeAssociationProxy
.Is assigned to the
InspectionAttr.extension_type
attribute.
-
attribute