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, 8 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, 8 months ago by robert

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

Python Powered | © 2012-2014 Kevin Veroneau