生のSQLクエリの実行—Djangoドキュメント

提供：Dev Guides
< DjangoDjango/docs/2.2.x/topics/db/sql
移動先:案内、検索

生のSQLクエリの実行

Djangoには、生のSQLクエリを実行する2つの方法があります。 Manager.raw（）を使用して生のクエリを実行し、モデルインスタンスを返すか、モデルレイヤーを完全に回避して[ X198X]カスタムSQLを直接実行します。

生のSQLを使用する前にORMを調べてください！

Django ORMは、生のSQLを記述せずにクエリを表現するための多くのツールを提供します。 例えば：

生のSQLを使用する前に、 ORM を調べてください。 サポートチャネルのいずれかで、ORMがユースケースをサポートしているかどうかを確認してください。


警告

生のSQLを作成するときは、常に十分に注意する必要があります。 SQLインジェクション攻撃から保護するために、使用するたびに、paramsを使用してユーザーが制御できるパラメーターを適切にエスケープする必要があります。 SQLインジェクション保護の詳細をお読みください。


生のクエリの実行

raw()マネージャーメソッドを使用して、モデルインスタンスを返す生のSQLクエリを実行できます。

Manager.raw(raw_query, params=None, translations=None)

このメソッドは、生のSQLクエリを受け取り、それを実行して、django.db.models.query.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テーブルの名前はどこから来たのですか？

デフォルトでは、Djangoは、モデルの「アプリラベル」（manage.py startappで使用した名前）をモデルのクラス名にアンダースコアを付けて結合することにより、データベーステーブル名を計算します。 この例では、Personモデルがmyappという名前のアプリに存在すると想定しているため、そのテーブルはmyapp_personになります。

詳細については、 db_table オプションのドキュメントを確認してください。このオプションでは、データベーステーブル名を手動で設定することもできます。


警告

.raw()に渡されるSQLステートメントのチェックは行われません。 Djangoは、ステートメントがデータベースから一連の行を返すことを期待していますが、それを強制することは何もしていません。 クエリが行を返さない場合、（おそらく不可解な）エラーが発生します。


警告

MySQLでクエリを実行している場合、MySQLのサイレント型強制は、型を混合するときに予期しない結果を引き起こす可能性があることに注意してください。 文字列型の列でクエリを実行するが、整数値を使用する場合、MySQLは、比較を実行する前に、テーブル内のすべての値の型を整数に強制します。 たとえば、テーブルに値'abc'、'def'が含まれていて、WHERE mycolumn=0をクエリすると、両方の行が一致します。 これを防ぐには、クエリで値を使用する前に、正しい型キャストを実行してください。


クエリフィールドのモデルフィールドへのマッピング

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オブジェクトは、遅延モデルインスタンスになります（ defer（）を参照）。 これは、クエリから省略されたフィールドがオンデマンドでロードされることを意味します。 例えば： 

>>> for p in Person.objects.raw('SELECT id, first_name FROM myapp_person'):
...     print(p.first_name, # This will be retrieved by the original query
...           p.last_name) # This will be retrieved on demand
...
John Smith
Jane Jones

外見から見ると、これはクエリが名と姓の両方を取得したように見えます。 ただし、この例では実際に3つのクエリが発行されました。 raw（）クエリでは名のみが取得されました。最後の名前は両方とも、印刷時にオンデマンドで取得されました。

除外できないフィールドは、主キーフィールドだけです。 Djangoは主キーを使用してモデルインスタンスを識別するため、常に生のクエリに含める必要があります。 主キーを含めるのを忘れると、InvalidQuery例外が発生します。


注釈の追加

モデルで定義されていないフィールドを含むクエリを実行することもできます。 たとえば、 PostgreSQLのage（）関数を使用して、データベースによって計算された年齢の人のリストを取得できます。 

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

多くの場合、 Func（）式を使用することで、生のSQLを使用して注釈を計算することを回避できます。


raw()へのパラメーターの受け渡し

パラメータ化されたクエリを実行する必要がある場合は、params引数をraw()に使用できます。 

>>> lname = 'Doe'
>>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])

paramsは、パラメーターのリストまたは辞書です。 リストのクエリ文字列では%sプレースホルダーを使用し、辞書の場合は%(key)sプレースホルダーを使用します（もちろん、keyは辞書キーに置き換えられます）。あなたのデータベースエンジンの。 このようなプレースホルダーは、params引数のパラメーターに置き換えられます。

ノート

辞書パラメータはSQLiteバックエンドではサポートされていません。 このバックエンドでは、パラメータをリストとして渡す必要があります。


警告

SQL文字列の生のクエリや引用符のプレースホルダーに文字列フォーマットを使用しないでください。

上記のクエリを次のように記述したくなります。 

>>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
>>> Person.objects.raw(query)

また、次のようにクエリを作成する必要があると思うかもしれません（%sを引用符で囲みます）。 

>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'"

これらの間違いをしないでください。

SQLインジェクション保護で説明したように、params引数を使用し、プレースホルダーを引用符で囲まないままにすると、 SQLインジェクション攻撃から保護されます。データベース。 文字列補間を使用するか、プレースホルダーを引用すると、SQLインジェクションのリスクがあります。


カスタムSQLを直接実行する

Manager.raw（）でも十分でない場合があります。モデルに適切にマップされないクエリを実行するか、UPDATE、INSERTを直接実行する必要があります。 、またはDELETEクエリ。

このような場合は、いつでもデータベースに直接アクセスして、モデルレイヤー全体をルーティングできます。

オブジェクトdjango.db.connectionは、デフォルトのデータベース接続を表します。 データベース接続を使用するには、connection.cursor()を呼び出してカーソルオブジェクトを取得します。 次に、cursor.execute(sql, [params])を呼び出してSQLを実行し、cursor.fetchone()またはcursor.fetchall()を呼び出して結果の行を返します。

例えば： 

from django.db import connection

def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()

    return row

SQLインジェクションから保護するために、SQL文字列の%sプレースホルダーを引用符で囲むことはできません。

クエリにリテラルのパーセント記号を含める場合は、パラメータを渡す場合にそれらを2倍にする必要があることに注意してください。 

cursor.execute("SELECT foo FROM bar WHERE baz = '30%'")
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])

複数のデータベースを使用している場合は、django.db.connectionsを使用して、特定のデータベースの接続（およびカーソル）を取得できます。 django.db.connectionsは辞書のようなオブジェクトで、エイリアスを使用して特定の接続を取得できます。 

from django.db import connections
with connections['my_db_alias'].cursor() as cursor:
    # Your code here...

デフォルトでは、Python DB APIはフィールド名なしで結果を返します。つまり、dictではなく、listの値が返されます。 わずかなパフォーマンスとメモリコストで、次のようなものを使用して結果をdictとして返すことができます。 

def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

もう1つのオプションは、Python標準ライブラリのcollections.namedtuple()を使用することです。 namedtupleは、属性ルックアップによってアクセス可能なフィールドを持つタプルのようなオブジェクトです。 また、インデックス作成と反復可能です。 結果は不変であり、フィールド名またはインデックスでアクセスできます。これは便利な場合があります。 

from collections import namedtuple

def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

3つの違いの例を次に示します。 

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> cursor.fetchall()
((54360982, None), (54360880, None))

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> results = namedtuplefetchall(cursor)
>>> results
[Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]
>>> results[0].id
54360982
>>> results[0][0]
54360982

接続とカーソル

connectionとcursorは、トランザクション処理を除いて、 PEP 249 で説明されている標準のPythonDB-APIをほとんど実装しています。 ]。

Python DB-APIに慣れていない場合は、cursor.execute()のSQLステートメントで、SQL内に直接パラメーターを追加するのではなく、プレースホルダー"%s"を使用することに注意してください。 この手法を使用する場合、基盤となるデータベースライブラリは必要に応じてパラメータを自動的にエスケープします。

また、Djangoは"%s"プレースホルダーを想定しており、SQLitePythonバインディングで使用される"?"プレースホルダーはではなくを想定していることに注意してください。 これは一貫性と正気のためです。

カーソルをコンテキストマネージャーとして使用する： 

with connection.cursor() as c:
    c.execute(...)

と同等です： 

c = connection.cursor()
try:
    c.execute(...)
finally:
    c.close()

ストアドプロシージャの呼び出し

CursorWrapper.callproc(procname, params=None, kparams=None)

指定された名前でデータベースストアドプロシージャを呼び出します。 入力パラメータのシーケンス（params）または辞書（kparams）を提供できます。 ほとんどのデータベースはkparamsをサポートしていません。 Djangoの組み込みバックエンドのうち、Oracleのみがサポートしています。

たとえば、Oracleデータベースにこのストアドプロシージャがある場合、次のようになります。

CREATE PROCEDURE "TEST_PROCEDURE"(v_i INTEGER, v_text NVARCHAR2(10)) AS
    p_i INTEGER;
    p_text NVARCHAR2(10);
BEGIN
    p_i := v_i;
    p_text := v_text;
    ...
END;

これはそれを呼びます：

with connection.cursor() as cursor:
    cursor.callproc('test_procedure', [1, 'test'])



「https://www.finddevguides.com/index.php?title=Django/docs/2.2.x/topics/db/sql&oldid=95031」から取得
Powered by MediaWiki