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
....
サンプル例では２つのセット（２列の組み合わせを２組）を用いましたが、同じ方法で複数列の組み合わせ条件を複数個つくり、それを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のスキルを使って毎月の収入源を増やすのが最適なんじゃないかという件
実際、今のスキルに合わせて仕事を獲得し、スキマ時間で毎月+５、+20、+70万円くらいの人が多いです。
必要なスキル、仕事の獲得までの流れは以下の記事で徹底解説しています。
（もちろん全部無料です）
空いた時間にぜひ参考にしてみてください。
まとめ
SQLAlchemyのORM query機能で複数列の値の組み合わせを条件として指定し、行を取得する方法をまとめました。
