SQLAlchemyでINNER JOINする方法 


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

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


[MySQL :: MySQL Documentation: Other MySQL Documentation](http://dev.mysql.com/doc/index-other.html "MySQL :: MySQL Documentation: Other MySQL Documentation")
上のページのmenagerie databaseの右のリンクからサンプルデータを取得してロードします。

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

```mysql
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'は動物の出産という意味)


```pattern1
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でどう表すかというとこうなる。

```python
   # 各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)
```

```python
    #: 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)
```

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

```python
    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は豊富すぎるほど機能が盛りだくさんなので本格的に使う前に一度ドキュメントをちゃんと読んだほうがいいと思います。
日本語情報そんなに多くないのでStack Overflowとか英語のサイト結構読んで勉強してました。
Pythonやるなら英語出来ないと辛い。RubyならRailsのおかげで日本語情報結構あるんだけどPythonは日本で流行ってないからノウハウが少ないと感じる。

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

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

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



```python:sample.py
#!/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))
```