Django ORM的使用

定义模型

继承 models.Model 并使用 models.XXXField

from django.db import models

class Book(models.Model)
    title = models.CharField(max_length=100, blank=True)
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher, on_delete=models.DO_NOTHING)
    publication_date = models.DateField(null=True, blank=True)

    def __str__(self):
    return f"<Book {self.title}>"

    class Meta:
        ordering = ["name"]
        db_table = ''

注意其中的 manytomanyfield and foreignkey 字段。注意 ForeignKey 字段必须添加 on_delete 参数，参考这里。

YN：on_delete 最好使用 models.DO_NOTHING，虽然会造成数据库的完整性缺失，但是没有丢失任何信息。另外，对于数据库来说，尽量少删除数据，而是用一个字段标记为已删除。

指向自己的外键: models.ForeignKey('self')

字段的参数

• null is the field nullable. default False.
• blank could this filed be left blank, this is used for django validation, not database scheme, default False.
• db_index create db index on this field, default False.
• choices used to limit the choice to that field and the text shown. choices = ((1, 'male'), (0, 'female'))
• default value or a callable to set default value.
• help_text
• verbose verbose name for the field
• unique should this field be unique.
• primary_key set this field as primary_key, if this is set, django will not generate id field

自动生成的 ID 字段

by default, django gives each model a primary key field. if primary_key=True is set on any other field, django will not generate this.

class meta

• db_table, the dafault is APPNAME_MODEL
• ordering, a list of fields to set order
• unique_together, a tuple of unique fields tuples: unique_together = (("driver", "restaurant"),)
• index_together, a list of indexes, index_together = [["pub_date", "deadline"],]

leave field blank

to make string field optional, just add blank = True if you want to allow blank values in a date field (e.g., DateField, TimeField, DateTimeField) or numeric field (e.g.,IntegerField, DecimalField, FloatField), you’ll need to use both null=True and blank=True.

Abstract base class

比如说有时候我们对于每一个模型都需要创建 create_time, modify_time 字段，在 meta 中设定 abstract 为 true。

from django.db import models

class CommonInfo(models.Model):
    name = models.CharField(max_length=100)
    age = models.PositiveIntegerField()

    class Meta:
        abstract = True

class Student(CommonInfo):
    home_group = models.CharField(max_length=5)

others

如果你想覆盖 __init__ 方法, 记得

def __init__(self, *args, **kwargs):
    super().__init__(self, *args, **kwargs)
    # your code here

使用 Model

Create

Model.objects.create(**kwargs)

Query

Model.objects.get(kwargs) returns one object, may raise DoesNotExist or MultiOjbectsReturned Model.objects.all() Model.objects.filter(kwargs) returns a query set Model.objects.order_by(*colnames) note, you could use "-" in the colnames, cool

just use filter, get may raise Exceptions

查询条件

field__lookuptype=value

lookups

/exact/iexact
contains/icontains
(i)startswith/(i)endswith
range
in
gt/gte/lt/lte
year/month/day/week_day/hour/minute/second
isnull
regex/iregex

save a model

model = Model() model.save() note that all of the fields will be updated, not just the ones that have been changed. model/queryset.update() model/queryset.delete()

QuerySet slicing

slicing will cause limit cause, brilliant, however, negative slicing is not supported

Generally, slicing a QuerySet returns a new QuerySet – it doesn’t evaluate the query.An exception is if you use the step parameter of Python slice syntax. For example, this would actually execute the query in order to return a list of every second object of the first 10:

>>> Entry.objects.all()[:10:2]

May raise IndexError

Tricks

>>> Publisher.objects.order_by('name')[-1]
Traceback (most recent call last):
  ...
AssertionError: Negative indexing is not supported.
This is easy to get around, though. Just change the order_by() statement, like this:
>>> Publisher.objects.order_by('-name')[0]

Model.objects.get().delete()

column can be accessed as attribute

django abstract base class is fun, but should be avoided, because we want to find-grain control the db

django-admin makemigrations django-admin sqlmigrate don't worry about the numbers of migrations

复杂查询，使用 F 和 Q

https://docs.djangoproject.com/en/1.10/topics/db/queries/#complex-lookups-with-q

Keyword argument queries – in filter(), etc. – are “AND”ed together. If you need to execute more complex queries (for example, queries with OR statements), you can use Q objects.

F and Q F is for field Q is for query

django queryset

filter(kwargs) exclude(kwargs) count create get_or_create update delete iterate exists iterator

QuerySets are lazy!

Each QuerySet contains a cache to minimize database access. Understanding how it works will allow you to write the most efficient code. In a newly created QuerySet, the cache is empty. The first time a QuerySet is evaluated – and, hence, a database query happens – Django saves the query results in the QuerySet’s cache and returns the results that have been explicitly requested (e.g., the next element, if the QuerySet is being iterated over). Subsequent evaluations of the QuerySet reuse the cached results.

Using iterator vs directly

A QuerySet typically caches its results internally so that repeated evaluations do not result in additional queries. In contrast, iterator() will read results directly, without doing any caching at the QuerySet level (internally, the default iterator calls iterator() and caches the return value). Using iterator would probably save your memory.

Keep this caching behavior in mind, because it may bite you if you don’t use your QuerySets correctly. For example, the following will create two QuerySets, evaluate them, and throw them away:

>>> print([e.headline for e in Entry.objects.all()]) # two querysets created and evaluated and thrown
>>> print([e.pub_date for e in Entry.objects.all()])

That means the same database query will be executed twice, effectively doubling your database load. Also, there’s a possibility the two lists may not include the same database records, because an Entry may have been added or deleted in the split second between the two requests. To avoid this problem, simply save the QuerySet and reuse it:

>>> queryset = Entry.objects.all()        # store the queryset to a variable
>>> print([p.headline for p in queryset]) # Evaluate the query set.
>>> print([p.pub_date for p in queryset]) # Re-use the cache from the evaluation.

When querysets are not cached?

Querysets do not always cache their results. When evaluating only part of the queryset, the cache is checked, but if it is not populated then the items returned by the subsequent query are not cached. Specifically, this means that limiting the queryset using an array slice or an index will not populate the cache.

For example, repeatedly getting a certain index in a queryset object will query the database each time:

>>> queryset = Entry.objects.all()
>>> print queryset[5] # Queries the database
>>> print queryset[5] # Queries the database again 

However, if the entire queryset has already been evaluated, the cache will be checked instead:

>>> queryset = Entry.objects.all()
>>> [entry for entry in queryset] # Queries the database
>>> print queryset[5] # Uses cache
>>> print queryset[5] # Uses cache 
Here are some examples of other actions that will result in the entire queryset being evaluated and therefore populate the cache:
>>> [entry for entry in queryset]
>>> bool(queryset)
>>> entry in queryset
>>> list(queryset)

F expressions

what if you want to compare the value of a model field with another field on the same model? use F(colname) to reference the column value

Q Expressions

encapsulate a collection of keyword arguments. Q objects can be combined using the & and | operators. When an operator is used on two Qobjects, it yields a new Q object.

by default, all the lookups passed to filter is AND

Q(question__startswith='Who') | Q(question__startswith='What')

Q supports & / ~

use Qs as args

Poll.objects.get(
    Q(question__startswith='Who'),
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)

if a Q object is provided, it must precede the definition of any keyword arguments.

if you want to add extra check in model save just override the defualt save method aorr add post save handlers

fat models is not all that good, it may cause god object problem

使用 only 来指定需要的字段。

如果只需要一个或者几个值，可以使用 values_list 方法

In [6]: authors = Author.objects.values_list('name', 'qq')

In [7]: authors

Out[7]: <QuerySet [(u'WeizhongTu', u'336643078'), (u'twz915', u'915792575'), (u'wangdachui', u'353506297'), (u'xiaoming', u'004466315')]>

In [8]: list(authors)

Out[8]: 

[(u'WeizhongTu', u'336643078'),

 (u'twz915', u'915792575'),

 (u'wangdachui', u'353506297'),

 (u'xiaoming', u'004466315')]

如果只需要 1 个字段，可以指定 flat=True

In [9]: Author.objects.values_list('name', flat=True)

Out[9]: <QuerySet [u'WeizhongTu', u'twz915', u'wangdachui', u'xiaoming']>

In [10]: list(Author.objects.values_list('name', flat=True))

Out[10]: [u'WeizhongTu', u'twz915', u'wangdachui', u'xiaoming']

查看执行的 sql 语句和执行时间

from django.db import connection
print(connection.queries)

why not using foreign keys

you can not use foreign keys across two databases

如何在 django 外部单独使用

import os
from django.conf import settings
from django.apps import apps

conf = {
    'INSTALLED_APPS': [
        'Demo'
    ],
    'DATABASES': {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': os.path.join('.', 'db.sqlite3'),
        }
    }
}

settings.configure(**conf)
apps.populate(settings.INSTALLED_APPS)

https://stackoverflow.com/a/46050808/1061155