❮ Django Form Django Model ❯

Django ORM - Single Table Example

Before reading this section, you should have read the Django Models to understand basic configurations and common problem solutions.

Next, we will recreate a project named app01 (if you have already created it, skip the following steps):

django-admin.py startproject app01

Then, find the INSTALLED_APPS entry in settings.py as follows:

INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'app01',               # Add this line
)

Next, tell Django to use the pymysql module to connect to the MySQL database:

Example

# Import modules and configure in the __init__.py file at the same level as settings.py
import pymysql
pymysql.install_as_MySQLdb()

Create Model

Add the following class in models.py in your project:

app01/models.py

class Book(models.Model): 
    id = models.AutoField(primary_key=True) # id will be automatically created, can be manually entered
    title = models.CharField(max_length=32) # Book title
    price = models.DecimalField(max_digits=5, decimal_places=2) # Book price
    publish = models.CharField(max_length=32) # Publisher name
    pub_date = models.DateField() # Publication date

Then execute the following commands in the command line:

$ python3 manage.py migrate   # Create table structure

$ python3 manage.py makemigrations app01  # Inform Django that there are some changes in our models
$ python3 manage.py migrate app01   # Create table structure

Common Error Messages

If you encounter the following error message when executing the above commands:

The reason is that MySQLclient currently only supports up to Python 3.4. Therefore, if you are using a higher version of Python, you need to modify as follows:

Find the base.py file in the path ...site-packages\Django-2.0-py3.6.egg\django\db\backends\mysql and comment out these two lines of code (located at the beginning of the file):

if version < (1, 3, 13):
    raise ImproperlyConfigured('mysqlclient 1.3.13 or newer is required; you have %s.' % Database.__version__)

Typically, clicking on the error code file path information will automatically jump to the line number in the error file, where we comment out the error line.

At this point, the database tutorialpro will create a table named app01_book.

Next, we add views.py and models.py files in the app01 project. The directory structure of the app01 project is as follows:

app01
|-- app01
|   |-- __init__.py
|   |-- __pycache__
|   |-- asgi.py
|   |-- migrations
|   |-- models.py
|   |-- settings.py
|   |-- urls.py
|   |-- views.py
|   `-- wsgi.py

Database Addition

Configuration rules:

app01/urls.py: File code:

from django.contrib import admin
from django.urls import path
from . import views

urlpatterns = [
    path('add_book/', views.add_book),
]

Method One: Model class instantiation

Import the models.py file from the app directory:

```python
from app directory import models

And after instantiating an object, you must execute object.save() for it to be successfully added to the database.

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    book = models.Book(title="tutorialpro.org", price=300, publish="tutorialpro", pub_date="2008-8-8")
    book.save()
    return HttpResponse("<p>Data added successfully!</p>")

Method 2: Using the create method provided by ORM's objects (recommended)

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.create(title="如來神掌", price=200, publish="功夫出版社", pub_date="2010-10-10")
    print(books, type(books))  # Book object (18)
    return HttpResponse("<p>Data added successfully!</p>")

Searching

Use the all() method to query all content.

The return is a QuerySet type data, similar to a list, containing individual model class objects, which can be accessed by index.

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.all()
    print(books, type(books))  # QuerySet type, similar to list, data displayed in the command line when accessing the URL.
    return HttpResponse("<p>Search successful!</p>")

filter() method is used to query data that meets the conditions.

The return is a QuerySet type data, similar to a list, containing model class objects that meet the conditions, which can be accessed by index.

pk=3 means primary key primary key=3, equivalent to id=3.

Since id has a special meaning in PyCharm as a built-in function to view memory address id(), pk is used instead.

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.filter(pk=5)
    print(books)
    print("//////////////////////////////////////")
    books = models.Book.objects.filter(publish='tutorialpro', price=300)
    print(books, type(books))  # QuerySet type, similar to list.
    return HttpResponse("<p>Search successful!</p>")

exclude() method is used to query data that does not meet the conditions.

The return is a QuerySet type data, similar to a list, containing model class objects that do not meet the conditions, which can be accessed by index.

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.exclude(pk=5)
    print(books)
    print("//////////////////////////////////////")

books = models.Book.objects.exclude(publish='tutorialpro', price=300)
print(books, type(books))  # QuerySet type, similar to list.
return HttpResponse("<p>Search successful!</p>")

get() method is used to query the model class object that matches the condition. The object matching the condition must be unique; if there is more than one or none, an error will be thrown.

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.get(pk=5)
    books = models.Book.objects.get(pk=18)  # Error, no matching object
    books = models.Book.objects.get(price=200)  # Error, more than one matching object
    print(books, type(books))  # Model class object
    return HttpResponse("<p>Search successful!</p>")

order_by() method is used to sort the query results.

It returns QuerySet type data, similar to a list, containing sorted model class objects, which can be accessed by index.

Note:

a. The field name in the parameter must be in quotes.
b. Descending order is achieved by adding a hyphen - before the field name.

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.order_by("price")  # Query all, ascending order by price
    books = models.Book.objects.order_by("-price")  # Query all, descending order by price
    return HttpResponse("<p>Search successful!</p>")

reverse() method is used to reverse the query results.

It returns QuerySet type data, similar to a list, containing reversed model class objects, which can be accessed by index.

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    # Ascending order by price: descending order then reverse
    books = models.Book.objects.order_by("-price").reverse()
    return HttpResponse("<p>Search successful!</p>")

count() method is used to query the number of data entries. The returned data is an integer.

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.count()  # Query the total number of data entries
    books = models.Book.objects.filter(price=200).count()  # Query the number of data entries matching the condition
    return HttpResponse("<p>Search successful!</p>")

first() method returns the first data entry. The returned data is a model class object and can also be accessed by index [0].

app01/views.py: File code:

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.first()  # Returns the first data entry of all data
    return HttpResponse("<p>Search successful!</p>")

last() method returns the last data entry. The returned data is a model class object and cannot be accessed by index [-1] as ORM does not support reverse indexing.

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.last()  # Returns the last data entry
    return HttpResponse("<p>Search successful!</p>")

exists() method is used to check if there is any data in the QuerySet list.

The return type is boolean, true if there is data, false if there is none.

Note: The data type to be checked can only be QuerySet type, not integer or model class objects.

Example

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    books = models.Book.objects.exists()
    # Error, the data type to be checked can only be QuerySet type, not integer
    books = models.Book.objects.count().exists()
    # Error, the data type to be checked can only be QuerySet type, not model class objects
    books = models.Book.objects.first().exists()
    return HttpResponse("<p>Search successful!</p>")

values() method is used to query data for specific fields.

The return type is QuerySet data, similar to a list, but it contains iterable dictionaries instead of model class objects. The keys in the dictionaries are the fields and the values are the data.

Note:

Field names should be in quotes
Use values to get field names and data

Example

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    # Query data for all id and price fields
    books = models.Book.objects.values("pk", "price")
    print(books[0]["price"], type(books))  # Get the price data of the first record
    return HttpResponse("<p>Search successful!</p>")

values_list() method is used to query data for specific fields.

The return type is QuerySet data, similar to a list, but it contains tuples instead of model class objects. The tuples contain the data corresponding to the queried fields.

Note:

Field names should be in quotes
Use values_list to get only the data

Example

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    # Query data for all price and publish fields
    books = models.Book.objects.values_list("price", "publish")
    print(books)
    print(books[0][0], type(books))  # Get the price data of the first record
    return HttpResponse("<p>Search successful!</p>")

distinct() method is used to remove duplicate data.

The return type is QuerySet data.

Note:

Removing duplicates from model class objects is meaningless since each object is unique.
distinct() is usually used in conjunction with values or values_list.

Example

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    # Query the number of unique publishers
    books = models.Book.objects.values_list("publish").distinct()  # Removing duplicates from model class objects is meaningless since each object is unique.
    books = models.Book.objects.distinct()
    return HttpResponse("<p>Search successful!</p>")

The filter() method is based on double underscore fuzzy queries (the same applies to exclude).

Note: In filter, the operator can only use the equal sign =, not greater than >, less than <, or any other symbols.

__in is used for reading ranges, with a list following the = sign.

Example

from django.shortcuts import render, HttpResponse
from app01 import models
def add_book(request):
    # Query data with price 200 or 300
    books = models.Book.objects.filter(price__in=[200, 300])
    return HttpResponse("<p>Search successful!</p>")

__gt represents greater than, with a number following the = sign.

# Query data with price greater than 200
books = models.Book.objects.filter(price__gt=200)

# Query data with price greater than or equal to 200
books = models.Book.objects.filter(price__gte=200)

__lt represents less than, with a number following the = sign.

# Query data with price less than 300
books = models.Book.objects.filter(price__lt=300)

__lte represents less than or equal to, with a number following the = sign.

# Query data with price less than or equal to 300
books = models.Book.objects.filter(price__lte=300)

__range represents between, inclusive range, with a list of two elements following the = sign.

books = models.Book.objects.filter(price__range=[200, 300])

__contains represents contains, with a string following the = sign.

books = models.Book.objects.filter(title__contains="菜")

__icontains represents case-insensitive contains, with a string following the = sign.

books = models.Book.objects.filter(title__icontains="python")  # Case-insensitive

__startswith represents starts with, with a string following the = sign.

books = models.Book.objects.filter(title__startswith="菜")

__endswith represents ends with, with a string following the = sign.

books = models.Book.objects.filter(title__endswith="教程")

__year represents the year of a DateField, with a number following the = sign.

books = models.Book.objects.filter(pub_date__year=2008)

__month represents the month of a DateField, with a number following the = sign.

books = models.Book.objects.filter(pub_date__month=10)

__day represents the day of a DateField, with a number following the = sign.

books = models.Book.objects.filter(pub_date__day=01)

Delete

Method 1: Using the model class object.delete().

Return Value: A tuple, the first element being the number of affected rows.

books = models.Book.objects.filter(pk=8).first().delete()

Method 2: Using QuerySet data type.delete() (recommended)

Return Value: A tuple, the first element being the number of affected rows.

books = models.Book.objects.filter(pk__in=[1, 2]).delete()

Note:

a. When deleting data, Django mimics the SQL constraint ON DELETE CASCADE behavior, meaning that when an object is deleted, its associated foreign key objects are also deleted.
b. The delete() method is a method of the QuerySet data type, but not applicable to the Manager itself. So to delete all data, you cannot omit all.
```
books = models.Book.objects.delete()  # Error
books = models.Book.objects.all().delete()  # Delete successful
```

Update

Method 1:

model_instance.attribute = changed_attribute_value model_instance.save()


**Return Value:** The edited model instance.

books = models.Book.objects.filter(pk=7).first() books.price = 400 books.save()


**Method Two:** QuerySet.update(field_name=changed_data) (recommended)

**Return Value:** Integer, the number of affected rows

## Example

from django.shortcuts import render, HttpResponse from app01 import models def add_book(request): books = models.Book.objects.filter(pk__in=[7, 8]).update(price=888) return HttpResponse(books) ```

❮ Django Form Django Model ❯