Can I use a database view as a model in Django?

Question

i'd like to use a view i've created in my database as the source for my django-view.

Is this possible, without using custom sql?

******13/02/09 UPDATE***********

Like many of the answers suggest, you can just make your own view in the database and then use it within the API by defining it in models.py.

some warning though:

• manage.py syncdb will not work anymore
• the view need the same thing at the start of its name as all the other models(tables) e.g if your app is called "thing" then your view will need to be called thing_$viewname

Solution 1

Since Django 1.1, you can use Options.managed for that.

For older versions, you can easily define a Model class for a view and use it like your other views. I just tested it using a Sqlite-based app and it seems to work fine. Just make sure to add a primary key field if your view's "primary key" column is not named 'id' and specify the view's name in the Meta options if your view is not called 'app_classname'.

The only problem is that the "syncdb" command will raise an exception since Django will try to create the table. You can prevent that by defining the 'view models' in a separate Python file, different than models.py. This way, Django will not see them when introspecting models.py to determine the models to create for the app and therefor will not attempt to create the table.

Solution 2

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.

Solution 3

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.

Solution 4

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"

Solution 5

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 ;-)

View more solutions

Comments

[Sam Corder]

Any idea how it would handle calling the save method on it? Some dbms's have updatable views.

[Ferdinand Beyer]

I am not sure. Chances are that Django simply tries to run an INSERT or UPDATE query on the view, but I don't have too much insight in the Django source code :-)

[spence91]

To provide some context; this feature is available in Django 1.1 upwards.

[jnns]

Keep in mind that you will run into TransactionErrors when trying to delete objects that are referenced by objects in your database view by models.ForeignKey.

[rhunwicks]

If you are using Django 1.3+ then you can avoid TransactionErrors by using ForeignKey.on_delete: user = models.ForeignKey(User, on_delete=models.DO_NOTHING)

[Ferdinand Beyer]

Sigh. No need for downvotes, folks! This is an ancient answer to an ancient question. In February 2009, there was no Options.managed yet, which appeared in Django 1.1 on July 29, 2009...

[D.W.]

If your answer is no longer appropriate/accurate today, I encourage you to delete the answer or replace it with the answer that's correct today. (Downvotes are entirely understandable if the answer is no longer accurate or the best solution today, even if the answer was accurate at the time the question was asked.) StackExchange is intended to be a repository of high-quality answers that will be useful to future readers, not just a historical archive of what the right answer was 5 years ago.

[Dan]

If you don't want to install sqlparse, you can wrap the arguments to RunSQL in [].