Django - F Expressions for database-level operations

in this video we're going to start looking at some more advanced features of the Django orm and we're going to specifically look at if expressions in this video These make it possible to look at model fields and your Django models and perform database updates without having to pull that value out into python code we're going to see exactly what that means in this video and in the next set of videos we're going to look at Q objects the coalesce function and databases and we're going to look at conditional expressions and sub queries so let's get started with f Expressions I have the documentation open here I'll leave a link to this below the video there is a section on built-in expressions in Django and the main thing we're going to look at and the main thing in this particular documentation is the F Expressions now let's start by just reading this paragraph an F object represents the value of a model field transformed value of a model field or annotated column and it makes it possible to refer to these field values and perform database operations using them without actually having to pull them out of the database and into python memory instead of that Django uses the F object to generate an SQL expression that describes the required operation at the database level and that's the key point this is done at the database level and it can be therefore more performant and it can cut down on the number of queries that you're issuing and the amount of data that you're pulling into memory in the Django application so let's get started just now I'm going to go back to vs code where we've been working in this series we have a run method here and this script and we're going to start with an example now of how we can use an F expression and why we would want to use that so what I'm going to do is I'm going to pull out a particular rating from the database and I'm going to use the rating.objects.filter function to get a rating of value 3 and we're going to call the DOT first function after that to get the first record in that query set and store it in the rating variable now let's say that we want to update this rating by one we want to increment the rating from three to four now one way to do this would be to see rating dot rating and use the plus equals notation in Python and that will add one to that and then we can call rating dot save just below that I'm going to uncomment the connection.query statement and we're going to run this script below and it's pythonmanage.pi runscript and the name of the script is orm script so let's execute this and we see a couple of SQL statements here the first one is pulling out the rating with the value of 3 and it's limiting that to one to get back a single rating that is converted to a Django model and the second statement is the update statement that is setting that rating to four what we can do instead is use an F expression to notify the database that we want to update a particular rating by one and by doing this we're going to avoid pulling the value out from the database into python memory so let's see an example of this now I'm going to change this line of code that we have here I'm going to delete that and instead we're going to set rating dot rating equal to an F expression now we need to import the F object from Django and that's going to be from the django.db.models module so I'm going to remove all the m reports from the previous video and we're going to import the F expression so we just need to import this here and then we can refer to it here and instantiate an F expression and as it says on vs code here we provide the name of our field on the model so in our rating model we're going to look at the rating field and we're going to take that and add one to it so this is a different type of operation than what we did before instead we're using an F expression to look at the rating field that exists in the database for this rating model and we're adding one to that and below that we can call the save function and we're going to re-execute this script now and see the effect of using the F expression so let's run the script now there's a subtle difference here in the way that this update statement is being executed if we look at the statement we are looking at the rating here now before and the last expression this was being set explicitly to 4 and that value of 4 was coming from Python and being sent to the database by Django but in this case we have a different expression where we're setting the racing for this particular rating and the database case to whatever it was before plus one so we're not referencing the value explicitly here we're referencing what exists in the database for this row and we're adding one to that and that means that we don't need to pull the value out into memory we don't need to pull it out into our code that can be done at a database level so by doing this type of expression in the database directly rather than pulling values into memory and modifying them in your application you can get some performance improvements in your Django apps now it doesn't really make a lot of difference for a single object but this will make a difference over a query set of objects if you have a lot of rows in the table and you want to update a particular value for every single Row in that query set and that table then this can make a difference to Performance so it's a handy technique to know about to use the F expression pass a field to that and then perform some kind of update to that particular column so that's one use case that's pretty simple but here's another one that's very useful as well so let's come up with an example right now if we look at the rating table table in the database you can see that the rating column here has ratings between one and five so on this system the restaurants are rated and the rating is between one and five now let's say we wanted to move to a new type of rating system between 1 and 10. so what we're effectively going to do here just to make this work is we're going to double the existing ratings in the tables so that we can move to a rating system between 1 and 10 instead of one and five so we need to get every single Row in this table and we need to double the value of the rating field now one way to do this you could pull every single row out into your Django application then iterate over them and update each Row's rating field individually and then send those updates to the database with the save method but an alternative way to do something like this where you have to update a particular column and a table and we have to do that for every single row is we can use an F expression so let's go back to vs code and we're going to issue a query here that's going to allow us to update the ratings from 1 to 5 to 1 to 10. so what I'm going to do is simply issue a single statement here it's going to be restaurant.objects.update so we've seen that update function in an earlier video this allows us to perform an update on every single model in a query set so we're going to update the rating field on that racing model and we're going to double the value for every single rating that currently exists so what we can do is use an F expression to refer to the current value in the database table and then we can multiply that by two with this notation here that just comes from python so every rating in the table has a value in that column and we're multiplying that by two for every single Row in the database so let's clear the terminal and run this script and we're going to see what query is executed you can see at the bottom an update statement here and what happens when we use the safe expression with this update is that we're updating the rating table and we're setting the rating column in that table to its current value multiplied by two so let's go back to the database browser for SQL Lite and we're going to see if that has updated this rating column so when we refresh this table you can see now that we have values between 1 and 10. actually we have values between two and ten this is not our perfect update to multiply these by two but it is demonstrating what you can do with an update and with an F expression in the database and the important thing if you look at that SQL query is that this is updated at the database level in a single query and that can be much more performant than pulling these values all out into memory and then issuing an update for each row in the table so let's set this back to the original ratings we can change the F expression here instead of multiplying the rating by two we can now divide it by two and we're going to get back the original values when we rerun this script and we go back to the database browser let's refresh the table and now we have the original values for each row now there's more that we can do with f Expressions I'm going to go back to models.pi and go down to the sale model that we have right at the bottom here currently this has an income field let's now add an expenditure field as well let's assume that each seal comes with a certain level of expenditure this doesn't necessarily make a lot of sense but we're going to roll with this example just to demonstrate some other features of these F Expressions so I'm going to change the name of this field to expenditure and that is going to be another decimal field in our model and it's going to represent some sort of expenditure that's associated with each sale that the restaurant makes so we've added a new field to our Django model so we need to run now the make migrations command and if we look at the text that's appearing at the bottom it says that it's impossible to add a non-nullable field expenditure to the sale model without specifying a default so the database has some existing rows and this column that we're adding cannot be null so we need to provide a default value for now so that's option one and I'm going to provide a default value of 0 at the moment for all existing rows we're now going to amend this script that we've been working with called orm Script and what we're going to do is add a random expenditure for each row and the table so we've set the default to zero but what we're going to do now is grab all of the sales from that table with the sale.objects.all function and then we're going to iterate over each sale in the sales table here and we're going to set the expenditure field that we've just added to a random number so let's use the random dot uniform function from Python and if you're using that make sure that you have the random module imported we're going to uniformly generate a random number between 5 and 100 for this expenditure and then after we've done that at the bottom I'm going to use the sale.objects.bulk update function so we'll call the bulk update function and what we're passing into that is the sales query set and we need to pass as a second argument a sequence of fields that we're going to update in that table so the field that we're going to update is just the expenditure field that's the only one we've changed in this for Loop above so we're going to issue the bulk update to all of the sales that we've pulled out in the query set and we're specifying that the update should apply to the expenditure column and that table so let's comment out connection dot queries and what I also need to do and I forgot to do this is run the migrate command so let's run python manage.pi migrate We performed the make migrations command but we haven't actually migrated and added that column to the database so this is the script that's going to update that column if we go back to the table at the moment for the sales if we look at the table we can see that all expenditures have that default value of 0 that we specified but when we run this script we're hoping that that will be updated to this random number so let's run the script and we're going to see in the database that this has updated and we now have a random expenditure for each row in this table now one thing to note here is that this bulk update method is more performant when you're updating multiple records than calling the sale.save function within the for Loop and that's because instead of issuing a save query to the database for every single object in that query set instead we just update the value and then we call the bulk update after the loop has completed what I want to do now is show how we can use the F expression in our filter statement in Django so what we have on the cell model is an income and an expenditure field let's say we were a data analyst and we wanted to find all of the sales whose expenditure was greater than the income so in other words all sales that made a loss in this table let's go back to the orm script.pi I'm going to remove the code that we have here and instead of seal.objects.org I'm going to use the sale.objects.filter function and we're going to look at the expenditure field in these cells and we want to get expenditures that are greater than the incomes in this table so we're going to use an F expression here with this lookup and we're going to specify the income field as the field that we're wanting to do this comparison against so in other words we're filtering the sales and we're looking for expenditures that are greater than the value and the income column for each row in the table so let's uncomment connection dot queries and what we're going to do above is just print the sales to the term terminal so that this query is actually executed and that's because remember Django will lazy load these objects from the database only when they're actually needed so we print the sales and we're going to look at the queries let's run the script at the bottom and we're going to look at the output here for the SQL now what we have here is we have the select statement but if we look at the where Clause here we're looking at the sales where the expenditure is greater than the sales income so that's the effect of using these F expressions and the filter function we can take a field on our model in this case it's the expenditure field and we can perform the filter by looking at and comparing the values in that column with the values in another column using the F expression to refer to those other columns and none of these comparisons are actually done in the python code so that can make this much more performant than pulling out all of the sales and then explicitly comparing the expenditure to the income and the code using something like if statements this is a much better way to do this in the Django orm where we use the f expression and refer to the field values in another column now one other thing we can do here with f Expressions is we can use them to perform annotations we saw in the last video how to use the annotate function in Django let's say that we now wanted to add an annotated field called profit to our cell model and the profit is going to be the income minus the expenditure so if you have an income of ten dollars and the expenditure is five dollars we have a profit of five dollars on the other hand if we have an income of five dollars and an expenditure of ten dollars the profit is going to be minus five dollars therefore it's basically a loss in that case so let's go back to the orm script and we're going to code this annotation up using F expression so let's remove the code here and we're going to use the annotate function to do this now I'm going to call my annotated field profit and that's going to be the Alias that's used in the database query under the hood but the profit is going to be as I said it's going to be the income and we're going to subtract the expenditure from that income minus the expenditure and that's going to give us the profit for each sale in the table now what I'm going to do here is comment out the connection dot queries that we have below and the sales that we're pulling out on the line above I'm going to grab the first object and that query set and what we're going to do is look at its profit that is the annotated field by using the dot profit notation here and then we're going to run the script I'm going to see the profit for that particular seal in the database and we can see that the profit here is minus 33.8 so by doing this we are able to annotate every single sale in this query set with a profit field that determines the income minus the expenditure and that calculation is being done again in the database it's been done at the database level and then we can pull that out as values in this query set so this shows how we can take the values in two different columns and then perform some sort of calculation based on those values and then annotate a model with the result of that calculation what we can also do is use Django's order by function to to order by the annotated field as we saw in the previous video so if we wanted to get the objects who had the most profit first what we can do is we can use the minus profit notation here so we're ordering by profit and descending order if we run this script again we're going to see that the value coming out is the value with the highest profit so this is the role in the database that had the most income minus expenditure the most profit in other words so we're annotating the sales with a profit based on these F expressions and then we're ordering by that annotation and we're coming to the end of this video now I'm going to show a few more examples let's see an example with the aggregate function that we also saw in a previous video we may want to look at the sales and we might want to find the number of sales in the database table whose income was greater than expenditure and vice versa the amount of sales that had an expenditure that was greater than the income so what I'm going to do is remove this code here and instead of annotate we're going to use the aggregate function and we're going to pass a couple of Expressions to aggregate in order to calculate the number of sales that had a profit and a number that had a loss so let's create an aggregation for the profit what we need to do at the top is import the count function from Django's models module and what we're going to do for the profit is we're going to count the ID column but we're going to pass a filter to that and that's because we're not just going to count all of the distinct IDs in the table but we're going to filter and we're only going to get the sales that made a profit so in order to do that what we're going to do is use a q object in Django and that encapsulates our query filter in this case what we're going to do is also import that from the django.db.models module and I'm actually going to do a video on Queue objects in the next video in this series so if you're not sure what a q object is stick around for the next video in this series but for now we're going to use it to perform this filter in this aggregation that we're using here and we're going to aggregate over the income field and we're only going to get incomes that are greater than the expenditure in the database so we're going to use an F expression to refer to the expenditure field and the table so just to summarize we're using an aggregate function and we're calculating a profit aggregation and that's the number of sales using this current aggregation function whose income was greater than the expenditure in that table and we're also going to add another aggregation here for the loss and I'm actually just going to copy this down to the line below all we need to change here is the lookup that's being used instead of greater than we'll get all of the values whose income was less than expenditure and that will then give the losses and we're going to count these up as part of this aggregation what we're going to do below is modify the print statement here and we're just going to print out the result of calling this aggregate method so let's rerun this script at the bottom and you can see the output here we have 52 records that made a profit and we have 48 sale records that made a loss in this aggregation now before we finish this video I'm going to add one small caveat to using F expressions in Django and what I'm going to do is clear out this code and bring back the code that we had at the start of this video where we pulled a rating and we incremented that rating by one and then called the save method and this incrementing is done by using an F expression and referring to the rating field and adding one to it now what I'm going to do below that is print out the rating dot rating value here and we're going to do that at the top as well just before we change the rating so let's print it out at both stages of this operation before and after this expression is applied followed by the save method so let's rerun the script at the bottom and we're going to see the output that we get back and you can see that the value initially is four and then after we've used the F expression to increment that value and save it to the table we then have a different type of object appearing below it's not the number five as you might expect but we have a statement here saying F rating plus value one and this is called a combined expression object if we look at the type of rating dot rating here I'm going to print the type to the terminal when we re-execute this script you can see it is of type combined expression so this does not have the new value of the rating that's been applied at the database level and that's because that value has not been pulled out into the memory of this Django application that's running this query so because the operation takes place at a database level the model's rating field is not refreshed when we access that rating dot rating value at the bottom now this might not be an issue if you're just performing a One-Shot update or expression using these if statements but if you need to reuse the model or you're performing further saves on the model you need to be careful when using these so if you need that value again what you can do is call rating dot refresh from database the refresh from DB function will then pull that new value out from the database into the memory of your application so let's change the type statement that we have below here and return the rating instead if we run this script again we're going to see that we have a rating of six initially and then when we perform the update using the F expression and the save method the rating is changed to 7 and the database but in our application instead of having a combined expression object what we do is we call refresh from DB and that pulls out the new value from the database for this rating instance so that's just a quick caveat on using F Expressions when you need to reuse that model further on in your application or when you're performing a folder saved to that model in the same pipeline so that's all for this video thanks a lot for watching we're going to cover Q objects in the next video if you've enjoyed this video please like And subscribe to the channel and we'll see you in the next video

Channel: BugBytes

Views: 4,088

Keywords:

Id: NDOYWw0tDgw

Length: 20min 36sec (1236 seconds)

Published: Mon Aug 21 2023