Database Functions¶
MySQL/MariaDB-specific database functions for the ORM.
The following can be imported from django_mysql.models.functions
.
Control Flow Functions¶
- class django_mysql.models.functions.If(condition, true, false=None)¶
Evaluates the expression
condition
and returns the value of the expressiontrue
if true, and the result of expressionfalse
if false. Iffalse
is not given, it will beValue(None)
, i.e.NULL
.Usage example:
>>> Author.objects.annotate( ... is_william=If(Q(name__startswith="William "), True, False) ... ).values_list("name", "is_william") [('William Shakespeare', True), ('Ian Fleming', False), ('William Wordsworth', True)]
Numeric Functions¶
- class django_mysql.models.functions.CRC32(expression)¶
Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is
NULL
if the argument isNULL
. The argument is expected to be a string and (if possible) is treated as one if it is not.Usage example:
>>> Author.objects.annotate(description_crc=CRC32("description"))
String Functions¶
- class django_mysql.models.functions.ConcatWS(*expressions, separator=',')¶
ConcatWS
stands for Concatenate With Separator and is a special form ofConcat
(included in Django). It concatenates all of its argument expressions as strings with the givenseparator
. SinceNULL
values are skipped, unlike inConcat
, you can use the empty string as a separator and it acts as aNULL
-safe version ofConcat
.If
separator
is a string, it will be turned into aValue
. If you wish to join with the value of a field, you can pass in anF
object for that field.Usage example:
>>> Author.objects.annotate(sales_list=ConcatWS("sales_eu", "sales_us"))
- class django_mysql.models.functions.ELT(number, values)¶
Given a numerical expression
number
, it returns thenumber
th element fromvalues
, 1-indexed. Ifnumber
is less than 1 or greater than the number of expressions, it will returnNone
. It is the complement of theField
function.Note that if
number
is a string, it will refer to a field, whereas members ofvalues
that are strings will be wrapped withValue
automatically and thus interpreted as the given string. This is for convenience with the most common usage pattern where you have the list pre-loaded in python, e.g. achoices
field. If you want to refer to a column, use Django’sF()
class.Usage example:
>>> # Say Person.life_state is either 1 (alive), 2 (dead), or 3 (M.I.A.) >>> Person.objects.annotate(state_name=ELT("life_state", ["Alive", "Dead", "M.I.A."]))
- class django_mysql.models.functions.Field(expression, values)¶
Given an
expression
and a list of stringsvalues
, returns the 1-indexed location of theexpression
’s value invalues
, or 0 if not found. This is commonly used withorder_by
to keep groups of elements together. It is the complement of theELT
function.Note that if
expression
is a string, it will refer to a field, whereas if any member ofvalues
is a string, it will automatically be wrapped withValue
and refer to the given string. This is for convenience with the most common usage pattern where you have the list of things pre-loaded in Python, e.g. in a field’schoices
. If you want to refer to a column, use Django’sF()
class.Usage example:
>>> # Females, then males - but other values of gender (e.g. empty string) first >>> Person.objects.all().order_by(Field("gender", ["Female", "Male"]))
XML Functions¶
- class django_mysql.models.functions.UpdateXML(xml_target, xpath_expr, new_xml)¶
Returns the XML fragment
xml_target
with the single match forxpath_expr
replaced with the xml fragmentnew_xml
. If nothing matchesxpath_expr
, or if multiple matches are found, the originalxml_target
is returned unchanged.This can be used for single-query updates of text fields containing XML.
Note that if
xml_target
is given as a string, it will refer to a column, whilst if eitherxpath_expr
ornew_xml
are strings, they will be used as strings directly. If you wantxpath_expr
ornew_xml
to refer to columns, use Django’sF()
class.Usage example:
# Remove 'sagacity' from all authors' xml_attrs >>> Author.objects.update(xml_attrs=UpdateXML("xml_attrs", "/sagacity", ""))
- class django_mysql.models.functions.XMLExtractValue(xml_frag, xpath_expr)¶
Returns the text (
CDATA
) of the first text node which is a child of the element(s) in the XML fragmentxml_frag
matched by the XPath expressionxpath_expr
. In SQL this function is calledExtractValue
; the class has theXML
prefix to make it clearer what kind of values are it extracts.Note that if
xml_frag
is given as a string, it will refer to a column, whilst ifxpath_expr
is a string, it will be used as a string. If you wantxpath_expr
to refer to a column, use Django’sF()
class.Usage example:
# Count the number of authors with 'sagacity' in their xml_attrs >>> num_authors_with_sagacity = ( ... Author.objects.annotate( ... has_sagacity=XMLExtractValue("xml_attrs", "count(/sagacity)") ... ) ... .filter(has_sagacity="1") ... .count() ... )
Regexp Functions¶
Note
These work with MariaDB 10.0.5+ only, which includes PCRE regular expressions and these extra functions to use them. More information can be found in its documentation.
- class django_mysql.models.functions.RegexpInstr(expression, regex)¶
Returns the 1-indexed position of the first occurrence of the regular expression
regex
in the string value ofexpression
, or 0 if it was not found.Note that if
expression
is given as a string, it will refer to a column, whilst ifregex
is a string, it will be used as a string. If you wantregex
to refer to a column, use Django’sF()
class.Docs: MariaDB.
Usage example:
>>> Author.objects.annotate(name_pos=RegexpInstr("name", r"ens")).filter(name_pos__gt=0) [<Author: Charles Dickens>, <Author: Robert Louis Stevenson>]
- class django_mysql.models.functions.RegexpReplace(expression, regex, replace)¶
Returns the string value of
expression
with all occurrences of the regular expressionregex
replaced by the stringreplace
. If no occurrences are found, then subject is returned as is.Note that if
expression
is given as a string, it will refer to a column, whilst if eitherregex
orreplace
are strings, they will be used as strings. If you wantregex
orreplace
to refer to columns, use Django’sF()
class.Docs: MariaDB.
Usage example:
>>> Author.objects.create(name="Charles Dickens") >>> Author.objects.create(name="Roald Dahl") >>> qs = Author.objects.annotate( ... surname_first=RegexpReplace("name", r"^(.*) (.*)$", r"\2, \1") ... ).order_by("surname_first") >>> qs [<Author: Roald Dahl>, <Author: Charles Dickens>] >>> qs[0].surname_first "Dahl, Roald"
- class django_mysql.models.functions.RegexpSubstr(expression, regex)¶
Returns the part of the string value of
expression
that matches the regular expressionregex
, or an empty string ifregex
was not found.Note that if
expression
is given as a string, it will refer to a column, whilst ifregex
is a string, it will be used as a string. If you wantregex
to refer to a column, use Django’sF()
class.Docs: MariaDB.
Usage example:
>>> Author.objects.create(name="Euripides") >>> Author.objects.create(name="Frank Miller") >>> Author.objects.create(name="Sophocles") >>> Author.objects.annotate(name_has_space=CharLength(RegexpSubstr("name", r"\s"))).filter( ... name_has_space=0 ... ) [<Author: Euripides>, <Author: Sophocles>]
Information Functions¶
- class django_mysql.models.functions.LastInsertId(expression=None)¶
With no argument, returns the last value added to an auto-increment column, or set by another call to
LastInsertId
with an argument. With an argument, sets the ‘last insert id’ value to the value of the given expression, and returns that value. This can be used to implement simpleUPDATE ... RETURNING
style queries.This function also has a class method:
- get(using=DEFAULT_DB_ALIAS)¶
Returns the value set by a call to
LastInsertId()
with an argument, by performing a single query. It is stored per-connection, hence you may need to pass the alias of the connection that set theLastInsertId
asusing
.Note
Any queries on the database connection between setting
LastInsertId
and callingLastInsertId.get()
can reset the value. These might come from Django, which can issue multiple queries forupdate()
with multi-table inheritance, or fordelete()
with cascading.
Usage examples:
>>> Countable.objects.filter(id=1).update(counter=LastInsertId("counter") + 1) 1 >>> # Get the pre-increase value of 'counter' as stored on the server >>> LastInsertId.get() 242 >>> Author.objects.filter(id=1, age=LastInsertId("age")).delete() 1 >>> # We can also use the stored value directly in a query >>> Author.objects.filter(id=2).update(age=LastInsertId()) 1 >>> Author.objects.get(id=2).age 35
JSON Database Functions¶
These functions work with data stored in Django’s JSONField
on MySQL and
MariaDB only. JSONField
is built in to Django 3.1+ and can be installed on
older Django versions with the
django-jsonfield-backport
package.
These functions use JSON paths to address content inside JSON documents - for more information on their syntax, refer to the docs: MySQL / MariaDB.
- class django_mysql.models.functions.JSONExtract(expression, *paths, output_field=None)¶
Given
expression
that resolves to some JSON data, extract the given JSON paths. If there is a single path, the plain value is returned; if there is more than one path, the output is a JSON array with the list of values represented by the paths. If the expression does not match for a particular JSON object, returnsNULL
.If only one path is given,
output_field
may also be given as a model field instance likeIntegerField()
, into which Django will load the value; the default isJSONField()
, as it supports all return types including the array of values for multiple paths.Note that if
expression
is a string, it will refer to a field, whereas members ofpaths
that are strings will be wrapped withValue
automatically and thus interpreted as the given string. If you want any ofpaths
to refer to a field, use Django’sF()
class.Usage examples:
>>> # Fetch a list of tuples (id, size_or_None) for all ShopItems >>> ShopItem.objects.annotate(size=JSONExtract("attrs", "$.size")).values_list("id", "size") [(1, '3m'), (3, '5nm'), (8, None)] >>> # Fetch the distinct values of attrs['colours'][0] for all items >>> ShopItem.objects.annotate( ... primary_colour=JSONExtract("attrs", "$.colours[0]") ... ).distinct().values_list("primary_colour", flat=True) ['Red', 'Blue', None]
- class django_mysql.models.functions.JSONKeys(expression, path=None)¶
Given
expression
that resolves to some JSON data containing a JSON object, return the keys in that top-level object as a JSON array, or ifpath
is given, return the keys at that path. If the path does not match, or ifexpression
is not a JSON object (e.g. it contains a JSON array instead), returnsNULL
.Note that if
expression
is a string, it will refer to a field, whereas ifpath
is a string it will be wrapped withValue
automatically and thus interpreted as the given string. If you wantpath
to refer to a field, use Django’sF()
class.>>> # Fetch the top-level keys for the first item >>> ShopItem.objects.annotate(keys=JSONKeys("attrs")).values_list("keys", flat=True)[0] ['size', 'colours', 'age', 'price', 'origin'] >>> # Fetch the keys in 'origin' for the first item >>> ShopItem.objects.annotate(keys=JSONKeys("attrs", "$.origin")).values_list( ... "keys", flat=True ... )[0] ['continent', 'country', 'town']
- class django_mysql.models.functions.JSONLength(expression, path=None)¶
Given
expression
that resolves to some JSON data, return the length of that data, or ifpath
is given, return the length of the data at that path. If the path does not match, or ifexpression
isNULL
it returnsNULL
.As per the MySQL documentation, the length of a document is determined as follows:
The length of a scalar is 1.
The length of an array is the number of array elements.
The length of an object is the number of object members.
The length does not count the length of nested arrays or objects.
Note that if
expression
is a string, it will refer to a field, whereas ifpath
is a string it will be wrapped withValue
automatically and thus interpreted as the given string. If you wantpath
to refer to a field, use Django’sF()
class.>>> # Which ShopItems don't have more than three colours? >>> ShopItem.objects.annotate(num_colours=JSONLength("attrs", "$.colours")).filter( ... num_colours__gt=3 ... ) [<ShopItem: Rainbow Wheel>, <ShopItem: Hard Candies>]
- class django_mysql.models.functions.JSONInsert(expression, data)¶
Given
expression
that resolves to some JSON data, adds to it using the dictionarydata
of JSON paths to new values. If any JSON path in thedata
dictionary does not match, or ifexpression
isNULL
, it returnsNULL
. Paths that already exist in the original data are ignored.Note that if
expression
is a string, it will refer to a field, whereas keys and values within thepairs
dictionary will be wrapped withValue
automatically and thus interpreted as the given string. If you want a key or value to refer to a field, use Django’sF()
class.>>> # Add power_level = 0 for those items that don't have power_level >>> ShopItem.objects.update(attrs=JSONInsert("attrs", {"$.power_level": 0}))
- class django_mysql.models.functions.JSONReplace(expression, data)¶
Given
expression
that resolves to some JSON data, replaces existing paths in it using the dictionarydata
of JSON paths to new values. If any JSON path within thedata
dictionary does not match, or ifexpression
isNULL
, it returnsNULL
. Paths that do not exist in the original data are ignored.Note that if
expression
is a string, it will refer to a field, whereas keys and values within thepairs
dictionary will be wrapped withValue
automatically and thus interpreted as the given string. If you want a key or value to refer to a field, use Django’sF()
class.>>> # Reset all items' monthly_sales to 0 directly in MySQL >>> ShopItem.objects.update(attrs=JSONReplace("attrs", {"$.monthly_sales": 0}))
- class django_mysql.models.functions.JSONSet(expression, data)¶
Given
expression
that resolves to some JSON data, updates it using the dictionarydata
of JSON paths to new values. If any of the JSON paths within the data dictionary does not match, or ifexpression
isNULL
, it returnsNULL
. All paths can be modified - those that did not exist before and those that did.Note that if
expression
is a string, it will refer to a field, whereas keys and values within thedata
dictionary will be wrapped withValue
automatically and thus interpreted as the given string. If you want a key or value to refer to a field, use Django’sF()
class.>>> # Modify 'size' value to '10m' directly in MySQL >>> shop_item = ShopItem.objects.latest() >>> shop_item.attrs = JSONSet("attrs", {"$.size": "10m"}) >>> shop_item.save()
- class django_mysql.models.functions.JSONArrayAppend(expression, data)¶
Given
expression
that resolves to some JSON data, adds to it using the dictionarydata
of JSON paths to new values. If a path selects an array, the new value will be appended to it. On the other hand, if a path selects a scalar or object value, that value is autowrapped within an array and the new value is added to that array. If any of the JSON paths within the data dictionary does not match, or ifexpression
isNULL
, it returnsNULL
.Note that if
expression
is a string, it will refer to a field, whereas keys and values within thedata
dictionary will be wrapped withValue
automatically and thus interpreted as the given string. If you want a key or value to refer to a field, use Django’sF()
class.>>> # Append the string '10m' to the array 'sizes' directly in MySQL >>> shop_item = ShopItem.objects.latest() >>> shop_item.attrs = JSONArrayAppend("attrs", {"$.sizes": "10m"}) >>> shop_item.save()
Dynamic Columns Functions¶
These are MariaDB 10.0+ only, and for use with DynamicField
.
- class django_mysql.models.functions.AsType(expression, data_type)¶
A partial function that should be used as part of a
ColumnAdd
expression when you want to ensure thatexpression
will be stored as a given typedata_type
. The possible values fordata_type
are the same as documented for theDynamicField
lookups.Note that this is not a valid standalone function and must be used as part of
ColumnAdd
- see below.
- class django_mysql.models.functions.ColumnAdd(expression, to_add)¶
Given
expression
that resolves to aDynamicField
(most often a field name), add/update with the dictionaryto_add
and return the new Dynamic Columns value. This can be used for atomic single-query updates on Dynamic Columns.Note that you can add optional types (and you should!). These can not be drawn from the
spec
of theDynamicField
due to ORM restrictions, so there are no guarantees about the types that will get used if you do not. To add a type cast, wrap the value with anAsType
(above) - see examples below.Docs: MariaDB.
Usage examples:
>>> # Add default 'for_sale' as INTEGER 1 to every item >>> ShopItem.objects.update(attrs=ColumnAdd("attrs", {"for_sale": AsType(1, "INTEGER")})) >>> # Fix some data >>> ShopItem.objects.filter(attrs__size="L").update( ... attrs=ColumnAdd("attrs", {"size": AsType("Large", "CHAR")}) ... )
- class django_mysql.models.functions.ColumnDelete(expression, *to_delete)¶
Given
expression
that resolves to aDynamicField
(most often a field name), delete the columns listed by the other expressionsto_delete
, and return the new Dynamic Columns value. This can be used for atomic single-query deletions on Dynamic Columns.Note that strings in
to_delete
will be wrapped withValue
automatically and thus interpreted as the given string - if they weren’t, Django would interpret them as meaning “the value in this (non-dynamic) column”. If you do mean that, useF('fieldname')
.Docs: MariaDB.
Usage examples:
>>> # Remove 'for_sail' and 'for_purchase' from every item >>> ShopItem.objects.update(attrs=ColumnDelete("attrs", "for_sail", "for_purchase"))
- class django_mysql.models.functions.ColumnGet(expression, name, data_type)¶
Given
expression
that resolves to aDynamicField
(most often a field name), return the value of the columnname
when cast to the typedata_type
, orNULL
/None
if the column does not exist. This can be used to select a subset of column values when you don’t want to fetch the whole blob. The possible values fordata_type
are the same as documented for theDynamicField
lookups.Docs: MariaDB.
Usage examples:
>>> # Fetch a list of tuples (id, size_or_None) for all items >>> ShopItem.objects.annotate(size=ColumnGet("attrs", "size", "CHAR")).values_list( ... "id", "size" ... ) >>> # Fetch the distinct values of attrs['seller']['url'] for all items >>> ShopItem.objects.annotate( ... seller_url=ColumnGet(ColumnGet("attrs", "seller", "BINARY"), "url", "CHAR") ... ).distinct().values_list("seller_url", flat=True)