Skip to main content

数据库函数

下面介绍的类为用户提供了一种方法,使用底层数据库提供的函数作为Django中的注释,聚合或过滤器。函数也是 表达式,因此它们可以与其他表达式(如 聚合函数)一起使用和组合。

我们将在每个函数的示例中使用以下模型:

class Author(models.Model):
    name = models.CharField(max_length=50)
    age = models.PositiveIntegerField(null=True, blank=True)
    alias = models.CharField(max_length=50, null=True, blank=True)
    goes_by = models.CharField(max_length=50, null=True, blank=True)

我们通常不建议为 CharField 允许 null=True,因为这允许字段有两个“空值”,但它对于下面的 Coalesce 示例很重要。

Cast

class Cast(expression, output_field)
New in Django 1.10.

强制 expression 的结果类型为 output_field 的结果类型。

用法示例:

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cast
>>> Value.objects.create(integer=4)
>>> value = Value.objects.annotate(as_float=Cast('integer', FloatField())).get()
>>> print(value.as_float)
4.0

Coalesce

class Coalesce(*expressions, **extra)[源代码]

接受至少两个字段名称或表达式的列表,并返回第一个非空值(请注意,空字符串不被视为空值)。每个参数必须是相似的类型,因此混合文本和数字将导致数据库错误。

用法示例:

>>> # Get a screen name from least to most public
>>> from django.db.models import Sum, Value as V
>>> from django.db.models.functions import Coalesce
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
>>> author = Author.objects.annotate(
...    screen_name=Coalesce('alias', 'goes_by', 'name')).get()
>>> print(author.screen_name)
Maggie

>>> # Prevent an aggregate Sum() from returning None
>>> aggregated = Author.objects.aggregate(
...    combined_age=Coalesce(Sum('age'), V(0)),
...    combined_age_default=Sum('age'))
>>> print(aggregated['combined_age'])
0
>>> print(aggregated['combined_age_default'])
None

警告

在MySQL上传递给 Coalesce 的Python值可能会转换为不正确的类型,除非显式转换为正确的数据库类型:

>>> from django.db.models import DateTimeField
>>> from django.db.models.functions import Cast, Coalesce
>>> from django.utils import timezone
>>> now = timezone.now()
>>> Coalesce('updated', Cast(now, DateTimeField()))

Concat

class Concat(*expressions, **extra)[源代码]

接受至少两个文本字段或表达式的列表,并返回连接的文本。每个参数必须是文本或字符类型。如果要将 TextField()CharField() 连接,请务必告诉Django output_field 应该是 TextField()。这在连接 Value 时也是必需的,如下面的示例所示。

此函数永远不会有null结果。在后端,null参数导致整个表达式为null,Django将确保每个空部分首先转换为空字符串。

用法示例:

>>> # Get the display name as "name (goes_by)"
>>> from django.db.models import CharField, Value as V
>>> from django.db.models.functions import Concat
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
>>> author = Author.objects.annotate(
...    screen_name=Concat('name', V(' ('), 'goes_by', V(')'),
...    output_field=CharField())).get()
>>> print(author.screen_name)
Margaret Smith (Maggie)

Greatest

class Greatest(*expressions, **extra)
New in Django 1.9.

接受至少两个字段名称或表达式的列表,并返回最大值。每个参数必须是相似的类型,因此混合文本和数字将导致数据库错误。

用法示例:

class Blog(models.Model):
    body = models.TextField()
    modified = models.DateTimeField(auto_now=True)

class Comment(models.Model):
    body = models.TextField()
    modified = models.DateTimeField(auto_now=True)
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)

>>> from django.db.models.functions import Greatest
>>> blog = Blog.objects.create(body='Greatest is the best.')
>>> comment = Comment.objects.create(body='No, Least is better.', blog=blog)
>>> comments = Comment.objects.annotate(last_updated=Greatest('modified', 'blog__modified'))
>>> annotated_comment = comments.get()

annotated_comment.last_updated 将是最近的 blog.modifiedcomment.modified

警告

当一个或多个表达式可能是 null 时,Greatest 的行为在数据库之间不同:

  • PostgreSQL:Greatest 将返回最大的非空表达式,如果所有表达式都是 null,则返回 null

  • SQLite,Oracle和MySQL:如果任何表达式是 nullGreatest 将返回 null

如果您知道一个合理的最小值以提供默认值,则可以使用 Coalesce 来模拟PostgreSQL行为。

Least

class Least(*expressions, **extra)
New in Django 1.9.

接受至少两个字段名称或表达式的列表,并返回最小值。每个参数必须是相似的类型,因此混合文本和数字将导致数据库错误。

警告

当一个或多个表达式可能是 null 时,Least 的行为在数据库之间不同:

  • PostgreSQL:Least 将返回最小的非空表达式,或者 null,如果所有表达式都是 null

  • SQLite,Oracle和MySQL:如果任何表达式是 nullLeast 将返回 null

如果你知道一个合理的最大值提供作为默认值,PostgreSQL的行为可以使用 Coalesce 来模拟。

Length

class Length(expression, **extra)[源代码]

接受单个文本字段或表达式,并返回值的字符数。如果表达式为null,则长度也将为null。

用法示例:

>>> # Get the length of the name and goes_by fields
>>> from django.db.models.functions import Length
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(
...    name_length=Length('name'),
...    goes_by_length=Length('goes_by')).get()
>>> print(author.name_length, author.goes_by_length)
(14, None)

它也可以注册为变换。例如:

>>> from django.db.models import CharField
>>> from django.db.models.functions import Length
>>> CharField.register_lookup(Length, 'length')
>>> # Get authors whose name is longer than 7 characters
>>> authors = Author.objects.filter(name__length__gt=7)
Changed in Django 1.9:

添加了将函数注册为变换的功能。

Lower

class Lower(expression, **extra)[源代码]

接受单个文本字段或表达式,并返回小写表示形式。

它也可以注册为 Length 中描述的变换。

用法示例:

>>> from django.db.models.functions import Lower
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_lower=Lower('name')).get()
>>> print(author.name_lower)
margaret smith
Changed in Django 1.9:

添加了将函数注册为变换的功能。

Now

class Now
New in Django 1.9.

返回执行查询时的数据库服务器当前日期和时间,通常使用SQL CURRENT_TIMESTAMP

用法示例:

>>> from django.db.models.functions import Now
>>> Article.objects.filter(published__lte=Now())
<QuerySet [<Article: How to Django>]>

PostgreSQL注意事项

在PostgreSQL上,SQL CURRENT_TIMESTAMP 返回当前事务开始的时间。因此,为了跨数据库兼容性,Now() 改用 STATEMENT_TIMESTAMP。如果需要事务时间戳,请使用 django.contrib.postgres.functions.TransactionNow

Substr

class Substr(expression, pos, length=None, **extra)[源代码]

从位置 pos 开始的字段或表达式返回长度为 length 的子字符串。位置是1索引的,因此位置必须大于0.如果 lengthNone,则将返回字符串的其余部分。

用法示例:

>>> # Set the alias to the first 5 characters of the name as lowercase
>>> from django.db.models.functions import Substr, Lower
>>> Author.objects.create(name='Margaret Smith')
>>> Author.objects.update(alias=Lower(Substr('name', 1, 5)))
1
>>> print(Author.objects.get(name='Margaret Smith').alias)
marga

Upper

class Upper(expression, **extra)[源代码]

接受单个文本字段或表达式,并返回大写形式。

它也可以注册为 Length 中描述的变换。

用法示例:

>>> from django.db.models.functions import Upper
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_upper=Upper('name')).get()
>>> print(author.name_upper)
MARGARET SMITH
Changed in Django 1.9:

添加了将函数注册为变换的功能。

日期函数

New in Django 1.10.

我们将在每个函数的示例中使用以下模型:

class Experiment(models.Model):
    start_datetime = models.DateTimeField()
    start_date = models.DateField(null=True, blank=True)
    end_datetime = models.DateTimeField(null=True, blank=True)
    end_date = models.DateField(null=True, blank=True)

Extract

class Extract(expression, lookup_name=None, tzinfo=None, **extra)

将日期的组件提取为数字。

使用表示 DateFieldDateTimeFieldlookup_nameexpression,并将由 lookup_name 引用的日期的一部分返回为 IntegerField。 Django通常使用数据库的提取功能,因此您可以使用数据库支持的任何 lookup_name。通常由 pytz 提供的 tzinfo 子类可以被传递以提取特定时区中的值。

给定日期时间 2015-06-15 23:30:01.000321+00:00,内置 lookup_name s返回:

  • “年”:2015

  • “月”:6

  • “天”:15

  • “week_day”:2

  • “hour”:23

  • “分”:30

  • “second”:1

如果不同的时区像 Australia/Melbourne 在Django中处于活动状态,那么datetime将在提取值之前转换为时区。以上示例日期中墨尔本的时区偏移量为+10:00。当此时区处于活动状态时返回的值将与上述相同,除了:

  • “天”:16

  • “week_day”:3

  • “小时”:9

week_day

week_day lookup_type 的计算方式与大多数数据库和Python的标准函数不同。这个函数将返回 1 的星期日,2 的星期一,通过 7 的星期六。

在Python中的等价计算是:

>>> from datetime import datetime
>>> dt = datetime(2015, 6, 15)
>>> (dt.isoweekday() % 7) + 1
2

上面的每个 lookup_name 具有通常应该使用的相应的 Extract 子类(下面列出),而不是更冗长的等同物,例如。使用 ExtractYear(...) 而不是 Extract(..., lookup_name='year')

用法示例:

>>> from datetime import datetime
>>> from django.db.models.functions import Extract
>>> start = datetime(2015, 6, 15)
>>> end = datetime(2015, 7, 2)
>>> Experiment.objects.create(
...    start_datetime=start, start_date=start.date(),
...    end_datetime=end, end_date=end.date())
>>> # Add the experiment start year as a field in the QuerySet.
>>> experiment = Experiment.objects.annotate(
...    start_year=Extract('start_datetime', 'year')).get()
>>> experiment.start_year
2015
>>> # How many experiments completed in the same year in which they started?
>>> Experiment.objects.filter(
...    start_datetime__year=Extract('end_datetime', 'year')).count()
1

DateField 提取物

class ExtractYear(expression, tzinfo=None, **extra)
lookup_name = 'year'
class ExtractMonth(expression, tzinfo=None, **extra)
lookup_name = 'month'
class ExtractDay(expression, tzinfo=None, **extra)
lookup_name = 'day'
class ExtractWeekDay(expression, tzinfo=None, **extra)
lookup_name = 'week_day'

这些在逻辑上等同于 Extract('date_field', lookup_name)。每个类也是在 DateField 上注册的 Transform 和作为 __(lookup_name) 注册的 DateTimeField,例如 __year

由于 DateField 没有时间组件,只有处理日期部分的 Extract 子类可以与 DateField 一起使用:

>>> from datetime import datetime
>>> from django.utils import timezone
>>> from django.db.models.functions import (
...    ExtractYear, ExtractMonth, ExtractDay, ExtractWeekDay
... )
>>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
>>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
>>> Experiment.objects.create(
...    start_datetime=start_2015, start_date=start_2015.date(),
...    end_datetime=end_2015, end_date=end_2015.date())
>>> Experiment.objects.annotate(
...     year=ExtractYear('start_date'),
...     month=ExtractMonth('start_date'),
...     day=ExtractDay('start_date'),
...     weekday=ExtractWeekDay('start_date'),
... ).values('year', 'month', 'day', 'weekday').get(
...     end_date__year=ExtractYear('start_date'),
... )
{'year': 2015, 'month': 6, 'day': 15, 'weekday': 2}

DateTimeField 提取物

除了以下内容,上面列出的 DateField 的所有提取物也可以用于 DateTimeField

class ExtractHour(expression, tzinfo=None, **extra)
lookup_name = 'hour'
class ExtractMinute(expression, tzinfo=None, **extra)
lookup_name = 'minute'
class ExtractSecond(expression, tzinfo=None, **extra)
lookup_name = 'second'

这些在逻辑上等同于 Extract('datetime_field', lookup_name)。每个类别也是在 DateTimeField 上注册为 __(lookup_name)Transform,例如 __minute

DateTimeField 示例:

>>> from datetime import datetime
>>> from django.utils import timezone
>>> from django.db.models.functions import (
...    ExtractYear, ExtractMonth, ExtractDay, ExtractWeekDay,
...    ExtractHour, ExtractMinute, ExtractSecond,
... )
>>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
>>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
>>> Experiment.objects.create(
...    start_datetime=start_2015, start_date=start_2015.date(),
...    end_datetime=end_2015, end_date=end_2015.date())
>>> Experiment.objects.annotate(
...     year=ExtractYear('start_datetime'),
...     month=ExtractMonth('start_datetime'),
...     day=ExtractDay('start_datetime'),
...     weekday=ExtractWeekDay('start_datetime'),
...     hour=ExtractHour('start_datetime'),
...     minute=ExtractMinute('start_datetime'),
...     second=ExtractSecond('start_datetime'),
... ).values(
...     'year', 'month', 'day', 'weekday', 'hour', 'minute', 'second',
... ).get(end_datetime__year=ExtractYear('start_datetime'))
{'year': 2015, 'month': 6, 'day': 15, 'weekday': 2, 'hour': 23, 'minute': 30, 'second': 1}

USE_TZTrue 时,则数据时间以UTC为单位存储在数据库中。如果Django中的其他时区处于活动状态,那么datetime将在提取值之前转换为该时区。以下示例转换为墨尔本时区(UTC +10:00),该时区更改返回的日期,工作日和小时值:

>>> import pytz
>>> tzinfo = pytz.timezone('Australia/Melbourne')  # UTC+10:00
>>> with timezone.override(tzinfo):
...    Experiment.objects.annotate(
...        day=ExtractDay('start_datetime'),
...        weekday=ExtractWeekDay('start_datetime'),
...        hour=ExtractHour('start_datetime'),
...    ).values('day', 'weekday', 'hour').get(
...        end_datetime__year=ExtractYear('start_datetime'),
...    )
{'day': 16, 'weekday': 3, 'hour': 9}

将时区显式传递给 Extract 函数的行为方式相同,并且优先级高于活动时区:

>>> import pytz
>>> tzinfo = pytz.timezone('Australia/Melbourne')
>>> Experiment.objects.annotate(
...     day=ExtractDay('start_datetime', tzinfo=melb),
...     weekday=ExtractWeekDay('start_datetime', tzinfo=melb),
...     hour=ExtractHour('start_datetime', tzinfo=melb),
... ).values('day', 'weekday', 'hour').get(
...     end_datetime__year=ExtractYear('start_datetime'),
... )
{'day': 16, 'weekday': 3, 'hour': 9}

Trunc

class Trunc(expression, kind, output_field=None, tzinfo=None, **extra)

截断到显着组件的日期。

当你只关心某个特定年份,小时或日期发生的事情,而不是确切的秒,那么 Trunc (及其子类)可以用来过滤或聚合你的数据。例如,您可以使用 Trunc 计算每天的销售量。

Trunc 采用单个 expression,表示 DateFieldDateTimeFieldkind 表示日期部分,output_fieldDateTimeField()DateField()。它返回一个日期时间或日期,取决于 output_field,字段最多 kind 设置为它们的最小值。如果省略 output_field,它将默认为 expressionoutput_field。通常由 pytz 提供的 tzinfo 子类可以被传递以截断特定时区中的值。

给定日期时间 2015-06-15 14:30:50.000321+00:00,内置 kind s返回:

  • “year”:2015-01-01 00:00:00 + 00:00

  • “month”:2015-06-01 00:00:00 + 00:00

  • “day”:2015-06-15 00:00:00 + 00:00

  • “hour”:2015-06-15 14:00:00 + 00:00

  • “minute”:2015-06-15 14:30:00 + 00:00

  • “秒”:2015-06-15 14:30:50 + 00:00

如果不同的时区像 Australia/Melbourne 在Django中处于活动状态,那么datetime将在截断值之前转换为新的时区。以上示例日期中墨尔本的时区偏移量为+10:00。当此时区处于活动状态时返回的值将为:

  • “year”:2015-01-01 00:00:00 + 11:00

  • “month”:2015-06-01 00:00:00 + 10:00

  • “day”:2015-06-16 00:00:00 + 10:00

  • “hour”:2015-06-16 00:00:00 + 10:00

  • “minute”:2015-06-16 00:30:00 + 10:00

  • “秒”:2015-06-16 00:30:50 + 10:00

年份的偏移为+11:00,因为结果转换为夏令时。

上面的每个 kind 具有通常应该使用的相应的 Trunc 子类(下面列出),而不是更冗长的等同物,例如。使用 TruncYear(...) 而不是 Trunc(..., kind='year')

子类都定义为变换,但它们未注册任何字段,因为明显的查找名称已由 Extract 子类保留。

用法示例:

>>> from datetime import datetime
>>> from django.db.models import Count, DateTimeField
>>> from django.db.models.functions import Trunc
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 30, 50, 321))
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 40, 2, 123))
>>> Experiment.objects.create(start_datetime=datetime(2015, 12, 25, 10, 5, 27, 999))
>>> experiments_per_day = Experiment.objects.annotate(
...    start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).values('start_day').annotate(experiments=Count('id'))
>>> for exp in experiments_per_day:
...     print(exp['start_day'], exp['experiments'])
...
2015-06-15 00:00:00 2
2015-12-25 00:00:00 1
>>> experiments = Experiment.objects.annotate(
...    start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).filter(start_day=datetime(2015, 6, 15))
>>> for exp in experiments:
...     print(exp.start_datetime)
...
2015-06-15 14:30:50.000321
2015-06-15 14:40:02.000123

DateField 截断

class TruncYear(expression, output_field=None, tzinfo=None, **extra)
kind = 'year'
class TruncMonth(expression, output_field=None, tzinfo=None, **extra)
kind = 'month'

这些在逻辑上等同于 Trunc('date_field', kind)。它们截断日期的所有部分直到 kind,这允许以较小的精度分组或过滤日期。 expression 可以具有 DateFieldDateTimeFieldoutput_field

由于 DateField 没有时间组件,只有处理日期部分的 Trunc 子类可以与 DateField 一起使用:

>>> from datetime import datetime
>>> from django.db.models import Count
>>> from django.db.models.functions import TruncMonth, TruncYear
>>> from django.utils import timezone
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
>>> start2 = datetime(2015, 6, 15, 14, 40, 2, 123, tzinfo=timezone.utc)
>>> start3 = datetime(2015, 12, 31, 17, 5, 27, 999, tzinfo=timezone.utc)
>>> Experiment.objects.create(start_datetime=start1, start_date=start1.date())
>>> Experiment.objects.create(start_datetime=start2, start_date=start2.date())
>>> Experiment.objects.create(start_datetime=start3, start_date=start3.date())
>>> experiments_per_year = Experiment.objects.annotate(
...    year=TruncYear('start_date')).values('year').annotate(
...    experiments=Count('id'))
>>> for exp in experiments_per_year:
...     print(exp['year'], exp['experiments'])
...
2014-01-01 1
2015-01-01 2

>>> import pytz
>>> melb = pytz.timezone('Australia/Melbourne')
>>> experiments_per_month = Experiment.objects.annotate(
...    month=TruncMonth('start_datetime', tzinfo=melb)).values('month').annotate(
...    experiments=Count('id'))
>>> for exp in experiments_per_month:
...     print(exp['month'], exp['experiments'])
...
2015-06-01 00:00:00+10:00 1
2016-01-01 00:00:00+11:00 1
2014-06-01 00:00:00+10:00 1

DateTimeField 截断

class TruncDate(expression, **extra)
lookup_name = 'date'
output_field = DateField()

TruncDateexpression 转换为日期,而不是使用内置的SQL truncate函数。它也注册为 __date 作为 DateTimeField 的变换。

class TruncDay(expression, output_field=None, tzinfo=None, **extra)
kind = 'day'
class TruncHour(expression, output_field=None, tzinfo=None, **extra)
kind = 'hour'
class TruncMinute(expression, output_field=None, tzinfo=None, **extra)
kind = 'minute'
class TruncSecond(expression, output_field=None, tzinfo=None, **extra)
kind = 'second'

这些在逻辑上等同于 Trunc('datetime_field', kind)。它们将日期的所有部分截断为 kind,并允许以较低的精度对数据集进行分组或过滤。 expression 必须具有 DateTimeFieldoutput_field

用法示例:

>>> from datetime import date, datetime
>>> from django.db.models import Count
>>> from django.db.models.functions import (
...     TruncDate, TruncDay, TruncHour, TruncMinute, TruncSecond,
... )
>>> from django.utils import timezone
>>> import pytz
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
>>> Experiment.objects.create(start_datetime=start1, start_date=start1.date())
>>> melb = pytz.timezone('Australia/Melbourne')
>>> Experiment.objects.annotate(
...     date=TruncDate('start_datetime'),
...     day=TruncDay('start_datetime', tzinfo=melb),
...     hour=TruncHour('start_datetime', tzinfo=melb),
...     minute=TruncMinute('start_datetime'),
...     second=TruncSecond('start_datetime'),
... ).values('date', 'day', 'hour', 'minute', 'second').get()
{'date': datetime.date(2014, 6, 15),
 'day': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
 'hour': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
 'minute': 'minute': datetime.datetime(2014, 6, 15, 14, 30, tzinfo=<UTC>),
 'second': datetime.datetime(2014, 6, 15, 14, 30, 50, tzinfo=<UTC>)
}