Execute native SQL queries

This is the 8th day of my participation in the August More Text Challenge. For details, see:August is more challenging

Django lets you execute native SQL queries in two ways:

  • You can use manager.raw () to execute a native query and return an instance of the model.
  • Or execute custom SQL directly without using the model layer at all.

1. Execute native queries

The manager method raw() can be used to execute a native SQL query, returning an instance of the model:

Manager.raw(raw_query, params=None, translations=None)
Copy the code

This method accepts a native SQL query, execute it, and return a django. Db. Models. Query. RawQuerySet instance. This RawQuerySet can be iterated over to retrieve object instances just like a normal QuerySet.

1.1 Common Query

class Person(models.Model) :
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)
Copy the code

You can then execute custom SQL like this:

for p in Person.objects.raw('SELECT * FROM app_person') :The default django table name is the app name plus the class name
    print(p)
​
# Error: no query is found
Copy the code

1.2 Map query fields to model fields

The RAW () field maps the fields in the query statement to the fields in the model.

The sorting of fields in the query statement is not important. In other words, the following two queries are consistent:

Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM app_person')
​
Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM app_person')
Copy the code

Matches are made by name. This means that you can use THE AS clause of SQL to map the fields in the query statement to the fields in the model. So, if you have other tables that contain Person data, you can easily map them to the Person instance:

>>> Person.objects.raw('''SELECT first AS first_name,
.                             last AS last_name,
.                             bd AS birth_date,
.                             pk AS id,
.                      FROM app_person''')
Copy the code

As long as the names match, the model instance is created correctly.

Alternatively, you can use the translations argument of raw() to map the fields in the query to the fields in the model. This is a dictionary that maps the field names in the query statement to the field names in the model. For example, the query above could also be written like this:

>>> name_map = {'first': 'first_name'.'last': 'last_name'.'bd': 'birth_date'.'pk': 'id'}
>>> Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)
Copy the code

1.3 Index Query

Raw () supports indexing, so if you only need the first result write:

>>> first_person = Person.objects.raw('SELECT * FROM app_person') [0]
Copy the code

1.4 Passing parameters to raw()

If you need to perform parameterized queries, you can use the params parameter of raw() :

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

Params is a parameter dictionary. You will replace the %s placeholder in the query string with a list, or the %(key)s placeholder with a dictionary (where key should be replaced by dictionary key of course), regardless of which database engine you are using. These placeholders are replaced with the value of the params parameter.

!!!!!!!!! The argument must be passed as a list in such a format that it cannot be written as a string

2. Execute custom SQL directly

Bypass the model layer.

The django.db.connection object represents the default database connection. To use this database connection, call connection.cursor() to get a pointer object. Then, call cursor.execute(SQL, [params]) to execute the SQL and cursor.fetchone(), or cursor.fetchall() to get the resulting data.

from django.db import connection
​
def my_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
Copy the code

To avoid SQL injection, you must never enclose the %s placeholder in an SQL string in quotes.

Note that to include textual percent signs in a query, you need to use two percent signs in the passed parameter:

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

2.1 Specifying a database connection

Gets a connection (and pointer) to the specified database using django.db.connections. Django.db.connections is a dictionary-like object that allows you to get the specified connection using the connection alias:

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