Weird and Wonderful things to do with the ORM

A collection of non-standard techniques for working with the ORM that are interesting and you possibly shouldn't do.

Transcript

1. W E I R D A N D W O N D E R F U L T H I N G S T O D O W I T H T H E O R M M A R C TA M LY N
2. Y O U C A N I N T E R A C T W I T H Y O U R D ATA B A S E T H E W E I R D
3. Y O U D O N ' T H AV E T O W R I T E S Q L T H E W O N D E R F U L
4. T H A N K Y O U
5. H I ! • Marc Tamlyn • Django core developer • Technical lead at Photocrowd
6. A TA L K I N T H R E E A C T S • Querying your data • Encapsulating logic • Advanced Prefetching
7. D E F I N I N G O U R P R O J E C T • Real world motivation • Car dealership administration system • New car sales, used car sales, servicing, repairs… • LOTS of deliberate naivety!
8. M O D E L S • Department, Employee, Customer • NewVehicle, UsedVehicle, OwnedVehicle • No core Vehicle model • Task • Many small models for specific processes
9. Q U E RY I N G Y O U R D ATA A C T I
10. F I N D A U S E D C A R F R O M A C E R TA I N Y E A R • UK registration plates • \w\w17\w\w\w • \w\w66\w\w\w • \w\w16\w\w\w • …
11. A N I C E A P I >>> UsedVehicle.objects.filter( ... registration_plate__year=2007, ... )
12. D E F I N E T H E L O O K U P class RegistrationPlateYearLookup(Lookup): lookup_name = 'year' def as_sql(self, compiler, connection): lhs_sql, lhs_args = self.process_lhs( compiler, connection, ) rhs1 = str(self.rhs)[-2:] rhs2 = str(int(rhs1) + 50)
13. D E F I N E T H E L O O K U P return ( ( "(%s ~ '^\w\w%s\w\w\w$'"
 " OR %s ~ '^\w\w%s\w\w\w$')" ) % ( lhs_sql, rhs1, lhs_sql, rhs2, ), list(lhs_args) + list(lhs_args), )
14. R E G I S T E R I T CharField.register_lookup( RegistrationPlateYearLookup )
15. Q U E RY ! >>> UsedVehicle.objects.filter( ... registration_plate__year=2007, ... ) [<Car: AB07CDE>, <Car: FG67HIJ>, <Car: KL07MNP>…]
16. A D D I T I O N A L VA L I D AT I O N >>> UsedVehicle.objects.filter( ... registration_plate__year=27, ... ) Traceback (most recent call last): File "<stdin>", line 1, in <module> ValueError: Year "27" is not valid. Did you mean to query Chariot?
17. O R D E R C A R S B Y R E G I S T R AT I O N Y E A R • 17, 66, 16, 65, 15, 64, 14, …, 52, 02, 51, 01 • Z\d\d\d?\w\w\w, Y\d\d\d?\w\w\w, X\d\d\d?\w\w\w… • \w\w\w\d\d\d?Z, \w\w\w\d\d\d?Y, \w\w\w\d\d\d?X…
18. A N I C E A P I >>> UsedVehicle.objects.order_by( ... PlateYear('registration_plate').desc(), ... )
19. W E H AV E T H E T E C H N O L O G Y >>> UsedVehicle.objects.annotate( ... year=Case( When( registration_plate__regex='…', SubString(…), ), When(…), … ), ... ).order_by('year')
20. L E T ' S C H E AT def PlateYear(field): return Case(…)
21. E N C A P S U L AT I N G L O G I C A C T I I
22. S E L L I N G A U S E D C A R • Lots of different transaction modes • Cash sale • Part exchange • Hire purchase • …
23. A P I # Common utilities vehicle.complete_purchase() # Specific utilities vehicle.perform_credit_check() vehicle.process_part_exchange()
24. N I C E R A P I # Hire purchase vehicle.transaction.complete_purchase() vehicle.transaction.perform_credit_check() # Part exchange vehicle.transaction.complete_purchase() vehicle.transaction.process_part_exchange()
25. H O W T O A C H I E V E T H I S • Custom field • Stores an identifying string • Resolves to a bound class instance when loaded
26. T H E C L A S S class BaseTransaction(object): def __init__(self, vehicle): self.vehicle = vehicle def complete_transaction(self): # do stuff here…
27. T H E C L A S S class HirePurchase(BaseTransaction): slug = 'hire-purchase' def perform_credit_check(self): # do stuff here…
28. T H E F I E L D class TransactionTypeField(CharField): def __init__(self, **kwargs): kwargs.setdefault('max_length', 100) super().__init__(**kwargs)
29. S E N D VA L U E S T O T H E D ATA B A S E class TransactionTypeField(CharField): … def get_prep_value(self): if value is None: return value return value.slug
30. G E T B A C K F R O M T H E D ATA B A S E class TransactionTypeField(CharField): … def from_db_value(self, value, expression, connection, context): if value is None: return value return TransactionDescriptor(value)
31. T H E D E S C R I P T O R class TransactionDescriptor(object): def __init__(self, value): self.value = value def __get__(self, instance, cls=None): cls = get_class(self.value) return cls(instance)
32. U S E I T >>> type(vehicle.transaction) HirePurchase >>> vehicle.transaction.vehicle is vehicle True >>> vehicle.transaction = PartExchange # Could be implemented via the __set__
33. W H Y B O T H E R • Won't need it for the first 1-2 logical chunks • Encapsulates complex, different processes applicable to instances of the same model • Testable • Keeps model class size free from large numbers of methods full of elifs
34. A D VA N C E D P R E F E T C H I N G A C T I I I
35. TA S K L O G G I N G • Two main views • What has happened to all the cars in for a service today? • What have the mechanics done today?
36. M O D E L class Task(models.Model): employee = models.ForeignKey(Employee) vehicle = RegistrationPlateField() action = models.CharField() notes = models.TextField() timestamp = models.DateTimeField()
37. A P I S department.employee_set.prefetch_related( PrefetchUserTasks(today_only=True), ) OwnedVehicle.objects.filter( service_date=today, ).prefetch_related( PrefetchVehicleTasks(), )
38. T H E U S E R C A S E • A wrapper on top of the Prefetch object • Easy enough because we have a ForeignKey relationship
39. T H E U S E R C A S E def PrefetchUserTasks(Prefetch): def __init__(self, today_only=False): qs = Task.objects.all() if today_only: qs = qs.filter(…) super().__init__( 'prefetched_tasks', qs, 'task_set', )
40. A L L D O N E ! >>> peeps = dept.employee_set.prefetch_related( ... PrefetchUserTasks(today_only=True), ... ) >>> peeps[0].prefetched_tasks [<Task: Change oil>, <Task: Check tyres>]
41. T H E V E H I C L E C A S E • This one is harder! • Vehicle history may come from the time the vehicle was in different tables, and there is no foreign key • We look up by registration plate which is shared between those tables
42. S E T T I N G U P T H E R E L AT E D M A N A G E R >>> used_vehicle.task_set.order_by('-timestamp') [<Task: Clean & Valet>, <Task: Change oil>, …] >>> owned_vehicle.task_set.order_by('-timestamp') [<Task: Clean & Valet>, <Task: Change oil>, …]
43. T H E M A N A G E R class RelatedTaskManager(Manager): def __init__(self, instance): super().__init__() self.instance = instance self.model = Task
44. T H E M A N A G E R class RelatedTaskManager(Manager): def get_queryset(self): plate = self.instance.registration_plate return self.model.objects.filter( registration_plate=plate, )
45. T H E D E S C R I P T O R class TasksDescriptor( ReverseManyToOneDescriptor): related_manager_cls = RelatedTaskManager def __init__(self): """Don't call super as we don't have a rel object""" pass
46. T H E M O D E L class Vehicle(models.Model): task_set = TasksDescriptor()
47. P R O G R E S S ! used_vehicle.task_set.order_by('-timestamp')
48. A D D I N G P R E F E T C H I N G class RelatedTaskManager(Manager): def get_prefetch_queryset( self, instances, queryset): • Instances is a concrete list of Vehicle objects • Queryset is the base queryset of Tasks to start from (e.g. Task.objects.all())
49. A D D I N G P R E F E T C H I N G class RelatedTaskManager(Manager): def get_prefetch_queryset( self, instances, queryset): plates = [ instance.registration_plate for instance in instances ] queryset = queryset.filter( registration_plate__in=plates, )
50. A D D I N G P R E F E T C H I N G return ( queryset, # Queryset of Tasks # Task value matcher lambda result: result.registration_plate, # Vehicle value matcher lambda inst: inst.registration_plate, False, # Many objects not one 'task_set', # cache name )
51. N O R M A L P R E F E T C H I N G N O W W O R K S >>> UsedVehicle.objects.prefetch_related( ... 'task_set', ... )
52. C U S T O M P R E F E T C H O B J E C T def PrefetchVehicleTasks(Prefetch): def __init__(self): qs = Task.objects.select_related( 'employee', ) super().__init__( 'prefetched_tasks', qs, 'task_set', )
53. A L L D O N E ! OwnedVehicle.objects.filter( service_date=today, ).prefetch_related( PrefetchVehicleTasks(), )
54. S U M M A RY E P I L O G U E
55. B E A U T I F U L A P I S A R E P O S S I B L E • Expressions, lookups and transforms are a powerful collection of tools to create domain specific logic • Descriptors are magical • Prefetching can apply to any way you can connect the two objects together
56. T H E F U T U R E • Subquery expressions (Django 1.11+) • Functional indexes from expressions (Custom indexes Django 1.11+, functional 2.0+ hopefully) • Virtual fields resolving to an expression (TODO) • Lazily evaluated prefetching across multiple querysets (TODO)
57. T H A N K Y O U github.com/mjtamlyn twitter.com/mjtamlyn photocrowd.com/mjtamlyn