SQLAlchemyCore-セット操作の使用

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

UNIONやINTERSECTなどのセット操作は、標準SQLとその方言のほとんどでサポートされています。SQLAlchemyは、次の関数を使用してそれらを実装します-

連合（）

UNIONは、2つ以上のSELECTステートメントの結果を結合するときに、結果セットから重複を排除します。列の数とデータ型は、両方のテーブルで同じである必要があります。

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', '[email protected]'),
   (2, 1, 'ChurchGate Mumbai', '[email protected]'),
   (3, 3, 'Jubilee Hills Hyderabad', '[email protected]'),
   (4, 5, 'MG Road Bangaluru', '[email protected]')
]

union_all（）

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

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 ?

_（）を除く

SQL EXCEPT句/演算子は、2つのSELECTステートメントを組み合わせて、最初のSELECTステートメントから2番目のSELECTステートメントでは返されない行を返すために使用されます。exception_（）関数は、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', '[email protected]'),
   (3, 3, 'Jubilee Hills Hyderabad', '[email protected]')]

交差（）

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

次の例では、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', '[email protected]')]