[Django]-Can I use a database view as a model in Django?

Just an update for those who’ll encounter this question (from Google or whatever else)…

Currently Django has a simple “proper way” to define model without managing database tables:

Options.managed

Defaults to True, meaning Django will create the appropriate database tables in syncdb and remove them as part of a reset management command. That is, Django manages the database tables’ lifecycles.

If False, no database table creation or deletion operations will be performed for this model. This is useful if the model represents an existing table or a database view that has been created by some other means. This is the only difference when managed is False. All other aspects of model handling are exactly the same as normal.

I just implemented a model using a view with postgres 9.4 and django 1.8.

I created custom migration classes like this:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('myapp', '0002_previousdependency'),
    ]

    sql = """
    create VIEW myapp_myview as
     select your view here
    """

    operations = [
        migrations.RunSQL("drop view if exists myapp_myview;"),
        migrations.RunSQL(sql)
    ]

I wrote the model as I normally would. It works for my purposes.

Note– When I ran makemigrations a new migration file was created for the model, which I manually deleted.

Full disclosure- my view is read only because I am using a view derived from a jsonb data type and have not written an ON UPDATE INSTEAD rule.

We’ve done this quite extensively in our applications with MySQL to work around the single database limitation of Django. Our application has a couple of databases living in a single MySQL instance. We can achieve cross-database model joins this way as long as we have created views for each table in the “current” database.

As far as inserts/updates into views go, with our use cases, a view is basically a “select * from [db.table];”. In other words, we don’t do any complex joins or filtering so insert/updates trigger from save() work just fine. If your use case requires such complex joins or extensive filtering, I suspect you won’t have any problems for read-only scenarios, but may run into insert/update issues. I think there are some underlying constraints in MySQL that prevent you from updating into views that cross tables, have complex filters, etc.

Anyway, your mileage may vary if you are using a RDBMS other than MySQL, but Django doesn’t really care if its sitting on top of a physical table or view. It’s going to be the RDBMS that determines whether it actually functions as you expect. As a previous commenter noted, you’ll likely be throwing syncdb out the window, although we successfully worked around it with a post-syncdb signal that drops the physical table created by Django and runs our “create view…” command. However, the post-syncdb signal is a bit esoteric in the way it gets triggered, so caveat emptor there as well.

EDIT: Of course by “post-syncdb signal” I mean “post-syncdb listener”

From Django Official Documentation, you could call the view like this:

#import library
from django.db import connection

#Create the cursor
cursor = connection.cursor()

#Write the SQL code
sql_string = 'SELECT * FROM myview'

#Execute the SQL
cursor.execute(sql_string)
result = cursor.fetchall()

Hope it helps 😉