★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

45.7. データベースアクセス

PL/Python言語モジュールは自動的にplpyというPythonモジュールをインポートします。 このモジュールの関数と定数は、plpy.fooのように作成したPythonコードから使用することができます。

45.7.1. データベースアクセス関数

plpyモジュールはデータベースコマンドを実行するために数個の関数を用意しています。

plpy.execute(query [, max-rows])

plpy.executeを、問い合わせ文字列および省略可能な行数制限引数を付けて呼び出すと、問い合わせが実行され、結果オブジェクトとして問い合わせ結果が返ります。

結果オブジェクトはリストもしくは辞書オブジェクトをエミュレートします。 結果オブジェクトは、行番号や列名によってアクセスすることができます。 例を示します。

rv = plpy.execute("SELECT * FROM my_table", 5)

これは、my_tableから5行までを返します。 my_tablemy_column列が存在する場合、その列には以下のようにアクセスできます。

foo = rv[i]["my_column"]

戻った行数はビルトインlen関数を使用して取得できます。

結果オブジェクトには以下のメソッドが追加されています。

nrows()

コマンドによる処理の行数を返します。 戻った行数と同じとは限らないことに注意してください。 例えば、UPDATEコマンドではゼロでない値を返しますが、行を戻すことはありません(RETURNINGを使用したときは別です)。

status()

SPI_execute()関数の戻り値を返します。

colnames()
coltypes()
coltypmods()

各々、列名のリスト、列の型OIDのリスト、列に関する型独自の型修飾子のリストを返します。

RETURNINGを持たないUPDATEDROP TABLEなど、結果セットを生成しないコマンドによる結果オブジェクトに対して呼び出された場合、これらのメソッドは例外を発生します。 しかし、ゼロ行の結果セットに対してこれらのメソッドを使用することには問題ありません。

__str__()

標準の__str__メソッドが定義されていますので、例えば問い合わせの実行結果をplpy.debug(rv)を使ってデバッグできます。

結果オブジェクトは変更できます。

plpy.executeを呼び出すことにより、結果セット全体がメモリ内に読み込まれることに注意してください。 結果セットが比較的小さいことが確実な場合だけ、この関数を使用してください。 大規模な結果を取り込む場合の過度のメモリ使用に関する危険を回避したい場合は、plpy.executeではなくplpy.cursorを使用してください。

plpy.prepare(query [, argtypes])
plpy.execute(plan [, arguments [, max-rows]])

plpy.prepareは問い合わせの実行計画を準備します。 問い合わせ内にパラメータ参照がある場合、問い合わせ文字列および引数型のリストとともに呼び出されます。 例を示します。

plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])

text$1として渡される変数の型です。 問い合わせにパラメータを渡さない場合、2番目の引数は省略可能です。

文を準備した後、それを実行するために関数plpy.executeの亜種を使用します。

rv = plpy.execute(plan, ["name"], 5)

実行計画を(問い合わせ文字列ではなく)最初の引数として渡してください。 問い合わせに代入する値のリストを、2番目の引数として渡してください。 問い合わせにパラメータがない場合、2番目の引数は省略可能です。 3番目の引数は、前に述べた省略可能な行数制限引数です。

代わりに、計画オブジェクトのexecuteメソッドを呼び出すことができます。

rv = plan.execute(["name"], 5)

問い合わせパラメータおよび結果行のフィールドは45.3で示した通り、PostgreSQLとPythonのデータ型の間で変換されます。

PL/Pythonモジュールを使用して準備した計画は自動的に保存されます。 これが何を意味するのかについてはSPIの文書(第46章)を参照してください。 これを複数呼び出しにおいて効果的に使用するためには、永続的な格納用辞書であるSDまたはGD45.4を参照)のいずれかを使用する必要があります。 例を示します。

CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
    if "plan" in SD:
        plan = SD["plan"]
    else:
        plan = plpy.prepare("SELECT 1")
        SD["plan"] = plan
    # rest of function
$$ LANGUAGE plpythonu;

plpy.cursor(query)
plpy.cursor(plan [, arguments])

plpy.cursor関数はplpy.executeと同じ引数を受け取り(行数制限引数を除いた)カーソルオブジェクトとして返します。 これにより大規模な結果セットをより小さな塊の中で処理することができます。 plpy.executeの場合と同様、問い合わせ文字列または引数リスト付きの計画オブジェクトを使用できますし、計画オブジェクトのメソッドとしてcursor関数を呼ぶことができます。

カーソルオブジェクトは、整数パラメータを受付け、結果オブジェクトを返すfetchメソッドを提供します。 fetchを呼び出す度に、返されるオブジェクトには次の一群の行が含まれます。 この行数はパラメータ値より多くなることはありません。 全ての行が出し尽くされると、fetchは空の結果オブジェクトを返すようになります。 カーソルオブジェクトはまた、すべての行を出し尽くすまで一度に1行を生成するイテレータインタフェースを提供します。 この方法で取り出されたデータは結果オブジェクトとしては返されず、1つの辞書が単一の結果行に対応する辞書群として返されます。

大きなテーブルのデータを処理する、2つの方法の例を示します。

CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
odd = 0
for row in plpy.cursor("select num from largetable"):
    if row['num'] % 2:
         odd += 1
return odd
$$ LANGUAGE plpythonu;

CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
odd = 0
cursor = plpy.cursor("select num from largetable")
while True:
    rows = cursor.fetch(batch_size)
    if not rows:
        break
    for row in rows:
        if row['num'] % 2:
            odd += 1
return odd
$$ LANGUAGE plpythonu;

CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
odd = 0
plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])

rows = list(plpy.cursor(plan, [2]))  # または = list(plan.cursor([2]))

return len(rows)
$$ LANGUAGE plpythonu;

カーソルは自動的に処分されます。 しかし、カーソルが保有していた資源を明示的に開放したい場合は、closeメソッドを使用してください。 閉じた後、カーソルからこれ以上取り込むことはできません。

ヒント

plpy.cursorによって作成されたオブジェクトと、PythonデータベースAPI仕様において定義されたDB-APIカーソルとを混同しないでください。 名称以外の共通点はありません。

45.7.2. エラーの捕捉

データベースにアクセスする関数はエラーに遭遇し、エラーが関数をアボートして例外を発生させる原因となります。 plpy.executeおよびplpy.prepareは、デフォルトでは関数を終了させるplpy.SPIErrorのサブクラスのインスタンスを発生させることができます。 このエラーは、try/except構文を使用して、Pythonの他の例外と同様に処理できます。 例を示します。

CREATE FUNCTION try_adding_joe() RETURNS text AS $$
    try:
        plpy.execute("INSERT INTO users(username) VALUES ('joe')")
    except plpy.SPIError:

        "うまくいかなかった" を返す
    else:

        "Joeが追加された" を返す
$$ LANGUAGE plpythonu;

発生される例外の実クラスはエラーを引き起こした特定の条件と対応します。 表 A.1にあり得る条件のリストがありますので参照してください。 plpy.spiexceptionsモジュールはPostgreSQLの条件それぞれに対して、その条件名に因んだ名前の例外クラスを定義しています。 例えばdivision_by_zeroDivisionByZerounique_violationUniqueViolationに、fdw_errorFdwErrorなどのようになります。 これらの例外クラスはそれぞれSPIErrorを継承したものです。 このように分離することで特定のエラーをより簡単に扱うことができるようになります。 以下に例を示します。

CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
from plpy import spiexceptions
try:
    plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
    plpy.execute(plan, [numerator, denominator])
except spiexceptions.DivisionByZero:
    return "denominator cannot equal zero"
except spiexceptions.UniqueViolation:
    return "already have that fraction"
except plpy.SPIError, e:
    return "other error, SQLSTATE %s" % e.sqlstate
else:
    return "fraction inserted"
$$ LANGUAGE plpythonu;

plpy.spiexceptionsモジュールからの全ての例外はSPIErrorを継承するため、例外を処理するexcept句は全てのデータベースアクセスエラーを捕捉することに注意してください。

異なったエラー条件を処理する代りの方法として、SPIError例外を捕捉して、例外オブジェクトのsqlstate属性を調べることにより、exceptブロック内部の明細なエラー条件を決定できます。 この属性はSQLSTATEエラーコードを含む文字列値です。 この方法は、ほぼ同じ機能を提供します。