Using SELECT Statements¶

CoreとORMの両方で、 select() 関数は全てのSELECT問い合わせで使用される Select 構文を生成します。Coreの Connection.execute() やORMの Session.execute() のようなメソッドに渡されると、現在のトランザクションでSELECT文が発行され、返された Result オブジェクトを介して結果の行が利用可能になります。

ORM読者 - ここの内容はコアとORMの両方の使用に等しく適用され、基本的なORMバリアントの使用例がここで言及されています。しかし、より多くのORM固有の機能も利用可能です。これらは ORM Querying Guide で文書化されています。

The select() SQL Expression Construct¶

select() 構文は、 insert() と同じように、それぞれのメソッドがより多くの状態をオブジェクト上に構築する generative アプローチを使用して、文を構築します。他のSQL構文と同様に、その場で文字列化することができます:

>>> fromsqlalchemyimportselect>>> stmt=select(user_table).where(user_table.c.name=="spongebob")>>> print(stmt)SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1

また、他のすべての文レベルのSQL構文と同じように、実際に文を実行するには、実行メソッドに渡します。SELECT文は行を返すので、結果オブジェクトを繰り返して Row オブジェクトを返すことができます。:

>>> withengine.connect()asconn:...     for row in conn.execute(stmt):...         print(row)BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
(1, 'spongebob', 'Spongebob Squarepants')ROLLBACK

ORMを使用する場合、特にORMエンティティに対して作成された select() 構文を使用する場合は、 Session の Session.execute() メソッドを使用して実行します。このアプローチを使用して、結果から Row オブジェクトを取得し続けますが、これらの行は、各行内の個々の要素として、 User cクラスのインスタンスなどの完全なエンティティを含めることができるようになりました。

>>> stmt=select(User).where(User.name=="spongebob")>>> withSession(engine)assession:...     for row in session.execute(stmt):...         print(row)BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)ROLLBACK

以下のセクションでは、SELECT構文について詳しく説明します。

Setting the COLUMNS and FROM clause¶

select() 関数は、任意の数の Column および/または Table 式を表す位置要素、および広範囲の互換性のあるオブジェクトを受け付けます。これらのオブジェクトは、結果セットの列として返される選択元のSQL式のリストに解決されます。これらの要素は、より単純なケースでは、渡された列やテーブルのような式から推測されるFROM句を作成するのにも役立ちます:

>>> print(select(user_table))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account

Coreアプローチを使用して個々の列からSELECTするために、 Column オブジェクトは Table.c アクセッサからアクセスされ、直接送ることができます;FROM句は、それらの列によって表されるすべての Table および他の FromClause オブジェクトの集合として推測されます:

>>> print(select(user_table.c.name,user_table.c.fullname))SELECT user_account.name, user_account.fullname
FROM user_account

あるいは、 Table などの任意の FromClause の FromClause.c コレクションを使用する場合、文字列名のタプルを使用して select() に複数の列を指定することができます:

>>> print(select(user_table.c["name","fullname"]))SELECT user_account.name, user_account.fullname
FROM user_account

New in version 2.0: Added tuple-accessor capability to the FromClause.c collection

Selecting ORM Entities and Columns¶

ORM Session.execute() メソッドを使って上記のような文を実行する場合、 user_table とは対照的に、 User のような完全なエンティティから選択する場合には重要な違いがあります。それは、 エンティティ自体が各行内の単一の要素として返される ということです。つまり、上記の文から行を取得する場合、取得するもののリストには User エンティティしかないので、 User クラスのインスタンスを含む要素を1つだけ持つ Row オブジェクトが返されます。:

>>> row=session.execute(select(User)).first()BEGIN...
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> row(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)

上記の Row には、 User エンティティを表す要素が1つだけあります:

>>> row[0]
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

上記と同じ結果を得るために非常に推奨される便利な方法は、 Session.scalars() メソッドを使用して文を直接実行することです。このメソッドは、各行の最初の”列”を一度に配信する ScalarResult オブジェクトを返します。この場合は、 User クラスのインスタンスです。

>>> user = session.scalars(select(User)).first()
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] (){stop}
>>> user
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

あるいは、クラスにバインドされた属性を使用して、ORMエンティティの個々の列を結果行の中の個別の要素として選択することもできます。これらが select() のような構成体に渡されると、 Column または各属性で表される他のSQL式に解決されます:

>>> print(select(User.name,User.fullname))SELECT user_account.name, user_account.fullname
FROM user_account

Session.execute() を使用して*this*文を呼び出すと、値ごとに個別の要素を持ち、それぞれが個別の列または他のSQL式に対応する行を受け取るようになりました。:

>>> row=session.execute(select(User.name,User.fullname)).first()SELECT user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> row('spongebob', 'Spongebob Squarepants')

以下のように、これらのアプローチを組み合わせることもできます。ここでは、行の最初の要素として User エンティティの name 属性を選択し、2番目の要素で完全な Address エンティティと組み合わせます。:

>>> session.execute(...     select(User.name, Address).where(User.id == Address.user_id).order_by(Address.id)... ).all()SELECT user_account.name, address.id, address.email_address, address.user_id
FROM user_account, address
WHERE user_account.id = address.user_id ORDER BY address.id
[...] ()
[('spongebob', Address(id=1, email_address='spongebob@sqlalchemy.org')),('sandy', Address(id=2, email_address='sandy@sqlalchemy.org')),('sandy', Address(id=3, email_address='sandy@squirrelpower.org'))]

ORMエンティティと列を選択する方法、および行を変換する一般的な方法については、 Selecting ORM Entities and Attributes で詳しく説明しています。

See also

Selecting ORM Entities and Attributes - ORM Querying Guide を参照してください。

Selecting from Labeled SQL Expressions¶

ColumnElement.label() メソッドおよびORM属性で利用可能な同名のメソッドは、列または式のSQLラベルを提供し、結果セット内で特定の名前を持つことを可能にします。これは、結果行内の任意のSQL式を名前で参照する場合に便利です。

>>> fromsqlalchemyimportfunc,cast>>> stmt=select(...     ("Username: " + user_table.c.name).label("username"),... ).order_by(user_table.c.name)>>> withengine.connect()asconn:...     for row in conn.execute(stmt):...         print(f"{row.username}")BEGIN (implicit)
SELECT ? || user_account.name AS username
FROM user_account ORDER BY user_account.name
[...] ('Username: ',)
Username: patrickUsername: sandyUsername: spongebobROLLBACK

See also

Ordering or Grouping by a Label - 作成したラベル名は、:class`_sql.Select`のORDER BY句やGROUP BY句でも参照できます。

Selecting with Textual Column Expressions¶

select() 関数を使用して Select オブジェクトを構築する場合、通常は table metadata を使用して定義された一連の Table および Column オブジェクトを渡します。または、ORMを使用する場合は、テーブル列を表すORMマップ属性を送信します。しかし、時には、定数文字列式のような任意のSQLブロックを文の中に作成したり、文字通りに書くのがより速い任意のSQLを作成したりする必要もあります。

Working with Transactions and the DBAPI で紹介された text() 構文は、実際には:class:_sqlに埋め込むことができます。Select`構文は、ハードコードされた文字列リテラル `’some phrase’`` を作成し、それをSELECT文の中に埋め込む以下のように、直接埋め込むことができます:

>>> fromsqlalchemyimporttext>>> stmt=select(text("'some phrase'"),user_table.c.name).order_by(user_table.c.name)>>> withengine.connect()asconn:...     print(conn.execute(stmt).all())BEGIN (implicit)
SELECT 'some phrase', user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')]ROLLBACK

text() 構文はほとんどの場所でリテラルSQL句を挿入するために使用できますが、実際には個々の列式を表すテキスト単位を扱っていることがよくあります。この一般的なケースでは、代わりに literal_column() 構文を使用して、テキスト断片からより多くの機能を得ることができます。このオブジェクトは text() に似ていますが、任意の形式のSQLを表すのではなく、単一の「列」を明示的に表し、サブクエリや他の式でラベルを付けて参照できる点が異なります:

>>> fromsqlalchemyimportliteral_column>>> stmt=select(literal_column("'some phrase'").label("p"),user_table.c.name).order_by(...     user_table.c.name... )>>> withengine.connect()asconn:...     for row in conn.execute(stmt):...         print(f"{row.p}, {row.name}")BEGIN (implicit)
SELECT 'some phrase' AS p, user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
some phrase, patricksome phrase, sandysome phrase, spongebobROLLBACK

どちらの場合も、 text() または literal_column() を使用する場合は、リテラル値ではなく、構文SQL式を作成していることに注意してください。したがって、描画したいSQLに必要な引用符や構文を含める必要があります。

The WHERE clause¶

SQLAlchemyでは、標準のPython演算子を Column や同様のオブジェクトと組み合わせて使用することで、 name='squidward' や user_id > 10 などのSQL式を作成することができます。ブール式の場合、 == 、 != 、 < 、 >= などのほとんどのPython演算子は、単純なブールの True / False の値ではなく、新しいSQL式オブジェクトを生成します。

>>> print(user_table.c.name == "squidward")
user_account.name = :name_1

>>> print(address_table.c.user_id > 10)
address.user_id > :user_id_1

次のような式を使用して、結果のオブジェクトを Select.where() メソッドに渡すことで、WHERE句を生成できます。:

>>> print(select(user_table).where(user_table.c.name=="squidward"))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1

ANDで結合された複数の式を生成するには、 Select.where() メソッドを何回でも呼び出すことができます:

>>> print(...     select(address_table.c.email_address)...     .where(user_table.c.name == "squidward")...     .where(address_table.c.user_id == user_table.c.id)... )SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

Select.where() を1回呼び出すだけでも、同じ効果を持つ複数の式を使用できます:

>>> print(...     select(address_table.c.email_address).where(...         user_table.c.name == "squidward",...         address_table.c.user_id == user_table.c.id,...     )... )SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

“AND”と”OR”の接続詞はどちらも and_() と or_() 関数を使って直接利用できます。以下にORMエンティティの観点から説明します:

>>> fromsqlalchemyimportand_,or_>>> print(...     select(Address.email_address).where(...         and_(...             or_(User.name == "squidward", User.name == "sandy"),...             Address.user_id == User.id,...         )...     )... )SELECT address.email_address
FROM address, user_account
WHERE (user_account.name = :name_1 OR user_account.name = :name_2)
AND address.user_id = user_account.id

単一のエンティティに対する単純な「等価性」比較のために、 Select.filter_by() として知られる一般的なメソッドもあります。これは、列キーまたはORM属性名に一致するキーワード引数を受け入れます。これは、一番左のFROM句または最後に結合されたエンティティに対してフィルタします:

>>> print(select(User).filter_by(name="spongebob",fullname="Spongebob Squarepants"))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1

See also

Operator Reference - descriptions of most SQL operator functions in SQLAlchemy

Explicit FROM clauses and JOINs¶

前述したように、FROM句は通常、columns節で設定した式や Select の他の要素に基づいて推論されます。

2つのテーブルからの列を置くと、カンマ区切りのFROM句が得られます。:

>>> print(select(user_table.c.name,address_table.c.email_address))SELECT user_account.name, address.email_address
FROM user_account, address

この2つのテーブルをJOINするには、通常 Select の2つのメソッドのどちらかを使用します。1つは Select.join_from() メソッドで、JOINの左側と右側を明示的に指定することができます。:

>>> print(...     select(user_table.c.name, address_table.c.email_address).join_from(...         user_table, address_table...     )... )SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

もう一つは Select.join() メソッドです。これはJOINの右側だけを示し、左側が推測されます:

>>> print(select(user_table.c.name,address_table.c.email_address).join(address_table))SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

また、columns節から思い通りに推論されない場合には、FROM句に明示的に要素を追加するオプションもあります。これを実現するために、以下のように Select.select_from() メソッドを使用します。ここでは、FROM句の最初の要素として user_table を設定し、2番目の要素として address_table を設定します。:

>>> print(select(address_table.c.email_address).select_from(user_table).join(address_table))SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

Select.select_from() を使用するもう1つの例は、columns節がFROM句を提供するのに十分な情報を持っていない場合です。例えば、一般的なSQL式 count(*) からSELECTするには、 sqlalchemy.sql.expression.func というSQLAlchemy要素を使用してSQL count() 関数を生成します:

>>> fromsqlalchemyimportfunc>>> print(select(func.count("*")).select_from(user_table))SELECT count(:count_2) AS count_1
FROM user_account

See also

ORM Querying Guide 内の Setting the leftmost FROM clause in a join には、 Select.select_from() と Select.join() の相互作用に関する追加の例と注意が含まれています。

Setting the ON Clause¶

これまでのJOINの例では、 Select 構文が2つのテーブル間を結合し、自動的にON句を生成することを示しました。これらの例では、このON句を生成するために使われる単一の ForeignKeyConstraint 定義が user_table オブジェクトと address_table オブジェクトの Table オブジェクトに含まれているため、このようなことが起こります。

結合の左ターゲットと右ターゲットにそのような制約がない場合、または複数の制約が設定されている場合は、ON句を直接指定する必要があります。 Select.join() と Select.join_from() はどちらも、ON句の追加引数を受け付けます。これは、 The WHERE clause で説明したのと同じSQL式の仕組みを使用して記述されます:

>>> print(...     select(address_table.c.email_address)...     .select_from(user_table)...     .join(address_table, user_table.c.id == address_table.c.user_id)... )SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

ORMのヒント - 前のセクションの Declaring Mapped Classes で設定されたマッピングのように、 relationship() 構文を利用するORMエンティティを使用するときにON句を生成する別の方法があります。これはそれ自体の主題全体であり、 Using Relationships to Join で詳細に紹介されています。

OUTER and FULL join¶

Select.join() メソッドと Select.join_from() メソッドはどちらもキーワード引数 Select.join.isouter と Select.join.full を受け入れ、それぞれLEFT OUTER JOINと全外部結合を返します:

>>> print(select(user_table).join(address_table,isouter=True))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id
>>> print(select(user_table).join(address_table,full=True))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id

Select.outerjoin() というメソッドもあり、これは .join(..., isouter=True) を使うのと同じです。

Tip

SQLにも”RIGHT OUTER JOIN”があります。SQLAlchemyはこれを直接表示するのではなく、テーブルの順序を逆にして”LEFT OUTER JOIN”を使用します。

ORDER BY, GROUP BY, HAVING¶

SELECT SQL文には、ORDER BYという句が含まれています。この句を使用して、指定した順序で選択したローを返します。

GROUP BY句はORDER BY句と同様に構成され、選択したローを特定のグループに細分化して、そのグループに対して集合関数を呼び出すことができます。HAVING句は通常GROUP BY句と一緒に使用され、WHERE句と似た形式ですが、グループ内で使用される集合関数に適用される点が異なります。

ORDER BY¶

ORDER BY句は、通常は Column や同様のオブジェクトに基づいたSQL式の構文で構成されます。 Select.order_by() メソッドは、位置的に次の式を1つ以上受け付けます:

>>> print(select(user_table).order_by(user_table.c.name))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.name

昇順/降順は ColumnElement.asc() 修飾子と ColumnElement.desc() 修飾子から使用できます。これらはORM結合属性からも存在します:

>>> print(select(User).order_by(User.fullname.desc()))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.fullname DESC

上記の文は、 user_account.fullname 列によって降順にソートされた行を生成します。

Aggregate functions with GROUP BY / HAVING¶

SQLでは、集計関数を使用すると、複数の行にまたがる列式をまとめて集計し、1つの結果を生成できます。たとえば、カウント、平均の計算、値セット内の最大値または最小値の検索などです。

SQLAlchemyは、 func として知られる名前空間を使用して、制限のない方法でSQL関数を提供します。これは特別なコンストラクタオブジェクトで、特定のSQL関数の名前が与えられると、 Function の新しいインスタンスを作成します。このSQL関数には、任意の名前を付けることができます。また、他のすべての場合と同様に、SQL式の構成体である0個以上の引数を関数に渡すこともできます。例えば、 user_account.id 列に対してSQL COUNT()関数を描画するには、 count() という名前を呼び出します。

>>> from sqlalchemy import func
>>> count_fn = func.count(user_table.c.id)
>>> print(count_fn)
{printsql}count(user_account.id)

SQL関数については、このチュートリアルの後半の Working with SQL Functions で詳しく説明します。

SQLで集計関数を使用する場合、GROUP BY句は、集計関数が各グループに個別に適用されるグループに行を分割できるという点で重要です。SELECT文のCOLUMNS句で集計されていない列を要求する場合、SQLでは、これらの列がすべてGROUP BY句の対象になる必要があります。これは、直接または主キーの関連付けに基づいて間接的に行われます。HAVING句は、直接の行の内容ではなく集計された値に基づいて行を除外する点を除いて、WHERE句と同様に使用されます。

SQLAlchemyは、 Select.group_by() と Select.having() メソッドを使って、これら2つの句を提供します。以下では、複数のアドレスを持つユーザに対して、ユーザ名フィールドとアドレスのカウントを選択する方法を説明します。:

.. sourcecode:: pycon+sql

>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(User.name, func.count(Address.id).label("count"))
...         .join(Address)
...         .group_by(User.name)
...         .having(func.count(Address.id) > 1)
...     )
...     print(result.all())
{execsql}BEGIN (implicit)
SELECT user_account.name, count(address.id) AS count
FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name
HAVING count(address.id) > ?
[...] (1,){stop}
[('sandy', 2)]
{execsql}ROLLBACK{stop}

Ordering or Grouping by a Label¶

特にいくつかのデータベースバックエンドでの重要なテクニックは、既にcolumns節に記述されている式をORDER BYまたはGROUP BYできることです。ORDER BY句やGROUP BY句で式を再記述せず、代わりにCOLUMNS節の列名やラベル付きの名前を使用します。この形式は、名前の文字列テキストを Select.order_by() または Select.group_by() メソッドに渡すことで使用できます。渡されるテキストは**直接描画されません**。代わりに、columns節の式に与えられた名前がコンテキスト内でその式の名前として描画され、一致が見つからない場合はエラーが発生します。単項修飾子 asc() と desc() もこの形式で使用できます。

>>> fromsqlalchemyimportfunc,desc>>> stmt=(...     select(Address.user_id, func.count(Address.id).label("num_addresses"))...     .group_by("user_id")...     .order_by("user_id", desc("num_addresses"))... )>>> print(stmt)SELECT address.user_id, count(address.id) AS num_addresses
FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC

Using Aliases¶

ここでは、複数のテーブルから選択してジョインを使用していますが、文のFROM句で同じテーブルを複数回参照する必要がある場合があります。これは、SQL aliases を使用して行います。SQL**aliases**は、文で参照できるテーブルまたはサブクエリに代替名を提供する構文です。

SQLAlchemy式言語では、これらの「名前」は代わりに FromClause オブジェクトによって表されます。これは Alias 構成体として知られており、Coreでは FromClause.alias() メソッドを使って構成されています。 Alias 構成体は、 Alias.c コレクション内の Column オブジェクトの名前空間も持つという点で、 Table 構成体とよく似ています。たとえば、次のSELECT文は、ユーザ名の一意のペアをすべて返します。:

>>> user_alias_1=user_table.alias()>>> user_alias_2=user_table.alias()>>> print(...     select(user_alias_1.c.name, user_alias_2.c.name).join_from(...         user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id...     )... )SELECT user_account_1.name, user_account_2.name AS name_1
FROM user_account AS user_account_1
JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id

ORM Entity Aliases¶

FromClause.alias() メソッドに相当するORM関数は、ORM aliased() 関数です。これは、 User や Address などのエンティティに適用できます。これは、ORM機能を維持しながら、元のマップされた Table オブジェクトに対して内部的に Alias オブジェクトを生成します。以下のSELECTは、2つの特定のメールアドレスを含むすべてのオブジェクトを User エンティティから選択します。

>>> from sqlalchemy.orm import aliased
>>> address_alias_1 = aliased(Address)
>>> address_alias_2 = aliased(Address)
>>> print(
...     select(User)
...     .join_from(User, address_alias_1)
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join_from(User, address_alias_2)
...     .where(address_alias_2.email_address == "patrick@gmail.com")
... )
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

Tip

Setting the ON Clause で説明されているように、ORMは relationship() 構文を使用して結合する別の方法を提供します。エイリアスを使用する上記の例は、 Using Relationship to join between aliased targets で relationship() を使用して説明されています。

Subqueries and CTEs¶

SQLの副問合せは、カッコ内に表示され、囲む文のコンテキスト内に配置されるSELECT文です。通常はSELECT文ですが、必須ではありません。

このセクションでは、いわゆる”非スカラー”サブクエリについて説明します。このサブクエリは通常、包含するSELECTのFROM句に配置されます。また、サブクエリと同様の方法で使用されますが、追加の機能を含む共通テーブル式(CTE)についても説明します。

SQLAlchemyはサブクエリを表現するために Subquery オブジェクトを使い、CTEを表現するために CTE を使います。CTEは通常、それぞれ Select.subquery() と Select.cte() メソッドから得られます。どちらのオブジェクトも、より大きな select() 構文の中でFROM要素として使うことができます。

Subquery を作成して、 address テーブルから行の総数を選択することができます(集約関数とGROUP BYは以前 Aggregate functions with GROUP BY / HAVING で導入されました)。

>>> subq = (
...     select(func.count(address_table.c.id).label("count"), address_table.c.user_id)
...     .group_by(address_table.c.user_id)
...     .subquery()
... )

副問い合わせを他の Select や他の文の中に埋め込むことなく、それ自身で文字列化すると、括弧を含まない普通のSELECT文が生成されます:

>>> print(subq)SELECT count(address.id) AS count, address.user_id
FROM address GROUP BY address.user_id

Subquery オブジェクトは、 Table のような他のFROMオブジェクトと同じように動作します。特に、選択する列の Subquery.c 名前空間が含まれています。この名前空間を使って、 user_id 列とカスタムのラベル付き``count`` 式の両方を参照することができます。

>>> print(select(subq.c.user_id, subq.c.count))
{printsql}SELECT anon_1.user_id, anon_1.count
FROM (SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id) AS anon_1

Common Table Expressions (CTEs)¶

SQLAlchemyにおける CTE 構文の使い方は、 Subquery 構文の使い方と実質的に同じです。代わりに Select.subquery() メソッドの呼び出しを Select.cte() を使うように変更することで、結果のオブジェクトを同じようにFROM要素として使うことができますが、描画されるSQLは非常に異なる一般的なテーブル式構文です:

>>> subq=(...     select(func.count(address_table.c.id).label("count"), address_table.c.user_id)...     .group_by(address_table.c.user_id)...     .cte()... )>>> stmt=select(user_table.c.name,user_table.c.fullname,subq.c.count).join_from(...     user_table, subq... )>>> print(stmt)WITH anon_1 AS
(SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id)
 SELECT user_account.name, user_account.fullname, anon_1.count
FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id

CTE 構文には、”再帰的”なスタイルで使用できる機能もあります。より複雑な場合には、INSERT、UPDATE、DELETE文のRETURNING句から構成することもできます。 CTE のdocstringには、これらの追加パターンの詳細が含まれています。

どちらの場合も、副問い合わせとCTEはSQLレベルで「匿名」の名前を使って命名されています。Pythonコードでは、これらの名前を提供する必要はまったくありません。 Subquery または CTE インスタンスのオブジェクトIDは、描画されるときのオブジェクトの構文IDとして機能します。SQLで描画される名前は、 Select.subquery() または Select.cte() メソッドの最初の引数として渡すことで提供できます。

See also

Select.subquery() - further detail on subqueries

Select.cte() - examples for CTE including how to use RECURSIVE as well as DML-oriented CTEs

ORM Entity Subqueries/CTEs¶

ORMでは、 aliased() 構文を使って、 User や Address クラスなどのORMエンティティを、行のソースを表す任意の FromClause 概念に関連付けることができます。前のセクション ORM Entity Aliases では、 aliased() を使って、マップされたクラスを、マップされた Table の Alias に関連付ける方法を説明しました。ここでは、 aliased() が、 Subquery と Select 構文に対して生成された CTE の両方に対して同じことを行う方法を説明します。 Select 構文は、最終的には同じマップされた Table から派生します。

以下は aliased() を Subquery 構文に適用して、その行からORMエンティティを抽出できるようにした例です。結果は一連の User オブジェクトと Address オブジェクトを表示します。各 Address オブジェクトのデータは、最終的には address テーブルに対するサブクエリから取得されたものであり、そのテーブルから直接取得されたものではありません。

>>> subq=select(Address).where(~Address.email_address.like("%@aol.com")).subquery()>>> address_subq=aliased(Address,subq)>>> stmt=(...     select(User, address_subq)...     .join_from(User, address_subq)...     .order_by(User.id, address_subq.id)... )>>> withSession(engine)assession:...     for user, address in session.execute(stmt):...         print(f"{user} {address}")BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account JOIN
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE address.email_address NOT LIKE ?) AS anon_1 ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.id
[...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')ROLLBACK

次の例もまったく同じですが、代わりに CTE 構文を使用しています。:

>>> cte_obj=select(Address).where(~Address.email_address.like("%@aol.com")).cte()>>> address_cte=aliased(Address,cte_obj)>>> stmt=(...     select(User, address_cte)...     .join_from(User, address_cte)...     .order_by(User.id, address_cte.id)... )>>> withSession(engine)assession:...     for user, address in session.execute(stmt):...         print(f"{user} {address}")BEGIN (implicit)
WITH anon_1 AS
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE address.email_address NOT LIKE ?)
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account
JOIN anon_1 ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.id
[...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')ROLLBACK

See also

Selecting Entities from Subqueries - ORM Querying Guide を参照してください。

Scalar and Correlated Subqueries¶

スカラー副問い合わせは、正確に0または1つの行と正確に1つの列を返す副問い合わせです。副問い合わせは、それを囲むSELECT文のCOLUMNS句またはWHERE句で使用されます。通常の副問い合わせとは異なり、FROM句では使用されません。 correlated subquery は、それを囲むSELECT文のテーブルを参照するスカラー副問い合わせです。

SQLAlchemyは ScalarSelect 構文を使ってスカラー副問い合わせを表現します。これは ColumnElement 式の階層の一部です。対照的に、通常の副問い合わせは Subquery 構文で表現され、 FromClause 階層にあります。

スカラー副問い合わせは、以前に Aggregate functions with GROUP BY / HAVING で紹介された集約関数と一緒に使用されることがよくありますが、必ずしもそうではありません。スカラー副問い合わせは、以下のように Select.scalar_subquery() メソッドを使用して明示的に示されます。それ自体で文字列化された場合のデフォルトの文字列形式は、2つのテーブルから選択する通常のSELECT文としてレンダリングされます:

>>> subq=(...     select(func.count(address_table.c.id))...     .where(user_table.c.id == address_table.c.user_id)...     .scalar_subquery()... )>>> print(subq)(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id)

上記の subq オブジェクトは ColumnElement SQL式の階層に含まれ、他の列式と同じように使うことができます。:

>>> print(subq==5)(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id) = :param_1

スカラー副問い合わせは、それ自身で文字列化された場合、FROM句に user_account と address の両方を描画しますが、それを user_account テーブルを扱う select() 構文に埋め込むと、 user_account テーブルは自動的に**相関**されます。つまり、副問い合わせのFROM句には描画されません。

>>> stmt = select(user_table.c.name, subq.label("address_count"))
>>> print(stmt)
{printsql}SELECT user_account.name, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account

通常、単純な相関サブクエリは期待通りの動作をします。しかし、相関があいまいな場合、SQLAlchemyはより明確にする必要があることを知らせてくれます。:

>>> stmt = (
...     select(
...         user_table.c.name,
...         address_table.c.email_address,
...         subq.label("address_count"),
...     )
...     .join_from(user_table, address_table)
...     .order_by(user_table.c.id, address_table.c.id)
... )
>>> print(stmt)
Traceback (most recent call last):
...
InvalidRequestError: Select statement '<... Select object at ...>' returned
no FROM clauses due to auto-correlation; specify correlate(<tables>) to
control correlation manually.

相関させようとしているのが user_table であることを指定するには、 ScalarSelect.correlate() または ScalarSelect.correlate_except() メソッドを使って指定します:

>>> subq = (
...     select(func.count(address_table.c.id))
...     .where(user_table.c.id == address_table.c.user_id)
...     .scalar_subquery()
...     .correlate(user_table)
... )

この文は、他の文と同様にこのカラムのデータを返します。:

>>> withengine.connect()asconn:...     result = conn.execute(...         select(...             user_table.c.name,...             address_table.c.email_address,...             subq.label("address_count"),...         )...         .join_from(user_table, address_table)...         .order_by(user_table.c.id, address_table.c.id)...     )...     print(result.all())BEGIN (implicit)
SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id
[...] ()
[('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2), ('sandy', 'sandy@squirrelpower.org', 2)]ROLLBACK

LATERAL correlation¶

LATERAL相関はSQL相関の特殊なサブカテゴリで、選択可能な単位が1つのFROM句内で別の選択可能な単位を参照できるようにします。これは非常に特殊なユースケースで、標準SQLの一部ではありますが、PostgreSQLの最近のバージョンでサポートされていることしか知られていません。

通常、SELECT文がFROM句の中で table1 JOIN (SELECT ...) AS subquery を参照している場合、右側の副問い合わせは左側の”table1”式を参照しない可能性があります。correlationは、このSELECTを完全に囲む別のSELECTの一部であるテーブルのみを参照する可能性があります。LATERALキーワードを使用すると、この動作を逆にして、右側のJOINからの相関を許可できます。

SQLAlchemyは Select.lateral() メソッドを使用してこの機能をサポートしています。このメソッドは Lateral という名前のオブジェクトを作成します。 Lateral は Subquery および Alias と同じ族に属していますが、この構文がSELECTを囲むFROM句に追加されたときの相関動作も含んでいます。次の例は、LATERALを使用して、前のセクションで説明した「ユーザーアカウント/電子メールアドレスのカウント」データを選択するSQLクエリを示しています。

>>> subq = (
...     select(
...         func.count(address_table.c.id).label("address_count"),
...         address_table.c.email_address,
...         address_table.c.user_id,
...     )
...     .where(user_table.c.id == address_table.c.user_id)
...     .lateral()
... )
>>> stmt = (
...     select(user_table.c.name, subq.c.address_count, subq.c.email_address)
...     .join_from(user_table, subq)
...     .order_by(user_table.c.id, subq.c.email_address)
... )
>>> print(stmt)
{printsql}SELECT user_account.name, anon_1.address_count, anon_1.email_address
FROM user_account
JOIN LATERAL (SELECT count(address.id) AS address_count,
address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE user_account.id = address.user_id) AS anon_1
ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.email_address

上の例では、JOINの右側は、結合の左側にある user_account テーブルに対応する副問い合わせです。

Select.lateral() を使用すると、 Select.correlate() メソッドと Select.correlate_except() メソッドの動作が Lateral 構文にも適用されます。

See also

Lateral

Select.lateral()

UNION, UNION ALL and other set operations¶

SQLでは、UNIONまたはUNION ALL SQL操作を使用してSELECT文をマージできます。この操作では、1つ以上の文によって生成されるすべてのローのセットが生成されます。INTERSECT [ALL]やEXCEPT [ALL]などの他の集合演算も可能です。

SQLAlchemyの Select 構文は、 union(),:func:_sql.intersect,:func:_sql.except_ のような関数と、”all”に対応する union_all(),:func:_sql.intersect_all,:func:_sql.except_all のような関数を使って、この種の合成をサポートしています。これらの関数はすべて、任意の数の副選択可能なものを受け付けます。副選択可能なものは通常 Select 構文ですが、既存の合成も可能です。

これらの関数によって生成される構文は CompoundSelect です。これは Select 構文と同じ方法で使用されますが、メソッドの数が少なくなります。例えば union_all() によって生成される CompoundSelect は、 Connection.execute() を使って直接呼び出すことができます:

>>> fromsqlalchemyimportunion_all>>> stmt1=select(user_table).where(user_table.c.name=="sandy")>>> stmt2=select(user_table).where(user_table.c.name=="spongebob")>>> u=union_all(stmt1,stmt2)>>> withengine.connect()asconn:...     result = conn.execute(u)...     print(result.all())BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[generated in ...] ('sandy', 'spongebob')
[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')]ROLLBACK

CompoundSelect を Select と同じように副問い合わせとして使用すると、 SelectBase.subquery() メソッドが提供されます。このメソッドは、 FromClause.c コレクションを持つ Subquery オブジェクトを生成します。このコレクションは select() で参照できます:

>>> u_subq=u.subquery()>>> stmt=(...     select(u_subq.c.name, address_table.c.email_address)...     .join_from(address_table, u_subq)...     .order_by(u_subq.c.name, address_table.c.email_address)... )>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     print(result.all())BEGIN (implicit)
SELECT anon_1.name, address.email_address
FROM address JOIN
  (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  FROM user_account
  WHERE user_account.name = ?
UNION ALL
  SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  FROM user_account
  WHERE user_account.name = ?)
AS anon_1 ON anon_1.id = address.user_id
ORDER BY anon_1.name, address.email_address
[generated in ...] ('sandy', 'spongebob')
[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')]ROLLBACK

Selecting ORM Entities from Unions¶

これまでの例では、2つの Table オブジェクトを指定してUNIONを構築し、データベースの行を返す方法を説明しました。UNIONやその他の集合演算を使用して、ORMオブジェクトとして受け取る行を選択したい場合、使用できるアプローチは2つあります。どちらの場合も、まず、実行したいSELECT/UNION/etc文を表す select() または CompoundSelect オブジェクトを構築します。この文は、ターゲットのORMエンティティまたはその基礎となるマップされた Table オブジェクトに対して構成する必要があります:

>>> stmt1 = select(User).where(User.name == "sandy")
>>> stmt2 = select(User).where(User.name == "spongebob")
>>> u = union_all(stmt1, stmt2)

副問い合わせ内にまだネストされていないUNIONを持つ単純なSELECTの場合、 Select.from_statement() メソッドを使用して、コンテキストを取得するORMオブジェクト内でこれらを使用することができます。このアプローチでは、UNION文は問い合わせ全体を表します。 Select.from_statement() が使用された後に追加の条件を追加することはできません。

>>> orm_stmt = select(User).from_statement(u)
>>> with Session(engine) as session:
...     for obj in session.execute(orm_stmt).scalars():
...         print(obj)
{execsql}BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[generated in ...] ('sandy', 'spongebob')
{stop}User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
{execsql}ROLLBACK{stop}

UNIONまたはその他のセット関連の構文をエンティティ関連のコンポーネントとしてより柔軟に使用するには、 CompoundSelect 構文を CompoundSelect.subquery() を使用してサブクエリに編成し、 aliased() 関数を使用してORMオブジェクトにリンクします。これは ORM Entity Subqueries/CTEs で紹介された方法と同じように機能します。まず目的のエンティティのサブクエリへのアドホックな「マッピング」を作成し、次にその新しいエンティティから他のマップされたクラスであるかのように選択します。次の例では、サブクエリによってエクスポートされた列によってフィルタまたはソートできるため、UNION自体の外部にORDER BYなどの追加の基準を追加できます。

>>> user_alias = aliased(User, u.subquery())
>>> orm_stmt = select(user_alias).order_by(user_alias.id)
>>> with Session(engine) as session:
...     for obj in session.execute(orm_stmt).scalars():
...         print(obj)
{execsql}BEGIN (implicit)
SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.name = ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.name = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] ('sandy', 'spongebob')
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
{execsql}ROLLBACK{stop}

EXISTS subqueries¶

SQL EXISTSキーワードは scalar subqueries と共に使用される演算子で、SELECT文が行を返すかどうかに応じて真偽を返します。SQLAlchemyには ScalarSelect オブジェクトの変形である Exists が含まれています。これはEXISTS副問い合わせを生成しますが、最も便利なのは SelectBase.exists() メソッドを使用して生成されます。以下ではEXISTSを生成して、 address に複数の関連する行を持つ user_account 行を返すことができます。:

>>> subq=(...     select(func.count(address_table.c.id))...     .where(user_table.c.id == address_table.c.user_id)...     .group_by(address_table.c.user_id)...     .having(func.count(address_table.c.id) > 1)... ).exists()>>> withengine.connect()asconn:...     result = conn.execute(select(user_table.c.name).where(subq))...     print(result.all())BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE EXISTS (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id GROUP BY address.user_id
HAVING count(address.id) > ?)
[...] (1,)
[('sandy',)]ROLLBACK

EXISTS構文は、否定として使用されることがよくあります。たとえば、NOT EXISTSは、関連するテーブルに行がない行を見つけるためのSQL効率的な形式を提供します。以下では、電子メールアドレスを持たないユーザ名を選択します。2番目のWHERE句内で使用されているバイナリ否定演算子 (~) に注意してください。

>>> subq=(...     select(address_table.c.id).where(user_table.c.id == address_table.c.user_id)... ).exists()>>> withengine.connect()asconn:...     result = conn.execute(select(user_table.c.name).where(~subq))...     print(result.all())BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE NOT (EXISTS (SELECT address.id
FROM address
WHERE user_account.id = address.user_id))
[...] ()
[('patrick',)]ROLLBACK

Working with SQL Functions¶

このセクションの Aggregate functions with GROUP BY / HAVING で最初に紹介された func オブジェクトは、新しい Function オブジェクトを作成するためのファクトリとして機能します。これは、 select() のような構文で使用されると、通常は名前、いくつかの括弧(常にではありませんが)、および場合によってはいくつかの引数で構成されるSQL関数表示を生成します。一般的なSQL関数の例を次に示します。

count() 関数、返された行数を数える集約関数:

>>> print(select(func.count()).select_from(user_table))SELECT count(*) AS count_1
FROM user_account

lower() 関数、文字列を小文字に変換する文字列関数:

>>> print(select(func.lower("A String With Much UPPERCASE")))SELECT lower(:lower_2) AS lower_1

現在の日付と時刻を提供する now() 関数。これは一般的な関数であるため、SQLAlchemyはバックエンドごとに異なる方法でこれを表示する方法を知っています。SQLiteの場合はCURRENT_TIMESTAMP関数を使用します。
```
>>> stmt = select(func.now())
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     print(result.all())
BEGIN (implicit)
SELECT CURRENT_TIMESTAMP AS now_1
[...] ()
[(datetime.datetime(...),)]
ROLLBACK
```

ほとんどのデータベースバックエンドには、何百とは言わないまでも、何十もの異なるSQL関数がありますので、 func はできるだけ自由に使えるようにしています。この名前空間からアクセスされた名前は、自動的に一般的な方法で描画されるSQL関数とみなされます:

>>> print(select(func.some_crazy_function(user_table.c.name,17)))SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1
FROM user_account

同時に、非常に一般的なSQL関数の比較的少数のセット、例えば count,:class:_functions.now,:class:_functions.max,:class:_functions.concat などには、適切な型情報や、場合によってはバックエンド固有のSQL生成を提供する、事前にパッケージ化されたバージョンが含まれています。以下の例では、 now 関数について、PostgreSQLダイアレクトとOracleダイアレクトで発生するSQL生成を比較しています:

>>> fromsqlalchemy.dialectsimportpostgresql>>> print(select(func.now()).compile(dialect=postgresql.dialect()))SELECT now() AS now_1
>>> fromsqlalchemy.dialectsimportoracle>>> print(select(func.now()).compile(dialect=oracle.dialect()))SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL

Functions Have Return Types¶

関数は列式なので、生成されたSQL式のデータ型を記述するSQL datatypes もあります。ここでは、これらの型を”SQL戻り型”と呼びます。これは、Python関数の”戻り型”とは対照的に、データベース側のSQL式のコンテキストで関数によって返されるSQL値の型を指します。

任意のSQL関数のSQL戻り値の型は、通常はデバッグ目的で、 Function.type 属性を参照することでアクセスできます。これは非常に一般的なSQL関数の いくつかを選択 するために事前に設定されていますが、ほとんどのSQL関数では、特に指定がなければ”null”データ型になります:

>>> # pre-configured SQL function (only a few dozen of these)
>>> func.now().type
DateTime()

>>> # arbitrary SQL function (all other SQL functions)
>>> func.run_some_calculation().type
NullType()

これらのSQLの戻り値の型は、より大きな式のコンテキストで関数式を使用する場合に重要です。つまり、数式演算子は、式のデータ型が Integer や Numeric のような場合にうまく機能します。JSONアクセサが機能するためには、 JSON のような型を使用する必要があります。特定のクラスの関数は、特定の列を参照する必要がある場合に、列の値ではなく行全体を返します。このような関数は table valued functions として知られています。

関数のSQLの戻り値の型は、SQLAlchemyが結果セット処理を適用する必要がある場合に、文を実行して行を取得するときにも重要になることがあります。その代表的な例がSQLiteの日付関連の関数で、SQLAlchemyの DateTime と関連するデータ型が、結果行を受け取るときに文字列値からPythonの DateTime() オブジェクトに変換する役割を果たします。

作成中の関数に特定の型を適用するには、 Function.type_ パラメータを使用して渡します。type引数は TypeEngine クラスまたはインスタンスのいずれかです。以下の例では、 JSON クラスを渡してPostgreSQLの json_object() 関数を生成しますが、SQLの戻り値の型はJSON型になることに注意してください。

>>> from sqlalchemy import JSON
>>> function_expr = func.json_object('{a, 1, b, "def", c, 3.5}', type_=JSON)

JSON データ型を持つJSON関数を作成することで、SQL式オブジェクトは要素へのアクセスなどのJSON関連の機能を持つようになります:

>>> stmt=select(function_expr["def"])>>> print(stmt)SELECT json_object(:json_object_1)[:json_object_2] AS anon_1

Built-in Functions Have Pre-Configured Return Types¶

count 、 max 、 min のような一般的な集約関数や、 now のような非常に少数の日付関数、 concat のような文字列関数では、SQLの戻り値の型が適切に設定されます。時には使用法に基づいて設定されます。 max 関数や同様の集約フィルタ関数では、与えられた引数に基づいてSQLの戻り値の型が設定されます:

>>> m1 = func.max(Column("some_int", Integer))
>>> m1.type
Integer()

>>> m2 = func.max(Column("some_str", String))
>>> m2.type
String()

日付と時刻の関数は通常、 DateTime 、 Date または Time で記述されるSQL式に対応します:

>>> func.now().type
DateTime()
>>> func.current_date().type
Date()

concat などの既知の文字列関数は、SQL式が String 型であることを認識します:

>>> func.concat("x", "y").type
String()

しかし、ほとんどのSQL関数では、SQLAlchemyは既知の関数の非常に小さなリストに明示的に存在していません。例えば、SQL関数の func.lower() や func.upper() を使って文字列の大文字小文字を変換する問題は通常ありませんが、SQLAlchemyは実際にはこれらの関数を認識しないので、SQLの戻り値の型は「null」です:

>>> func.upper("lowercase").type
NullType()

upper や lower``のような単純な関数では、この問題は通常重要ではありません。というのは、SQLAlchemy側で特別な型処理をしなくても、データベースから文字列値を受け取ることができるからです。また、SQLAlchemyの型強制規則は、しばしば意図を正しく推測することもできます。例えばPythonの ``+ 演算子は、式の両側を見ることに基づいて、文字列連結演算子として正しく解釈されます。

>>> print(select(func.upper("lowercase") + " suffix"))
{printsql}SELECT upper(:upper_1) || :upper_2 AS anon_1

全体として、 Function.type_ パラメータが必要になるシナリオは次のようになります。:

関数がまだSQLAlchemy組み込み関数ではないこと。これは、関数を作成し、 Function.type 属性を調べることで証明できます。つまり:
```
>>> func.count().type
Integer()
```
vs.:
```
>>> func.json_object('{"a", "b"}').type
NullType()
```

関数を意識した式のサポートが必要です。これは通常、 JSON や ARRAY のようなデータ型に関連する特殊な演算子を指します。

結果値の処理が必要です。これには、 DateTime 、 Boolean 、 Enum などの型や、 JSON 、 ARRAY などの特殊なデータ型が含まれます。

Advanced SQL Function Techniques¶

以下のサブセクションでは、SQL関数を使用して実行できるその他のことについて説明します。これらのテクニックは、基本的なSQL関数の使用ほど一般的ではなく、より高度ですが、それでも非常に一般的です。これは主に、PostgreSQLが、JSONデータで一般的なテーブル値や列値の形式など、より複雑な関数形式に重点を置いているためです。

Using Window Functions¶

window関数はSQLの集約関数の特殊な使用法で、個々の結果の行が処理されるときに、グループ内で返される行の集約値を計算します。一方、 MAX() のような関数は、行集合内の列の最大値を返しますが、”window関数”と同じ関数を使用すると、各行の その行の 最大値を返します。

SQLでは、ウィンドウ関数を使用して、関数を適用する行、行のさまざまなサブセットに対してウィンドウを考慮する”partition”値、および集約関数に適用するローの順序を示す”order by”式を指定できます。

SQLAlchemyでは、 func 名前空間で生成される全てのSQL関数は FunctionElement.over() メソッドを含んでいます。このメソッドはwindow関数または”OVER”構文を与えます。生成される構文は Over 構文です。

ウィンドウ関数と共に使用される一般的な関数は、単に行を数える row_number() 関数です。この行数をユーザ名に対して分割して、個々のユーザのメールアドレスに番号を付けることができます。

>>> stmt=(...     select(...         func.row_number().over(partition_by=user_table.c.name),...         user_table.c.name,...         address_table.c.email_address,...     )...     .select_from(user_table)...     .join(address_table)... )>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     print(result.all())BEGIN (implicit)
SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
[(1, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (1, 'spongebob', 'spongebob@sqlalchemy.org')]ROLLBACK

上の例では、 FunctionElement.over.partition_by パラメータが使用されているので、OVER句の中で PARTITION BY 句がレンダリングされます。また、 FunctionElement.over.order_by を使って ORDER BY 句を使用することもできます。

>>> stmt=(...     select(...         func.count().over(order_by=user_table.c.name),...         user_table.c.name,...         address_table.c.email_address,...     )...     .select_from(user_table)...     .join(address_table)... )>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     print(result.all())BEGIN (implicit)
SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
[(2, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (3, 'spongebob', 'spongebob@sqlalchemy.org')]ROLLBACK

Window関数のその他のオプションには、範囲の使用が含まれます。他の例については over() を参照してください。

Tip

FunctionElement.over() メソッドは、実際に集約関数であるSQL関数にのみ適用されることに注意してください。一方、 Over 構文は、与えられたSQL関数に対してそれ自身をレンダリングしますが、関数自体がSQL集約関数でない場合、データベースは式を拒否します。

Special Modifiers WITHIN GROUP, FILTER¶

“WITHIN GROUP”SQL構文は、”ordered set”または”hypothetical set”集約関数と組み合わせて使用されます。一般的な”ordered set”関数には、 percentile_cont() と rank() があります。SQLAlchemyには、 rank 、 dense_rank 、mode 、 percentile_cont および percentile_disc の組み込み実装が含まれており、これらには FunctionElement.within_group() メソッドが含まれています:

>>> print(...     func.unnest(...         func.percentile_disc([0.25, 0.5, 0.75, 1]).within_group(user_table.c.name)...     )... )unnest(percentile_disc(:percentile_disc_1) WITHIN GROUP (ORDER BY user_account.name))

“FILTER”は、 FunctionElement.filter() メソッドを使用して、返される行の合計範囲と比較して、集約関数の範囲を特定の行のサブセットに制限するために、いくつかのバックエンドでサポートされています:

>>> stmt=(...     select(...         func.count(address_table.c.email_address).filter(user_table.c.name == "sandy"),...         func.count(address_table.c.email_address).filter(...             user_table.c.name == "spongebob"...         ),...     )...     .select_from(user_table)...     .join(address_table)... )>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     print(result.all())BEGIN (implicit)
SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1,
count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ('sandy', 'spongebob')
[(2, 1)]ROLLBACK

Table-Valued Functions¶

テーブル値を持つSQL関数は、名前付きの副要素を含むスカラー表現をサポートしています。JSONやAR RAY指向の関数や generate_series() のような関数でよく使われますが、テーブル値を持つ関数はFROM句で指定され、テーブルとして、時には列としても参照されます。この形式の関数はPostgreSQLデータベース内では顕著ですが、テーブル値を持つ関数のいくつかの形式はSQLite、Oracle、SQL Serverでもサポートされています。

多くのデータベースがテーブル値やその他の特殊な形式をサポートしていますが、PostgreSQLではこれらの機能が最も必要とされる傾向があります。PostgreSQL構文のその他の例やその他の機能については、このセクションを参照してください。

SQLAlchemyは FunctionElement.table_valued() メソッドを基本的な”table valued function”構文として提供しています。これは func オブジェクトを、位置的に渡された文字列名に基づいて、一連の名前付き列を含むFROM句に変換します。これは TableValuedAlias オブジェクトを返します。これは関数対応の Alias 構文で、 Using Aliases で紹介されている他のFROM句として使用できます。以下では、PostgreSQLでは一般的ですが、最近のバージョンのSQLiteでもサポートされている json_each() 関数について説明します。:

>>> onetwothree=func.json_each('["one", "two", "three"]').table_valued("value")>>> stmt=select(onetwothree).where(onetwothree.c.value.in_(["two","three"]))>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     result.all()BEGIN (implicit)
SELECT anon_1.value
FROM json_each(?) AS anon_1
WHERE anon_1.value IN (?, ?)
[...] ('["one", "two", "three"]', 'two', 'three')
[('two',), ('three',)]ROLLBACK

上記では、SQLiteとPostgreSQLでサポートされているJSON関数 json_each() を使用して、 value として参照される1つの列を持つテーブル値式を生成し、その3つの行のうち2つを選択しました。

See also

Table-Valued Functions - PostgreSQL ドキュメント内-この節では、PostgreSQLで動作することが知られている特別な列の派生や”WITH ORDINALITY”などの追加の構文について詳しく説明します。

Column Valued Functions - Table Valued Function as a Scalar Column¶

PostgreSQLとOracleでサポートされている特別な構文は、FROM句内の関数を参照し、SELECT文または他の列式のコンテキストのcolumns句内の単一の列として自分自身を提供するというものです。PostgreSQLでは、この構文を json_array_elements() 、 json_object_keys() 、 json_each_text() 、 json_each() などの関数で大いに利用しています。

SQLAlchemyはこれを”column valued”関数と呼び、 FunctionElement.column_valued() 修飾子を Function 構文に適用することで利用できます:

>>> fromsqlalchemyimportselect,func>>> stmt=select(func.json_array_elements('["one", "two"]').column_valued("x"))>>> print(stmt)SELECT x
FROM json_array_elements(:json_array_elements_1) AS x

“column valued”形式はOracleダイアレクトでもサポートされており、カスタムSQL関数で使用できます。:

>>> fromsqlalchemy.dialectsimportoracle>>> stmt=select(func.scalar_strings(5).column_valued("s"))>>> print(stmt.compile(dialect=oracle.dialect()))SELECT s.COLUMN_VALUE
FROM TABLE (scalar_strings(:scalar_strings_1)) s

See also

Column Valued Functions - PostgreSQL を参照してください。

Data Casts and Type Coercion

SQLでは、式のデータ型を明示的に示す必要があることがよくあります。これは、あいまいな式でどのような型が期待されているかをデータベースに伝えるため、または場合によってはSQL式の暗黙のデータ型を別のものに変換したいときに必要です。このタスクにはSQL CASTキーワードが使用されますが、SQLAlchemyでは cast() 関数によって提供されます。この関数は引数として列式とデータ型オブジェクトを受け入れます。以下に示すように、ここではSQL式 CAST(user_account.id AS VARCHAR) を user_table.c.id 列オブジェクトから生成します。:

>>> fromsqlalchemyimportcast>>> stmt=select(cast(user_table.c.id,String))>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     result.all()BEGIN (implicit)
SELECT CAST(user_account.id AS VARCHAR) AS id
FROM user_account
[...] ()
[('1',), ('2',), ('3',)]ROLLBACK

cast() 関数は、SQL CAST構文を描画するだけでなく、Python側で指定されたデータ型として動作するSQLAlchemy列式も生成します。 JSON への cast() である文字列式は、JSONの添字と比較演算子を取得します。例えば:

>>> fromsqlalchemyimportJSON>>> print(cast("{'a': 'b'}",JSON)["a"])CAST(:param_1 AS JSON)[:param_2]

type_coerce() - a Python-only “cast”¶

上記のすべての理由から、式のデータ型をSQLAlchemyに認識させる必要がある場合がありますが、CAST式自体をSQL側でレンダリングしないようにする必要があります。これは、CAST式がなくてもすでに動作しているSQL操作に干渉する可能性があります。この非常に一般的なユースケースには、 type_coerce() という別の関数があります。これは cast() と密接に関連しています。特定のSQLデータベース型を持つPython式を設定しますが、データベース側では CAST キーワードやデータ型をレンダリングしません。 type_coerce() は、 JSON データ型を扱う場合に特に重要です。 JSON データ型は、通常、さまざまなプラットフォームで文字列指向のデータ型と複雑な関係を持ち、SQLiteやMariaDBのように明示的なデータ型でない場合もあります。以下では、 type_coerce() を使用して、Python構造をJSON文字列としてMySQLのJSON関数の1つに渡します。:

>>> importjson>>> fromsqlalchemyimportJSON>>> fromsqlalchemyimporttype_coerce>>> fromsqlalchemy.dialectsimportmysql>>> s=select(type_coerce({"some_key":{"foo":"bar"}},JSON)["some_key"])>>> print(s.compile(dialect=mysql.dialect()))SELECT JSON_EXTRACT(%s, %s) AS anon_1

上の例では、MySQLのSQL関数 JSON_EXTRACT が呼び出されたのは、Python辞書が JSON として扱われるべきであることを示すために type_coerce() を使用したからです。Pythonの __getitem__ 演算子、この場合は ['some_key'] が結果として利用可能になり、 JSON_EXTRACT パス式(ここには示されていませんが、この場合は最終的に '$."some_key"' になります)を描画できるようになりました。

SQLAlchemy 2.0 Documentation - 私家版日本語訳ドキュメント

SQLAlchemy 2.0 Documentation - 私家版日本語訳ドキュメント

SQLAlchemy Unified Tutorial

Using SELECT Statements¶

The select() SQL Expression Construct¶

Setting the COLUMNS and FROM clause¶

Selecting ORM Entities and Columns¶

Selecting from Labeled SQL Expressions¶

Selecting with Textual Column Expressions¶

The WHERE clause¶

Explicit FROM clauses and JOINs¶

Setting the ON Clause¶

OUTER and FULL join¶

ORDER BY, GROUP BY, HAVING¶

ORDER BY¶

Aggregate functions with GROUP BY / HAVING¶

Ordering or Grouping by a Label¶

Using Aliases¶

ORM Entity Aliases¶

Subqueries and CTEs¶

Common Table Expressions (CTEs)¶

ORM Entity Subqueries/CTEs¶

Scalar and Correlated Subqueries¶

LATERAL correlation¶

UNION, UNION ALL and other set operations¶

Selecting ORM Entities from Unions¶

EXISTS subqueries¶

Working with SQL Functions¶

Functions Have Return Types¶

Built-in Functions Have Pre-Configured Return Types¶

Advanced SQL Function Techniques¶

Using Window Functions¶

Special Modifiers WITHIN GROUP, FILTER¶

Table-Valued Functions¶

Column Valued Functions - Table Valued Function as a Scalar Column¶

type_coerce() - a Python-only “cast”¶