UPDATE and DELETE - SQLite3 with Python 3 part 5

Key Takeaways

what is going on guys and girls welcome to part five of our SQ light miniseries as well as another Python 3 Basics tutorial uh in this tutorial we're going to be talking about the point and no return so we're going to be talking about deletes and updates to your SQ light and really SQL in general databases so uh both changing values and updating Val or changing is update so the the query to change a value is update um but updating and deleting values in SQL is a permanent job when you've done it there are no undos so it's really important that you don't screw up and do something you don't want to do because there is no control Z there is no edit undo uh what you've done is permanent so really keep that in mind so now what we're going to go ahead and do luckily this is a simple database we're not too worried about screwing anything up so don't worry about that but keep in mind there are no undos so especially like with web servers or just anything in general where you have a database it's just a good idea to always do automatic backups it's just just do it you will never regret it I have done automatic backups I usually pay hosts to like I use linode and digital lotion all that I pay the extra money to just have them do the automated backups because it's just worth it I've never regretted it just make sure you always backing things up when you have databases anyway really anytime but with databas bases especially um so anyways Define Dell and update here we go so first of all um let's select the data that we're working with so we're going to say c. execute and then we're just going to do a simple select all from stuff to plot um and then we haven't really covered on liners um probably after this is we're going to talk about oneliner four loops but here's a a quick introduction um so if you just want to like do on line for Loops you can do stuff like this you can say well for example you would do like never mind I'm not going to explain we'll wait till we get there but basically what we're going to do is we're going to print Row for Row for Row in c. fetchall okay so it's just going to print out every Row for every Row in c. fetchall a nice simple oneline for Loop um and then we're going to Dell an update we're not going to graph data anymore so this just selects this we're not actually deleting or updating anything so here's our our information okay we we apparently have a lot of twos a lot of eights probably eight is what we have a lot of so we'll use eight so um so that's the information that we currently have now what we can do is we can start changing values so first what we could do is we could say c. execute and what we can say is we can update and then you update a table so you update stuff to plot and then you update and when you update you set something we're going to say set value uh equal to you could make this a variable so you could do something like this set value equal to question mark if you wanted that to be a variable but for now we'll just hardcode it 99 where value equals again you could make this a variable if you wanted or and that's SQ light VAR iable you can make it a variable in uh my SQL with that where value equals three no8 okay so where the value is equal to eight we're going to change it to a 99 now that changes something right so you've opened the file you've changed some data what do you need to do you need to save it so we need to run con. commit that will save it and then when we're all done committing we'll just copy that to line up there paste and see our changes in action so save and run that and sure enough here we are we got a bunch of these 99s now of course we didn't really space anything out but somewhere between here and here we have our new data so now we have a bunch of 99s okay so we'll close that and we don't let's see so so okay so that was how you can update some stuff so I'm going to get rid of um what we'll do is we'll just comment this out so alt three again if you're an idol so now our database currently that change is permanent right so 99 is what eights are replaced with and now let's say we want to get rid of every row that has a 99 how do we do that well you can do something like this you could say [Music] um c. execute execute and what we're going to do is delete from stuff to plot where value equals 99 now in my SQL just so you know you can limit queries like you could say I want to limit this query to the maximum that you delete is 50 unfortunately an SQ light that doesn't exist so you're just screwed if you enter the wrong thing because for example if you do this delete from stuff to plot you cleared your database or your table rather so keep that in mind now delete from stuff to plot where value equals 99 con connection commit and let's print out what we have now and in fact let's go ahead and print 20 time actually let's do 50 times the pound sign that just prints out 50 pound signs so we can see the separation all right so the new new separation happens here so as you can see this one contains a bunch of 99 values after that separation we have no 99s they have all been eradicated again there is no undo unfortunately now uh one thing that you can do is first of all in my SQL you can limit delete so for example let's say you've got database with 1 million rows and you're about to run um an update or a delete um and you expect that you will only be deleting or updating a th rows in MySQL you would just you know you would limit you know 1,50 right and if you affect exactly 1,50 rows you know that something went maybe ay um but you can limit it just in case but generally you limit after you've done something else and that would be uh a simple select so for example before you run a delete query probably what you need to do is instead um you could do something like this like uh let's look at our database really quick so we can see okay we've got a bunch of twos so let's let's say we're going to uh run an update or a delete on the twos and we have in our mind how many rows that should affect I don't know maybe 10 um well we entered it really should affect maybe like 3 to five but we'll see anyway I forget how many times I ran that but anyways uh that aside uh what you would do first is instead of just running a delete like let's say the query that you want to write is basically um let's get rid of that CU that's not valid uh so you're going to run the following query you're going to delete from stuff to plot where value equals two so before you go deleting why not um replace delete from and and actually why don't you just replace delete Del with select all okay so you select all so you can see what you're about to delete and you can either print it out or another thing that you can do is simply print oops print lent print Len c. fetchall and you know you can both print out everything um and apparently c. fetchall is empty why would that be empty lens c. fetch dra why would that be empty someone explain that one to me [Music] um data equals row let's see data equals Row for Row in c. fetch all so that just makes a list out of that print one data I would expected that to work why is what data what am I doing wrong here maybe maybe what's happening is every time you Fe yeah probably when you fetch all that's that uh and there's no more data there so you might actually have to run the SQL statement twice so you would say this and then print Len of c. fetchall hope this works there we go okay so seven so keep that in mind I guess I I that's news to me I didn't know you could reference a fetchall twice but I usually what I'll do is I'll say like data equals c. fetchall that way data stays populated and c. fetchall can do other things and populate other things um so that would that would save you from having that issue that I just had uh but interesting I did not know that as soon as you referenced fetch all once that emptied out the cursor always learning so anyways um that's one example of how you could see first of all you can see where the columns are equal to two so you could see exactly what you're about to delete or you could get the amount of data that you're about to delete so with with SQ light that's as good as it gets but with um if you were using MySQL you would see okay we have seven rows so you would delete from stuff to plot where value equals 2 and then in MySQL you would limit that query to seven okay and then that would keep you as safe as possible all right so anyways that's SQ light SQ light is again just a really light version of SQL that pretty much covers the major things you would do with SQ light but of course there are plenty of other things you could you could conceivably do with SQ light uh but if you need to have more options and more configurations and you want a little more efficiency you would probably jump into something more like my SQL um but sqlite is more than capable of running even large databases the real problem with SQ light is because it's it's not a server it is a single file uh the main kind of choke point for SQ light is when you have multiple simultaneous input output operations happening at any one time so if you're running a forum and you've got like you know 50 users on it any one time and they are doing things simultaneously they may have to wait a little bit longer to get that information that they query so they click on like say a thread um at the same time someone else clicks on a different thread uh it's going to be a little slower to return um the values to that user with something like SQ light but again even in that case with like let's say you only have 50 users online SQ light is more than enough it's really SQ light doesn't scale to like millions of users okay so anyways um that's that that's SQ light if you guys have any questions comments whatever up to this point feel free to leave them below otherwise as always thanks for watching thanks for all the support subscriptions and it's till next time