Every feature in whistle-stop detail.
Extra checks added to Django's check framework to ensure your Django and MySQL configurations are optimal.
$ ./manage.py check
?: (django_mysql.W001) MySQL strict mode is not set for database connection 'default'
...Django-MySQL comes with a number of extensions to QuerySet that can be
installed in a number of ways - e.g. adding the QuerySetMixin to your
existing QuerySet subclass.
SELECT COUNT(*) ... can become a slow query, since it requires a scan of
all rows; the approx_count functions solves this by returning the estimated
count that MySQL keeps in metadata. You can call it directly:
Author.objects.approx_count()Or if you have pre-existing code that calls count() on a QuerySet you
pass it, such as the Django Admin, you can set the QuerySet to do try
approx_count first automatically:
qs = Author.objects.all().count_tries_approx()
# Now calling qs.count() will try approx_count() first:ref:`Read more <approximate-counting>`
Use MySQL's query hints to optimize the SQL your QuerySets generate:
Author.objects.straight_join().filter(book_set__title__startswith="The ")
# Does SELECT STRAIGHT_JOIN ...:ref:`Read more <query_hints>`
Sometimes you need to modify every single instance of a model in a big table, without creating any long running queries that consume large amounts of resources. The 'smart' iterators traverse the table by slicing it into primary key ranges which span the table, performing each slice separately, and dynamically adjusting the slice size to keep them fast:
# Some authors to fix
bad_authors = Author.objects.filter(address="Nowhere")
# Before: bad, we can't fit all these in memory
for author in bad_authors.all():
pass
# After: good, takes small dynamically adjusted slices, wraps in atomic()
for author in bad_authors.iter_smart():
author.address = ""
author.save()
author.send_apology_email():ref:`Read more <smart-iteration>`
For interactive debugging of queries, this captures the query that the
QuerySet represents, and passes it through EXPLAIN and
pt-visual-explain to get a visual representation of the query plan:
>>> Author.objects.all().pt_visual_explain()
Table scan
rows 1020
+- Table
table myapp_author:ref:`Read more <pt-visual-explain>`
Fields that use MariaDB/MySQL-specific features!
Use MariaDB's Dynamic Columns for storing arbitrary, nested dictionaries of values:
class ShopItem(Model):
name = models.CharField(max_length=200)
attrs = DynamicField()>>> ShopItem.objects.create(name="Camembert", attrs={"smelliness": 15})
>>> ShopItem.objects.create(name="Brie", attrs={"smelliness": 5, "squishiness": 10})
>>> ShopItem.objects.filter(attrs__smelliness_INTEGER__gte=10)
[<ShopItem: Camembert>]:ref:`Read more <dynamic-columns-field>`
A field class for using MySQL's ENUM type, which allows strings that are
restricted to a set of choices to be stored in a space efficient manner:
class BookCover(Model):
color = EnumField(choices=["red", "green", "blue"])A field class for using MySQL's CHAR type, which allows strings to be
stored at a fixed width:
class Address(Model):
zip_code = FixedCharField(length=10):ref:`Read more <fixedchar-field>`
Django's :class:`~django.db.models.TextField` and
:class:`~django.db.models.BinaryField` fields are fixed at the MySQL level to
use the maximum size class for the BLOB and TEXT data types - these
fields allow you to use the other sizes, and migrate between them:
class BookBlurb(Model):
blurb = SizedTextField(size_class=3)
# Has a maximum length of 16MiB, compared to plain TextField which has
# a limit of 4GB (!):ref:`Read more <resizable-blob-text-fields>`
Some database systems, such as the Java Hibernate ORM, don't use MySQL's
bool data type for storing boolean flags and instead use BIT(1). This
field class allows you to interact with those fields:
class HibernateModel(Model):
some_bool = Bit1BooleanField()
some_nullable_bool = NullBit1BooleanField():ref:`Read more <bit1booleanfields>`
MySQL’s TINYINT type efficiently stores small integers in just one byte.
These fields allow you to use it seamlessly in Django models:
class TinyIntModel(Model):
tiny_value = TinyIntegerField() # Supports values from -128 to 127.
positive_tiny_value = PositiveTinyIntegerField() # Supports values from 0 to 255.:ref:`Read more <tinyintegerfields>`
ORM extensions to built-in fields:
>>> Author.objects.filter(name__sounds_like="Robert")
[<Author: Robert>, <Author: Rupert>]:ref:`Read more <field-lookups>`
MySQL's powerful GROUP_CONCAT statement is added as an aggregate, allowing
you to bring back the concatenation of values from a group in one query:
>>> author = Author.objects.annotate(book_ids=GroupConcat("books__id")).get(
... name="William Shakespeare"
... )
>>> author.book_ids
"1,2,5,17,29"MySQL-specific database functions for the ORM:
>>> Author.objects.annotate(
... full_name=ConcatWS("first_name", "last_name", separator=" ")
... ).first().full_name
"Charles Dickens":ref:`Read more <database_functions>`
MySQL-specific operations for django migrations:
from django.db import migrations
from django_mysql.operations import InstallPlugin
class Migration(migrations.Migration):
dependencies = []
operations = [InstallPlugin("metadata_lock_info", "metadata_lock_info.so")]:ref:`Read more <migration_operations>`
An efficient backend for Django's cache framework using MySQL features:
cache.set("my_key", "my_value") # Uses only one query
cache.get_many(["key1", "key2"]) # Only one query to do this too!
cache.set("another_key", some_big_value) # Compressed above 5kb by defaultUse MySQL as a locking server for arbitrarily named locks:
with Lock("ExternalAPI", timeout=10.0):
do_some_external_api_stuff()Easy access to global or session status variables:
if global_status.get("Threads_running") > 100:
raise BorkError("Server too busy right now, come back later")dbparams helps you include your database parameters from settings in
commandline tools with dbparams:
$ mysqldump $(python manage.py dbparams) > dump.sql:ref:`Read more <management_commands>`
Set some MySQL server variables on a test case for every method or just a specific one:
class MyTests(TestCase):
@override_mysql_variables(SQL_MODE="ANSI")
def test_it_works_in_ansi_mode(self):
self.run_it()