Wednesday, May 16th, 2012

South is absolutely brilliant

South is absolutely brilliant. I never expected it to properly convert over my data as easily as it did. Basically, I originally had a monolithic table, which I wanted to convert into 3 separate tables linked together using a ForeignKey. The reason was, that 2 fields contains mostly the same information, and re-typing it, and organizing the data was becoming a tad tedious. Here is the first Python script I ran after creating the new tables. This script automatically separates the data into the required tables for me:

from tickets.models import Taxonomy, Category, Reason

print "Starting Taxonomy migration process..."
for tax in Taxonomy.objects.all():
	print "Processing %s..." % tax
	try:
		ci = Category.objects.get(title=tax.ci)
	except Category.DoesNotExist:
		ci = Category.objects.create(title=tax.ci)
		print "Created Category %s." % ci
	try:
		r = Reason.objects.get(title=tax.reason)
	except Reason.DoesNotExist:
		r = Reason.objects.create(title=tax.reason)
		print "Created Reason %s." % r
	tax.ci = ci.pk
	tax.reason = r.pk
	tax.save()
	print "Linked PKs, finished."

As you can see, it replaces the actual category name and reason with just the PK, at first I wasn't sure what South would do with the data if I just convert a CharField over to a ForeignKey. Here are the changes to the main model:

	ci = models.CharField(max_length=90, blank=True, verbose_name='CI Unique ID')
	reason = models.CharField(max_length=90)
# To:
	ci = models.ForeignKey(Category, blank=True, verbose_name='CI Unique ID')
	reason = models.ForeignKey(Reason)

Here is the migration data generated by South:

class Migration(SchemaMigration):

    def forwards(self, orm):
        
        # Renaming column for 'Taxonomy.ci' to match new field type.
        db.rename_column('tickets_taxonomy', 'ci', 'ci_id')
        # Changing field 'Taxonomy.ci'
        db.alter_column('tickets_taxonomy', 'ci_id', self.gf('django.db.models.fields.related.ForeignKey')(to=orm['tickets.Category']))

        # Adding index on 'Taxonomy', fields ['ci']
        db.create_index('tickets_taxonomy', ['ci_id'])

        # Renaming column for 'Taxonomy.reason' to match new field type.
        db.rename_column('tickets_taxonomy', 'reason', 'reason_id')
        # Changing field 'Taxonomy.reason'
        db.alter_column('tickets_taxonomy', 'reason_id', self.gf('django.db.models.fields.related.ForeignKey')(to=orm['tickets.Reason'], max_length=90))

        # Adding index on 'Taxonomy', fields ['reason']
        db.create_index('tickets_taxonomy', ['reason_id'])

All in all, there was almost no work required, besides writing up a quick Python script to move the actual data to a new table, and place the PK into it. According to the database, the fields are now INTs, as they should be for ForeignKeys.

Comment #1: Posted 2 years, 3 months ago by Adam Skutt

Foreign Keys are not pointers and need not be integers. In many correctly designed schemas, they probably will not be integers.

Comment #2: Posted 2 years, 3 months ago by robert

South's all well and good until you need to use custom SQL.

About Me

My Photo
Names Kevin, hugely into UNIX technologies, not just Linux. I've dabbled with the demons, played with the Sun, and now with the Penguins.




Kevin Veroneau Consulting Services
Do you require the services of a Django contractor? Do you need both a website and hosting services? Perhaps I can help.

This Month

If you like what you read, please consider donating to help with hosting costs, and to fund future books to review.

Python Powered | © 2012-2014 Kevin Veroneau