python - Django: models setup for using one database to populate another

Question

I'm rebuilding a personal project in Django, (a family tree), and I'm working on migrating the actual data from the old awkward database to my new model/schema, both Postgres databases. I've defined them in the DATABASES list on settings.py as 'default' and 'source'.

I've made my models and I'd like to copy the records from the old database into their corresponding table in the new database, but I'm not quite understanding how to set up the models for it to work, since the Django code uses the models/ORM to access/update/create objects, and I only have models reflecting the new schema, not the old ones.

In a coincidental case where I have a table with the exact same schema in the old and new database, I have a management command that can grab the old records from the source using my new ImagePerson model (ported_data = ImagePerson.objects.using('source').all()), since it the expected fields are the same. Then I save objects for them in the 'default': (obj, created_bool) = ImagePerson.objects.using('default').get_or_create(field=fieldvalue, etc), and it works just like I need it to.

However when I have a table where the old version is missing fields that my new model/table have, I can't use the model to access those records (which makes sense). Am I supposed to also make some kind of legacy version of each model for use in the migration? I saw a tutorial mention running ./manage.py inspectdb --database=source > models.py, but doing so didn't seem to add anything else to my file (and it would seem weird to save temporary/legacy models in there anyway). What's the right way to access the old-formatted records? Is the ORM right?

To give a specific example, I have a Notes table to hold a memory about a specific person or about a specific family. The old table used a 'type' field (1 was for person note, 2 was for family note), and a ref_id that would be the id for the person or family the note applies to. The new table instead has a person_id field and a family_id field.

I'd like my management command to be able to pull all the records from the source table, then if type=1, look up the person with id equal to the ref_id field, and save a new object in the new database with that person. I can grab them using the new Notes model with the old database like this: ported_notes = Note.objects.using('source').all(), but then if I try to access any field (like print(note_row.body)), I get an error that the result object is missing the person_id field

django.db.utils.ProgrammingError: column notes.person_id does not exist

What's the right way to approach this?

Answer 1

Creating models for your old schema definitely doesn't seem like the right approach.

One solution would be to write a data-migration, where you could use raw SQL to fetch your old data, and then use the ORW to write it to your new tables/models:

from django.db import migrations, connections

def transfer_data(apps, schema_editor):
    ModelForNewDB = apps.get_model('yourappname', 'ModelForNewDB')
    # Fetch your old data
    with connections['my_old_db'].cursor() as cursor:
        cursor.execute('select * from some_table')
        data = cursor.fetchall()
    
    # Write it to your new models
    for datum in data:
        # do something with the data / add any
        # additional values needed.
        ModelForNewDB.objects.create(...)


class Migration(migrations.Migration):

    dependencies = [
        ('yourappname', '0001_initial'),
    ]

    operations = [
        migrations.RunPython(transfer_data),
    ]

Then simply run your migrations. One thing to note however:

If you have foreignKeys etc. between tables you will need to be careful how you order the migrations. This can be done by editing your dependencies. You may even have to add a migration to allow null values for some foreign keys, and then add another one afterwards to correct this.