あんまり情報が無くてハマったのでメモっとく。
今回は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は豊富すぎるほど機能が盛りだくさんなので本格的に使う前に一度ドキュメントをちゃんと読んだほうがいいと思います。
日本語情報そんなに多くないのでStack Overflowとか英語のサイト結構読んで勉強してました。
Pythonやるなら英語出来ないと辛い。RubyならRailsのおかげで日本語情報結構あるんだけどPythonは日本で流行ってないからノウハウが少ないと感じる。
一回わかるまで結構な時間かかると思いますが理解できたらかなり強力なライブラリということを実感出来ました。
自分もまだまだ勉強中なので上に示した書き方が必ずしもベストだと思ってないですしもっと可読性が高いベターな書き方があるはずです。
SQLAlchemyはORMとして使うドキュメントは多数見かけましたが自分としてはSELECT文のクエリビルダ−としてSQLAlchemyを使うのが好きです。
プロジェクトで自作のクエリビルダ−のライブラリ作ってアレが出来ないとか作りこむよりも
全員でSQLAlchemyを勉強して使うほうが何倍も楽だと思いました。
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))