Chapter 6: データベース抽象化レイヤ

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

DAL

依存関係

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

database drivers

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

DAL
SQLite
MySQL
PostgresSQL
Oracle
MSSQL
FireBird
DB2
Informix
Sybase
Teradata
MongoDB
CouchDB
SAPDB
Cubrid

databasedrivers (source)
SQLitesqlite3 or pysqlite2 or zxJDBC [zxjdbc] (on Jython)
PostgreSQLpsycopg2 [psycopg2] or pg8000 [pg8000] or zxJDBC [zxjdbc] (on Jython)
MySQLpymysql [pymysql] or MySQLdb [mysqldb]
Oraclecx_Oracle [cxoracle]
MSSQLpyodbc [pyodbc]
FireBirdkinterbasdb [kinterbasdb] or fdb or pyodbc
DB2pyodbc [pyodbc]
Informixinformixdb [informixdb]
Ingresingresdbi [ingresdbi]
Cubridcubriddb [cubridb] [cubridb]
SybaseSybase [Sybase]
Teradatapyodbc [Teradata]
SAPDBsapdb [SAPDB]
MongoDBpymongo [pymongo]
IMAPimaplib [IMAP]

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

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

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

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

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

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

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

insert
truncate
drop
import_from_csv_file
count

.insert.truncate.drop.import_from_csv_file .insert, .truncate, .drop, and .import_from_csv_file.

Field

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

Rows

DAL Rows

Row
はデータベースのselect文よって返されるオブジェクトです。Row 行のリストとして考えることができます:

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

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

1
2
for row in rows:
    print row.myfield
Query

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

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

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

1
2
3
4
myset = db(myquery)
rows = myset.select()
myset.update(myfield='somevalue')
myset.delete()
Expression

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

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

接続文字列

connection strings

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

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

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

SQLitesqlite://storage.db
MySQLmysql://username:password@localhost/test
PostgreSQLpostgres://username:password@localhost/test
MSSQLmssql://username:password@localhost/test
FireBirdfirebird://username:password@localhost/test
Oracleoracle://username/password@test
DB2db2://username:password@test
Ingresingres://username:password@localhost/test
Sybasesybase://username:password@localhost/test
Informixinformix://username:password@test
Teradatateradata://DSN=dsn;UID=user;PWD=pass;DATABASE=test
Cubridcubrid://username:password@localhost/test
SAPDBsapdb://username:password@localhost/test
IMAPimap://user:password@server:port
MongoDBmongodb://username:password@localhost/test
Google/SQLgoogle:sql://project:instance/database
Google/NoSQLgoogle:datastore

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

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

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

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

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

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

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

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

接続プール

connection pooling

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はそれぞれに接続しようと試みます。その主な目的は、複数のデータベースサーバに対応し、それらの間で負荷を分散させることです。ここでは典型的な使用例を示します:

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

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

予約キーワード

reserved Keywords

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

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

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

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

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

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

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

1
check_reserved=['postgres', 'postgres_nonreserved']

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

PostgreSQLpostgres(_nonreserved)
MySQLmysql
FireBirdfirebird(_nonreserved)
MSSQLmssql
Oracleoracle

DAL, Table, Field

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

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

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

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

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

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

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

_dbname
1
2
>>> print db._dbname
sqlite

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

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

define_table
Field
type
length
default
requires
required
unique
notnull
ondelete
uploadfield
uploadseparate
migrate
sql.log

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

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

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

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

named id field

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

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

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

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

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

レコードの表現

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

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

もしくは

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

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

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

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

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

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

1
2
3
4
5
6
7
8
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で使用されます。与えられた型に対するデフォルトのバリデータの一覧を、以下に示します:
field typedefault field validators
stringIS_LENGTH(length) default length is 512
textIS_LENGTH(65536)
blobNone
booleanNone
integerIS_INT_IN_RANGE(-1e100, 1e100)
doubleIS_FLOAT_IN_RANGE(-1e100, 1e100)
decimal(n,m)IS_DECIMAL_IN_RANGE(-1e100, 1e100)
dateIS_DATE()
timeIS_TIME()
datetimeIS_DATETIME()
passwordNone
uploadNone
reference <table>IS_IN_DB(db,table.field,format)
list:stringNone
list:integerNone
list:reference <table>IS_IN_DB(db,table.field,format,multiple=True)
jsonIS_JSON()
bigintNone
big-idNone
big-referenceNone

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

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

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

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

requires=... は、フォームレベルで強制され、required=True はDAL(挿入)レベルで強制されることに注意してください。一方、notnulluniqueondelete はデータベースレベルで強制されます。それらは時として冗長に見えるかもしれませんが、DALを用いたプログラミングにおいて、その区別を管理することは重要です。
ondelete
  • 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/" フォルダになります。別のパスが設定されている場合、ファイルは別のフォルダにアップロードされます。例えば次のようにすると、
1
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がインストールされている必要があります。uploadfsPyFileSystem を使用する必要があります。
    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または、フィールド値を受け取りフィールド値の代替表現として値を返す関数を指定できます。例:
1
2
3
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

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

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

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

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

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

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

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

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

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

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

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

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

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

1
2
3
4
5
6
>>> 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'>

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

1
2
3
4
5
6
7
8
>>> print db.person.name.type
string
>>> print db.person.name.unique
False
>>> print db.person.name.notnull
False
>>> print db.person.name.length
32

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

1
2
3
4
5
6
>>> 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) のタプルを返します。入力値がバリデータを通った場合、errorNone になります。

マイグレーション

migrations

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

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

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

1
>>> 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のデフォルト値を設定します。例えば:

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

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

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

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

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

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

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

fake_migrate

マイグレーションには一般的に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を有効にすることです:

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

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

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

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

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

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

挿入

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

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

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

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

truncate
1
>>> db.person.truncate()

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

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

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

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

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

bulk_insert

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

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

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

コミットロールバック

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

commit
1
>>> db.commit()

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

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

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

rollback
1
>>> db.rollback()

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

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

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

1
2
3
4
5
6
7
8
9
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文を明示的に発行することを可能にします。

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

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

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

1
[{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 に、任意のラベルを使用できます。

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

_lastsql

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

_lastdb
1
2
3
>>> rows = db().select(db.person.ALL)
>>> print db._lastsql
SELECT person.id, person.name FROM person;
web2pyは "*" 演算子を使ったクエリを生成することはありません。web2pyでは常に、明示的にフィールドを選択します。

drop

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

drop
1
>>> db.person.drop()

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

インデックス

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

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

1
2
3
>>> 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') のように、明示的に含む必要があります。ここで...は、自動インクリメントした整数フィールドの名前です。

keyed table

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

1
2
3
4
5
6
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フィールドを持つデータベースのビューを作成することを、お勧めします。

分散トランザクション

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

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

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

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

1
DAL.distributed_transaction_commit(db_a, db_b)

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

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

いろいろなアップロード

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

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

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

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

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

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

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

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

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

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

1
2
3
4
5
6
>>> 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つのレコードを挿入してみます:

1
2
3
4
5
6
7
>>> 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 変数として利用することができます:

Table
1
>>> person = db.person

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

Field
1
>>> name = person.name

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

Query
1
>>> q = name=='Alex'

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

Set
1
>>> s = db(q)

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

select

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

Rows
select

1
>>> rows = s.select()
Row

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

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

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

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

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

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

1
2
3
4
5
>>> for row in db().select(db.person.id, db.person.name):
        print row.name
Alex
Bob
Carl

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

1
2
3
4
5
>>> for row in db().select(db.person.ALL):
        print row.name
Alex
Bob
Carl

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

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

1
2
3
4
5
>>> 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 shortcuts

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

1
myrecord = db.mytable[id]

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

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

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

1
del db.mytable[id]

これは以下と等価です。

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

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

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

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

これは以下と等価です。

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

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

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

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

これは以下と等価です。

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

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

Row のフェッチ

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

1
2
3
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

recursive selects

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

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

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

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

これは次と等価です。

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

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

_id

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

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

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

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

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

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

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

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

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

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

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

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

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

これは以下と等価です:

1
2
3
{{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オブジェクトに渡される汎用的なヘルパ属性。

次に例を示します:

1
2
3
4
5
6
7
{{extend 'layout.html'}}
<h1>Records</h1>
{{=SQLTABLE(rows,
     headers='fieldname:capitalize',
     truncate=100,
     upload=URL('download'))
}}

SQLFORM.grid
SQLFORM.smartgrid

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

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

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

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

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

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

orderby, groupby, limitby, distinct, having

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

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

orderby
groupby
having

1
2
3
4
5
6
>>> for row in db().select(
        db.person.ALL, orderby=db.person.name):
        print row.name
Alex
Bob
Carl

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

1
2
3
4
5
6
>>> for row in db().select(
        db.person.ALL, orderby=~db.person.name):
        print row.name
Carl
Bob
Alex

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

1
2
3
4
5
6
>>> for row in db().select(
        db.person.ALL, orderby='<random>'):
        print row.name
Carl
Alex
Bob
orderby='<random>' の使用はGoogle NoSQL上ではサポートされません。しかし同じ状況や、同様にビルトインが不十分な他の多くの場合、インポートを使うことができます:
1
2
import random
rows=db(...).select().sort(lambda row: random.random())

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

1
2
3
4
5
6
>>> 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では利用できません):

1
2
3
4
5
6
7
>>> 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

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

以下に例を示します:

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

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

1
2
3
4
5
>>> for row in db().select(db.person.name,distinct=db.person.name):
        print row.name
Alex
Bob
Carl

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

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

論理演算子

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

and
or
not

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

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

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

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

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

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

1
2
3
4
>>> 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

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

count
isempty

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

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

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

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

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

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

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

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

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

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

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

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

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

1
2
3
4
5
>>> 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文を含むこともできます。例えば:

1
2
3
4
5
6
7
>>> 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

update_record

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

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

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

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

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

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

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

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

辞書からの挿入や更新

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

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

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

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

_id

1
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".

firstlast

first
last

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

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

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

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

as_dictas_list

as_list
as_dict

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

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

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

1
2
3
>>> 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セットのレコードを結合することができます:

1
2
3
4
5
6
7
>>> rows3 = rows1 & rows2
>>> print rows3
name
Max
Tim
John
Tim

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

1
2
3
4
5
6
>>> rows3 = rows1 | rows2
>>> print rows3
name
Max
Tim
John

find, exclude, sort

find
exclude
sort

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

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

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

その使用例です:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
>>> 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

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

1
2
3
4
5
6
7
8
>>> 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

update_or_insert

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

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

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

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

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

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

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

1
2
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

validate_and_insert
validate_and_update

この関数は、

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

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

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

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

同様に、

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

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

1
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なインターフェース(10章を参照)を構築する場合や、SQLFORM.gridSQLFORM.smartgrid の内部で使用されています。

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

計算されたフィールド

compute

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

1
2
3
4
5
6
7
8
>>> 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の入力テキストを、リクエスト毎の加工を避けるために格納する
  • 検索用に、フィールドの正規化した値を計算し、検索時に使用する

仮想フィールド

virtual fields

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

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

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

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

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

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

1
2
>>> 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

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

1
>>> 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のフィールドはテーブルを定義する際に、合わせて定義しておくことができます:

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

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

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

1
2
3
4
>>> 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 属性に追加することによって、この仮想フィールドにリンクされます。

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

1
2
3
4
5
6
7
8
9
>>> 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)の結果に対しても作用することができます

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> 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_priceself.order_item.quantity の両方にアクセスしています。仮想フィールドはrowsオブジェクトの setvirtualfields メソッドを用いてテーブルのrowsに付け加えられます。このメソッドは任意の数の名前付き引数を取ります。そして次のように、複数のクラスで定義された複数の仮想フィールドを設定し、それらを複数のテーブルに付け加えることができます:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
>>> 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)することが可能です。そのためにやることは、関数を返すようにし、その関数を呼び出すことによってアクセスすることです:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> 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()

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

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

1対多のリレーション

one to many

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

1
2
3
4
5
6
7
>>> 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つの等価な方法で指定することができます:

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

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

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

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

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

1
2
3
4
5
6
>>> 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することができます:

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

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

referencing
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
>>> 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
inner join
join

1
2
3
4
5
6
>>> 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からのフィールド値を取り出すときに、テーブルを指定する必要があります。つまり以前は次のように指定しました:

1
row.name

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

1
row.person.name

もしくは:

1
row.thing.name

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

1
2
3
4
5
6
>>> 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
left outer join
outer join

1
2
3
4
5
6
7
8
9
>>> 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

ここで:

1
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毎にそれらをグループ化して、グループ化している最中にカウントします:

grouping
1
2
3
4
5
6
>>> 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 と指定した場合、異なる値だけをカウントします。

多対多

many-to-many

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

以下のように行います:

1
2
3
4
5
6
7
>>> 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)関係は、次のように書き換えることができます:

1
2
3
>>> 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がボートを一緒に持っているという、新しいリレーションを加えることができます:

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

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

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

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

1
2
3
4
5
6
>>> for row in persons_and_things.select():
        print row.person.name, row.thing.name
Alex Boat
Alex Chair
Bob Shoes
Curt Boat

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

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

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

1
2
3
4
>>> 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

list:string
list:integer
list:reference
contains
multiple
tags

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

1
2
3
list:string
list:integer
list:reference <table>

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

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

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

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

1
2
3
4
5
6
7
8
9
>>> 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 演算子は、標準の stringtext フィールドでも機能し、LIKE '%value%' にマッピングされます。

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
>>> 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 フィールドがデフォルト値を持っている点に注意してください。

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

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

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

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

その他の演算子

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

date
datetime
time

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> 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
startswith
regexp
contains
upper
lower

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

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

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

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

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

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

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

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

1
2
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でしかサポートされていません。

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

upper
lower

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

yearmonthdayhourminutesseconds

hour
minutes
seconds
day
month
year

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

1
2
3
4
5
>>> 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を返します:

belongs
1
2
3
4
>>> 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つだけ、明示的にセットを定義するものを選択する必要があります。

nested select
1
2
3
4
5
6
>>> 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文が要求され、検索するフィールが他テーブルを参照している場合、クエリーを引数として使用できます。例えば:

1
2
3
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 を使用する必要はありません。

nested_select

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

1
2
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クエリとして実行されます。

sumavgminmax そして len

sum
avg
min
max

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

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

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

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

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

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

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

サブストリング

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

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

coalescecoalesce_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の生成

raw SQL

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

サンプルは _insert

_insert

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

サンプルは _count

_count

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

サンプルは _select

_select

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

サンプルは _delete

_delete

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

最後に、サンプルは _update

_update

1
2
>>> print db(db.person.name=='Alex')._update()
UPDATE person SET  WHERE person.name='Alex';
さらに、db._lastsql を用いて、直近のSQLコードを返すことができます。これは、executesqlを用いて手動で実行されたSQLでも、DALによって生成されたSQLでも可能です。

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

export
import

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

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

csv
1
2
3
4
5
6
>>> 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" ファイルに格納することができます:

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

これは以下と等価です

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

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

1
>>> 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つのコマンドでデータベース全体をバックアップ/復元することができます:

エクスポートするには:

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

インポートするには:

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

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

1
2
TABLE tablename
field1, field2, field3, ...

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

1
END

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

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

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

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
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を変更することなく実現できます。以下、どのようにするかを示します:

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
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を取得するようにします。

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

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
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

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

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

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

Rows objects

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

HTML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
>>> 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>
Rows custom tags

DALのRowsを、カスタムタグを持った他のXMLフォーマットへとシリアライズしたい場合は、普遍的なタグヘルパや*表記を使用して簡単に行うことができます:

XML
1
2
3
4
5
6
7
8
9
>>> rows = db(db.person.id > 0).select()
>>> print TAG.result(*[TAG.row(*[TAG.field(r[f], _name=f)           for f in db.person.fields]) for r in rows])
<result>
  <row>
    <field name="id">1</field>
    <field name="name">Alex</field>
  </row>
  ...
</result>

データ表現

export_to_csv_file

export_to_csv_file 関数はキーワード引数 represent を持ちます。Trueの場合、データのエクスポート中に、生のデータの代わりに、カラムの represent 関数を用います。

colnames

この関数はまた、エクスポートしたいカラムの名前のリストを保持するキーワード引数 colnames を持ちます。デフォルトでは全てのカラムになります。

export_to_csv_fileimport_from_csv_file の両方とも、CSVの構文解析機に保存/読み込み先のファイルのフォーマットを知らせる次のキーワード引数を持ちます:

  • delimiter: 値の区切り文字の指定(デフォルトは',')
  • quotechar: 文字列値を引用符で囲むために使用する文字(デフォルトはダブルクォート)
  • quoting: 引用符の体系(デフォルトは csv.QUOTE_MINIMAL)

ここでは、いくつか使用例を示します:

1
2
3
4
5
6
>>> import csv
>>> rows = db(query).select()
>>> rows.export_to_csv_file(open('/tmp/test.txt', 'w'),
        delimiter='|',
        quotechar='"',
        quoting=csv.QUOTE_NONNUMERIC)

これは以下のようなレンダリングになります。

1
"hello"|35|"this is the text description"|"2013-03-03"

より詳細な情報は公式のPythonドキュメントを参照してください。[quoteall]

Selectのキャッシュ

selectメソッドではcache引数を取ります。これはデフォルトではNoneです。キャッシュの利用の際は、ここにタプルを設定する必要があります。このタプルの最初の要素はキャッシュモデルで(cache.ram、chace.diskなど)、第2の要素は秒単位の有効期限です。

次の例では、前に定義したdb.logテーブルに対するselectをキャッシュするコントローラを設定しています。実際のselectでは60秒間隔より頻繁に、バックエンドのデータベースからデータを取り出すことはなく、cache.ramに結果を格納します。このコントローラへの次の呼び出しが、最終のデータベースIOから60秒以内に発生する場合、cache.ramから前回のデータが単純に取り出されます。

cache select
1
2
3
def cache_db_select():
    logs = db().select(db.log.ALL, cache=(cache.ram, 60))
    return dict(logs=logs)
cacheable

select メソッドはオプションで cacheable 引数をとり、通常は False がセットされています。cacheable=True の場合は結果の Rows がシリアライズ化されますが、update_recorddelete_record メソッドを使用することができません。

これらのメソッドが不必要な場合、cacheable引数を設定するだけでselect文を高速化できます:

1
rows = db(query).select(cacheable=True)

cache 引数がセットされているが、cacheable=False (デフォルト)の場合、実際のRowsオブジェクトはキャッシュされず、データベースの結果だけがキャッシュされます。cache 引数が cacheable=True と共に使用された場合は、全てのRowsオブジェクトがキャッシュされるため、次回呼び出し時の処理が早くなります:

1
rows = db(query).select(cache=(cache.ram,3600),cacheable=True)

自己参照と別名

self reference
alias

自分自身を参照するフィールドを持つテーブルを定義することが可能です。以下に例を示します:

reference table
1
2
3
4
db.define_table('person',
    Field('name'),
    Field('father_id', 'reference person'),
    Field('mother_id', 'reference person'))

テーブルオブジェクトをフィールドタイプに使用する代替表記は、定義される前の db.person 変数が使用されているため、失敗することに注意してください:

1
2
3
4
db.define_table('person',
    Field('name'),
    Field('father_id', db.person), # wrong!
    Field('mother_id', db.person)) # wrong!

実際、db.tablename"reference tablename" は同じフィールドの型になりますが、後者のみが自己参照に使用できます。

with_alias

テーブルが自分自身を参照する場合、SQLの "AS" キーワードの使用なしに、JOINを実行して、personとその親(parents)を選択することは不可能です。これはweb2pyにおいては with_alias を用いて実現されます。以下がその例です:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
>>> Father = db.person.with_alias('father')
>>> Mother = db.person.with_alias('mother')
>>> db.person.insert(name='Massimo')
1
>>> db.person.insert(name='Claudia')
2
>>> db.person.insert(name='Marco', father_id=1, mother_id=2)
3
>>> rows = db().select(db.person.name, Father.name, Mother.name,
      left=(Father.on(Father.id==db.person.father_id),
            Mother.on(Mother.id==db.person.mother_id)))
>>> for row in rows:
        print row.person.name, row.father.name, row.mother.name
Massimo None None
Claudia None None
Marco Massimo Claudia

以下のものを区別して選択していることに注意してください:

  • "father_id": "person" テーブルにおいて使用されるフィールド名
  • "father": 上記のフィールドによって参照されるテーブルのために使用する別名。これはデータベースとやり取りされます。
  • "Father": その別名を参照するためのweb2pyによって使用される変数

僅かな違いなので、それら3つに同じ名前をつけても間違いではありません:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
db.define_table('person',
    Field('name'),
    Field('father', 'reference person'),
    Field('mother', 'reference person'))
>>> father = db.person.with_alias('father')
>>> mother = db.person.with_alias('mother')
>>> db.person.insert(name='Massimo')
1
>>> db.person.insert(name='Claudia')
2
>>> db.person.insert(name='Marco', father=1, mother=2)
3
>>> rows = db().select(db.person.name, father.name, mother.name,
      left=(father.on(father.id==db.person.father),
            mother.on(mother.id==db.person.mother)))
>>> for row in rows:
        print row.person.name, row.father.name, row.mother.name
Massimo None None
Claudia None None
Marco Massimo Claudia

しかし、正しいクエリを構築するには、この区別を明確にすることが重要です。

高度な機能

テーブル継承

inheritance

他のテーブルの全てのフィールドを含んだテーブルを、作成することが可能です。これは、他のテーブルを define_table に置くだけで十分です。例えば次のようになります。

1
2
db.define_table('person', Field('name'))
db.define_table('doctor', db.person, Field('specialization'))
dummy table

データベースに格納されないダミーテーブルを定義して、他の複数の場所で再利用することも可能です。例えば:

1
2
3
4
5
6
7
signature = db.Table(db, 'signature',
    Field('created_on', 'datetime', default=request.now),
    Field('created_by', db.auth_user, default=auth.user_id),
    Field('updated_on', 'datetime', update=request.now),
    Field('updated_by', db.auth_user, update=auth.user_id))

db.define_table('payment', Field('amount', 'double'), signature)

この例は、標準のweb2py認証が有効になっていることを前提としています。

もし Auth を利用している場合は、このようなテーブルをweb2pyが既に作成済みです:

auth = Auth(db)
db.define_table('payment', Field('amount', 'double'), auth.signature)

テーブル継承を使用する際に、バリデータも継承したい場合は、継承テーブルを定義する前に継承元のバリデータを定義しておく必要があります。

filter_infilter_out

filter_in
filter_out

フィールドの値がデータベースに挿入される前や、データベースから取り出された後に、定義したフィルタを呼びだすことが可能です。

json形式でシリアライズ化したデータを保存したい場合を考えてみてください。このようにすることができます:

1
2
3
4
5
6
7
8
9
>>> from simplejson import loads, dumps
>>> db.define_table('anyobj',Field('name'),Field('data','text'))
>>> db.anyobj.data.filter_in = lambda obj, dumps=dumps: dumps(obj)
>>> db.anyobj.data.filter_out = lambda txt, loads=loads: loads(txt)
>>> myobj = ['hello', 'world', 1, {2: 3}]
>>> id = db.anyobj.insert(name='myobjname', data=myobj)
>>> row = db.anyobj(id)
>>> row.data
['hello', 'world', 1, {2: 3}]

後述する SQLCustomType というフィールドタイプを使う方法もあります。

コールバックの前後

_before_insert
_after_insert
_before_update
_after_update
_before_delete
_after_delete

web2pyは挿入、更新・削除の前後に呼び出されるコールバックを登録するメカニズムがあります。

それぞれのテーブルは6つのコールバックのリストを持ちます:

1
2
3
4
5
6
db.mytable._before_insert
db.mytable._after_insert
db.mytable._before_update
db.mytable._after_update
db.mytable._before_delete
db.mytable._after_delete

対応する関数をこれらのリストの一つに追加することで、コールバック関数を登録できます。

例で説明がするのが一番だと思います。

1
2
3
4
5
6
7
8
>>> db.define_table('person',Field('name'))
>>> def pprint(*args): print args
>>> db.person._before_insert.append(lambda f: pprint(f))
>>> db.person._after_insert.append(lambda f,id: pprint(f,id))
>>> db.person._before_update.append(lambda s,f: pprint(s,f))
>>> db.person._after_update.append(lambda s,f: pprint(s,f))
>>> db.person._before_delete.append(lambda s: pprint(s))
>>> db.person._after_delete.append(lambda s: pprint(s))

ここで f は挿入・更新で渡されるフィールドの辞書型で、id は挿入されたレコードのid、s は更新・削除で使われるセットのオブジェクトです。

1
2
3
4
5
6
7
8
9
>>> db.person.insert(name='John')
({'name': 'John'},)
({'name': 'John'}, 1)
>>> db(db.person.id==1).update(name='Tim')
(<Set (person.id = 1)>, {'name': 'Tim'})
(<Set (person.id = 1)>, {'name': 'Tim'})
>>> db(db.person.id==1).delete()
(<Set (person.id = 1)>,)
(<Set (person.id = 1)>,)

これらのコールバックの戻り値は NoneFalse であるべきです。_before_* 系のコールバックが True の値を返した場合は、実際の挿入・更新・削除処理は実行されません。

update_naive
.

コールバックが同じテーブルや異なるテーブルに対して更新処理を実行するが、自身のコールバックを繰り返し呼び出さないようにする必要がある場合があります。

この場合、update_naive というメソッドがあり、update のように動作するが前後のコールバックを無視します。

レコードのバージョン管理

_enable_record_versioning

web2pyを利用して、レコードが個別に更新された際に、それぞれのレコードの複製を保管させることが可能です。これにはいくつか異なる方法があり、構文を使って一度に全てのテーブルに対して実施することができます:

1
auth.enable_record_versioning(db)

これにはAuthが必要でアクセス制御の章で後述されています。以下のようにそれぞれのテーブルに対して行うこともできます。

次のテーブルで考えてみます:

1
2
3
4
5
db.define_table('stored_item',
    Field('name'),
    Field('quantity','integer'),
    Field('is_active','boolean',
          writable=False,readable=False,default=True))

隠しフィールドであるブーリアンの is_active がありデフォルト値がTrueになっている点に注意してください。

web2pyにテーブルを作成し(同じまたは別のデータベース)更新時に、テーブルのそれぞれのレコードの全てのバージョンを保管するようにさせることができます。

これは次のように実現できます:

1
db.stored_item._enable_record_versioning()

またはより冗長な記述で:

db.stored_item._enable_record_versioning(
    archive_db = db,
    archive_name = 'stored_item_archive',
    current_record = 'current_record',
    is_active = 'is_active')

archive_db=db は、web2pyに stored_item と同じデータベースにテーブルのアーカイブを保管するように指示します。archive_name はアーカイブされるテーブルの名前です。アーカイブテーブルはオリジナルのテーブルである stored_item と同じフィールドを持ちますが、ユニークなフィールドはユニークではなくなり(複数のバージョンを保管するため)、current_record で指定された追加フィールドを持ちます。これは stored_item にある現在のレコードを参照します。

レコードを削除しても、実際には削除はされません。削除されたレコードは stored_item_archive テーブルに複製され(更新時のように)、is_active フィールドにFalseがセットされます。web2pyのバージョン管理を有効にすることで、stored_item テーブルの全てのレコードに対して is_active がFalseのレコードを非表示にするよう custom_filter がセットされます。_enable_record_versioning メソッドの is_active パラメータは、フィールドが削除されたかどうか決定する custom_filter に使われるフィールド名を指定することができます。

custom_filter は、appadmin画面では無視されます。

コモンフィールドとマルチテナント

common fields
multi tenancy

db._common_fields は全てのテーブルに属するフィールドのリストです。このリストにテーブルを含むこともでき、その場合は該当テーブルの全てのフィールドがリストされたとして理解します。例えば、auth を除く全てのテーブルにsignatureを追加したい場合があります。この場合、db.define_tables() の後で、且つ、それ以外のテーブルを定義する前に、以下を挿入します。

db._common_fields.append(auth.signature)

"request_tenant" は特別なフィールドです。このフィールドは(標準での定義が)存在しませんが、作成し、どんな(もしくは全て)のテーブルに追加できます。

db._common_fields.append(Field('request_tenant',
    default=request.env.http_host,writable=False))

db.request_tenant というフィールドを持っているテーブルは、全てのクエリに対する全レコードが、常に自動でフィルタされます:

1
db.table.request_tenant == db.table.request_tenant.default

そしてレコードの挿入のたびに、デフォルトの値がセットされます。上記の例では以下がセットされます。

default = request.env.http_host

つまり、アプリ上の全てのテーブルとクエリを以下でフィルタするという選択をしたことになります。

db.table.request_tenant == request.env.http_host

この簡単なトリックで、アプリケーションを複数のテナントに対応したアプリケーションにすることができます。つまりアプリを、単一インスタンス上の単一データベースで運用しているおり、複数ドメイン(上記の例の場合、ドメイン名は request.env.http_host から取得)でのアクセスがアプリに対してある場合、訪問者が接続したドメイン名によって参照するデータが異なることになります。複数のオンラインストアを、単一アプリとデータベースを使用し異なるドメイン下で運用する場合などを、想像してみてください。

複数のテナントによるフィルタを

ignore_common_filters
で無効にできます。

1
rows = db(query, ignore_common_filters=True).select()

コモンフィルタ

コモンフィルタは上記の複数のテナントという考え方を一般化したものです。同じクエリを繰り返し使用させない簡単な方法を提供します。次のテーブル例を考えてください:

db.define_table('blog_post',
    Field('subject'),
    Field('post_text', 'text'),
    Field('is_public', 'boolean'),
    common_filter = lambda query: db.blog_post.is_public==True
)

このテーブルに対する選択、削除、更新は、公開されたブログ記事だけを含みます。その属性はコントローラで変更できます:

db.blog_post._common_filter = lambda query: db.blog_post.is_public == True

それぞれのブログ記事検索に "db.blog_post.is_public==True" という条件を繰り返し使用する必要がなくなり、非公開ブログ記事の参照不可の設定忘れ、といったセキュリティも強化できます。

もし意図的にコモンフィルタを外したい(例えば、管理者は非公開のブログ記事を参照できる)場合は、フィルタを削除することができます:

db.blog_post._common_filter = None

もしくは、無視するするには次のようにします:

db(query, ignore_common_filters=True).select(...)

カスタムField型 (実験的)

SQLCustomType

filter_infilter_out に加えて、新しい/カスタムフィールド型を定義することが可能です。圧縮されたバイナリデータを含むフィールドの例です:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
from gluon.dal import SQLCustomType
import zlib

compressed = SQLCustomType(
     type ='text',
     native='text',
     encoder =(lambda x: zlib.compress(x or '')),
     decoder = (lambda x: zlib.decompress(x))
)

db.define_table('example', Field('data',type=compressed))

SQLCustomType はフィールド型のファクトリです。その type 引数はweb2pyの標準型の一つでなければなりません。web2pyレベルで、そのフィールド値をどのように扱うべきか指示します。native はデータベースが接続されているかぎり使用できるフィールドの名前です。データベースエンジン特有の型名も許可します。encoder はデータ格納時に適用されるオプションの変換関数で、decoder は逆変換の関数です。

この機能は実験的とされています。現実的に長い間使用されて動作していますが、コードがポータブルでなくなる可能性があります。例として、データベース特有のフィールド型を使用した場合にGoogle App Engine NoSQLで動作しなくなります。

テーブル定義なしでDALを使用

DALは以下のようにすることで、どのようなPythonプログラムからでも使用できます:

1
2
from gluon import DAL, Field
db = DAL('sqlite://storage.sqlite',folder='path/to/app/databases')

つまり、DALとFieldをインポートし、接続、.tableファイル(app/databasesフォルダ)を含むフォルダを指定すればよいです。

データやその属性にアクセスするには、db.define_tables(...) で接続する全てのテーブルを定義する必要があります。

もしデータにだけアクセスし、web2pyテーブル属性は必要がない場合は、.tableファイルにあるメタデータから必要な情報を読み込むようにweb2pyに指示するだけです。テーブルを再定義する必要はないです:

1
2
3
from gluon import DAL, Field
db = DAL('sqlite://storage.sqlite',folder='path/to/app/databases',
         auto_import=True))

これによって再定義せずに db.table に接続できます。

PostGIS、SpatiaLiteとMS Geo (実験的)

PostGIS
StatiaLite
Geo Extensions
geometry
geoPoint
geoLine
geoPolygon

DALはPostGIS(PostgresSQL用)、spatialite(SQLite用)、MSSQLと空間データ型といった地理的APIをサポートします。これはSahanaプロジェクトによってスポンサーされ、Denes Lengyelによって実装された機能です。

DALは形状や地形のフィールドタイプと次のような機能を提供します:

st_asgeojson
st_astext
st_contains
st_distance
st_equals
st_intersects
st_overlaps
st_simplify
st_touches
st_within

st_asgeojson (PostGIS only)
st_astext
st_contains
st_distance
st_equals
st_intersects
st_overlaps
st_simplify (PostGIS only)
st_touches
st_within
st_x
st_y

いくつか例を挙げます:

1
2
3
from gluon.dal import DAL, Field, geoPoint, geoLine, geoPolygon
db = DAL("mssql://user:pass@host:db")
sp = db.define_table('spatial', Field('loc','geometry()'))

以下は点、線、多角形を挿入します:

1
2
3
sp.insert(loc=geoPoint(1,1))
sp.insert(loc=geoLine((100,100),(20,180),(180,180)))
sp.insert(loc=geoPolygon((0,0),(150,0),(150,150),(0,150),(0,0)))

以下の構文は

1
rows = db(sp.id>0).select()

常に地形データをシリアライズ化された文字として返す点に注意してください。st_astext() を使って明示的に行うこともできます:

1
2
3
4
5
print db(sp.id>0).select(sp.id, sp.loc.st_astext())
spatial.id,spatial.loc.STAsText()
1, "POINT (1 2)"
2, "LINESTRING (100 100, 20 180, 180 180)"
3, "POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))"

st_asgeojson() を使ってネイティブな表現を問い合わせることもできます(PostGISのみ):

1
2
3
4
5
print db(sp.id>0).select(sp.id, sp.loc.st_asgeojson().with_alias('loc'))
spatial.id,loc
1, [1, 2]
2, [[100, 100], [20 180], [180, 180]]
3, [[[0, 0], [150, 0], [150, 150], [0, 150], [0, 0]]]

(配列が点を、配列の配列が線を、配列の配列の配列が多角形を表しています)

地形データ関数を利用する例です:

1
2
3
4
5
6
query = sp.loc.st_intersects(geoLine((20,120),(60,160)))
query = sp.loc.st_overlaps(geoPolygon((1,1),(11,1),(11,11),(11,1),(1,1)))
query = sp.loc.st_contains(geoPoint(1,1))
print db(query).select(sp.id,sp.loc)
spatial.id,spatial.loc
3,"POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))"

計算された距離も浮動少数で取得できます:

1
2
3
4
5
6
dist = sp.loc.st_distance(geoPoint(-1,2)).with_alias('dist')
print db(sp.id>0).select(sp.id, dist)
spatial.id, dist
1 2.0
2 140.714249456
3 1.0

異なるdbからデータをコピー

以下のデータベースを使用している場合を考えてください:

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

そして異なる接続文字で、別のデータベースに移動したいとします:

db = DAL('postgres://username:password@localhost/mydb')

切り替える前に、新しいデータベースへデータを移動してメタデータを再構築したいです。新しいデータベースは存在するが空であると想定します。

web2pyはこれを実現するスクリプトを提供します:

cd web2py
python scripts/cpdb.py    -f applications/app/databases    -y 'sqlite://storage.sqlite'    -Y 'postgres://username:password@localhost/mydb'

スクリプト実行後、単にモデルの接続文字を切り替えるだけで、全て動きます。新しいデータも存在しています。

このスクリプトはひとつのアプリケーションから別のアプリケーションへデータを移動できる様々なコマンドラインオプションを提供し、全てのテーブルを移動したり、一部だけ移動したり、テーブルのデータをクリアしたりします。詳しくは次を実行してみてください:

python scripts/cpdb.py -h

新しいDALとアダプタの注意点

データベース抽象化レイヤのソースコードは2010年に全て書き換えられました。後方互換性を保ちながら、よりモジュール化し拡張性しやすくすることができました。ここで主要なロジックについて説明します。

"gluon/dal.py" ファイルは、とりわけ次のクラスを定義します。

ConnectionPool
BaseAdapter extends ConnectionPool
Row
DAL
Reference
Table
Expression
Field
Query
Set
Rows

BaseAdapter を除き、使用方法については前節で説明しました。TableSet オブジェクトがデータベースと通信する必要がある場合、アダプタにSQLを生成したり、関数を呼び出すメッソドを委譲します。

例えば:

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

以下を呼び出します

Table.insert(myfield='myvalue')

これは以下を返すことでアダプタに委譲します:

db._adapter.insert(db.mytable,db.mytable._listify(dict(myfield='myvalue')))

ここで db.mytable._listify は引数の辞書を (field,value) のリストに変換し、adapterinsert メソッドを呼びます。db._adapter は、大体次のようなことをしています:

query = db._adapter._insert(db.mytable,list_of_fields)
db._adapter.execute(query)

最初の行はクエリを作り、2行目で実行しています。

BaseAdapterは、全てのアダプタのインターフェースを定義します。

執筆時点で、"gluon/dal.py" は次のアダプタを含みます:

SQLiteAdapter extends BaseAdapter
JDBCSQLiteAdapter extends SQLiteAdapter
MySQLAdapter extends BaseAdapter
PostgreSQLAdapter extends BaseAdapter
JDBCPostgreSQLAdapter extends PostgreSQLAdapter
OracleAdapter extends BaseAdapter
MSSQLAdapter extends BaseAdapter
MSSQL2Adapter extends MSSQLAdapter
FireBirdAdapter extends BaseAdapter
FireBirdEmbeddedAdapter extends FireBirdAdapter
InformixAdapter extends BaseAdapter
DB2Adapter extends BaseAdapter
IngresAdapter extends BaseAdapter
IngresUnicodeAdapter extends IngresAdapter
GoogleSQLAdapter extends MySQLAdapter
NoSQLAdapter extends BaseAdapter
GoogleDatastoreAdapter extends NoSQLAdapter
CubridAdapter extends MySQLAdapter (experimental)
TeradataAdapter extends DB2Adapter (experimental)
SAPDBAdapter extends BaseAdapter (experimental)
CouchDBAdapter extends NoSQLAdapter (experimental)
IMAPAdapter extends NoSQLAdapter (experimental)
MongoDBAdapter extends NoSQLAdapter (experimental)

これは BaseAdapter をオーバーライドします。

それぞれのアダプタは、おおよそ次のような構造です:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
class MySQLAdapter(BaseAdapter):

    # specify a diver to use
    driver = globals().get('pymysql',None)

    # map web2py types into database types
    types = {
        'boolean': 'CHAR(1)',
        'string': 'VARCHAR(%(length)s)',
        'text': 'LONGTEXT',
        ...
        }

    # connect to the database using driver
    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                credential_decoder=lambda x:x, driver_args={},
                adapter_args={}):
        # parse uri string and store parameters in driver_args
        ...
        # define a connection function
        def connect(driver_args=driver_args):
            return self.driver.connect(**driver_args)
        # place it in the pool
        self.pool_connection(connect)
        # set optional parameters (after connection)
        self.execute('SET FOREIGN_KEY_CHECKS=1;')
        self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")

   # override BaseAdapter methods as needed
   def lastrowid(self,table):
        self.execute('select last_insert_id();')
        return int(self.cursor.fetchone()[0])

様々なアダプタの例を参考にすれば、新しいアダプタを記述するのも簡単です。

db インスタンスが作成されると:

db = DAL('mysql://...')

uri文字列の接頭辞はアダプタを定義しています。マッピングは "gluon/dal.py" で、次のように辞書型で定義されています:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
ADAPTERS = {
    'sqlite': SQLiteAdapter,
    'sqlite:memory': SQLiteAdapter,
    'mysql': MySQLAdapter,
    'postgres': PostgreSQLAdapter,
    'oracle': OracleAdapter,
    'mssql': MSSQLAdapter,
    'mssql2': MSSQL2Adapter,
    'db2': DB2Adapter,
    'teradata': TeradataAdapter,
    'informix': InformixAdapter,
    'firebird': FireBirdAdapter,
    'firebird_embedded': FireBirdAdapter,
    'ingres': IngresAdapter,
    'ingresu': IngresUnicodeAdapter,
    'sapdb': SAPDBAdapter,
    'cubrid': CubridAdapter,
    'jdbc:sqlite': JDBCSQLiteAdapter,
    'jdbc:sqlite:memory': JDBCSQLiteAdapter,
    'jdbc:postgres': JDBCPostgreSQLAdapter,
    'gae': GoogleDatastoreAdapter, # discouraged, for backward compatibility
    'google:datastore': GoogleDatastoreAdapter,
    'google:sql': GoogleSQLAdapter,
    'couchdb': CouchDBAdapter,
    'mongodb': MongoDBAdapter,
    'imap': IMAPAdapter
}

uri文字列はアダプタ自身で、より詳細に解析されます。

どのようなアダプタでも異なるドライバに置き換えることができます:

import MySQLdb as mysqldb
from gluon.dal import MySQLAdapter
MySQLAdapter.driver = mysqldb

つまり mysqldb は.connect()メソッドと共に that module である必要があります。 オプションのドライバ引数やアダプタ引数を指定することもできます:

db =DAL(..., driver_args={}, adapter_args={})

データベースの処方箋

SQLiteは、カラムの削除や置き換えをサポートしていません。これはweb2pyのマイグレーションがある程度まで機能することを意味します。テーブルからフィールドを削除した場合、そのカラムはデータベースには残るが、web2pyからは見えなくなります。もし、もう一度そのカラムを定義した場合は、web2pyは再作成を実行して失敗します。この場合、fake_migrate=True をセットすることでカラムを再度追加しないでメタデータを再構築できます。また同様の理由で、SQLite はカラム型の変更に対応していません。文字フィールドに数字を挿入した場合、それは文字として格納されます。もし後でモデルを変更し型を "string" から "integer" に変更した場合、SQLiteはその数字を文字として保持し続けるため、データを抽出した際に問題になる可能性があります。

MySQL は一つのトランザクションによる、複数のALTER TABLEをサポートしません。これはマイグレーション処理が、複数のコミットに分割されることを意味します。もし何か失敗した場合に、マイグレーションがおかしくなる(web2pyメタデータが実際にテーブル構造と一致しなくなる)可能性があります。これは残念なことですが、その問題を防いだり(一度に一つずつのテーブルをマイグレーション)、事後修正(web2pyモデルをデータベースのテーブル構造に合わせるように戻す、fake_migrate=True をセットしてメタデータが再構築されてから、fake_migrate=False をセットし再度テーブルのマイグレーションを行う)をすることができます。

Google SQL はMySQLと同様の問題に加えて、さらに問題を抱えています。具体的には、web2pyによってマイグレーションされていないテーブルメタデータ自身も、データベースのテーブルに格納する必要があります。これはGoogle App Engineが、読み取り専用のファイルシステムを有しているからです。上記のMySQLの問題と合わせたGoogle:SQLでのweb2pyマイグレーションは、メータデータの破損をもたらす可能性があります。繰り返しなりますが、これは防いだり(一度に一つずつテーブルをマイグレーションし、migrate=Falseをセットすることで、メタデータテーブルに接続されることがなくなる)、事後修正(Google ダッシュボードからデータベースに接続し、web2py_filesystem というテーブルに存在する破損した入力結果を削除する)することができます。

limitby

MSSQL はOFFSETキーワードをサポートしません。このため、データベースはページネーションをすることができません。limitby=(a,b) を実行する場合、web2pyは最初に b の行を取得し a の行を破棄します。これは他のデータベースエンジンに比べてオーバーヘッドが大きくなる可能性があります。

Oracle もページネーションをサポートしていません。また、OFFSETやLIMITキーワードもサポートしていません。web2pyはページネーションを実現しますが、db(...).select(limitby=(a,b)) を複雑な3方向のネストしたselect(Oracleの公式ドキュメントで推奨されているように)に変換しています。簡単なselectの場合は、これで動作しますが、結合などをする複雑なselectの場合はおかしくなる可能性があります。

MSSQL はONDELETE CASCADEを持つテーブルで循環参照をする際に問題があります。これはMSSQLのバグで、全ての参照フィールドのondelete属性に "NO ACTION" をセットすることで回避できます。また、テーブルを定義する前に全てを一度に設定することもできます:

1
2
3
4
db = DAL('mssql://....')
for key in ['reference','reference FK']:
    db._adapter.types[key]=db._adapter.types[key].replace(
        '%(on_delete_action)s','NO ACTION')

MSSQL はDISTINCTキーワードに渡す引数の問題もあります。以下は動作しますが、

db(query).select(distinct=True)

次は動作しません

db(query).select(distinct=db.mytable.myfield)

Google NoSQL (Datastore) は結合、左外部結合、集計、式の使用、複数のテーブルの使用、like演算、"text" フィールドの検索が許可されていません。トランザクションも制限されているためweb2pyから自動で提供されません(オンラインのGoogle App Engine ドキュメントで説明されている、run_in_transaction というGoogle APIを使用する必要があります)。また、1回のクエリで1度に取得できるレコード数も制限(執筆時点で1000レコード)されています。Google datastoreのレコードIDは整数型ですが連番ではありません。SQLでは "list:string" が "text" 型にマッピングされますが、Google Datastoreでは、ListStringProperty にマッピングされます。同様に、"list:integer" と "list:reference" は "ListProperty" にマッピングされます。これにより、SQLデータベースよりGoogle NoSQLの方が、これらのフィールド型の中身を検索する効率が良いです。

第3版 - 翻訳: 細田謙二 レビュー: Omi Chiba
第4版 - 翻訳: Omi Chiba レビュー: Hitoshi Kato
第5版 - 翻訳: Omi Chiba レビュー: Hitoshi Kato
 top