原始的SQL查詢 · Django 中文文檔 1.8

# 進行原始的sql查詢 # 在*模型查詢API*不夠用的情況下，你可以使用原始的sql語句。django提供兩種方法使用原始sql進行查詢：一種是使用**Manager.raw()**方法，*進行原始查詢并返回模型實例*；另一種是完全避開模型層，*直接執行自定義的sql語句*。 > **警告** > > 編寫原始的sql語句時，應該格外小心。每次使用的時候，都要確保轉義了參數中的任何控制字符，以防受到sql注入攻擊。更多信息請參閱*防止sql注入*。 ## 進行原始查詢 ## **raw()**方法用于原始的sql查詢，并返回模型的實例： ``` Manager.raw(raw_query, params=None, translations=None) ``` 這個方法執行原始的sql查詢之后，返回**django.db.models.query.RawQuerySet**的實例。**RawQuerySet**實例可以像一般的**QuerySet**那樣，通過迭代來提供對象的實例。這里最好通過例子展示一下，假設存在以下模型： ``` class Person(models.Model): first_name = models.CharField(...) last_name = models.CharField(...) birth_date = models.DateField(...) ``` 你可以像這樣執行自定義的sql語句： ``` >>> for p in Person.objects.raw('SELECT * FROM myapp_person'): ... print(p) John Smith Jane Jones ``` 當然，這個例子不是特別有趣，和直接使用**Person.objects.all()**的結果一模一樣。但是，**raw()**擁有其它更強大的使用方法。 > **模型表的名稱** > > 在上面的例子中，**Person**表的名稱是從哪里得到的？ > > 通常，Django通過將模型的名稱和模型的“應用標簽”（你在**manage.py startapp**中使用的名稱）進行關聯，用一條下劃線連接他們，來組合表的名稱。在這里我們假定**Person**模型存在于一個叫做**myapp**的應用中，所以表就應該叫做**myapp_person**。 > > 更多細節請查看**db_table**選項的文檔，它也可以讓你自定義表的名稱。 > **警告** > > 傳遞給**raw()**方法的sql語句并沒有任何檢查。django默認它會返回一個數據集，但這不是強制性的。如果查詢的結果不是數據集，則會產生一個錯誤。 > **警告** > > 如果你在mysql上執行查詢，注意在類型不一致的時候，mysql的靜默類型強制可能導致意想不到的結果發生。如果你在一個字符串類型的列上查詢一個整數類型的值，mysql會在比較前強制把每個值的類型轉成整數。例如，如果你的表中包含值**'abc'**和**'def'**，你查詢**'where mycolumn=0'**，那么兩行都會匹配。要防止這種情況，在查詢中使用值之前，要做好正確的類型轉換。 > **警告** > > 雖然**RawQuerySet**可以像普通的**QuerySet**一樣迭代，**RawQuerySet**并沒有實現可以在**QuerySet**上使用的所有方法。例如，**\_\_bool\_\_()**和**\_\_len\_\_()**在**RawQuerySet**中沒有被定義，所以所有**RawQuerySet**轉化為布爾值的結果都是**True**。**RawQuerySet**中沒有實現他們的原因是，在沒有內部緩存的情況下會導致性能下降，而且增加內部緩存不向后兼容。 ## 將查詢字段映射到模型字段 ## **raw()**方法自動將查詢字段映射到模型字段。字段的順序并不重要。換句話說，下面兩種查詢的作用相同： ``` >>> 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') ... ``` Django會根據名字進行匹配。這意味著你可以使用sql的**as**子句來映射二者。所以如果在其他的表中有一些**Person**數據，你可以很容易地把它們映射成**Person**實例。 ``` >>> Person.objects.raw('''SELECT first AS first_name, ... last AS last_name, ... bd AS birth_date, ... pk AS id, ... FROM some_other_table''') ``` 只要名字能對應上，模型的實例就會被正確創建。又或者，你可以在**raw()**方法中使用翻譯參數。翻譯參數是一個字典，將表中的字段名稱映射為模型中的字段名稱、例如，上面的查詢可以寫成這樣： ``` >>> 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()**方法支持索引訪問，所以如果只需要第一條記錄，可以這樣寫： ``` >>> first_person = Person.objects.raw('SELECT * FROM myapp_person')[0] ``` 然而，索引和切片并不在數據庫層面上進行操作。如果數據庫中有很多的**Person**對象，更加高效的方法是在sql層面限制查詢中結果的數量： ``` >>> first_person = Person.objects.raw('SELECT * FROM myapp_person LIMIT 1')[0] ``` ## 延遲加載模型字段 ## 字段也可以被省略： ``` >>> people = Person.objects.raw('SELECT id, first_name FROM myapp_person') ``` 查詢返回的Person對象是一個延遲的模型實例（請見 **defer()**）。這意味著被省略的字段，在訪問時才被加載。例如: ``` >>> 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 ``` 從表面上來看，看起來這個查詢獲取了**first_name**和**last_name**。然而，這個例子實際上執行了3次查詢。只有**first_name**字段在**raw()**查詢中獲取，**last_name**字符按在執行打印命令時才被獲取。只有一種字段不可以被省略，就是主鍵。Django 使用主鍵來識別模型的實例，所以它在每次原始查詢中都必須包含。如果你忘記包含主鍵的話，會拋出一個**InvalidQuery**異常。 ## 增加注解 ## 你也可以在查詢中包含模型中沒有定義的字段。例如，我們可以使用**PostgreSQL的age()函數**來獲得一群人的列表，帶有數據庫計算出的年齡。 ``` >>> 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. ... ``` ## 向 **raw()** 方法中傳遞參數 ## 如果你需要參數化的查詢，可以向**raw()** 方法傳遞**params**參數。 ``` >>> lname = 'Doe' >>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname]) ``` **params**是存放參數的列表或字典。你可以在查詢語句中使用**%s**占位符，或者對于字典使用**%(key)**占位符（**key**會被替換成字典中鍵為key的值），無論你的數據庫引擎是什么。這樣的占位符會被替換成參數表中正確的參數。 > **注意** > > SQLite后端不支持字典，你必須以列表的形式傳遞參數。 > **警告** > > 不要在原始查詢中使用字符串格式化！ > > 它類似于這種樣子： > ``` >>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname >>> Person.objects.raw(query) ``` > 使用參數化查詢可以完全防止sql注入，一種普遍的漏洞使攻擊者可以向你的數據庫中注入任何sql語句。如果你使用字符串格式化，早晚會受到sql輸入的攻擊。只要你記住默認使用參數化查詢，就可以免于攻擊。 ## 直接執行自定義sql ## 有時**Manager.raw()**方法并不十分好用，你不需要將查詢結果映射成模型，或者你需要執行**UPDATE**、**INSERT**以及**DELETE**查詢。在這些情況下，你可以直接訪問數據庫，完全避開模型層。 **django.db.connection**對象提供了常規數據庫連接的方式。為了使用數據庫連接，調用**connection.cursor()**方法來獲取一個游標對象之后，調用**cursor.execute(sql, [params])**來執行sql語句，調用**cursor.fetchone()**或者**curser.fetchall()**來返回結果行。例如: ``` 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 ``` 注意如果你的查詢中包含百分號字符，你需要寫成兩個百分號字符，以便能正確傳遞參數： ``` cursor.execute("SELECT foo FROM bar WHERE baz = '30%'") cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id]) ``` 如果你使用了不止一個數據庫，你可以使用**django.db.connections**來獲取針對特定數據庫的連接（以及游標）對象。**django.db.connections**是一個類似于字典的對象，允許你通過它的別名獲取特定的連接 ``` from django.db import connections cursor = connections['my_db_alias'].cursor() # Your code here... ``` 通常，Python DB API會返回不帶字段的結果，這意味著你需要以一個列表結束，而不是一個字典。花費一點性能之后，你可以返回一個字典形式的結果，像這樣： ``` def dictfetchall(cursor): "Returns all rows from a cursor as a dict" desc = cursor.description return [ dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall() ] ``` 下面是一個體現二者區別的例子: ``` >>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2"); >>> cursor.fetchall() ((54360982L, None), (54360880L, None)) >>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2"); >>> dictfetchall(cursor) [{'parent_id': None, 'id': 54360982L}, {'parent_id': None, 'id': 54360880L}] ``` ## 連接和游標 ## 連接和游標主要實現PEP 249中描述的Python DB API標準，除非它涉及到事務處理。如果你不熟悉Python DB-API，注意**cursor.execute()**中的sql語句使用占位符**"%s"**，而不是直接在sql中添加參數。如果你使用它，下面的數據庫會在必要時自動轉義你的參數。也要注意Django使用**"%s"**占位符，而不是SQLite Python綁定的**"?"**占位符。這是一致性和可用性的緣故。 ``` Django 1.7中的改變。 ``` **PEP 249**并沒有說明游標是否可以作為上下文管理器使用。在python2.7之前，游標可以用作上下文管理器，由于魔術方法lookups中意想不到的行為(Python ticket #9220)。Django 1.7 顯式添加了對允許游標作為上下文管理器使用的支持。將游標作為上下文管理器使用: ``` with connection.cursor() as c: c.execute(...) ``` 等價于： ``` c = connection.cursor() try: c.execute(...) finally: c.close() ```