Django Simple CRUD Operation Example

In the previous article, we have learned How To Manage Models In Django Admin Site. In this article, we will tell you how to process CRUD(Create, Retrieve, Update and Delete) operations on those models to manipulate data in the backend database table. All the data manipulation is processed through model functions, you do not need to run any SQL command at all.

This example is based on the How To Manage Models In Django Admin Site article. There are two model Department and Employee in the example, and the related SQLite database table name is user_register_login_department and user_register_login_employee.

1. Django CRUD In Command Shell.

1. Django provides a command shell to execute CRUD operations. So first you should run into the shell environment follow the below steps.
2. Open a terminal and cd into the Django project root folder.
3. Execute $ python3 manage.py shell command to go into the shell interface.

   192:DjangoHelloWorld zhaosong$ python3 manage.py shell
   Python 3.6.5 |Anaconda, Inc.| (default, Apr 26 2018, 08:42:37) 
   Type 'copyright', 'credits' or 'license' for more information
   IPython 6.4.0 -- An enhanced Interactive Python. Type '?' for help.
   Caching the list of root modules, please wait!
   (This will only be done once - type '%rehashx' to reset cache!)

4. Then you can execute the python model CRUD operation code.

2. Insert Model Data Into The Database.

1. Insert Department model data.

   In [2]: from user_register_login.models import Department
   In [7]: dept = Department(dept_name='Quality Assurance', dept_desc='Website test and use case design.')
   In [8]: dept.save()
   

2.  You can also use create a method to create a department model object and insert the data to the backend database table like below.

   Department.objects.create(dept_name='Market', dept_desc='Website market plan definition.')
   <Department: Market,Website market plan definition.>

3. Insert Employee model data. The Employee model has two foreign key fields which should be User and Department model instance. So if you run the below command to insert Employee data, you will get errors.

   In [10]: from datetime import datetime
   
   In [11]: emp = Employee(user='tom',dept='Quality Assurance', emp_mobile='1381998982289',emp_salary=8000, emp_onboard_date=datetime(2018,9,9,12,0,0))
   ---------------------------------------------------------------------------
   ValueError                                Traceback (most recent call last)
   <ipython-input-11-d493c8a6bf28> in <module>()
   ----> 1 emp = Employee(user='tom',dept='Quality Assurance', emp_mobile='1381998982289',emp_salary=8000, emp_onboard_date=datetime(2018,9,9,12,0,0))
   
   ~/anaconda3/lib/python3.6/site-packages/django/db/models/base.py in __init__(self, *args, **kwargs)
       464                 # checked) by the RelatedObjectDescriptor.
       465                 if rel_obj is not _DEFERRED:
   --> 466                     _setattr(self, field.name, rel_obj)
       467             else:
       468                 if val is not _DEFERRED:
   
   ~/anaconda3/lib/python3.6/site-packages/django/db/models/fields/related_descriptors.py in __set__(self, instance, value)
       208                     instance._meta.object_name,
       209                     self.field.name,
   --> 210                     self.field.remote_field.model._meta.object_name,
       211                 )
       212             )
   
   ValueError: Cannot assign "'tom'": "Employee.user" must be a "User" instance.
   
   

4. Below is the correct python source code to insert employees with a foreign key model instance.

   In [12]: from datetime import datetime
   In [13]: from django.contrib.auth.models import User
   In [14]: from user_register_login.models import Department, Employee
            # get User object that username is tom
   In [15]: user = User.objects.get(username='tom')
            # get department object that dept_name is Quality Assurance.
   In [17]: dept = Department.objects.get(dept_name='Quality Assurance')
   In [18]: emp = Employee(user=user, dept=dept, emp_mobile='1381998982289',emp_salary=8000, emp_onboard_date=datetime(2018,9,9,12,0,0))
   In [19]: emp.save()
   

3. Select Model Data From Database.

1. Select all model data objects: model_name.objects.all().

   In [20]: from user_register_login.models import Department, Employee
   
   In [21]: Department.objects.all()
   Out[21]: <QuerySet [<Department: Development,Develop dev2qa.com website use Django>, <Department: Quality Assurance,Website test and use case design.>]>
   
   In [22]: Employee.objects.all()
   Out[22]: <QuerySet [<Employee: jerry,18000,1390128889898>, <Employee: kevin,1000,139012988987>, <Employee: tom,8000,1381998982289>]>
   

2. Select single model data object: model_name.objects.get(field_name=field_value). If the data do not exist, you will get a DoesNotExist exception. If there are more than one model data that match the condition, then you will get a MultipleObjectsReturned exception.

   In [23]: Department.objects.get(dept_name='Develop')
   ---------------------------------------------------------------------------
   DoesNotExist                              Traceback (most recent call last)
   <ipython-input-23-be4c8470d9ee> in <module>()
   ----> 1 Department.objects.get(dept_name='Develop')
   
   ~/anaconda3/lib/python3.6/site-packages/django/db/models/manager.py in manager_method(self, *args, **kwargs)
        80         def create_method(name, method):
        81             def manager_method(self, *args, **kwargs):
   ---> 82                 return getattr(self.get_queryset(), name)(*args, **kwargs)
        83             manager_method.__name__ = method.__name__
        84             manager_method.__doc__ = method.__doc__
   
   ~/anaconda3/lib/python3.6/site-packages/django/db/models/query.py in get(self, *args, **kwargs)
       397             raise self.model.DoesNotExist(
       398                 "%s matching query does not exist." %
   --> 399                 self.model._meta.object_name
       400             )
       401         raise self.model.MultipleObjectsReturned(
   
   DoesNotExist: Department matching query does not exist.
   
   In [24]: Department.objects.get(dept_name='Development')
   Out[24]: <Department: Development,Develop dev2qa.com website use Django>

3. Use filter function to select model data objects by condition : model_name.objects.filter(field_name__contains=part_field_value). The filter method is similar as like statement in SQL command. It will perform condition search, if no data match then return an empty list.

   In [27]: Department.objects.filter(dept_name__contains='Development')
   Out[27]: <QuerySet [<Department: Development,Develop dev2qa.com website use Django>]>
   
   In [28]: Department.objects.filter(dept_name__contains='Market')
   Out[28]: <QuerySet []>
   
   In [29]: Department.objects.filter(dept_name__contains='Quality')
   Out[29]: <QuerySet [<Department: Quality Assurance,Website test and use case design.>]>

4. Use the filter function to select model data by multiple conditions:

   Department.objects.filter(id=1, dept_desc__contains='dev2qa')
   <QuerySet [<Department: Development,Develop dev2qa.com website use Django>]>

5. You can also use multiple filter functions to build a filter chain to select data by multiple conditions:

   Department.objects.filter(dept_desc__contains='dev2qa').filter(id=1)
   <QuerySet [<Department: Development,Develop dev2qa.com website use Django>]>

6. The exclude function can be used to exclude specified data records from the return model data result:

   Department.objects.filter(dept_desc__contains='dev2qa').exclude(id=1)
   <QuerySet [<Department: Quality Assurance,Test dev2qa.com web site use python and webdriver.>]>

7. The order_by function can help you to sort the query result data with the specified model field in ascending or descending order:

   # order by one model field in ascending order.
   Department.objects.order_by('id')
   <QuerySet [<Department: Development,Develop dev2qa.com website use Django>, <Department: Quality Assurance,Test dev2qa.com web site use python and webdriver.>, <Department: Market,Website market plan definition.>]>
   
   
   # sort query result in descending order. 
   >>> Department.objects.order_by('-id')
   <QuerySet [<Department: Quality Assurance,Test dev2qa.com web site use python and webdriver.>, <Department: Market,Website market plan definition.>, <Department: Development,Develop dev2qa.com website use Django>]>
   
   # order by multiple model field.
   Department.objects.order_by('dept_name', 'id')
   <QuerySet [<Department: Development,Develop dev2qa.com website use Django>, <Department: Market,Website market plan definition.>, <Department: Quality Assurance,Test dev2qa.com web site use python and webdriver.>]>
   # order the filter result data by dept_name field
   Department.objects.filter(dept_desc__contains='dev2qa').order_by('dept_name')
   <QuerySet [<Department: Development,Develop dev2qa.com website use Django>, <Department: Quality Assurance,Test dev2qa.com web site use python and webdriver.>]>
   

8. order_by function chain is also allowed to sort query result data by multiple model fields:

   # another way to order the result by multiple model field.
   Department.objects.order_by('id').order_by('dept_name')
   <QuerySet [<Department: Development,Develop dev2qa.com website use Django>, <Department: Market,Website market plan definition.>, <Department: Quality Assurance,Test dev2qa.com web site use python and webdriver.>]>

4. Update Model Data To Database.

1. Update single model data.

   In [1]: from user_register_login.models import Department,Employee
   In [2]: dept = Department.objects.get(dept_name='Development')
   In [3]: dept.dept_desc = 'This department role is website develop'
   In [5]: dept.save()
   

2. Batch update.

   In [6]: Employee.objects.select_for_update().filter(emp_mobile__contains=39).update(emp_salary=80000)
   Out[6]: 2
   

5. Delete Model Data From Database.

1. Delete single model data.

   In [11]: emp = Employee.objects.get(emp_salary=8000)
   In [12]: emp.delete()
   Out[12]: (1, {'user_register_login.Employee': 1})

2. Batch delete.

   In [15]: Employee.objects.select_for_update().filter(emp_salary__contains=8000).delete()
   Out[15]: (2, {'user_register_login.Employee': 2})