Django ORM - Insert data into multiple tables & using atomic operations

hello my name is Xander welcome to the orm Mastery  course level one for beginners this tutorial   is part of the YouTube Edition playlist Django  database RM Mastery you can find a link to this   playlist in the video description where you'll  find all the tutorials Associated to this course   in this tutorial we explore the concept of  inserting data into multiple tables utilizing   Atomic operations if you would like to follow  along step by step you can download our base   Django project code base one there is a link in  the video description to that code now if you   are not familiar with Django and how to start a  new Django project there is also a video guide   on how to download and start the project again  there is a link in the video description to that   if you like this course and would like to  learn more then do consider our course on   udemy Django database or a Mastery level one the  actual thumbnail might change over time but just   look for created by very Academy there is a  link in the video description to the course   that is always going to give you the best  price for the course so by default Django   runs in Auto commit mode which essentially means  that every query is immediately committed to the   database so this example here imagine we send  an update query to a table in Django what's   going to happen is that this SQL statement  is going to be evaluated executed and we're   going to say it's committed to the database so  the database has changed into its final state   let's now imagine we send a second query to  our table um that then also then goes ahead   and is executed by the date by the  database and then is also committed   individually so these can be considered separate  transactions so we have two transactions here   there are many occasions when our application  performs an operation and it requires to run   multiple queries let's just imagine we're building  a bank application here we're recording the user   the amount of money that they have in the  bank so let's imagine user a and user B   they want to pay each other some money so user  a sends user B 10 10 pounds okay so we need to   send a statement an SQL statement to our database  to update user A's account so we update the total   amount of money that they have so this is one  transaction so by the default behavior of Django   it would send and execute this query and commit to  the database an extreme example but let's imagine   that the database now fails before the second  operation occurs here we have a obvious problem   we've now updated user A's bank account but user B  bank account hasn't been updated so user B hasn't   actually received the money so we need to ensure  that our transactions here follow four basic   rules of working with databases or it needs to  follow four basic properties and that's acid so   atomicity consistency isolation and durability  so here we're Focus focusing on atomicity so to comply with atomicity we need to ensure  that the entire operation here is successful   before we then commit it to the database so we're  now going to configure Django to prevent it from   Auto committing our operation here we're now  going to create an atomic operation or Atomic   transaction whereby we first of all check that  we can update user A's bank account and then we   go ahead and check to make sure we can update  user B's count account and then we're when both   operations have been successful we can then go  and commit now when we commit we are committing   both of these changes now both of these changes  will be successful and thereby we can ensure the   data Integrity of our database so if there were  any errors at all that occurred through any of   these SQL operations it would mean that we can  roll back we never at any point make any changes   to the database if any of these SQL statements  were to cause any errors so it just happens that   we have a very similar situation where we can  apply this approach to our set of tables here   now if you remember we have this sub product here  in this product inventory table now every time we   add a subproduct inside of this product inventory  table here we would also need to ensure that we   create a new object here in the stock table which  is going to correspond or be related to that new   subproduct because our subproduct we need to  record how much stock we have of that particular   sub product so we would need to create an entry in  stock and update the inventory in the stock table   so we're now going to use the principles  of atomicity to create a transaction which   includes two queries should one of these  queries fail then we will roll back at   no point in time where we commit to  the database should both queries be   successful we would then commit to the  database and the changes will be made so I've gone ahead and created the initial data  because we're using foreign Keys here and we're   using the product inventory table we're going  to need to populate the product brand and the   product type table now we've already utilized  these tables in the previous tutorial so we're   going to need to make sure that we delete  any existing data so if you are following   along you will find the base code at the  start of this section and that will lead   you to this point so we can then go ahead  and access the shell we're working inside   the shell and then we go ahead and we access  all the tables that we're going to need now   this code is in the video tutorial here you  can download it have a look at that if you   want to follow along so first of all I'm just  going to delete everything so I'll use this and   delete and you can see that there was an error  here for some reason I tried to delete probably   um because of a foreign key and that's not a  problem um okay so that's that's absolutely fine   right so now we've deleted everything just  to make sure that everything was deleted if   you are following on from a previous tutorial  let's just go ahead now and populate so we've   got the product table the brand table  we need to do this in order of course so   um let's make sure we don't add anything to the  product inventory table so just add to the product   table so we'll just add an entry there you can  see that we've got an error in the product table   for some reason so let's just delete yep okay  so it didn't work the first time so we deleted   and now we've added a new item to the product  table okay so now we can do the brand table so we've added a new item there to the brand  table and then let's just go ahead and add to the   product type table and you can see that there's  a problem I don't know what happened earlier   so we just go ahead and delete so I'm just running  these commands here and let me go ahead and   run that there we go so we've added now to  the product brand and the product type table   to better emphasize uh atomicity in play  Let's go ahead and go over to the inventory   application here go into the views  and we're going to create a view   okay so I've gone ahead and created a view I've  got all the tools in that we're going to use   again this code is available you can download  so in our view here we're going to just return   a HTTP response if you didn't delete this from  a previous tutorial you may already have this so   we're going to bring we're going to bring in all  of our models we're probably not going to need   them all we're only going to need the product  inventory in stock and actual fact so that's   not a problem and then let's go ahead and bring  in the Integrity Arrow which we're going to need   shortly and then the transaction from Django DB so  those are all the tools we're going to use so here   we have a function I'll just uh wrap that so you  can see everything so you can see we've created a   simple function here that takes in the request  as per normal and returns a HTTP response High   so I made this on purpose so that we can explore  the tools but if you remember if we want to add a   new product we need to line up all of these IDs  now because we've been utilizing the database   throughout this course then the IDS would have  changed because they're Auto incrementing right   so let's just double check this so product type  so let's go into the product uh well let's just   do the brown Goods there so the brand so we're  using brand id1 so we just need to make sure   the brand ID is one and then we're going to be  needing the product so product is 11 ID 11 so   we're going to need to make sure that is product  ID 11 and then finally we are using the product   uh this is the product type yeah the product  type table so that's ID3 so we just need to make   sure that the product type is three okay so that  should now be aligned to all the other tables the   foreign keys there so that should be inserted now  this definitely isn't aligned okay so this isn't   going to work and that's the whole purpose here so  here we're just emphasizing the fact that for our   for our software to work correctly how we want it  to we need to make sure that when we insert a new   product a subproduct here then we also build  an entry into the stock table now this won't   work because this ID is incorrect but we can  run this anyway so I'm going to open up a new   um shell here to actually run to run  Django so we've got a separate tab here for   Django so that's now running so we can use  Curl or go into the uh go into the browser   so let's go into the browser let's go ahead and  go into Django here so we have a path Slash new and there we go so here we've received an  Integrity error foreign key problem so it   looks like we've got a foreign key  error so we were expecting that but   here's my point because Django is running  Auto commit mode it's gone ahead and it's well it's gone ahead and so here's the point  because Django is running in Auto commit mode   it means that it's run this query and it's  committed it to the database now because the   product inventory ID is incorrect it doesn't  match across to this product this has failed   and it's called for it's um caused an Integrity  error it's a foreign key constraint error so in   actual fact our transaction here we've created  two transactions but in actual fact our operation   is now failed because we've added something to  the database which is now incorrect because we   don't want to add that unless there is a stock  item so potentially we're causing issues in our   application so we want to make it so that we build  a transaction which includes both of these queries   should one of them fail then everything should  fail of course what I mean by that is if any   one of these fails then we should roll back and  not commit any of these queries to the database   so let's just go into the product uh the product  inventory table here and show the table you can   see that the first query actually committed  to the database there and obviously that's   not what we want we want to make sure that if  this is committed to the database stock is two so first up then let's go into the first tab  here and we're just going to delete everything   from the inventory table start fresh again okay  so now we're going to build our Atomic block here   right so Django provides different methods to set  this up we're going to just use a try accept and   we're going to bring in transaction dot Atomic  so let's do that so let's go in and do a try   and then we'll do a try with so we go  ahead try with transaction dot atomic okay so we're now creating an atomic uh block  here and we're saying that both of these   query like I'm just repeating myself um  but both of these queries now need to be   successful for us to commit to the database  right so let's go ahead and do the accept   and then we're bringing the Integrity error and  then we capture that and then just return for   example our HTTP response something simple there  we go okay so should any of these queries fail   then we should now see the error so I've already  deleted from a table so there is nothing inside of   the product inventory table there so let's just  run this again so although we don't need to I'm   just going to run the server fresh again and let's  go back to the browser and let's uh run this again so this time we receive the Integrity error  HTTP response error so that is indicating it   is working or capturing the error and you  can see this time if I go into the product   inventory table we haven't added the item or  we haven't inserted the item into the database   so you can already guess why that is because  we're running Atomic transactions here so   because this failed it means that this wasn't  inserted because everything needs to pass in   order for us to commit any of these queries or  execute any of these queries and commit to the   database so let's now change this right  so this is where it gets tricky because   um I'm I don't know what product ID this is going  to be so and let's just uh let's just highlight   this out and we'll run this again apologies  um so we'll run this again and it says hi   so successful we're going to the table okay so  this is id5 okay so we've just added something   against the product inventory table that has an  ID of five so the next time we add something we   know it's going to be id6 so let's just go ahead  and uncomment that out so that's going to be id6   of course this is where it doesn't work I'm just  showing you this as an example of course so let's   add something here so we can add a new item and  we can that's okay um okay so we're just going to   add this a new item now and we know that's  going to be id6 so let's give this a go um very fresh and there we go so I've refreshed  twice the first time it said hi the second   time it said error so let's go into the product  table here looks like we've got the second item   that's all good brand ID blah blah so that  was id6 now let's go into our stock table   sorry if you can see that and there we go we  now have our stock item related to product id6 so just to recap we need to remember that Django  by default and this is something we can actually   change in the settings if we wanted to so have a  look at the documentation but by default Django   is running Auto commit mode so each query is  immediately committed to the database so we've   seen how to override that by creating a atomic  transaction block whereby we create multiple   queries within a transaction block and run  them against the database should one of them   fail then everything is rolled back and none  of the queries are committed to the database

Channel: Very Academy

Views: 3,052

Keywords: django orm, django atomic, django insert

Id: WFPgXxO0N4U

Length: 17min 29sec (1049 seconds)

Published: Fri Oct 07 2022