0

I have two databases to connect to for my Django App. I have only read access for one of the database, from where I want to pull only a few columns for my model into the other database where I have read and write access. It does not make sense to 'inspectdb' the read access databases with 100+ tables and the 100+ columns for just a few columns.

How do I add the column from another database to my existing model in the simplest manner using a join? Can I use a raw sql query with parameters to add the field into my model?

class APPNAME(models.Model):
    class Meta:
        db_table = 'APPNAME'

    item= models.PositiveIntegerField(db_column='Item')
    issue = models.CharField(max_length=200, db_column='Issue')
    detail = models.TextField(db_column='Detail')
    created_at = models.DateTimeField(auto_now_add=True, db_column='Create Datetime')
    updated_at = models.DateTimeField(auto_now=True, db_column='Update Datetime')
    external_column = How to join this column???

Here is a simple SQL Query to showcase what I am trying to do with the Django model.

SELECT
APP.[Item]
,APP.[Issue]
,APP.[Detail]
,APP.[Create Datetime]
,APP.[Update Datetime]
,BIG.[External Column]

FROM DEVDB.dbo.APPNAME APP
INNER JOIN PRODDB.dbo.BIGTABLE BIG ON APP.[Item] = BIG.[Item]
Shantanu
  • 839
  • 13
  • 27
  • 2
    can you elaborate on 'pull a few columns'? A join is with a table, not with a column so it's not clear what's the end result you want. How do the rows in the read-only table relate to the rows in your APPNAME table, i.e. which row of the column should populate your `external_column` field? – dirkgroten Jan 12 '19 at 17:32
  • Thank you. Added the SQL query that I want translated into Django. – Shantanu Jan 12 '19 at 17:37
  • So the end result you want is that each time you retrieve an APPNAME object, you also need to query the BIG database with a JOIN to fetch the value for external_column? Or would it be acceptable to do this once when creating the object and copy the value into the external_column of the read/write database? – dirkgroten Jan 12 '19 at 17:40
  • The App is a basic CRUD one, so the APPNAME object should ideally fetch the value of [External Column] everytime a user enters a new APP.[Item] value into the app. – Shantanu Jan 12 '19 at 17:46
  • 2
    @Shantanu take a look at https://stackoverflow.com/questions/23268895/cross-database-foreign-key-error Django have no support for cross db relations. But you can have a simple model describing you fileds in BIGTABLE db in `@property` method (this should be good enough for querying single objects). And overwrite Manager `get_queryset` method or something like that, where you should get values to search over BIGTABLE db and add results as property for each record – Alex C Jan 12 '19 at 17:49

0 Answers0