sqlite3 と python で csv ファイルを分析する

sqlite はお手軽に SQL を利用するための仕組みです。データベースが一つの UNIX ファイルとして管理されており、データベースの作成・削除が簡便に行うことができること、また、サーバクライアントアーキテクチャではなく、 OS の提供するファイルシステムのロック機構を活用して ACID 特性を実現しているという特徴があります (図 4)^*6 。データベースを利用する場合においては、データベースをサーバクライアントモデルで利用しようとすると、データベースファイル置き場やポート番号やホスト名やユーザ名やパスワードの設定が最低でも必要になりますが、それらが必要なくなります。そのため、データベースのインスタンスを別に立ち上げなくてもよいんだけど、ちょっと SQL を使いたいというようなクイックハックに便利です。

図 4:	一般的な DB と sqlite の違い

まず、この記事に必要な関連パッケージをインストールしましょう。

apt-get install sqlite3 python-pysqlite2

6.1 データベースの作成

sqlite3 という CUI のアプリケーションがあり、一般的な SQL 文を利用することができます。また、 ruby, perl, ocaml, haskell, common lisp, Smalltalk などの一般的なプログラミング言語用のバインディングも用意されており、データベースを利用することができます。

$ sqlite3 debmtg.db
sqlite>

存在しない新しいファイル名を指定すれば、そのファイル名でデータベースが作成されます。この時点で SQL 文 (CREATE TABLE) などが利用できます。

6.2 データをつっこむ

データベースも作成できたので、データをつっこんでみましょう。 csv ファイルからデータベースにデータを挿入するケースを考えてみます。実は sqlite3 の .import コマンドを使えばよいのですが、ここではプログラム言語のバインディングを活用してインポートしてみます。

上川 ,10
岩松,15
山田,9

csv ファイルを読み込み SQL コマンドを出力する python のコードを書きます。

  #!/usr/bin/python2.5

  """ test program to import minimalistic csv file to sqlite database.
  Creates debmtg.db
  """
  from pysqlite2 import dbapi2
  import csv

  con = dbapi2.connect(’debmtg.db’)
  cur = con.cursor()

  cur.execute(’create table test(name text, score number)’)

  for rows in csv.reader(file(’test.csv’)):
      cur.execute(’insert into test(name, score) values(?,?)’,
                      (rows[0].decode(’utf-8’),int(rows[1])))

  con.commit()
  con.close()

6.3 SQL を使ってみる

sqlite3 コマンドを実行するとインタラクティブに SQL 文を入力することが可能です。

まず、 sqlite 独自の命令をつかってデータベースの構造を分析してみます。

$ sqlite3 debmtg.db
sqlite> .tables
test
sqlite> .dump test
BEGIN TRANSACTION;
CREATE TABLE test(name text, score number);
INSERT INTO "test" VALUES(’ 上川’,10);
INSERT INTO "test" VALUES(’ 岩松’,15);
INSERT INTO "test" VALUES(’ 山田’,9);
COMMIT;

SQL 文でランキングを調べたり平均値を調べたりもできます。

sqlite> select name, score from test order by score;
山田|9
上川|10
岩松|15
sqlite> select sum(score)/count(score) from test;
11

以上、簡単ですが、 sqlite の紹介でした。 ____________________________________________________________________________________________

Debian 勉強会資料

2008 年12 月20 日初版第1 刷発行
東京エリア Debian 勉強会（編集・印刷・発行）
__________________________

6 sqlite3 と python で csv ファイルを分析する

6.1 データベースの作成

6.2 データをつっこむ

6.3 SQL を使ってみる