Create database view from django model

Question

I learned sql "view" as a virtual table to facilitate the SQL operations, like

MySQL [distributor]> CREATE VIEW CustomerEMailList AS
    -> SELECT cust_id, cust_name, cust_email
    -> FROM Customers
    -> WHERE cust_email IS NOT NULL;
Query OK, 0 rows affected (0.026 sec)

MySQL [distributor]> select * from customeremaillist;
+------------+---------------+-----------------------+
| cust_id    | cust_name     | cust_email            |
+------------+---------------+-----------------------+
| 1000000001 | Village Toys  | sales@villagetoys.com |
| 1000000003 | Fun4All       | jjones@fun4all.com    |
| 1000000004 | Fun4All       | dstephens@fun4all.com |
| 1000000005 | The Toy Store | kim@thetoystore.com   |
| 1000000006 | toy land      | sam@toyland.com       |
+------------+---------------+-----------------------+
5 rows in set (0.014 sec)

When I checked the Django documentation subsequently, there are no such functionality to create a virtual "model table" which could simplify the data manipulation.

Should I forget the virtual table "view" when using Django ORM?

There is an extension `django-database-view`s for that: https://pypi.org/project/django-database-view/ — Willem Van Onsem, Aug 13 '18 at 08:12

Willem Van Onsem · Accepted Answer · 2021-05-19T21:23:35.847

24

Django has - as far as I know at the moment - no builtin support for views.

But you can construct such views, by using the django-database-view package.

After installing the package (for example with pip):

 pip install django-database-view

Furthermore the dbview app has to be registered in the settings.py file:

# settings.py

INSTALLED_APPS = (
    # ...
    'dbview',
    # ...
)

Now you can construct a view, this looks a bit similar to the construction of a model, except that you need to implement a view(..) function that specifies the query behind the view. Something similar to:

from django.db import models
from dbview.models import DbView

class CustomerEMailList(DbView):
    cust = models.OneToOneField(Customer, primary_key=True)
    cust_name = models.CharField()
    cust_email = models.CharField()

    @classmethod
    def view(klass):
        qs = (Customers.objects.filter(cust_email__isnull=False)
                               .values('cust_id', 'cust_name', 'cust_email'))
        return str(qs.query)

Now we can make a migrations:

./manage.py makemigrations

Now in the migration, we need to make a change: the calls to migrations.CreateModel that are related to the constructed view(s), should be changed to the CreateView of the dbview module. Something that looks like:

from django.db import migrations
from dbview import CreateView

class Migration(migrations.Migration):

    dependencies = []

    operations = [
        CreateView(
            name='CustomerEMailList',
            fields=[
                # ...
            ],
        ),
    ]

edited May 19 '21 at 21:23

answered Aug 13 '18 at 08:43

Willem Van Onsem

443,496
30
428
555

4

The core functionality of the `django-database-view` package is contained in a total of 42 lines (at this moment), so it may be interesting just to cherry-pick from the [source](https://github.com/manuelnaranjo/django-database-view/blob/master/dbview/helpers.py) and adapt to your needs, instead of introducing the external dependency. – djvg Dec 19 '18 at 10:21
1

I think this package is broken, this does not work. – Sachin G. Jun 09 '19 at 11:47
Unfortunately, it is able only for MySQL database :( – Carlos Andres Jun 04 '20 at 14:00
Thank you my friend. Just note that you need to import 'dbview.models', not just 'dbview'. So, the second line must be replaced to 'from dbview.models import DbView' – Programadores Brasil May 19 '21 at 20:48
seems from django 3.2 the reference to CreateView has changed.. from dbview import CreateView has changed to: from django.views.generic.edit import CreateView – Sindhujit Sep 27 '22 at 17:34
@Sindhujit: no, this is not a class-based view, but a database view, that thus originates from the `dbview` module, not one of the modules of Django. – Willem Van Onsem Sep 28 '22 at 04:34
@WillemVanOnsem ok . So now I installed dbview from pypi and also db2py, but it complains that ImportError: cannot import name 'db2py' – Sindhujit Sep 29 '22 at 16:38
This solution will let you both manage (i.e., create) your views with Django and make them available in your Django app. You cannot modify views though with this, here's a [fork of the repo](https://github.com/Seriouskosk/django-database-view/tree/master) with DropView implemented as well. – Akaisteph7 Aug 14 '23 at 21:14

score 22 · Answer 2 · answered Nov 10 '19 at 16:44

22

According to the Django ORM Cookbook by Agiliq, you can do it like the following.

Create view:

create view temp_user as
select id, first_name from auth_user;

Create a model which is not managed and naming a db_table explicitly:

class TempUser(models.Model):
    first_name = models.CharField(max_length=100)

    class Meta:
        managed = False
        db_table = "temp_user"

You'll be able to query then, but you'll receive an error once you try to update.

Query like always:

TempUser.objects.all().values()

I haven't tried this yet, but I certainly will.

answered Nov 10 '19 at 16:44

cslotty

1,696
20
28

4

Interesting hidden feature this. Note that you need an id field in the view, even if you don't reference it in the model, otherwise Django will complain. Also take for example a multi-table view based on a join... you don't even need to reference all the fields in the view, just the primary keys, then declare them as ForeignKey fields in the Django view model and let Django do all the relationship handling and lazy loading. – Jim Bob Aug 07 '20 at 19:42
1

This solution is good if you do not want to manage (i.e., create/modify) your views with Django but just make them available. – Akaisteph7 Aug 14 '23 at 21:05

score 3 · Answer 3 · edited Dec 19 '20 at 19:20

I created a Django plugin which you can create a view table. You can check it here, on pypi.org

Install with pip install django-view-table and set INSTALLED_APPS like this:

INSTALLED_APPS = [
    'viewtable',
]

And so, a view table model can be written as follows:

from django.db import models
from view_table.models import ViewTable

# Base table
class Book(models.Model):
    name = models.CharField(max_length=100)
    category = models.CharField(max_length=100)


# View table
class Books(ViewTable):
    category = models.CharField(max_length=100)
    count = models.IntegerField()

    @classmethod
    def get_query(self):
        # Select sql statement
        return Book.objects.values('category').annotate(count=models.Count('category')).query

Finally, create the table:

python manage.py createviewtable

Create database view from django model

3 Answers3

Linked