SQLAlchemyコア-セット操作の使用

前の章では、max（）、min（）、count（）などのさまざまな関数について学びました。ここでは、集合演算とその使用法について学びます。

UNIONやINTERSECTなどの集合演算は、標準SQLとそのほとんどの方言でサポートされています。 SQLAlchemyは、次の機能の助けを借りてそれらを実装します-

連合（）

2つ以上のSELECTステートメントの結果を組み合わせながら、UNIONは結果セットから重複を削除します。列とデータ型の数は両方のテーブルで同じでなければなりません。

union（）関数は、複数のテーブルからCompoundSelectオブジェクトを返します。次の例は、その使用を示しています-

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()
conn = engine.connect()
addresses = Table(
   'addresses', meta,
   Column('id', Integer, primary_key = True),
   Column('st_id', Integer),
   Column('postal_add', String),
   Column('email_add', String)
)

u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))))

result = conn.execute(u)
result.fetchall()

ユニオン構造は、次のSQL式に変換されます-

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

アドレス表から、次の行はユニオン操作を表します-

[
   (1, 1, 'Shivajinagar Pune', 'ravi@gmail.com'),
   (2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com'),
   (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com'),
   (4, 5, 'MG Road Bangaluru', 'as@yahoo.com')
]

union_all（）

UNION ALL操作では、重複を削除できず、結果セット内のデータをソートできません。たとえば、上記のクエリでは、効果を確認するためにUNIONがUNION ALLに置き換えられています。

u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))

対応するSQL式は次のとおりです-

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION ALL SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

except_（）

SQL EXCEPT 句/演算子は、2つのSELECTステートメントを結合し、2番目のSELECTステートメントによって返されない最初のSELECTステートメントから行を返すために使用されます。 except_（）関数は、EXCEPT句を含むSELECT式を生成します。

次の例では、except_（）関数は、email_addフィールドに「gmail.com」が含まれる住所テーブルのレコードのみを返しますが、postal_addフィールドの一部として「Pune」が含まれるレコードは除外します。

u = except_(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

上記のコードの結果は、次のSQL式です-

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? EXCEPT SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

アドレステーブルに以前の例で使用されたデータが含まれていると仮定すると、次の出力が表示されます-

[(2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com'),
   (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')]

intersection（）

INTERSECT演算子を使用して、SQLは両方のSELECTステートメントからの共通行を表示します。 intersection（）関数はこの振る舞いを実装します。

次の例では、2つのSELECT構造体は、intersect（）関数のパラメーターです。 1つはemail_add列の一部として「gmail.com」を含む行を返し、もう1つはpostal_add列の一部として「Pune」を含む行を返します。結果は、両方の結果セットの共通行になります。

u = intersect(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

実際には、これは次のSQL文と同等です-

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? INTERSECT SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

2つのバインドされたパラメーター「％gmail.com」と「％Pune」は、以下に示すように、アドレステーブルの元のデータから単一の行を生成します-

[(1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')]

Sqlalchemy-core-using-set-operations

SQLAlchemyコア-セット操作の使用

連合（）

union_all（）

except_（）

intersection（）

目次