Django executes Raw SQL

Ii. Summary of Knowledge points

When the model query APIs don ' t go far enough, you can fall back to writing raw SQL.

Go far enough: not enough fall back to: Help
Raw: Raw, unprocessed

Django provides two ways of executing (performing) The original SQL query:

(1) , Manager.raw (): Executes the original query and returns the model instance

(2) , executing custom SQL directly: directly execute the custom SQL, this way can completely avoid the data model, but directly execute the original SQL statement.

Third, Raw () method

The Raw () Manager method can be used to perform raw SQL queries that return model instances:
Manager. Raw ( raw_query , params=none , translations=none )

Usage:

In Person.objects.raw (' SELECT * from person LIMIT 2 '): ...     print Pjohn smithjane Jones  

Note that the original SQL model, if not defined in db_table , uses the name of the app, followed by an underscore followed by the name of the model class, such as "myblog_new"; The above example is already handled when defining the class:

Class New (models. Model):    ...    ...... #自定义表名    Meta:        ' New '  

2. Query word De Yin to model field (Mapping query fields

Raw () automatically maps fields in the the the model. and is matched by name, which means we can use the SQL clause (clause)

>>> Person.objects.raw ("' SELECT first as first_name,...                              Last as last_name,...                              BD as Birth_date,...                              PK as ID,... From some_other_table ")    

Returns a Rawqueryset object

3, Index Search (index lookups)

First_person = Person.objects.raw (' frommyapp_person') [0]first_person = Person.objects.raw (' 1') [0] #然而, indexes and slices are not executed at the database level (except limit)   

4. Delay model field (deferring)

Field may also is left out (leaving out: ignored, not considered; forgotten), which means that the query for the fields will be excluded from loading as needed.

In Person.objects.raw (' SELECT ID, first_name from Myapp_person '): ...     # This will retrieve the original query # This will retrieve the demand ... John smithjane Jones   

This example actually retrieves three fields, a primary key (required), an original SQL field, and a requirement field. Here the primary key field cannot be omitted, otherwise an error will occur, as follows:

5. Transfer parameters (passing parameters into Raw () )

If you need to perform a parameterized query, you can use the params parameter primitive ()

Note two points: (1),

(2), must use [parameter], otherwise error:
(3), this way is not correct:

error:>>> query = 'WHERE last_name =%s'% lname>>> Person.objects.raw (query) 

Iv. direct execution of the custom SQL

Manager.raw () far from enough, you can directly perform custom sql,directly execute UPDATE , INSERT , or DELETE queries.

Django.db.connection: Represents the default database connection  
django.db.transaction  : Represents the default database transaction (transaction)  
calls   with database connection; connection.cursor ()   gets a cursor object.  
then call   Cursor.execute (Sql, [params])   execute sql 
Cursor.fetchone ()   or   cursor.fetchall ():   return result row

Call   If you perform a modification operation; Transaction.commit_ Unless_managed () to ensure that your changes are committed to the database.

My_custom_sql():    # Data modification operation--Submit Request Cursor.execute (# Data retrieval operation, no need to submit cursor.execute (return row 

django.db.connections : for using multiple databases

From django.db import connectionscursor = connections[' My_db_alias '].cursor ()# Your code here ... Transaction.commit_unless_managed (using=' My_db_alias ')   

Usually we don't need to call transaction.commit_unless_managed manually ( ), we can do this:

@commit_on_successdef my_custom_sql_view ( Request, Value): from django.db import connection, transaction cursor = con Nection.cursor () # Data modifying Operation Cursor.execute ( "UPDATE Bar SET Foo = 1 WHERE baz =%s ", [value]) # Since we modified data, mark the transaction as Dirty Transactio N.set_dirty () # Data retrieval operation. This doesn ' t dirty the transaction, # so-no call-to-set_dirty () is required. Cursor.execute ( "select Foo from bar WHERE baz =%s", [value]) row = Cursor.fetchone () return render_to _response ( ' template.html ', {    

Django executes raw SQL