Login

Pull ID from arbitrary sequence

Author:
nirvdrum
Posted:
July 7, 2007
Language:
Python
Version:
.96
Tags:
database sequence postgresql
Score:
1 (after 1 ratings)

Django does not currently allow one to pull ID values from arbitrarily named sequences. For example, if you did not create your ID column using the serial data type in PostgreSQL, you likely will not be able to use your sequences. This is quite a problem for those integrating with legacy databases. While ultimately the best place to fix this is django proper, this decorator will help people get by for now.

Note that in this case, all of my sequences are named "pk_TABLENAME". You'll likely have a different convention and should update the decorator appropriately. While I could have made the pattern a parameter, it didn't seem like that would gain much here.

 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
def update_id(func):
    '''A decorator for pulling a data object's ID value out of a
       user-defined sequence.  This gets around a limitation in 
       django whereby we cannot supply our own sequence names.'''
    
    def decorated_function(*args):
        # Grab a reference to the data object we want to update.
        data_object = args[0]
        
        # Only update the ID if there isn't one yet.
        if data_object.id is None:
            # Construct the new sequence name based on the table's meta data.
            sequence_name = 'pk_%s' % data_object._meta.db_table
        
            # Query the database for the next sequence value.
            from django.db import connection
            cursor = connection.cursor()
            cursor.execute("SELECT nextval(%s)", [sequence_name])
            row = cursor.fetchone()
        
            # Update the data object's ID with the returned sequence value.
            data_object.id = row[0]
        
        # Execute the function we're decorating.
        return func(*args)
    
    return decorated_function


# Example model using the decorator to grab the ID.  Note that the ID
# is a regular integer field and that it is not displayed in the admin.
class FAQ(models.Model):
    id = models.IntegerField(primary_key=True)
    category = models.ForeignKey(FAQCategory)
    question = models.CharField(maxlength=255)
    answer = models.TextField()
    published = models.BooleanField(default=False)
    list_ordering = models.FloatField(max_digits=6, decimal_places=2, default=9999)
    
    def __str__(self):
        return self.question
    
    @update_id
    def save(self):
        # Now actually save the object.
        super(FAQ, self).save()
    
    class Meta:
        db_table = 'faqs'
    
    class Admin:
        fields = (
            (None, {'fields': ('category', 'question', 'answer', 'published', 'list_ordering')}),
        )
        list_display = ('question', 'category', 'list_ordering')

More like this

  1. Prefetch id for Postgresql backend by bretth 3 years, 4 months ago
  2. PostgreSQL ON DELETE CASCADE by mjt 5 years, 9 months ago
  3. Server Side Cursors for Django's psycopg2 Backend by ryanbutterfield 4 years, 6 months ago
  4. Database Cache Management View by kedare 6 years, 8 months ago
  5. Clean up expired django.contrib.session's in a huge MySQL InnoDB table by patsplat 6 years, 8 months ago

Comments

StriveForBest (on July 29, 2015):

thanks, that was helpful. one question, don't you need to close the cursor?

#

Please login first before commenting.