PostgreSQL & Django
A tasty mix
PyWaw #26, 08.07.2013
Zbigniew Siciarz @zsiciarz http://siciarz.net
Slide 2
Slide 2 text
DjangoCon-inspired
• Advanced PostgreSQL in Django
by Christophe Pettus
http://www.youtube.com/watch?v=C3bZbA4jaAg
• Getting past the Django ORM limitations
with Postgres by Craig Kerstiens
http://www.youtube.com/watch?v=sXmFK_qsvlo
Slide 3
Slide 3 text
Case study
• pretty slides are pretty, but show me the code
• practical example?
• yet another online photo gallery…
• …built with Django…
• …using PostgreSQL goodies!
Slide 4
Slide 4 text
HStore
Slide 5
Slide 5 text
NoSQL in my SQL?!
• HStore is a key value store within Postgres. You can
use it similar to how you would use a dictionary within
another language […] – Craig Kerstiens, Postgres
Guide
http://postgresguide.com/sexy/hstore.html
• CREATE EXTENSION hstore;
Slide 6
Slide 6 text
This is Hastur, not HStore
http://www.goominet.com/lovecraft/
Slide 7
Slide 7 text
django-hstore
• pip install django-hstore
• exif = hstore.DictionaryField(db_index=True)
• photo.exif = {‘FocalLength’: ‘11’}
• South sometimes gets confused
• custom database backend
• who maintains the code?
Slide 8
Slide 8 text
Where do we go from here?
Slide 9
Slide 9 text
djorm-ext-hstore
• fork of django-hstore
• actively maintained and improved
• plays nice with South
• no need for custom database backend
• Yay!
Slide 10
Slide 10 text
Storing EXIF data in HStore
Slide 11
Slide 11 text
Storing EXIF data in HStore
Slide 12
Slide 12 text
Storing EXIF data in HStore
Slide 13
Slide 13 text
array
Slide 14
Slide 14 text
[]
• multiple values of the same type in one
column
• built-in; no extension needed
• variable length arrays
• maps well to a Python list
Slide 15
Slide 15 text
djorm-ext-pgarray
• same maintainer as djorm-ext-hstore and other
ORM extensions
• provides model field and form field
• pip install djorm-ext-pgarray
• or…
• https://github.com/ntucker/djorm-ext-pgarray
(better tag string parsing in ArrayFormField)
Slide 16
Slide 16 text
Tagging images
Slide 17
Slide 17 text
@>
• @> means „contains”
• zs=> select array[1,2,3] @> array[2,1];
?column?
----------
t
(1 row)
• use djorm-ext-expressions for Pythonic syntax
Slide 18
Slide 18 text
Tags in forms
Slide 19
Slide 19 text
Popular tags
Slide 20
Slide 20 text
Putting it all together
• django-pgallery
https://github.com/zsiciarz/django-pgallery
• proof-of-concept code
• TODO: tests, docs, better templates…
• pull requests are welcome :-)