In this post, we will see a list all the basic data operations like create, search, insert, update, delete etc. in a Django application.

Let us assume we have a table named employees in database and the model for it is Employee.

The fields for the table are:

 id, integer
 first_name, varchar
 last_name, varchar
 gender, varchar
 create_date, date

We also assume that we have one more table named Sales with following fields:

product_id, integer
employee_id, integer
amount float
  1. Creating a new employee with individual fields
    employee = Employee()
    
    employee.first_name = "Ashutosh"
    employee.last_name = "Pandey"
    employee.gender = "Male"
    employee.create_date = datetime.date.today().strftime("%Y-%m-%d")
    
    employee.save()
  2. Creating a new employee on a single line
    employee = Employee(first_name="Ashutosh", last_name="Pandey", gender="Male", create_date=datetime.date.today().strftime("%Y-%m-%d"))
    
    employee.save()
  3. Creating a new employee from form fields
    employee = Employee()
    
    employee.first_name = request.POST.get("first_name")
    employee.last_name = request.POST.get("last_name")
    employee.gender = request.POST.get("gender")
    employee.create_date = datetime.date.today
    
    employee.save()
  4. Reading all employees
    employees = Employee.objects.all()
  5. Finding single employee by id
    employee = Employee.objects.get(id=5)         # id is the name of primary key field
  6. Finding single employee by conditions
    employee = Employee.objects.filter(first_name="Ashutosh")[0]
    employee = Employee.objects.filter(first_name="Ashutosh").reverse()[0]
    employee = Employee.objects.filter(first_name="Ashutosh").latest('create_date')
    employee = Employee.objects.filter(first_name="Ashutosh").filter(last_name="Pandey")[0]
  7. Field lookups
    Django provides special keywords that can be applied to fields to give exact result. Field lookups can be used with following syntax:

    fieldname__lookuptype=value  (double underscore after fieldname)

    Here are some examples:
    __lt          =>    less than
    __lte        =>    less than or equal to
    __ge        =>    greater than
    __gte       =>    greater than or equal to
    __exact   =>    match as it is (case sensitive)
    __iexact  =>    match as it is (case in-sensitive)
    __contains => like query (case sensitive)
    __icontains => like query (case in-sensitive)
    __startswith => like query starting with specified string
    __istartswith => like query starting with specified string (case in-sensitive)
    __endswith => like query ending with specified string
    __iendswith => like query ending with specified string (case in-sensitive)

    employees = Employee.objects.filter(create_date__lte='2015-05-05')
    employees = Employee.objects.filter(create_date__gte='2015-05-05')
    employees = Employee.objects.filter(first_name__exact='Ashutosh')
    employees = Employee.objects.filter(first_name__iexact='Ashutosh')
    employees = Employee.objects.filter(first_name__contains='Ashu')
    employees = Employee.objects.filter(first_name__startswith='Ashu')
    employees = Employee.objects.filter(first_name__endswith='Pandey')
  8. Excluding records
    employees = Employee.objects.exclude(create_date__gt=datetime.date(2015,8,6), gender='Male')

    This will generate the query:
    select * from employees where not (create_date > ‘2015-8-6′ and gender=’Male’)

    employees = Employee.objects.exclude(create_date__gt=datetime.date(2015,8,6)).exclude(gender='Male')

    This will generate the query:
    select * from employees where not create_date > ‘2015-8-6′ and not gender=’Male’

  9. Ordering records
    employees = Employee.objects.filter(gender="Male").order_by('first_name')
    employees = Employee.objects.filter(gender="Male").order_by('first_name').order_by('last_name')
    employees = Employee.objects.filter(gender="Male").order_by('first_name').reverse()
  10. Aggregate functions
    count = Sale.objects.aggregate(Count('product_id'))
    sum = Sale.objects.filter(employee_id=5).aggregate(Sum('amount'))
    average = Sale.objects.filter(employee_id=5).aggregate(Avg('amount'))
    maximum = Sale.objects.all().aggregate(Max('amount'))
    minimum = Sale.objects.all().aggregate(Min('amount'))
  11. Limiting records
    employees = Employee.objects.filter(gender="Male")[:10]
  12. Limiting records with offset
    employees = Employee.objects.filter(gender="Male")[5:10]    # get records from 5 to 10
  13. Selecting specific columns
    employees = Employee.objects.filter(gender="Male").values_list('first_name', 'last_name')
  14. Deleting an employee
    employee = Employee.objects.get(id=1)
    employee.delete()
  15. Updating existing record
    employee = Employee.objects.get(id=1)
    employee.first_name = "Amitabh"
    employee.last_name = "Bachhan"
    employee.save()