In this post, we will see how to work with database. For that, we will create models, migrations and then migrate to create database tables.

First we should know what a model is. Models are Python classes that talk to database, store and validate data, perform business logic etc. They allow us to interact with database without using SQL queries. There are lot of pre-defined methods that helps in creating, updating, deleting and reading records from database and return them as objects. This makes developer’s life much easy.

Django also support relationship between models. For example, if you try to read employee object from database, you can also get sale object as well. We don’t have to write any table joins for this.

Let us create an example and see how models work. Here are the steps for this:

  1. We will be using SQLite database which is  provided as default.
  2. Now, create a new Django project. Click here for how to.
  3. Create a new file named models.py inside the app folder.
  4. In this example, we will create two models: employee and sale. Add following code to the models.py file:
    from django.db import models
    
    class Employee(models.Model):
        id = models.IntegerField(primary_key=True)
        first_name = models.CharField(max_length=255)
        last_name = models.CharField(max_length=255)
        gender = models.CharField(max_length=6)
        create_date = models.DateField()
    
    class Sale(models.Model):
        product_id = models.IntegerField()
        employee_id = models.IntegerField()
        amount = models.FloatField()

    All our model classes subclass the Model class and defines fields we wish to use in those models.

  5. Next, we specify our database settings. We will be using MySQL database in this example. Open the app\settings.py file and update the DATABASES section like this:
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': os.path.join(BASE_DIR, 'mydb.sqlite3'),
        }
    }
  6. Now its time to migrate our models. Migration reads our model classes and automatically creates tables in the database. Start command prompt and move into your project directory. Execute the command:
    python manage.py syncdb

    This should create tables in your database.
    If you are using PyCharm, go to Tools -> Run manage.py Task -> syncdb

  7. To test our models and performing CRUD operations, we will be using python shell. Execute the following command:
    python manage.py shell

    This will start an interactive shell on which we can execute our commands.
    If you are using PyCharm, go to Tools -> Python Console

  8. You should see the shell like this:
    >>>
  9. Now, we can test our database commands like this:
    >>> from app.models import Employee
    >>> import datetime
    >>> employee = Employee(first_name="Ashutosh", last_name="Pandey", gender="Male", create_date=datetime.date.today().strftime("%Y-%m-%d"))
    >>> employee.save()

    If you are using PyCharm, you have to execute following commands first before executing the above ones:

    from django.conf import settings
    settings.configure(DEBUG=True)
  10. For more database operations, please follow the link below
    CRUD operations in Django
Basic CRUD example

Let us create a basic example where we will see how to perform following actions:

  1. Reading existing employees
  2. Create a new employee
  3. Updating an existing employee
  4. Deleting an existing employee

Here are the steps we are going to perform:

  1. Create a new file named employee.py inside the app\views folder
    app\views\employee.py
  2. Create following six methods in it:
    list, create, save, edit, update, remove
  3. Here is the complete employee.py file:
    from django.shortcuts import render, redirect
    from app.models import Employee
    import time
    
    def list(request): 
       employees = Employee.objects.all()
       if not employees:
            found = len(employees)>0
       else:
            found = False
       return render(request, 'employee/list.html', {'found' : found, 'employees' : employees})
    
    def create(request):
       return render(request, 'employee/create.html')
    
    def save(request):
        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.status = 'active'
        employee.create_date = time.strftime('%Y-%m-%d')
    
        employee.save()
    
        return redirect('/list-employees')
    
    def remove(request, employee_id):
       employee = Employee.objects.get(id=employee_id)
       employee.delete()
       return redirect('/list-employees')
    
    def edit(request, employee_id):
        employee = Employee.objects.get(id=employee_id)
        request.session['employee_id'] = employee_id   # put employee to edit in session
        return render(request, 'employee/edit.html', {'employee': employee})
    
    def update(request):
       employee = Employee.objects.get(id=request.session['employee_id'])
       if employee:
            employee.first_name = request.POST.get('first_name')
            employee.last_name = request.POST.get('last_name')
            employee.gender = request.POST.get('gender')
    
            return redirect('/list-employees')
       else:
           return redirect('/error')
  4. Now, create a new folder named empoyee inside the templates folder. In this folder, we will create following template files:
    list.html, create.html, edit.html
  5. Here is the source code for all the template files:
    list.html

    <!DOCTYPE html>
    <html>
    <head>
        <title>
            Create new employee
        </title>
    </head>
    <body>
    
        <a href="create-employee">Create new employee</a>
        <hr/>
    
        {%  if employees and employees|length > 0  %}
            <table style="width: 800px;">
                <thead>
                    <tr>
                        <th style="width:35%; text-align:left">First name</th>
                        <th style="width:35%; text-align:left">Last name</th>
                        <th style="text-align: left">Email</th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>
                {% for employee in employees  %}
                    <tr>
                        <td>{{ employee.first_name }}</td>
                        <td>{{ employee.last_name }}</td>
                        <td>{{ employee.email }}</td>
                        <td>
                            <a href="edit-employee/{{ employee.id }}">Edit</a>
                            <a href="remove-employee/{{ employee.id }}">Remove</a>
                        </td>
                    </tr>
                {% endfor %}
                </tbody>
            </table>
        {% else %}
            No records found
        {% endif %}
    
    </body>
    </html>

    create.html

    <!DOCTYPE html>
    <html>
    <head>
        <title>
            Create new employee
        </title>
    
    </head>
    <body>
    
        <form method="post" action="/save-employee">
            {% csrf_token %}
            First name : <input name="first_name" type="text"/><br/>
            Last name : <input name="last_name" type="text"/><br/>
            Email : <input name="email" type="email"/><br/>
    
            <input value="Create" type="submit"/>
        </form>
    
    </body>
    </html>

    edit.html

    <!DOCTYPE html>
    <html>
    <head>
        <title>
            Edit employee
        </title>
    
    </head>
    <body>
    
        <form method="post" action="/update-employee">
            {% csrf_token %}
            First name : <input name="first_name" type="text" value="{{ employee.first_name }}"/><br/>
            Last name : <input name="last_name" type="text" value="{{ employee.last_name }}"/><br/>
            Email : <input name="email" type="email" value="{{ employee.email }}"/><br/>
    
            <input value="Update" type="submit"/>
        </form>
    
    </body>
    </html>
  6. Lastly, add following URLs to your urls.py file:
    url(r'^create-employee', employee.create),
    url(r'^save-employee', employee.save),
    url(r'^edit-employee/(?P<employee_id>\d+)', employee.edit),
    url(r'^update-employee', employee.update),
    url(r'^remove-employee/(?P<employee_id>\d+)', employee.remove),
    url(r'^list-employees', employee.list),
  7. Now run your server and visit the URL:
    http://localhost:8000/list-employees