3.5. Expression

Expressionは式のクラスです。メソッドのパラメータ式などで利用します。

またメソッドには、Queryインスタンス(条件式)を生成するもの、データ操作を行うものがあります。 これらのメソッドは主に、Expressionクラスの継承クラスである Field を使って利用します。

参考: Expressions

Expressionクラス — 式に関するクラス

— 条件式 —

like()

あいまい検索

startswith()

前方一致検索

endswith()

後方一致検索

contains()

部分一致検索

belongs()

IN演算子

regexp()

正規表現による検索

— データ操作 —

sum()

合計値計算

avg()

平均値計算

max()

最大値の取得

min()

最小値の取得

len()

長さの取得

abs()

絶対値取得

coalesce()

NULL値の変換

coalesce_zero()

NULL値のゼロ変換

upper()

大文字変換

lower()

小文字変換

year()

日付時間の要素抜き出し

month()

day()

hour()

minutes()

seconds()

3.5.1. インスタンス化

Field インスタンスを使って式を作成すると、Expressionインスタンスになります [1]

【 Expressionインスタンスの生成 】

  • 生成・・・・・・ Fieldインスタンスを使用して式を書きます。
    >>> e = db.item.unit_cost * 1.1
    

update() メソッドのパラメータとしてExpressionインスタンスを利用するサンプルです。

>>> e = db.item.unit_cost * 1.1                 # unit_cost を1.1倍にする式
>>> db(db.item.id==1).update(unit_cost = e)     # Expressionインスタンスをフィールド値として指定
1

Note

サンプルでは数値演算を行っていますが、同じように 文字列 演算を指定した Expressionインスタンスを利用しても上手くは 動きません。これは定義した式がSQL文に変換され、データベース更新をかけるからです。 例えば、Pythonでは文字列結合に + 演算子を使用しますが、データベースによっては + 演算子では文字列結合ができません。

3.5.2. メソッド(条件式)

条件式として利用できるメソッドがあります。これらのメソッドはExpressionのインスタンスと、継承クラスである Field のインスタンスで使用可能です。

参考: like, startswith, contains, upper, lowerlike, startswith,contains,upper, lower (日本語)

メソッド説明用のサンプルでは次のテーブルを利用します。

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

db.define_table('dog',
    Field('owner_id', db.person),
    Field('name'))
Expression.like(value) → Queryインスタンス

フィールド値の文字列をあいまい検索します。 Field インスタンスからlikeメソッドを呼びます。

>>> print db(db.person.name.like('PA%')).select()
person.id,person.name
3,Pamadapa
value
検索文字列を設定します。検索文字列中のパーセント記号(%)はワイルドカードとして機能します。
戻り値
Queryインスタンスを返します。
Expression.startswith(value) → Queryインスタンス

like() のショットカットです。先頭文字列を検索します(前方一致検索)。

>>> print db(db.person.name.startswith('PA')).select()
person.id,person.name
3,Pamadapa

like を使用したサンプルと同等の構文

>>> print db(db.person.name.like('PA%')).select()
value
検索文字列を設定します。
戻り値
Queryインスタンスを返します。
Expression.endswith(value) → Queryインスタンス

like() のショットカットです。後方文字列を検索します(後方一致検索)。

>>> print db(db.person.name.endswith('SA')).select()
person.id,person.name
5,Sasodusa

like を使用したサンプルと同等の構文

>>> print db(db.person.name.like('%SA')).select()
value
検索文字列を設定します。
戻り値
Queryインスタンスを返します。
Expression.contains(value[, all]) → Queryインスタンス

like() のショットカットです。途中に含まれる文字列を検索します(部分一致検索)。

>>> print db(db.person.name.contains('PA')).select()
person.id,person.name
1,Sapopadu
3,Pamadapa
7,Ducepapa

like を使用したサンプルと同等の構文

>>> print db(db.person.name.like('%PA%')).select()
value
検索文字列を設定します。検索文字列はリスト値もしくはタプル値でも指定できます。
all

valueがリスト値もしくはタプル値の場合、有効なパラメータです。ANDもしくはORを有効にします。 デフォルトは False です。Falseの場合、OR条件になります。Trueでは、AND条件になります。

>>> print db(db.person.name.contains(['OC','CE'])).select()
person.id,person.name
1,Socopato
4,Cecemosa
5,Cecopadu
6,Cedumoco
7,Ceduduco

>>> print db(db.person.name.contains(['OC','CE'],all=True)).select()
person.id,person.name
6,Cedumoco
戻り値
Queryインスタンスを返します。
注意点

containsメソッドはリストフィールドに対しては特殊な動きをします。 文字列の部分一致を検索するのではなく、listフィールドに含まれる個々の値の検索を行います。 このサンプルは リストフィールドを使用した多対多 を参照ください。

リストフィールド ・・・ list:string , list:integer , list:reference <table>
Expression.belongs(value) → Queryインスタンス
フィールド値が指定したタプルの値に一致しているものを検索します。SQLのIN演算子と同様の動きをします。
この機能は Google AppEngine(GAE)でもは動作しますが、Datastoreの制約上、valueに設定できる値は30個までです。
>>> print db(db.person.id.belongs((1,3,4))).select()
person.id,person.name
1,Sapopadu
3,Pamadapa
4,Dumatata
value

検索用のタプルもしくはリスト値を設定します。

valueをクエリ式にすることも可能です。この場合は入れ子(ネスト)になります。
クエリ式を設定する場合は select() メソッドにアンダーバ(_)を付けて _select に変更します。
>>> set = db(db.person.name.like('D%')).\
>>>        _select(db.person.name)              # personからnameの先頭文字がDのデータを抜き出す
>>> print db(db.dog.name.belongs(set)).select() # dogのnameと一致するデータを抜き出す
dog.id,dog.owner_id,dog.name
34,9,Dumatata

GAEではクエリ式で指定した場合、エラーになります。代わりにvalueに設定する値を検索後、リスト内包表記などで設定します。

_selectはSQL文を生成します。set変数は次のようになります。

>>> print set
SELECT  person.name FROM person WHERE (person.name LIKE 'D%');

SQL文の生成 も参照ください。

戻り値
Queryインスタンスを返します。
Expression.regexp(value) → Queryインスタンス

正規表現を使用し検索を行います。このメソッドはデータベースが、PostgreSQL及びSQLiteの場合だけ使用可能です。

>>> print db(db.person.name.regexp('^[ABCDE].*')).select()
person.id,person.name
1,Deborah Kludt
9,Blanche Semmler
10,Britt Krugh
12,Elizabeth Shelpman

先頭文字が、ABCDEの何れかの文字で始まる名前のレコードを検索します。

value
正規表現の文字列を設定します。
戻り値
Queryインスタンスを返します。

3.5.3. メソッド(データ操作)

データ操作を行えるメソッドがあります。これらのメソッドはExpressionのインスタンスと、継承クラスである Field の インスタンスで使用可能です。

参考: like, startswith, contains, upper, lowerlike, startswith,contains,upper, lower (日本語)

Expression.sum() → Expressionインスタンス

自分自身を合計します。SQLのSUM関数に変換されます。

>>> age = (db.person.birthday.year() * -1 + datetime.datetime.today().year).sum()
>>> print db().select(age).first()[age]
16

年齢の合計を計算します。age変数の式が変なのは、Expressionクラスでオーバロードされている四則演算子を動作させるためには、Expressionインスタンスもしくは 派生系のFieldインスタンスに対する演算で表現する必要があるからです。

戻り値
Expressionインスタンスを返します。
Expression.avg() → Expressionインスタンス

自分自身の平均を計算します。SQLのAVG関数に変換されます。

>>> age = (db.person.birthday.year() * -1 + datetime.datetime.today().year).avg()
>>> print db().select(age).first()[age]
5

年齢の平均値を取得します。

戻り値
Expressionインスタンスを返します。
Expression.max() → Expressionインスタンス

自分自身の最大値を返します。SQLのMAX関数に変換されます。

>>> age = (db.person.birthday.year() * -1 + datetime.datetime.today().year).max()
>>> print db().select(age).first()[age]
6

年齢の最大値を取得します。

戻り値
Expressionインスタンスを返します。
Expression.min() → Expressionインスタンス

自分自身の最小値を返します。SQLのMIN関数に変換されます。

>>> age = (db.person.birthday.year() * -1 + datetime.datetime.today().year).min()
>>> print db().select(age).first()[age]
5

年齢の最小値を取得します。

戻り値
Expressionインスタンスを返します。
Expression.len() → Expressionインスタンス

自分自身の長さを返します。SQLのLENGTH関数に変換されます。

>>> name = db.person.name.len().max()
>>> print db().select(name).first()[name]
8

名前の長さで最大のものを取得します。

戻り値
Expressionインスタンスを返します。
Expression.abs() → Expressionインスタンス

自分自身の絶対値を返します。SQLのABS関数に変換されます。

>>> age = (db.person.birthday.year() - datetime.datetime.today().year).abs()
>>> print db().select(age)
"ABS((web2py_extract('year',person.birthday) - 2013))"
5
5
6

誕生年から現在の年を引いた絶対値を取得します。

戻り値
Expressionインスタンスを返します。
Expression.coalesce(*others) → Expressionインスタンス

自分自身がNULLの場合、NULL以外のパラメータ値を返します。SQLのCOALESCE関数に変換されます。

>>> print db(db.person.id==db.dog.owner_id).select(db.dog.name.coalesce(db.person.name))
"COALESCE(dog.name,person.name)"
Comoceta
Soducomo
Cosasamo
Pacedada

dogテーブルのnameフィールドがNULLだった場合、personテーブルのnameフィールドを代替表示します。

>>> print db(db.person.id==db.dog.owner_id).select(db.dog.name.coalesce("'----'"))
"COALESCE(dog.name,'----')"
Comoceta
Soducomo
Cosasamo
----

dogテーブルのnameフィールドがNULLだった場合、—- を代替表示します。

others

NULLの時に代替するフィールドや固定値を指定します。文字列を指定する場合、一重引用符(‘)や二重引用符(”)で2重に囲む必要があります。

このパラメータは複数の指定が可能です。複数指定した場合、順番にNULL値かどうかを判定し、最初にNULLでないパラメータを返します。

戻り値
Expressionインスタンスを返します。
Expression.coalesce_zero() → Expressionインスタンス

自分自身がNULLの場合、0を返します。この機能は .coalesce(0) と同等です。

>>> print db(db.person).select(db.person.name, db.person.birthday.day().coa
person.name,"COALESCE(web2py_extract('day',person.birthday),0)"
Pacedada,23
Socepopa,29
Cosotama,11
test,0

birthday.day() がNULLだった場合、0を代替表示します(4行目)。

戻り値
Expressionインスタンスを返します。
Expression.upper() → Expressionインスタンス

式やフィールド値の文字列を大文字に変換します。

>>> print db(db.person).select(db.person.name.upper())
UPPER(person.name)
SAPOPADU
PODATOSO
PAMADAPA
戻り値
Expressionインスタンスを返します。
Expression.lower() → Expressionインスタンス

式やフィールド値の文字列を小文字に変換します。

>>> print db(db.person).select(db.person.name.lower())
LOWER(person.name)
sapopadu
podatoso
pamadapa
戻り値
Expressionインスタンスを返します。
Expression.year() → Expressionインスタンス
Expression.month() → Expressionインスタンス
Expression.day() → Expressionインスタンス
Expression.hour() → Expressionインスタンス
Expression.minutes() → Expressionインスタンス
Expression.seconds() → Expressionインスタンス

日時フィールドを年・月・日・時間・分・秒 の各値に変換します。

>>> print db(db.person).select(db.person.birthday.day())
"web2py_extract('day',person.birthday)"
23
29
11
>>> print db(db.person.birthday.year()==2008).select()
person.id,person.name,person.birthday
1,Deborah Kludt,2008-10-23 00:00:00
2,Margarite Coonradt,2008-07-30 00:00:00
戻り値
Expressionインスタンスを返します。

参考: year, month, day, hour, minutes, secondsyear, month, day, hour, minutes, seconds (日本語)


[1]Pythonのinstance型オブジェクトではありませんが、簡単に説明するために インスタンス という言葉を使用しています。