I'm using Django 2.0 and Python 3.7. I have this model and manager for attempting to find my model by a name, case-insentively. The backing database is MySql 5.7. Unfortunately, when the query actually gets created, it doesn't appear Django is doing anything to account for case-insensitivity. This is an example query that results ... What changes do I need

How do I get Django to run a case-insensitive query against my MySql 5.7 db?

I’m using Django 2.0 and Python 3.7. I have this model and manager for attempting to find my model by a name, case-insentively. The backing database is MySql 5.7.

class CoopManager(models.Manager):
    ...

    # Look up coops by a partial name (case insensitive)
    def find_by_name(self, partial_name):
        queryset = Coop.objects.filter(name__icontains=partial_name, enabled=True)
        print(queryset.query)
        return queryset


class Coop(models.Model):
    objects = CoopManager()
    name = models.CharField(max_length=250, null=False)
    type = models.ForeignKey(CoopType, on_delete=None)
    address = AddressField(on_delete=models.CASCADE)
    enabled = models.BooleanField(default=True, null=False)
    phone = PhoneNumberField(null=True)
    email = models.EmailField(null=True)
    web_site = models.TextField()

Unfortunately, when the query actually gets created, it doesn’t appear Django is doing anything to account for case-insensitivity. This is an example query that results …

SELECT `maps_coop`.`id`, `maps_coop`.`name`, `maps_coop`.`type_id`, `maps_coop`.`address_id`, `maps_coop`.`enabled`, `maps_coop`.`phone`, `maps_coop`.`email`, `maps_coop`.`web_site` FROM `maps_coop` WHERE (`maps_coop`.`name` LIKE %res% AND `maps_coop`.`enabled` = True)

What changes do I need to make so that Django executes the query in a case-insensitive way against my MySql database?

Edit: Here is the MySql output for “show create table …”

| maps_coop | CREATE TABLE `maps_coop` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) COLLATE utf8_bin NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  `phone` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `email` varchar(254) COLLATE utf8_bin DEFAULT NULL,
  `web_site` longtext COLLATE utf8_bin NOT NULL,
  `address_id` int(11) DEFAULT NULL,
  `type_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `maps_coop_address_id_f90898f3` (`address_id`),
  KEY `maps_coop_type_id_982652ce_fk_maps_cooptype_id` (`type_id`),
  CONSTRAINT `maps_coop_address_id_f90898f3_fk_address_address_id` FOREIGN KEY (`address_id`) REFERENCES `address_address` (`id`),
  CONSTRAINT `maps_coop_type_id_982652ce_fk_maps_cooptype_id` FOREIGN KEY (`type_id`) REFERENCES `maps_cooptype` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=933 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

Answer

You have to change the MySQL database/table collation to utf8_unicode_ci or utf_unicode_ci; the ci acronym at the end stands for “case insensitive”.

Also check this Q/A utf8_bin vs. utf_unicode_ci.

Advertisement

Answer