db.pyでデータベース探索

データベースにテーブルやカラムがたくさんあったり、そのデータベースをはじめて扱う場合、欲しいデータがどこにあるのか探すのになかなか手間がかかることがあります。

そんな場合に役に立ちそうな```db.py``` と言うツールを見つけたので試してみました。

* https://github.com/yhat/db.py

直接SQLで探索する方法は以下の記事を参照。

* [SQLでデータベース探索](http://qiita.com/atsaki/items/5d6960b0d99793fff472)

## db.pyでできること

```db.py``` を使うと以下のようなことが簡単にできます。

* テーブル名やカラム名の検索
* クエリの実行
* データのサンプリング
* よく使うデータベースの接続情報の保存
* pandasの機能を使用したクエリ結果の処理

データベースに格納されているデータの全体像を把握したり、欲しい情報がどこにあるのか探すために使うと便利です。

## インストール

### db.pyのインストール

db.pyは```Python``` で書かれており```pip``` でインストール可能です。

```bash
$ pip install db.py
```

### ドライバのインストール

データベースに接続するために必要なドライバをインストールします。

各データベースで使用可能なドライバはdb.pyのREADMEを参照してください。
各ドライバのインストール方法は各ドライバのドキュメントを参照してください。

MySQLのドライバは```MySQLdb``` のみREADMEに書かれていますが、```pymysql``` を使用することもできます。両方使用可能な場合には```MySQLdb``` が使用されます。

今回は```SQLite``` を使用するため、ドライバのインストールは必要ありません。（あらかじめインストールされています。）

### bpython, ipythonのインストール

db.pyはインタプリタから使用します。通常のインタプリタでも使えますが、```bpython``` や```IPython``` を使うと補完などの機能が使えるので便利です。どちらも```pip``` でインストール可能です。

個人的には自動でヘルプや補完候補を出してくれる```bpython``` がオススメです。

```bash
$ pip install bpython
$ pip install ipython
```

## データベースに接続する

インタープリタを起動し、```DB```クラス をインポートします。

接続に必要な情報を与えてやると、データベースに接続することができます。

```pycon
>>> from db import DB
>>> db = DB(filename="chinook.sql", dbtype="sqlite")
```

```chinook.sql``` は```db.py``` がサンプル用に用意しているデータベースです。
READMEに書いてあるのと同様に```DemoDB```を使って読み込むこともできます。

```pycon
>>> from db import DemoDB
>>> db = DemoDB()
```

## テーブルの一覧を表示する

```db.tables``` の中にテーブルが格納されています。
どのようなカラムがあるのか確認することもできます。

```pycon
>>> db.tables
+---------------+----------------------------------------------------------------------------------+
| Table         | Columns                                                                          |
+---------------+----------------------------------------------------------------------------------+
| Album         | AlbumId, Title, ArtistId                                                         |
| Artist        | ArtistId, Name                                                                   |
| Customer      | CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalC |
|               | ode, Phone, Fax, Email, SupportRepId                                             |
| Employee      | EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, |
|               |  City, State, Country, PostalCode, Phone, Fax, Email                             |
| Genre         | GenreId, Name                                                                    |
| Invoice       | InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, B |
|               | illingCountry, BillingPostalCode, Total                                          |
| InvoiceLine   | InvoiceLineId, InvoiceId, TrackId, UnitPrice, Quantity                           |
| MediaType     | MediaTypeId, Name                                                                |
| Playlist      | PlaylistId, Name                                                                 |
| PlaylistTrack | PlaylistId, TrackId                                                              |
| Track         | TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, Uni |
|               | tPrice                                                                           |
+---------------+----------------------------------------------------------------------------------+
```

それぞれのテーブルについての情報をみるとカラムの型などの情報を見ることができます。

```pycon
>>> db.tables.Customer
+------------------------------------------------------------------------+
|                                Customer                                |
+--------------+--------------+---------------------+--------------------+
| Column       | Type         | Foreign Keys        | Reference Keys     |
+--------------+--------------+---------------------+--------------------+
| CustomerId   | INTEGER      |                     | Invoice.CustomerId |
| FirstName    | NVARCHAR(40) |                     |                    |
| LastName     | NVARCHAR(20) |                     |                    |
| Company      | NVARCHAR(80) |                     |                    |
| Address      | NVARCHAR(70) |                     |                    |
| City         | NVARCHAR(40) |                     |                    |
| State        | NVARCHAR(40) |                     |                    |
| Country      | NVARCHAR(40) |                     |                    |
| PostalCode   | NVARCHAR(10) |                     |                    |
| Phone        | NVARCHAR(24) |                     |                    |
| Fax          | NVARCHAR(24) |                     |                    |
| Email        | NVARCHAR(60) |                     |                    |
| SupportRepId | INTEGER      | Employee.EmployeeId |                    |
+--------------+--------------+---------------------+--------------------+
```

## テーブルからデータを取り出す

### all, select, unique

```all``` メソッドを使うとテーブル内の全データを取り出すことができます。
```SELECT * FROM table_name``` に相当します。

```len``` と組み合わせて行数を数えるのにも使えます。

```pycon
>>> len(db.tables.Customer.all())
59
```

```select``` を使うカラムを指定することができます。
```SELECT co1, col2, col3 FROM table_name``` に相当します。

```pycon
>>> db.tables.Customer.select("CustomerId", "FirstName", "LastName")[:5]
   CustomerId  FirstName     LastName
0           1       Luís    Gonçalves
1           2     Leonie       Köhler
2           3   François     Tremblay
3           4      Bjørn       Hansen
4           5  František  Wichterlová
```

```unique``` は```select```とほぼ同じですが```SELECT DISTINCT``` します。
```SELECT DISTINCT co1, col2, col3 FROM table_name``` に相当します。

```pycon
>>> len(db.tables.Customer.select("SupportRepId"))
59
>>> len(db.tables.Customer.unique("SupportRepId"))
3
```

上記の様に一列だけ指定するのであれば次のように書くこともできます。

```pycon
>>> len(db.tables.Customer.SupportRepId.unique())
3
```

### head, sample

```head``` で先頭のデータを取り出ししたり、```sample``` でデータをサンプリングしたりすることができます。

```pycon
>>> db.tables.Customer.head(1)
   CustomerId FirstName   LastName  \
0           1      Luís  Gonçalves   

                                            Company  \
0  Embraer - Empresa Brasileira de Aeronáutica S.A.   

                           Address                 City State Country  \
0  Av. Brigadeiro Faria Lima, 2170  São José dos Campos    SP  Brazil   

  PostalCode               Phone                 Fax                 Email  \
0  12227-000  +55 (12) 3923-5555  +55 (12) 3923-5566  luisg@embraer.com.br   

   SupportRepId  
0             3 

>>> db.tables.Customer.sample(5)
   CustomerId FirstName    LastName Company                 Address  \
0          42     Wyatt      Girard    None  9, Place Louis Barthou   
1          59      Puja  Srivastava    None       3,Raj Bhavan Road   
2          51    Joakim   Johansson    None             Celsiusg. 9   
3          33     Ellie    Sullivan    None          5112 48 Street   
4          52      Emma       Jones    None       202 Hoxton Street   

          City State         Country PostalCode               Phone   Fax  \
0     Bordeaux  None          France      33000  +33 05 56 96 96 96  None   
1    Bangalore  None           India     560001    +91 080 22289999  None   
2    Stockholm  None          Sweden      11230    +46 08-651 52 52  None   
3  Yellowknife    NT          Canada    X1A 1N6   +1 (867) 920-2233  None   
4       London  None  United Kingdom     N1 5LH   +44 020 7707 0707  None   

                       Email  SupportRepId  
0      wyatt.girard@yahoo.fr             3  
1   puja_srivastava@yahoo.in             3  
2  joakim.johansson@yahoo.se             5  
3     ellie.sullivan@shaw.ca             3  
4     emma_jones@hotmail.com             3 
```

### pandasの機能を使った操作

取り出したデータは```pandas``` の```DataFrame``` となっているため必要なカラムのみを切り出したり、特定のカラムでソートするなどの操作が可能です。

```pycon
>>> db.tables.Customer.sample(5)[["CustomerId", "FirstName", "LastName", "Email"]].sort("CustomerId")
   CustomerId FirstName   LastName                      Email
1          36    Hannah  Schneider  hannah.schneider@yahoo.de
0          46      Hugh   O'Reilly       hughoreilly@apple.ie
4          51    Joakim  Johansson  joakim.johansson@yahoo.se
2          52      Emma      Jones     emma_jones@hotmail.com
3          54     Steve     Murray      steve.murray@yahoo.uk
```

## テーブル・カラムの検索

```find_tables``` メソッドを使用してテーブル名を検索することができます。
テーブルにlistという文字列を含むテーブルを検索したい場合には次のようにします。

```pycon
>>> db.find_table("*list*")
+---------------+---------------------+
| Table         | Columns             |
+---------------+---------------------+
| Playlist      | PlaylistId, Name    |
| PlaylistTrack | PlaylistId, TrackId |
+---------------+---------------------+
```

カラム名もテーブル名と同様に検索することができます。

```pycon
>>> db.find_column("*Name")
+-----------+-------------+---------------+
| Table     | Column Name | Type          |
+-----------+-------------+---------------+
| Artist    |     Name    | NVARCHAR(120) |
| Customer  |  FirstName  | NVARCHAR(40)  |
| Customer  |   LastName  | NVARCHAR(20)  |
| Employee  |  FirstName  | NVARCHAR(20)  |
| Employee  |   LastName  | NVARCHAR(20)  |
| Genre     |     Name    | NVARCHAR(120) |
| MediaType |     Name    | NVARCHAR(120) |
| Playlist  |     Name    | NVARCHAR(120) |
| Track     |     Name    | NVARCHAR(200) |
+-----------+-------------+---------------+
```

カラムの型を指定することもできます。

```pycon
>>> db.find_column("*Name", data_type="NVARCHAR(20)")
+----------+-------------+--------------+
| Table    | Column Name | Type         |
+----------+-------------+--------------+
| Customer |   LastName  | NVARCHAR(20) |
| Employee |  FirstName  | NVARCHAR(20) |
| Employee |   LastName  | NVARCHAR(20) |
+----------+-------------+--------------+
```

## クエリの実行

```query``` メソッドを使用すると```SQL``` を実行することができます。

```pycon
>>> db.query("SELECT CustomerId, FirstName, LastName, Email FROM Customer LIMIT 5")
   CustomerId  FirstName     LastName                     Email
0           1       Luís    Gonçalves      luisg@embraer.com.br
1           2     Leonie       Köhler     leonekohler@surfeu.de
2           3   François     Tremblay       ftremblay@gmail.com
3           4      Bjørn       Hansen     bjorn.hansen@yahoo.no
4           5  František  Wichterlová  frantisekw@jetbrains.com
```

もちろんJOINもできます。

```pycon
>>> db.query("SELECT c.CustomerId, c.FirstName, e.EmployeeId, e.FirstName FROM Customer c JOIN Employee e ON c.SupportRepId = e.EmployeeId LIMIT 5")
   CustomerId  FirstName  EmployeeId FirstName
0           1       Luís           3      Jane
1           2     Leonie           5     Steve
2           3   François           3      Jane
3           4      Bjørn           4  Margaret
4           5  František           4  Margaret
```

## プロファイルの使用

よく使用するデータベースの接続情報をプロファイルに保存しておくと、プロファイル名を使用して簡単に接続できるようになります。

### 既存のプロファイルの表示

```pycon
>>> from db import list_profiles
>>> list_profiles()
{}
```

### プロファイルの保存

データベースに接続した状態で```save_credentials``` メソッドを使用するとプロファイルを保存できます。名前を省略すると```default``` になります。

```pycon
>>> db.save_profile
>>> db.save_credentials("demodb")
>>> list_profiles()
{'demodb': {u'username': None, u'dbtype': u'sqlite' ......
```

### プロファイルを利用したデータベースへの接続

プロファイル名を使用して簡単に接続できます。

```pycon
>>> from db import DB
>>> db = DB(profile="demodb")
```

### プロファイルの削除

```remove_profile``` でプロファイルを削除できます。

```pycon
>>> from db import remove_profile
>>> from db import remove_profile
>>> remove_profile("demodb")
>>> db = DB(profile="demodb")
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/Users/N1212A001/virtualenv/lib/python2.7/site-packages/db/db.py", line 730, in __init__
    self.load_credentials(profile)
  File "/Users/N1212A001/virtualenv/lib/python2.7/site-packages/db/db.py", line 840, in load_credentials
    raise Exception("Credentials not configured!")
Exception: Credentials not configured!
```

## まとめ

単純な機能を使用するだけでもmysqlコマンドなどで直接データベースに接続するよりかなり手間が減らせそうです。よく使うクエリを実行したり、結果をグラフにしたりする関数を作成しておくとさらに便利になると思います。