Turtles all the way down

Django has a wonderful ORM which allows construction of complex SQL queries. But just how do we get from Pony.objects.all() to SELECT ... FROM ponies_pony? Grab your boots, your mining helmet and your weapons of choice, we're going hunting in the ORM. Will there be dragons in the depths, or is it just turtles all the way down?

Transcript

1. Turtles all the way down Marc Tamlyn DjangoVillage Keynote
2. The ORM
3. The ORM • Maps objects to underlying relational data store • Allows powerful expressions for interacting with that data store • How does it work?
4. The ORM
5. The ORM
6. Something simple class Dragon(models.Model): name = models.CharField(max_length=255) trained = models.BooleanField(default=False) trainer = models.ForeignKey('Trainer')
7. Dragon.objects.filter(trained=False)
8. Classes Call graph
9. Model Classes Call graph
10. Model Manager
11. Model Manager Manager.filter()
12. Model Manager Manager.filter() Manager.get_queryset()
13. Model Manager Queryset Manager.filter() Manager.get_queryset()
14. Model Manager Queryset Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude()
15. Model Manager Queryset Query Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude()
16. Model Manager Queryset Query Q Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q()
17. Model Manager Queryset Query Q Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q()
18. Model Manager Queryset Query Q WhereNode Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q() WhereNode() ! ! ! ! ! ! ! ! !
19. Model Manager Queryset Query Q WhereNode Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q() WhereNode() Query.build_filter() ! ! ! ! ! ! ! ! WhereNode.add()
20. Model Manager Queryset Query Q WhereNode Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q() WhereNode() Query.build_filter() Query.solve_lookup_type() ! ! ! ! ! ! WhereNode.add()
21. Model Manager Queryset Query Q WhereNode Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q() WhereNode() Query.build_filter() Query.solve_lookup_type() Query.prepare_lookup_value() ! ! ! ! ! ! WhereNode.add()
22. Model Manager Queryset Query Q WhereNode Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q() WhereNode() Query.build_filter() Query.solve_lookup_type() Query.prepare_lookup_value() Query.setup_joins() Query.trim_joins() ! ! ! ! WhereNode.add()
23. Model Manager Queryset Query Q WhereNode Col Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q() WhereNode() Query.build_filter() Query.solve_lookup_type() Query.prepare_lookup_value() Query.setup_joins() Query.trim_joins() Col() ! ! ! WhereNode.add()
24. Model Manager Queryset Query Q WhereNode Col Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q() WhereNode() Query.build_filter() Query.solve_lookup_type() Query.prepare_lookup_value() Query.setup_joins() Query.trim_joins() Col() Query.build_lookup() ! WhereNode.add() WhereNode.add()
25. Model Manager Queryset Query Q WhereNode Col Lookup Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q() WhereNode() Query.build_filter() Query.solve_lookup_type() Query.prepare_lookup_value() Query.setup_joins() Query.trim_joins() Col() Query.build_lookup() Exact() WhereNode.add() WhereNode.add()
26. Model Manager Queryset Query Q WhereNode Col Lookup Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q() WhereNode() Query.build_filter() Query.solve_lookup_type() Query.prepare_lookup_value() Query.setup_joins() Query.trim_joins() Col() Query.build_lookup() Exact() WhereNode.add() WhereNode.add()
27. Hooray! • We made a queryset • Let's check what it looks like
28. Our query • qs # Queryset • qs.query # Query • qs.query.where # WhereNode • qs.query.where.children[0] # Exact • qs.query.where.children[0].lhs # Col • qs.query.where.children[0].rhs # False
29. Our query • qs.query.tables # ['dragons__dragon'] • qs.query.alias_map # join info • qs.query.having • qs.query.order_by • qs.query.select • qs.query.low_mark / qs.query.high_mark
30. Our query >>> print(qs.query) "SELECT dragons_dragon.name, dragons_dragon.trained, dragons_dragon.trainer_id FROM dragons_dragon WHERE dragons_dragon.trained=f"
31. Hang on a minute… • We don't have a Dragon() yet • How did we get to that SQL string?
32. SQLCompiler
33. Model Manager Queryset Query Q WhereNode Lookup Col Queryset.iterator()
34. Model Manager Queryset Query Q WhereNode Lookup Col Queryset.iterator() Query.get_compiler()
35. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query)
36. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter()
37. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter() SQLCompiler.execute_sql()
38. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter() SQLCompiler.execute_sql() SQLCompiler.as_sql()
39. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter() SQLCompiler.execute_sql() SQLCompiler.as_sql() 'SELECT' SQLCompiler.get_columns() SQLCompiler.get_default_columns()
40. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter() SQLCompiler.execute_sql() SQLCompiler.as_sql() 'SELECT' SQLCompiler.get_columns() SQLCompiler.get_default_columns() 'FROM' SQLCompiler.get_from_clause()
41. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter() SQLCompiler.execute_sql() SQLCompiler.as_sql() 'SELECT' SQLCompiler.get_columns() SQLCompiler.get_default_columns() 'FROM' SQLCompiler.get_from_clause() 'WHERE' SQLCompiler.compile(query.where) WhereNode.as_sql() Exact.as_sql() Col.as_sql()
42. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter() SQLCompiler.execute_sql() SQLCompiler.as_sql() 'SELECT' SQLCompiler.get_columns() SQLCompiler.get_default_columns() 'FROM' SQLCompiler.get_from_clause() 'WHERE' SQLCompiler.compile(query.where) WhereNode.as_sql() Exact.as_sql() Col.as_sql() connection.cursor.execute()
43. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter() SQLCompiler.execute_sql() SQLCompiler.as_sql() 'SELECT' SQLCompiler.get_columns() SQLCompiler.get_default_columns() 'FROM' SQLCompiler.get_from_clause() 'WHERE' SQLCompiler.compile(query.where) WhereNode.as_sql() Exact.as_sql() Col.as_sql() connection.cursor.execute() Dragon()
44. Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter() SQLCompiler.execute_sql() SQLCompiler.as_sql() 'SELECT' SQLCompiler.get_columns() SQLCompiler.get_default_columns() 'FROM' SQLCompiler.get_from_clause() 'WHERE' SQLCompiler.compile(query.where) WhereNode.as_sql() Exact.as_sql() Col.as_sql() connection.cursor.execute() Dragon()
45. >>> print(Dragon.objects.filter(trained=False)) ... [<Dragon: Toothless>]
46. Other things Dragon.objects.filter(trainer__is_viking=True)
47. Other things Dragon.objects.filter(trainer__in=trainers)
48. Other things Dragon.objects.select_related('trainer')
49. Other things Dragon.objects.order_by('age')
50. Other things Dragon.objects.all()[:3]
51. Other things Dragon.objects.distinct()
52. Other things Dragon.objects.values()
53. Other things Dragon.objects.prefetch_related()
54. CrUD? • Use subclasses of Query and SQLCompiler • Also for aggregates and dates
55. Database agnosticism • DatabaseBackend • Comprises: • DatabaseOperations • DatabaseFeatures • DatabaseIntrospection • DatabaseValidation • DatabaseClient
56. Custom Prefetch • New in 1.7 • Customise the way prefetch_related works
57. Custom prefetch prefetch_related(Prefetch('dragon_set')) ! prefetch_related(Prefetch( 'dragon_set', queryset=Dragon.objects.filter( trained=False), to_attr='untrained_dragons') ) ! prefetch_related(Prefetch('dragon_set', to_attr='dragons'))
58. Custom lookups • New in Django 1.7 • Know how to represent their own SQL rather than relying on SQLCompiler • The Query Expression API • as_sql • as_vendorname for database agnosticism • output_type
59. Expressions • New in Django 1.8? • Came out of a better way to model aggregation and allow for more complex aggregates • Flexible enough to be usable in order_by, values and custom indexes • Share the Query Expression API
60. Questions? Marc Tamlyn @mjtamlyn