Login

Arbitrary auto-generated primary keys

Author:
gsakkis
Posted:
July 23, 2010
Language:
Python
Version:
1.2
Score:
2 (after 2 ratings)

Auto-incremented primary keys are the default in Django and they are supported natively in most databases but for anything more complex things are less trivial. DB sequences are not standard, may not be available and even if they are, they are typically limited to simple integer sequence generators. This snippet bypasses the problem by allowing arbitrary auto-generated keys in Python.

The implementation needs to determine whether an IntegrityError was raised because of a duplicate primary key. Unfortunately this information is not readily available, it can be found only by sniffing the DB-specific error code and string. The current version works for MySQL (5.1 at least); comments about how to determine this in other databases will be incorporated in the snippet.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
from django.db import IntegrityError

class AutoPrimaryKeyMixin(object):
    '''A mixin class for ad-hoc auto-generated primary keys.

    This mixin allows a model to auto-generate primary key, regardless of
    whether the underlying database supports sequences natively. Moreover, the
    generators are not limited to simple integer sequences; arbitrary primary
    keys can be generated at the application level.

    Instead of locking the table, the primary key integrity constraint is
    maintained by attempting the save, catching the potential IntegrityError,
    re-generating the primary key and keep trying until it succeeds. The key
    generation callable is passed a ``num_attempts`` parameter so it may raise
    an exception if desired after a maximum number of failed attempts to break
    from the loop.

    .. note:: The current version works only in MySQL.
    '''

    def save(self, *args, **kwargs):
        if self.pk != self._meta.pk.get_default():
            return super(AutoPrimaryKeyMixin, self).save(*args, **kwargs)
        # here we just want to force_insert=True but since parameters can be
        # passed positionally and/or by name, we have to do the following acrobatics
        args = (True, False) + args[2:]
        kwargs.pop('force_insert', None)
        kwargs.pop('force_update', None)
        num_attempts = 0
        while True:
            try:
                self.pk = self._compute_new_pk(num_attempts)
                return super(AutoPrimaryKeyMixin, self).save(*args, **kwargs)
            except IntegrityError, ex:
                # XXX: ugly MySQL-specific hack to determine if the
                # IntegrityError is due to a duplicate primary key.
                # Supporting more databases will require more similar hacks
                if not (ex[0] == 1062 and ex[1].endswith("'PRIMARY'")):
                    raise
                num_attempts += 1

    def _compute_new_pk(self, num_attempts):
        raise NotImplementedError('abstract method')

#======= examples ===================================================

from django.db import models

class AutoIncrementPrimaryKeyMixin(AutoPrimaryKeyMixin):
    '''Mixin for models with positive auto-increment primary key IDs.

    Each generated ID is 1 higher than the *current* maximum ID. Thus IDs
    generated by this class are not necessarily ever increasing, unlike native
    auto-increment IDs.
    '''

    def _compute_new_pk(self, num_attempts):
        max_pk = models.Max(self._meta.pk.attname)
        aggregate = self.__class__._base_manager.aggregate
        return max(1, (aggregate(max_pk=max_pk)['max_pk'] or 0) + 1)

class AutoDecrementPrimaryKeyMixin(AutoPrimaryKeyMixin):
    '''Mixin for models with negative auto-decrement primary key IDs.

    Each generated ID is 1 lower than the *current* minimum ID. Thus IDs
    generated by this class are not necessarily ever decreasing.
    '''

    def _compute_new_pk(self, num_attempts):
        min_pk = models.Min(self._meta.pk.attname)
        aggregate = self.__class__._base_manager.aggregate
        return min(-1, (aggregate(min_pk=min_pk)['min_pk'] or 0) - 1)

class UUIDModel(AutoPrimaryKeyMixin, models.Model):
    '''Abstract base class for models with UUID primary keys.'''
    
    uuid = models.CharField(max_length=36, primary_key=True)

    class Meta:
        abstract = True

    def _compute_new_pk(self, num_attempts):
        import uuid
        return str(uuid.uuid1())

class NegIDPerson(AutoDecrementPrimaryKeyMixin, models.Model):
    name = models.CharField(max_length=100)
    age = models.PositiveIntegerField()

class UUIDPerson(UUIDModel):
    name = models.CharField(max_length=100)
    age = models.PositiveIntegerField()

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 2 months, 2 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 2 months, 3 weeks ago
  3. Serializer factory with Django Rest Framework by julio 9 months, 2 weeks ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 10 months, 1 week ago
  5. Help text hyperlinks by sa2812 11 months ago

Comments

gmandx (on July 23, 2010):

How do I use this to generate UUIDs as primary keys?

#

gsakkis (on July 23, 2010):

@gmandx, great example; I updated the snippet to include a UUID base abstract model (and also discovered and fixed a small bug).

#

Please login first before commenting.