Django Running Raw Queries

Introduction

Structured Query Language is abbreviated as SQL. SQL is used for data management and retrieval of data in relational databases. SQL is generally a computer language of the database. For relational database systems, SQL works as the standard language. SQL is a query language that is used mostly in all databases. Some of the uses of SQL are given below:

• With the help of SQL, users can access the data from the relational database management system.
• Allowed the description of data by users.
• Through SQL, users can manipulate the data and define the data in the database system.
• With the help of SQL, users can create tables and databases and also drop tables and databases.
• Creation of view, storing of procedure, and function can be possible with the help of SQL.
• Users also set permissions on tables, views, and procedures through SQL.

INSERT, CREATE, UPDATE SELECT, DELETE, and DROP etc are some of the commands that are used by SQL for creating interaction with relational database management systems. Based on their nature, these commands are divided into the following groups:

DDL - Data Definition Language

• CREATE
• ALTER
• DROP
• TRUNCATE

DML - Data Manipulation Language

• SELECT
• INSERT
• UPDATE
• DELETE

DCL - Data Control Language

• GRANT
• REVOKE

TCL - Transaction Control Language

• COMMIT
• ROLLBACK

Requirement of Doing Raw SQL Queries

Raw SQL queries are used for achieving better performance in query execution and raw queries are also used if the performance of the API query model is not so good. The gap between the code that is executing queries and the database is bridged by the Django Object Relational Mapper (ORM).

Two methods are provided by Django for performing the raw SQL queries. These methods are given below:

• For performing raw queries and for returning model instances use the Manager.raw() method
• Avoiding the entire model layer and then performing custom SQL queries directly and the database is accessed directly, by entirely routing around the model layer.

Performing Raw SQL Queries Using the Raw Method

The raw() manager method is a method that is used for executing raw SQL queries and these queries will return the model instances.

Syntax:

Parameters: A raw SQL query is taken by this method and then executes.

Return: Returns an instance django.db.models.query.RawQuerySet. For providing object instances, the instances of this RawQuerySet will be iterated as that of a normal QuerySet.

Example of Raw Method

Let us take an example of the model which stored the data of the student Here the name of the model is Student which will store the name of the student, and the date of birth of the student int the birth_date field, and the father's name is stored in the father_name field.

Custom SQL queries can be executed in the way given below:

Mapping of Query Fields Name to Model Fields

Fields provided in the query are automatically mapped by the raw() with the fields on the model The order in which fields are written in the query is not a matter. Or we can say that below given two queries will work similar

The name is used for matching. In other words, we can say that for mapping the fields in the query to the fields in the model we can use the AS clause of the SQL.

The creation of Model instances will be done correctly until the name matches. And in other ways, the translation arguments can be used for mapping the query fields to the fields in the model.

Index Lookups

Indexing is also supported by the raw(), so the below query can be written for fetching the first result.

But, it becomes difficult to perform slicing and indexing at the level of the database. So it is good to limit the query at the level of the SQL if in the database we have a large number of objects of the person.

Deferring Model Fields

We can also leave some fields

The object of the Student returned by the above query is a deferred model instance. This means the fields are loaded on demand that is not present in the query.

If we look at this, then from appearance it is observed that the first name and last name are both retrieved from the database. But in the example a total of three queries are issued. raw() is used here only for retrieving the first name and then the last name is fetched from the database at the time of printing i.e. it is fetched on demand. The primary key field is a field that you can not leave as the primary key is used by Django for model instance identification. So it is mandatory to include the primary key field in the raw query. If someone forgets to include the primary key field in the raw query then this will lead to raising a FieldDoesNotExist exception.

Adding Annotations

Those queries can also be executed that contain those fields which are not defined in the model. For instance, like in the below lines of code PostgreSQL’s age() function is used for fetching the people list with their ages which are calculated based on birth_date stored in the database.

For the computation of the annotations, you can avoid using the raw SQL in place of the func() expression can also be used.

Passing Parameters Into Raw()

Param's arguments can also be used with raw for executing the parameterized queries.

Params are generally the dictionary or list of parameters. For the list %s placeholder can be used in the query string. Or for a dictionary % (key) s placeholder can be used, and the dictionary key replaces the key.

Executing Custom SQL Directly

There are some situations where using manager.raw() is not quite sufficient. Sometimes it is required to execute which does not do clear mappings with the models, or INSERT, UPDATE, or DELETE queries will be executed directly. In these situations, the database is accessed directly, by entirely routing around the model layer. The default database connection is represented by the django.db.connection. For utilization of the database connection, for fetching cursor objects call connection.cursor(). And then for query execution cursor.execute(sql, [params]) is called, and for returning the resultant rows of the query cursor.fetchone() or cursor.fetchall() is used.

Example of Using Custom SQL Directly

For protection from SQL injection, in the SQL string, quotes should not be included around the %s placeholders. For including a literal percent sign in the SQL string, you have to write it 2 times if you are passing arguments.

In this case, if you want to use more than one database, then for particular databases, for obtaining the connection (or cursor) django.db.connections is used. Django.db.connections is an object just like a dictionary that enables specific connection retrieval with the help of its alias Results returned by the Python DB API are by default without the name of their fields, which means that you can end up with a values list, instead of dict. Results can be returned as dict in small memory and performance cost in the way given below:

And one more option is using collections.namedtuple() from the standard library of python. A namedtuple is generally a tuple-like object, And in the namedtuple attribute, lookup is used for accessing fields. And it is also iterable and indexable. And results provides are immutable(i.e. unchangeable) and indices or field names are used for accessing

Connections and Cursors

A network connection with the database is called the connection() and the real-time use of this connection() is to return the cursor. Cursor allows the traversal of the data stored in the database. Standard Python DB-API defined in the PEP 249 is mostly implemented by cursor and connection except when there is transaction handling.

Calling Stored Procedures

For calling the procedure stored in the database the syntax given below is used. Procedures are stored in the database to increase the reusability of the code. And for calling the procedure syntax is given below:

Syntax:

Parameters: Stored procedures of the database can be called with a name and input parameters sequence(params) and (kparams)dictionary is provided. Django's built-in backends kparams are not supported by most of the databases, it is supported by oracle only. For instance, below is the Oracle database stored procedure.

It can be called: