Tweet
Tweet

Slide 1

Slide 1 text

Turtles all the way down Marc Tamlyn DjangoVillage Keynote

Slide 2

Slide 2 text

The ORM

Slide 3

Slide 3 text

The ORM • Maps objects to underlying relational data store • Allows powerful expressions for interacting with that data store • How does it work?

Slide 4

Slide 4 text

The ORM

Slide 5

Slide 5 text

The ORM

Slide 6

Slide 6 text

Something simple class Dragon(models.Model): name = models.CharField(max_length=255) trained = models.BooleanField(default=False) trainer = models.ForeignKey('Trainer')

Slide 7

Slide 7 text

Dragon.objects.filter(trained=False)

Slide 8

Slide 8 text

Classes Call graph

Slide 9

Slide 9 text

Model Classes Call graph

Slide 10

Slide 10 text

Model Manager

Slide 11

Slide 11 text

Model Manager Manager.filter()

Slide 12

Slide 12 text

Model Manager Manager.filter() Manager.get_queryset()

Slide 13

Slide 13 text

Model Manager Queryset Manager.filter() Manager.get_queryset()

Slide 14

Slide 14 text

Model Manager Queryset Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude()

Slide 15

Slide 15 text

Model Manager Queryset Query Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude()

Slide 16

Slide 16 text

Model Manager Queryset Query Q Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q()

Slide 17

Slide 17 text

Model Manager Queryset Query Q Manager.filter() Manager.get_queryset() Queryset.filter() Queryset._filter_or_exclude() Q() Query.add_q() Query._add_q()

Slide 18

Slide 18 text

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() ! ! ! ! ! ! ! ! !

Slide 19

Slide 19 text

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()

Slide 20

Slide 20 text

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()

Slide 21

Slide 21 text

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()

Slide 22

Slide 22 text

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()

Slide 23

Slide 23 text

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()

Slide 24

Slide 24 text

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()

Slide 25

Slide 25 text

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()

Slide 26

Slide 26 text

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()

Slide 27

Slide 27 text

Hooray! • We made a queryset • Let's check what it looks like

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

Our query >>> print(qs.query) "SELECT dragons_dragon.name, dragons_dragon.trained, dragons_dragon.trainer_id FROM dragons_dragon WHERE dragons_dragon.trained=f"

Slide 31

Slide 31 text

Hang on a minute… • We don't have a Dragon() yet • How did we get to that SQL string?

Slide 32

Slide 32 text

SQLCompiler

Slide 33

Slide 33 text

Model Manager Queryset Query Q WhereNode Lookup Col Queryset.iterator()

Slide 34

Slide 34 text

Model Manager Queryset Query Q WhereNode Lookup Col Queryset.iterator() Query.get_compiler()

Slide 35

Slide 35 text

Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query)

Slide 36

Slide 36 text

Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter()

Slide 37

Slide 37 text

Model Manager Queryset Query Q WhereNode Lookup Col ! SQLCompiler Queryset.iterator() Query.get_compiler() SQLCompiler(query) SQLCompiler.results_iter() SQLCompiler.execute_sql()

Slide 38

Slide 38 text

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()

Slide 39

Slide 39 text

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()

Slide 40

Slide 40 text

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()

Slide 41

Slide 41 text

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()

Slide 42

Slide 42 text

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()

Slide 43

Slide 43 text

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()

Slide 44

Slide 44 text

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()

Slide 45

Slide 45 text

>>> print(Dragon.objects.filter(trained=False)) ... []

Slide 46

Slide 46 text

Other things Dragon.objects.filter(trainer__is_viking=True)

Slide 47

Slide 47 text

Other things Dragon.objects.filter(trainer__in=trainers)

Slide 48

Slide 48 text

Other things Dragon.objects.select_related('trainer')

Slide 49

Slide 49 text

Other things Dragon.objects.order_by('age')

Slide 50

Slide 50 text

Other things Dragon.objects.all()[:3]

Slide 51

Slide 51 text

Other things Dragon.objects.distinct()

Slide 52

Slide 52 text

Other things Dragon.objects.values()

Slide 53

Slide 53 text

Other things Dragon.objects.prefetch_related()

Slide 54

Slide 54 text

CrUD? • Use subclasses of Query and SQLCompiler • Also for aggregates and dates

Slide 55

Slide 55 text

Database agnosticism • DatabaseBackend • Comprises: • DatabaseOperations • DatabaseFeatures • DatabaseIntrospection • DatabaseValidation • DatabaseClient

Slide 56

Slide 56 text

Custom Prefetch • New in 1.7 • Customise the way prefetch_related works

Slide 57

Slide 57 text

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'))

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

Questions? Marc Tamlyn @mjtamlyn