Performing raw SQL queries in Django (Part II)

Như các bạn đã biết, Django cung cấp sẵn một bộ API (QuerySet) cho phép thực hiện các thao tác CRUD.

Các bạn có thể tham khảo ở đây:

• A Survey On QuerySet In Django (Part I)
• A Survey On QuerySet In Django (Part II)

Bộ API này đơn giản hóa việc thao tác với data trong Django. Cú pháp đơn giản là điểm mạnh của bộ API này. Nhưng một khi câu query quá phức tạp hoặc khó viết với QuerySet, chúng ta cần sử dụng đến các câu query SQL thuần.

Trong bài viết này, tôi sẽ đề cập đến việc thao tác với database bằng các câu lệnh SQL thuần.

Có hai cách để thực hiện các câu query SQL thuần trong Django. Thứ nhất là sử dụng Manager.raw() để thực hiện các câu query và trả về các instance. Thứ hai, chúng ta sẽ dùng các câu lệnh SQL một cách trực tiếp. Ở cách thứ hai, chúng ta tránh được việc sử dụng lớp model (model layer).

Đây là phần II của loạt bài viết về việc sử dụng SQL thuần trong Django. Các bạn có thể tham khảo phần I ở link bên dưới:

• Performing raw SQL queries in Django (Part I)

d. Adding annotations

Chúng ta có thể thực thi các câu query chứa các field không được định nghĩa trong model. Ví dụ, chúng ta có thể sử dụng hàm age() của PostgreSQL để lấy ra một list các instance Person cùng với tuổi của họ:

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))
...
Khanh is 25.
Jane is 42.
...

e. Passing parameters into raw()

Nếu bạn muốn thực hiện các câu query ở dạng tham số hóa, bạn có thể sử dụng tham biến params của raw():

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

params là một list hoặc dictionary các tham số. Bạn sẽ sử dụng các placeholder %s trong câu query cho một list hoặc %(key)s cho một dictionary (tất nhiên thì ở đây, key chính là một khóa dictionary) mà không cần quan tâm đến database engine. Các placeholder đó sẽ được thay thế bởi các tham số trong tham biến params.

• Note

  SQLite không hỗ trợ params dạng dictionary. Với backend này, bạn phải truyền params dạng list.

• Warning

  Không sử dụng string formatting ở các câu query thuần

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

**Đừng!**

Nếu bạn cố gắng viết một câu query như bên trên thì rất có thể bạn sẽ là nạn nhân của các tấn công kiểu ***SQL injection***.

Thay vì string interpolation, hãy sử dụng tham biến **params**. Điều này sẽ giúp cho bạn tránh khỏi các tấn công kiểu ***SQL injection***.

2. Executing custom SQL directly

Đôi khi thậm chí Manager.raw() cũng không đáp ứng hết được nhu cầu của bạn. Ví dụ như việc thực hiện các câu query không liên kết rõ ràng với các model hoặc các câu query trực tiếp thực hiện các câu lệnh UPDATE, INSERT, DELETE.

Object django.db.connection biểu diễn database connection mặc định. Để sử dụng database connection, gọi connection.cursor() để lấy ra object cursor. Sau đó thì gọi cursor.execute(sql, [params]) để thực thi câu query và cursor.fetchone() hoặc cursor.fetchall() để trả về kết quả.

Ví dụ:

from django.db import connection

def my_custom_sql(self):
    cursor = connection.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

Chú ý rằng nếu bạn muốn dùng ký tự % thông thường thì bạn phải nhân đôi nó trong trường hợp bạn đang truyền parameter:

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

Nếu bạn đang sử dụng nhiều hơn một database, bạn có thể sử dụng django.db.connections để lấy ra connection (hoặc cursor) của một database cụ thể nào đó. database.db.connections là một đối tượng kiểu dictionary cho phép bạn gọi ra một connection cụ thể sử dụng alias của nó:

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

Mặc định, Python DB API sẽ trả về các kết quả mà không gồm tên các field. Điều này có nghĩa là mỗi kết quả trả về là một list các giá trị thay vì một dictionary. Tuy nhiên, bạn cũng có thể chuyển các kết quả thành một dictionary nếu như bạn chấp nhận đánh đổi một chút hiệu năng và một chút bộ nhớ:

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

Một lựa chọn khác là sử dụng collections.namedtuple() của thư viện chuẩn Python. Một namedtuple là một object kiểu tuple có các trường có thể truy cập bằng attribute lookup. Các kết quả trả về không thể bị thay đổi và có thể truy cập qua tên field và chỉ mục:

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

Dưới đây là một ví dụ về sự khác biệt giữa ba cách tiếp cận trên:

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