Django 2.2: Creating models for multiple databases

Question

I am new to Django 2.2 and I am building a small project that includes 2 databases not managed by Django. One is new and the other is legacy. I added them to the database list

DATABASES = {
    'default': {
        'ENGINE' :'django.db.backends.mysql',
        'NAME': 'petshows',
        'USER':'somecoolusername',
        'PASSWORD': 'somesecurepassword',
        'HOST': 'localhost',
        'PORT':'3306'
    },
    'pets': {
        'ENGINE' :'django.db.backends.mysql',
        'NAME': 'pets',
        'USER':'mysecureusers',
        'PASSWORD': 'somecoolpassword',
        'HOST': 'localhost',
        'PORT':'3306'
    }
}

I created models for petshows which seem to work fine. But I need to add some models so I can read foreign keys from the second database 'pets'.

My models.py

class ShowPets(models.Model):
    sp_id = models.IntegerField(primary_key=True)
    pet_id = models.IntegerField(blank=True, null=True)
    show_id = models.IntegerField(blank=True, null=True)
    created_at = models.DateTimeField(blank=True, null=True)
    updated_at = models.DateTimeField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'show_pets'


class Shows(models.Model):
    show_id = models.AutoField(primary_key=True)
    show_name = models.CharField(max_length=145, blank=True, null=True)
    show_date = models.DateField(blank=True, null=True)
    location_id = models.IntegerField(blank=True, null=True)
    org_id = models.IntegerField(blank=True, null=True)
    created_at = models.DateTimeField(blank=True, null=True)
    updated_at = models.DateTimeField(blank=True, null=True)

    def show_date_pretty(self):
        return self.show_date.strftime('%b %e %Y')

    class Meta:
        managed = False
        db_table = 'shows'

I would like to create a model for Pets, pets table in the pets database, but I cannot figure out from the documentation how to be able to access the second database as a Model and then do a join on pet_id (foreign key), via the ShowPets model and the show_pets table (in the petshows database), to the pet_id (primary key) from pets table (in the pets database) to be able to pull things like pet_name, etc.

Can anyone give me any good hints or links on how to accomplish this? Thanks!

monkut · Accepted Answer · 2019-09-16 15:46:27Z

3

Django doesn't support cross database joins with the django orm.

https://docs.djangoproject.com/en/2.2/topics/db/multi-db/#cross-database-relations

But you can manually access either database using the .using() method for querying:

Shows.objects.using('pets').all()

On save you can use the optional using= parameter to define the db to use:

show = Shows()
show.save(using='pets')

As a side note, it's common practice to make your model names singular (then the above instance creation makes more sense)

answered Sep 16, 2019 at 15:46

monkut

44.1k26 gold badges133 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django 2.2: Creating models for multiple databases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related