【sqlalchemy】サブクエリの使い方

今回はsqlalchemyによるサブクエリの使い方についてです。

こちらのサンプルの対象のデータベースはPostgresqlです。

 

 

 

 

 

 

サンプルコード

サンプルテーブルは以下のようなものを用意

sample_code：キーとなるコード

ymd：日付

high_score：ハイスコア

low_score：ロースコア

 

クエリ仕様

サンプルテーブルは日ごとにスコアが登録されているものとして、

SampleModelのレコードで、ハイスコアとロースコアが0になっているものに

sampleコード単位にハイスコア、ロースコアの平均を出して更新するというイメージです。

 

　　　　　　# サブクエリ
            sub_query = (
                session.query(
                    SampleModel.sample_code,
                    func.avg(SampleModel.high_score).label(
                        "average_high"
                    ),
                    func.avg(SampleModel.low_score).label(
                        "average_low"
                    ),
                )
                .filter(
                    SampleModel.high_score != 0.0,
                    SampleModel.low_score != 0.0,
                )
                .group_by(SampleModel.sample_code)
            ).subquery("sub")

　　　　　　# メインクエリ
            update_stmt = (
                update(SampleModel)
                .values(
                    high_score=sub_query.c.average_high,
                    low_score=sub_query.c.average_low,
                )
                .where(
                    and_(
                        SampleModel.sample_code
                        == sub_query.c.sample_code,
                        SampleModel.high_score == 0.0,
                        SampleModel.low_score == 0.0,
                    )
                )
            )

 

発行されるクエリ

上記のクエリビルダーにて発行されるSQLは以下のようになります。

update SampleModel
set high_score = sub.average_high,
    low_score = sub.average_low
from (
 select avg(SampleModel.high_score) as average_high,
        avg(SampleModel.low_score) as average_low
 from SampleModel
 where high_score != 0
 and   low_scrore != 0
 group by sample_code
) sub
where sample_code = sub.sample_code
and   high_score = 0
and   low_scrore = 0

 

解説

sqlalchemyで複雑なクエリを組む場合は、サブクエリの導入を検討し、

各サブクエリは変数として部分的に作成することが良いと思います。

 

サブクエリにはqueryの最後にsubquery(“エイリアス名”)をつけて上げることで、

クエリビルダーの中でそのサブクエリの変数が使用できるようになります。

 

 

また、サブクエリの変数を介して、selectした項目を使用する場合には、

変数.c.項目名（.labelによりエイリアスを設定知る場合にはその名前）と

することで使用できます。

 

 

 

 

にほんブログ村

人気ブログランキング