PostgreSQL & Django - a tasty mix

My talk at PyWaw #26.
http://www.pywaw.org/08-07-2013

Transcript

1. PostgreSQL & Django
   A tasty mix
   PyWaw #26, 08.07.2013
   Zbigniew Siciarz @zsiciarz http://siciarz.net
   

   View Slide

2. 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
   

   View Slide

3. Case study
   • pretty slides are pretty, but show me the code
   • practical example?
   • yet another online photo gallery…
   • …built with Django…
   • …using PostgreSQL goodies!
   

   View Slide

4. HStore
   

   View Slide

5. 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;
   

   View Slide

6. This is Hastur, not HStore
   http://www.goominet.com/lovecraft/
   

   View Slide

7. 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?
   

   View Slide

8. Where do we go from here?
   

   View Slide

9. djorm-ext-hstore
   • fork of django-hstore
   • actively maintained and improved
   • plays nice with South
   • no need for custom database backend
   • Yay!
   

   View Slide

10. Storing EXIF data in HStore
    

    View Slide

11. Storing EXIF data in HStore
    

    View Slide

12. Storing EXIF data in HStore
    

    View Slide

13. array
    

    View Slide

14. []
    • multiple values of the same type in one
    column
    • built-in; no extension needed
    • variable length arrays
    • maps well to a Python list
    

    View Slide

15. 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)
    

    View Slide

16. Tagging images
    

    View Slide

17. @>
    • @> means „contains”
    • zs=> select array[1,2,3] @> array[2,1];
    ?column?
    ----------
    t
    (1 row)
    • use djorm-ext-expressions for Pythonic syntax
    

    View Slide

18. Tags in forms
    

    View Slide

19. Popular tags
    

    View Slide

20. 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 :-)
    

    View Slide

21. Questions?
    

    View Slide

22. Thank you!
    

    View Slide