revision-up-to: | 17812 (1.4) unfinished |
---|
モデルクエリAPI では十分には役に立たない場合、
逆戻りして素のSQLを書くことができます。Djangoでは素のSQLクエリを実行する
のに2つの方法があります:Manager.raw()
を使って、
素のクエリを実行する ことでモデルインスタンスを返すか、あるいは
モデル層全体を避けて カスタムSQLを直接実行する ことができます。
リリースノートを参照してください
マネージャーメソッド raw()
を使って素のSQLクエリを実行し、モデルインスタンス
を返すことができます。:
Manager.
raw
(raw_query, params=None, translations=None)¶このメソッドは素のSQLクエリを受け取りそれを実行し、
RawQuerySet
インスタンスを返します。
この RawQuerySet
インスタンスを通常の
QuerySet の様に列挙してオブジェクトインスタンスを返すことができます。
例を使うと最も簡単に説明できます。次のようなモデルがあるとします:
class Person(models.Model):
first_name = models.CharField(...)
last_name = models.CharField(...)
birth_date = models.DateField(...)
このようなカスタムSQLを実行したとします
>>> for p in Person.objects.raw('SELECT * FROM myapp_person'):
... print p
John Smith
Jane Jones
もちろんこの例はあまりエキサイティングではありません – Person.objects.all()
を実行しているのと全く同じです。しかし、 raw()
は他のオプションを提供
していてそれを使うと非常にパワフルです。
モデルテーブル名
Person
テーブルの名前はこの例では何処から来たのか?
デフォルトでは Djanog はデータベーステーブル名をモデルの
“アプリケーションラベル(app label)” – manage.py startapp
で
使われた名前です – とモデルのクラス名を間にアンダースコアを入れて
連結させて作ります。仮定した例では、 Person
モデルは myapp
アプリケーションに存在するので、 myapp_person
となります。
詳細は db_table
オプションに関するドキュメントで
確認してください。そこにはデータベーステーブル名を手動で設定する
方法も書かれています。
Warning
.raw()
に渡されたSQL文はチェックされません。DjangoはそのSQL文が
データベースから行セットを返すことを期待しますが、そうするために
何かをしてくれる訳ではありません。クエリが行を返さないのであれば、
(おそらく意味不明な)エラーとなります。
raw()
はクエリの中のフィールドをモデルのフィールドに自動的に対応付け
します。
クエリにおけるフィールドの並び順は関係ありません。言い換えると、以下の 2つのクエリは同じように動きます:
>>> Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')
...
>>> Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person')
...
対応付けは名前で行われます。つまり、クエリの中でSQLの AS
句を使って
フィールドをモデルフィールドに対応付けすることができます。よってもしも他の
テーブルに Person
のデータを持っているのであれば、 Person
インスタンス
に簡単に対応付けさせることができます
>>> Person.objects.raw('''SELECT first AS first_name,
... last AS last_name,
... bd AS birth_date,
... pk as id,
... FROM some_other_table''')
名前が一致するのであれば、モデルインスタンスは正常に作成されます。
また translations
引数を raw()
に渡すことでクエリの
フィールドをモデルフィールドに対応付けすることも出来ます。
これは、クエリのフィールド名をモデルのフィール名に辞書で対応付けさせる
方法です。例えば、上のクエリはこのように書き換えることが出来ます:
>>> name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
>>> Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)
raw()
ではインデックスを使うことができるので、最初の結果だけが欲しいの
であればこのように書くことができます:
>>> first_person = Person.objects.raw('SELECT * from myapp_person')[0]
けれども、インデックス化とスライス化はデータベースレベルでは行われません。
データベースに巨大な Person
オブジェクトがあるのならば、SQLレベルで
クエリに制限をかける方がより効率的です
>>> first_person = Person.objects.raw('SELECT * from myapp_person LIMIT 1')[0]
フィールドは省略することができます
>>> people = Person.objects.raw('SELECT id, first_name FROM myapp_person')
このクエリで返される Person
オブジェクトは遅延評価されるモデル
インスタンスとなります ( defer()
を参照 )。つまり、クエリで省略されたフィールドが必要であればロードすること
が出来るということです。例えば:
>>> for p in Person.objects.raw('SELECT id, first_name FROM myapp_person'):
... print p.first_name, # オリジナルのクエリで取得される
... print p.last_name # 必要なときに取得される
...
John Smith
Jane Jones
外見上は下の名前も名字も同じクエリで取得されたように見えます。しかし、この例 では実際は3つのクエリが発行されます。raw()クエリでは下の名前のみが取得され ます – 名字は print される時に必要に応じて取得されます。
省略出来ないフィールドが1つだけあります – プライマリキー
フィールドです。 Djangoはプライマリキーを使ってモデルインスタンスを識別
するので、素のクエリには常に含まれている必要があります。プライマリキーを
入れるのを忘れると InvalidQuery
例外が投げられます。
モデルに定義されないフィールドを含んだクエリを実行することも出来ます。 例えば、 PostgreSQL’s age() function を使ってデータベースで計算した 年齢の人々を一覧することができます
>>> people = Person.objects.raw('SELECT *, age(birth_date) AS age FROM myapp_person')
>>> for p in people:
... print "%s is %s." % (p.first_name, p.age)
John is 37.
Jane is 42.
...
raw()
にパラメータを与える¶クエリをパラメータ化したいのであれば、 raw()
に params
引数を渡す
ことが出来ます
>>> lname = 'Doe'
>>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])
params
はパラメータリストです。
クエリ文字列の中で %s
プレースホルダーを使います(これはデータベース
エンジンに関わらず同じです); それらは params
リストからのパラメータで
置き換えられます。
Warning
rawクエリでは文字列書式化を使うな!
上のクエリをこの様に書き換えたくなるかも知れません:
>>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
>>> Person.objects.raw(query)
してはいけません
params
リストの使用は SQL injection attacks から完璧に
守られます。この攻撃は攻撃者がデータベースに任意のSQLと投入するための
よく知られている手法です。もしも文字列の補完挿入を使うと、早晩SQL
インジェクションの餌食になるでしょう。 params
リストを使う限りは
攻撃から守られます。
時々 Manager.raw()
でさえも十分でないことがあります: モデルに明確に
マップさせることの出来ないクエリや、 UPDATE
. INSERT
, DELETE
を
直接実行する必要なこともあるでしょう。
このようなケースでは、モデル層全体を通過してデータベースを直接操作すること が常に可能です。
django.db.connection
というオブジェクトがデフォルトのデータベー ス接続を
表現して、 django.db.transaction
がデフォルトのトランザクションを表現して
います。このデフォルトのデータベース接続を使うには、 まず
connection.cursor()
を呼び出してカーソルオブジェクトを取得します。
次いで cursor.execute(sql, [params])
を呼び出して SQL を実行した後、
cursor.fetchone()
や cursor.fetchall()
を読んで結果行を返します。
データを変更する操作を行った後には、必ず
transaction.commit_unless_managed()
を呼び出して、変更をデータベースに
commit してください。クエリが単にデータを取得するだけの操作なら、 commit は
必要ありません。 例を示しましょう:
def my_custom_sql():
from django.db import connection, transaction
cursor = connection.cursor()
# データを変更する操作なので、 commit が必要
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
transaction.commit_unless_managed()
# データを取得するだけの操作なので commit は不要
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
row = cursor.fetchone()
return row
複数のデータベースを使っているのであれば django.db.connections
を使って
目的のデータベース接続(とカーソル)を取得することができます。
django.db.connections
は辞書ライクなオブジェクトでエイリアスを使って
特定のデータベース接続を取得することができます:
from django.db import connections
cursor = connections['my_db_alias'].cursor()
# ここにコードを書く...
transaction.commit_unless_managed(using='my_db_alias')
デフォルトでは Python DB API がフィールド名無しの結果を返しますが、
これは値の list
であって dict
でないということです。
少しだけパフォーマンスを犠牲にすれば次のようにして結果を dict
で
返すことができます
def dictfetchall(cursor):
" カーソルの全ての行を辞書として返す "
desc = cursor.description
return [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
2つの違いを例として示します:
>>> cursor.execute("SELECT id, parent_id from test LIMIT 2");
>>> cursor.fetchall()
((54360982L, None), (54360880L, None))
>>> cursor.execute("SELECT id, parent_id from test LIMIT 2");
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982L}, {'parent_id': None, 'id': 54360880L}]
素のSQL呼び出しをするときは、 Djangoは現在のトランザクションを自動的に ダーティとしてマークします。これらの呼び出しを含むトランザクションが 正確に閉じたことを確認する必要があります。 詳細に関しては、 Djangoのトンランザクション処理に求められるもの を参照してください。
リリースノートを参照してください
Django 1.3より前では、 素のSQL呼び出しを使う際、
transaction.set_dirty()
を使って手動で トランザクションを
ダーティーとマークする必要がありました。
connection
と cursor
は PEP 249 で説明されている Python DB-API
標準をほとんど実装しています
(ただし トランザクション処理 を除く)。
Python DB-APIに精通していないのであれば cursor.execute()
のSQL文は
SQL文にパラメータを直接追加するのではなく、プレースホルダーとして "%s"
を使うということを知っておいてください。
このテクニックを使うのであれば、使用されているデータベースライブラリは必要に
応じて 自動的にパラメータをクオートしたりエスケープしたりします。
(また、Djangoは "%s"
を求めているのであって SQLite Python バインディング
で使われる "?"
では ありません 。これは一貫性とわかりやすさのためです)
。
Oct 26, 2017