読者です 読者をやめる 読者になる 読者になる

uokadaの見逃し三振は嫌いです

ここで述べられていることは私の個人的な意見に基づくものであり、私が所属する組織には一切の関係はありません。

SQLAlchemyでINNER JOINする方法

python MySQL

あんまり情報が無くてハマったのでメモっとく。

今回はMySQLが用意しているサンプルテーブルを使ってINNER JOINの例を示してみる。

MySQL :: MySQL Documentation: Other MySQL Documentation 上のページのmenagerie databaseの右のリンクからサンプルデータを取得してロードします。

MySQLへのロード方法は割愛。 サンプルデータの中身は2つのテーブルがあり、それぞれレコードが10件,9件入っている状態です。

mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+
2 rows in set (0.01 sec)

mysql> select * FROM event;
+----------+------------+----------+-----------------------------+
| name     | date       | type     | remark                      |
+----------+------------+----------+-----------------------------+
| Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male |
| Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male |
| Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         |
| Chirpy   | 1999-03-21 | vet      | needed beak straightened    |
| Slim     | 1997-08-03 | vet      | broken rib                  |
| Bowser   | 1991-10-12 | kennel   | NULL                        |
| Fang     | 1991-10-12 | kennel   | NULL                        |
| Fang     | 1998-08-28 | birthday | Gave him a new chew toy     |
| Claws    | 1998-03-17 | birthday | Gave him a new flea collar  |
| Whistler | 1998-12-09 | birthday | First birthday              |
+----------+------------+----------+-----------------------------+
10 rows in set (0.00 sec)

mysql> select * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

ここからSQLAlchemyの話。 こんな結合をしたいと思ったことはよくあるよね。 (ちなみに、'litter'は動物の出産という意味)

mysql> SELECT event.name, event.date, event.type, event.remark, pet.species  
        FROM event INNER JOIN pet ON event.name = pet.name
        WHERE event.type ='litter';
+--------+------------+--------+-----------------------------+---------+
| name   | date       | type   | remark                      | species |
+--------+------------+--------+-----------------------------+---------+
| Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male | cat     |
| Buffy  | 1993-06-23 | litter | 5 puppies, 2 female, 3 male | dog     |
| Buffy  | 1994-06-19 | litter | 3 puppies, 3 female         | dog     |
+--------+------------+--------+-----------------------------+---------+
3 rows in set (0.00 sec)

これをSQLAlchemyでどう表すかというとこうなる。

   # 各tableのインスタンスを作成
   events = Table('event', metadata, autoload=True)
   pets = Table('pet', metadata, autoload=True)

   #: 取得したいカラムのリストを作成
   columns = [events, pets.c.species]

    #: join()で結合させたいテーブルと条件を指定しselect()を呼び出し
    q = join(events, pets, events.c.name == pets.c.name) \
        .select() \
        .where(events.c.type == 'litter')
    #: 最後にwith_only_columns()に取得したいカラムのリストを渡す    
    q =  q.with_only_columns(columns)

with_only_columns()は取得するフィールドを絞るために使う。

次にGROUP BYを使ってcount()を使うクエリ例として下のようなクエリを作ってみる。

mysql> SELECT event.name, count(event.name) AS litter_count 
       FROM event INNER JOIN pet ON event.name = pet.name 
       WHERE event.type = 'litter' GROUP BY event.name;

+--------+--------------+
| name   | litter_count |
+--------+--------------+
| Buffy  |            2 |
| Fluffy |            1 |
+--------+--------------+
2 rows in set (0.00 sec)
    #: func.count()で集約するカラムを指定
    #: AS litter_count をlabel('litter_count')で指定
    columns = [events.c.name, func.count(events.c.name).label('litter_count')]

    q = join(events, pets, events.c.name == pets.c.name) \
        .select() \
        .where(events.c.type == 'litter') \
        .group_by(events.c.name)  # <= さっきのクエリに1行追加
    q =  q.with_only_columns(columns)

もっと短い書き方をすると次のコードになります。

    q =  select(events.c.name == pets.c.name) \
        .where(events.c.type == 'litter') \
        .group_by(events.c.name)
    q =  q.with_only_columns(columns)

join()がなくなってselct()の中に結合条件が移動しています。

SQLAlchemyならたったこれだけのコードでINNER JOINやGROUP BYとCOUNT()を使ったクエリが書けます。

ここにたどり着くまでにいろいろドキュメント読んでやっとこのクエリが出来ました。
SQLAlchemyは豊富すぎるほど機能が盛りだくさんなので本格的に使う前に一度ドキュメントをちゃんと読んだほうがいいと思います。

公式ドキュメントの他にはStackOverFlowとか英語のサイト結構読んで勉強してました。
pythonやるなら英語出来ないと辛い。Rubyならrailsのおかげで日本語情報結構あるんだけどpythonは日本で流行ってないからノウハウが少ないと感じる。

SQLAlchemyは概要が一回わかるまで結構な時間かかると思いますが理解できたらかなり強力なライブラリということを実感出来ました。 自分もまだまだ勉強中なので上に示した書き方が必ずしもベストだと思ってないですしもっと可読性が高いベターな書き方があるはずです。

SQLAlchemyはORMとして使うドキュメントは多数見かけましたが自分としてはSELECT文のクエリビルダ−としてSQLAlchemyを使うのが好きです。

プロジェクトで自作のクエリビルダ−のライブラリ作ってアレが出来ないとか作りこむよりも 全員でSQLAlchemyを勉強して使うほうが何倍も楽だと思いました。

Essential Sqlalchemy

Essential Sqlalchemy

新しい本が出るみたいだけど、kindle版で出るのかな?というか出て欲しい。

SQLAlchemy: Database Access Using Python (Developer's Library)

SQLAlchemy: Database Access Using Python (Developer's Library)

  • 作者: Mark Ramm,Michael Bayer
  • 出版社/メーカー: Addison-Wesley Professional
  • 発売日: 2013/02/08
  • メディア: ペーパーバック
  • クリック: 3回
  • この商品を含むブログを見る

#!/usr/bin/env python2.7
# -*- coding:utf-8 -*-

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.sql import select, join, func

from pprint import pprint

config = {
    'user': 'user',
    'passwd':'password',
    'host': 'localhost',
    'port': 3306,
    'database': 'menagerie',
}
dsn_fmt = 'mysql+pymysql://%(user)s:%(passwd)s@%(host)s:%(port)d/%(database)s'
dsn = dsn_fmt % config

engine = create_engine(dsn, echo=True)

metadata = MetaData(bind=engine)

events = Table('event', metadata, autoload=True)
pets = Table('pet', metadata, autoload=True)

if __name__ == '__main__':
    #: part1
    columns = [events, pets.c.species]
    q = join(events, pets, events.c.name == pets.c.name) \
        .select().where(events.c.type == 'litter')
    q =  q.with_only_columns(columns)
    print q
    for row in q.execute():
        pprint(dict(row))

    #: part2
    columns = [events.c.name, func.count(events.c.name).label('litter_count')]
    q = join(events, pets, events.c.name == pets.c.name) \
        .select() \
        .where(events.c.type == 'litter') \
        .group_by(events.c.name)
    q =  q.with_only_columns(columns)
    print q
    for row in q.execute():
        pprint(dict(row))

    #: part2 another version
    q =  select(events.c.name == pets.c.name) \
        .where(events.c.type == 'litter') \
        .group_by(events.c.name)
    q =  q.with_only_columns(columns)
    print q
    for row in q.execute():
        pprint(dict(row))