Map Django Model to Legacy multi-join query

[Fred]

I've got a complex 1000+ table vendor read-only MS SqlServer database and only have SELECT privileges.  I can connect with pymssql no problem.

Of all these tables, I probably care about 50 or less.

I cannot create views unless I introduce another physical server and use the "Linked Server" option.  There are several administrative and performance hurdles to this.

This DB is not clearly documented, so I have to explore and refine my SELECT statement for each query.  I'm typically ending up with 5...10 joins per query.

Most relationships are Foreign Keys although a few are many-to-many.

I've searched without success for a way to "encapsulate" a complex SELECT statement into a django model definition.

Is there any way to do this within the django model framework?

[James Schneider]

Not sure what you mean. 

Are you looking to build the model definitions in your models.py files, or are you trying to take the results of a query against MSSQL and pass them to a constructor for a model that has already been defined? 

Either way, eventually you'll end up with a copy of the 'model' in your local DB assuming that you save the model, not sure if that is your intention or not. And it likely will not be a fun process if the data sets you are pulling do not have a similar structure to abstract into a unified model (or couple of models), which sounds like it may be the case if you have extensive data queries that both follow foreign keys or many-to-many relationships.

-James 

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8cc80725-083a-406e-a6cb-725ff6a2222c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Scot Hacker]

On Tuesday, January 20, 2015 at 8:02:57 AM UTC-8, Fred wrote:

I've got a complex 1000+ table vendor read-only MS SqlServer database and only have SELECT privileges.  I can connect with pymssql no problem.

Of all these tables, I probably care about 50 or less.

I cannot create views unless I introduce another physical server and use the "Linked Server" option. 

I am working in a similar environment - 800+ tables in an external read-only  mysql db, plus whatever internal tables I need. To set it up, I used inspectdb to generate starter models for all 800 tables and piped the results to a text file. Then I manually copy out just the model definitions for the tables I actually interact with, and remove the many rows I don't need access to, and paste those into standard Django models. Massaged those as necessary. Then used Django's routers system for getting those definitions to appear in the admin (read only of course). And am able to create custom views via the .using() syntax. It's working so far, but I haven't gotten much farther than initial setup yet. Not sure what you mean by "linked server" - I don't see that mentioned in the docs. 

./s