Python Intermediate Tutorial #8 - Database Programming

what is going on guys welcome to this Python tutorial series for intermediates in today's episode we're going to talk about database programming in Python now databases are one of the most important and most popular way to store data in computer science and in today's video we're going to focus on how to implement database programming in Python notice that we're not going to talk too much about database structure itself we're not going to talk too much about SQL because you know for databases most of them use the structured query language called SQL which is basically what it means structured query language but we're not going to get too deep into that so you either should know something about SQL or not care too much about it we're only going to discuss how to do this in Python of course I'm going to use SQL code but you don't necessarily need to understand it however it's quite simple to understand because it's almost plain English so let us get into the code now the basic database that we use some pythons called SQLite 3 of course there are also modules for MySQL or MongoDB and so on but for this video we're going to focus on SQLite 3 so the first thing that we're going to do is we're going to import SQLite 3 now to work with databases we first need to create a database so what we're going to do now is we're going to create a connection to a database this connection works in the following way if there is a database with that name we're going to work with offline file databases we're not going to connect to an online database now so if this database already exists it's going to connect to it and if it does not exist it's going to create a new one so we're going to create a connection object and this connection object will be the result of the method connect so SQLite 3 dot connect now SQLite three darknet the connect returns us a connection and we have to specify what we're going to connect to so in this case this would be the database name I'm going to call it my data dot DB you can call it whatever you want and you'll see that when I run this module right now nothing happens but on my desktop I get a new file called my data dot DB and I click on a property so you can see it's a database file so this is now our database it's empty there's nothing in it but it's like the database created and when I run the script again of course again nothing happens but it connects to the database so we don't get an error the second thing that we need now is the so-called cursor because we now have the database but to execute SQL queries we need to have a so-called cursor which allows us is it's more or less the object or the interface to the database so every time I want to execute something we need to use that cursor and to get that cursor we just say cursor equals connection dot cursor we got the cursor for disconnection and that's basically it that's the setup that we need to work with the database and everything from now is or almost everything from now on is pure SQL so for example the first thing that I would do is to create a new table because the database is structured in tables and at every table has rows and columns so let's just go ahead and create a new table in this database what I can do for this is I right cursor dot execute and execute is the method that we're going to use to execute a query or statement in SQL so I'm going to define a multi-line string here so that it's a bit a little bit more readable and I'm going to say create table and let's say I create a person's table I want to have a table of different people different person song don't say create table persons and then I have to use parentheses here and specify the columns so I'm going to say ok every person has a first name which is of the data type text so this would be the string in SQL of course I can also use different a different datatype for string for example this one year bar car or bar chart r32 which is variable character of the line 30 queue but I'm going to just use text here because it's only for demonstration purposes then we also have a last name also a text and then we have an age for example integer this time in SQL we need to write a full word integer actually I don't know if we need to but we can we don't have to write in the least and Python we have the in keyword here we can write integer and this is basically your statement this creates a new table and empty table with three columns so I can just execute this but before I run the script here everything that you execute does not actually get applied until you commit so what you have to do is the cursor executes the statement and the statement is more or less in the connection but to really apply to the database really committed to the data player to the database we need to say connection dot commit and then of course every time in the end I should close the connection because this is what we do with connections so run module and of course we don't see anything but what happens when I run the script one more time is we get an error message because we once created this database and this table sorry and it says table persons already exists because we created the table it got saved in a database then we load the database and try to create this table again but this table already exists so we get an error message now we can also can direct us by just saying create table if not exists things it should work and then we don't get an error now what we can now do is we can insert data into this table we have now a database with an empty table and now what we can do is we can execute another statement that insert some data into this table so what I can do is I can say cursor dot execute and a multi-line string here and just say insert into persons values and then I pass parenthesis here with the values so I can say the first person is Paul Smith and he's 24 years old then I can say I don't know mark and Mark's name is Johnson and he's 42 years old and then we have Anna and Anna is also called Smith and she is 34 years old so these have the same last name and these are the values that we put into our table so we insert these values here these entries these rows are or these persons into the table persons so when I run this will not we're not going to see anything but we also don't get any error so it probably worked and to now see the result what we can do is we can make a third statement that selects these rows or these objects these persons out of the tables so we want to print them out onto the screen so what I can do is I can say cursor dot execute and then I say select by the way all this screen code here all these all these statements or SQL code if you haven't noticed yet so it's not Python we're just passing SQL statements so I say select everything to select everything from persons and then we specify a condition we say where last name equals and then I say Smith so in this case what we're trying to get here is all the person's every value from so all columns from every world a row where the last name is Smith so we don't we're not only interested in the first name the last name or the eighth row interested in all three values but only of those people who have the last name Smith so to get the results now I say results or actually I can say Rose because this is what you do in the database or world you oftentimes referred to this as rose and the rose would be just the connection or actually I'm not sure if you have to say cursor dot fetch or connect that's all but I think cursor and then we print rose yeah it was cursor but why did it do that five times it's actually you right but it's oh I know why because I executed the script way too often of course because I inserted the same values over and over again but when I create a new database we get the two are the two rows Paul Smith 24 years old when Anna Smith 34 years old we didn't get all the rows because marks last name is Johnson so we filtered out the two entries where the last name is Smith now what we could do now is we could combine object-oriented programming with data based programming so we have these person entries in our database and what we could try to do is we could try to load these database entries into our script as objects and the other way around so we create objects in our script personal objects in our script and then we load these objects to the database in form of a database row entry and to do that what I have to do here or what we have to do here is to create a class person with a constructor as we already know how to do that so first-name lastname and H I'm going to use first and last so that we don't have to type too much and then just say self dot first equals first just a basic constructor and when we have the constructor we can now specify or define a method which loads a person from the table and converts it into a Python object so we can say load load person and now we have to think about it how do we want to load that person because in this case which you will never find a database like that in this case we don't really have a primary key we don't have a unique identifier because in databases you always have something like an ID or social security number or the combination of name age and last name or birthday or something like that in this case we can have many people with the same first-name with the same last name with the same age so it's not really that perfect but we could just go ahead and define a new ID here so ID would be an integer and a primary key and this would be unique then of course so I could also specify an ID here but I have to call it a different name because ID is a key word in Python let's just say number for now or ID number and then I say self dot ID number equals ID number now what I could do is I could just say self and the ID number that needs to be passed and then we could go ahead and load the person that has this ID so we could just execute of course we have to define the cursor up here so that we can use it or actually it doesn't work that way because we need a cursor defined in the class so I have to specify everything in the class here as an object maybe so self dot actually let's do it like that self dot connection equals connection and self dot cursor equals cursor and then what I do is I just say cursor dot execute and I execute a select statement so the Select statement would be select everything of the person so from persons where the ID equals and then a placeholder format our ID number basically that and then I get the results of course so the results would be cursor dot and this time we're going to use fetch one so we're not going to say fetch all we don't get a list of tuples we get a tuple of the first element so we for half which is impossible in this case but if we have a or if we have multiple entries with the same ID we would get only the first one but in this case we have only one entry or a maximum of one entry because it's a primary key and a primary key is unique so I get the results here from fetch one and then I say self dot our ID number of course is the ID number if it wasn't already specified but then we say self dot first equals and then we have to say results and specifying index the first index or the 0th index is the ID so we got we are going to say results one for the first name self dot last equals results two and the age will be results three so it should work that way hopefully and yeah basically that's how we load a person so now we have to execute this first of course so let's again copy that it's a bit chaotic right now because I'm improvising this code here I mean it's kind of prepared but the the stuff with the ID for example was improvised so it might lead to some errors but I think it should work let's first of all delete the database table person that okay of course we need to specify some values here for the ID because we now have a an extra column but other than that it should work so let's delete it yeah now we have an ID column as well and therefore if we remove this code for now what should work is that we create a new person object and then load it into the database so the database already exists and what I'm going to do now is I'm going to say P 1 equals person and this person has the ID I don't know 8 or actually no actually I have to specify an empty person because let's just say n minus 1 here for default values because I'm trying to load one I'm not trying to insert one into the database so i'm going to add some random values here and I'm going to say P 1 equals person and then I'm going to say P 1 dot load person and pass the ID of Paul this was 1 and then hopefully if I print P 1 dot first it should be Paul if everything worked out fine this should be the case connection is not defined wise connection not defined of course connection is not defined because I have to refer to self dot connection this is quite logical here let's make sense not a big deal then we have another mistake here operational error unrecognized token in line four where is it 14 sorry our load person dot execute uh-huh I know why because the format is out of the parentheses here we're modifying the string not the statement now it would work yeah so now we have Paul as we loaded Paul into the person so I can also print the other values here I can say p1 at last and print not p2 sorry if you want at last p1 dot H and P one ID actually ID number in this case and then it should work we got Paul Smith 24 years old and the ID is 1 so we successfully loaded 8 an object from the database into the Python script as a Python object now we only have to do this the other way around as well so we now have a an object let's say we define an object here p1 equals person and we pass certain values ID number will be 7 then the first name will be I don't know Alex and the lastname will be I'm very bad at improvising or brainstorming American last names but let's just say I really don't know let's say Robin's because of Tony Robbins and then we say H equals 30 and this object right here does Python object we want to put it into the database as a row entry and to do that of course we have to define a new function we're going to say def a insert person and for this we're just going to insert we don't need any specific parameters here because we're just going to take the object as it is and insert it into the database so we're going to say cursor or sorry self dot cursor and dot execute and we're going to execute the state which is insert insert into persons values and then we're going to say placeholder placeholder placeholder and placeholder I think we only need four values yes and then we say format and we pass self dot ID number south dot first south dot last and South dot H and of course then we have to say self or actually we have to say connection self dot connection god commit and to check that I need to somehow load it back but let's just see if this way if this works first if we get any errors here now we don't get any errors so probably the entries already in there and now that just checked that I'm going to make a quick select everything statements so I'm going to define the connection here and a cursor here and then I'm going to say cursor dot execute select everything from persons that's it then I'm going to say connection or actually I'm going to see results equals cursor dot fetch all we print the results and close to connection this should work actually self is not defined self of course yeah no doesn't work why doesn't it work I insert person I committed to the connection but it does not get inserted into this maybe because I need to close the connection let's see if that solves the problem self dot connection dot close before we open a new one I don't know no that's not the problem insert into persons values we enter all of that we pass all of that does not work why doesn't it work okay I figured out why it didn't work because I'm stupid I of course need to call the method so that it can work so we're going to say insert person here we cannot run we cannot see result if we don't run the method and no such column Alex oh all right we have to specify the this year strings because when we enter some or when we pass something into the format function we don't get Alex as a string we get Alex I mean we get LX as a string but we don't get it with quotation marks so in the statement itself it does not have quotation marks and it looks for a column named named Alex so we have to specify for ID doesn't need to be the case but for the first and the last name I need to specify quotation marks here because otherwise it's going to look for a row with that name and now it works finally as you can see when we query out all the elements from person we got Paul mark Anna and Alex Robin's here with the ID 7 and eh 30 as we defined him here in Python so that's basically what you need to know about database programming in Python at least when it comes to sqlite3 of course as I said we have modules for MySQL and MongoDB and so on and all kinds of different databases and also the queries were kind of simplistic today because this tutorial uses a Python tutorials not an SQL tutorial so we didn't have any joins or sub queries or anything like that anything complex because we learned how to execute statements SQL statements and Python and you can use that to do all kinds of things in SQL so if you need to learn SQL go on Google look it up look up SQL tutorial I bet you'll find something and maybe I'm going to make some SQL tutorials in the future who knows so that's basically the way it comes to Python and database programming so thank you very much for watching hit the like button if you liked the video subscribe to this channel if you want to see more feel free to ask questions and give feedback in the comment section down below and thank you very much for watching see you next video bye [Music]