============================= 素の SQL クエリを直接実行する ============================= :revision-up-to: 17812 (1.4) unfinished .. currentmodule:: django.db.models :doc:`モデルクエリAPI ` では十分には役に立たない場合、 逆戻りして素のSQLを書くことができます。Djangoでは素のSQLクエリを実行する のに２つの方法があります::meth:`Manager.raw()` を使って、 `素のクエリを実行する`__ ことでモデルインスタンスを返すか、あるいは モデル層全体を避けて `カスタムSQLを直接実行する`__ ことができます。 __ `素のクエリを実行する` __ `カスタムSQLを直接実行する` .. _executing-raw-queries: 素のクエリを実行する ====================== .. versionadded:: 1.2 マネージャーメソッド ``raw()`` を使って素のSQLクエリを実行し、モデルインスタンス を返すことができます。: .. method:: Manager.raw(raw_query, params=None, translations=None) このメソッドは素のSQLクエリを受け取りそれを実行し、 :class:`~django.db.models.query.RawQuerySet` インスタンスを返します。 この :class:`~django.db.models.query.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()`` は他のオプションを提供 していてそれを使うと非常にパワフルです。 .. admonition:: モデルテーブル名 ``Person`` テーブルの名前はこの例では何処から来たのか？ デフォルトでは Djanog はデータベーステーブル名をモデルの "アプリケーションラベル(app label)" -- ``manage.py startapp`` で 使われた名前です -- とモデルのクラス名を間にアンダースコアを入れて 連結させて作ります。仮定した例では、 ``Person`` モデルは ``myapp`` アプリケーションに存在するので、 ``myapp_person`` となります。 詳細は :attr:`~Options.db_table` オプションに関するドキュメントで 確認してください。そこにはデータベーステーブル名を手動で設定する 方法も書かれています。 .. warning:: ``.raw()`` に渡されたSQL文はチェックされません。DjangoはそのSQL文が データベースから行セットを返すことを期待しますが、そうするために 何かをしてくれる訳ではありません。クエリが行を返さないのであれば、 (おそらく意味不明な)エラーとなります。 クエリフィールドをモデルフィールドに対応付けする ------------------------------------------------------------------- ``raw()`` はクエリの中のフィールドをモデルのフィールドに自動的に対応付け します。 クエリにおけるフィールドの並び順は関係ありません。言い換えると、以下の ２つのクエリは同じように動きます:: >>> 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`` オブジェクトは遅延評価されるモデル インスタンスとなります ( :meth:`~django.db.models.query.QuerySet.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 外見上は下の名前も名字も同じクエリで取得されたように見えます。しかし、この例 では実際は３つのクエリが発行されます。raw()クエリでは下の名前のみが取得され ます -- 名字は print される時に必要に応じて取得されます。 省略出来ないフィールドが１つだけあります -- プライマリキー フィールドです。 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. ... __ http://www.postgresql.org/docs/8.4/static/functions-datetime.html ``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`` リストを使う限りは 攻撃から守られます。 __ http://en.wikipedia.org/wiki/SQL_injection .. _executing-custom-sql: カスタムSQLを直接実行する ============================= 時々 :meth:`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() ] ２つの違いを例として示します:: >>> 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}] .. _transactions-and-raw-sql: トランザクションと素のSQL ------------------------------------------------ 素のSQL呼び出しをするときは、 Djangoは現在のトランザクションを自動的に ダーティとしてマークします。これらの呼び出しを含むトランザクションが 正確に閉じたことを確認する必要があります。 詳細に関しては、 :ref:`Djangoのトンランザクション処理に求められるもの ` を参照してください。 .. versionchanged:: 1.3 Django 1.3より前では、 素のSQL呼び出しを使う際、 ``transaction.set_dirty()`` を使って手動で トランザクションを ダーティーとマークする必要がありました。 接続とカーソル ----------------------- ``connection`` と ``cursor`` は :pep:`249` で説明されている Python DB-API 標準をほとんど実装しています (ただし :doc:`トランザクション処理 ` を除く)。 Python DB-APIに精通していないのであれば ``cursor.execute()`` のSQL文は SQL文にパラメータを直接追加するのではなく、プレースホルダーとして ``"%s"`` を使うということを知っておいてください。 このテクニックを使うのであれば、使用されているデータベースライブラリは必要に 応じて 自動的にパラメータをクオートしたりエスケープしたりします。 (また、Djangoは ``"%s"`` を求めているのであって SQLite Python バインディング で使われる ``"?"`` では *ありません* 。これは一貫性とわかりやすさのためです) 。