Share
Embed
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.
Marc Tamlyn
April 05, 2017
Transcript
57 slides
-
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 -
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 -
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 -
H I ! • Marc Tamlyn • Django core developer
• Technical lead at Photocrowd -
A TA L K I N T H R E
E A C T S • Querying your data • Encapsulating logic • Advanced Prefetching -
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! -
M O D E L S • Department, Employee, Customer
• NewVehicle, UsedVehicle, OwnedVehicle • No core Vehicle model • Task • Many small models for specific processes -
Q U E RY I N G Y O U
R D ATA A C T I -
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 • … -
A N I C E A P I >>> UsedVehicle.objects.filter(
... registration_plate__year=2007, ... ) -
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) -
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), ) -
R E G I S T E R I T
CharField.register_lookup( RegistrationPlateYearLookup ) -
Q U E RY ! >>> UsedVehicle.objects.filter( ... registration_plate__year=2007, ...
) [<Car: AB07CDE>, <Car: FG67HIJ>, <Car: KL07MNP>…] -
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? -
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… -
A N I C E A P I >>> UsedVehicle.objects.order_by(
... PlateYear('registration_plate').desc(), ... ) -
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') -
L E T ' S C H E AT def
PlateYear(field): return Case(…) -
E N C A P S U L AT I
N G L O G I C A C T I I -
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 • … -
A P I # Common utilities vehicle.complete_purchase() # Specific utilities
vehicle.perform_credit_check() vehicle.process_part_exchange() -
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() -
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 -
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… -
T H E C L A S S class HirePurchase(BaseTransaction):
slug = 'hire-purchase' def perform_credit_check(self): # do stuff here… -
T H E F I E L D class TransactionTypeField(CharField):
def __init__(self, **kwargs): kwargs.setdefault('max_length', 100) super().__init__(**kwargs) -
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 -
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) -
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) -
U S E I T >>> type(vehicle.transaction) HirePurchase >>> vehicle.transaction.vehicle
is vehicle True >>> vehicle.transaction = PartExchange # Could be implemented via the __set__ -
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 -
A D VA N C E D P R E
F E T C H I N G A C T I I I -
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? -
M O D E L class Task(models.Model): employee = models.ForeignKey(Employee)
vehicle = RegistrationPlateField() action = models.CharField() notes = models.TextField() timestamp = models.DateTimeField() -
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 -
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', ) -
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>] -
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 -
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>, …] -
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 -
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, ) -
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 -
T H E M O D E L class Vehicle(models.Model):
task_set = TasksDescriptor() -
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()) -
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, ) -
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 ) -
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', ... ) -
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', ) -
A L L D O N E ! OwnedVehicle.objects.filter( service_date=today,
).prefetch_related( PrefetchVehicleTasks(), ) -
S U M M A RY E P I L
O G U E -
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 -
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) -
T H A N K Y O U github.com/mjtamlyn twitter.com/mjtamlyn
photocrowd.com/mjtamlyn