Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
22
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

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

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

そんな場合に役に立ちそうな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はインタプリタから使用します。通常のインタプリタでも使えますが、bpythonIPython を使うと補完などの機能が使えるので便利です。どちらもpip でインストール可能です。

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

$ pip install bpython
$ pip install ipython

データベースに接続する

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

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

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

chinook.sqldb.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á

uniqueselectとほぼ同じですが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の機能を使った操作

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

>>> 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コマンドなどで直接データベースに接続するよりかなり手間が減らせそうです。よく使うクエリを実行したり、結果をグラフにしたりする関数を作成しておくとさらに便利になると思います。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
22
Help us understand the problem. What are the problem?