Django Simple CRUD Operation Example

In previous article, we have learnt 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 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.

two django model mapped sqlite db table

1. Django CRUD In Command Shell.

Django provide a command shell to execute CRUD operations. So first you should run into the shell environment follow below steps.

  1. Open a terminal and cd into the Django project root folder.
  2. 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!)
  3. Then you can execute python model CRUD operation code.

2.  Insert Model Data Into 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()
    

    insert a new department in django model database

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

    Department.objects.create(dept_name='Market', dept_desc='Website market plan definition.')
    <Department: Market,Website market plan definition.>
  2. Insert Employee model data.
    The Employee model has two foreign key fields which should be User and Department model instance. So if you run 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.
    
    

    Below is the correct python source code to insert employee with 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()
    

    insert tom in django employee model

2. 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 DoesNotExist exception. If there are more than one model data which match the condition, then you will get 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 filter function to select model data by multiple condition :
    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 function to build filter chain to select data by multiple condition:
    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 record 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 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.>]>

3. 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
    

4. 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})
READ :   How To Pass Multiple Parameters Via Url In Django

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.