Load Database Tables Into Pandas

Key Takeaways

what is going on guys welcome back in this video today we're going to learn how to take database tables and load them into pandas data frames as well as how to convert pandas data frames into database tables so let us get right into it [Music] now for this video we're obviously going to need to have pandas installed so if you don't have panels installed you need to open up your command line and you need to type pip or pip3 depending on your operating system and installation in my case pip install pandas in order to install the module and once you have it installed you can import it in your python script by saying import pandas and we usually give it the Alias PD so I think most of you guys will already know this in addition to that we're also going to make use of sqlite 3 so we need a database that we can work with because as I already said in the introduction we're going to use pandas to load data from the database and store it into a data frame so we're going to convert the database table structure to a pandas data frame structure and vice versa so we're also going to take Panda's data frames and load them or write them into SQL tables and for that we're going to need a database so we're going to say here import sqlite 3 uh which is a file based database and we're going to start by creating some simple sample data so we're going to just have a simple people table and we're going to have name social security number and H or something like that and then we're going to take that data we're going to add some instances we're going to take those instances and load them into a pandas data frame for the first step then we're going to create a pandas data frame with new instance system we're going to write those into the database so let's start by defining the connection to be sqlite3 connect it's going to connect to a file let's say mydb.db so in the case of sqlite3 this will either create the file if it doesn't exist or open the file which is the database if it exists in our case it doesn't exist so it's going to create a new file and in order to do something with that connection we need to create a cursor so we need to say Cur equals connection dot cursor this will allow us to execute statements to query data and so on and uh what we want to do first is want to say Cur dot execute and we're going to have a multi-line string here just a simple create table if not exists people and we're going to have the following values inside of it SSN for social security number this will be an integer let's say um and it will be the primary key then we're going to say we want to have a name which is a varchar 255 and not no and then we want to also have an H which is the integer it can be null as well so a very simple database table we're going to now also say cur.execute and we're going to say insert into people values and then we're going to add I mean actually we need to First Define here what we're adding social security number name H like this so entered into people SSN name H values and here we're going to now pass a bunch of um actually we don't need these parentheses here we just do it like this so we're going to enter the values uh one for example it's going to be Mike and Mike is 25 years old and Mike should use single quotations otherwise Mike will not have a name so we're going to copy that and you can choose whatever you want here as a sample data so 90. something like this something like this something like this I'm gonna have Anna I'm gonna have Bob we're gonna have John and we're gonna have uh uh I don't know Stan something like this then Anna's going to be 35 Bob is going to be 76 John is going to be 55 and Stan is going to be 18. so those are the sample values to now commit those to the database we just have to say con dot Commit This will then be written to the database so I can already just run this file here so I can just execute this and you can see we have this database here so I can double click it it opens in pycharm the um the the database here and I can just say select everything from people then I can run this and you can see as a result set down here I get the table with the name H social security number um of the individual people so that's a sample down now let's go ahead and take that just make the query give me all the people as we did just right now in the database Explorer give me all the people all the instances from the people table and instead of just showing them we want to store them in a pandas data frame you saw that the format is compatible we have a primary key in in pandas we also have an index we had a name column in h column we can also have columns and pandas so all we need to do actually to take that now and turn it into a pandas data frame is we need to say SQL equals and then PD dot read SQL query so we take the query and we need to provide here what we want to have so in my case this is going to be select everything from people you can of course also have multiple tables you can have joins and you can have filters so you can say okay where H is less than 50 or something then you'll only get some of those um but once you have the query what you do is you also specify the connection so what happens here is you have the SQL equals PD read SQL query this query applied on that connection and in order to get the actual data frame we now need to say DF equals and then PD data frame and the input for the data frame will be the SQL object that we have here and we're going to also Define the column name so we're going to say the columns are SSN name and H so I think I will have to delete the database because otherwise I'm going to enter the same values here uh again what's the problem here why can't I delete the database is it still open somewhere let's see okay I'm not able to delete it so let's just go ahead and change the name so we have a new database udb.db and what now happens is again I create the table I insert the values and then I commit the changes then we get all the people and we turn them into a pandas data frame so in order to see that this works we're going to say print DF and I hope my PC is not lagging I hope it's just pycharm when I run this now we should see a pandas data frame having the same values inside of the data frame and as you can see this is actually the case so we have the social security number the name H and we have an index here we can also of course go ahead and say something like DF dot set index and we can set the index to SSN with the in place keyword argument to true so that we don't have to reassign this um now let's see if I can delete this one okay I can delete this one um and now you can see that the SSN is actually the index of the primary key and this is the pandas data frame uh version of our database table we can also now do the opposite so what I can do here now let's say here a comment new section or actually let's call the section pandas to SQL what we can do here is we can just say new data frame so new DF equals PD dot data frame and of course you can also load that from a CSV file you don't have to create it manually I'm going to use here a simple dictionary I'm going to say SSN is equal to something something [Music] so three people here then I'm going to say name is going to be uh Fox lion and Cat I know those are animals but we're gonna say that people have these names um and we're gonna say age is equal to 50 35 6. so this is now a panda's data frame with new data and we can take this data frame into read it or write it into the database so we can just use a specific method of this data frame to turn it into SQL code and thus into a database table so all we need to do here is we need to say new underscore DF dot to underscore SQL so basically the opposite of read SQL query is to SQL and all we need to do here is we need to provide a table name people in our case we need to say that the connection is the connection object and we need to say what happens if the database table already exists so in our case the people table already exists because we already wrote some data into it and we already created it in this case it could also have a conflict if you don't provide a keyword so it would say okay the table already exists so I cannot write anything to the table but if you specify here if exists equals append this is going to take the data and append it to the existing table and we also want to say index equals false so that we don't have any problems and when I now delete this database and when I rerun the script now first of all we can see that we still get the data from the database up here but then if I double click on the database I open it up here I say select everything let me just zoom in a little bit select everything from people and I run this you will see that I actually have these new instances lion Fox and Cat inside of the table here as well so this is how you take database tables and load them into pandas data frames and vice versa so that's it for today's video I hope you enjoyed it and hope you learned something if so let me know by hitting a like button and leaving a comment in the comment section down below and of course don't forget to subscribe and hit the notification Bell to not miss a single future video for free other than that thank you very much for watching see you next video and bye [Music] foreign [Music]