Django Querysets | One To One Foreign Key, Inner Joins, Query Filtering
Django provides a data abstraction layer between models and database tables. Querysets are used to retrieve data from tables and other save, update and delete operations.
There is various queryset function provided by Django to satisfy needs. Querysets are python objects which include information about that particular Course.objects.create.
In this post, we’ll be discussing the basics of Querysets
Table of Contents
- Creating a new Object
- Accessing Objects
- Updating Objects
- Deleting Objects
- OneToOne relationships between models
- Query Filter and Mapping
- Inner Join
- Conclusion
By taking an example of students, courses and subjects are tables. First, we’ll create models and migrate to database.
In our app named query_set has models.py file. if doesn’t exist then create one.
from django.db import models
class Course(models.Model):
id=models.AutoField(primary_key=True)
course_name=models.CharField(max_length=200)
course_desc=models.TextField(null=True)
created_at=models.DateTimeField(auto_now_add=True)
updated_at=models.DateTimeField(auto_now=True)
class Meta:
db_table="courses"
verbose_name_plural="Courses"
def __str__(self):
return self.course_name
class Subject(models.Model):
id=models.AutoField(primary_key=True)
course=models.ForeignKey(Course,related_name="subject_course",on_delete=models.CASCADE)
subject_name=models.CharField(max_length=200)
created_at=models.DateTimeField(auto_now_add=True)
updated_at=models.DateTimeField(auto_now=True)
class Meta:
db_table="subjects"
verbose_name_plural="Subjects"
def __str__(self):
return self.subject_name
class Student(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=200)
course=models.ForeignKey(Course,related_name="course",on_delete=models.CASCADE)
joining_date=models.DateField()
created_at = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)
class Meta:
db_table="students"
verbose_name_plural="Students"
def __str__(self):
return self.name
Course model is an independent model which has course_name as the mandatory field next, we have Subject the model which has a ForeignKey relationship with Course model
as a particular course may have multiple subjects and Student model has name,course and joining_date as mandatory fields and has ForeignKey relationship with Course model
as many students may take up some courses. We’ll not be going in a detailed explanation of creating models and model fields in this topic we’ll be focussing on make queries.
Creating a new Object
Let us include models in our python shell. Go to root project and type python manage.py shell in terminal.
>>> from query_sets.models import Course, Subject, Student >>> course = Course.objects.create(course_name="BCA",course_desc="Bachelor of Computer Application") >>> print(course) BCA
Path to models can be given through from .models import ,. We have called Course.objects.create() method where Course in model and objects is a Default Manager for model Course.
For every model, there is at least one manager by default. Finally `create()` method within `objects` manager which creates a new row in courses table and returns that object.
When we print(course) displays name format as specified in __str__(self) a method in Course model.
Accessing Objects
Querysets has methods of accessing objects from the table and they are get() and filter() method.
get()– The method returns a single object useful in getting a particular object by primary key.filter()– The method returns list-objects useful which retrieve by a set of conditions.
Here we’ll be using get() to retrieve already saved BCA course.
>>>from query_sets.models import Course, Subject, Student >>>course = Course.objects.get(id=1) >>>print(course) BCA >>>print(course.course_desc) Bachelor of Computer Application
Using dot( . ) operator we can access fields of Course Model. The get() the method takes keyword argument here the keyword argument is id equals to.
Insisted explicitly specifying id we can specify pk which means as primary_key of that particular model Course.objects.get(pk=1) which also returns the course object.
Accessing object by filter() method.
>>> from query_sets.models import Course, Subject, Student >>> course = Course.objects.filter(pk=1) >>> print(course) <QuerySet [<Course: BCA>]>
The filter() methods return list objects which has a single object. This method is useful in retrieving a list of data and returns empty QuerySet list if filter conditions are not matched.
Whereas get() method if object by id not found then an exception is raised query_sets.models.DoesNotExist: Course matching query does not exist.
>>> course = Course.objects.filter(pk=10) >>> print(course) <QuerySet []> >>> course = Course.objects.get(pk=10) Traceback (most recent call last): query_sets.models.DoesNotExist: Course matching query does not exist.
Updating Objects
Updating objects is easier simply call .save() on the object, if the primary key exists then object get updated else if the primary key is None the new object is created.
Let us try to update course_desc of Course model.
from query_sets.models import Course, Subject, Student >>> course = Course.objects.get(pk=1) >>> print(course.course_desc) Bachelor of Computer Application >>> course.course_desc = "BCA fullform (Bachelor of Computer Application) is a 3 year course which provides basic knowledge of computer programming and application" >>> course.save() #this updates the database
Deleting Objects
>>> course = Course.objects.get(pk=1)
>>> print(course)
BCA
>>> course.delete()
(1, {'query_sets.Subject': 0, 'query_sets.Course': 1, 'query_sets.Student': 0})
Each QuerySet object carries .delete() method and when called the object get deleted from the database. course.delete() returns a tuple which has information related mapped records being deleted.
OneToOne and ForeignKey relationships between models
OneToOne Relationship
This relationship can be set in the model. We need to specify other model names which will be mapped to this model. By default, unique=True is enabled which means that there must be only one unique field of that particular column in the table. If a duplicate is detected than exception django.db.utils.IntegrityError: (1062, “Duplicate entry”) is raised.
ManyToMany Relationship
This relationship is shared a lot of similarities with OneToOne Relationship but the only difference is it doesn’t have unique attribute set to True.
Query Filter and Mapping Objects
Now let us insert subjects for our course BCA.
>>> course = Course.objects.get(pk=1) >>> subject_1=Subject.objects.create(course=course,subject_name="C Programming") >>> subject_2=Subject.objects.create(course=course,subject_name="DataStructures using C") >>> subject_3=Subject.objects.create(course=course,subject_name="Visual Programming")
First, we’ll get single course object from Course Model and while creating subjects insisted of passing course_id to create method we can directly pass course object and Django it will map newly created subject to that course.
Django model API provides more features other than retrieving and creating objects. In Subject the model we have specified field course as a ForeignKey relationship with Course Model.
In other frameworks, we must explicitly declare relationships in the model. But Django will get course details from subject-object.
Now access the course through subject
from query_sets.models import Course, Subject, Student >>> subject_1=Subject.objects.get(pk=1) >>> subject_1 <Subject: C Programming> >>> subject_1.course <Course: BCA>
subject_1.course return course object which is mapped to that particular subject-object.
Let us take this even further and add some more courses and subjects so that we can have plenty of data to query.
>>> course = Course.objects.create(course_name="BBA") >>> print(course) BBA >>> subject_1 = Subject.objects.create(course=course,subject_name="Business Administration") >>> subject_2 = Subject.objects.create(course=course,subject_name="Economics") >>> subject_3 = Subject.objects.create(course=course,subject_name="Accounting") >>> subject_4 = Subject.objects.create(course=course,subject_name="Introduction to Taxation") >>> subject_5 = Subject.objects.create(course=course,subject_name="Business Marketing")
Now there is a new course BBA which has five different subjects.
Retrieving subjects of a particular course
>>> subjects=Subject.objects.filter(course__course_name__exact="BBA") >>> print(subjects) <QuerySet [<Subject: Business Administration>, <Subject: Economics>, <Subject: Accounting>, <Subject: Introduction to Taxation>, <Subject: Business Marketing>]> # or other way >>> subjects_1=Subject.objects.filter(course=Course.objects.get(pk=1)) >>> print(subjects_1) <QuerySet [<Subject: C Programming>, <Subject: DataStructures using C>, <Subject: Visual Programming>]> >>>
By querying we get Queryset list of subjects which belong to course BBA.in above example we have specified two ways of retrieving data but they always return the same queryset.
Note
__excat and = are similarly were =(equal to ) calls __excat explicitly.
Next is to add students to course. A student must be enrolled in only one course and there will be many numbers of students who are enrolled in the same course.
So there is ForeignKey relationship between Course and Student model.
from datetime import datetime, date
>>> bba_course = Course.objects.get(course_name__contains="BBA")
>>> bca_course = Course.objects.get(course_name__contains="BCA")
students_list = [
{'name':"Rakshit", 'course':bba_course, 'joining_date':"2018-02-21"},
{'name':"Amar", 'course':bba_course, 'joining_date':"2019-03-05"},
{'name':"Suresh", 'course':bca_course, 'joining_date':"2019-05-15"},
{'name':"Kiran", 'course':bca_course, 'joining_date':"2018-02-05"},
{'name':"Vasudev", 'course':bca_course, 'joining_date':"2018-06-18"},
{'name':"Prakash", 'course':bba_course, 'joining_date':"2018-08-12"},
{'name':"Naresh", 'course':bba_course, 'joining_date':"2018-11-25"},
];
for student in students_list:
students_data = Student.objects.create(**student)
We can also create objects using a dictionary. The students_list is a list which has student data as dictionary iteration student list over
Student.objects.create(**student) inserts a new record to in each iteration. In our tables, we have added four students from BBA and three students from BCA.
Get subjects taken by a student
from query_sets.models import Course, Subject, Student >>> student_course = Student.objects.get(pk=2) >>> print(student_course) Amar >>> print(student_course.course) BBA >>> Subject.objects.filter(course=student_course.course) <QuerySet [<Subject: Business Administration>, <Subject: Economics>, <Subject: Accounting>, <Subject: Introduction to Taxation>, <Subject: Business Marketing>]>
Get subjects of all students
students = Student.objects.all()
for student in students:
student.subjects = Subject.objects.filter(course=student.course)
print(students)
>>> print(students)
<QuerySet [<Student: Rakshit>, <Student: Amar>, <Student: Suresh>, <Student: Kiran>, <Student: Vasudev>, <Student: Prakash>, <Student: Naresh>]>
>>> print(students[0].subjects)
<QuerySet [<Subject: Business Administration>, <Subject: Economics>, <Subject: Accounting>, <Subject: Introduction to Taxation>, <Subject: Business Marketing>]>
>>> print(students[2].subjects)
<QuerySet [<Subject: C Programming>, <Subject: DataStructures using C>, <Subject: Visual Programming>]>
>>>
Inner Join
It is possible to join two tables in Django. For this, we make use of the select_related() method.
>>> a1=Subject.objects.select_related('course')
>>> print(a1)
<QuerySet [<Subject: C Programming>, <Subject: DataStructures using C>, <Subject: Visual Programming>, <Subject: Business Administration>, <Subject: Economics>, <Subject: Accounting>, <Subject: Introduction to Taxation>, <Subject: Business Marketing>]>
>>> print(a1.query)
SELECT `subjects`.`id`, `subjects`.`course_id`, `subjects`.`subject_name`, `subjects`.`created_at`, `subjects`.`updated_at`, `courses`.`id`, `courses`.`course_name`, `courses`.`course_desc`, `courses`.`created_at`, `courses`.`updated_at` FROM `subjects` INNER JOIN `courses` ON (`subjects`.`course_id` = `courses`.`id`)
>>> a2=Subject.objects.filter(course__course_name__exact="BBA")
>>> print(a2)
<QuerySet [<Subject: Business Administration>, <Subject: Economics>, <Subject: Accounting>, <Subject: Introduction to Taxation>, <Subject: Business Marketing>]>
>>> print(a2.query)
SELECT `subjects`.`id`, `subjects`.`course_id`, `subjects`.`subject_name`, `subjects`.`created_at`, `subjects`.`updated_at` FROM `subjects` INNER JOIN `courses` ON (`subjects`.`course_id` = `courses`.`id`) WHERE `courses`.`course_name` = BBA
>>>
Subject.objects.select_related('course') return a list of queryset objects where course_id matches to both tables. We can view raw MySQL query by calling .query property to object a1.
In the next example, we filter subjects by course_name using double underscore ( __ ).
First, we specify <model_name in small letters>__<model field name>__<query attributes such as exact, contain or etc>.
Note
You can find complete documentation regarding Django querysets
Conclusion
Till here we have the lead foundation for Querysets Fundamentals to learn more go to next part 2 of QuerySet Fundamentals. If you have any doubts please mention in the comment section and we’ll reach you soon and we would also love to hear requests and your recommendations for new tutorials/posts.
Related Posts
- Python Django Projects | Create a Todo Web Application
- Python Django – Multiple Files Validation and Uploads
