web2py

データベース抽象化レイヤ

依存関係

web2pyは、データベース抽象化層(DAL)を備えています。これは、Pythonオブジェクトを、クエリやテーブル、レコードなどのデーターベース・オブジェクトに対応付けするAPIです。DALは、データベース・バックエンド固有の方言を用いてSQLをリアルタイムで動的に生成します。そのため、開発者はSQLコードを書く必要がなく、また、異なるSQL方言を学ぶ必要もありません(SQLという言葉は総称的に用いています)。そして、アプリケーションは異なるタイプのデータベース間でポータブルになります。この文書の執筆時点で、サポートされているデータベースは、SQLite(Pythonに備わっています。したがってweb2pyにも備わっています)、PostgreSQL、MySQL、Oracle、MSSQL、FireBird、DB2、Informix、Ingres、(部分的に)the GoogleApp Engine(SQL及びNoSQL)です。実験的に他の多くのデータベースもサポートしています。web2pyのウェブサイトやメーリングリストで最新の適応状況を確認してください。Google NoSQLは、第13章で具体的な事例として扱います。

Windowsのバイナリ・ディストリビューションは、SQLiteとMySQLと一緒にすぐ使えます。Macのバイナリ・ディストリビューションは、SQLiteと一緒にすぐに使えます。 他のデータベースバックエンドを使うには、ソース・ディストリビューションから実行し、バックエンドに必要な適切なドライバをインストールしてください。

適切なドライバをインストールしたら、web2pyをソースから起動してください。web2pyはドライバを見つけます。以下はドライバのリストです:

sqlite3、pymysql、pg8000 と imaplib はweb2pyに同梱されています。MongoDBは実験的にサポートしています。IMAPオプションは、IMAPにアクセスするためにDALを使用できます。

web2pyは、DALを構成する次のクラスを定義しています:

DAL オブジェクトは、データベース接続を表します。例えば:

db = DAL('sqlite://storage.db')

Table はデータベースのテーブルを表します。Tableを直接インスタンス化するのではなく、代わりに DAL.define_table によってインスタンス化します。

db.define_table('mytable', Field('myfield'))

Tableの中で最も重要なメソッドは以下のものです:

.insert、.truncate、.drop と .import_from_csv_file .insert, .truncate, .drop, and .import_from_csv_file.

Field はデータベースのフィールドを表します。インスタンス化し、DAL.define_table に引数として渡すことができます。

DAL Rows

rows = db(db.mytable.myfield!=None).select()

Row はフィールドの値を保持します。

for row in rows:
    print row.myfield

Query はSQLの "where" 句を表現するオブジェクトです:

myquery = (db.mytable.myfield != None) | (db.mytable.myfield > 'A')

Set はレコードのセットを表します。最も重要なメソッドは、count、select、update、delete です。例えば次のようになります:

myset = db(myquery)
rows = myset.select()
myset.update(myfield='somevalue')
myset.delete()

Expression は orderby や groupby 式のようなものです。Fieldクラスは、Expressionから派生しています。以下に例を示します。

myorder = db.mytable.myfield.upper() | db.mytable.id
db().select(db.table.ALL, orderby=myorder)

接続文字列

データベースとの接続は、DALのオブジェクトのインスタンスを作成することによって確立されます:

>>> db = DAL('sqlite://storage.db', pool_size=0)

db はキーワードではありません。それはローカルな変数で、接続オブジェクト DAL を格納します。違う名前を付けても問題ありません。DAL のコンストラクタは1つの引数、すなわち接続文字列を必要とします。接続文字列は、特定のバックエンドのデータベースに依存する唯一のweb2pyコードです。ここでは、サポートされているバックエンドのデータベースの具体的な接続文字列の例を示します(全てのケースでデータベースは、localhostのデフォルトポート上で動作し、"test" という名前だと仮定しています):

SQLiteではデータベースが、単一のファイルからなることに注意してください。ファイルが存在しない場合は作成されます。このファイルはアクセスするたびにロックされます。MySQL、PostgreSQL、MSSQL。FireBird、Oracle、DB2、Ingres、Informixの場合、"test" データベースはweb2pyの外部で作成される必要があります。接続が確立されると、web2pyは、テーブルを適切に作成、変更、削除します。

接続文字列を None に設定することも可能です。この場合、DALはいかなるバックエンド・データベースにも接続しませんが、テスト用途としてAPIにはアクセス可能です。この例は、第7章で説明します。

実際にデータベースには接続しないが接続しているかのようにSQL分を発行したい場合があります。これは以下で実現できます。

db = DAL('...', do_connect=False)

この場合、_select、_insert、_update、_delete をSQL文発行のため呼び出すことはできますが、select、insert、update、deleteを呼び出すことはできません。ほとんどの場合、必要になるデータベースドライバなしで、do_connect=False を使用することができます。

web2pyはデータベースのエンコーディングにutf8をデフォルトで使用している点に注意してください。もし異なる動作の既存のデータベースがある場合は、次のように db_codec オプションパラメタの変更が必要です。

db = DAL('...', db_codec='latin1')

そうしない場合、UnicodeDecodeError チケットを受け取ることになります。

接続プール

DALのコンストラクタの2番目の引数は pool_size です。デフォルトは0です。

各リクエストに対して新規のデータベース接続を確立するのはかなり遅いので、web2pyは接続プール機構を実装しています。接続が確立し、ページが処理され、トランザクションが完了すると、その接続は閉じずプールされます。次のHTTPリクエストが来ると、web2pyはそのプールから接続を取得して、新規のトランザクションに利用しようと試みます。もしプールに利用可能な接続が存在しないと、新しい接続を確立します。

web2pyの起動時は、プールは常に空です。プールは、pool_size の値か最大同時リクエスト数の、どちらか少ない方まで増えます。つまり、pool_size=10 でも、サーバが5より多い同時リクエスト数を受け付けない場合、実際のプールサイズは5までしか成長しません。pool_size=0 の場合は、接続プールは使用されません。

プール内の接続は、スレッド間で順番に共有されます。つまり、それらは2つの異なるスレッドによって利用されますが、同時には利用されません。また、それぞれのweb2pyのプロセスには、1つのプールしかありません。

pool_size パラメタは、SQLiteとGAEでは無視されます。接続プールはSQLiteの場合は無視されます。特に恩恵がないからです。

接続の失敗

web2pyがデータベースへの接続に失敗した場合、1秒間待機し、さらに合計5回の接続を試みます。接続プールではプールされている接続の中で、オープンになっているが暫く使用していないものを、データベース側でクローズにすることが可能です。再試行の機能によりweb2pyは、切断した接続に対する再確立を試みます。

複製されたデータベース

DAL(...) の最初の引数は、URIのリストにすることもできます。このケースでは、web2pyはそれぞれに接続しようと試みます。その主な目的は、複数のデータベースサーバに対応し、それらの間で負荷を分散させることです。ここでは典型的な使用例を示します:

db = DAL(['mysql://...1','mysql://...2','mysql://...3'])

このケースでは、DALは最初のものに接続しようと試み、失敗したら、第2、第3を試みます。これはマスタ-スレーブ構成のデータベースにおいて、負荷を分散するためにも利用できます。詳細は、第13章のスケーラビリティの中で説明します。

予約キーワード

DALのコンストラクタに渡すことのできる、別の引数 check_reserved があります。これは、対象となるバックエンドのデータベースでの予約されたSQLキーワードに対して、テーブル名やカラム名をチェックすることができます。check_reserved にはデフォルトでNoneがセットされています。

これは、データベース・バックエンドのアダプタの名前を含む文字列のリストです。

アダプタの名前は、DALの接続文字列において使用されているものと同じです。例えば、PostgreSQLとMSSQLに対してチェックしたい場合は、次のような接続文字列になります:

db = DAL('sqlite://storage.db',
         check_reserved=['postgres', 'mssql'])

DALはリストと同じ順番で、キーワードを走査します。

"all" と "common" という2つの追加オプションがあります。allを指定すると、全ての知られているSQLキーワードに対してチェックされます。commonを指定すると、SELECT、INSERT、UPDATE などの一般的なSQLのキーワードだけがチェックされます。

サポートされるバックエンドに対して、非予約語のSQLキーワードをチェックするかどうかを指定することも可能です。この場合、_nonreserved を名前に追加してください。例えば:

check_reserved=['postgres', 'postgres_nonreserved']

以下のデータベース・バックエンドは、予約語のチェックをサポートしています。

DAL, Table, Field

web2pyのシェルを介してDALのAPIの実験をしてみましょう。

まずは接続を作成してみましょう。例なので、SQLiteを使用してもよいです。バックエンドのエンジンを変更したとしても、この本文の内容を特に変更する必要はありません。

>>> db = DAL('sqlite://storage.db')

データベースは今接続されて、その接続はグローバル変数 db に格納されます。

何時でも、接続文字列を取り出すことができます。

>>> print db._uri
sqlite://storage.db

データベース名も取り出せます。

>>> print db._dbname
sqlite

接続文字列は _uri と呼ばれます。なぜなら、Uniform Resource Identifierのインスタンスだからです。

DALでは、同じデータベースや異なるデータベース、さらに、異なる種類のデータベースに対する複数の接続が可能です。ここでは、最も一般的な状況として、単一のデータベースを想定します。

DALの最も重要なメソッドは define_table です:

>>> db.define_table('person', Field('name'))

これは、"name" フィールド(カラム)を持つ "person" という Table オブジェクトを、定義し格納して返しています。このオブジェクトはまた、db.person に関連付けられているので、その戻り値を捉える必要はありません。

いずれにせよweb2pyが作成するため、"id" フィールドは宣言しないでください。全てのテーブルは、"id" というフィールドをデフォルトで持っています。これは、(1から始まる)自動インクリメントした整数のフィールドで、相互参照や、各レコードをユニークにするために用いられます。すなわち、"id" はプライマリーキーです(注: idが1から始まるかはバックエンドによります、例えばこれは、Google App Engine NoSQLでは適用されません)。

オプションで、type='id' とするフィールドを定義することができます。web2pyはこのフィールドを自動インクリメントしたidフィールドとして使用します。これは、レガシなデータベーステーブルにアクセスする時以外には推奨されません。いくつかの制約がありますが、複数の異なるプライマリキーを使用することもできます。これについては、"レガシデータベースとキー付きテーブル" のセクションで説明します。

テーブルは一度だけ定義できますが、web2pyに強制的に再定義を命じることができます。

db.define_table('person', Field('name'))
db.define_table('person', Field('name'), redefine=True)

もしフィールドの内容が異なっていた場合、その再定義はマイグレーションをトリガーにすることができます。

通常、web2pyモデルはコントローラより先に実行されるため、定義されているが不必要なテーブルが存在する可能性があります。そのためテーブル定義を遅延させることで、コードの実行速度を上げることが必要になります。これは DAL(...,lazy_tables=True) 属性で実現できます。これによりテーブルはアクセスされた場合にのみ、実際に作成されます。

レコードの表現

これはオプションですが、レコードの書式表現を指定することを推奨します:

>>> db.define_table('person', Field('name'), format='%(name)s')

もしくは

>>> db.define_table('person', Field('name'), format='%(name)s %(id)s')

より複雑なものは関数を使用します:

>>> db.define_table('person', Field('name'),
       format=lambda r: r.name or 'anonymous')

format属性は、2つの目的のために使用されます:

• セレクト/オプションのドロップダウンにおいて、参照先のレコードを表現するため。
• このテーブルを参照する全てのフィールドに対して、db.othertable.person.represent 属性を設定するため。これはSQLTABLEがidによって参照を表示するのではなく、代わりに好ましい書式表現を用いることを意味します。

以下に示すのはFieldコンストラクタのデフォルトの値です:

Field(name, 'string', length=None, default=None,
      required=False, requires='<default>',
      ondelete='CASCADE', notnull=False, unique=False,
      uploadfield=True, widget=None, label=None, comment=None,
      writable=True, readable=True, update=None, authorize=None,
      autodelete=False, represent=None, compute=None,
      uploadfolder=os.path.join(request.folder,'uploads'),
      uploadseparate=None,uploadfs=None)

全てが各フィールド型に関係がある、というわけではありません。"length" は、"string" 型のフィールドのみに関係しています。"uploadfield" と "authorize" は、"upload" 型のフィールドのみに関係します。"ondelete" は、"reference" と "upload" 型のフィールドのみに関係します。

• length は、"string"、"password"、"upload" のフィールド最大長をセットします。length が指定されていない場合は、デフォルト値が使用されます。ただし、デフォルト値に関しては後方互換は保証されていません。アップグレードでの意図しないマイグレーションを避けるため、string、password、uploadフィールドに対しては、常にlengthを指定することを推奨します。
• default は、フィールドのデフォルト値を設定します。デフォルト値は値が明示的に指定されていない場合に、挿入を実行したときに使用されます。またSQLFORMを用いてテーブルから構築されたフォームで、事前入力のために使用されます。注意点として、固定値を指定する代わりに、適切な値を返す関数(lambdaを含む)を使用することができます。この場合、複数のレコードが一つのトランザクションで挿入されても、各レコードが挿入されるたびに関数が呼び出されます。
• required はDALに、このフィールドの値が明示的に指定されていない場合、挿入することを許可しません。
• requires はバリデータ、または、バリデータのリストです。これはDALでは使用されず、SQLFORMで使用されます。与えられた型に対するデフォルトのバリデータの一覧を、以下に示します:

DecimalはPythonの decimal モジュールに定義されているような、Decimal オブジェクトを要求し返します。SQLiteでは decimal 型は処理できないので、double として扱われます。(n、m)はそれぞれ、合計の桁数と小数点以下の桁数です。

big-id と big-reference は一部のデータベースエンジンでのみ試験的にサポートされています。レガシーテーブルでは通常あまり使用されないフィールド型です。しかしながら、DALコンストラクタは bigint_id 引数を持っており、True がセットされた場合は、id と reference フィールドをそれぞれ、big-id と big-reference とします。

list: フィールドは特殊です。なぜなら、NoSQL上の特定の非正規化の特徴(Google App Engine NoSQL では、ListProperty や StringListProperty といったフィールド型)が有利になるように、そして、それらを他のサポートされたリレーショナル・データベースに移植できるように設計されているからです。リレーショナルデータベースでは、リストは text フィールドとして格納されます。項目は、| によって区切られ、文字列項目の各 | は || にエスケープされます。詳細は、listフィールドのセクションで説明します。

json フィールド型の意味ははすぐに分かると思います。シリアル化されたjsonオブジェクトを保管します。特にMongoDBでの使用を想定して設計されておりますが他のデータベースアダプタに移植することもできます。

requires=... は、フォームレベルで強制され、required=True はDAL(挿入)レベルで強制されることに注意してください。一方、notnull や unique、ondelete はデータベースレベルで強制されます。それらは時として冗長に見えるかもしれませんが、DALを用いたプログラミングにおいて、その区別を管理することは重要です。

• ondelete は "ON DELETE" SQL文へと変換されます。デフォルトでは、"CASCADE" に設定されています。これは、レコードを削除する時に、それを参照している全てのレコードを削除するようにデータベースに指示します。この機能を無効にするには、ondeleteを "NO ACTION"、または "SET NULL" に設定してください。
• notnull=True は "NOT NULL" SQL文へと変換されます。これにより、データベースから、このフィールドにnull値が挿入されることを防ぎます。
• unique=True は"UNIQUE"SQL文へと変換され、フィールドの値が、そのテーブル内でユニークであることを保証します。これはデータベース・レベルで強制されます。
• uploadfield は、"upload" 型のフィールドに対してのみ適用されます。"upload" 型のフィールドは他に保存された、デフォルトではアプリケーションの "uploads/" フォルダ下のファイルシステム上の、ファイルの名前を格納します。もし uploadfield が設定されている場合は、ファイルは同じテーブルのblobフィールドに格納され、uploadfield の値はそのblobフィールドの名前になります。この点に関しては後で、SQLFORMのコンテキストでより詳しく説明します。
• uploadfolder は、デフォルトではアプリケーションの "uploads/" フォルダになります。別のパスが設定されている場合、ファイルは別のフォルダにアップロードされます。例えば次のようにすると、

Field(...,uploadfolder=os.path.join(request.folder,'static/temp'))

ファイルは web2py/applications/myapp/static/temp フォルダにアップロードされます。 will upload files to the "web2py/applications/myapp/static/temp" folder.

• uploadseparate は、Trueに設定されていると、ファイルはuploadfolderフォルダの異なるサブフォルダの下にアップロードされます。これは、非常に多くのファイルを同じフォルダ/サブフォルダに置くことを回避するために最適化されています。注意: システムの中断なしに、uploadseparateの値をTrueからFalseに変更することはできません。web2pyは分解したサブフォルダを使用するかしないかのどちらかしかとることはできません。ファイルをアップロードしてからこの挙動を変更すると、web2pyはそれらのファイルを取り出すことができなくなります。これが起こった場合は、ファイルを移動し、問題を解決することは可能ですが、ここでは説明しません。
• uploadfs は、Amazon S3やリモートSFTPといった指定した異なるファイルシステムに、ファイルをアップロードすることができます。このオプションを使用するにはPyFileSystemがインストールされている必要があります。uploadfs は PyFileSystem を使用する必要があります。
  PyFileSystem
  uploadfs
• widget は、利用可能なウィジェット・オブジェクトの1つである必要があります。カスタム・ウィジェットやSQLFORM.widgets.string.widgetなどです。利用可能なウィジェットのリストは後述します。各フィールドの型は、デフォルトのウィジェットを持ちます。
• label は自動生成されるフォームにおいて、フィールドに使用されるラベルを含む文字列(または文字列にシリアライズできるもの)です。
• comment はフィールドに関連付けられたコメントを含み、自動生成されるフォームにおいて、入力フィールドの右側に表示される文字列(または文字列にシリアライズできるもの)です。
• writable は、フォームに書き込み可能かどうかを宣言します。
• readable は、フォームに読み込み可能かどうか宣言します。もしフィールドがreadableでもwritableでもない場合、作成と更新フォームにでフィールドは表示されません。
• update は、レコード更新時のフィールドのデフォルト値になります。
• compute は、オプション的な関数です。レコードが挿入もしくは更新された時、compute関数が実行され、フィールドには関数の結果が設定されます。レコードはcompute関数に 辞書 として渡されます。そして辞書には、フィールドの現在の値や、他のどのcomputeフィールドの値も含まれていません。
• authorize は、"upload" フィールドのみで使用し、フィールドのアクセスコントロール要求に使用可能です。詳細は、認証と承認のコンテキストで、後述します。
• autodelete は、アップロードされたファイルを参照するレコードが削除された場合、対応するファイルを削除するかどうかを決定します。"upload" フィールドに対してのみ有効です。
• represent は、Noneまたは、フィールド値を受け取りフィールド値の代替表現として値を返す関数を指定できます。例:

db.mytable.name.represent = lambda name,row: name.capitalize()
db.mytable.other_id.represent = lambda id,row: row.myfield
db.mytable.some_uploadfield.represent = lambda value,row:     A('get it', _href=URL('download', args=value))

"blob"フィールドもまた特別です。デフォルトではバイナリデータは、実際のデータベースフィールドに格納される前に、base64でエンコードされ、そして取り出す時にデコードされます。これにはblobフィールドに必要なものより、25%多く記憶領域を使用するというマイナスの効果がありますが、2つの利点があります。1つは平均でweb2pyとデータベースサーバー間のデータ通信量を削減します。そしてもう１つは、通信をバックエンド固有のエスケープ規則から独立させます。

ほとんどのフィールドやテーブル属性は、定義された後でも変更可能です:

db.define_table('person',Field('name',default=''),format='%(name)s')
db.person._format = '%(name)s/%(id)s'
db.person.name.default = 'anonymous'

(テーブルの属性はフィールド名との衝突を避けるために、下線を接頭文字として使用している点に注意してください)。

データベース接続に対して、定義されているテーブル一覧を問い合わせることができます:

>>> print db.tables
['person']

定義されているフィールド一覧に関しても、テーブルに問い合わせることができます:

>>> print db.person.fields
['id', 'name']

テーブルの型を問い合わせることができます:

>>> print type(db.person)
<class 'pydal.objects.Table'>

またDAL接続から、次のようにテーブルにアクセスすることができます:

>>> print type(db['person'])
<class 'pydal.objects.Table'>

同様にフィールドの名前から、同等な複数の方法でフィールドにアクセスすることができます:

>>> print type(db.person.name)
<class 'pydal.objects.Field'>
>>> print type(db.person['name'])
<class 'pydal.objects.Field'>
>>> print type(db['person']['name'])
<class 'pydal.objects.Field'>

フィールド名を指定して、定義で設定された属性にアクセスすることができます:

>>> print db.person.name.type
string
>>> print db.person.name.unique
False
>>> print db.person.name.notnull
False
>>> print db.person.name.length
32

親のテーブルやテーブル名、親の接続にもアクセスできます:

>>> db.person.name._table == db.person
True
>>> db.person.name._tablename == 'person'
True
>>> db.person.name._db == db
True

フィールドはメソッドを持っています。後述しますがクエリーを作成する際に使用する場合があります。 フィールドオブジェクトの特別なメソッドは validate で、そのフィールドに対するバリデータを呼び出します。

print db.person.name.validate('John')

これは (value, error) のタプルを返します。入力値がバリデータを通った場合、error は None になります。

マイグレーション

define_table は、対応するテーブルが存在するかどうかをチェックします。存在しない場合は、それを作成するSQLを生成し、そのSQLを実行します。テーブルが存在しても定義されているものと違うものであれば、そのテーブルを変更するSQLを生成し実行します。フィールドの型を変更し名前は変更してない場合、データを変更しようと試みます(そうしたくない場合は、テーブルを2度、定義し直す必要があります。2度目はフィールドを除くことによって、そのフィールドを削除するようにweb2pyに指示します。2度目は、新規に定義したフィールドを加えて、web2pyに作らせます)。テーブルが存在し現在の定義と一致する場合は、そのままになります。全ての場合において、そのテーブルを表現する db.person オブジェクトが作られます。

このような挙動を、ここでは "マイグレーション" と言います。web2pyは全てのマイグレーションとマイグレーションの試みを、"databases/sql.log" ファイルにログとして記録します。

define_table の最初の引数は常にテーブルの名前です。他の無名引数はフィールド(Field)です。この関数はまた、"migrate" という省略可能な最後の引数をとることができます。これは、次のように名前によって明示的に参照されなければなりません:

>>> db.define_table('person', Field('name'), migrate='person.table')

migrateの値は、(アプリケーションの "database" フォルダ内の)ファイル名です。このファイルには、このテーブルの内部的なマイグレーション情報がweb2pyによって格納されています。これらのファイルはとても重要で、対応するテーブルが存在する場合は削除するべきではありません。もしテーブルが削除されてこのファイルだけが残っている場合は手動で削除することができます。デフォルトでは、migrateはTrueに設定されています。こうすると、web2pyは接続文字列のハッシュからファイル名を生成します。migrateがFalseに設定されていると、マイグレーションは実行されません。web2pyは、データベースにテーブルが存在し、define_table に列挙されたフィールドを含んでいると想定します。ベストプラクティスは、明示的な名前をこのmigrateテーブルに与えることです。

同じアプリケーションに、同じmigrateファイルを持つ2つのテーブルが存在することはありません。

DALクラスはまた、"migrate" 引数をとります。これは、define_table が呼び出されたときのmigrateのデフォルト値を設定します。例えば:

>>> db = DAL('sqlite://storage.db', migrate=False)

このようにすると、db.define_table がmigrate引数なしに呼び出されたときは常に、migrateのデフォルト値がFalseに設定されます。

web2pyはカラムの追加、削除、変更(sqliteを除く)の処理のみマイグレーションすることに注意してください。default、unique、notnull、ondeleteといった属性の変更はマイグレーションしません。

接続時にマイグレーションを全てのテーブルに対して、無効にすることもできます:

db = DAL(...,migrate_enabled=False)

これは2つのアプリケーションが同じデータベースを共有する場合に推奨されます。2つの内、1つのアプリケーションでマイグレーションを実行し、もう一方は無効にするべきです。

壊れたマイグレーションの修復

マイグレーションには一般的に2つの問題があり、そして、それらを修復する方法があります。

1つの問題は、SQLite固有のものです。SQLiteは、カラムの型を強制せず、また、カラムを削除することができません。この意味は、もし文字列型のカラムがあり、それを削除した場合、実際には削除されないということです。異なる型のカラムを再び追加しよう(例えばdatetime)とした場合、(実質的にゴミとなる)文字列が含まれるdatetimeカラムを作ってしまうことになります。web2pyはこれに対してエラーを出しません。なぜならレコードを取得しようとして失敗するまでは、データベースに何が入っているか分からないからです。

もしweb2pyが、レコード選択時にgluon.sql.parse関数でエラーを返す場合、これは上記の問題による、カラム内の壊れたデータの問題になります。

解決方法は、テーブルの全てのレコードを更新し、問題となっているカラムの値をNoneに更新することです。

もう1つの問題は、より一般的ですが、MySQLで典型的に見られるものです。MySQLは、トランザクション中に複数のALTER TABLEを許可しません。これは、web2pyが、複雑なトランザクションを小さなもの(一度に1つのALTER TABLE)に分解しなければならず、一つ一つコミットしなければならないことを意味します。したがって、複雑なトランザクションの一部がコミットされ、別の部分が失敗して、web2pyを壊れた状態にしてしまう可能性があります。なぜトランザクションの一部が失敗するでしょうか？。なぜなら例えば、テーブルを変更し、文字列カラムを日付カラムに変更しようとしたとき、web2pyがそれらのデータの変換を試みますが、データ変換ができない場合があるからです。web2pyはどうなるのでしょうか？。データベースに実際に格納したテーブル構造は正確に何なのか、ということについて混乱します。

解決策は、全てのテーブルに対するmigrationを無効にし、次のように、fake migrationを有効にすることです:

db.define_table(....,migrate=False,fake_migrate=True)

これにより、テーブルに関するweb2pyのメタデータは、テーブル定義に従って再構築されます。(マイグレーションが失敗する前のものと後のものの)どれが機能するか、複数のテーブル定義で試してみてください。成功したら、fake_migrate=True パラメータを削除してください。

マイグレーションの問題を修復しようとする前に、"applications/yourapp/databases/*.table" ファイルのコピーをとっておくのが賢明です。

マイグレーション問題の修復を全テーブルに対して一度に行うこともできます:

db = DAL(...,fake_migrate_all=True)

データベースに存在しないテーブルがモデルで定義されている場合は失敗しますが、原因部分を限定する手助けにはなります。

挿入

テーブルを指定して、レコードを挿入することができます。

>>> db.person.insert(name="Alex")
1
>>> db.person.insert(name="Bob")
2

挿入は、挿入したそれぞれのレコードのユニークな "id" 値を返します。

テーブルを切捨てることができます。つまり、全てのレコードを削除し、idのカウンタを元に戻します。

>>> db.person.truncate()

この時、もう一度レコードを挿入した場合、カウンタは1から始まります(これはバックエンド固有で、Google NoSQLには適用されません):

>>> db.person.insert(name="Alex")
1

truncate にパラメータを渡す事もできます。例えばSQLITEにidカウンタをリセットするよう指示できます。

db.person.truncate('RESTART IDENTITY CASCADE')

この引数は生のSQL文であるため、特定のエンジンになります。

web2pyはbulk_insertメソッドも提供しています。

>>> db.person.bulk_insert([{'name':'Alex'}, {'name':'John'}, {'name':'Tim'}])
[3,4,5]

これは、挿入されるフィールドの辞書のリストを受け取り、複数の挿入を一度に実行します。そして挿入された複数のレコードのIDを返します。サポートされているリレーショナルデータベースでは、この関数を使用した場合と、ループさせて個別に挿入をした場合を比べても、特に利点はありません。しかし、Google App Engineでは、大幅な高速化が見込めます。

コミット と ロールバック

いかなる作成、削除、挿入、切捨て、削除、更新操作も、コミットコマンドが発行されるまでは、実際にはコミットされません。

>>> db.commit()

確認のため、新規のレコードを挿入してみましょう:

>>> db.person.insert(name="Bob")
2

そしてロールバックします。つまり、最後にコミットした時点からの全ての操作を無効にします:

>>> db.rollback()

再び挿入すると、前回の挿入はロールバックされたので、カウンタは再び2に設定されます。

>>> db.person.insert(name="Bob")
2

モデル、ビュー、コントローラ内のコードは、web2pyのコードで次のように囲まれます:

try:
     execute models, controller function and view
except:
     rollback all connections
     log the traceback
     send a ticket to the visitor
else:
     commit all connections
     save cookies, sessions and return the page

web2pyにおいて コミット や ロールバック を明示的に呼び出すことは、より細かい制御を望まない限り、必要ありません。

生のSQL

クエリ実行時間の計測

全てのクエリは、web2pyによって実行時間が自動計測されます。db._timings 変数はタプルのリストです。それぞれのタプルはデータベース・ドライバに渡された生のSQLとその実行時間を秒数で持っています。この変数はtoolbarを使用し表示可能です:

{{=response.toolbar()}}

executesql

DALは、SQL文を明示的に発行することを可能にします。

>>> print db.executesql('SELECT * FROM person;')
[(1, u'Massimo'), (2, u'Massimo')]

この場合、戻り値は、DALによって構文解析や変換されることはなく、そのフォーマットは特定のデータベース・ドライバに依存します。selectでの使用は通常は必要ありませんが、インデックスの使用ではより一般的です。 executesql は4つのオプション引数を取ります: placeholders、as_dict、fields、colnames です。 placeholders は、SQLで置換されるオプションの値の配列、もしくはDBドライバでサポートされいれば、SQLの名前付きのプレースホルダーに一致するキーを持つ辞書です。

as_dict がTrueに設定されていると、DBドライバによって返される結果のカーソルは、dbフィールド名をキーとして持つ辞書の配列に変換されます。as_dict = True で返された結果は、通常のselectに .as_list() を適用した時に返されるものと同様のものになります。

[{field1: value1, field2: value2}, {field1: value1b, field2: value2b}]

fields 引数はDBから返されたフィールドに対応するDALフィールドオブジェクトのリストです。フィールドオブジェクトはDALオブジェクトで定義されている一つまたは複数のテーブルの一部分になります。fields リストはフィールドオブジェクトに加えて一つまたは複数のテーブルオブジェクトを含むことができたり、テーブル単体(リストでない)であったりします。その場合はテーブルからフィールドオブジェクトを取得できます。

fields 引数の代わりにtablename.fieldnameという形式で colnames 引数を使用することができます。繰り返しになりますが、DALオブジェクトで定義されているテーブルとフィールドが対象になります。

fields と対応する colnames の両方を指定することもできます。この場合、fields はフィールドオブジェクトに加えてDAL　Expressionオブジェクトを含むことができます。"fields" 内のフィールドオブジェクトや対応する colnames はtablename.fieldname形式である必要があります。fields 内のExpressionオブジェクトでは対応する colnames に、任意のラベルを使用できます。

fields や colnames で参照されるDALテーブルオブジェクトはダミーテーブルでも問題なく、データベース内に存在するテーブルである必要はありません。また、fields と colnames はDBから返される結果カーソルのフィールド順でなければいけない点に注意してください。

_lastsql

SQLがexecutesqlを使用し手動で実行されたとしても、DALによって生成されたSQLでも、db._lastsql でSQLのコードを常に見ることができます。これは、デバッグに便利です:

>>> rows = db().select(db.person.ALL)
>>> print db._lastsql
SELECT person.id, person.name FROM person;

web2pyは "*" 演算子を使ったクエリを生成することはありません。web2pyでは常に、明示的にフィールドを選択します。

drop

最後になりますが、テーブルを削除することもできます。この場合は全てのデータが失われます:

>>> db.person.drop()

sqliteでの注意点: ファイルシステムのアプリのデータベースディレクトリに移動し、削除テーブルに関連付けられたファイルを削除しないかぎり、web2pyは削除されたテーブルを再作成することはありません。

インデックス

現在DALのAPIは、テーブルにインデックスを作成するコマンドを提供していませんが、これは executesql コマンドによって行うことができます。その理由は、既存のインデックスではマイグレーションが複雑になり、それを明示的に扱ったほうが良いからです。インデックスは、クエリで頻繁に使用されているフィールドに対して必要になります。

次に示すのは、SQLiteにおいてSQLを使用してインデックスを作成する例 です:

>>> db = DAL('sqlite://storage.db')
>>> db.define_table('person', Field('name'))
>>> db.executesql('CREATE INDEX IF NOT EXISTS myidx ON person (name);')

他のデータベースの方言は非常に似た構文を持っていますが、オプション的な "IF NOT EXISTS" 宣言をサポートしていないことがあります。

レガシー・データベースとキー付きテーブル

web2pyは、いくつかの条件の下で、レガシー・データベースに接続することができます。

最も簡単な方法は、以下の条件を満たしている時です:

• 各テーブルは、必ず "id" と呼ばれる一意で自動インクリメントした整数フィールドを持ちます。
• レコードは、必ず "id" フィールドだけを使用し参照されます。

既存のテーブルにアクセスする時、つまり現在のアプリケーションで、web2pyによってテーブルが作成されていない場合は常に migrate=False としてください。

レガシー・テーブルに自動インクリメントを行う整数フィールドがあり、それが "id" という名前でない場合でも、web2pyはアクセス可能です。しかしこの場合、テーブル定義に Field('....','id') のように、明示的に含む必要があります。ここで...は、自動インクリメントした整数フィールドの名前です。

最後に、レガシー・テーブルが自動インクリメントidでないプライマリキーを使用していた場合、次の例のように、キー付きテーブルを用いてアクセスすることが可能です:

db.define_table('account',
    Field('accnum','integer'),
    Field('acctype'),
    Field('accdesc'),
    primarykey=['accnum','acctype'],
    migrate=False)

• primarykey はプライマリキーを構成するフィールド名のリストです。
• 指定されなくても全てのparimarykeyフィールドは NOT NULL がセットされています。
• キー付きテーブルは他のキー付きテーブルだけを参照できます。
• 参照するフィールドは reference tablename.fieldname フォーマットを使用しなければなりません。
• update_record 関数を、キー付きテーブルのRowsに使用することはできません。

現在はDB2とMS-SQL、Ingres、Informixに対してのみ利用可能です。しかし、他のデータベースもサポートされる予定です。

執筆時点で、primarykey属性が、全てのレガシー・テーブルと、サポートされたデータベース・バックエンドに対して機能することは保障されていません。シンプルにするため、可能なら自動インクリメントしたidフィールドを持つデータベースのビューを作成することを、お勧めします。

分散トランザクション

執筆時点では、この機能はPostgreSQL、MySQL、Firebirdに対してのみサポートされています。これらは2相コミットのAPIを公開しているためです。

異なるPostgreSQLデータベースに接続する、2つ(またはそれ以上)の接続を持っていると仮定します:

db_a = DAL('postgres://...')
db_b = DAL('postgres://...')

モデルやコントローラにおいて、それらを同時にコミットすることが可能です:

DAL.distributed_transaction_commit(db_a, db_b)

失敗した場合は、この関数はロールバックして、Exception を発生させます。

コントローラで1つのアクションが返された時、もし2つの別個の接続を持ち、かつ、上記の関数を呼び出していない場合は、web2pyはそれらを個別にコミットします。これは、1つのコミットが成功し、もう1つが失敗するという可能性があることを意味します。分散トランザクションはこのようなことが起こるのを防ぎます。

いろいろなアップロード

次のモデルを考えてください:

>>> db.define_table('myfile',
    Field('image', 'upload', default='path/'))

'upload' フィールド型の場合、オプションでパスにデフォルト値(絶対パスまたは現在のappフォルダからの相対パス)を設定することができます。そしてデフォルト画像の複製がパスにセットされます。画像を選択しなかった全ての新規レコードについて新しい複製が作成されます。

通常、挿入は、SQLFORMやcrudフォーム(SQLFORMの1つ)を介して自動的で処理されます。しかし場合によっては、ファイルシステム上にすでにファイルがあり、プログラムでアップロードしたいことがあります。これは次のような方法で行うことができます:

>>> stream = open(filename, 'rb')
>>> db.myfile.insert(image=db.myfile.image.store(stream, filename))

簡単な方法でファイルを挿入し自動的にstoreメソッドを呼びだすことも可能です:

>>> stream = open(filename, 'rb')
>>> db.myfile.insert(image=stream)

この場合、ファイル名は可能であればストリームオブジェクトから取得されます。

uploadフィールドオブジェクトの store メソッドは、ファイルストリームとファイル名を受け取ります。ファイル名はファイルの拡張子(型)を決めるのに使用され、(web2pyのアップロード機構に従って)そのファイルのための新しい仮の名前を作成し、(特に指定がなければuploadsフォルダの下の)その新しい仮のファイルにファイルの内容をロードします。そして、新しい仮のファイル名が返され、db.myfile テーブルの image フィールドに格納されます。

もしファイルシステムではなくblobフィールドにファイルが保存されている場合、store() メソッドはblobフィールドにファイルを挿入されない(store() がinsertメソッドより先に呼び出されるから)ので、ファイルは明示的にblobフィールドに挿入される必要があります:

>>> db.define_table('myfile',
        Field('image', 'upload', uploadfield='image_file'),
        Field('image_file', 'blob'))
>>> stream = open(filename, 'rb')
>>> db.myfile.insert(image=db.myfile.image.store(stream, filename),
        image_file=stream.read())

.store の逆は .retrieve になります:

>>> row = db(db.myfile).select().first()
>>> (filename, stream) = db.myfile.image.retrieve(row.image)
>>> import shutil
>>> shutil.copyfileobj(stream,open(filename,'wb'))

Query, Set, Rows

再び、先ほど定義した(削除した)テーブルを考え、3つのレコードを挿入してみます:

>>> db.define_table('person', Field('name'))
>>> db.person.insert(name="Alex")
1
>>> db.person.insert(name="Bob")
2
>>> db.person.insert(name="Carl")
3

テーブルは変数に格納することができます。例えば、person 変数として利用することができます:

>>> person = db.person

フィールドも、name のように変数に格納することができます。例えば次のようにすることができます:

>>> name = person.name

クエリを(==, !=, <, >, <=, >=, like, belongsのような演算子を用いて)構築し、そのクエリを次のように変数 q に格納することもできます:

>>> q = name=='Alex'

db をクエリと共に呼び出すと、レコードセットを定義していることになります。次のように書いて、それを変数 s に格納することができます:

>>> s = db(q)

ここまでデータベースクエリが実行されていないことに注意してください。DAL+クエリ は、単純にクエリにマッチするdb内のレコードセットを定義するだけです。web2pyはクエリからどのテーブル(もしくは複数のテーブル)が該当しているかを決めるので、テーブルを実際に指定する必要はありません。

select

Set s に対して、select コマンドを用いてレコードを取得することができます:

>>> rows = s.select()

これは、Rowオブジェクトを要素とする pydal.objects.Rows クラスの反復可能なオブジェクトを返します。pydal.objects.Row オブジェクトは辞書のように振舞いますが、gluon.storage.Storage と同様、その要素は属性に関連付けられています。前者は、その値が読み取り専用であるということで後者とは異なります。

Rowsオブジェクトは、selectの結果をループで回し、各行の選択したフィールドをプリントすることができます:

>>> for row in rows:
        print row.id, row.name
1 Alex

上の一連の手順は、次のように1つの文で行うことができます:

>>> for row in db(db.person.name=='Alex').select():
        print row.name
Alex

selectコマンドは引数を取ることが可能です。全ての無名引数は、取得するフィールド名として解釈されます。例えば、"id" と "name" フィールドを次のように明示的に取得することができます:

>>> for row in db().select(db.person.id, db.person.name):
        print row.name
Alex
Bob
Carl

テーブルのALL属性によって、全てのフィールドを指定することができます:

>>> for row in db().select(db.person.ALL):
        print row.name
Alex
Bob
Carl

dbにはクエリ文字列が何も渡されていないことに注目してください。web2pyは、personテーブルの全てのフィールドが追加情報なしに要求された場合、personテーブルの全てのレコードが要求されていることを理解しています。

同等の代替構文は以下の通りです:

>>> for row in db(db.person.id > 0).select():
        print row.name
Alex
Bob
Carl

そして、person(id > 0)テーブルの全てのレコードが追加情報なしに要求された場合、web2pyはpersonテーブルの全てのフィールドが要求されていることを理解しています。

一つのrowに対して、

row = rows[0]

値をいくつかの同等な式で取得できます:

>>> row.name
Alex
>>> row['name']
Alex
>>> row('person.name')
Alex

後者の構文は、カラムではなく式で選択したい場合に特に便利です。これについては後述します。

また以下のように、

rows.compact = False

表記法を無効にしたり、

row[i].name

有効にして長い表記法にしたり:

row[i].person.name

できますが、実際にはこのようにする必要は特にありません。

ショートカット

DALはコードを簡素化する、さまざまなショートカットをサポートしています。具体例を示します:

myrecord = db.mytable[id]

これは、id を持つレコードが存在すれば返します。id が存在しない場合は、None を返します。上記の文は以下と等価です:

myrecord = db(db.mytable.id==id).select().first()

次のように、idを使ってレコードを削除することができます:

del db.mytable[id]

これは以下と等価です。

db(db.mytable.id==id).delete()

これは、id を持つレコードが存在すれば削除します。

次のようにして、レコードを挿入することが可能です:

db.mytable[0] = dict(myfield='somevalue')

これは以下と等価です。

db.mytable.insert(myfield='somevalue')

これは、右側の辞書で指定したフィールド値を持つ新規レコードを作成します。

次のようにしてレコードを更新することができます:

db.mytable[id] = dict(myfield='somevalue')

これは以下と等価です。

db(db.mytable.id==id).update(myfield='somevalue')

右側の辞書で指定したフィールド値で既存のレコードを更新します。

Row のフェッチ

以下のように、もう一つの便利な構文があります:

record = db.mytable(id)
record = db.mytable(db.mytable.id==id)
record = db.mytable(id,myfield='somevalue')

上記の構文は、明らかに db.mytable[id] と似ていますが、より柔軟性が高く安全です。まず初めに、これはidが整数(またはstr(id) が整数)であることを確認し、そうでない場合は None を返します(例外を発生させることはありません)。レコードが満たすべき、複数の条件を指定することも可能です。条件が合わない場合は、同様に None を返します。

再帰的な selects

前述のpersonテーブルと、"person" を参照する新規の "thing" テーブルを考えます:

>>> db.define_table('thing',
        Field('name'),
        Field('owner_id','reference person'))

このテーブルの単純なselectは次のようになります:

>>> things = db(db.thing).select()

これは次と等価です。

>>> things = db(db.thing._id>0).select()

._id はテーブルのプライマリキーを参照しています。通常、db.dog._id は db.dog.id と同じで、この本でもこれを前提にしています。

thingsの各Rowに対して、選択したテーブル(thing)のフィールドだけでなく、リンクしたテーブルのフィールドを(再帰的に)取り出すことが可能です:

>>> for thing in things: print thing.name, thing.owner_id.name

ここでは thing.owner_id.name は、thingsの各々のthingに対して一度のデータベースselectが必要であり、このため非効率です。利用可能な時は、再帰的なselectの代わりにjoinを用いることを推奨します。とはいえ、これは個々のレコードにアクセスする時には、便利で実用的です。

personによって参照されたthingsを、逆方向でselectすることも可能です:

person =  db.person(id)
for thing in person.thing.select(orderby=db.thing.name):
    print person.name, 'owns', thing.name

この最後の式で、person.thing は次のものに対するショートカットになります:

db(db.thing.owner_id==person.id)

つまり、現在の person によって参照される(複数の) dog の Set になります。この構文では参照しているテーブルが、参照されたテーブルへ複数の参照を持つ場合は破綻します。そのような時は、より明確に完全なクエリを使用する必要があります。

ビュー における Rows のシリアライズ

クエリーを含む次のアクションがある場合、

def index()
    return dict(rows = db(query).select())

selectの結果は、ビューに次の構文を使用し、表示することができます:

{{extend 'layout.html'}}
<h1>Records</h1>
{{=rows}}

これは以下と等価です:

{{extend 'layout.html'}}
<h1>Records</h1>
{{=SQLTABLE(rows)}}

SQLTABLE はrowsを、HTMLのテーブルに変換します。テーブルはヘッダにカラム名を含んでおり、一行毎にレコードがあります。行は "even" と "odd" クラスで交互にマークされます。内部では、Rowsは最初にSQLTABLEオブジェクト(テーブルとは混同しないでください)へと変換され、シリアライズされます。データベースから抽出した値はまた、そのフィールドに関連付けられたバリデータによってフォマットされ、エスケープされます(注：ビュー内でdbをこのような方法で使用することは、普通は良いMVCのプラクティスとして考えられていません)。

また、SQLTABLEを明示的に呼び出すことも可能で、便利な時があります。

SQLTABLEのコンストラクタは次のようなオプション引数をとります:

• linkto URL、または、参照フィールドをリンクするために使用される関数(デフォルトはNone)
• upload URL、または、アップロードしたファイルのダウンロードを許可するダウンロード関数(デフォルトはNone)
• headers ヘッダーに使用するフィールド名と、そのラベルをマッピングする辞書(デフォルトは {})。一種の命令を指定することもできます。現在は、headers='fieldname:capitalize' をサポートしています。
• truncate テーブルの長い値を切り捨てる文字数(デフォルトは16)
• columns カラムとして表示するフィールド名のリスト(tablename.fieldnameのフォーマット)。リストされていないものは表示されません(デフォルトは全て)。
• **attributes 最外部のTABLEオブジェクトに渡される汎用的なヘルパ属性。

次に例を示します：

{{extend 'layout.html'}}
<h1>Records</h1>
{{=SQLTABLE(rows,
     headers='fieldname:capitalize',
     truncate=100,
     upload=URL('download'))
}}

SQLTABLE は便利ですが、より多くの機能を必要とする場合があります。SQLFORM.grid はSQLTABLEの拡張で、検索、ページング、詳細レコードの表示、作成、編集、削除機能を持ったテーブルを作成します。SQLFORM.smartgrid は上記の全ての機能に加えて、参照レコードへアクセスするためのボタンを作成します。

以下は SQLFORM.grid の使用例です:

def index():
    return dict(grid=SQLFORM.grid(query))

そして対応するビューは次のようになります:

{{extend 'layout.html'}}
{{=grid}}

SQLFORM.grid と SQLFORM.smartgrid は制約を受けますがより強力であるため、SQLTABLE より優れているといえます。第8章で詳細を説明します。

orderby, groupby, limitby, distinct, having

select コマンドは5つのオプション引数をとります: orderby、groupby、limitby、left、cacheです。ここでは、最初の3つについて説明します。

次のように、nameでソートされたレコードを取り出すことができます:

>>> for row in db().select(
        db.person.ALL, orderby=db.person.name):
        print row.name
Alex
Bob
Carl

nameの逆順でソートされたレコードを取り出すことができます(チルダに注意してください):

>>> for row in db().select(
        db.person.ALL, orderby=~db.person.name):
        print row.name
Carl
Bob
Alex

ランダムな順番で取り出したレコードを得ることが可能です:

>>> for row in db().select(
        db.person.ALL, orderby='<random>'):
        print row.name
Carl
Alex
Bob

orderby='<random>' の使用はGoogle NoSQL上ではサポートされません。しかし同じ状況や、同様にビルトインが不十分な他の多くの場合、インポートを使うことができます:

import random
rows=db(...).select().sort(lambda row: random.random())

複数のフィールドに対して、レコードをソートすることができます。これはフィールドを "|" によって連結することで可能です:

>>> for row in db().select(
        db.person.ALL, orderby=db.person.name|db.person.id):
        print row.name
Carl
Bob
Alex

orderbyと一緒にgroupbyを用いて、指定したフィールドの同じ値を持つレコードをグループ化することができます(これはバックエンドに依存します、Google NoSQLでは利用できません):

>>> for row in db().select(
        db.person.ALL,
        orderby=db.person.name, groupby=db.person.name):
        print row.name
Alex
Bob
Carl

グループの条件を指定するために、groupby と一緒に having を使用できます(条件に一致するものだけ、グループ化されます)。

>>> print db(query1).select(db.person.ALL, groupby=db.person.name, having=query2)

query1が表示するレコードをフィルタし、query2がグループ化されるレコードをフィルタしている点に注意してください。

distinct=True の引数を用いて、重複のないレコードだけを選択することができます。フィールドを全て指定して、グループ化するのと同じ効果を持ちます。ただしこの場合、ソートは必要ありません。distinctを用いるとき、全フィールドを選択をしないことは重要です。特に、"id" フィールドを選択しないでください。選択した場合、全レコードが常に重複なしの状態になってしまいます。

以下に例を示します:

>>> for row in db().select(db.person.name, distinct=True):
        print row.name
Alex
Bob
Carl

distinct は式にすることもできます。例えば:

>>> for row in db().select(db.person.name,distinct=db.person.name):
        print row.name
Alex
Bob
Carl

limitbyを使用すると、レコードの一部を選択することができます(以下の例では、0から始まる最初の2つが選択されます):

>>> for row in db().select(db.person.ALL, limitby=(0, 2)):
        print row.name
Alex
Bob

論理演算子

クエリは、ANDの二項演算子 "&" を使用し、組み合わせることができます:

>>> rows = db((db.person.name=='Alex') & (db.person.id>3)).select()
>>> for row in rows: print row.id, row.name
4 Alex

ORの二項演算子 "|" も同様です:

>>> rows = db((db.person.name=='Alex') | (db.person.id>3)).select()
>>> for row in rows: print row.id, row.name
1 Alex

"!=" の二項演算子によって、クエリ(またはサブクエリ)を否定することができます:

>>> rows = db((db.person.name!='Alex') | (db.person.id>3)).select()
>>> for row in rows: print row.id, row.name
2 Bob
3 Carl

もしくは "~" 単項演算子によって、明示的に否定することも可能です:

>>> rows = db(~(db.person.name=='Alex') | (db.person.id>3)).select()
>>> for row in rows: print row.id, row.name
2 Bob
3 Carl

Pythonでの "and" と "or" 演算子のオーバーロード制約により、これらはクエリ生成には使用できません。バイナリ演算子である "&" と "|" が、代わりに使用される必要があります。注意点として、これらの演算子("and" や "or" とは異なる)は比較演算子より優先順位が高いため、別途、上記の例のように括弧で括られる必要があります。同様に、単項演算子である "~" も比較演算子より優先順位が高いため、~ 単項演算子も括弧で括られる必要があります。

in-place論理演算子(累積代入文)を使用し、クエリを構築することもできます:

>>> query = db.person.name!='Alex'
>>> query &= db.person.id>3
>>> query |= db.person.name=='John'

count, isempty, delete, update

セット内のレコードをカウントすることができます:

>>> print db(db.person.id > 0).count()
3

count はオプションで、デフォルトがFalseの distinct 引数を指定することが可能です。これは select で同じ引数を指定した場合と、非常によく似た動作をします。

テーブルのレコードが空かどうかをチェックしたい場合があります。この場合カウントするよりも、isempty メソッドを使うほうがより効率的です。

>>> print db(db.person.id > 0).isempty()
False

もしくは、次の等価の式:

>>> print db(db.person).isempty()
False

セット内のレコードを削除することができます:

>>> db(db.person.id > 3).delete()

セット内の全てのレコードを更新することができます。更新が必要なフィールドに対応する、名前付き引数を渡します:

>>> db(db.person.id > 3).update(name='Ken')

式

更新文で割り当てる値は、式でも可能です。例えば、次のようなモデルで考えてみます。

>>> db.define_table('person',
        Field('name'),
        Field('visits', 'integer', default=0))
>>> db(db.person.name == 'Massimo').update(
        visits = db.person.visits + 1)

クエリで使用される値もまた、式にすることができます。

>>> db.define_table('person',
        Field('name'),
        Field('visits', 'integer', default=0),
        Field('clicks', 'integer', default=0))
>>> db(db.person.visits == db.person.clicks + 1).delete()

case

case

式はcase文を含むこともできます。例えば:

>>> db.define_table('person',Field('name'))
>>> condition = db.person.name.startswith('M')
>>> yes_or_no = condition.case('Yes','No')
>>> for row in db().select(db.person.name, yes_or_no):
...     print row.person.name,  row(yes_or_no)
Max Yes
John No

update_record

web2pyでは update_record を用いて、すでにメモリ上にある単一のレコードを更新することも可能です。

>>> row = db(db.person.id==2).select().first()
>>> row.update_record(name='Curt')

update_record を次のものと混同しないでください。

>>> row.update(name='Curt')

その理由は、単一のrowに対して、update メソッドはrowオブジェクトを更新しますが、update_record のようにデータベースのレコードを更新することはしないからです。

rowの属性を変更(一度に一つずつ)し、それを保存するために引数指定なしで update_record() を実行することもできます:

>>> row = db(db.person.id > 2).select().first()
>>> row.name = 'Curt'
>>> row.update_record() # saves above change

update_record メソッドは id フィールドがselect文に含まれ、cacheable に True がセットされていない場合のみ使用できます。

辞書からの挿入や更新

テーブル名、フィールド名、その値が全て変数に保管されているデータを、テーブルに対し挿入や更新しなければいけない場合があります。例えば: tablename、fieldname、value があるとします。

次の構文で挿入することができます:

db[tablename].insert(**{fieldname:value})

レコードの更新は与えられたidによって行われます:　

db(db[tablename]._id==id).update(**{fieldname:value})

table.id の代わりに table._id を使用した点に注意してください。この場合、"id" 以外の名前の "id" 型のフィールドを持つテーブルに対してもクエリが動作します。 Notice we used table._id instead of table.id. In this way the query works even for tables with a field of type "id" which has a name other than "id".

first と last

レコードを保持したRowsオブジェクトが与えられた時、次のように記述できます:

>>> rows = db(query).select()
>>> first_row = rows.first()
>>> last_row = rows.last()

これは以下のものに相当します。

>>> first_row = rows[0] if len(rows)>0 else None
>>> last_row = rows[-1] if len(rows)>0 else None

as_dict と as_list

Rowオブジェクトは、as_dict() メソッドを用いて標準の辞書にシリアライズすることが可能です。Rowsオブジェクトは、as_list() メソッドを用いて辞書のリストにシリアライズすることが可能です。例をいくつか示します:

>>> rows = db(query).select()
>>> rows_list = rows.as_list()
>>> first_row_dict = rows.first().as_dict()

これらのメソッドは、Rowsを汎用的なビューに渡したり、Rowsをセッションに格納したりするのに便利です(Rowsオブジェクト自体は、オープンしているDB接続への参照があるのでシリアライズできません):

>>> rows = db(query).select()
>>> session.rows = rows # not allowed!
>>> session.rows = rows.as_list() # allowed!

rowsの結合

RowオブジェクトはPythonレベルで結合することができます。以下のように仮定すると:

>>> print rows1
person.name
Max
Tim
>>> print rows2
person.name
John
Tim

2つのrowセットのレコードを結合することができます:

>>> rows3 = rows1 & rows2
>>> print rows3
name
Max
Tim
John
Tim

重複文も除外して結合することもできます:

>>> rows3 = rows1 | rows2
>>> print rows3
name
Max
Tim
John

find, exclude, sort

2つのselectの実行が必要であり、さらに前回のselectのサブセットを保持するしている、ということはよくあります。この場合、再度データベースにアクセスするのは無駄なことです。find、exclude、sort オブジェクトは、Rowsオブジェクトを操作し、データベースアクセスなしで別のRowsオブジェクト生成を可能にします。具体的に次のようになります:

• find は、条件でフィルタされた新規のRowsセットを返します。元のRowsはそのままです。
• exclude は、条件でフィルタされた新規のRowsセットを返します。それらは元のRowsから取り除かれます。
• sort は、条件でソートされた新規のRowsセットを返します。元のRowsはそのままです。

これら全てのメソッドは、単一の引数として、各々のrowに作用する関数をとります。

その使用例です:

>>> db.define_table('person',Field('name'))
>>> db.person.insert(name='John')
>>> db.person.insert(name='Max')
>>> db.person.insert(name='Alex')
>>> rows = db(db.person).select()
>>> for row in rows.find(lambda row: row.name[0]=='M'):
        print row.name
Max
>>> print len(rows)
3
>>> for row in rows.exclude(lambda row: row.name[0]=='M'):
        print row.name
Max
>>> print len(rows)
2
>>> for row in rows.sort(lambda row: row.name):
        print row.name
Alex
John

これらは組み合わせることができます:

>>> rows = db(db.person).select()
>>> rows = rows.find(
        lambda row: 'x' in row.name).sort(
            lambda row: row.name)
>>> for row in rows:
        print row.name
Alex
Max

Sortは reverse=True という読んで字のごとくの引数があります。

find メソッドはselect method と同様の記述方法・役割を持つlimitby引数を持ちます。

その他のメソッド

update_or_insert

同じ値が存在しない場合だけ、挿入したい時があります。 これは以下のように実現できます。

db.define_table('person',Field('name'),Field('birthplace'))
db.person.update_or_insert(name='John',birthplace='Chicago')

Chicagoで生まれたJohnが、他に存在しない場合だけ挿入されます。

レコードの存在チェックに、どのキーを使用するかを指定することができます。例えば:

db.person.update_or_insert(db.person.name=='John',
     name='John',birthplace='Chicago')

Johnが存在する場合はbirthplaceが更新され、それ以外は挿入されます。

上記の例では一つのフィールドで条件指定していました。これはqueryを使用することもできます。

db.person.update_or_insert((db.person.name=='John') & (db.person.birthplace=='Chicago')),
     name='John',birthplace='Chicago',pet='Rover')

validate_and_insert, validate_and_update

この関数は、

ret = db.mytable.validate_and_insert(field='value')

以下とほぼ同じ様に動作します。

id = db.mytable.insert(field='value')

違いは、挿入の前にバリデータが実行され、通らなかった場合は挿入されないという点です。バリデータを通らなかった場合は ret.error にエラー内容があります。通った場合は、新規レコードのidは ret.id にあります。通常、バリデータはフォームの処理ロジックで実装されるので、この関数を使用する機会はほとんどないはずです。

同様に、

ret = db(query).validate_and_update(field='value')

以下とほぼ同じ様に動作します。

num = db(query).update(field='value')

違いは、更新の前にバリデータが実行される点です。単一のテーブルでだけ動作する点に注意してください。更新されたレコード数は res.updated に、エラーは ret.errors にあります。

smart_query (実験的)

以下のような自然言語を使ったクエリを解析したい場合があります。

name contain m and age greater than 18

DALはこのようなタイプのクエリを解析するメソッドを提供します:

search = 'name contain m and age greater than 18'
rows = db.smart_query([db.person],search).select()

最初の引数はテーブルのリストか検索可能なフィールドでないといけません。検索文字列が無効な場合は RuntimeError が発生します。この機能はRESTfulなインターフェース(１０章を参照)を構築する場合や、SQLFORM.grid と SQLFORM.smartgrid の内部で使用されています。

smartquery検索文字列では、フィールドはフィールド名だけかテーブル名.フィールド名で指定できます。空白を含む文字はダブルクォーテーションで区切られます。

計算されたフィールド

DALのフィールドは compute フィールドを持つことがあります。これは、Rowオブジェクトを引数に取り、そのフィールドに対する値を返す関数(もしくは無名関数)である必要があります。新規のレコードが挿入や更新などで変更される時、そのフィールドの値が用意されていない場合、web2pyは compute 関数を用いて他のフィールドの値から計算しようとします。以下がその例です:

>>> db.define_table('item',
        Field('unit_price','double'),
        Field('quantity','integer'),
        Field('total_price',
            compute=lambda r: r['unit_price']*r['quantity']))
>>> r = db.item.insert(unit_price=1.99, quantity=5)
>>> print r.total_price
9.95

なお、計算された値はdbに格納され、後述する仮想フィールドの場合のように再取得時に計算されることはありません。計算されたフィールドの2つの典型的な活用方法は、次の通りです:

• wikiアプリケーションにおいて、HTMLに加工されたwikiの入力テキストを、リクエスト毎の加工を避けるために格納する
• 検索用に、フィールドの正規化した値を計算し、検索時に使用する

仮想フィールド

仮想フィールドもまた、(前節のように)計算されたフィールドですが、それらは異なります。なぜなら、データベースには格納されず、また、データベースからレコードが取り出されるたびに計算されるという点で仮想であるからです。追加の格納先なしに単純にユーザーコードを用いることができますが、それを用いて検索することはできません。

ニュースタイル仮想フィールド

web2pyは新しくて簡単な仮想フィールドやlazy仮想フィールドを定義する方法を提供します。ここで述べる機能を提供するAPIが若干修整される可能性があるため、この節は実験的としておきます。

先ほどの小節で説明した例を考えて見ましょう。例えば次のようなモデルがあります:

>>> db.define_table('item',
        Field('unit_price','double'),
        Field('quantity','integer'),

total_price 仮想フィールドを以下のように定義することができます。

>>> db.item.total_price = Field.Virtual(
    lambda row: row.item.unit_price*row.item.quantity)

つまり、Field.Virtual として新しい total_price というフィールドを単純に定義しています。コンストラクタの唯一の引数は、rowを取得し、計算された値を返す関数です。

上記のように定義された仮想フィールドの場合、レコードが選択された際に、全てのレコードが自動で計算されます。

>>> for row in db(db.item).select(): print row.total_price

呼び出された時にオンデマンドで計算する方法も可能です。例えば:

>>> db.item.discounted_total = Field.Method(lambda row, discount=0.0:        row.item.unit_price*row.item.quantity*(1.0-discount/100))

このケースでは、row.discounted_total は値ではなく関数です。この関数は暗黙の row を除いて(rowオブジェクトの self のように)、同じ引数が Method コンストラクタに渡されます。

上記の例にある遅延(lazy)フィールドは、それぞれの item の合計金額を計算します:

>>> for row in db(db.item).select(): print row.discounted_total()

そして、discount 率(15%)をオプションで渡すこともできます:

>>> for row in db(db.item).select(): print row.discounted_total(15)

VirtualとMethodのフィールドはテーブルを定義する際に、合わせて定義しておくことができます:

>>> db.define_table('item',
        Field('unit_price','double'),
        Field('quantity','integer'),
        Field.Virtual('total_price', lambda row: ...),
        Field.Method('discounted_total', lambda row, discount=0.0: ...))

仮想フィールドは他のフィールドと同様の属性(default, readable, requires, etc)を持たず、db.table.fields のリストには表示されず、テーブル(TABLE)やグリッド(SQLFORM.grid, SQLFORM.smartgrid)ではデフォルトで表示されない点に気をつけてください。

オールドスタイル仮想フィールド

1つ以上の仮想フィールドを定義するためには、コンテナクラスを定義し、インスタンス化し、テーブルまたは選択に対してリンクさせる必要があります。例えば、次のようなテーブルを考えてください:

>>> db.define_table('item',
        Field('unit_price','double'),
        Field('quantity','integer'),

この時、total_price という仮想フィールドを次のように定義できます。

>>> class MyVirtualFields(object):
        def total_price(self):
            return self.item.unit_price*self.item.quantity
>>> db.item.virtualfields.append(MyVirtualFields())

単一の引数(self)を取るクラスの各メソッドが、新規の仮想フィールドになることに注意してください。フィールドの値は self.item.unit_price のように完全パスで参照されます。テーブルは、このクラスのインスタンスをテーブルの virtualfields 属性に追加することによって、この仮想フィールドにリンクされます。

仮想フィールドも同様に、次のように再帰的なフィールドにアクセスできます。

>>> db.define_table('item',
        Field('unit_price','double'))
>>> db.define_table('order_item',
        Field('item','reference item'),
        Field('quantity','integer'))
>>> class MyVirtualFields(object):
        def total_price(self):
            return self.order_item.item.unit_price                 * self.order_item.quantity
>>> db.order_item.virtualfields.append(MyVirtualFields())

再帰的なフィールドは self.order_item.item.unit_price にアクセスしていますが、ここで、self はループで回されているレコードであることに注意してください。

これらは、結合(JOIN)の結果に対しても作用することができます

>>> db.define_table('item',
        Field('unit_price','double'))
>>> db.define_table('order_item',
        Field('item','reference item'),
        Field('quantity','integer'))
>>> rows = db(db.order_item.item==db.item.id).select()
>>> class MyVirtualFields(object):
        def total_price(self):
            return self.item.unit_price                 * self.order_item.quantity
>>> rows.setvirtualfields(order_item=MyVirtualFields())
>>> for row in rows: print row.order_item.total_price

この場合、どのように構文が異なっているかに注意してください。仮想フィールドは、join選択に属している self.item.unit_price と self.order_item.quantity の両方にアクセスしています。仮想フィールドはrowsオブジェクトの setvirtualfields メソッドを用いてテーブルのrowsに付け加えられます。このメソッドは任意の数の名前付き引数を取ります。そして次のように、複数のクラスで定義された複数の仮想フィールドを設定し、それらを複数のテーブルに付け加えることができます:

>>> class MyVirtualFields1(object):
        def discounted_unit_price(self):
            return self.item.unit_price*0.90
>>> class MyVirtualFields2(object):
        def total_price(self):
            return self.item.unit_price                 * self.order_item.quantity
        def discounted_total_price(self):
            return self.item.discounted_unit_price                 * self.order_item.quantity
>>> rows.setvirtualfields(
        item=MyVirtualFields1(),
        order_item=MyVirtualFields2())
>>> for row in rows:
        print row.order_item.discounted_total_price

仮想フィールドは遅延(lazy)することが可能です。そのためにやることは、関数を返すようにし、その関数を呼び出すことによってアクセスすることです:

>>> db.define_table('item',
        Field('unit_price','double'),
        Field('quantity','integer'),
>>> class MyVirtualFields(object):
        def lazy_total_price(self):
            def lazy(self=self):
                return self.item.unit_price                     * self.item.quantity
            return lazy
>>> db.item.virtualfields.append(MyVirtualFields())
>>> for item in db(db.item).select():
        print item.lazy_total_price()

もしくは、無名関数を使用したショートカットを使用可能です:

>>> class MyVirtualFields(object):
        def lazy_total_price(self):
            return lambda self=self: self.item.unit_price                 * self.item.quantity

1対多のリレーション

web2pyのDALを用いて1対多のリレーションをどのように実装するかを説明するために、"person" テーブルを参照するもう1つの "thing" テーブルを定義します。"person" もここで再定義します:

>>> db.define_table('person',
                    Field('name'),
                    format='%(name)s')
>>> db.define_table('thing',
                    Field('name'),
                    Field('owner_id', 'reference person'),
                    format='%(name)s')

"thing" テーブルは、物の名前と持ち主という2つのフィールドを持ちます。"owner_id" は参照フィールドです。参照フィールドは2つの等価な方法で指定することができます:

Field('owner_id', 'reference person')
Field('owner_id', db.person)

後者は前者に変換されます。lazyテーブル、自己参照、それ以外の循環的な参照型の場合で前者で記述する必要がある場合以外は、これらは等価です。

フィールド型が他のテーブルの場合、前提として他のテーブルをidで参照します。実際、実在の型の値を出力及び取得することができます:

>>> print db.thing.owner_id.type
reference person

ここで、Alexが持っている2つとBobが持っている1つの計3つを挿入してみます:

>>> db.thing.insert(name='Boat', owner_id=1)
1
>>> db.thing.insert(name='Chair', owner_id=1)
2
>>> db.thing.insert(name='Shoes', owner_id=2)
3

他のテーブルと同じように、テーブルをselectすることができます:

>>> for row in db(db.thing.owner_id==1).select():
        print row.name
Boat
Chair

thingはpersonに対して参照を持っているため、personは複数の物を持つことができます。したがって、personテーブルのレコードはこの時、thingという新規の属性を取得します。これにより、全ての持ち主に対してループを回して、それらの所有物を取得することが簡単にできるようになります:

>>> for person in db().select(db.person.ALL):
        print person.name
        for thing in person.thing.select():
            print '    ', thing.name
Alex
     Boat
     Chair
Bob
     Shoes
Carl

内部結合(Inner Joins)

同様の結果を得るための別の方法は、join、具体的には、INNER JOINを用いることです。web2pyは、次の例のようにクエリが2つ以上のテーブルをリンクする時に、joinを自動で透過的に実行します:

>>> rows = db(db.person.id==db.thing.owner_id).select()
>>> for row in rows:
        print row.person.name, 'has', row.thing.name
Alex has Boat
Alex has Chair
Bob has Shoes

web2pyがjoinを行って、rowsが、一緒にリンクされた各テーブルからの2つのレコードを含んでいることに注目してください。2つのレコードは競合する名前のフィールドを持つ可能性があるので、rowからのフィールド値を取り出すときに、テーブルを指定する必要があります。つまり以前は次のように指定しました:

row.name

これが物の名称か、持ち主の名前かは明らかでした。joinの結果では、次のようにより明示的にする必要があります:

row.person.name

もしくは:

row.thing.name

INNER JOINには別の構文もあります:

>>> rows = db(db.person).select(join=db.thing.on(db.person.id==db.thing.owner_id))
>>> for row in rows:
    print row.person.name, 'has', row.thing.name
Alex has Boat
Alex has Chair
Bob has Shoes

出力結果は同じですが、生成されるSQLは異なる可能性があります。後者の構文は同じテーブルが2回結合されて別名が使用された場合に、それぞれのテーブルを明確に指定できます:

>>> db.define_table('thing',
        Field('name'),
        Field('owner_id1','reference person'),
        Field('owner_id2','reference person'))
>>> rows = db(db.person).select(
    join=[db.person.with_alias('owner_id1').on(db.person.id==db.thing.owner_id1).
          db.person.with_alias('owner_id2').on(db.person.id==db.thing.owner_id2)])

join の値には、db.table.on(...) のリストを使用できます。

左外部結合(Left Outer Join)

Carlは何も持っていないので、上記の表には表示されませんでした。持ち主(物を持っているいないに関わらず)と物(もし所有されていれば)を選択しようとした場合、LEFT OUTER JOINを実行する必要があります。これは、selectコマンドの "left" 引数を用いて行うことができます。以下がその例です:

>>> rows=db().select(
        db.person.ALL, db.thing.ALL,
        left=db.thing.on(db.person.id==db.thing.owner_id))
>>> for row in rows:
        print row.person.name, 'has', row.thing.name
Alex has Boat
Alex has Chair
Bob has Shoes
Carl has None

ここで:

left = db.thing.on(...)

これは、left joinクエリを行います。db.thing.on の引数は、joinに必要な条件になります(上述のinner joinで使用したものと同じです)。left joinの場合、どのフィールドを選択するかは明示的にする必要があります。

複数のleft joinをする場合は、db.mytable.on(...) のリストかタプルを left 属性に渡すことで組み合わせることができます。

グループ化とカウント

結合(join)を行う時、特定の条件に従って行をグループ化し、カウントしたい場合があります。例えば、持ち主が持っている物の数をカウントする場合です。web2pyではこれも同様に行うことができます。初めに、カウント演算子が必要になります。第2に、personテーブルとthingテーブルを、その持ち主(owner)によってjoinします。第3に、全ての行(person + thing)を選択し、person毎にそれらをグループ化して、グループ化している最中にカウントします:

>>> count = db.person.id.count()
>>> for row in db(db.person.id==db.thing.owner_id).select(
        db.person.name, count, groupby=db.person.name):
        print row.person.name, row[count]
Alex 2
Bob 1

count 演算子(組み込み)が、フィールドのように用いられていることに注意してください。ここでの唯一の問題は、どのように情報を取り出すかにあります。各行は明らかに1人のpersonとcountを含んでいますが、しかしcountはpersonのフィールドではなく、テーブルでもありません。これは、どうなるのでしょうか？。クエリ式自体と同じキーを持つ、レコードを表現するストレージオブジェクトになります。Fieldオブジェクトのcountメソッドには、オプションの distinct 引数を持っています。True と指定した場合、異なる値だけをカウントします。

多対多

前述の例では、1つの物は1人の持ち主(owner)を持つけれど、1人の持ち主は多くの物を持てるようにしました。AlexとCurtが持っているボートははどうなるのでしょうか？これには多対多のリレーションが必要です。そしてこれは、所有(ownership)関係で1人の持ち主と1つ物をリンクする中間テーブルを介して実現されます。

以下のように行います:

>>> db.define_table('person',
                    Field('name'))
>>> db.define_table('thing',
                    Field('name'))
>>> db.define_table('ownership',
                    Field('person', 'reference person'),
                    Field('thing', 'reference thing'))

これまでの所有(ownership)関係は、次のように書き換えることができます:

>>> db.ownership.insert(person=1, thing=1) # Alex owns Boat
>>> db.ownership.insert(person=1, thing=2) # Alex owns Chair
>>> db.ownership.insert(person=2, thing=3) # Bob owns Shoes

今度は、Curtがボートを一緒に持っているという、新しいリレーションを加えることができます:

>>> db.ownership.insert(person=3, thing=1) # Curt owns Boat too

3方向のテーブル間のリレーションを持っているので、操作を実行する上で、次のような新規のSetを定義すると便利です:

>>> persons_and_things = db(
        (db.person.id==db.ownership.person)         & (db.thing.id==db.ownership.thing))

これで新規のSetから、全ての持ち主と持ち物を簡単に選択できます:

>>> for row in persons_and_things.select():
        print row.person.name, row.thing.name
Alex Boat
Alex Chair
Bob Shoes
Curt Boat

同様に、Alexが持っている全ての物を検索することもできます:

>>> for row in persons_and_things(db.person.name=='Alex').select():
        print row.thing.name
Boat
Chair

そして、ボートの持ち主も検索することができます:

>>> for row in persons_and_things(db.thing.name=='Boat').select():
        print row.person.name
Alex
Curt

多対多の簡易な代替案はタグ付けです。タグ付けは IS_IN_DB のコンテキストで後述します。タグ付けは、Google App Engine NoSQLのようなJOINをサポートしていないデータベース・バックエンドでも機能します。

多対多、list:<type>、contains

web2pyは、以下の特別なフィールド型を用意しています:

list:string
list:integer
list:reference <table>

これらはそれぞれ、文字列、整数、参照のリストを収容します。

Google App Engine NoSQLでは、list:string は StringListProperty にマッピングされ、他の2つは、ListProperty(int) にマッピングされます。リレーショナル・データベースでは、| によって区切られた項目のリストを持つテキストフィールドにマッピングされます。例えば、[1,2,3] は |1|2|3| にマッピングされます。

文字列のリストでは、項目内の任意の | が || に置換されるように項目はエスケープされます。いずれにせよ、これは内部表現でありユーザーに対しては透過的です。

次の例のように、list:string を用いることができます:

>>> db.define_table('product',
        Field('name'),
        Field('colors','list:string'))
>>> db.product.colors.requires=IS_IN_SET(('red','blue','green'))
>>> db.product.insert(name='Toy Car',colors=['red','green'])
>>> products = db(db.product.colors.contains('red')).select()
>>> for item in products:
        print item.name, item.colors
Toy Car ['red', 'green']

list:integer も同様に機能します。ただし、項目は整数でなければなりません。

例のごとく、この要求は、insert レベルではなく、フォームレベルで強制されます。

list:<type> フィールドにおいて、contains(value) 演算子は value が含まれているかをリストに対してチェックする、通常にはないクエリにマッピングされます。contains 演算子は、標準の string と text フィールドでも機能し、LIKE '%value%' にマッピングされます。

list:reference と contains(value) 演算子は、多対多リレーションの非正規化にとって特に有用です。以下がその例です。

>>> db.define_table('tag',Field('name'),format='%(name)s')
>>> db.define_table('product',
        Field('name'),
        Field('tags','list:reference tag'))
>>> a = db.tag.insert(name='red')
>>> b = db.tag.insert(name='green')
>>> c = db.tag.insert(name='blue')
>>> db.product.insert(name='Toy Car',tags=[a, b, c])
>>> products = db(db.product.tags.contains(b)).select()
>>> for item in products:
        print item.name, item.tags
Toy Car [1, 2, 3]
>>> for item in products:
        print item.name, db.product.tags.represent(item.tags)
Toy Car red, green, blue

list:reference tag フィールドがデフォルト値を持っている点に注意してください。

requires = IS_IN_DB(db,'tag.id',db.tag._format,multiple=True)

これは、フォームにおいて SELECT/OPTION の複数ドロップボックスを生成します。

また、このフィールドはデフォルトで、フォーマットした参照のカンマ区切りリストのように、参照リストを表現する represent 属性を取得することにも注意してください。これは、フォームと SQLTABLE の読み込み時に利用されます。

list:reference はデフォルトのバリデータとデフォルトの表現を持つ一方、list:integer と list:string は持ちません。したがって、これら2つをフォームで利用する場合、IS_IN_SET か IS_IN_DB バリデータが必要になります。

その他の演算子

web2pyには、同等なSQL演算子にアクセスするためのAPIを提供する演算子があります。"log" という別のテーブルを定義してみます。そのテーブルでは、セキュリティ・イベントとそのevent_timeと重大度(severity)を格納するようにします。ここで重大度(severity)は整数です。

>>> db.define_table('log', Field('event'),
                           Field('event_time', 'datetime'),
                           Field('severity', 'integer'))

前回と同様、イベントとして、"port scan" と "xss injection" と "unauthorized login" を数個挿入します。例として、同じevent_timeを持つが重大度(それぞれ1、2、3)は異なるイベントをログとして記録します。

>>> import datetime
>>> now = datetime.datetime.now()
>>> print db.log.insert(
        event='port scan', event_time=now, severity=1)
1
>>> print db.log.insert(
        event='xss injection', event_time=now, severity=2)
2
>>> print db.log.insert(
        event='unauthorized login', event_time=now, severity=3)
3

like, regexp, startswith, contains, upper, lower

フィールドは、文字列を照合するためのlike演算子を持っています:

>>> for row in db(db.log.event.like('port%')).select():
        print row.event
port scan

ここで、"port%" は "port" から始まる文字列を示しています。パーセント記号文字 "%" は、"任意の文字列"を意味するワイルドカード文字です。

like演算子はケースセンシティブですが、以下のようにオプションで指定することもできます。

db.mytable.myfield.like('value',case_sensitive=True)

web2pyはまた、いくつかのショートカットを提供しています:

db.mytable.myfield.startswith('value')
db.mytable.myfield.contains('value')

これは、それぞれ以下に相当します which are equivalent respectively to

db.mytable.myfield.like('value%')
db.mytable.myfield.like('%value%')

contains は、前節で説明したように、list:<type> フィールドに対して特別な意味を持つことに注意してください。

contains メソッドは値をリストで渡すことや、ブーリアン型の変数 all をオプションで指定し全ての値を含むレコードだけを検索することもできます。

db.mytable.myfield.contains(['value1','value2'], all=True)

or any value from the list

db.mytable.myfield.contains(['value1','value2'], all=false)

like メソッドのように動作する regexp メソッドというもあり、正規表現をつかって検索することもできます。これはPostgreSQLとSQLiteでしかサポートされていません。

同様に、フィールドの値を大文字、または、小文字に変換するために upper と lower メソッドを使用することができます。さらに、like演算子と組み合わせることができます:

>>> for row in db(db.log.event.upper().like('PORT%')).select():
        print row.event
port scan

year、month、day、hour、minutes、seconds

dateとdatetimeフィールドはday、month、yearメソッドを持ちます。datetimeおよびtimeフィールドは、hour、minutes、secondsメソッドを持ちます。以下がその例です:

>>> for row in db(db.log.event_time.year()==2013).select():
        print row.event
port scan
xss injection
unauthorized login

belongs

SQLのIN演算子は、belongsメソッドを介して実現されます。このメソッドは、フィールドの値が指定したセット(タプルのリスト)に所属している時にtrueを返します:

>>> for row in db(db.log.severity.belongs((1, 2))).select():
        print row.event
port scan
xss injection

DALはまた、belongs演算子の引数にネストしたselectを許しています。唯一の注意点は、ネストしたselectは select ではなく _select でなければならず、フィールドは1つだけ、明示的にセットを定義するものを選択する必要があります。

>>> bad_days = db(db.log.severity==3)._select(db.log.event_time)
>>> for row in db(db.log.event_time.belongs(bad_days)).select():
        print row.event
port scan
xss injection
unauthorized login

このようにネストしたselect文が要求され、検索するフィールが他テーブルを参照している場合、クエリーを引数として使用できます。例えば:

db.define_table('person', Field('name'))
db.define_table('thing', Field('name'), Field('owner_id', 'reference thing'))
db(db.thing.owner_id.belongs(db.person.name=='Jonathan')).select()

この場合、次のselect文は db.thing.owner_id によって参照されているフィールドのみが必要なのが明らかなので、_select を使用する必要はありません。

ネストしたselect文は値の挿入・更新の場合にも使用できますが、構文は異なります:

lazy = db(db.person.name=='Jonathan').nested_select(db.person.id)
db(db.thing.id==1).update(owner_id = lazy)

この場合 lazy は "Jonathan" というpersonの id を取得するためのネスト式です。この2行の結果が1つのSQLクエリとして実行されます。

sum、avg、min、max そして len

前回は、カウント演算子をレコードのカウントに使用しました。同様にサム(sum)演算子を、レコードのグループから特定のフィールドの値を足す(sum)ことに使用することができます。カウントの場合と同様に、サムの結果は格納オブジェクトから取り出すことができます:

>>> sum = db.log.severity.sum()
>>> print db().select(sum).first()[sum]
6

同様に avg、min、max で、選択されたレコードの平均値、最小値、最大値を取り出せます。例えば:

>>> max = db.log.severity.max()
>>> print db().select(max).first()[max]
3

.len()は文字、テキスト、またはブーリアン型のフィールドの長さを計算します。

式を組み合わせてより複雑な式を作ることができます。この例では、logテーブルのseverity文字フィールドの長さに、1を加えた結果を合計しています:

>>> sum = (db.log.severity.len()+1).sum()
>>> print db().select(sum).first()[sum]

サブストリング

サブストリングの値を参照した式を作成することができます。例えば、最初の3文字の名前が同じ物をグループ化でき、各グループから1つだけ選択します:

db(db.thing).select(distinct = db.thing.name[:3])

coalesce と coalesce_zero によるデフォルト値

データベースから値を取得したいが、NULLの時はデフォルトの値が必要な場合があります。SQLではこの目的のために COALESCE が提供されています。web2pyでは等価の coalesce メソッドを提供します。

>>> db.define_table('sysuser',Field('username'),Field('fullname'))
>>> db.sysuser.insert(username='max',fullname='Max Power')
>>> db.sysuser.insert(username='tim',fullname=None)
print db(db.sysuser).select(db.sysuser.fullname.coalesce(db.sysuser.username))
"COALESCE(sysuser.fullname,sysuser.username)"
Max Power
tim

数学的な式を計算したいが、本来ゼロであるべきフィールドにNoneがセットされている場合もあります。coalesce_zero はクエリでNoneのデフォルト値にゼロをセットする手助けをします:

>>> db.define_table('sysuser',Field('username'),Field('points'))
>>> db.sysuser.insert(username='max',points=10)
>>> db.sysuser.insert(username='tim',points=None)
>>> print db(db.sysuser).select(db.sysuser.points.coalesce_zero().sum())
"SUM(COALESCE(sysuser.points,0))"
10

生SQLの生成

SQLは生成したいが実行したくないことがあります。web2pyでこれを行うのは簡単です。なぜならデータベースのIOを実行する全てのコマンドは、単純に実行しようとしたSQLを実行せずに返す、同等のコマンドを持つからです。これらのコマンドは、機能するものと同じ名前と構文を持ちますが、アンダースコアで始まります:

サンプルは _insert

>>> print db.person._insert(name='Alex')
INSERT INTO person(name) VALUES ('Alex');

サンプルは _count

>>> print db(db.person.name=='Alex')._count()
SELECT count(*) FROM person WHERE person.name='Alex';

サンプルは _select

>>> print db(db.person.name=='Alex')._select()
SELECT person.id, person.name FROM person WHERE person.name='Alex';

サンプルは _delete

>>> print db(db.person.name=='Alex')._delete()
DELETE FROM person WHERE person.name='Alex';

最後に、サンプルは _update

>>> print db(db.person.name=='Alex')._update()
UPDATE person SET  WHERE person.name='Alex';

さらに、db._lastsql を用いて、直近のSQLコードを返すことができます。これは、executesqlを用いて手動で実行されたSQLでも、DALによって生成されたSQLでも可能です。

データのエクスポートとインポート

CSV(一度に1つのテーブル)

DALのRowsオブジェクトが文字列に変換される時、自動的にCSV形式にシリアライズされます:

>>> rows = db(db.person.id==db.thing.owner_id).select()
>>> print rows
person.id,person.name,thing.id,thing.name,thing.owner_id
1,Alex,1,Boat,1
1,Alex,2,Chair,1
2,Bob,3,Shoes,2

単一のテーブルをCSV形式にシリアライズして、"test.csv" ファイルに格納することができます:

>>> open('test.csv', 'wb').write(str(db(db.person.id).select()))

これは以下と等価です

>>> rows = db(db.person.id).select()
>>> rows.export_to_csv_file(open('test.csv', 'wb'))

そして、次のようにして簡単にそれを読み取ることができます:

>>> db.person.import_from_csv_file(open('test.csv', 'r'))

インポートする時に、web2pyはCSVのヘッダにあるフィールド名を探します。この例では、"person.name" と "person.id" という2つのカラムを見つけます。"person" という接頭辞と、"id" というフィールドは無視されます。そして全てのレコードは追加され、新しいIDが割り当てられます。これら両方の操作はappadminのWebインターフェースを介して行うことができます。

CSV(全てのテーブルを一度に)

web2pyでは、次の2つのコマンドでデータベース全体をバックアップ/復元することができます:

エクスポートするには:

>>> db.export_to_csv_file(open('somefile.csv', 'wb'))

インポートするには:

>>> db.import_from_csv_file(open('somefile.csv', 'rb'))

このメカニズムは、インポートしたデータベースがエクスポートするデータベースと異なるタイプのものでも使用することができます。データは "somefile.csv" にCSVファイルとして格納されます。このファイルでは、各テーブルは、テーブル名を示す一つの行と、フィールド名を持つもう一つの行から始まります:

TABLE tablename
field1, field2, field3, ...

2つのテーブルは \r\n\r\n で区切られます。ファイルは次の行で終わります。

END

このファイルには、アップロードファイルがデータベースに格納されていない限り含まれません。どのような場合でも、"uploads" フォルダを個別に圧縮することは十分に簡単です。

インポートする時、新規のレコードはデータベースが空でない場合に、データベースに追加されます。一般に新しくインポートしたレコードは、元の(保存した)レコードと同じレコードidを持つことはありません。しかしweb2pyは参照も復元するので、idの値が変化しても参照が機能しなくなることはありません。

もしテーブルに "uuid" と呼ばれるフィールドが含まれる場合、そのフィールドは重複を識別するために使用されます。また、インポートしたレコードが既存のレコードと同じ "uuid" を持つ場合、既存のレコードは更新されます。

CSVとリモート・データベースの同期

次のモデルを考えてください:

db = DAL('sqlite:memory:')
db.define_table('person',
    Field('name'),
    format='%(name)s')
db.define_table('thing',
    Field('owner_id', 'reference person'),
    Field('name'),
    format='%(name)s')

if not db(db.person).count():
    id = db.person.insert(name="Massimo")
    db.thing.insert(owner_id=id, name="Chair")

各レコードは、IDによって識別され、そのIDによって参照されます。別々にインストールしたweb2pyによって、利用されるデータベースの2つのコピーを持っているなら、IDは各データベースにおいてのみユニークで、データベース全体ではユニークではありません。これは、異なるデータベースからレコードをマージする時に問題になります。

複数のデータベース全体でレコードを一意に識別できるようにするには、レコードを次のようにする必要があります:

• 一意のID(UUID)を持たせる
• event_timeを持たせる(複数のコピーがある場合、より最近のものを判別するために)
• idの代わりにUUIDで参照する

これはweb2pyを変更することなく実現できます。以下、どのようにするかを示します:

上記のモデルを次のように変更します:

db.define_table('person',
    Field('uuid', length=64, default=lambda:str(uuid.uuid4())),
    Field('modified_on', 'datetime', default=request.now),
    Field('name'),
    format='%(name)s')

db.define_table('thing',
    Field('uuid', length=64, default=lambda:str(uuid.uuid4())),
    Field('modified_on', 'datetime', default=request.now),
    Field('owner_id', length=64),
    Field('name'),
    format='%(name)s')

db.thing.owner_id.requires = IS_IN_DB(db,'person.uuid','%(name)s')

if not db(db.person.id).count():
    id = uuid.uuid4()
    db.person.insert(name="Massimo", uuid=id)
    db.thing.insert(owner_id=id, name="Chair")

上記のテーブル定義では、2つのuuidフィールドのデフォルト値が(文字に変換された)UUIDを返すラムダ関数によってセットされています。ラムダ関数はそれぞれのレコードが挿入される際に呼び出され、複数のレコードが一つのトランザクションで挿入された場合でも、ユニークなUUIDを取得するようにします。

データベースをエクスポートするコントローラの関数を作成します:

def export():
    s = StringIO.StringIO()
    db.export_to_csv_file(s)
    response.headers['Content-Type'] = 'text/csv'
    return s.getvalue()

他のデータベースが保存したコピーをインポートし、レコードを同期するコントローラの関数を作成します:

def import_and_sync():
    form = FORM(INPUT(_type='file', _name='data'), INPUT(_type='submit'))
    if form.process().accepted:
        db.import_from_csv_file(form.vars.data.file,unique=False)
        # for every table
        for table in db.tables:
            # for every uuid, delete all but the latest
            items = db(db[table]).select(db[table].id,
                       db[table].uuid,
                       orderby=db[table].modified_on,
                       groupby=db[table].uuid)
            for item in items:
                db((db[table].uuid==item.uuid)&                   (db[table].id!=item.id)).delete()
    return dict(form=form)

オプションですがuuidよる検索を早くするために、手動でindexを作成するべきです。

別の方法として、XML-RPCを用いてファイルをエクスポート/インポートすることができます。

レコードがアップロードしたファイルを参照する場合、uploadsフォルダの中身もまたエクスポート/インポートする必要があります。ただし、ファイルはUUIDで既にラベル付けされているので、名前の衝突と参照を心配する必要はありません。

HTML/XMLの(一度に一つのテーブル)

DALのRowsオブジェクトはまた、(ヘルパのように)自身をXML/HTMLへとシリアライズする xml メソッドを持ちます:

>>> rows = db(db.person.id > 0).select()
>>> print rows.xml()
<table>
  <thead>
    <tr>
      <th>person.id</th>
      <th>person.name</th>
      <th>thing.id</th>
      <th>thing.name</th>
      <th>thing.owner_id</th>
    </tr>
  </thead>
  <tbody>
    <tr class="even">
      <td>1</td>
      <td>Alex</td>
      <td>1</td>
      <td>Boat</td>
      <td>1</td>
    </tr>
    ...
  </tbody>
</table>