How to Use SQL with Python! (CREATE, INSERT, READ/SELECT, UPDATE and DELETE)

what's up you guys welcome back to the channel in today's video I'm gonna cover how to do SQL work using python we are going to cover the most common things you would need to do in SQL so creating databases and tables uh inserting new data into SQL reading data from SQL updating existing data sets deleting existing data from data sets and then we're going to take a look at a pretty cool combination of using SQL and python to go through a data set and remove duplicates [Music] so before we dive into this too much all of the code that I'll be using as an example in this tutorial and covering line by line will be available at the GitHub Link in the description of this video below please consider leaving a like on the video subscribing to the channel it really helps me grow and if you love the content on the channel there's a patreon link down there as well consider checking that out okay so now let's dive into the content and take a look at how to create a table and a database connection to SQL in Python all right so let's start with creating a table and a database in python for SQL okay and so everything I'm going to do today is using the SQL Lite 3 python module so you'll need import SQL Lite 3 at the top and you'll probably hear me say SQL and SQL throughout this tutorial SQL stands for structured query language and that's commonly abbreviated SQL so either are valid but you need to start with import SQL lite3 if you don't have it installed it's usually one of the modules that comes installed with python today but you can run pip and install that and the first thing you want to do is establish a connection so put that in a variable I always call it connection but you can call it whatever you want put that in a variable and just do sqlite3 dot connect okay and then here you need the name of what you want your database to be called if it exists it'll connect to it already if it doesn't exist it'll actually make this database so I'm going to call it lemastertech.db this dot DB just means database and you can see in my uh folder on the left or hopefully you can see I know it's a little small there is no Lemaster tech.db but when I boot this up and run it there's it's going to create a Lemaster tech.db okay so the next thing you want to do is create a cursor and a cursor is the thing that actually lets you send and receive commands back and forth so for cursor we're going to do cursor equals connection our variable from before Dot and then cursor okay that's a built-in part of the sqlite package and this cursor is going to be our communication uh tool back and forth all right so now let's say that we want to make a table that's going to be a list of data so each entry will have I'm going to use since it's Lemaster Tech Lemaster tech.db I'm going to use um videos that I've done so I'm gonna have the name of the video how many views it had and then the date this will let you see three different types of uh data okay and in SQL the date format is year year two digit month two-digit day okay so each each entry is going to have that now let's uh create the SQL command and to do this we make a SQL command inside of triple quotes Okay so this is the way to do a long block of text in Python and it must be written in SQL format okay so you can't use Python format to write SQL commands it has to be in SQL format luckily SQL format is not that hard it's very logical and you can find all these common examples online but let's take a look at how to create a table okay so I make my triple quotes and literally the instruction for creating a table is create table all caps like that okay and then just the name of the table and I want to be called sample because it's my sample table I'm just showing you guys a sample table and then inside of parentheses put your variables in there so the column names that I want I'm going to have vid name and then you need to Define what variable type it'll be and again you can find the variable types available in SQL online but essentially a varchar is their version of a string it's like variable character okay so it could be alphanumerical a punctuation anything so I'm gonna and then the 30 means it could have up to 30 characters and then I'm going to have view count as another column and that will be an integer and again this isn't going to be a real in-depth tutorial on SQL itself um so if any of the SQL pieces here confuse you and just following along isn't enough for you um let me know in the comments below and I can do more SQL content down the line um and obviously let me know in the comments if you have any questions about anything you see here okay and then I'm gonna say when it was published and that variable will be a date format okay so these are the three columns that I'm going to put in my table obviously put whatever columns you want in your table inside these parentheses and then just put the close parentheses and then the three uh quotation marks to end your SQL command and now we have this variable SQL command that we're going to send to the cursor using cursor.execute and then put in here SQL command and one thing I want to note you could copy this whole thing and put it inside of these parentheses I do not find that that makes the code easier to read actually think it makes the code harder to read which is why I like to break it out and then it's super clear where all of your command stuff is but it would be valid to copy this whole thing inside here and you could even use single quotes if you did that all right and then something you want to do at the end every time you're done working with SQL is do connection Dot close Okay so this is really important to make sure that every change that you actually make is uh finished and then you terminate the process you don't want an open connection to SQL if you're done using it all right so uh this is it for creating a database and creating a table I'm going to run this and what the only thing we're going to see right now is if everything worked you're not going to get an error code that just means that it worked properly but what you might have seen is over on the left in my file folder in my structure Lemaster tech.db was created now if you were to click it and try to view it in like plain text it's gonna look kinda janky because uh pycharm and most Ides other than like Microsoft Visual Studio don't have a good built-in tool for reading this data so the next portion is going to be how do we insert data and then how do we read data because right now we've created a database but there's no data in it so next step is we're going to look at inserting data all right so for inserting data the only thing I'm going to start from is a list with my actual data so remember I made a table that is going to receive the name of the video The View counts for the video and then the date it was published and I don't have it in any particular order it's not in date or view count or anything like that arranged it's just a list of lists that I called videos so let's take a look at how to get this list stored inside my sqlite database okay so the first couple lines are going to be exactly the same you always need to have SQL Lite 3 in your project if you're doing all of this in the same dot Pi file you probably already have it there you need that connection every time equals sqlite3 dot connect and so for future steps I'm not going to do these every time I'm just going to copy them from the previous step I just want to make it clear these things always need to be in there so connection and cursor are going to be the same for basically every single project every single step that we do in this tutorial okay so you need a connection and you need your cursor now we have our list of videos and now let's take a look at how to move these videos into SQL and it's actually really simple okay so we are going to go through the entire list and just say for I or item whatever you want to call it in range length of videos okay so uh for every entry that I have in my videos list I want to make these intermediate variables name equals to videos at I so my my list entry at whatever index I'm doing and then the first variable is going to be which is actually zero because in Python list you start counting from zero hopefully you know that if you're doing stuff like messing with SQL hopefully you know enough python to know you start counting from zero but so then views is the second element in there which gives it index value one and then the date is the third value which has index value two okay so now all we have to do to get that as an entry into our SQL database and table every time is cursor.execute and then in single quotes now because we're doing it all in this line we're going to say insert into sample that's the table and then here are the values so all caps say the values now the thing is we can't just say name views date because this is just a string as far as SQL knows so the the syntax is very specific here using SQL lite3 you have to put question marks there because that is how it knows to look for a secondary variable passed in that are actually the parameters you're sending so the parameters inside of these parentheses making it a tuple making it a sort of secondary list being passed into this cursor.execute this is your SQL command here are the variables that are going to be replacing the question marks in the order you put them in so name views date and you can experiment with messing these uh messing this order up you're going to get data in the wrong order but so then very important at the end of changing anything in your SQL uh data so we didn't have to do do this next step when we created the table in the database it happens as soon as you run that code but if you change the data inside your database you need to commit data or no changes changes will be saved okay and this is really really simple it's just connection dot commit but anytime you change data if you forget this line and then you have your connection Dot close you're going to go look at the data inside of your connection and it's not going to tell you anything meaningful because nothing will have changed okay so let's go ahead and run this one for inserting the data and again if it works properly all it's going to do is return nothing because we're not reading any data out of the database currently we're just stuffing our data in and again most Ides don't have a good way of reading this so if you go in here you're going to see something that kind of looks like the data you just put in there but in some sort of janky messed up way because uh most Ides don't know how to read this so let's take a look next at how to get the data that is now inside the database read into our python project a lot of the time that's what we're trying to do anyways is access the database pull the data into Python and do something interesting with it so now let's take a look at reading data back from SQL okay so as I said in the inserting data section I'm not going to retype these four lines for each one it just chews up too much time but you have to still have the module in here and you still need need your connection and cursor and always just throw that dot close in right at the beginning so you don't accidentally leave it open all right but let's say we want to fetch all of the data from a table from table in SQL database okay so the command for this is super super easy it is cursor dot execute right which is how we send commands to SQL and the command itself is just going to be select and then SQL uses this star or asterisk to mean all and then from the table sample so if you want to do more complicated things with this like you want to add where to the end and say only where there's a specific condition met we're going to take a look at how to do things like that in the next sections but for now let's just say we want to get all of the data out of this table and then we're going to mess with it in Python a little bit later we want to store all fetched data I got a spell it right fetched data inside results variable okay so just executing grabbing all the data isn't enough yet we need to make a variable that I call results and then it's cursor Dot and then this fetch all command that's built into SQL lite3 and now we have all of the data in this results variable so let's take a look at what we just got let's say for I in results and then colon let's print I so this is going to go through results which will be a list as we grab it from SQL and we're going to print each row from that list in this for Loop and then we'll go ahead and close the connection so now if you remember we created the table in the database that should have these three columns for every entry we sent this list of data to the database now let's go ahead and run this and see what we get back from the database okay so it looks perfect we got um all four of the videos that we made that we entered into the database and now without like having that list anywhere here in Python we are pulling it from SQL so this is how you read data from a SQL table in the next section we're going to take a look at updating data in SQL so you find out what the information in SQL looks like but what if we need to make changes or modifications to it once we read it okay so starting from our same four generic lines let's take a look at what we want to do once we have read the data in right so going back to read data data data whatever let's say that you looked at these and you said Pac-Man's a little bit too generic like maybe you have a video where you cover Pac-Man and Python and then a separate one where you cover like Pac-Man in a game engine and so you want to update that and make it it a little more a little more accurate or a little more detailed of a title so I want to take a item where the title is Pac-Man and I want to change it to python Pac-Man well to do this all I have to do is two lines cursor dot execute should look familiar by now and then instead of quotes here because I'm going to need to use a SQL string inside of these quotes I'm going to use the python triple apostrophe which is just another way to show a block of text or strings and now the command to update text in SQL is update so that is really nice it's really nice how logical a lot of these instructions are let's update the table sample and set the the column vid name right so if you forget your column names you can go back to the table and database where you set them and remember we said vid name is equal to what we are going to make the name for the video so set vid name equal to and we'll say python Pac-Man where now this is where I said like in the select uh in the read data section after you have your um after you have your select from or your Set uh you can add this instruction where where you're adding conditionals to basically say I don't just want to set every video name equal to python Pac-Man I want to do it where the existing video name is just equal to Pac-Man okay so here are those single quotes that I was talking about um and since SQL SQL looks for single quotes around strings like this we are going to use the three apostrophes to Define where our SQL command starts and stops but that is all we have to do to execute the actual name change of the data and you'll see I ended it with a semicolon SQL likes ending lines and instructions and commands with semicolons and then again remembering back to a few sections ago so we have to do connection dot commit now because we have changed the data inside of SQL so if I run this let's go ahead and run updating data and you can see I just get the uh finished with exit Code Zero so it didn't throw an error but now if we go back to read data and let's go ahead and read the data now you can see it says python Pac-Man which is what we changed it to instead of just Pac-Man so that is how you update data next let's take a look at how you would delete data from python okay so to kick off the deleting of data from python I'm going to leave everything from the previous one except the cursor.execute line I'm going to leave commit because if we're deleting data um then obviously we are changing the data so we need to have that commit line in there too right away so the only thing we're changing between updating and deleting is our execution so our Command that we send to SQL we're going to start with the three quotes again but now instead of delete or update it's delete okay so again super logical and we're going to say from our sample table and now we need to say what we want to delete I don't just want to delete everything from the table you can do that if you want to we'll cover that in the next piece which is a bit of a practice problem but we're going to delete from the sample where and I'm going to look at my four videos down here and actually if I'm thinking about it as the owner of the channel well this is a python video this is a python video this is a python video this is just me talking about some stuff so maybe I don't want that in there um so I'm going to say okay delete data where the video name is equal to and then the video name was controls top five okay that's what I called it um in my table and then semicolon and now if I execute this line that is all I have to do to get rid of data that I don't want so I'm going to run that I get my exit code 0 meaning there's no errors let's go back to read data and I pull it in now and you can see it's just python Pacman read from text in python python Excel so the only things I've kept in here were python videos I got rid of that extra entry okay so uh that is it for deleting data a very quick crash course again if you have specific questions on anything you saw in here or I went too fast let me know about in the comments below if there is enough interest I will do a deeper dive into SQL Concepts in Python hopefully you have found what you're looking for in these sections if you want to stick around I'm going to do one pretty interesting thing which is practice grabbing all of the data from a SQL table parsing it out in Python removing duplicate entries and then sending it back to SQL so using python to get rid of duplicates from a data set so we're going to do that next using the concepts that we just learned but if you want to try doing that then I suggest go back to insert data run it several times with the same set of data so like for me I just ran it four times and now if I run reading data real quick you're gonna see I have this really long data set because insert data doesn't check to see if there are already rows of that data in there and so I have all of these duplicates now and so what we're going to do in the next section is talk about how to delete those duplicates using python if you want to try this problem before looking at the solution and going through it with me this is a really great spot to pause the video and test your understanding of what we did because just using the concepts that we know here and some basic python you can get rid of all the duplicates so try that problem and then I'm going to show you how I did it and we're going to go through it together next okay so let's dive into the problem of how do I remove all of these duplicates in a SQL data set using python code so we can do this with just the concepts covered in this video it is a really great way of testing your understanding of these Concepts but looking through the list you can see I have way too much data in here it doesn't make sense for my SQL table to store all of these multiple times so what we're going to do is we're going to start by getting all of the data into python into a python list then we are going to modify that list in Python to remove duplicates using really easy list operations in Python then we are going to overwrite the data that is in the SQL database to just have the data that we want to do it and we can do all of this using just the concepts covered in this video so let's get started with how I did it again as with almost anything in programming there's a bunch of right Solutions so if you tested this and tried this on your own and your way works that doesn't mean that my way is better or more efficient it just means that there are a lot of ways to skin a cat and you found one of them so good job if you were able to get this working let me know in the comments below if you have any questions about the solution we do together all right so the first thing I'm going to do is select all of the data from the database okay so select star from sample just like when we read the data the only difference is now I don't actually want to just read the data in my results variable so just like before it's this cursor.fetch all and then execute select from sample but I'm not just going to print it on the screen now okay what I'm going to do is I'm going to say 4i in results just like before but now I'm not just going to print it on the screen I'm going to check and see if results dot count which now this is a python operation so if the count of I is greater than one this is a python way of saying if the value I which is the individual line entry in our results table that you can see down here well if the number of times that exists in my python list is greater than one then what do I want to do I want to remove all but one of those so I want to say 4 J in range results dot results dot count I minus one so if my count is greater than one I have two three four of an entry then for whatever that number is minus one I want to just come in and very simply do results dot remove the value I okay so I'm going to remove that if it's in there four times I'm gonna remove three of them and that is all we have to do now I am going to show what we did there and say for I in results print I and what's cool is we haven't changed any data yet so I'm going to go ahead and just comment out the commit line for a second and what we'll be able to do here is see what's happening in Python but we're not yet overwriting the data in our SQL table so if I run this run deleting duplicates okay you can see I get just one of each line and you you can actually see I have Pac-Man and python Pac-Man because they're not identical so when I ran my inserting data line it sent a bunch of lines that said Pac-Man in there but my updating data I didn't run that again to change all of them uh to Pac-Man python so I can do that just to get rid of the um just to get rid of those duplicates I can run updating this and now if I do deleting duplicates and run that again you'll see now because I got rid of all the ones that just say Pac-Man they say python Pac-Man um this is giving us a python list that gets rid of all the duplicates but if I look back at just reading data you'll see the actual list of data in SQL still has all those duplicates because all we're doing here is getting rid of duplicates in Python But ultimately we want to send those back to SQL and say you don't need duplicates we're going to overwrite the data so to do that what we're going to do here is now do cursor dot execute and then delete delete from sample and so if we don't put any like where or as or anything else like that it is going to delete everything from the samples table which is actually what we want because now we have the data for I in results we have all of the data that we want to send to that table so we're going to start by clearing everything that's currently in there out all right and for I in results cursor dot execute so we're going to write the data that we do want to send them back in one at a time and we're going to do it a little bit more efficiently than we did in our first uh in our first insert data all right so insert into sample that's our table and then here are the values and again because we're doing this in a for Loop they're going to be question mark question mark question mark because we need them to be variable all right and now the parameters that we're passing in rather than uh doing that like name views date we're just gonna say I character Zero I index one and then I index two so this is a little bit less clear than what we did in insert data where we named them name views and date but it allows you to eliminate three lines of code and three intermediate variables I just wanted to be super clear the first time showing you guys this piece of code so this is all we need to do to say insert these three values our video name our view count and our date into our sample table and now let's go ahead and commit the data that we do change and let's take a look at it now so I'm going going to run deleting duplicates and you can see again it's showing us this line here so it's showing us our python list same as before so let's go to our reading data function that all this does is reads the data that's currently in there and let's see if we did get rid of the duplicates okay and check out that data so now we just have one of each type and if we were to run insert data again we could read it and what we're going to find is okay now we have two of each but if I run my updating data function again I change it to uh uh just two of python Pacman and then I can run my deleting duplicates and all of a sudden all I have in SQL is one line of each video so I hope you found this tutorial super useful I really really think that combining this SQL database with python which is one of the just best easiest to understand programming languages you can unlock some really cool projects and a lot of careers and career paths rely on SQL and python so if you had questions or you want to see a deeper dive into the applications of SQL and python let me know I'd like to at some point in the future combine pandas which is a python module with python and SQL but I only want to make that kind of content if there's a real interest in it so let me know in the comments below let me know with a like feel free to subscribe to the channel check out patreon link down there if you feel overwhelming amounts of gratitude and let me know what you want to see next on the channel thank you so much for watching and until next time bye