How to load data into PostgreSQL using Python | Your first ETL pipeline | Data Engineering Tutorial

guys welcome back to another python class and today we are going to um cover everything which we missed last week because we had a break and I will show you what we have planned for today so I just want to give you a very quick recap of what we did last time which is connected to the ETL Pipeline and then we will just question ourselves or ask ourselves what we know actually and what you should practice now because we um arrived at a point where you already can learn a lot of Topics by yourself and I prepared some demo for you um how you actually can Implement an ETL pipeline from extracting all the data which is based on tasks number eight and how you then can load it into a database in this case it's postgres as you remember right so let me just do a quick recap for you here so last time I told you how you or how I approach a web page if I have to scrape it right and in this case this is just like our community web page the self-def community where you can see all the data or the information and then when I go into a community you can see all the people here right I can make it a little bit bigger maybe yeah you can see that and what I told you is you need to have some kind of web development understanding and that's um the inspector tool here or how you want to talk call this the dev developer tools and that's where you can see all the source code and then the most important party actually is the network tab where you can see all the information which this websites those websites you are visiting um get from the server so they can actually show them in the front end so if I um refresh the page you can see what's happening here and that's why you also can see this waterfall of um the all the I don't know in this case the CSS files which are getting loaded so you can see how long they take and if I go on all let's see if you can see all of them yeah you can see how long the webpage also needs to do all often that and wait let me just turn this off yeah and um that's just another additional information but your task in number eight was to scrape all the people from this page right or something like that and um then you also had this task to create a Json or a list of dictionaries where you just understand how dictionaries work and how to enrich the data right after all I hope you did your homework actually so let's just recap what we actually did last time and what we already know you already know how an ETL pipeline is um or consists of oh wait where can I go can I go here yeah so you you remember what is it what page can we take yeah let's let's take this one again so just forget this one so you know um let's let's pretend that um all those different pieces you can see here are different sources where you can get all your data from right and this is your um python code and maybe also your database where you then want to implement or load all the data in so that's why what you need to know is that the ETL pipeline consists of multiple data sources and you have to collect them in your python code and then you have to transform them and load them somewhere so you can store it and other people can work with it right and today that's the most important part actually is where I'm going to show you how you can use Python and postgres to do exactly this because I think um you probably know how to do that but I just want to give you some best practices so you see how I do that and how it would look like if I do that and for that I took the number eight task from somebody I think it was from Johnny but uh yeah also if you have any questions guys just let me know in the chat so I can read that um yeah so um let me go to Discord quickly so we actually can check the GitHub repository so I took this one from Johnny and it was task number eight yeah yeah I make it a little bit bigger um yeah so you know this file where you actually created um the dictionary or the the list of self deaf members from our web page um because I just skipped the part the part where I scraped the data I just take this one so we just assume we somehow scraped the data already and we don't have this because we don't we um got it from somewhere because in in the real world you either have to scrape it yourself so you have to build a scraper or you get the data in form of um different file formats like a CSV or Json or another database I don't know it could be anything or just apis and then you have to put them together right but in this case we assume we already have some kind of data and what we want to do is let's say you have here the weight with the web page again so let's say we have this page and let's say they all are famous people who you need to analyze and that's why we put them all here and this is just um your your what is it called um soil code your visualization how you do that so you have this web page which is called the self-def.com like this and you need to scrape all of those users into your python code right and then you need to transform them somehow so just pretend this is a cork yeah so you just do something on top of that and then you need to load them into your database right so ETL so this is e t and L and so to do that I prepared some code for you which you can see here and let me do it a little bit bigger or put it way to make it bigger oh my God is it oh my God it's pie charm again I have to do it in the settings so let me go um font appearance no it's where is it general or not it's here no it's not here oh my God what is it again font editor I hear current current what is that I don't want this I want this one how do I activate it oh my God please why is it not working oh my God you can't see that right enter presentation mode what do I have to press because I installed Vim so it took something like that what is that why that why does it say like this oh no I know why because I have this cyberpunk theme okay oh perfect a little bit too big okay let's go to 35. okay so I prepared a couple of files which you can see here but you can see this is all like the whole playground here you need to see them actually because I have a lot of different data here um but what you need to know is that I have a main file which you always need to have right and then I have um those functions which you can see here which makes your whole main file look very clean right and um what you guys have done so far is that for example if I go to my helper functions which are over here I think it's a little bit too big still let me make it a little bit smaller uh settings to 30 please don't be yeah okay it's fine okay you see I have a lot of functions here and what you guys have done is that you probably um just put them all above your top top level environment here which is totally fine but when you have a lot of functions like what I have here like I have one two three six yeah six functions somewhere how here I don't want to have them all over on top of that because it's a little bit confusing and people who have to work with you they then forget uh or they they don't know what they are looking at immediately that's why I just try to modulize it a lot okay and um now I just want to show you what I normally do so the first step is that I um always initiate a logger okay I don't know if you already implemented the loggers because um for Johnny I told him he can practice working with loggers already and um for those who don't know what loggers are loggers basically are um objects which you can create yourself and they help you to lock all your events which you define for yourself and for that you can say okay if I open a file you can say print or file got opened right and then it prints into your um terminal uh here on the on the bottom some somehow which is fine but if with a logger you can also Define um for example a logging file and this file tracks everything you set you want to track and saves it into your file and you can look it up every time or anytime and you also can Define okay in which function did that happen at what time did it happen what is the state of a variable or whatever you want to look at so you don't have to print your very variable or every iteration of your for Loop every time to see what's even happening and with a logger you just can avoid all of that and you don't have to print all the time and for that I just created a function which you can see here on top and the function is in a module and remember the module is like your own python file which um is collecting all your functions which you want to reuse all the time and for that I created a folder here maybe if you if you can see that it's this folder here I think it's a little bit too small I don't know if I can make this bigger no I can make a pick oh my God I just closed it okay I don't want to look how to do that how to make this bigger but yeah you can see the utilities folder here and in this utilities folder I have a file which is called get data and here you also can see I have a file which is called helper psql which is who's standing for postgresql right and uh you also can just put it into your main folder just like that and your root um root path it doesn't matter but I like to keep everything like very structured that's why I create a folder which stands for utilities which basically are your tools which you use to help you doing something right and in this case I imported it and said okay from utilities helper helper psql import init logger and then you can see here on top it's called the helper psql and this function is basically doing what I want to have as my logger so um for those who don't know how to do that you basically just import the logging module here which is um coming with python by default so you just have to write import login and then you have to give your logging object some parameters and in this case you just have to say Okay login you see it's just coming from login logging dot basicconfig so it's a function and then you need to say okay the level of your logging um on the level means if you want to lock a warning an error or whatever that is but that's not so important right now and this is my format how I want to print it but you you will see that when I run the logger okay maybe I can just can do it here and comment this out so you can see what I mean so when I start this main function here in my main database.pi file it will just go into init logger and then just do this it will just say logging info and then start of program and blogger which basically is the same like if you would say print yeah you could just say print like that okay so let's see what will happen I'll just run it boom and then you see this is my print right the the thing which you always do so you just print it okay fine I can put a variables whatever you want but here you can see the logger suddenly is red so it you can see what's happening and then it says the format was here format so you don't need to understand how this is uh built if you want to use a logger just use it how how you find it on the internet whatever that is and you can understand it later but right now you see how this actually is built so it tells this is the time and you can see this is my date and also with my time so I see when it happened and if I look at the time right now it really is um yeah it was this time when I logged it and then I set the level name and the level name basically is this so I say logging dot info and that's why I uh get the info State here I also could say um wait uh error and then I print to run it again then you see oh boom it says error boom I have an error somehow but it you should add this at um at a try accept block but in this case I just had it to to show you so I just keep it like this I run it again and then you see it will just print what I said it should print in this case it's a startup program and my logger that's it nothing more and I can replace this it's gone and if you don't um uh Define a log file the the file where you want to have it I think it's lock uh it's like this or something and then you can say uh log.lock or something I think it's this one or file I I don't know I need to file name something like this yeah file name and then it will create this log file actually and writes it inside so I don't know if that actually works or you just can look it up um let me go here let's say python logger log file so let's see if it actually works how to lock so this also is a very good page where you can check out all of the information so you can see the level of um logging log file fire log into a file so you can see here yeah it's exactly like this so I can define a file name and then it will work like that but in this case I don't want this because it is just some demo but you could do that if you want so this is the first part and if I do that in a logger I can use it in every function which I have and then it will always lock everything which I do so you always know what's happening and then after that I need of course uh to connect to the database so that was just the logger and then I have a function which is called the psql connection test so this is a function which you always can find on the internet and which works actually like this weight I need to make it a little bit smaller here can you see that actually wait I'll close this one a little bit okay no hey how do I close this isn't it not my oh it's the structure okay so you can see this is my whole test function just to test if I successfully um connected to postgres and to do that of course you have to start your postgres server and uh maybe have a GUI like PG admin which I showed you before like this one where you can actually connect to your database and see what's happening and in this case I just have this one and um I don't want to go into detail all of that if you want to understand it in detail just look up my code I will create a repository and also now upload all the tasks here you can check all the tasks from one repository instead of Discord and I will also upload this code so you can see it but what I basically do is look at this I say the first thing is logging dot info connecting to the pro skills database so it will just say this so let's see and then I have um this part where I connect to my database so um I think you'll remember how to do that so you need cycle PG let's that's why psycho PG is imported up here and then you have to connect a function which takes all those um parameters or arguments here actually and then you can create a cursor and then just execute a query in this case it's the version and I want to lock the version to see if it actually works so let's test it and do this and then as you can see here it says startup program because in my inner blogger it says startup program then connecting to database so you see connecting to a postgres database so it's exactly what I what I'm doing and you as you can see it doesn't even take like it's it's at the same time so the the second how long it takes is is so fast it's not even visible here because I just have three uh digits after my comma of this time but yeah and then I have personal Statewide version because I say it here and then I do or execute it and then you see I just fetch my result and then I lock the database info and then it tells me postgres 15 because also if you don't know postgres just released uh the version number 15 and this is information and then I just close the connection again in my finally blog and then I say database connection close so that's my test and I know okay it works perfect then I just have to copy all of this part here basically and put it into another function where I can use it for some data processing and this in this case I created another function which is called pskp psql connection and as you can see I also have my dock string here which basically is just telling me what this function should do but yeah it's basically clear and it doesn't have any parameters so it does matter and then I just start by setting the connection to none so I know if it returns none it's it's not working but actually it's useless it depends on you how you want to do that but I see it also in a lot of code maybe it's best practice I don't know but it's not so necessary and then you can see it's exactly doing this same thing which I did before so I'm just connecting here and it works and then I return the connection and that's what you can see here when I comment this out you can see I use this function and then I get the connection back because the thing is you don't want to open a new connection every time you want to connect to the database right so you just want to connect to the database one time have the connection open and do everything you want and then close it instead of opening it closing it open it closing it except if if there's a lot of time in between then you should or can do that but for that I um created the connection object and return it and also here's another thing guys the try accept block I don't know if you work with it a lot but I totally recommend you to do that and also to check out the different types of exceptions you know because normally what you do is um you can just write accept exception as error error and then I say logging in for error and then it will just print me the arrow and it doesn't matter what kind of exception I get in this case everything which happens will be locked and then it's gone then it I have an arrow it stops whatever that is right but in this case I know because the only thing which I do here or the the only code I execute is related to Psycho PG and it's connected to a database so I only can get an exception which should be around a database error that's why I set it as psychopg database arrow and you can look it up but it's also okay if you do it like this if you don't know okay and you also just can leave it like that if you want but um I think you want to keep it a little bit clean so you just use what is happening here okay just checking in if you guys have any questions not good good good no question so far perfect okay I'll just leave it here a little bit of it okay um so far so good did you understand that guys just give me some feedback while I drink afro Blazer anybody okay I get it you you're nodding perfect okay so that's basically what I do first and then I have the connection and now I have you need to remember what you have you have a connection object and this connection object is used to create cursors cursors to actually can execute or commit or close your whole connection or not not close it actually just commit and execute all your um SQL commands right and in this case I just created it and then what I wanted to do is that you guys remember when I go to Johnny's bootcamp we had this kind of data right so we had the self-step members and all of them had a name that's good name reason to code and your task was to enrich the data and say okay I want a new key which is called um addiction yeah addicted to and then you need to give everybody a unique uh or a different addiction and then it will probably look like uh not like this I don't know I'll just show you my data so somewhere here and my data so I also have a different folder just for all my data normally but in this case I just have the members from the self-def community as you can see here so this is the data so you see this is what we had before and then we had the enrich data so if you can see that name Discord reason to code improve or got upgraded to addiction I just changed a couple of keys here as well because it makes a little bit more sense than to say reason to code but that's not a big deal so you can change it however you want and Discord ID coding motivation addiction to Among Us whatever you want and this is our data actually and now imagine we extracted the data from our web page or whatever that is where we are at and then you um created some kind of list or a dictionary and edit all your data into that or you you dump them into a CSV file or whatever that is it doesn't matter because um in the next lesson I will show you different ways how you can store your data to make it a little bit um mobile so you can transfer it without before you actually do something right so we will then see how we can work with CSV files um Json files or um data frames that's like the most interesting part actually so that's uh how it will be but in this case it's a list which you can see here on this bracket of dictionaries which you can see on those um curly brackets right so this is the data which I use which I want to put or load into my database okay and remember let's try to use the right words because places here he likes knowledge and he likes to use the theoretically great or good words because you put it into your better ways that's right but what you actually do is you load it or you insert it into your database and so we close this one so you now see what we have here you're lagging heart oh my God why am I lagging [Music] um is it just for you Blazer or is it also for afro afro am I lagging or uh Johnny guys am I legging oh my God I'm lagging okay let me just stop virtual Cam and do it again stop boom okay let's do this here start with a cam okay am I still lagging is it just my voice or is it also um my cam a little bit is it better now hello we are testing am I lagging hello hello okay so what are you writing so much displays are writing a book again oh my Lord the video then the voice started to lag okay he's writing on this phone fine now okay it's fine perfect perfect okay okay afro I guess it's good now perfect okay so then guys I hope it will stay stable but of course we we are almost through okay so sorry guys let's go back to the data actually so remember this is now our data which we extracted somehow or somebody sent the data and imagine we don't have um I don't know what are the five um objects or five um um yeah I don't know rows in your uh which you want to have in your database but you have like millions or something right and not just five we just pretend we have a couple of more and now I need to understand how this data looks like and in this case it's pretty straightforward because everything is together and we just have three or four different keys in this case it's named Discord ID coding motivation addiction so this is a process where you do database design and you have to come up with a schema and then you have to create a table for this schema okay and to do that you need to understand okay what or how should I call my columns in this case the keys of your dictionaries should be your columns so that's pretty straightforward so you have name Discovery called the motivation addiction and then you need to understand okay what kind of data type should they have and in this case all of them actually are strings so that's also straightforward but sometimes you also have dates or numbers or float I don't know what you could have and you need to to um know that right and here's another thing for example sometimes here you have um Hobbies and maybe we also have favorite food and then you see this guy only has four columns or Keys actually and this guy has six right and then imagine this guy um um favorite programming language then it's Python and then this is another one which you don't even have before right that also could be the case and then you need also to um look at all the data first and scan through it to see okay what columns do I have before before I create the table so let's imagine you just look into one one data set in in this case or like one object of your data whatever you want to or whatever you have I don't know but let's say this is one row okay because we have a database and we have columns and rows and let's say this is one row and all of them have four columns and then you're like okay we're four columns then we just create four columns and that and then we insert all of them but then you will start your script and then it will say oh your your table doesn't have uh the column favorite food and then you're like what is the favorite food I didn't have that here so that's why you you need to also um check through all the data or of course the best way to do that is to have a script which basically scans through all the columns or like the keys of your dictionaries and gets all the unique keys and then you know those are this is the list of keys which you have and then you also can check the values and then based on that you can create your table but let's start very simple and say we don't have all of that and all the data which we have is the same okay and that's why we go and create a function which is called create table just very simple very basic it doesn't matter and um I created this helper postgres file module which should have this file and this is here and as you can see remember I created this postgres connection object which is here and I just pause it to create table so I know I already have one argument or one parameter for my function which this doesn't doesn't exist yet but it does it it does exist because I already created it right so I just can scan through it and explain it to you so I go into my file again I created create table function and then of course I have to import it here that's why you can see it somewhere here yeah exactly create table that's why it's here on top if you can see that so I imported it now I go into the file create the function create my doc string and say what should this function do and then I create my query which should create this table remember we don't want to do that in PG admin so you don't want to go here and then right click on it and say query tool and then you don't want to say create tablet right you want to do it in Python because we want to automate it we don't want to to do that once or something like that yes hello Marvin you see SQL I'm just showing the whole process how I do that when I have some data set already and they have the perfect um states where they are clean already and then I import them into my python or like I just load it into pyth and then transform them and load them into postgres database and in this case now I have to create table function and I create a variable which basically consists of my query which should create a table and remember how to create a table you say create table if not exist means if it doesn't exist then you create it but if it exists don't do it okay so in this case I say okay if it doesn't exist create the members um table and and then I have to of course say or call and and label all the columns in this case I have an ID and remember when you do that you don't need to say member name you also don't need to say member ID because this this is like um um too much information because you already know you have a members table you know that's why if you go into the members table you you know you will have members you don't need to to to to call it member again right so that's why you also don't call your your Logitech um Mouse this is uh my my Logitech mouse mouse or something like that right so m m x Logitech I this is my mouse for example this one and then I don't also add Mouse as well because in the context of tech you always know that it's a mouse right so just to give you some context and then I just create an ID and here's the key thing actually the the key point because normally you say I have ID this is my name of the column and then I Define the data type and then and then some other information which are related to the column itself which can make it like um the primary key or the foreign key or I can add the information that this column should never be null or it should be uh this value by default or whatever that is so you can Define all of that right and then and here I defined the data type ethereal okay and serial is like um an integer but instead of saying um integer I say cereal and the Advent oh my God the advantage I get is that whenever I add a new member into this table I have I don't have to say um insert into my table and name the ID I don't have to say okay now I add the member number 5000 and this is ID 5000 or whatever that is and because serial does it for yourself so it takes the last member and knows what at what number you are at and then just increments it by itself that's why you don't need to use it but I will show you when we insert it into the table okay and yeah so I have ID and then I also set it to primary key because I don't want to have the same ID all over again so I just want to have one two three and two whatever number I have but um this is not the best practice because you need an ID which also helps you um to identify your role later but this is not the topic today I just want to show you this part we can talk about it the next time then I have the name this code ID coding motivation addiction whatever you have and then I just give them all the data type of our chart 255 which means you can um put in a string which is 255 characters long and then I say they are not allowed to be null so they have they need to have some kind of value so that's my grade table command actually okay and of course you don't write it just like that what you do is you go into this K in the into this um what is it PG admin you copy this one and all you just do it here first and oh my God it's so big you just go here and then you try to create this table and then I just execute it and then you see the table I don't know if you can see that but it says crazy the table returned a successful what is it yeah it returned successfully after 91 milliseconds and then I can check here in my tables and refresh it and then I see the members on me so if that works then you copy it and put it into your python but I don't want this so I drop table members so this is how you drop it so it's at drop table so if I refresh it it is not here anymore it shouldn't be here hello yeah now it's gone you see I don't know if you can see that if it's too small or not but yeah hello Adam hello Adam we are just creating um the database so remember we created the connection now we scanned through our data and understood okay what kind of columns do we have in this case ID name oh my God coding motivation and addiction which you can see exactly like here is exactly same right then I go and turn the connection object into a cursor which you have learned in the previous lessons and then I execute it as you can see here and then I lock it again and say okay I executed the query I created the table you just I just said it like like that but the best the better way is to say or to actually make another um query which checks if your table exists so you could say um you want to execute in this query again and then it will say uh this table exists already you know or you just say okay I go here and say cursor execute and say select from members and then if if the table exists then of course it should return zero right then you know okay it has been created that's what you also could do but yeah let's continue we close the connection and commit all of that and then we're done and then the table should have been created so I will just do that right now because you see my grade table doesn't do anything else after that and if you look closely you can see again I have a try accept block again just to catch the error if something happens during my process of creating the table because maybe my internet is gone or the the database is uh I don't know disconnected whatever that is so let me just run this boom and then let's see what my logging is saying started the program okay connecting to processary database okay executed query which is basically this line so I don't like that actually but I just wanted to show you how it could look like so you know something happened right and of course you can say on success you should print that but that's something you can Define for yourself how you want to structure all of that I just want to show you a way how I do that so now I go back to here and you remember I dropped the table before and now it should be there anymore but I refresh and then boom members is back so let me just drop it again I refresh the table hello it's gone again now I execute this again boom then I check it again refresh table boom you got it okay that's that's the first step of your automation because now you just have to run your script and it creates it for you instead of saving this um what is it saving this somewhere and then going back copy paste and run it again so you create it and if somebody breaks it you have to copy it again go into PG admin and then create it again it's a little bit a hassle you know that's why we don't want to do that and here we are to learn how to automate all of your processes so let's continue we created the table successfully and we also said um we execute it that's fine now we want to actually get all the self-def members and remember they all are stored in this Json file which I start in the path data or my folder which is called Data here if you can see that actually and for that I need to create a function which I called hello get self deaf members boom and I know okay in this function I want to read the file I want to read it can you see my finger I want to read it and also use the connection and loop through all the members to see if I actually get all the information and turn them into a dictionary because they are listed as a dictionary and then for each member I want to take all the different Keys which they have like name Discord ID coding motivation addiction and split them up into a new um no I don't want to split them up I just want to have them so I can split them up into my insert query but that's what I'm going to show you now okay so we have to open it and loop through all the data first and to do that I have a new module which is called get data which also is in my utilities folder which is called get self-dest member so if I click here you see I click on control on my keyboard control and then you can see it's it's getting highlighted and it will point to my function where it is written but if you don't have it it of course won't point you to somewhere it will just tell you this function doesn't exist do you want to create it and and then you can click it and then it will create a function okay so now I just click on this and then you see this is already here but let me close it again and close this and close this oh no I leave it open and then I click on here again and then it opens a new file or where it is stored art already and then you see the data is here get self-def members function and what does it say it says read some members from data sales staff members and Rich Jason and inserts them into the database or table whatever that is it's a pretty bad description but for me I know what it does but you should have a little bit more detail like which table and where the data is coming from or something like that or you can write it into read me whatever you want okay so remember this is how you open a file with open the contact manager the path inside this one and remember you also can create a config file where you can save this um path and then change the config file all the time instead of this one and then I have the context manager here and then I just can say open the whole um data of this adjacent file and load it as a dictionary in this case it's a Json and then I just call it data but you can call it whatever you want in this case you can call them self deaf members or whatever that is and then I Loop through all of them and say okay for member in data and then I say log info okay which member did I actually um go through or iterate through and then I should have in my logging processing member and then I will have a long Isaac Ash and so on and then I just told myself okay because I have to do the insert every time I just create a function which is related to postgres so I just put it into the helper um module as well and then I call it here and this function will get the connection object as well and of course each member because if I pass this member to to this function it will have all the information like the name Discord ID coding motivation addiction right so that's why I give it to this function every time I do that and then I check where this function is or I have to write it actually so I just go into this file but I already have written it and then you can see this is the function in helper P SQL dot pi and then I created the same structure like you have seen before so I have a query which I called Q insert member is the Q always stands for query so I know it's query and then this looks like that so if you remember I have to insert into command and then I I want to insert them into this table then I call all the columns which I want to insert into and then of course the values which are structured exactly like this so this is the usual format how you can insert into a database with the insert um command okay and in this case I have one two three four um columns and then of course I need four values as well which are placeholders like the percentage s format style you also can use um dot format um or F string but um I think this is a little bit or has a little bit more overview and also it gives you a little bit more of um security because of um SQL infection it's section inject infection injection but that's a topic related to security of your database but we will get to that like I don't know at the end of when we already have a full ETL of a project okay but so you know it will just give you a little bit of more security because it's harder for the people to to change your um Curry but yeah let's let's just continue and then I create a new cursor base on my connection object again and execute this one and as you can see I execute this query q insert member and then I have a tuple here which consists of all the information of my member which I have when I Loop through each of them so you see I say member name and the first placeholder here here in number one index 0 is name the next one is Discord ID so this is Discord ID this code ID comes into this one and so on coding motivation addiction so you see coding motivation addiction and then I just execute it and committed and then it should work and of course you catch the error if something happens and here you also can say for example I say error in uh during or like during no no not during actually I prefer to use the function name so I say error in error oh my God error in insert now what is it insert what is the function called member member member function then I call it like this and of course I can do an F string here oops like this and then of course I have to go like this F so you could say error and insert member function so you already know exactly where it should be or where it's happening but yeah let's see what will happen so I have all those all the functions now and let's see again if my table is empty select all from members boom and then you see I have nothing but the the table has been created like I uh have executed before so I have ID name Discord ID coding motivation addiction whatever that is and all the data types here and now I'll just run it and hope everything will work so let's see boom then I go here again I see I started I connected I execute this create table if it doesn't exist and then I also say Okay processing member long Isaac Ash albino test and then of course I could say here at the end of my function print oh no not print logging Dot info and then I say end of program but here I don't have it yet you know so I have to import it import login and then I run it again let's see what let me check this here actually first for you so execute it now you see all the files or the members have been inserted and that's how you insert the data from your python file or whatever that is or from an API into your Python and then load it into your database and now if I run it again of course I will have more people so let's see now you see I said end of program and now I check it again now you see I have 10 people suddenly because the only identifier I don't know if you can see that actually can you can you see that guys I think it's too too small I don't even know how to change this one data output no you can't see it yeah let me see Crow tool editor man I don't know how to change this wait it is in the language oh man I don't know ah I have to make it like this okay I think you can see that now so I have now inserted all the data and the issue was that it always tells you okay the PK is here and this is also unique right that's why it it puts all the data into the database because it says all the IDS are different that's why I don't care what name and whatever that is I have I'll just insert them into that one but if I say that my primary key for example is my name then I can't insert long again because it already exists and you all you always can just have one primary key for like um not like not one but you have one column which is a primary key and each row is unique and that's why that would avoid this issue but in this case then if another person is called long but has another Discord ID then you can't do that then it will just say yeah long already exists in the database so I can't add you but it's not a long but he doesn't know that that's why you could you could try and set your Discord ID as your primary key and then it will say okay you can't have the same ID anyway right it's Unique like even if you take Blazer's name Blazer zero zero zero one or whatever that is or zero uh 1000 I don't know it could be any number and if I have another blazer with the same number it will never be inserted and that's how you can avoid duplicates in your table okay and yeah but that's basically it and our um script has been finished and that's how I would do that if I were you guys so I um left a couple of um elements out of this whole um example because I don't want to overload you with too much information and the key Point here actually is today the the logger as well and to to have your whole um code base like modulize and cleaned right that's why if you look at my code now everybody who opens it they will just see this one I even can remove this one okay it's gone and then they know okay I need the logger I do a connection I create a table with a connection I get the members with the connection done but imagine somebody goes into my code and sees all of this and he's like man what is this okay I have to in a blogger um connection test oh what is this why do we have to test and then we have a normal connection okay create table then oh my God what is this and so on so it's a little bit more time consuming to get over that so you have a wall of text which you don't want somebody to face when they open your code right because your code also is a reflection of your own skills of your own um proactivity and yeah it's just a reflection of you or if you are a good or a great developer slash programmer okay so and that's it for today actually I hope on that gave you some more insights how you can do all of that and if you have any questions just let me know in the comments or in the Discord server and if you haven't joined it yet just go into the description so you check out um our server which is here as you can see no it's not here because um I removed it but this is basically our server where we also have our weekly class and yeah guys I hope you learned something today and next week we will continue and work with different file formats so you can upgrade your skills working with different file types and data types and yeah don't forget to like subscribe comment whatever you want and see you in the next episode Peace

Channel: long.integer

Views: 9,430

Keywords: coding, programming, codingbootcamp, pythonprogramming, learnhowtocode, coding for beginners, programming fundamentals, how to start programming for beginners, python vs rust, data science beginner projects python, programming for beginners 2022, data engineer vs data scientist vs machine learning engineer, top programming languages 2023, learn programming africa, python interview questions 2022, how to learn python, learn coding java installation

Id: HVnzn9TOwlA

Length: 56min 21sec (3381 seconds)

Published: Wed Oct 19 2022