Django and PostgreSQL, a happy relationship

Given at PgDay Italy 2015 23 October 2015

Transcript

1. PGDAY 2015 DJANGO AND POSTGRESQL, A HAPPY RELATIONSHIP by Iacopo Spalletti @yakkys
2. WHO AM I? Founder and Lead developer @NephilaIt django CMS core developer django CMS installer author
3. WHAT'S DJANGO? A web framework written in Python Based on an DBMS-agnostic ORM
4. DJANGO ORM But sometimes abstraction is not enough
5. POSTGRESQL EXTENSIONS PostGIS Support django-hstore django.contrib.postgres
6. DJANGO-HSTORE External application to support hstore datatype Supports Django 1.6-1.8+ Integrated editor
7. DJANGO-HSTORE Easy to use class PrinterData(models.Model): printer = models.ForeignKey(Printer) data = hstore.DictionaryField() ... objects = hstore.HStoreManager() That's all (plus run create extension hstore)
8. DJANGO-HSTORE Demo time
9. CONTRIB.POSTGRES Official module Available since 1.8 Supports many datatypes UUID HStore Array Rangefields
10. CONTRIB.POSTGRES UUID class Example(models.Model): id = models.UUIDField(default=uuid.uuid4, primary_key=True)
11. CONTRIB.POSTGRES ARRAYFIELD class Post(models.Model): name = models.CharField(max_length=200) tags = ArrayField(models.CharField(max_length=200)) Supports query functions: contains (@>), contained_by (<@), overlap (&&), len, indexing, slicing
12. CONTRIB.POSTGRES ARRAYFIELD # SQL operator: @> Post.objects.filter(tags__contains=['thoughts']) # SQL operator: <@ Post.objects.filter(tags__contained_by=['thoughts', 'django']) # SQL operator: && Post.objects.filter(tags__overlap=['thoughts']) # len Post.objects.filter(tags__len=1) # indexing Post.objects.filter(tags__1__iexact='django') # slicing Post.objects.filter(tags__1_2='thoughts')
13. CONTRIB.POSTGRES HSTORE class Post(models.Model): name = models.CharField(max_length=200) data = HStoreField() Support query functions: filter, chaining, contains, contained_by, has_key, has_any_key, has_keys
14. CONTRIB.POSTGRES HSTORE # filter Post.objects.filter(data__category='main') # chaining Post.objects.filter(data__category__contains='h') # SQL operator: @> Post.objects.filter(data__contains={'category': 'home'}) # SQL operator: <@ Post.objects.filter(data__contained_by={'category': 'home', 'author' # SQL operator: ? Post.objects.filter(data__has_key='category') # SQL operator: ?| Post.objects.filter(data__has_any_keys='category') # SQL operator: ?& Post.objects.filter(data__has_keys=['category', 'author'])
15. CONTRIB.POSTGRES RANGE FIELDS python_2_unicode_compatible class Post(models.Model): name = models.CharField(max_length=200) ... skill_range = IntegerRangeField() Supports a lot of query functions: contains, contained_by, overlap, fully_lt, fully_gt, not_lt, not_gt, adjacent_to, startswith, endswith, isempty Definition of custom range types
16. CONTRIB.POSTGRES RANGE FIELDS # SQL operator: @> Post.objects.filter(skill_range__contains=NumericRange(3, 5)) # SQL operator: <@ Post.objects.filter(skill_range__contained_by=NumericRange(2, 3)) # SQL operator: && Post.objects.filter(skill_range__overlap=NumericRange(2, 3)) # fully_lt Post.objects.filter(skill_range__fully_lt=NumericRange(3, 6)) # fully_gt Post.objects.filter(skill_range__fully_gt=NumericRange(2, 3))
17. DJANGO AND POSTGRESQL Support is evolving More goodies in 1.9
18. GRAZIE!