Django筆記二十一之使用原生SQL查詢資料庫

XHunter發表於2023-04-10
Django筆記SQL資料庫

本文首發於公眾號：Hunter後端
原文連結：Django筆記二十一之使用原生SQL查詢資料庫

Django 提供了兩種方式來執行原生 SQL 程式碼。

一種是使用 raw() 函式，一種是 使用 connection.cursor()。

但是官方還是推薦在使用原生 SQL 之前，儘量的先去探索一下 QuerySet 提供的各種 API。

目前而言，官方文件提供的各種 API 是能夠滿足絕大多數的應用場景的。

以下是本篇筆記的目錄：

  1. raw()
  2. connection.cursor()
  3. 多資料庫操作

1、raw()

這個方法可以用來操作原生 SQL，然後返回 model 例項：

我們以 Blog 作為示例，使用的程式碼如下：

for blog in Blog.objects.raw("select * from blog_blog"):
    print(blog)

上面這段程式碼的作用效果跟 Blog.objects.all() 獲取的結果是一樣的，但是在一些操作上又不太一樣，比如 all().count() 可以獲取到 總數，但是 raw() 就不能進行這種操作。

需要注意的是，raw() 並不會去檢測輸入的 SQL 程式碼，即使我們使用 Blog 這個model 來查詢 Entry 的資料，也能夠返回結果，不過返回的都是 Entry 表的屬性：

for blog in Blog.objects.raw("select * from blog_entry"):
    print(blog.__dict__)  # __dict__ 輸出的都是 blog_entry 的欄位

也就是說 在 Blog.objects.raw() 中，真正起作用的只有 raw() 這一個函式，前面的 Blog 不過是一個架子，或者途徑，是為了引出 raw() 這個函式。

自定義返回欄位

在 raw() 函式的 SQL 程式碼裡，我們可以自定義選中的欄位，如果需要使用沒有選中的欄位，那麼系統會再次訪問資料庫來獲取，這個操作過程就跟前面介紹的 defer() 函式一樣。

for item in Blog.objects.raw("select id from blog_blog"):
    print(item.__dict__)
    print(item.id, item.name)
    print(item.__dict__)

{'_state': <django.db.models.base.ModelState object at 0x7fbd4165b6a0>, 'id': 2}
2 hunter
{'_state': <django.db.models.base.ModelState object at 0x7fbd4165b6a0>, 'id': 2, 'name': 'hunter'}

可以看到，返回的結果中先輸出的資料只有 id，後面，當我們訪問 name 欄位的時候，又去獲取了 name 欄位的資料。

自定義欄位必須包含主鍵

當我們自定義欄位返回的時候，必須是要包含主鍵欄位的，否則在我們獲取資訊的時候會報錯，比如下面的操作：

for blog in Blog.objects.raw("select name from blog_blog"):
    print(blog.__dict__)

在 print(blog.dict) 的時候就會報錯，資料中沒有主鍵資訊

自定義返回新欄位

可以跟 QuerySet 的 annotate 操作一樣，自定義新欄位返回，獲取的時候可以直接根據屬性值返回，比如：

entry = Entry.objects.raw("select *, date_format(pub_date, '%%Y-%%m') as date_1 from blog_entry")[0]
print(entry.date_1)

傳遞變數

給輸入的 SQL 語句傳遞變數：

name = "python"
Blog.objects.raw("select * from blog_blog where name = '%s'", [name])

2、connection.cursor()

Django 推出了一種更加直接執行 SQL 的方式，用到的模組是 django.db.connction，用到的 cursor 和 pymysql 的庫是一樣的用法，官方給出的示例如下：

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 中，需要對一些符號進行轉義之後才可以使用，比如：

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

其中，第二條語句的 % 需要進行轉義

適配fetch的資料

透過 fetchone 和 fetchall() 返回的資料是隻有 value 值的，沒有對應的欄位 key，如果可以適當的犧牲效能和記憶體，來換取獲取資料的便利和準確性，官方提供了這樣一種方式：

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

在我們執行完 cursor.execute(sql) 之後，把 cursor 作為引數傳入 dictfetchall 函式中，就可以返回一個字典列表

介紹一下 cursor.description，這個返回的是一個元組資料，裡面的元素也是一個元組，元組的第一個元素就是我們 select 的欄位名。

所以 columns = [col[0] for col in cursor.description] 這一行程式碼獲取的是指定的所有欄位名

使用示例：

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

with connection.cursor() as cursor:
	sql = "select id, name from blog_blog"
	cursor.execute(sql)
	results = dictfetchall(cursor)

print(results)

# 返回的結果：
# [{'id': 20, 'name': 'name_3'}, {'id': 21, 'name': 'name_4'}, {'id': 1, 'name': 'name_5'}]

在我使用的過程中，我們使用的是上下文管理器的方式來獲取遊標：

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

所以，使用完之後，不需要手動關閉，他與下面的用法效果是一致的：

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

但推薦的還是上下文管理器的方式，更優雅。

3、多資料庫操作

如果系統用到了多個資料庫，那麼在使用 cursor 的時候，就需要使用到 django.db.connections 模組：

from django.db import connections
with connections['my_db_alias'].cursor() as cursor:
    pass

以上就是本篇筆記全部內容，下一篇筆記將簡單介紹一下多資料庫的操作。

如果想獲取更多後端相關文章，可掃碼關注閱讀：
image

相關文章