Adds first-class support for PostgreSQL Views in the Django ORM
Install via pip:
pip install django-pgviews
Add to installed applications in settings.py:
INSTALLED_APPS = (
# ...
'django_pgviews',
)from django.db import models
from django_pgviews import view as pg
class Customer(models.Model):
name = models.CharField(max_length=100)
post_code = models.CharField(max_length=20)
is_preferred = models.BooleanField(default=False)
class Meta:
app_label = 'myapp'
class PreferredCustomer(pg.View):
projection = ['myapp.Customer.*',]
dependencies = ['myapp.OtherView',]
sql = """SELECT * FROM myapp_customer WHERE is_preferred = TRUE;"""
class Meta:
app_label = 'myapp'
db_table = 'myapp_preferredcustomer'
managed = FalseNOTE It is important that we include the managed = False in the Meta so
Django 1.7 migrations don't attempt to create DB tables for this view.
The SQL produced by this might look like:
CREATE VIEW myapp_preferredcustomer AS
SELECT * FROM myapp_customer WHERE is_preferred = TRUE;
To create all your views, run python manage.py sync_pgviews
You can also specify field names, which will map onto fields in your View:
from django_pgviews import view as pg
VIEW_SQL = """
SELECT name, post_code FROM myapp_customer WHERE is_preferred = TRUE
"""
class PreferredCustomer(pg.View):
name = models.CharField(max_length=100)
post_code = models.CharField(max_length=20)
sql = VIEW_SQLTo map onto a View, simply extend pg_views.view.View, assign SQL to the
sql argument and define a db_table. You must always set managed = False
on the Meta class.
Views can be created in a number of ways:
- Define fields to map onto the VIEW output
- Define a projection that describes the VIEW fields
Define the fields as you would with any Django Model:
from django_pgviews import view as pg
VIEW_SQL = """
SELECT name, post_code FROM myapp_customer WHERE is_preferred = TRUE
"""
class PreferredCustomer(pg.View):
name = models.CharField(max_length=100)
post_code = models.CharField(max_length=20)
sql = VIEW_SQL
class Meta:
managed = False
db_table = 'my_sql_view'django-pgviews can take a projection to figure out what fields it needs to
map onto for a view. To use this, set the projection attribute:
from django_pgviews import view as pg
class PreferredCustomer(pg.View):
projection = ['myapp.Customer.*',]
sql = """SELECT * FROM myapp_customer WHERE is_preferred = TRUE;"""
class Meta:
db_table = 'my_sql_view'
managed = FalseThis will take all fields on myapp.Customer and apply them to
PreferredCustomer
Sometimes your models change and you need your Database Views to reflect the new data. Updating the View logic is as simple as modifying the underlying SQL and running:
python manage.py sync_pgviews --force
This will forcibly update any views that conflict with your new SQL.
You can specify other views you depend on. This ensures the other views are
installed beforehand. Using dependencies also ensures that your views get
refreshed correctly when using sync_pgviews --force.
Note: Views are synced after the Django application has migrated and adding models to the dependency list will cause syncing to fail.
Example:
from django_pgviews import view as pg
class PreferredCustomer(pg.View):
dependencies = ['myapp.OtherView',]
sql = """SELECT * FROM myapp_customer WHERE is_preferred = TRUE;"""
class Meta:
app_label = 'myapp'
db_table = 'myapp_preferredcustomer'
managed = FalsePostgres 9.3 and up supports materialized views which allow you to cache the results of views, potentially allowing them to load faster.
However, you do need to manually refresh the view. To do this automatically, you can attach signals and call the refresh function.
Example:
from django_pgviews import view as pg
VIEW_SQL = """
SELECT name, post_code FROM myapp_customer WHERE is_preferred = TRUE
"""
class Customer(models.Model):
name = models.CharField(max_length=100)
post_code = models.CharField(max_length=20)
is_preferred = models.BooleanField(default=True)
class PreferredCustomer(pg.MaterializedView):
name = models.CharField(max_length=100)
post_code = models.CharField(max_length=20)
sql = VIEW_SQL
@receiver(post_save, sender=Customer)
def customer_saved(sender, action=None, instance=None, **kwargs):
PreferredCustomer.refresh()Postgres 9.4 and up allow materialized views to be refreshed concurrently, without blocking reads, as long as a
unique index exists on the materialized view. To enable concurrent refresh, specify the name of a column that can be
used as a unique index on the materialized view. Unique index can be defined on more than one column of a materialized
view. Once enabled, passing concurrently=True to the model's refresh method will result in postgres performing the
refresh concurrently. (Note that the refresh method itself blocks until the refresh is complete; concurrent refresh is
most useful when materialized views are updated in another process or thread.)
Example:
from django_pgviews import view as pg
VIEW_SQL = """
SELECT id, name, post_code FROM myapp_customer WHERE is_preferred = TRUE
"""
class PreferredCustomer(pg.MaterializedView):
concurrent_index = 'id, post_code'
sql = VIEW_SQL
name = models.CharField(max_length=100)
post_code = models.CharField(max_length=20)
@receiver(post_save, sender=Customer)
def customer_saved(sender, action=None, instance=None, **kwargs):
PreferredCustomer.refresh(concurrently=True)You can define any table name you wish for your views. They can even live inside your own custom PostgreSQL schema.
from django_pgviews import view as pg
class PreferredCustomer(pg.View):
sql = """SELECT * FROM myapp_customer WHERE is_preferred = TRUE;"""
class Meta:
db_table = 'my_custom_schema.preferredcustomer'
managed = Falsedjango-pgviews 0.5.0 adds the ability to listen to when a post_sync event has
occurred.
Fired every time a VIEW is synchronised with the database.
Provides args:
sender- View Classupdate- Whether the view to be updatedforce- Whetherforcewas passedstatus- The result of creating the view e.g.EXISTS,FORCE_REQUIREDhas_changed- Whether the view had to change
Sent after all Postgres VIEWs are synchronised.
Provides args:
sender- AlwaysNone
| Django Version | Django-PGView Version |
|---|---|
| 1.4 and down | Unsupported |
| 1.5 | 0.0.1 |
| 1.6 | 0.0.3 |
| 1.7 | 0.0.4 |
| 1.9 | 0.1.0 |
| 1.10 | 0.2.0 |
Django 1.7 changed how models are loaded so that it's no longer possible to do
sql = str(User.objects.all().query) because the dependent models aren't
yet loaded by Django.
You now have to use the .view module directly.
When updating to Django 1.10, if you see AttributeError: can't set attribute
when you try to migrate or run tests, you need to check your migrations for
where _base_manager or _default_manager get set on the model and replace it
with objects inside the migration.
This also applies to Django MPTT who have covered this in a bit more detail.
Django PGViews supports Python 3 in versions 0.0.7 and above.
Changed from django.utils import six to import six in the views.py file, and added six as a dependency module due to the module being removed from Django.
Removed default_app_config.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent