SQLを使ってデータベースを扱う方法を学びます

はじめる前に

SQLを使ってデータベースを操作する方法は沢山あります。このセクションでは、Pythonが標準で扱えるSQLiteという形式のデータベースを扱います。他にもOracleやMySQLなど色々なデータベースがありますが、SQLを通じて扱うことが出来るという点では共通しているので、ここではSQLの基本を紹介します。

基本

SQLは、Structured Query Languageの略で、データベースを操作することに特化したプログラミング言語の一種です。データベース自体にも様々な種類があります。古くから広く使われているものの1つに、表（テーブル）にデータを保存し、その表の間の関係性を定義する、RDB（Relational DataBase）があります。SQLは、この表型のデータベースを操作するための言語です。

RDBを扱うためのソフトウェアにも沢山の種類があります。RDBMS（RDB Management System)と呼ばれるこれらのソフトには、OracleやMySQL、またPostgreSQLやMicrosoftのSQLServerなどが有名ですが、SQLiteというRDBMSがPythonには標準で付属しています。

Step 1: SQL Alchemyのセットアップ

データベースとPandasを接続するために、SQLAlchemyが必要です。詳しい情報はこちら すでに、import sqlalchemyとして、使えるようになっているか確認してみてください。エラーが起こる場合は、以下のいずれかの方法でインストールします。

（コマンドラインで）pip install sqlalchemy

Anacondaを使っている場合は、すでにセットアップされている可能性もありますが、コマンドラインから、次のようにします。

conda install sqlalchemy

Step 2: SQLite Broswerのセットアップ（必須ではありません）

SQLite形式のデータを閲覧・操作するのに便利です。

お使いのOSに応じたバージョンを用意します。 http://sqlitebrowser.org/

Step 3: sakilaデータベースの取得

sakilaデータベースは、レンタルビデオの情報をまとめたサンプルデータベースです。 http://dev.mysql.com/doc/sakila/en/

SQLite形式のデータを次のリンクから取得できます。ダウンロードはこちら

本家サイトから、SQLを取得して、自分で構築することも可能です http://dev.mysql.com/doc/index-other.html 少し難易度が高いので、チャレンジしてみたい方だけおすすめします。

データベースは、ipythonを起動した場所と同じディレクトリに保存してください。

In [3]:
# sakilaデータベースのテープルと、テーブル同士の関係を示したER（Entitiy-Relationship）図です。
# SQLを試しながら説明するので、いまは全部分からなくても大丈夫です。

from IPython.display import Image
Image('sakilaerm.png')
Out[3]:

Pythonには、SQLite3が付属しています。つまり、追加の作業無しに、SQLを使ったデータベースの操作ができるということです。pandasもデータベースからデータを読み込むことが出来ます。ただ、そのためには、SQL Alchemyがセットアップされている必要があります。

In [7]:
# インポートしてみましょう。
import sqlite3
import pandas as pd

まず、データベースと接続するために、Connectionオブジェクトを作る必要があります。 やってみましょう。

In [8]:
# sakila.dbのファイルが、このフォルダにあることを確認してください。
con = sqlite3.connect("sakila.db")

最も基本的なSQL文を実行してみます。ひとまず、接続を確認するための操作です。SQLに関する詳しい話は、次のレクチャーで触れます。

In [9]:
# SQL文を文字列として書きます。複数行になっても良いように、引用符を3つ書いておきましょう。
sql_query = ''' SELECT * FROM customer '''

# Pandasを利用します。
df = pd.read_sql(sql_query, con)

df
Out[9]:
customer_id store_id first_name last_name email address_id active create_date last_update
0 1 1 MARY SMITH MARY.SMITH@sakilacustomer.org 5 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
1 2 1 PATRICIA JOHNSON PATRICIA.JOHNSON@sakilacustomer.org 6 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
2 3 1 LINDA WILLIAMS LINDA.WILLIAMS@sakilacustomer.org 7 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
3 4 2 BARBARA JONES BARBARA.JONES@sakilacustomer.org 8 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
4 5 1 ELIZABETH BROWN ELIZABETH.BROWN@sakilacustomer.org 9 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
5 6 2 JENNIFER DAVIS JENNIFER.DAVIS@sakilacustomer.org 10 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
6 7 1 MARIA MILLER MARIA.MILLER@sakilacustomer.org 11 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
7 8 2 SUSAN WILSON SUSAN.WILSON@sakilacustomer.org 12 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
8 9 2 MARGARET MOORE MARGARET.MOORE@sakilacustomer.org 13 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
9 10 1 DOROTHY TAYLOR DOROTHY.TAYLOR@sakilacustomer.org 14 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
10 11 2 LISA ANDERSON LISA.ANDERSON@sakilacustomer.org 15 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
11 12 1 NANCY THOMAS NANCY.THOMAS@sakilacustomer.org 16 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
12 13 2 KAREN JACKSON KAREN.JACKSON@sakilacustomer.org 17 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
13 14 2 BETTY WHITE BETTY.WHITE@sakilacustomer.org 18 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
14 15 1 HELEN HARRIS HELEN.HARRIS@sakilacustomer.org 19 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
15 16 2 SANDRA MARTIN SANDRA.MARTIN@sakilacustomer.org 20 0 2006-02-14 22:04:36.000 2011-09-14 18:10:28
16 17 1 DONNA THOMPSON DONNA.THOMPSON@sakilacustomer.org 21 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
17 18 2 CAROL GARCIA CAROL.GARCIA@sakilacustomer.org 22 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
18 19 1 RUTH MARTINEZ RUTH.MARTINEZ@sakilacustomer.org 23 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
19 20 2 SHARON ROBINSON SHARON.ROBINSON@sakilacustomer.org 24 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
20 21 1 MICHELLE CLARK MICHELLE.CLARK@sakilacustomer.org 25 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
21 22 1 LAURA RODRIGUEZ LAURA.RODRIGUEZ@sakilacustomer.org 26 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
22 23 2 SARAH LEWIS SARAH.LEWIS@sakilacustomer.org 27 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
23 24 2 KIMBERLY LEE KIMBERLY.LEE@sakilacustomer.org 28 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
24 25 1 DEBORAH WALKER DEBORAH.WALKER@sakilacustomer.org 29 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
25 26 2 JESSICA HALL JESSICA.HALL@sakilacustomer.org 30 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
26 27 2 SHIRLEY ALLEN SHIRLEY.ALLEN@sakilacustomer.org 31 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
27 28 1 CYNTHIA YOUNG CYNTHIA.YOUNG@sakilacustomer.org 32 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
28 29 2 ANGELA HERNANDEZ ANGELA.HERNANDEZ@sakilacustomer.org 33 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
29 30 1 MELISSA KING MELISSA.KING@sakilacustomer.org 34 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
... ... ... ... ... ... ... ... ... ...
569 570 2 IVAN CROMWELL IVAN.CROMWELL@sakilacustomer.org 576 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
570 571 2 JOHNNIE CHISHOLM JOHNNIE.CHISHOLM@sakilacustomer.org 577 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
571 572 1 SIDNEY BURLESON SIDNEY.BURLESON@sakilacustomer.org 578 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
572 573 1 BYRON BOX BYRON.BOX@sakilacustomer.org 579 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
573 574 2 JULIAN VEST JULIAN.VEST@sakilacustomer.org 580 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
574 575 2 ISAAC OGLESBY ISAAC.OGLESBY@sakilacustomer.org 581 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
575 576 2 MORRIS MCCARTER MORRIS.MCCARTER@sakilacustomer.org 582 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
576 577 2 CLIFTON MALCOLM CLIFTON.MALCOLM@sakilacustomer.org 583 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
577 578 2 WILLARD LUMPKIN WILLARD.LUMPKIN@sakilacustomer.org 584 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
578 579 2 DARYL LARUE DARYL.LARUE@sakilacustomer.org 585 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
579 580 1 ROSS GREY ROSS.GREY@sakilacustomer.org 586 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
580 581 1 VIRGIL WOFFORD VIRGIL.WOFFORD@sakilacustomer.org 587 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
581 582 2 ANDY VANHORN ANDY.VANHORN@sakilacustomer.org 588 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
582 583 1 MARSHALL THORN MARSHALL.THORN@sakilacustomer.org 589 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
583 584 2 SALVADOR TEEL SALVADOR.TEEL@sakilacustomer.org 590 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
584 585 1 PERRY SWAFFORD PERRY.SWAFFORD@sakilacustomer.org 591 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
585 586 1 KIRK STCLAIR KIRK.STCLAIR@sakilacustomer.org 592 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
586 587 1 SERGIO STANFIELD SERGIO.STANFIELD@sakilacustomer.org 593 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
587 588 1 MARION OCAMPO MARION.OCAMPO@sakilacustomer.org 594 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
588 589 1 TRACY HERRMANN TRACY.HERRMANN@sakilacustomer.org 595 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
589 590 2 SETH HANNON SETH.HANNON@sakilacustomer.org 596 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
590 591 1 KENT ARSENAULT KENT.ARSENAULT@sakilacustomer.org 597 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
591 592 1 TERRANCE ROUSH TERRANCE.ROUSH@sakilacustomer.org 598 0 2006-02-14 22:04:37.000 2011-09-14 18:10:44
592 593 2 RENE MCALISTER RENE.MCALISTER@sakilacustomer.org 599 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
593 594 1 EDUARDO HIATT EDUARDO.HIATT@sakilacustomer.org 600 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
594 595 1 TERRENCE GUNDERSON TERRENCE.GUNDERSON@sakilacustomer.org 601 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
595 596 1 ENRIQUE FORSYTHE ENRIQUE.FORSYTHE@sakilacustomer.org 602 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
596 597 1 FREDDIE DUGGAN FREDDIE.DUGGAN@sakilacustomer.org 603 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
597 598 1 WADE DELVALLE WADE.DELVALLE@sakilacustomer.org 604 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44
598 599 2 AUSTIN CINTRON AUSTIN.CINTRON@sakilacustomer.org 605 1 2006-02-14 22:04:37.000 2011-09-14 18:10:44

599 rows × 9 columns

エラー無く実行できれば、設定は完了しています。 次のレクチャーで、詳しいSQLの使い方を紹介します。