Tech

SQLAlchemy 複数の列の値の組み合わせを条件としてSELECTする

SQLAlchemyはPythonでデータベースを扱うためのORMライブラリのひとつ。

データベースを扱いやすくしてくれる便利なモジュールが用意されています。

そんなSQLAlchemyのORMを使って、複数の列の値の組み合わせを条件に設定しqueryを行う方法をまとめています。

若干特殊な使い方ですが、データ分析をするのにあたり応用力のある知識なので共有しておきます。

実行環境
Python 3.9
Pandas 1.2.3
SQLAlchemy 1.4.5(今回はORM機能を使います)
PyMySQL 1.0.2

 

複数列の値の組み合わせを条件とする

複数の列の値を指定したtuple(タプル)またはdict(辞書)があって、それをフィルターの条件にするにはどうしたらいいでしょうか?

結論からいえば、各列の条件をand条件のリストとし、それをor条件にセットすればできます。

サンプルデータとして以下のようなテーブルデータがあるとき、

id, name, affiliation, job_title, expertise
'1', 'Vivian Rose', 'Operations', 'Insurance Broker', 'Communication'
'2', 'Rosalee Ashley', 'Human Resources', 'CNC Operator', 'Teamwork'
'3', 'Matt Leslie', 'Operations', 'Ambulatory Nurse', 'Leadership'
'4', 'Faith Pearce', 'Management', 'Budget Analyst', 'Conflict Resolution'
'5', 'Cedrick Dyson', 'Accounting', 'Investment Advisor', 'Work Under Pressure'
'6', 'Aeris Franks', 'Management', 'Machine Operator', 'Learning'

※データは適当にこちらのサイトで作りました。
https://www.onlinedatagenerator.com/

job_titleが 'Accountant' かつ expertiseが 'Communication'
job_titleが ' Budget Analyst’ かつ expertiseが 'Work Under Pressure'

の行のみを抽出したいとします。
(それぞれの列が異なる値の組み合わせであることに注目)

このとき、ORMのqueryを下のように書くと希望のデータが取得できます。

or_とand_はimportが必要なので注意しましょう。

from sqlalchemy import and_, or_

criteria = [
    {
        'job_title': 'Accountant',
        'expertise': 'Communication'
    },
    {
        'job_title': 'Budget Analyst',
        'expertise': 'Work Under Pressure'
    },
]

conditions = [and_(
    Users.job_title == c['job_title'],
    Users.expertise == c['expertise'],
) for c in criteria]

result = session.query(Users).filter(or_(*conditions)).all()

※Modelは別途定義しています。下に全ソースを載せているのでご確認ください。

ポイントは以下の合わせ技です。

  • 各値の組み合わせ条件をand_()条件とし、それらをlistとする(ここではリスト内包表記でlistを生成しています)
  • そのlistをor_()条件として指定する

これはANDとORを組み合わせるSELECT文の作り方と同じなので、直感的にもわかりやすいですよね。

抽出される行は以下のようになり、
Accountant×Communication と Budge Analyst×Work Under Pressureに限定されているのが分かります。

-----
name: Fred Shelton
job_title: Budget Analyst
expertise: Work Under Pressure
-----
name: Marla Terry
job_title: Accountant
expertise: Communication
-----
name: Rylee Bailey
job_title: Budget Analyst
expertise: Work Under Pressure
-----
name: Marie Umney
job_title: Accountant
expertise: Communication
-----
name: Doris Donovan
job_title: Accountant
expertise: Communication

....

サンプル例では2つのセット(2列の組み合わせを2組)を用いましたが、同じ方法で複数列の組み合わせ条件を複数個つくり、それをfilterに指定することができます。

特にand_()、or_()メソッドは汎用性が高いので、覚えておいて損はないでしょう。

 

(サンプル)全コード

import pandas as pd

from sqlalchemy import Column, BigInteger
from sqlalchemy import create_engine
from sqlalchemy import and_, or_
from sqlalchemy.dialects import mysql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker

Base = declarative_base()
class Users(Base):
    __tablename__ = 'users'
    id = Column(BigInteger, primary_key=True, autoincrement=True, nullable=False)
    name = Column(mysql.VARCHAR(100, collation='utf8_general_ci'), nullable=False)
    affiliation = Column(mysql.VARCHAR(100, collation='utf8_general_ci'), nullable=False)
    job_title = Column(mysql.VARCHAR(100, collation='utf8_general_ci'), nullable=False)
    expertise = Column(mysql.VARCHAR(100, collation='utf8_general_ci'), nullable=False)

def get_engine():
    db = 'mysql+pymysql://username:passw0rd@hostname/dbname?charset=utf8mb4'
    return create_engine(
        db,
        encoding='utf-8',
        echo=False,
        pool_pre_ping=True,
        pool_recycle=300,
    )

def main():
    s_maker = sessionmaker(
        bind=get_engine(),
        autocommit=False,
        autoflush=False,
    )
    session = s_maker()

    criteria = [
        {
            'job_title': 'Accountant',
            'expertise': 'Communication'
        },
        {
            'job_title': 'Budget Analyst',
            'expertise': 'Work Under Pressure'
        },
    ]

    conditions = [and_(
        Users.job_title == c['job_title'],
        Users.expertise == c['expertise'],
    ) for c in criteria]

    result = session.query(Users).filter(or_(*conditions)).all()

    for row in result:
        print("-----")
        print(f"name: {row.name}")
        print(f"job_title: {row.job_title}")
        print(f"expertise: {row.expertise}")

if __name__ == '__main__':
    main()

 

まとめ

SQLAlchemyのORM query機能で複数列の値の組み合わせを条件として指定し、行を取得する方法をまとめました。

  • この記事を書いた人

nextpenguin

システム開発・プログラミングのしごとやっています。甘味とコーヒーは生命線。日常での学びを記事にしています。

人気の記事

1

先日の記事では、初心者からフリーランスプログラマーになる難しさと、それでもなんとか稼げるようになるにはどうしたらいいか解説しました。 今回の記事では、稼げるフリーランサーになるために必要な要素を、もう ...

2

気休めだけの甘い言葉は書きません。 最近は多くの企業やサイトで、使い捨てられるプログラマーが欲しいがために、甘い言葉で初心者プログラマを誘い出しています。 この記事では、まずは「現実」をちゃんと理解し ...

3

Vuetifyの v-progress-circular コンポーネントは、数値データや処理状況を環状(円状)のデザインで教えてくれるUIデザインです。 ローディングのスピナー(処理中のマーク)として ...

4

※画像はずとまよの新曲とは一切関係ありません   ずっと真夜中でいいのに。(以下、ずとまよ)の新曲『勘ぐれい』のMVが12月1日に公開されましたね。 MV前に公開されていた原曲を聴き、神曲な ...

5

Vuexのstore(ストア)を使うと、各コンポーネント間で個別にデータのやり取りすることなく、データを一元的に管理できます。Vueでは欠かせない機能といえるでしょう。 state(変数、コンポーネン ...

-Tech

© 2021 スターレイヴ