How to programatically create a database view in Django?

Question

I have following query that I ran to create a database view inside my SQLite database:

CREATE VIEW customerview AS
SELECT
    a.id
  , name
  , email
  , vat
  , street
  , number
  , postal
  , city
  , country
  , geo_lat
  , geo_lon
  , customer_id
  , is_primary
FROM customerbin_address a
  , customerbin_customer b
WHERE b.id = a.customer_id
  AND a.is_primary = 1

In models.py I added the model:

class Customerview(models.Model):
    name = models.CharField(max_length=100, db_column='name')
    email = models.EmailField(unique=True, db_column='email')
    vat = VATNumberField(countries=['NL', 'BE', 'FR', 'DE', 'UK'], blank=True, null=True, db_column='vat')
    street = models.CharField(max_length=100, db_column='street')
    number = models.IntegerField(null=True, db_column='number')
    postal = models.IntegerField(null=True, db_column='postal')
    city = models.CharField(max_length=100, db_column='city')
    country = CountryField(db_column='country')
    is_primary = models.BooleanField(null=False, db_column='is_primary')
    geo_lat = models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True, db_column='geo_lat')
    geo_lon = models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True, db_column='geo_lon')

    class Meta:
            managed = False
            db_table = 'customerview'

and in admin.py I altered the list:

@admin.register(models.Customerview)
class CustomerviewAdmin(admin.ModelAdmin):
    list_display = ('name', 'email', 'vat', 'street', 'number', 'postal', 'city', 'country', 'is_primary', 'geo_lat', 'geo_lon')
    readonly_fields = ('name', 'email', 'vat', 'street', 'number', 'postal', 'city', 'country', 'is_primary', 'geo_lat', 'geo_lon',)

How do I programatically add the database view with the query above in my application?

https://docs.djangoproject.com/en/3.1/ref/migration-operations/#django.db.migrations.operations.RunSQL — iklinac, Mar 29 '21 at 11:07
If you have `managed = False` this means you can't modify the table from your `models.py`, including creating indexes. — MichielB, Mar 29 '21 at 11:13
@iklinac How do I do this in models.py? I have two different models (Customer, Address) and the model Customerview is a model that combines both Customer and Address model with a WHERE statement. I just need to have a method where I can create a database view from my code. — andyderuyter, Mar 29 '21 at 11:26
@MichielB It's not meant to be altered, it's merely to show the query in that model and later export it to an xlsx or csv. — andyderuyter, Mar 29 '21 at 11:27

score 5 · Accepted Answer · answered Mar 29 '21 at 11:27

5

Django's migrations framework lets you execute raw SQL - https://docs.djangoproject.com/en/3.1/ref/migration-operations/#runsql

So, you could create an empty migration (manage.py makemigrations <appname> --empty) and then edit it to execute your view-creating SQL via a migrations.RunSQL() call.

answered Mar 29 '21 at 11:27

Steve Jalim

11,989
1
37
54

Is this something I can integrate with my code as well? If I ship the code to another developer, it should be possible for him/her to just 'pip install requirements.txt' and have the app up and running without the need to use commands to execute an query. – andyderuyter Mar 29 '21 at 11:32
If you add it as a migration file, it will get applied to any database where the developer runs manage.py migrate, so everyone on your team can have the same view automatically set up - that's partly what migrations are for: a way to define replicable database states across multiple users of a project :thumbsup: – Steve Jalim Mar 29 '21 at 11:36
If you're saying that the other developer might not be able to run migrations manually, and you need to automatically do it via setup.py... that might be possible. See https://stackoverflow.com/questions/20288711/post-install-script-with-python-setuptools - though I haven't tried this – Steve Jalim Mar 29 '21 at 11:40
1

Thanks, It seems like I need to have a deeper look into migrations (Django and Python in general are very new to me). Appreciate the answers. – andyderuyter Mar 29 '21 at 11:56

score 0 · Answer 2 · answered Sep 22 '21 at 14:16

0

Maybe you should try this with the get_view_str method https://pypi.org/project/django-database-view/#description

answered Sep 22 '21 at 14:16

Julian Ricardo Corrales Fierro

1
1

Could you show how that would work? – Scratte Sep 22 '21 at 15:10
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 22 '21 at 16:49
While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/29892636) – Muhammedogz Sep 23 '21 at 09:25

How to programatically create a database view in Django?

2 Answers2

Linked