Django ORM - Updating and Deleting QuerySets / ForeignKey on_delete behaviour

so we've already seen in the series how to create database records and how to query the database with Django to retrieve those records in this video we're going to look at how to update and delete existing records from the database using Django and we're also going to see For the First Time how to do lookups when we're using the filter statement in Django in order to control what is put into the where Clause of an SQL query that can control what objects are returned from these queries now these Django operations are going to translate to two different keywords in SQL and that's the update statement and the delete from statement we're going to see examples of these queries and how the Django statements translate to these queries in this video so let's get started now I've got vs code open here and we're working with these restaurant models in this series so far we're going to continue that in this video and we're going to start by just showing something that we did very briefly in the last video and that's how to update an individual record or a model instance in Django and how that will translate to the underlying database so we're pulling out a restaurant here with the restaurant.objects.first query what we're going to do in this custom script here is we're going to print out the restaurant's name and then we are going to attempt to update that and we can do that just by accessing the field and setting it equal to a new value so for the first restaurant in the database let's give it a new name and we'll call it new restaurant name and once we've done that what we can do is we can call the model.save function in this case restaurant dot save in order to actually save that new change to the database and what's going to happen is because we're not creating a record this is actually going to change the existing data for that given row that we're pulling out into a Django model so let's now execute this script and remember we're using the Django extensions run script command to do that so we're going to run this script and it's called ormscript.pi we can just refer to it as orm script and we get the original name of the restaurant which was test that's coming from the print statement on line 9. below that we're setting it to a new name and then we're calling the save function now if we go to the database browser for SQL light you can see that existing rule with the name of test for that restaurant if we refresh this you can see that that has been updated and now the name is set to new restaurant name now this is demonstrating how to update a single role in the database using Django we simply fetch the row from the database and we can use a number of queries to do that for example the restaurant.objects.first query we can also use the dot get query to get an individual row back to our program and once we've got the object back we can simply update the field values to whatever we want and then we call the save function and what that's going to do under the hood is call the SQL update statement now if we want to see what's going on here we can print out the connection dot queries list that's our list of all the queries that have been executed in the comment workflow so what we're going to do is Rerun the script and we're going to see the queries that are executed below so let me expand this a little bit and you can see the first query here is the select statement that's getting the first restaurant from the database once we get that restaurant we have a second SQL statement and this is using the up update command in order to set the name field equal to our newly specified name now you might notice in the SQL query that the update statement is actually setting all of the fields values here from the model that we're getting back on line eight so it's going to set every value even though it's only the restaurant name that we're updating what we can do is if we make this a little bit smaller when we call the save function we can pass a keyword argument to that and that's the update underscore Fields keyword argument and we can set this equal to a list in Python where we can pass in only the fields that we want to actually update in the model in our case we're only updating one field and that's the name field so what we can do is save that and then we're going to re-execute the script and when we look at the update statement this time we're setting the name field only so if you only need to update a particular field or set of fields you can use the update Fields keyword argument to the save function in order to do that now there's one last thing I want to show on how to update a single model instance if we go back to models.pi what I'm going to do is for the restaurant model I'm going to override the save function so let's write the signature of the save function and what we're going to do is we're going to show a way that we can detect whether or not a model has been added for the first time or whether it's being updated when we call the save method now I'm going to go to the Django documentation on the model instance reference page I'll link this below the video what we're going to do is we're going to actually scroll right to the bottom on the sidebar on the right we can click the other attributes and models they have this attribute called underscore State and this refers to what's called a model State object and it tracks the life cycle of a model instance and that model State object has two attributes the first one is the one we're going to look at it's called adding and that's a Boolean flag that's set to true if the model has not yet been saved to the database so what I'm going to do within our save function is we're going to check that attribute and we're going to see whether or not it's true or false under certain conditions so let's print it out we're going to print out self dot underscore State and then we're going to print out the adding property of that model state it object and after we've done that we can just call this super.save function in order to execute the model save function as normal let's go back to our ormscript.pi file and what we're doing in this example is we're fetching a restaurant from the database we are updating its name and then we're calling the save method so this is an update we expect the adding state to be set to false because we're not adding an object for the first time we're updating it so let's execute this script and we're going to see that print statement and you can see that it's printing false to the terminal and that's because we have an object in the database we're pulling it out we're updating it and then we're saving it we're not adding it for the first time now what I'm going to do is go back to the ormscript.pi file and I'm going to remove this code here at the top and I'm going to paste some code in to actually create a restaurant and then call the save function so what we're doing here is what we've seen and I think it was the second video we just instantiate a restaurant object and then we're setting all of the fields to particular values and then we can call the dot save function so this is actually going to add a new restaurant to the database for the first time and I've already got one in here called my Italian restaurant so I'm going to make that my Italian restaurant number two let's re-execute the script and this time you see that true is printed to the terminal and that's because this time we are now adding an object for the first time so why make this self.state.adding be useful well if you're adding something for the first time you may want to detect that and then perform some sort of logic for example email a user or email certain people in your application to inform them that something has happened and this is not the only way to do this you can also write what's called a signal and djangle and signals can actually detect whether an object has been created for the first time and then you can also perform that action with the signal if you're interested in videos on signals please let me know in the comments this is just an alternative way where you override the save function and you check self.state.adding so let's now move on to another example we've seen how to update a single record in the database and that's a single rule which is represented in Django with a model instance now what happens if we want want to update multiple records in the database we can actually call a query set dot update method in order to do that so let's see that now we're going to go back to ormscript.pi and I'm going to remove this code we've got in the run method and what we want to do is let's say we want to update every single row that we have in our restaurant table so if we go back to the database browser for SQL Lite you can see we have five rows in the restaurant table now let's say we want to change the date opened from whatever dates in there at the moment to the current date and we want to do that for every single Row in the table we don't want to iterate over all of them and set that value then call the save method it turns out that Django has a query set dot update method so if we go to the documentation we have this section here which I'll link below the video and you can get a query set for example using the objects.filter function and then call the dot update method on that query set and the query set dot update is then going to take whatever's passed to the update and it's going to update that particular field or that set of fields with the values that you provide for every single object in that query set let's see an example now and go back to the wirem script so what I'm going to do is pull out all of the restaurants from the database we can use the restaurant.objects.org function for that now as I said let's say we want to update the date open field and set that to the current date we're going to take that query set of restaurants and we're going to call the dot update function and we pass to that any fields that we want to update so in our case we're going to update the date opened field and we're going to set that equal to timezone dot now and the time zone is coming from this import that I've got at the top here from the django.utels package so we're calling timezone.now and we're going to update every single record in the database and set the date open field to the current date so let's now execute this script and we're going to see the SQL statement that has been executed here at the bottom we have an update statement and we have the table which is the restaurant table and we're setting the date opened to that particular date for every single record in that table so in this update statement there is no where was after the set statement we simply update every single Row in the database with this new value for the date now I'm recording this on the 24th of June so if we go back to the database browser for SQL Lite we expect to see that value and the date opened column so let's refresh this table and you can see that that has updated so the takeaway here is that query sets in Django have a DOT update method that you can call and update the values of every model in that query set with whatever you provide as an argument and we don't need to apply this to every single Row in the database so I'm going to change this statement at the top instead of using dot all I'm going to use the dot filter statement and as an argument to filter we're going to pass a database lookup in Django now we've not seen this in this series so I'm going to go to the documentation and I'll link this below the video this is a page on field lookups in Django and field lookups are how you specify the meat of an SQL where Clause they can customize what is passed to that where clause and we're going to see how to do that now so you can see for example we have a model here called entry when we call the entry.objects dot get function we are passing not only the keyword ID which refers to a field on the model but we have these two underscores after the field name and then we have the lookup after those two underscores now this uses the exact lookup and this is actually the default lookup when you don't specify one of these lookups in your query but if we scroll down you can see we have a number of other lookups for example I exact which is a case and sensitive exact match if we scroll down further we have a contains lookup so for example to get all entries where the headline contains the name Lenin we could use this one for that and you can see the SQL equivalent below that where we have the headline field in the where Clause but it's not an equality what we have is the like statement and we're checking whether the particular column contains that name linen now in future videos I'm going to do a lot more on lookups but I do want to introduce it in this video so let's go back to vs code and what we are looking for is the name field on this restaurant model we're going to get all restaurants whose name starts with a capital P so we have two here called p pizza shop we're going to find them in this particular query so what we're going to do is use the two underscores and the lookup that we're going to provide to this query is the starch with lookup and we're going to set that equal to a capital P so that's going to return to us all restaurants that begin with the letter P and we're going to print those to the terminal on line nine below that we're going to call the update function as we did before this time I'm going to change the date opened rather than just the current date time I'm going to subtract a timezone.time Delta and to the time Delta I'm going to subtract let's say 365 days and that will change the date opened only for restaurants that begin with the letter P and the last change I want to make in this script is that the query set dot update function actually returns some data so let's print that to the terminal as well and at the bottom we're going to re-execute this script so we can see the print statement on line 9 has printed out this query set here containing two restaurants both of which have a name that begins with that letter P then when we call the update function on lines 11 to 13 you can see the number two has been printed out and that actually refers to the number of Records in the table that have been updated with the statement so in our case we have two restaurants that begin with the letter P when we call the update function it's not only going to change the date opened but it's also going to return to the color the number of rows that have been updated and finally we're also printing out the connection dot queries below and let's analyze that just now I'm going to make this a little bit bigger so you can see it we have the select statement where we're getting all of the fields from the table and that's the restaurant table but let's look at the where Clause where we're checking if the restaurant's name is like this pattern that's passed in here and you can see the pattern has the capital P at the beginning and then the percentage wildcard which indicates any number of characters after that initial character and this is the pattern and the query that we want when we want to fetch all of the restaurants that begin with the letter P so that where Clause is showing how this starts with lookup is customizing the underlying SQL to actually fetch the right records from the table and you can see that lookup also appears in the up update statement where we're setting the date opened to the given date where the restaurant's name is like that pattern and in SQL if you don't want to update all rows in the table when you update the table and you set the values you will specify a where close after the set and that controls which rows are actually updated so I hope that makes sense before we move on let's go back to the documentation for updating multiple objects and I'm going to scroll down to this paragraph here and it says be aware that the update method is converted directly to an SQL statement as we've just seen in the terminal and this is a bulk operation for direct updates it does not run the save method on your models and it doesn't emit the pre-saved and post save signals so it's important to know that if you have custom logic in your model save method like we added a second ago with the adding state that custom logic will not be called when you call the query set dot update function so when you call the update function on a query set that might update a set of models in your application you need to be aware that any logic you've gotten a signal or in your model save method well not by default be executed when you call the query set dot update method and as it says here if you need to save every item in a query set and you need to make sure that the save method is called on each instance you don't need anything special to handle that you can just look over all of the models and call Save now I would say if you don't need to execute custom logic then it's probably more efficient to use the query set dot update method because that creates one SQL statement to update all the relevant rows in the table whereas looping over the data and calling save one at a time is going to create much more SQL statements and that's perhaps going to affect performance if it's done too often with a lot of users so just be wary of these different issues that can occur in your Django application and the very last thing to show before we move on to deleting records if we go back to vs code when we call the update function on a query set we can actually pass multiple keyword arguments to update multiple fields for each row that's in that query set so for example as well as date opens we might want to set the restaurant's website so I'm going to set it to this dummy URL and if we now re-execute this script and go back to the database browser for SQL Lite when we refresh this you can see that for the given restaurants that begin with the letter P we now have that website entered into the website column as well as the date opened being set to a date in the previous year so you can pass multiple arguments to the dot update function let's now move on to show how to delete models and delete query sets from the database so I'm going to remove this code here and at the top of the Run function I'm going to fetch the first restaurant from the database table we're going to store that in a variable called restaurant and what I'm going to do below that is just print out the restaurant's primary key that's the ID of the restaurant in the database table and below that I'm going to fetch all of the ratings that the restaurant has so we're going to look at the restaurant we're going to get its related ratings and we're going to get all of those with the dot o function and if we run this script below here you can see the output we have number two for the primary key of the restaurant and we have a query set below that that has 3 ratings for this restaurant now what I want to do is delete this restaurant from the database table so we're going to delete the restaurant that has the primary key of two and that's the restaurant that we've called new restaurant name so that's going to be removed from this database table when we call the dot delete function on the model but the reason that I'm pulling out the ratings as well is because of something I'm going to show now in the models.pi file so if we look at the rating model we have this foreign key to the restaurant and that's specified with a models.foreign key field and this is the keyword argument I want to highlight here it's the on delete keyword argument to the foreign key at the moment we've set this to models.caskade and this is an important keyword argument to know when you're using foreign Keys what this means is that when we delete the parents model which in our case is the restaurant when we set the foreign keys on delete argument to models.caskade what this is going to do when we delete the parent model is it's going to Cascade that delete down to any child models so basically this is is going to remove any ratings for the restaurant when that parent restaurant is deleted from the database so at the moment as you can see below we have three ratings that are being printed to the terminal if we go to a database browser for sqlite and go to the core rating table you can see in the table here that we have a restaurant ID and we have three rows that refer to the restaurant with id2 now when we have a Django model that represents a row in the database let's go back to the orm script we have that here on line eight what I'm going to do below here is called the restaurant models delete method so all models in Django they have this dot delete method that you can use when you want to actually remove that role from the database and get rid of the data and the delete method will return the number of objects that have been deleted from the database and it will also return a dictionary with the object deletion numbers for each type of object that has been deleted so I think the best way to show this is to print it out to the terminal so let's print what's returned when we call restaurant.delete and I'm going to remove these top two we already know that the foreign Keys too and we know that the restaurant has three ratings so let's now execute this script and we're going to see the output on the terminal I'm going to make this a little bit bigger here when we call delete we get back the number of records that have been deleted and you can see the number is set to four and the second element of this Tuple is a dictionary that contains the number of deletions for each object and you can see that for the rating table we've removed three ratings from that table and for the restaurant table we've removed that single model that represented the restaurant we pulled out from the database so when we call delete on that restaurant it's deleted it from the database but it's also deleted the associated ratings it's deleted three of those and that's because of this models.cascade argument that we're providing to the foreign key so let's now go back to database browser for SQL Lite we have the rating table here we expect now to see that all ratings with an ID for the restaurant of two have been deleted from this table so let's refresh the table and you can see that we now don't have those reference these anymore we only now have ratings with the restaurant ID set to three so that particular model has been deleted and that deletion has cascaded down and deleted the associated ratings and if we go back to the core restaurant table you can see we now have four restaurants and the restaurant that we had with the ID of two is no longer in the database now if we go back to vs code and rerun this script here you can see that we get back the number three this time and it's deleted One restaurant again and two ratings what I want to do this time is analyze the SQL statements that are being executed here now the first one is simply fetching the first restaurant from the database table once we have that we have this delete from SQL statement and the delete from takes the table that we want to delete records from and then it takes a where Clause that specifies which rows in the table are to be deleted and in the where Clause here for the rating table you can see that we're deleting all records where the ratings of restaurant ID is set to the number three and at the bottom here we have another delete from statement where we are deleting from the restaurant table where that restaurant ID is equal to three so it's two delete statements the first one is executed on the child table and that deletes any children that have a reference to the parent that's about to be deleted and that's because of the models.caskade argument to the foreign key and then finally of course we delete the actual object from the restaurant table that's the parent object now we have one other SQL statement being executed here and that's an update statement that's on the sale table and that's setting the restaurant ID equal to null when the restaurant ID and the sale table that foreign key is equal to three so let's minimize this and we're going to look at the sale model here and we have this restaurant foreign key but the on delete argument to that is set to models.set now this is an alternative to cascading the delete from your parent table down to the child tables sometimes you do not want to do that you want to preserve the data that's in the child tables and that might make sense in a sale table where you have financial data that you don't want to just throw away restaurant is deleted from the database so instead of that you set the foreign key reference to null when the parent is deleted and that's why in this SQL statement that you see here we are updating the sale table and we're setting that foreign key equal to null when that parent restaurant has been deleted and we can do this only if we set the foreign key to a nullable field as well so make sure that if you need to preserve that data you can set the models dot set null flag on the on delete parameter and you can also specify the field to be nullable so we're highlighting what the on delete parameter to foreign key can do here what I'm now going to do is move on and we're going to show how query sets in Django like they have an update method they also have a delete method where you can delete every object in the query set so what I'm going to do is clear the terminal and let's make this a little bit smaller and go back to our script here and let's remove this code and instead we're going to use the restaurant model we're going to get all objects from the database and then when we have all objects that's a query set of objects we can then call the DOT delete function on that and it's going to remove every single object from the query set from the database table so let's save this file and I'm going to go back to the database browser for SQL Lite when we refresh this you can see at the moment we have three restaurants in the table but when we call the restaurant.objects.all.delete function that's going to fit all of the restaurants that we have all three of them and then for each one of those it's going to delete them from the database in one single SQL query so let's now run the script and we can see the output of that script we have the SQL statement here where we're deleting from the rating table where the restaurant ID is in any of those numbers four five and six and those numbers have been gathered from the first SQL statement which pulls all other restaurants from the database Django then takes the primary keys of each restaurant that's been pulled out and passes them in to the where Clause of this delete from statement to delete the records from the associated database tables in this case it's the rating table because of that models.k Cascade argument but we also have at the bottom the delete from called restaurant table where the ID is in that set of numbers now if we go back to the database browser for SQL 8 and refresh you can see that our restaurant table no longer has any rows they have all been deleted and that's because we've now called the query set dot delete function and if we go to the rating table that we had you can see we also no longer have any ratings in the table and that's because of the models.cascade argument on the other hand if we go to the sale table you can see that the sales have been preserved in this table for each restaurant but the ID of the restaurant associated with the sale is now set to null and this preserves the referential Integrity of this data we cannot refer to a foreign key that no longer exists in the parent table so we either have to delete the record from the database or another strategy is to set the foreign key equal to null that indicates that the parent no longer exists but we are keeping the data around because it may be important for the business or for other reasons now let's go to the Django documentation on the foreign key on delete argument just before we finish this video we're going to look at the different values that this accepts now we've already seen Cascade we also have a protect value here that we can use and this prevents the deletion of the referenced objects so sometimes you want to say this object is a foreign key for a child therefore we don't want to allow it to be deleted from the database you can use the models.protect value for that we also have a restrict value here and if we scroll down we have the set null and set default values as well we've seen set null and we've used that in the sale table set the default will set the new value of the column to whatever the default is for that field and below that we have this set value as well and if we look below you can set that equal to a python function which performs some sort of logic that will then set the value of the following key field when that parent is deleted the final value here is doing nothing and that specifies that if the paint is the least it we don't want to do anything to the childhood table so we're going to allow it to refer to a value that no longer exists in the paint table that's probably not the best idea I wouldn't recommend that one but it depends on your use case so that's all for this video we've learned how to update and delete records in Django both model instances and query sets we've also looked at model lookups and how they can be used to customize aware clause in Django and we've also looked at the on delete argument to foreign keys and how that affects deleting child instances when the delete method is called on a parent in the next video we're going to look at creating a default data set for the rest of this tutorial series and once we have that data set created we're going to dive further into querying databases with Django we're going to look further at the filter and exclude methods and how you can use different lookups in Django to customize what's returned from those queries we're also going to look at ordering records by a particular field and we're going to look at some helper functions such as the earliest and latest function in Django and after that next video we're going to start diving into more complex topics in Django so if you have any further suggestions for this series as well please let me know in the comments thank you for watching if you've enjoyed the video please like And subscribe to the channel and we'll see you in the next video

Channel: BugBytes

Views: 4,840

Keywords:

Id: cN4zjbcM2kk

Length: 26min 16sec (1576 seconds)

Published: Wed Jun 28 2023