Melakukan permintaan SQL mentah

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.

Menjelajahi ORM sebelum menggunakan SQL mentah!

ORM Django menyediakan banyak alat-alat untuk menyatakan permintaan tanpa menulis SQL mentah. Sebagai contoh:

• QuerySet API adalah luas.
• 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.

Peringatan

Anda harus berhati-hati ketika anda menulis SQL mentah. Setiap kali anda menggunakan itu, anda harus dengan benar meloloskan parameter apapun yang pengguna dapat mengendalikan menggunakan params untuk melindungi terhadap serangan SQL injection. Harap membaca lebih tentang SQL injection protection 1.

Melakukan permintaan mentah

Metode pengelola raw() dapat digunakan untuk melakukan permintaan SQL mentah yang mengembalikan instance model:

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.

Ini adalah terbaik digambarkan dengan sebuah contoh. Kiranya anda mempunyai model berikut:

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.

Nama tabel model

Dimana nama dari tabel Person datang dalam contoh itu?

Secara awalan, Django mencari tahu nama tabel basisdata dengan menggabungkan "app label" model -- nama anda gunakan dalam manage.py startapp -- pada nama kelas model, dengan sebuah garis bawah diantara mereka. Dalam contoh kami telah menganggap bahwa model Person tinggal dalam sebuah aplikasi bernama myapp, jadi tabelnya akan berupa myapp_person.

Untuk rincian lebih periksa dokumentasi untuk pilihan db_table, yang juga membiarkan anda secara manual menyetel nama tabel basisdata.

Peringatan

Tidak ada pemeriksaan selesai pada pernyataan SQL yang dilewatkan ke .raw(). Django mengharapkan pernyataan itu akan mengembalikan sekumpulan baris dari basisdata, tetapi tidak melakukan apapun untuk memaksa itu. Jika permintaan tidak mengembalikan baris, sebuah (kemungkinan samar) kesalahan akan menghasilkan.

Peringatan

Jika anda sedang melakukan permintaan pada MySQL, catat bahwa paksaan jenis diam MySQL mungkin menyebabkan hasil tidak diharapkan ketika mencampur jenis. Jika anda meminta pada sebuah kolom jenis string, tetapi dengan sebuah nilai integer, MySQL akan memaksa jenis-jenis dari semua nilai dalam tabel ke integer sebelum melakukan perbandingan. Sebagai contoh, jika tabel anda mengandung nilai-nilai 'abc', 'def' dan anda meminta untuk WHERE mycolumn=0, kedua baris akan cocok. Untuk mencegah ini, lakukan pembenaran typecast sebelum menggunakan nilai dalam sebuah permintaan.

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.

Catatan

Parameter dictionary tidak didukung dengan backend SQLite; dengan backend ini, anda harus melewatkan parameter sebagai sebuah list.

Peringatan

Jangan menggunakan string berbentuk pada permintaan mentah atau mengutip placeholder dalam string-string SQL anda !

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

Jangan membuat salah satu dari kesalahan-kesalahan ini.

Seperti diobrolkan dalam Perlindungan penyisipan SQL, menggunakan argumen params dan membiarkan placeholder tidak dikutip melindungi anda dari SQL injection attacks, pemanfaatan umum dimana penyerang memasukkan SQL berubah-ubah kedalam basisdata anda. Jika adan menggunakan penyisipan string atau mengutip placeholder, anda sedang pada resiko untuk penyisipan SQL.

Menjalankan penyesuaian SQL langsung

Terkadang bahkan Manager.raw() tidak cukup: anda mungkin butuh melakukan permintaan yang tidak memetakan dengan bersih pada model, atau langsung menjalankan permintaan UPDATE, INSERT, atau DELETE.

Dalam kasus-kasus ini, anda dapat selalu mengakses basisdata secara langsung, fungsi disekitar lapisan model sepenuhnya.

Obyek django.db.connection mewakili hubungan basisdata awalan. Untuk menggunakan hubungan basisdata, panggil connection.cursor() untuk mendapatkan obyek kursor. Kemudian, panggil cursor.execute(sql, [params]) untuk menjalankan SQL dan cursor.fetchone() atau cursor.fetchall() untuk mengembalikan baris hasil.

Sebagai contoh:

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

Untuk melindungi terhadap penyuntikan SQL, anda tidak harus menyertakan kutipan disekitar placeholder %s dalam string SQL.

Catat bahwa jika anda ingin menyertakan harfiah tanda persen dalam permintaan, anda telah menggandakan mereka dalam kasus anda sedang melewatkan parameter:

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

Jika anda sedang menggunakan more than one database 1, anda dapat menggunakan django.db.connections untuk mengambil hubungan (dan kursor ) untuk basisdata khusus. django.db.connections adalah obyek seperti-dictionary yang mengizinkan anda mengambil hubungan khusus menggunakan nama lainnya:

from django.db import connections

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

Secara awalan, API DB Python akan mengembalikan hasil tanpa nama-nama bidang mereka, yang berarti anda jadi list dari nilai, daripada dict. Pada penampilan kecil dan biaya memori, anda dapat mengembalikan hasil sebagai dict dengan menggunakan sesuatu seperti ini:

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()]

Pilihan lain adalah menggunakan collections.namedtuple() dari pustaka standar Python. Sebuah namedtuple adalah obyek seperti-tuple yang mempunyai bidang-bidang diakses oleh atribut pencarian; itu juga dapat diindeks dan berulang. Hasilnya adalah tetap dan dapat diakses oleh bidang nama atau indeks, yang mungkin berguna:

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

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

Memanggil prosedur penyimpanan

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

Panggilan sebuah store procedure dengan nama diberikan. Sebuah urutan (params) atau dictionary (kparams) dari parameter masukan mungkin disediakan. Kebanyakan basisdata tidak mendukung kparams. Dari backend siap-pakai Django, hanya Oracle mendukung itu.

Sebagai contoh, diberikan ini prosedur penyimpanan dalam sebuah basisdata 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;

Ini akan memanggil itu:

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