diff options
Diffstat (limited to 'docs/ref/models/database-functions.txt')
| -rw-r--r-- | docs/ref/models/database-functions.txt | 290 |
1 files changed, 218 insertions, 72 deletions
diff --git a/docs/ref/models/database-functions.txt b/docs/ref/models/database-functions.txt index f33e4f5370..f6d597a266 100644 --- a/docs/ref/models/database-functions.txt +++ b/docs/ref/models/database-functions.txt @@ -35,7 +35,9 @@ Comparison and conversion functions Forces the result type of ``expression`` to be the one from ``output_field``. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Cast @@ -56,7 +58,9 @@ first non-null value (note that an empty string is not considered a null value). Each argument must be of a similar type, so mixing text and numbers will result in a database error. -Usage examples:: +Usage examples: + +.. code-block:: pycon >>> # Get a screen name from least to most public >>> from django.db.models import Sum @@ -99,12 +103,16 @@ Usage examples:: Takes an expression and a collation name to query against. -For example, to filter case-insensitively in SQLite:: +For example, to filter case-insensitively in SQLite: + +.. code-block:: pycon >>> Author.objects.filter(name=Collate(Value('john'), 'nocase')) <QuerySet [<Author: John>, <Author: john>]> -It can also be used when ordering, for example with PostgreSQL:: +It can also be used when ordering, for example with PostgreSQL: + +.. code-block:: pycon >>> Author.objects.order_by(Collate('name', 'et-x-icu')) <QuerySet [<Author: Ursula>, <Author: Veronika>, <Author: Ülle>]> @@ -129,6 +137,8 @@ Usage example:: modified = models.DateTimeField(auto_now=True) blog = models.ForeignKey(Blog, on_delete=models.CASCADE) +.. code-block:: pycon + >>> 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) @@ -159,7 +169,9 @@ and ``comment.modified``. Takes a list of key-value pairs and returns a JSON object containing those pairs. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models import F >>> from django.db.models.functions import JSONObject, Lower @@ -272,7 +284,9 @@ returned when this timezone is active will be the same as above except for: databases and from Python's standard functions. This function will return ``1`` for Sunday, ``2`` for Monday, through ``7`` for Saturday. - The equivalent calculation in Python is:: + The equivalent calculation in Python is: + + .. code-block:: pycon >>> from datetime import datetime >>> dt = datetime(2015, 6, 15) @@ -292,7 +306,9 @@ Each ``lookup_name`` above has a corresponding ``Extract`` subclass (listed below) that should typically be used instead of the more verbose equivalent, e.g. use ``ExtractYear(...)`` rather than ``Extract(..., lookup_name='year')``. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from datetime import datetime >>> from django.db.models.functions import Extract @@ -356,7 +372,9 @@ class is also a ``Transform`` registered on ``DateField`` and ``DateTimeField`` as ``__(lookup_name)``, e.g. ``__year``. Since ``DateField``\s don't have a time component, only ``Extract`` subclasses -that deal with date-parts can be used with ``DateField``:: +that deal with date-parts can be used with ``DateField``: + +.. code-block:: pycon >>> from datetime import datetime, timezone >>> from django.db.models.functions import ( @@ -406,7 +424,9 @@ These are logically equivalent to ``Extract('datetime_field', lookup_name)``. Each class is also a ``Transform`` registered on ``DateTimeField`` as ``__(lookup_name)``, e.g. ``__minute``. -``DateTimeField`` examples:: +``DateTimeField`` examples: + +.. code-block:: pycon >>> from datetime import datetime, timezone >>> from django.db.models.functions import ( @@ -443,7 +463,9 @@ When :setting:`USE_TZ` is ``True`` then datetimes are stored in the database in UTC. If a different timezone is active in Django, the datetime is converted to that timezone before the value is extracted. The example below converts to the Melbourne timezone (UTC +10:00), which changes the day, weekday, and hour -values that are returned:: +values that are returned: + +.. code-block:: pycon >>> from django.utils import timezone >>> import zoneinfo @@ -460,7 +482,9 @@ values that are returned:: {'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9} Explicitly passing the timezone to the ``Extract`` function behaves in the same -way, and takes priority over an active timezone:: +way, and takes priority over an active timezone: + +.. code-block:: pycon >>> import zoneinfo >>> melb = zoneinfo.ZoneInfo('Australia/Melbourne') @@ -482,7 +506,9 @@ way, and takes priority over an active timezone:: Returns the database server's current date and time when the query is executed, typically using the SQL ``CURRENT_TIMESTAMP``. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Now >>> Article.objects.filter(published__lte=Now()) @@ -566,7 +592,9 @@ The subclasses are all defined as transforms, but they aren't registered with any fields, because the lookup names are already reserved by the ``Extract`` subclasses. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from datetime import datetime >>> from django.db.models import Count, DateTimeField @@ -622,7 +650,9 @@ with less precision. ``expression`` can have an ``output_field`` of either ``DateField`` or ``DateTimeField``. Since ``DateField``\s don't have a time component, only ``Trunc`` subclasses -that deal with date-parts can be used with ``DateField``:: +that deal with date-parts can be used with ``DateField``: + +.. code-block:: pycon >>> from datetime import datetime, timezone >>> from django.db.models import Count @@ -700,7 +730,9 @@ truncate all parts of the date up to ``kind`` and allow grouping or filtering datetimes with less precision. ``expression`` must have an ``output_field`` of ``DateTimeField``. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from datetime import date, datetime, timezone >>> from django.db.models import Count @@ -753,7 +785,9 @@ with less precision. ``expression`` can have an ``output_field`` of either ``TimeField`` or ``DateTimeField``. Since ``TimeField``\s don't have a date component, only ``Trunc`` subclasses -that deal with time-parts can be used with ``TimeField``:: +that deal with time-parts can be used with ``TimeField``: + +.. code-block:: pycon >>> from datetime import datetime, timezone >>> from django.db.models import Count, TimeField @@ -802,7 +836,9 @@ We'll be using the following model in math function examples:: Returns the absolute value of a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Abs >>> Vector.objects.create(x=-0.5, y=1.1) @@ -810,7 +846,9 @@ Usage example:: >>> vector.x_abs, vector.y_abs (0.5, 1.1) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Abs @@ -826,7 +864,9 @@ It can also be registered as a transform. For example:: Returns the arccosine of a numeric field or expression. The expression value must be within the range -1 to 1. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import ACos >>> Vector.objects.create(x=0.5, y=-0.9) @@ -834,7 +874,9 @@ Usage example:: >>> vector.x_acos, vector.y_acos (1.0471975511965979, 2.6905658417935308) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import ACos @@ -850,7 +892,9 @@ It can also be registered as a transform. For example:: Returns the arcsine of a numeric field or expression. The expression value must be in the range -1 to 1. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import ASin >>> Vector.objects.create(x=0, y=1) @@ -858,7 +902,9 @@ Usage example:: >>> vector.x_asin, vector.y_asin (0.0, 1.5707963267948966) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import ASin @@ -873,7 +919,9 @@ It can also be registered as a transform. For example:: Returns the arctangent of a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import ATan >>> Vector.objects.create(x=3.12, y=6.987) @@ -881,7 +929,9 @@ Usage example:: >>> vector.x_atan, vector.y_atan (1.2606282660069106, 1.428638798133829) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import ATan @@ -896,7 +946,9 @@ It can also be registered as a transform. For example:: Returns the arctangent of ``expression1 / expression2``. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import ATan2 >>> Vector.objects.create(x=2.5, y=1.9) @@ -912,7 +964,9 @@ Usage example:: Returns the smallest integer greater than or equal to a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Ceil >>> Vector.objects.create(x=3.12, y=7.0) @@ -920,7 +974,9 @@ Usage example:: >>> vector.x_ceil, vector.y_ceil (4.0, 7.0) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Ceil @@ -935,7 +991,9 @@ It can also be registered as a transform. For example:: Returns the cosine of a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Cos >>> Vector.objects.create(x=-8.0, y=3.1415926) @@ -943,7 +1001,9 @@ Usage example:: >>> vector.x_cos, vector.y_cos (-0.14550003380861354, -0.9999999999999986) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Cos @@ -958,7 +1018,9 @@ It can also be registered as a transform. For example:: Returns the cotangent of a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Cot >>> Vector.objects.create(x=12.0, y=1.0) @@ -966,7 +1028,9 @@ Usage example:: >>> vector.x_cot, vector.y_cot (-1.5726734063976826, 0.642092615934331) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Cot @@ -981,7 +1045,9 @@ It can also be registered as a transform. For example:: Converts a numeric field or expression from radians to degrees. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Degrees >>> Vector.objects.create(x=-1.57, y=3.14) @@ -989,7 +1055,9 @@ Usage example:: >>> vector.x_d, vector.y_d (-89.95437383553924, 179.9087476710785) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Degrees @@ -1005,7 +1073,9 @@ It can also be registered as a transform. For example:: Returns the value of ``e`` (the natural logarithm base) raised to the power of a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Exp >>> Vector.objects.create(x=5.4, y=-2.0) @@ -1013,7 +1083,9 @@ Usage example:: >>> vector.x_exp, vector.y_exp (221.40641620418717, 0.1353352832366127) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Exp @@ -1029,7 +1101,9 @@ It can also be registered as a transform. For example:: Returns the largest integer value not greater than a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Floor >>> Vector.objects.create(x=5.4, y=-2.3) @@ -1037,7 +1111,9 @@ Usage example:: >>> vector.x_floor, vector.y_floor (5.0, -3.0) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Floor @@ -1052,7 +1128,9 @@ It can also be registered as a transform. For example:: Returns the natural logarithm a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Ln >>> Vector.objects.create(x=5.4, y=233.0) @@ -1060,7 +1138,9 @@ Usage example:: >>> vector.x_ln, vector.y_ln (1.6863989535702288, 5.4510384535657) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Ln @@ -1076,7 +1156,9 @@ It can also be registered as a transform. For example:: Accepts two numeric fields or expressions and returns the logarithm of the first to base of the second. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Log >>> Vector.objects.create(x=2.0, y=4.0) @@ -1092,7 +1174,9 @@ Usage example:: Accepts two numeric fields or expressions and returns the remainder of the first divided by the second (modulo operation). -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Mod >>> Vector.objects.create(x=5.4, y=2.3) @@ -1115,7 +1199,9 @@ Returns the value of the mathematical constant ``π``. Accepts two numeric fields or expressions and returns the value of the first raised to the power of the second. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Power >>> Vector.objects.create(x=2, y=-2) @@ -1130,7 +1216,9 @@ Usage example:: Converts a numeric field or expression from degrees to radians. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Radians >>> Vector.objects.create(x=-90, y=180) @@ -1138,7 +1226,9 @@ Usage example:: >>> vector.x_r, vector.y_r (-1.5707963267948966, 3.141592653589793) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Radians @@ -1162,7 +1252,9 @@ Rounds a numeric field or expression to ``precision`` (must be an integer) decimal places. By default, it rounds to the nearest integer. Whether half values are rounded up or down depends on the database. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Round >>> Vector.objects.create(x=5.4, y=-2.37) @@ -1170,7 +1262,9 @@ Usage example:: >>> vector.x_r, vector.y_r (5.0, -2.4) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Round @@ -1185,7 +1279,9 @@ It can also be registered as a transform. For example:: Returns the sign (-1, 0, 1) of a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Sign >>> Vector.objects.create(x=5.4, y=-2.3) @@ -1193,7 +1289,9 @@ Usage example:: >>> vector.x_sign, vector.y_sign (1, -1) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Sign @@ -1208,7 +1306,9 @@ It can also be registered as a transform. For example:: Returns the sine of a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Sin >>> Vector.objects.create(x=5.4, y=-2.3) @@ -1216,7 +1316,9 @@ Usage example:: >>> vector.x_sin, vector.y_sin (-0.7727644875559871, -0.7457052121767203) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Sin @@ -1231,7 +1333,9 @@ It can also be registered as a transform. For example:: Returns the square root of a nonnegative numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Sqrt >>> Vector.objects.create(x=4.0, y=12.0) @@ -1239,7 +1343,9 @@ Usage example:: >>> vector.x_sqrt, vector.y_sqrt (2.0, 3.46410) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Sqrt @@ -1254,7 +1360,9 @@ It can also be registered as a transform. For example:: Returns the tangent of a numeric field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Tan >>> Vector.objects.create(x=0, y=12) @@ -1262,7 +1370,9 @@ Usage example:: >>> vector.x_tan, vector.y_tan (0.0, -0.6358599286615808) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import FloatField >>> from django.db.models.functions import Tan @@ -1287,7 +1397,9 @@ function. Like :class:`Length`, it can be registered as a transform on ``IntegerField``. The default lookup name is ``chr``. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Chr >>> Author.objects.create(name='Margaret Smith') @@ -1311,7 +1423,9 @@ This function will never have a null result. On backends where a null argument results in the entire expression being null, Django will ensure that each null part is converted to an empty string first. -Usage example:: +Usage example: + +.. code-block:: pycon >>> # Get the display name as "name (goes_by)" >>> from django.db.models import CharField, Value as V @@ -1333,7 +1447,9 @@ Usage example:: Returns the first ``length`` characters of the given text field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Left >>> Author.objects.create(name='Margaret Smith') @@ -1349,7 +1465,9 @@ Usage example:: Accepts a single text field or expression and returns the number of characters the value has. If the expression is null, then the length will also be null. -Usage example:: +Usage example: + +.. code-block:: pycon >>> # Get the length of the name and goes_by fields >>> from django.db.models.functions import Length @@ -1360,7 +1478,9 @@ Usage example:: >>> print(author.name_length, author.goes_by_length) (14, None) -It can also be registered as a transform. For example:: +It can also be registered as a transform. For example: + +.. code-block:: pycon >>> from django.db.models import CharField >>> from django.db.models.functions import Length @@ -1378,7 +1498,9 @@ representation. It can also be registered as a transform as described in :class:`Length`. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Lower >>> Author.objects.create(name='Margaret Smith') @@ -1395,7 +1517,9 @@ Returns the value of the given text field or expression padded on the left side with ``fill_text`` so that the resulting value is ``length`` characters long. The default ``fill_text`` is a space. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models import Value >>> from django.db.models.functions import LPad @@ -1423,7 +1547,9 @@ string. It can also be registered as a transform as described in :class:`Length`. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import MD5 >>> Author.objects.create(name='Margaret Smith') @@ -1444,7 +1570,9 @@ than one character long. It can also be registered as a transform as described in :class:`Length`. The default lookup name is ``ord``. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Ord >>> Author.objects.create(name='Margaret Smith') @@ -1460,7 +1588,9 @@ Usage example:: Returns the value of the given text field or expression repeated ``number`` times. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Repeat >>> Author.objects.create(name='John', alias='j') @@ -1478,7 +1608,9 @@ Replaces all occurrences of ``text`` with ``replacement`` in ``expression``. The default replacement text is the empty string. The arguments to the function are case-sensitive. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models import Value >>> from django.db.models.functions import Replace @@ -1500,7 +1632,9 @@ expression in reverse order. It can also be registered as a transform as described in :class:`Length`. The default lookup name is ``reverse``. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Reverse >>> Author.objects.create(name='Margaret Smith') @@ -1515,7 +1649,9 @@ Usage example:: Returns the last ``length`` characters of the given text field or expression. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Right >>> Author.objects.create(name='Margaret Smith') @@ -1553,7 +1689,9 @@ the string. They can also be registered as transforms as described in :class:`Length`. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import SHA1 >>> Author.objects.create(name='Margaret Smith') @@ -1581,7 +1719,9 @@ Returns a positive integer corresponding to the 1-indexed position of the first occurrence of ``substring`` inside ``string``, or 0 if ``substring`` is not found. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models import Value as V >>> from django.db.models.functions import StrIndex @@ -1613,7 +1753,9 @@ Returns a substring of length ``length`` from the field or expression starting at position ``pos``. The position is 1-indexed, so the position must be greater than 0. If ``length`` is ``None``, then the rest of the string will be returned. -Usage example:: +Usage example: + +.. code-block:: pycon >>> # Set the alias to the first 5 characters of the name as lowercase >>> from django.db.models.functions import Lower, Substr @@ -1631,7 +1773,9 @@ Usage example:: Returns the value of the given text field or expression with leading and trailing spaces removed. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Trim >>> Author.objects.create(name=' John ', alias='j') @@ -1650,7 +1794,9 @@ representation. It can also be registered as a transform as described in :class:`Length`. -Usage example:: +Usage example: + +.. code-block:: pycon >>> from django.db.models.functions import Upper >>> Author.objects.create(name='Margaret Smith') |