Postgres Views with Django: A Quick Demo

In this series so far we've talked about how to get our Django application to save uploaded images as bytea in Postgres. We've also walked through an example of a PL/Python function that processes the binary data to apply a blur filter to the uploaded image. Now, we'll show how to retrieve the blurred image from Django.

Use the function in a SQL view mapped to a Django model

Now that we have a PL/Python function, what do we do with it? The function lives in PostgreSQL, so we'll have to figure out how to call the function from Django.

You do have the option in Django to perform raw SQL queries if what you want to execute doesn't quite fit with Django's object-relational mapper (ORM). In our case, we decided to try another route. Since Django models map to database tables, could we have the values returned from our bytea_blur function live in a table as well?

Well, not quite in a database table, but we could create a database view. A view can be accessed or queried on like a regular table, and under the hood it's a stored SQL query. Django models can map to Postgres views too.

Here's the view that calls our PL/Python function:

CREATE OR REPLACE VIEW public.applyblur
 AS
 SELECT image_process_imagefile.id AS img_id,
    bytea_blur(image_process_imagefile.image_data) AS img_binary
    FROM image_process_imagefile;

We'll go ahead and define the corresponding model in Django:

image_process/models.py

class BlurredImage(models.Model):
    img_id = models.AutoField(primary_key=True)
    img_binary = models.BinaryField()

    class Meta:
        managed = False
        db_table = 'applyblur'

Our BlurredImage model has two fields, each corresponding to the columns returned by our view query. The most important part is what we've defined in our Meta subclass:

• We're telling Django that this model maps to the applyblur view (and that still works even if applyblur technically isn't a table).
• managed = False stops Django from performing any table operations on applyblur, including creating a new database table as part of a migration, or making any updates in the database if you were to change something in the model definition later. (We'd actually run into managed = False some time ago, using a "legacy" database with Django.)

We've already done the following:

1. Created our PL/Python function to process our raw image data in bytea,
2. Created a database view that gives us that processed data, again in bytea, and
3. Created a Django model that maps to that database view.

We can now go ahead and use Django's ORM to give us our blurred image.

Back to Django views (example)

This function-based Django view retrieves the blurred version of the example image we've been using:

image_process/views.py

from django.http import HttpResponse
from .models import BlurredImage


def view_blurred_example(request):
    # We only need this function to retrieve one specific image
    blurred = BlurredImage.objects.get(pk=1)
    response = HttpResponse(blurred.img_binary, content_type="image/jpeg")

    return response

We need our view to return an HttpResponse object, but additionally, the browser has to know what kind of content returns. If the binary string were all we passed into the HttpResponse constructor, you'd see only gibberish characters in the browser:

So, we include the content_type parameter in the response so the browser knows what kind of data it's dealing with. This sets the MIME type in the response's Content-Type header.

If we now set up a URLconf that references this Django view, and open the URL in our browser, the browser renders the blurred image (notice the difference from the original?):

Final notes

We've demonstrated how PL/Python can extend a Postgres database to do some pretty cool and powerful operations. We also hope this shows how you can use Postgres to get a clearer separation of concerns between data scientists and application developers.

Keep in mind that you can create PL/Python functions like these and have them be accessible on the database level, regardless of the app or client. The advanced data learning or analysis modules can be built in Python and placed in the database. The application developer can then take advantage of this work without having to reinvent the wheel. That could be another way to get more consistency and reproducibility out of your Postgres database.

It's been interesting to explore what's possible with Django, Postgres, and PL/Python. If you'd like more PL/Python content, don't forget to take a look at our other Crunchy blog posts:

• Getting Started with Postgres Functions in PL/Python
• Exploring PL/Python: Turn Postgres Table Data Into a NumPy Array
• Postgres Data Types in PL/Python

Have any of you used PL/Python as part of your data science stack? Tell us about it in the comments section below, or feel free to tweet @crunchydata.