Kat Batuigas
4 min read
Related Articles
- 8 Steps in Writing Analytical SQL Queries
- 4 Ways to Create Date Bins in Postgres: interval, date_trunc, extract, and to_char
- Crunchy Postgres for Kubernetes 5.7: Faster Backups, Automated Snapshots, Postgres 17 and More
- pg_parquet: An Extension to Connect Postgres and Parquet
- Convert JSON into Columns and Rows with JSON_TABLE
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 ifapplyblur
technically isn't a table). managed = False
stops Django from performing any table operations onapplyblur
, 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 intomanaged = False
some time ago, using a "legacy" database with Django.)
We've already done the following:
- Created our PL/Python function to process our raw image data in bytea,
- Created a database view that gives us that processed data, again in bytea, and
- 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.
Related Articles
- 8 Steps in Writing Analytical SQL Queries
8 min read
- 4 Ways to Create Date Bins in Postgres: interval, date_trunc, extract, and to_char
8 min read
- Crunchy Postgres for Kubernetes 5.7: Faster Backups, Automated Snapshots, Postgres 17 and More
4 min read
- pg_parquet: An Extension to Connect Postgres and Parquet
4 min read
- Convert JSON into Columns and Rows with JSON_TABLE
5 min read