素の SQL 文の実行

Django gives you two ways of performing raw SQL queries: you can use Manager.raw() to perform raw queries and return model instances, or you can avoid the model layer entirely and execute custom SQL directly.

Explore the ORM before using raw SQL!

The Django ORM provides many tools to express queries without writing raw SQL. For example:

• The QuerySet API is extensive.
• You can annotate and aggregate using many built-in database functions. Beyond those, you can create custom query expressions.

Before using raw SQL, explore the ORM. Ask on one of the support channels to see if the ORM supports your use case.

警告

直接 SQL を書く場合はいかなる時も十分警戒するべきです。それを利用する時は毎回、利用者が 引数 を利用する事で任意に設定可能な全てのパラメータは SQL インジェクション攻撃から防御するため適切にエスケープすべきです。詳細は SQL インジェクションの防御 を参照してください。

素のクエリを実行する

raw() マネージャメソッドは素の SQL 文を処理してモデルのインスタンスを返させる場合に利用できます:

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

This method takes a raw SQL query, executes it, and returns a django.db.models.query.RawQuerySet instance. This RawQuerySet instance can be iterated over like a normal QuerySet to provide object instances.

例を示す事で最も簡単に説明できます。以下のモデルについて考えてください:

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

You could then execute custom SQL like so:

>>> for p in Person.objects.raw("SELECT * FROM myapp_person"):
...     print(p)
...
John Smith
Jane Jones

This example isn't very exciting -- it's exactly the same as running Person.objects.all(). However, raw() has a bunch of other options that make it very powerful.

モデルのテーブル名称

この例で示したモデル Person のテーブル名はどのようにして得られたのでしょうか？

デフォルトでは、Django はデータベース上のテーブル名をモデルの "アプリのラベル名" -- manage.py startapp で利用した名前 -- とモデルのクラス名とを、間にアンダースコアを入れて結合させる事で取得しています。先の例ではモデル Person が myapp と名づけられたアプリケーション内に作成されていると仮定すると、そのテーブル名が myapp_person であると推定できます。

詳細に関しては、手動でデータベースのテーブル名を設定できる db_table オプションのドキュメントを参照してください。

警告

.raw() に対して渡された SQL 文はチェックされません。Django はそこに記述された内容によってデータベースが行を返す事を期待しますが、それを強制する処理は行いません。もし記述したクエリが行を返さない場合、(おそらく不可解な)例外が発生します。

警告

もしあなたが MySQL でクエリを処理する場合は、複数の型を扱う際に MySQL の暗黙的な型変換が予期しない結果をもたらす場合がある事に注意してください。もし文字列型で定義したカラムに対し、数値型の値で問い合わせた場合、MySQL は比較処理を行う前にテーブル上の全ての値の型を数値型に変換します。例えば 'abc'、 'def' といった値が含まれているテーブルに対して WHERE mycolumn=0 という条件での問い合わせを行うと、両方の行がマッチします。これを防ぐため、クエリの値を利用する前に適切な型キャストを行ってください。

クエリのフィールドをモデルのフィールドにマップする

raw() は自動的にクエリのフィールドをモデルのフィールドにマップします。

The order of fields in your query doesn't matter. In other words, both of the following queries work identically:

>>> 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")

Matching is done by name. This means that you can use SQL's AS clauses to map fields in the query to model fields. So if you had some other table that had Person data in it, you could easily map it into Person instances:

>>> Person.objects.raw(
...     """
...     SELECT first AS first_name,
...            last AS last_name,
...            bd AS birth_date,
...            pk AS id,
...     FROM some_other_table
...     """
... )

名称が一致している限り、そのモデルのインスタンスは適切に生成されます。

Alternatively, you can map fields in the query to model fields using the translations argument to raw(). This is a dictionary mapping names of fields in the query to names of fields on the model. For example, the above query could also be written:

>>> 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() supports indexing, so if you need only the first result you can write:

>>> first_person = Person.objects.raw("SELECT * FROM myapp_person")[0]

However, the indexing and slicing are not performed at the database level. If you have a large number of Person objects in your database, it is more efficient to limit the query at the SQL level:

>>> first_person = Person.objects.raw("SELECT * FROM myapp_person LIMIT 1")[0]

モデルのフィールドの遅延評価

Fields may also be left out:

>>> people = Person.objects.raw("SELECT id, first_name FROM myapp_person")

The Person objects returned by this query will be deferred model instances (see defer()). This means that the fields that are omitted from the query will be loaded on demand. For example:

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

From outward appearances, this looks like the query has retrieved both the first name and last name. However, this example actually issued 3 queries. Only the first names were retrieved by the raw() query -- the last names were both retrieved on demand when they were printed.

There is only one field that you can't leave out - the primary key field. Django uses the primary key to identify model instances, so it must always be included in a raw query. A FieldDoesNotExist exception will be raised if you forget to include the primary key.

付加情報の追加

You can also execute queries containing fields that aren't defined on the model. For example, we could use PostgreSQL's age() function to get a list of people with their ages calculated by the database:

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

You can often avoid using raw SQL to compute annotations by instead using a Func() expression.

raw() にパラメータを渡す

If you need to perform parameterized queries, you can use the params argument to raw():

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

params is a list or dictionary of parameters. You'll use %s placeholders in the query string for a list, or %(key)s placeholders for a dictionary (where key is replaced by a dictionary key), regardless of your database engine. Such placeholders will be replaced with parameters from the params argument.

注釈

SQLite バックエンドにおいて辞書はサポートされていません; パラメータはリストで渡す必要が有ります。

警告

Do not use string formatting on raw queries or quote placeholders in your SQL strings!

It's tempting to write the above query as:

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

You might also think you should write your query like this (with quotes around %s):

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

Don't make either of these mistakes.

As discussed in SQL injectionへの防御, using the params argument and leaving the placeholders unquoted protects you from SQL injection attacks, a common exploit where attackers inject arbitrary SQL into your database. If you use string interpolation or quote the placeholder, you're at risk for SQL injection.

独自の 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

To protect against SQL injection, you must not include quotes around the %s placeholders in the SQL string.

もしリテラルなパーセント記号をクエリ中で使いたい場合、パラメータを渡す際に二重に記述する必要が有る事に注意してください:

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

2 つ以上のデータベース を利用している場合、特定の接続(とカーソル)を取得するのに django.db.connections を利用できます。django.db.connections はその別名を指定する事で特定の接続を得られる、辞書に似たオブジェクトです:

from django.db import connections

with connections["my_db_alias"].cursor() as cursor:
    # Your code here
    ...

デフォルトでは、Python データベース API は返す結果にフィールド名を含まず、つまり 辞書 でなく、 リスト の値として結果を返します。処理能力とメモリを少々利用して、次のような処理を用いる事で結果を 辞書 として得られます:

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

別の選択肢は Python 標準ライブラリに含まれる collections.namedtuple() を利用する事です。namedtuple は属性を指定して値へのアクセスが可能なタプルに似たオブジェクトです; 加えてインデックスが利用でき、イテレート可能でもあります。取得した結果は不変であり属性名もしくはインデックスでアクセスできて便利です:

from collections import namedtuple


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

The dictfetchall() and namedtuplefetchall() examples assume unique column names, since a cursor cannot distinguish columns from different tables.

Here is an example of the difference between the three:

>>> 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 に規定された Python DB-API の基準にほぼ完全に準拠しています — トランザクション操作 は例外となります。

Python DB-API に精通していない場合、cursor.execute() 内の SQL 文には直接値を追加せずに、"%s" というプレースホルダーを利用する事に注意してください。この手法を用いる事で、内部で動作しているデータベースライブラリは自動的に必要に応じて値のエスケープを行ってくれます。

加えて Django はプレースホルダーとして Python に内蔵された SQLite モジュールで用いられる "?" でなく "%s" を期待して動作する事にも注意してください。これは全体の調和と健全性のためです。

コンテキストマネージャとしてのカーソルの利用:

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

これは以下と同じです:

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

Calling stored procedures

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

Calls a database stored procedure with the given name. A sequence (params) or dictionary (kparams) of input parameters may be provided. Most databases don't support kparams. Of Django's built-in backends, only Oracle supports it.

For example, given this stored procedure in an Oracle database:

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;

This will call it:

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