Python からoracle database をアクセスする方法


1.概要
この小文では、Pythonで書かれたプログラムからOracleデータベース中のデータにアクセスする方法について解説する。

PythonからOracle データベースにアクセスするためにoracledbモジュールを使用する。
oracledbはSQL/Net+ライブラリをもちいて、

Pythonプログラム中からSQL文を実行すること。
SQL文による検索結果をPythonプログラムでうけとること。
SQL文を用いて、データベースレコードの作成/更新をPythonから行なうこと。

などを実現している。

2.準備作業
Pythonでoracledbモジュールを利用するためには、若干の準備作業が必要である。これは、Oracleを利用するための環境変数を設定するものである。

1)環境変数の設定

abco1のshell promptの下で、~yamamoto/.oracle_setupをsource する。

%source ~yamamoto/.oracle_setup

もちろん、これを.cshrcや.loginファイルに入れておいても良い。


3.Pythonでのoracledbの利用法の概要

 Pythonでoracledbを利用してOracle Database から情報を引きだすための手順を以下に説明する。

1)pythonを起動する。
Pythonは/usr/local/bin/にあるので、通常は、

% python

でPythonが起動される。もしコマンドがみつから無い旨のメッセージがでたら、

%/usr/local/bin/python

あるいは、

%/proj/local/PA8000/bin/python

を試してみて下さい。

2)orabledbモジュールおよびdbiモジュールをロードする。

import oracledb
import dbi

3)オラクル・データベースとのコネクションを生成する。
これによって、Oracle DBとの接続が確立される。この際、OracleDBのグループ名とそのパスワードが必要である。練習用のグループとパスワードとして、scott/tigertが用意されている。

con=oracledb.oracledb("group/passwd")

4)カーソルオブジェクトを生成する。

cur = con.cursor()

5)カーソル・オブジェクトでsql文を実行する。

cur.execute("<any sql statement >")

6)カーソル・オブジェクトのfetchone/fetchmany/fetchallなどのメソッドでデータをとりこむ。
取り込んだデータは一レコード毎にTupleとなっている。fetchmany/fetchallはレコードのリストを返す。

cur.fetchmany(10)

7)RawDataが必要な場合にはdbiモジュールの助けをかりて変換する。

8)すべてのデータを取り込むとcursorオブジェクトはテーブルの終わりをさす。新しいデータが必要であればこのcursorオブジェクトにたいしexecuteメソッドを実行して、新しいdata viewをつくり、データを取り込むことが出来る。

以上の手順を使ったサンプルプログラムを以下に示す。

#サンプルプログラム
# test for oracledb module
# oracledb_test.py

import oracledb
import dbi

def ex(group_id, passwd , table_name,field_name="*"):
con = oracledb.oracledb(group_id + '/' +passwd)
cur = con.cursor()
cur.execute('select '+field_name+' from ' + table_name)
print cur.fetchall()
return con,cur

if (__name__ == "__main__") :
ex("scott","tiger","USER_TABLES")
ex("scott","tiger","USER_TABLES","TABLE_NAME")
ex("user_op","********","co.sad")


4.Module インターフェイス
 oracle モジュールでは幾つかのPython オブジェクトが定義されている。これらのオブジェクトについて説明する。また、OracleDBとのデータ交換でPythonのデータ型とOracleのデータ型で変換が必要な場合にこの変換をサポートするモジュールdbiについても解説する。

oracledbモジュールでは、

コネクションオブジェクト
cursor オブジェクト

の二つのオブジェクトと コネクションオブジェクトを生成するoracledbモジュールインターフェイス、

oracledb()

を定義している。 dbiモジュールは

DBI helperオブジェクト

を定義する。

モジュールインターフェイス:

oracledb(<connection_string>)
データベースへのコネクションン・オブジェクトを生成する。生成したコネクション・オブジェクトを値として返す。

error
データベース・モジュールがエラー時に発生する例外

コネクション・オブジェクト:

コネクション・オブジェクトは以下のメソッドをもつ。

close()
Close the connection now (rather than whenever __del__ is called).
The connection will be unusable from this point forward; anexception will be raised if any operation is attempted with theconnection.

commit()
Commit any pending transaction to the database.

rollback()
Roll the database back to the start of any pending transaction.

cursor()
コネクションに付随したカーソルオブジェクトを生成する。1つのコネクションオブジェクトに対し複数のカーソルオブジェクトを生成することが可能である。


カーソル・オブジェクト:
カーソル・オブジェクトはデータベース・カーソルを表すオブジェクトである。基本的には、SQLの実行結果を保存しておくテーブルの現在位置を示すと考えてよい。

description
descriptionアトリビュートは長さ7のtupleからなるtuple である。それぞれの長さ7のtupleはcursorのもっているテーブルの各列の、(名前、タイプコード、表示サイズ、内部データサイズ、精度、スケール、null_ok)を示している。
execute()の結果が空の場合やexecute()がまだこのcursorオブジェクトに対して実行されていないばあいには、description アトリビュートはNoneになる。(Noneは空を示すPythonのおぶじぇくとである。)タイプコードは後で述べるdbiで使われるタイプコードである。

close()
Close the cursor now (rather than whenever __del__ is called). The cursor will be unusable from this point forward; an exception will be raised if any operation is attempted with the cursor.

execute(<SQL文>[,<パラメータ>])
SQL文を実行し、Oracleデータベースの検索やコマンドの実行を行なう。
SQL文の後にパラメータを持っていてもよい。パラメータはPythonのSequence(eg. list or tuple)として与える。sequenceの各エレメントはSQL文中の変数にboundされる。割り当てはSQL文に現われる変数の順に従う。

ex:
cur.execute("insert into myTable values(:1,:2,:3)",(1,"Noboru", "Yamamoto"))

パラメータはまたsequenceのsequenceであっても良い。これによって複数の行を一度に挿入することが出来る。

execute()は実行するSQL文の種類とその実行結果に従って異なった値を返す。
If the statement is DDL (e.g. CREATE TABLE), then 1 is returned.
If the statement is DML (e.g. UPDATE or INSERT), then the number of rows affected is returned (0 or a positive integer).
If the statement is DQL (e.g. SELECT), None is returned, indicating that the statement is not really complete until you use one of the fetch methods.

fetchone()
検索結果の次のレコードを取り込んで、tuple として返す。

fetchmany([<サイズ>])
検索結果の次のレコードを最大sizeレコード分取り込んで、tuple として返す。検索結果が空あるいはすべての検索結果が返された後では空リストが返される。サイズを指定しない場合は1が仮定される。

fetchall()
すべての検索結果レコードをtupleのリストとして返す。

setinputsizes()
setoutputsize()
Dummy routines in oracledb.

DBI Helper オブジェクト:

DBIモジュールはデータベース固有のデータ表現にまつわる問題を解決するために導入された。oracledbでは(現在のところ)特に意味のある役割を果たしていない様である。このモジュールで定義されるタイプコードはcursorオブジェクトのdescriptionアトリビュートで使われている。
タイプコード:STRING, RAW, NUMBER, DATE, or ROWID.

Many databases need to have the input in a particular format for
binding to an operation's input parameters. For example, if an input is
destined for a DATE column, then it must be bound to the database in a
particular string format. Similar problems exist for "Row ID" columns
or large binary items (e.g. blobs or RAW columns). This presents
problems for Python since the parameters to the execute() method are
untyped. When the database module sees a Python string object, it
doesn't know if it should be bound as a simple CHAR column, as a raw
binary item, or as a DATE.

To overcome this problem, the dbi module was created. This module
specifies some basic database interface types for working with
databases. There are two classes: dbiDate and dbiRaw. These are simple
container classes that wrap up a value. When passed to the database
modules, the module can then detect that the input parameter is
intended as a DATE or a RAW. For symmetry, the database modules will
return DATE and RAW columns as instances of these classes.

A Cursor Object's description attribute returns information about each
of the result columns of a query.


The module exports the following names:

dbiDate(value)
This function constructs a dbiDate instance that holds a date
value. The value should be specified as an integer number of
seconds since the "epoch" (e.g. time.time()).

dbiRaw(value)
This function constructs a dbiRaw instance that holds a raw
(binary) value. The value should be specified as a Python string.

STRING
This object is used to describe columns in a database that are
string-based (e.g. CHAR).

RAW
This object is used to describe (large) binary columns in a
database (e.g. LONG RAW, blobs).

NUMBER
This object is used to describe numeric columns in a database.

DATE
This object is used to describe date columns in a database.

ROWID
This object is used to describe the "Row ID" column in a database.