Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create database view from django model

Tags:

django

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  | [email protected] |
| 1000000003 | Fun4All       | [email protected]    |
| 1000000004 | Fun4All       | [email protected] |
| 1000000005 | The Toy Store | [email protected]   |
| 1000000006 | toy land      | [email protected]       |
+------------+---------------+-----------------------+
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?

like image 586
AbstProcDo Avatar asked Aug 13 '18 08:08

AbstProcDo


People also ask

How do I create a database view in Django?

The database view is created by following the SQL and it can be injected into a customized data migration with the raw SQL execution command. The next step is to create a Django model which maps to this view so we can use Django ORM to retrieve the data from the view.

Can I use Django as database?

Django comes with built-in database backends. You may subclass an existing database backends to modify its behavior, features, or configuration. You can see the current list of database engines by looking in django/db/backends.

Does Django automatically create database?

Django doesn't create databases for you automatically. You have to do this yourself manually. This is a simple package that creates your database for you automatically, if necessary, when you run migrate for the first time.

How do I view tables in Django?

If you're interested, run the command-line client for your database and type \dt (PostgreSQL), SHOW TABLES; (MariaDB, MySQL), .tables (SQLite), or SELECT TABLE_NAME FROM USER_TABLES; (Oracle) to display the tables Django created.


1 Answers

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=[
                # ...
            ],
        ),
    ]
like image 115
Willem Van Onsem Avatar answered Sep 28 '22 01:09

Willem Van Onsem