Tag Archives: python

Django/Postgresql – inserting new record doesn’t return id.

You have an old database and you find the following happens.

>>> ap = ValidAirPressureCode(code='9', description='9', barcode='9', comments='
9'
)
>>> ap.save()
>>> ap.id
>>> ap = ValidAirPressureCode.objects.get(code='9')
>>> ap.id
11

In short Django doesn’t return the id of a newly inserted record. If you insert records in admin and choose to continue editing, you’ll get an error saying it can’t find a record with a null id. The problem (in my case) is that Django uses a postgres function called “pg_get_serial_sequence” to identify where the last inserted number given to a table’s record is stored but if your tables were not created using the serial type, this will return null. In my case it was because the database was nine years old and serial types did not exist then.

A proposed solution can be seen on this ticket but at the time of writing it is not implemented.

To fix it in my case I took a look at what my sequences were called. For example:

id | integer| \
    not null default \
      nextval(('t_valid_airpressure_pk_seq'::text)::regclass)

. I then wrote the following piece of middleware (just inserted a call to it in my settings.py middleware section).

class FixLastInsertId(object):

    def __init__(self):
        """ """
        def my_last_insert_id(self, cursor, table_name, pk_name):
            """ This code fails on the alc database, so we fall back to a method of
            getting the last id that does work"""

            sql = "SELECT CURRVAL(pg_get_serial_sequence('%s','%s'))" % (self.quote_name(table_name), pk_name)
            cursor.execute(sql)
            result = cursor.fetchone()[0]
            if not result:
                sql = "select currval('%s_pk_seq'::regclass)" % table_name
                cursor.execute(sql)
                result = cursor.fetchone()[0]
            return result

        from django.db import connection
        connection.ops.__class__.last_insert_id = my_last_insert_id

        # Tell Django to forget about this middleware now, we have
        # had our evil way.
        from django.core.exceptions import MiddlewareNotUsed
        raise MiddlewareNotUsed

Basically I monkey patch the last_insert_id method. I call the normal code and if that fails try with my own way. All my sequences have the same naming convention (tablename_pk_seq) so it works for me.

Thanks to Ross for pointing me in the right direction, although he didn’t approve of my monkey patching solution 😉

Converting Legacy Databases to Django 1.3 – Day 0.5

I have done this quite a few times in the past, but not recently. I was given three days to convert an existing postgres database (front ended with Zope) to Django 1.3. I thought it would be useful to document what I did here for my own future reference and to record any gotchas for posterity. The database I am converting is nine years old, so plenty of cruft through the years, although the basic structure is sound.

Note that the website of this application is not particularly complicated. Most of the core work of this application is done via backend processes written with Twisted. The front end is used for displaying the system status and allowing for data to be modified, with a few additional complications. The system will work without any web front end (although this is hardly ideal) without stopping production, so the risk is minimised.

This is the first day – although I only started from the beginning of the afternoon, so it’s the first half a day.
Continue reading

Python development with virtualenvwrapper, django, buildout and mercurial – a basic example

I have been trying to streamline my python development processes over the years. Earlier this year @rossjones introduced me to virtualenvwrapper which made things even simpler. I’m about to go into quite a concentrated period of development so I thought I’d take the opportunity to document what I do to make an easily reproduceable python environment. Please let me know of any errors or suggestions for better ways of doing this as I go along.

Note, I have just noticed this is not working on OS X Lion. Looking now at why. 20110816 17:28 BST

Continue reading