3

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?

4
  • docs.djangoproject.com/en/3.1/ref/migration-operations/… Commented Mar 29, 2021 at 11:07
  • If you have managed = False this means you can't modify the table from your models.py, including creating indexes. Commented Mar 29, 2021 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. Commented Mar 29, 2021 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. Commented Mar 29, 2021 at 11:27

2 Answers 2

6

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.

Sign up to request clarification or add additional context in comments.

4 Comments

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.
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:
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 stackoverflow.com/questions/20288711/… - though I haven't tried this
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.
0

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

3 Comments

Could you show how that would work?
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.
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.