Python SQLAlchemy 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()

 

Pythonのスキルを使って毎月の収入源を増やすのが最適なんじゃないかという件

実際、今のスキルに合わせて仕事を獲得し、スキマ時間で毎月+5、+20、+70万円くらいの人が多いです。

必要なスキル、仕事の獲得までの流れは以下の記事で徹底解説しています。
もちろん全部無料です
空いた時間にぜひ参考にしてみてください。

 

まとめ

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

  • この記事を書いた人

次世代ペンギン

長いのでペンギンとお呼びください。システム開発・プログラミングのお仕事をしています。甘味とコーヒーは生命線。多くの人に役立つ情報のシェアが目標です。

人気の記事

1

会社員でプログラマーとして働いている人、インフラやネットワークのエンジニアとして働いている人の中には、フリーランスのプログラマーとして独立、もしくは転向したい人もいるので ...

2

キャリアアップのため、または高収入を目指して、しっかりプログラミングを学びたいという人が増えてきましたね。 この記事では現役のエンジニアである私が、実際に仕事で稼げるよう ...

3

フリーランスのプログラマーにとって収入の向上に最も直結するのはスキルです。 必要なスキル、スキルの獲得方法が気になる人も多いでしょう。 また、これからフリーランスを目指す ...

4

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

5

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

-Python, SQLAlchemy, Tech
-,

© 2022 ペンギンのーと