How does the Django ORM generates SQL?
How does the Django ORM generates SQL?
Django ORM provides the user with many amazing tools to run raw SQL queries, for instance:
● QuerySet API is for extensive use.
● The user can create a custom query expression.
● The user can also simply comment and aggregate using the many built-in database functions.
Before you begin performing raw queries on Django ORM, it is better to explore the ORM and get used to the application.
Performing Raw SQL Queries on Django ORM:
Django Orm offers two different methods of performing raw SQL queries, first being the utilization of Manager.raw(), this is best to simply perform the raw queries and returning model instances. Using the second method, execution of custom SQL directly will allow the user to skip the model layering entirely and just get on with the SQL queries without much of a hassle.
Performing raw Queries by Manager.raw(), Method:
The Manager.raw(), the method is utilized to execute raw SQL queries that in the end return model instances.
Manager.raw(raw_query, params=None, translations=None)
This way of executing raw SQL queries has the procedure to first take a raw query, perform it, and then return a django.db.models.query.RawQuerySet instance.
This obtained RawQuerySet instance can generally be repeated or emphasized over a normal QuerySet to implement object instances very easily.
The best example of this method is as follows:
For instance, think that you have this model;
For executing custom SQL queries, you can simple do this:
>>>forpinPerson.objects.raw('SELECT * FROM myapp_person'):
While this example for sure is not the most exciting one, but this works the best and explains the method is a clear and easy way. It is all exactly as running a Person.objects.all(). Nevertheless, raw() have a variety of settings that makes it incredibly useful and great.
Mapping Query Areas to Model Fields:
The raw() is a function that automatically fills and maps the fields in the inquiry to all the fields and areas in the model. The order of the fields in the query does not really matter as the function is still executable without a certain order.
All this means, that both of the following examples of queries are working identically:
>>>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')
Finding the similarities and matchings are done by the names provided. The user can utilize SQL's AS strings and requirements to fill the fields in the query to the model. In this regard, if the user has other data on the table that has Person data on it, the user can with much ease, map that into the Person instances:
>>>Person.objects.raw('''SELECT first AS first_name,
... last AS last_name,
... bd AS birth_date,
... pk AS id,
... FROM some_other_table''')
Performing Custom SQL Directly:
For the execution of a more thorough mapping in the models or to directly perform UPDATE, INSERTandDELETE queries, Manager.raw() is not really enough and you might need a more direct approach to the whole SQL queries.
In situations such as these, utilizing the direct method of SQL queries is great, this way the user is completely disregarding the model layer.
The function django.db.connection is used to present the general database connections. To utilize this database connection, one can call connection.cursor() to obtain a certain cursor. After that, the user can call the cursor.execute(SQL, [params]) to perform the raw SQL queries and to cursor.fetchone() or either the cursor.fetchall() functions to bring the resulting rows back.
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s",[self.baz])
cursor.execute("SELECT foo FROM bar WHERE baz = %s",[self.baz])
In the SQL queries strings, if you place quotation marks around the percentages (%) placeholders, it is going to cause problems in the program's execution. So, it is always better to recheck your quotes markings.