Python SQLite Tutorial: Complete Overview - Creating a Database, Table, and Running Queries

hey there how's it going everybody in this video we're going to be learning how to work with SQL light now as filo light is extremely useful when you need some database functionality and don't want to spin up a full-fledged database like my sequel or Postgres so you can use SQL Lite for small to medium sized applications where your database is just going to live on disk or you can use it for testing and prototyping out an application and if you have the need to move up to a larger database then you can later port that over and SQL Lite is actually part of the standard library so there's no need to even install anything and we can just start working with it right out of the box and it's extremely easy to use because your database can just be a simple file or it can even be an in-memory database that just lives in RAM now one thing I want to point out is that this video is just going to focus on using SQL Lite and not how to program the SQL language itself so I'm going to assume that anyone watching this video has some basic knowledge of SQL and if you don't know SQL then I do have a short series on getting started with the basics okay so let's go ahead and get started so first of all I have some sample code here so that we have something to work with when creating our SQL Lite database now this sample code is from my object-oriented series and if you don't know how to use classes or what this code is doing then don't really worry about that you don't have to understand it for this video it's just going to be something that we can add to our database and actually have two files here in our current directory one of these is the employee PI module that contains our employee class that we're looking at right now and the other file here is called SQLite demo and that's where we're going to learn how to use SQLite so if I open up that SQLite demo then we just have a blank script here so right now let's not worry about the employee class and instead just jump right into working with SQL Lite let's say that we want to create an application where we have employees and we want to be able to add update and delete employees from our database as well as being able to grab employee information from that database so in order to prototype out this application let's use SQL Lite so this is in the standard library so we can just go ahead and import this without installing anything so we can say import SQL Lite 3 and there's only one L there and now we need a connection object that represents our data so I'll go ahead and create a variable here named Khan and set that equal to SQL light 3 dot Connect now within the connect method here we can either pass in a file where we want to store our data or we can even make an end memory database do an in-memory database we'll have a string here and say : memory and another : so that's how you would do an in-memory database but for our example we're going to instead use a file name and for this file name we're going to call this employee dot DB so now with just these two lines of code if I go ahead and run this then I know that the files in my directory here are a little small for you to see but it did create this employee dot DB file here in our directory now I want to point out that if you're following along with me and you're using sublime text like I'm using then I believe sublime text hides these dot DB files by default and I have unhidden that on my machine so if you don't see that then you might not want to actually check the file system because that files most likely there even if you don't see it within sublime text now that dot DB file isn't something you'll be able to open up and understand it'll look just kind of like gibberish if we look at the contents of that file but sequel Lite knows what to do with it and that's all that matters so when we run that connect method it creates that file even if it doesn't exist and if it does exist then it just connects so I can go ahead and run this code again and you can see that we didn't get any errors anything even though that employee dot DB file already exists ok so now that we have a connection let's create a cursor which allows us to execute some SQL commands so to create a cursor then we can just create a variable here and I'm just going to call this variable C and I'm going to set this equal to Khan for our connection dot cursor now that we have a cursor we can start running SQL commands using the execute method so we know that we want an employee's table so let's go ahead and create that so let's create an employee table that holds an employee's first-name lastname and pay so to do this we'll do C for the cursor that we created C dot execute and then the SQL command that we want to run so we'll say create now one thing to point out here is that I'm wrapping this SQL and three quotes on each side and if you're not familiar that is called a docstring and it's going to allow us to write a string that's multiple lines without any special breaks or anything like that some people like to do this differently than using the doc strings but this is how they do it in the Python documentation so I'd say that it's fun and when I'll write a statement that's just a single line then I'm just going to use the regular quotes for a string and not the doc string so just in case you are wondering what I was doing there so anyways the command that we want to run is create table and we want to create an employee's table and now let's put in some parentheses here and specify our columns so our first column we want to be the first name so we'll just call that column first and now we want to give this column a data type so SQL Lite has different data types than what you might be used to with other databases and there's not very many of them here and I have the data types page pulled up here in the documentation and you can see here under section 2 that there are basically five different ones to work with here so we have null integer real text and blob so for our first name column we're going to use text and for the last name column we're going to use text and then for the pay we could choose either real which is a floating point value or an integer and I think I'm going to go ahead and just use an integer even though that doesn't give us decimal point values so with that said let's go ahead and set these so we want first equal to text and then we want a last for the last name we want to set that as a txt also and then we also want a pay column and to pay let's set that as an integer now I'm going to go ahead and actually indent this over to be even with that and save that so now that we have our SQL statement to create our table now let's do a couple more things before actually running this and what I want to do here as I want to do a con for the connection not commit now realize there that I'm doing a connection not commit and not the C for the cursor so this commits the current transaction and it's easy to forget this step a lot of people leave that out so if you aren't seeing something in the database that you think should be there and make sure that you're committing your changes and also at the end here it's a good practice to close the connection to the database and we can do that with a con dot close so now let's go ahead and execute all of this code and we didn't get any errors when we ran that so that's good so that created our employees table and that will be obvious if we try to run this code again because if I try to run this code again now you can see that this time we did get an error and if we look at the error here it's saying that the table employees already exist so that's good so with that little bit of code we're already able to interact with the database we didn't have to install anything or start any servers or anything like that so let's comment out that line where we create our employees table and start adding some data to this database so now that we have that table let's add an employee to that database and for now let's just go ahead and type this in instead of using our sample employee class that I showed you earlier so above our commit statement here let's say C for our cursor C not execute now I'm just going to use regular single quotes because this will fit on one line I'm just going to say insert into employees values and now I want to fill in those that first column last column and pay column so I'll do the name as Cori and a comma last name as Schaffer and for pay I'll just make up 50,000 there so now if I run this code then we didn't get any error so that's good and to the best of our knowledge that data was inserted into our employee database but let's find out let's go ahead and find out by querying the database for that employee so to do this we're going to execute a select statement so I'll comment out this insert statement here and now let's create our select statement so at first I'm just going to hard-code in of what we're looking for and I'll do that by saying C dot execute and now we're going to type in our sequel command so I'll so I'll say select star from employees and I'll put in a where clause here to find that employee so I'll say where last equals Schaeffer and save that now that select statement is going to provide some results that we can iterate through so to iterate through that query result then we can use a few different methods here so we have C dot fetch one and what that will do is it will get the next row and our results and only return that row and if there's no more rows available then it just returns none so we have fetch one we also have fetch many and this takes an argument of a number so say you said fetch many five so what that will do is it will return that number of rows as a list and if there are no more rows available then it will just return an empty list and lastly we have fetch all that doesn't take any arguments and what that will do is it will get the remaining rows that are left and return those as a list and if there's no more rows then it will return an empty list so for our example here let's just say fetch one because it should only be one result and actually let's go ahead and print out that fetch one and save that so now if we run this then we can see that it returned our one entry that we inserted into the database and if it can't find any results so for example if I change this last name here to Smith instead and rerun that then you can see that fetch one just returned none because it didn't find any results so now I'm going to go ahead and change that back to the way that we had it and rerun that so you can see that fetch one just gave us that one result that one row if we instead did a fetch all which we talked about earlier now if I run that we still only have one result but now it's within a list so now is add one more employee by rerunning our insert statement here with some different values so instead I'll say Mary and I'll keep the last name the same and I'll just put 70,000 there now I believe that when we run our select statement here that it would do an auto commit of our insert above but just to be explicit let's go ahead and put a commit here so I'll do a con not commit and then after we commit that insert then it will run this select and since both of these employees have the same last name and it should get both of those with that select so if I run this and now you can see that our list from fetch all now has those two entries that have found okay and just so we don't insert any more values into our database right now I'm going to comment out this insert statement here so right now we have typed in the values that we are searching for directly into our select statements but the way you'll most likely be using this in Python is that you'll have some Python variables and you want to put the value from those variables into your query so to see an example of this let's start using that employee class that we looked at before and like I said if you don't know what this code is doing then don't worry too much about it this class just allows us to create employees and when we create an employee it comes in and it sets the first name last name and pay and the email and full name use those variables to create those attributes but we aren't going to use those in this example so now let's switch back to our sequel Lite demo here and I'm going to import that employee class and I can do this because that employee module is in the same directory as the script that we're currently in so I can just say from employee import that employee class and I'm going to spelled that there so I'll take that out so now above my insert statement here I can create a couple of instances of this class so for example I can say employee 1 is equal to employee and then I can pass in values for first last and pay so I'll just say John Doe and for the pay I'll say eighty thousand and now let me copy this and make a second instance of this class so I'll call this employee to and this will be Jane Doe and we'll do 90 there and we can access those first last and pay attributes by saying so we could do a print and I'll do the first one here I'll do print employee dot first and that will get the first name attribute of that instance and to get the others I could say dot last and hey so if I run that then you can see that it did print out the first name last name and pay of that instance and the extra print statement there is just the results of our select query and we still have that print statement in down there so currently these two new employees that we created or just Python objects and we haven't inserted them into our database yet so how would we do this so first let's add John Doe to the database now you might be tempted to do this using stream formatting and let me show you an example of what I mean here I'm going to take out these print statements real quick and I'm going to uncomment out this insert statement so let's say that with this insert statement we instead want to insert all those values from employee one and like I said you'll probably be tempted to use string formatting now you probably see me use string more formatting and my videos before but if you're not familiar with it then basically we're using braces as placeholders so instead of hard-coding these values in i'll put braces instead for each of these and then we can use the format method to populate those placeholders so for the first placeholder we want we want employee one dot first for the second one we want employee one last and for that last placeholder we want employee one dot pay now if you have seen this before and if you use string formatting a lot this is actually a bad practice when using databases in python and that this is the case for just about any database that you decide to use if you are accepting any values from user so say from like a website or something like that for example then this is vulnerable to SQL injection attacks and basically all that means is that there are values that I could set these variables equal to that could break the entire database and the reason for that is because it's not properly escaped so let me show you the correct way to do this and there's actually two different ways and I'll show you both ways here so the first way to do this is instead of using our regular brace placeholders here we can instead use question marks and this is a DB API placeholder and you also no longer need the quotes there to specify that it's a string because it will know that by the values that we pass in so I'm just going to do three question marks here as our placeholders and now I'm going to first I'm going to go ahead and copy these values and now I'm going to totally get rid of the dot format and instead what we're going to do is we're going to pass in another argument to this execute method so I'll just put in a comma there and for the second argument I'll pass in a tuple of all the values so I'll put a tuple passing all those values now one thing that I do want to note here is that even if you're only passing in one value into a placeholder you still need to put it within a tuple which can look a little strange and I'll show you how that looks when we run our select statement in just a second so I'm not going to run this quite yet I'm going to show you the second way that we can use the second proper way to use these placeholders so I'm going to do another insert statement for our second employee here Jane Doe and this second way of doing the proper placeholders is my personal favorite so instead of these question marks we're instead going to put a colon and a name describing the placeholder so for example I'll do colon first and colon last and then colon pay and now we're still passing in a second argument to the execute method but instead of a tuple it's going to be a dictionary and the dictionary keys are going to be the names of each of these placeholders in our SQL and the values will be what we want those placeholders to be so in this example this would be like I said this will be a dictionary now and now our keys will be all of these values that we want to fill in and the values of those dictionaries will be what we want those keys to be equal to or what we want those placeholders to be equal to so first so let me go ahead and copy this a couple of times so now I also want this to fill in that last placeholder and we want that to be not last and we want to fill in that pay placeholder so we'll do that pay and now we wanted to insert the second employee here so instead of employee one this is going to be employed - now this line is getting a little long here so if you wanted to break this up onto another line you could I think I'm just going to leave it the way it is for now now even though this one is longer the reason that I like this method of doing the placeholders is because when you only have one placeholder value I think it's a lot more readable and we'll look at that when we run our select ment statements but for now let's go ahead and run this code and get these employees added to our database I'm going to go ahead and run that and we're still printing out the Select statement from before but now I'm going to go ahead and comment out these insert statements so now for this select statement instead of searching for the last name of Schafer let's also run a select statement that searches for the last name of DOE and we'll go ahead and use both methods of using placeholders just like we did just so that we can get the hang of how to use both of those and how they both look so instead of hard-coding in Schaefer here I'll instead use the question mark placeholder that we use before so I'll say last equals question mark and now the value that I want for that will just be the string Schafer and like I said this is one value so we still have to make it a tuple so we have to put a comma here within those parentheses to turn that into a tuple now that's why I said that the question mark approach looks a little strange with one value because you still have to put it inside a tuple and that comma is needed or else you'll get in err and I've just always thought that it looks a little strange okay so using the other approach so I'm going to go ahead and copy this these lines here and we'll do another select statement below but now we're going to use that other placeholder approach and we'll do another select searching for the last name of dough so to do that we can say where last is equal to : last and now the way that we fill out this second argument is it's a dictionary and we want to say that use this key last which code is going to fill in that placeholder and we say that we want to search for the value of dough and like I said I think that this is a little more readable because even with the one value it's a little more obvious that we're saying okay we want this last placeholder here to have the value of dough so now if I go ahead and run the code that we have now then you can see that our fetch all after the first query gave us the two entries that we added earlier in the video and the fetch all after the second query gave us the values that we added using the instances of our employee class up here okay so we're just about finished up but let me show you one more thing here that I think you'll find useful in working with SQL Lite before I said that when we first make our connection up here at the top you can actually set this connection equal to memory and the way that we do that again is I'm going to go ahead and fill this in it's a : memory and then another : now what that's going to do is it will give us a database that lives and ran and that's useful for testing if you want a fresh clean database on every run so for example now I can uncomment out my create table here and also I'm going to uncomment out these insert statements so now if I run this code then you can see that we didn't get any errors and that's because it starts completely fresh so every time it creates this table and inserts these employees from scratch and you can just run this multiple times and not get any errors like you know the table already exists or anything like that and it also want won't insert multiple values since it starts fresh every time so I'll go ahead and rerun this again and you can see that we got the same result now since this did start from scratch one of our select statements here isn't returning any values because we overrode those insert statements from earlier so there are no longer any employees with the last name Schafer so the end memory database is nice when you're testing and you don't want to keep deleting a database file over and over and over it just automatically gives you a fresh slate and when you're ready then you can just pass in a file and then your database will be stored just like we did earlier in the video so with our database and memory let's quickly prototype out a basic application where we use our created table to insert select update and delete employees from a database just so we can tie everything together and also show you a little trick when we do this to make our executions more pythonic so to do this I'm going to create four functions here right above where we create our employees and just so you don't have to watch me type these out I'm actually going to grab these from my snippets here so I'm going to copy these over and I'm going to paste these right above our creative employees so we're going to have some very simple functions here where we're going to be able to insert employees to the database get an employee by their name update and employees pay and delete an employee so for the insert employees function I'm going to copy and paste one of our insertions from down here and just modify it slightly so I'll copy this second insertion here and put it in here now instead of inserting this exact instance now I'm going to insert the employee that we pass here into this function which is EMP and here's a little tip for making your SQL Lite code a bit more pythonic it's kind of a pain that we need to remember to commit these after every insert update or delete now if you know about contacts managers using the with statement then you might be wondering if there's a way that we can use these with SQL Lite and there is so if you don't know about context managers basically they're a way for us to manage a setup and teardown of resources automatically it's common to see these when using things like files also because people don't want to remember to close files every time they open one and with SQL Lite connection objects can be used as context managers that automatically commit or rollback transactions so transactions will automatically be committed unless there's an exception and then it will automatically be rolled back so to do this with SQLite we can say with con which is our connection and then within this block we just want to put our execute statement and now since we're executing it this insertion from within our context manager we no longer have the need for a commit statement after this so now this is done so now for our get employee by name function let's grab our select statement from down here towards the bottom and I'll just grab this one here and paste that in now instead of searching specifically for the last name of though let's instead search for the last name that's passed here into this function which is this last name variable so I'll remove dough and search for that last name that gets passed into that function now our select statements never needed to be committed so this doesn't need to be within a context manager like our inserts updates and deletes now if we wanted to return just one employee then we could do a fetch one but let's go ahead and return all of the employees with this last name so we'll say return C dot fetch all so now I think we're kind of getting the hang of how this works so instead of watching me type in the rest of these functions I'm going to go ahead and just grab these completed functions here from our snippets and I'm going to go ahead and paste these in here now just a quick look at what our functions are doing here this update pay function takes in an employee and a pay and we are using a context manager here since we're executing an update statement and we're basically just setting the employees pay where their first name and last name equal the first name and last name of the employee that we pass in and for our remove employee here we're using a context manager again since we're executing a delete statement and we're just deleting an employee where the first-name and last name equal the first name and last name of the employee we pass in so now let's delete all of the code from earlier and instead use these new functions so I'm going to keep the employees that we create there now I'm going to delete everything except where we close our connection so now I can insert both of these employees into the database just by using our new insert employee function so I'll insert the first employee and then I'll copy that and I'll insert the second employee and save that and now just like earlier let's grab all of the employees with the last name of those so I'll say employees equal get employees by name and we'll cert we'll pass in no for that name and then we will just go ahead and print those employee results so let's go ahead and run this and see if this is working up to this point okay so we got both of our employees that we inserted so now let's update the pay of one of our employees so we'll we'll set so I'll do an update pay and we'll set employee two's pay - let's just say 95 thousand and last thing let's also delete an employee so we will use our remove employ function and we'll remove employee one and now let's rerun that same get employees by name after we do all these updates and deletions and save that so now if I run this then we can see that the second time we print the results that John Doe has been removed and Jane those pay was updated to 95 thousand so that kind of gives you an idea for how you can use these functions to do this work for you so that you don't have to keep writing these same statements over and over so I think that's going to do it for this video hopefully now you have a pretty good idea for how you can get up and running with SQLite now there's plenty more to do with SQLite that we didn't cover in this video such as doing bulk inserts and things like that so definitely give it a look once you have everything from this video down then picking those skills up will be easy now another great thing about SQLite is that it also works with SQL alchemy now if you don't know what SQL alchemy is it's a popular ORM for Python it that abstract away a lot of differences between databases and I'll probably be doing a video on that and the as well but you could use SQL alchemy with SQL light to get everything prototype doubt in your application and when you're ready you could easily just replace that with a Postgres or my sequel database without changing hardly any of the code so if anyone has any questions about what we covered in this video then feel free to ask in the comment section below and I'll do my best to answer those now if you enjoy these tutorials and would like to support them then there are some ways you can do that the easiest way is to simply like the video and give it a thumbs up and also it's a huge help to share these videos with anyone who you think would find them useful and if you have the means you can contribute through patreon and there's a link to that page in the description section below be sure to subscribe for future videos and thank you all for watching you

Channel: Corey Schafer

Views: 845,328

Keywords: Python, SQLite, SQLite3, SQLLite, SQL, SQLite Tutorial, Python sqlite, sqlite module, Python Standard Library, SQLite Introduction, SQLite Beginners, SQLite Basics, Python Tutorial, Software Engineering, Python Database, Database, SQLite Database, sqlite python, python sqlite3

Id: pd-0G0MigUA

Length: 29min 49sec (1789 seconds)

Published: Tue Apr 18 2017