Download as PDF, PPTX
![Array type CREATE TABLE tictactoe (squares integer[3][3]); • Postgres 9.0+ • Django <=1.7: niwibe.github.io/djorm-pgarray • Django 1.7: bitbucket.org/schinckel/django- postgres/ • Django 1.8: django.contrib.postgres.fields.array](https://image.slidesharecdn.com/gettingmaximumofpythondjangowithpostgres9-150210001218-conversion-gate02/75/Getting-maximum-of-python-django-with-postgres-9-4-PyCon-Belarus-17-2048.jpg)
![Array type >>> Post.objects.create(name='First post', tags=['thoughts', 'django']) >>> Post.objects.create(name='Second post', tags=['thoughts']) >>> Post.objects.create(name='Third post', tags=['tutorial', 'django'])](https://image.slidesharecdn.com/gettingmaximumofpythondjangowithpostgres9-150210001218-conversion-gate02/75/Getting-maximum-of-python-django-with-postgres-9-4-PyCon-Belarus-19-2048.jpg)
![Array type >>> Post.objects.filter(tags__contains=['django']) [<Post: First post>, <Post: Third post>] >>> Post.objects.filter(tags__contains=['django', 'thoughts']) [<Post: First post>]](https://image.slidesharecdn.com/gettingmaximumofpythondjangowithpostgres9-150210001218-conversion-gate02/75/Getting-maximum-of-python-django-with-postgres-9-4-PyCon-Belarus-20-2048.jpg)
![JSONType CREATE TABLE json_test ( id serial primary key, data jsonb ); INSERT INTO json_test (data) VALUES ('{}'), ('{"a": 1}'), ('{"a": 2, "b": ["c", "d"]}'), ('{"a": 1, "b": {"c": "d", "e": true}}'), ('{"b": 2}');](https://image.slidesharecdn.com/gettingmaximumofpythondjangowithpostgres9-150210001218-conversion-gate02/75/Getting-maximum-of-python-django-with-postgres-9-4-PyCon-Belarus-26-2048.jpg)
![JSONType SELECT * FROM json_test; id | data ----+-------------------------------------- 1 | {} 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (5 rows)](https://image.slidesharecdn.com/gettingmaximumofpythondjangowithpostgres9-150210001218-conversion-gate02/75/Getting-maximum-of-python-django-with-postgres-9-4-PyCon-Belarus-27-2048.jpg)
![JSONType SELECT * FROM json_test WHERE data ?| array['a', 'b']; id | data ----+-------------------------------------- 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (4 rows)](https://image.slidesharecdn.com/gettingmaximumofpythondjangowithpostgres9-150210001218-conversion-gate02/75/Getting-maximum-of-python-django-with-postgres-9-4-PyCon-Belarus-29-2048.jpg)
![Indexes >python manage.py makemigrations --empty yourappname …… operations = [ migrations.RunSQL( "CREATE UNIQUE INDEX IDX1 " "ON Table1 (t1, t2) WHERE t2 IS NOT NULL"), ]](https://image.slidesharecdn.com/gettingmaximumofpythondjangowithpostgres9-150210001218-conversion-gate02/75/Getting-maximum-of-python-django-with-postgres-9-4-PyCon-Belarus-34-2048.jpg)
Uploaded byAlina Dolgikh
Володимир Гоцик. Getting maximum of python, django with postgres 9.4. PyCon Belarus
The document discusses the integration of PostgreSQL with Python and Django, highlighting features such as range types, array types, hstore, and JSONB support. It provides examples of how to implement these features using Django's ORM and discusses the benefits of using PostgreSQL's advanced functionalities. Additionally, it addresses indexing options and connection pooling strategies for improved performance.
Володимир Гоцик. Getting maximum of python, django with postgres 9.4. PyCon Belarus
- 1. Are you Postgresyet? PyCon Belarus, January 31, 2015 Volodymyr Hotsyk
- 2. About me • Pythondeveloper at GetGoing • PyCon Ukraine organizer • github/twitter/gmail: hotsyk
- 3. Outline • Why Postgres •Python + Postgres • Django support • Range types • Array type • HStore • JSONType • Indexes • Pools/HighLoad
- 4. Why Postgres • Windowfunctions • Flexible Datatypes • Functions • Custom Languages • Extensions
- 5. Why Postgres • ForeignData Wrappers • Conditional Constraints and Partial Indexes • Listen/Notify • Table Inheritance • Real NoSQL in SQL
- 6. Python + Postgres •Psycopg2 • pg8000 • Python3 + asyncio = aiopg
- 7. Django support • DjangoORM designed to work with all supported DBs • No Postgres specific features • Third-party apps to add support of specific features
- 8.
- 9. contrib.postgres in 1.8 •Specific model fields • ArrayField • HStoreField • Range Fields • Specific form fields and widgets • SimpleArrayField • SplitArrayField • HStoreField • Range Fields • Widgets • Specific lookups • Unaccent • Database migration operations • CreateExtension • HStoreExtension • UnaccentExtension • Validators • KeysValidator • Range validators
- 10. Range types • int4range— Range of integer • int8range — Range of bigint • numrange — Range of numeric • tsrange — Range of timestamp without time zone • tstzrange — Range of timestamp with time zone • daterange — Range of date
- 11. Range types >CREATE TABLEreservation (room int, during tsrange); >INSERT INTO reservation VALUES (1, '[2015-01-31 14:30, 2015-01-31 15:30)');
- 12. Range types +Django • 1.8: django.contrib.postgres • 1.7: bitbucket.org/schinckel/django-postgres
- 13. Range types +Django • IntegerRangeField • BigIntegerRangeField • FloatRangeField • DateTimeRangeField • DateRangeField
- 14. Range types >Event.objects.create(name='Meetup', ages=(18,70)) Query >Event.objects.filter( ages__contains=NumericRange(20, 35)) >Event.objects.filter( ages__contained_by=NumericRange(0, 55)) >Event.objects.filter( ages__overlap=NumericRange(18, 22))
- 15.
- 16.
- 17. Array type CREATE TABLEtictactoe (squares integer[3][3]); • Postgres 9.0+ • Django <=1.7: niwibe.github.io/djorm-pgarray • Django 1.7: bitbucket.org/schinckel/django- postgres/ • Django 1.8: django.contrib.postgres.fields.array
- 18. Array type from django.dbimport models from django.contrib.postgres.fields import ArrayField class Post(models.Model): name = models.CharField(max_length=200) tags = ArrayField(models.CharField(max_length=200), blank=True)
- 19. Array type >>> Post.objects.create(name='Firstpost', tags=['thoughts', 'django']) >>> Post.objects.create(name='Second post', tags=['thoughts']) >>> Post.objects.create(name='Third post', tags=['tutorial', 'django'])
- 20. Array type >>> Post.objects.filter(tags__contains=['django']) [<Post:First post>, <Post: Third post>] >>> Post.objects.filter(tags__contains=['django', 'thoughts']) [<Post: First post>]
- 21.
- 22. HStore • Key/value store •Postgres 9.1+ • >create extension hstore;
- 23. Django support • Django<= 1.7: django-hstore • Django 1.8: contrib.postgres
- 24.
- 25. JSONType • Postgres 9.4+ •Document DB in your SQL DB • Django 1.7: django-jsonfield, django-postgres • Django 1.9: contrib.postgres
- 26. JSONType CREATE TABLE json_test( id serial primary key, data jsonb ); INSERT INTO json_test (data) VALUES ('{}'), ('{"a": 1}'), ('{"a": 2, "b": ["c", "d"]}'), ('{"a": 1, "b": {"c": "d", "e": true}}'), ('{"b": 2}');
- 27. JSONType SELECT * FROMjson_test; id | data ----+-------------------------------------- 1 | {} 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (5 rows)
- 28. JSONType SELECT * FROMjson_test WHERE data = ‘{"a":1}'; id | data ----+------ 1 | {"a": 1} (1 row) SELECT * FROM json_test WHERE data @> ‘{"a":1}'; id | data ----+-------------------------------------- 2 | {"a": 1} 4 | {"a": 1, "b": {"c": "d", "e": true}} (2 rows)
- 29. JSONType SELECT * FROMjson_test WHERE data ?| array['a', 'b']; id | data ----+-------------------------------------- 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (4 rows)
- 30. JSONType SELECT * FROMjson_test WHERE data #> '{b,c}' = '"d"'; Give me objects where element b has a child object that has element c equal to the string "d". Neat. id | data ----+-------------------------------------- 4 | {"a": 1, "b": {"c": "d", "e": true}}
- 31. JSONType SELECT * FROMjson_test WHERE data #> '{b,c}' = '"d"'; Give me objects where element b has a child object that has element c equal to the string "d". Neat. id | data ----+-------------------------------------- 4 | {"a": 1, "b": {"c": "d", "e": true}}
- 32.
- 33. Indexes • Partial indexes •Multicolumn indexes
- 34. Indexes >python manage.py makemigrations--empty yourappname …… operations = [ migrations.RunSQL( "CREATE UNIQUE INDEX IDX1 " "ON Table1 (t1, t2) WHERE t2 IS NOT NULL"), ]
- 35. Pools • Postgres databaseconnections are expensive • Django 1.6+ - builtin pool • Django <1.6 - django-postgrespool, djorm-ext- pool, django-db-pool
- 36. Questions • hotsyk@hotsyk.com • twitter:@hotsyk •github:hotsyk