データベースにテーブルやカラムがたくさんあったり、そのデータベースをはじめて扱う場合、欲しいデータがどこにあるのか探すのになかなか手間がかかることがあります。
そんな場合に役に立ちそうなdb.py と言うツールを見つけたので試してみました。
直接SQLで探索する方法は以下の記事を参照。
db.pyでできること
db.py を使うと以下のようなことが簡単にできます。
- テーブル名やカラム名の検索
- クエリの実行
- データのサンプリング
- よく使うデータベースの接続情報の保存
- pandasの機能を使用したクエリ結果の処理
データベースに格納されているデータの全体像を把握したり、欲しい情報がどこにあるのか探すために使うと便利です。
インストール
db.pyのインストール
db.pyはPython で書かれておりpip でインストール可能です。
$ pip install db.py
ドライバのインストール
データベースに接続するために必要なドライバをインストールします。
各データベースで使用可能なドライバはdb.pyのREADMEを参照してください。
各ドライバのインストール方法は各ドライバのドキュメントを参照してください。
MySQLのドライバはMySQLdb のみREADMEに書かれていますが、pymysql を使用することもできます。両方使用可能な場合にはMySQLdb が使用されます。
今回はSQLite を使用するため、ドライバのインストールは必要ありません。（あらかじめインストールされています。）
bpython, ipythonのインストール
db.pyはインタプリタから使用します。通常のインタプリタでも使えますが、bpython やIPython を使うと補完などの機能が使えるので便利です。どちらもpip でインストール可能です。
個人的には自動でヘルプや補完候補を出してくれるbpython がオススメです。
$ pip install bpython
$ pip install ipython
データベースに接続する
インタープリタを起動し、DBクラス をインポートします。
接続に必要な情報を与えてやると、データベースに接続することができます。
>>> from db import DB
>>> db = DB(filename="chinook.sql", dbtype="sqlite")
chinook.sql はdb.py がサンプル用に用意しているデータベースです。
READMEに書いてあるのと同様にDemoDBを使って読み込むこともできます。
>>> from db import DemoDB
>>> db = DemoDB()
テーブルの一覧を表示する
db.tables の中にテーブルが格納されています。
どのようなカラムがあるのか確認することもできます。
>>> 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 |
+---------------+----------------------------------------------------------------------------------+
それぞれのテーブルについての情報をみるとカラムの型などの情報を見ることができます。
>>> 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 と組み合わせて行数を数えるのにも使えます。
>>> len(db.tables.Customer.all())
59
select を使うカラムを指定することができます。
SELECT co1, col2, col3 FROM table_name に相当します。
>>> 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 に相当します。
>>> len(db.tables.Customer.select("SupportRepId"))
59
>>> len(db.tables.Customer.unique("SupportRepId"))
3
上記の様に一列だけ指定するのであれば次のように書くこともできます。
>>> len(db.tables.Customer.SupportRepId.unique())
3
head, sample
head で先頭のデータを取り出ししたり、sample でデータをサンプリングしたりすることができます。
>>> 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 となっているため必要なカラムのみを切り出したり、特定のカラムでソートするなどの操作が可能です。
>>> 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という文字列を含むテーブルを検索したい場合には次のようにします。
>>> db.find_table("*list*")
+---------------+---------------------+
| Table | Columns |
+---------------+---------------------+
| Playlist | PlaylistId, Name |
| PlaylistTrack | PlaylistId, TrackId |
+---------------+---------------------+
カラム名もテーブル名と同様に検索することができます。
>>> 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) |
+-----------+-------------+---------------+
カラムの型を指定することもできます。
>>> 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 を実行することができます。
>>> 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もできます。
>>> 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
プロファイルの使用
よく使用するデータベースの接続情報をプロファイルに保存しておくと、プロファイル名を使用して簡単に接続できるようになります。
既存のプロファイルの表示
>>> from db import list_profiles
>>> list_profiles()
{}
プロファイルの保存
データベースに接続した状態でsave_credentials メソッドを使用するとプロファイルを保存できます。名前を省略するとdefault になります。
>>> db.save_profile
>>> db.save_credentials("demodb")
>>> list_profiles()
{'demodb': {u'username': None, u'dbtype': u'sqlite' ......
プロファイルを利用したデータベースへの接続
プロファイル名を使用して簡単に接続できます。
>>> from db import DB
>>> db = DB(profile="demodb")
プロファイルの削除
remove_profile でプロファイルを削除できます。
>>> 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コマンドなどで直接データベースに接続するよりかなり手間が減らせそうです。よく使うクエリを実行したり、結果をグラフにしたりする関数を作成しておくとさらに便利になると思います。