Pythonとデータベースの連携
データベースは、Pythonとは違った場所で動いている独立したアプリケーションであることがほとんどです。Pythonからデータベースを使うには、データベース接続用の拡張モジュールを利用します。この拡張モジュールを使ってデータベースに接続し、SQLなどを利用してデータベースと通信をするわけです。
図06 Pythonとデータベース接続モジュール
Pythonには、よく利用される主要なデータベースに接続するための拡張モジュールが一通り揃っています。Python 2.5からは、SQLiteというデータベースと連携をするためのモジュールが標準で搭載されています。SQLiteは、独立したアプリケーションとして動作しないタイプのデータベースです。Pythonを起動するだけで、手軽に本格的なデータベースの機能が利用できます。
ここでは、SQLiteを利用してPythonとデータベースの接続の具体例を見てみましょう。
SQLiteをPythonから利用する
SQLiteはとても手軽で高機能なデータベースシステムです。データベースを使うに当たってデータベースサーバなどを専用に立ち上げる必要はありません。PythonからSQLiteを使うには、sqlite3というライブラリがあればOKです。
Python 2.5からは、sqlite3が標準ライブラリに含まれています。つまり、Pythonをインストールするだけでデータベースが利用できるわけです。Python 2.5以前でSQLiteを使うには、pysqliteというモジュールをインストールする必要があります。
SQLiteはデータベースに登録されたデータをファイルに書き出します。ライブラリを使うときに、データを保存するファイルのパスを指定する必要があります。同じファイルを使えばデータベースに登録されたデータの内容を復元することができますので、PythonやPythonを使ったアプリケーションを終了しても、データは残っています。
PythonとSQLiteを接続する
PythonからSQLiteに接続するためには、まずモジュールをインポートする必要があります。その後、モジュールの関数を使ってコネクションオブジェクトと呼ばれるオブジェクトを作ります。コネクションオブジェクトには、接続文字列と呼ばれる文字列を引数に渡して作ります。接続文字列は接続するデータベースによって異なりますが、SQLiteの場合はデータを保存するファイルのパスを指定することになります。
コネクションオブジェクトを作ったら、次はカーソルと呼ばれるオブジェクトを作ります。データベースとのやりとりはカーソルを経由して行うことになります。SQLをデータベースに送ったり、SQLを実行した結果を受け取るときにカーソルを使うわけです。
以下のサンプルでは、コネクションオブジェクトとカーソルオブジェクトを生成し、SQLiteにSQLを送信しています。
PythonからSQLiteに接続してSQLを送信
:::python
>>> import sqlite3
>>> con=sqlite3.connect(":memory")
>>> cur=con.cursor()
>>> cur.execute("""CREATE TABLE address(
... id serial, postcode text,
... prefecture text, street text);""")
<sqlite3.Cursor object at 0x7ccb0>
>>> cur.execute("""INSERT INTO address(postcode, prefecture, street)
... VALUES('100', 'Tokyo', 'Chiyodaku');""")
<sqlite3.Cursor object at 0x7ccb0>
DBAPIを使う
Pythonでは、いろいろなデータベースと接続するための共通の仕組みとしてDBAPI 2.0と呼ばれる手法が利用されています。Pythonには、いろいろなデータベースに接続するためのモジュールが用意されています。しかし、接続や通信の方法はモジュールごとにまちまちであることがほとんどです。通信や接続の方法をある程度統一し、Pythonからさまざまなデータベースをより手軽に利用できるようにする目的もあり、DBAPIが作られました。個別のモジュールの利用方法を知らなくても、DBAPIの利用方法だけを知っていれば、Pythonからデータベースを利用できる、というわけです。
DBAPI 2.0では、コネクションオブジェクトとカーソルという2つのオブジェクトを使い、データベースとの通信を行います。
コネクションオブジェクト
データベースとの接続を行うときに利用するオブジェクトです。データベース接続モジュールの関数を呼び出して作成します。コネクションオブジェクトを作る関数には、引数として接続文字列と呼ばれる文字列を渡します。接続文字列には、接続するデータベースに関する情報を書き込みます。接続文字列としてどのような文字列を渡すかは、接続するデータベースによって異なります。
データベースの操作が終わり、接続を切るためにもコネクションオブジェクトを使います。
カーソル
データベースにSQLを送信して問い合わせを行ったり、結果を得るために利用するオブジェクトです。コネクションオブジェクトのメソッドを呼び出して作ります。
データベースに問い合わせを行うためには、カーソルのメソッドにSQL文字列を渡します。データベースからデータを取得するような問い合わせを行った場合や、データベースから返ってきたデータを取得するためにもカーソルを利用します。問い合わせを行った後、カーソルに対してメソッドを呼び出すことで、データを取得します。
図07 コネクションオブジェクトとカーソル
コネクションオブジェクトを作る
データベースに対する操作を行うためには、まずコネクションオブジェクトを作ります。SQLite用のコネクションオブジェクトを作るには、sqlite3モジュールのconnect()関数を呼びます。
connect(database(接続文字列))
connect()関数には、SQLiteがデータベースの内容を保存するファイルのパスをフルパスか相対パスで指定します。ファイルを変えることで、複数のデータベースを利用することができます。
":memory:"という文字列を指定すると、ファイルを使わず、メモリ上にSQLiteデータベースを保存します。ただし、メモリ上に保存したデータは接続を閉じると消えてしまいます。開発中やテストを行うときに利用すると便利です。
データベースとの接続を切断するには、コネクションオブジェクトに対してclose()メソッドを送信します。
カーソルを使う
SQLを使ってデータベースと通信を行うためにはカーソルを作ります。カーソルは、コネクションオブジェクトのメソッドを呼ぶことで作ります。
cursor()
カーソルオブジェクトを返します。このカーソルオブジェクトを使うことで、コネクションオブジェクトに指定されたデータベースに対して通信を行います。
カーソルオブジェクトでは以下のメソッドが利用できます。まずexecute()メソッドでSQLを発行して、必要に応じてfetchone()やfetchall()を使ってデータを取り出す、という処理の流れになります。
execute(sq(lSQL文字列)[,パラメータ])
SQLを引数として渡し、実行します。
完全なSQLを文字列として渡すほか、プレースホルダと呼ばれる置換用の文字列を含んだSQLのテンプレートを利用できます。プレースホルダを使うと、一部分だけ変化するSQL文字列を効率的に処理できます。
ただし、execute()メソッドを実行しただけでは、insert文やupdate文によるデータベースの更新は行われないので注意してください。PythonのDBAPIではトランザクション機能が自動的にオンになるためです。本書ではトランザクションについて詳しく解説しませんが、簡単に言うと、複数のデータベース更新処理が重なったときに、データベースに矛盾が起きないように排他制御をするための機能です。
データベースに対する更新を有効にするために,コネクションオブジェクトのcommit()というメソッドを呼ぶ必要があります。commit()メソッドを呼ぶと,トランザクションが閉じられます。commit()メソッドを呼ぶ前にカーソルオブジェクトが消滅すると、トランザクションがロールバックされてしまい,更新の内容がキャンセルされてしまいます。なお、明示的にトランザクションをロールバックしたいときには,rollback()メソッドを呼びます。
fetchone()
カーソルから選択したデータを1行だけ取り出します。戻り値は、選択した結果を順番に格納したタプルとなります。データがない場合にはNoneが返ってきます。SQLを使ってデータベースからデータを選択した後で呼び出します。
fetchal(l)
カーソルから選択したデータをすべて取り出します。戻り値は、選択した結果を順番に格納したタプルのタプルとなります。
また、選択を行った後のカーソルオブジェクトをfor文に添えると、このメソッドが呼び出されます。選択結果を1行づつ繰り返し変数に代入して処理を行えるわけです。
close()
カーソルを閉じます。close()メソッドを呼んだ後のカーソルでexecute()メソッドなどを呼び出そうとするとエラーになります。
SQLの動的生成
Webアプリケーションなどでは、SQLに指定する値だけを変えて処理をする、というようなことを頻繁に行います。たとえば、データを登録するINSERT文では、VALUES以下の値だけが異なり、その他の文字列はいつも決まっています。SQLを文字列として見ると、固定している部分と変化する部分があるわけです。
Pythonを使って、先ほどのaddressテーブルにデータを登録する方法を考えてみましょう。郵便番号、都道府県などの情報は変数に入っているとします。変数を使ってSQLに相当する文字列を組み立て、カーソルオブジェクトのexecute()メソッドを使ってデータベースに渡すわけです。
変数を使って文字列を組み立てる方法は何種類か考えられます。一番素朴な方法は文字列の足し算を使う方法でしょう。postcodeという変数に郵便番号が数値として入っていて、prefectureという変数に都道府県が文字列として入っているとします。INSERT文を組み立てるためには以下のようにすればいいはずです。
:::python
sql="INSERT INTO address(postcode, prefecuture) VALUES("
sql+="'"+str(postcode)+"','"+prefecture+"');"
SQLでは、文字列を引用符で囲む必要があります。文字列と変数の足し算をして、文字列をクオーテーションで囲むようなSQL文字列を作るようにするわけです。処理としては単純ですが、スマートな書き方とはいえません。また、クオーテーションが入り組んでいるので、修正がとても大変そうです。INSERT文を文字列として見ると、処理内容によって変化する場所はいつも同じです。固定した部分と変化する部分を分け、変化する部分だけを効率的に書き換える方法があれば、もっと手軽にSQLを扱えるはずです。
プレースホルダを使う
カーソルオブジェクトのexecute()メソッドにSQLを渡すには2種類の方法があります。1つは完全なSQL文字列を渡す方法。そしてもう1つはプレースホルダを使う方法です。プレースホルダは一種のテンプレートのように使えます。SQLのうち定型の部分と処理内容によって変化する部分を効率的に扱えるわけです。
プレースホルダをSQLに埋め込むには2種類の方法があります。
1つ目の方法はクエスチョンマークを使う方法です。オプションの引数にタプルを渡すと、クエスチョンマークのある場所にオブジェクトが順番に埋め込まれます。
次の例では、INSERT文の条件の部分をプレースホルダを使って書き換えています。「name」、「age」という変数にそれぞれ「guido」(文字列)、「23」(数値)という値が代入された状態で以下のようなメソッドを実行するとします。
:::python
cur.execute("""SELECT lastname FROM people
WHERE firstname=? AND age=?""",(name,age))
結果として、以下のようなSQLがデータベースに送信されます。
:::sql
SELECT lastname FROM people WEHRE firstname='guido' AND age=23
もう1つの方法は、辞書のキーを使って置換する場所を指定する方法です。クエスチョンマークを使った方法と違って、引数の順番の影響を受けないの が便利な点です。
代入された状態で以下のようなメソッドを実行すると、前の例と同じSQLが送信されます。
:::python
cur.execute("""SELECT lastname FROM people
WHERE firstname=:name AND age=:age""",
{'name':name,'age':age})
プレースホルダは、文字列をクエリとして記述するときに必要なクオーテーションやエスケープ処理などを自動的に行います。WebアプリケーションなどでパラメータにSQLを挿入することで起こるSQLインジェクションというセキュリティ上の問題も起こりにくくなります。文字列を連結してSQLを作るより、手軽で安全な手法と言えます。
Webアプリケーションでデータを保存する - データベース編
Pythonからデータベースを利用する方法を一通り学びました。ここでは、データベースを利用してデータの保存を行うWebアプリケーションを作ってみましょう。先ほど作った投票Webアプリケーションを、データベースにデータを保存するように改造してみます。
データベース操作は、DBAPIを利用します。まず、sqlite3モジュールを使ってコネクションオブジェクトを作ります。コネクションオブジェクトからカーソルオブジェクトを作り、SQLを文字列として組み立て、データベースとの通信をする、という手順になります。
データベースにデータを保存するためには、テーブルを定義する必要があります。今回作成するWebアプリケーションでは、「言語名」と「投票数」の2つのデータを管理します。CREATETABLE文を使って、この2つのフィールドを持ったテーブルを定義することになります。
図08 テーブル(language_pole)の構造
まず、言語ごとにINSERT文を使ってテーブルに行を追加します。投票が行われるごとに、UPDATE文を使って投票数を加算していけば、投票結果を保存できるはずです。
前回のプログラムを元に、データを登録、保存する部分のコードを書き換えます。投稿用のフォーム、結果を表示するグラフを表示するためのHTMLを作るコードは、前に作ったものからほぼそのまま流用できます。
以下がデータベース版の投票Webアプリです。データベースとの通信部分が加わったことで、プログラムが少し長くなっています。他のプログラムと同様に、実行権限を与えるなどしてcgi-binフォルダにファイルを置いてください。
dbpole.py
::python
#!/usr/bin/env python
# coding: utf-8
import sqlite3
from httphandler import Request, Response, get_htmltemplate
import cgitb; cgitb.enable()
form_body=u"""
<form method="POST" action="/cgi-bin/dbpole.py">
好きな軽量言語は?<br />
%s
<input type="submit" />
</form>"""
radio_parts=u"""
<input type="radio" name="language" value="%s" />%s
<div style="border-left: solid %sem red; ">%s</div>
"""
def incrementvalue(cur, lang_name):
cur.execute("""SELECT value FROM language_pole
WHERE name='%s'""" % lang_name)
item=None
for item in cur.fetchall():
cur.execute("""UPDATE language_pole
SET value=%d WHERE name='%s'""" % (item[0]+1,
lang_name))
if not item:
cur.execute("""INSERT INTO language_pole(name, value)
VALUES('%s', 1)""" % lang_name)
con=sqlite3.connect('./dbfile.dat') (1)
cur=con.cursor()
try: (2)
cur.execute("""CREATE TABLE language_pole (
name text, value int);""")
except:
pass
content="" (3)
req=Request()
if req.form.has_key('language'):
incrementvalue(cur, req.form['language'].value)
lang_dic={} (4)
cur.execute("""SELECT name, value FROM language_pole;""")
for res in cur.fetchall():
lang_dic[res[0]]=res[1]
for lang in ['Perl', 'PHP', 'Python', 'Ruby']:
num=lang_dic.get(lang, 0)
content+=radio_parts%(lang, lang, num, num)
con.commit()
res=Response()
body=form_body%content
res.set_body(get_htmltemplate()%body)
print res
プログラムの前半部分は、モジュールインポート、テンプレート文字列や関数定義などを行う部分です。後半部分からが、Webアプリケーションの処理をしている部分になります。
まず、sqlite3モジュールを使ってコネクションオブジェクトを作っています。コネクションオブジェクトを作るときにはSQLiteがデータを保存する際に利用するファイルを指定しています。直後では、コネクションオブジェクトからカーソルオブジェクトを作っています(1)。
その次のコードでは、データを保存するテーブルを作っています。カーソルオブジェクトを使ってCREATE TABLE文を文字列としてデータベースに送ることで、データベースにテーブルを作るよう指示をしています。
「language_pole」という名前のテーブルは1回しか作れません。2回目以降作ろうとするとエラーが発生します。このプログラムでは、try~except文でコードを囲み、エラーが起こってもそのままプログラムを実行するようにしています(2)。
次に、リクエストとして送られてきた投票の内容をデータベースに記録するコードが続きます。リクエストの中のクエリを調べ、投稿内容をデータベースに保存するincrementvalue()関数(3)を呼び出しています。
incrementvalue()関数では、SELECT文、INSERT文、UPDATE文の3種類のSQLを組み合わせて投票内容のアップデートをしています。まずSELECT文を使って、ある言語名を保存した列があるかどうか調べています。もし列がない場合は、新しく列を追加する必要があるので、INSERT文を使って列を追加しています。すでに列がある場合は、UPDATE文を使って列の内容を更新しています。
その次には、データベースにSQLを送り、これまでの投稿データを取り出しています。データベースから登録済みのデータを取り出すためにはSELECT文を使います。language_poleに登録されたデータをすべて取り出し、結果を使ってループを組んでいます。ループの中では、言語名をキーにした辞書を作っています(4)。
最後に、投票結果を元にして、投票用のフォームと結果の棒グラフを作り、レスポンスとして返しています。
