Memetakan bidang permintaan ke bidang model¶

raw() secara otomatis memetakan bidang-bidang dalam permintaan ke bidang pada model.

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

Selama nama-nama cocok, instance model akan dibuat dengan benar.

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)

Pencarian indeks¶

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]

Menangguhkan bidang-bidang model¶

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.

Menambahkan keterangan¶

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

Anda dapat sering menghindari penggunaan SQL mentah untuk menghitung penjelasan dengan menggunakan Func() expression.

Melewati parameter kedalam 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.

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

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

Hubungan dan kursor¶

connection dan cursor kebanyakan menerapkan API-DB Python standar digambarkan dalam pep:249 — kecuali ketika itu datang pada transaction handling 1.

Jika anda akrab dengan DB-API Python, catat bahwa pernyataan SQL dalam cursor.execute() menggunakan placeholder, "%s", daripada menambahkan parameter langsung dalam SQL. Jika anda menggunakan teknik ini, pustaka basisdata pokok akan otomatis meloloskan parameter anda seperlunya.

Juga catat bahwa Django mengharapkan placeholder "%s", bukan placeholder "?", yang digunakan oleh pengikatan Python SQLite. Ini adalah untuk kebaikan dari ketetapan dan kesegaran.

Menggunakan sebuah kursor sebagai pengelola konteks:

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

setara pada:

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