Data Science - PostgreSQL Database using Python Programming

[Music] let's talk about uh what are the uh technical things we can do to connect with the databases so everything is practical today so we are not going to see anything theoretical so first thing is first we are going to create one python file here so say that the python databases database so basically you are using the pg that's called pg pg stands for postgres dot py you can give any name i'm just giving the a reference name okay so so to begin with this uh python databases postgres we have to first to import some drivers as i told you yesterday python is a different programming language uh sql is a different programming language so there should be some bridge some connector should be there so that's where like we are going to use the uh one of these uh third party tool is called the psycopg2 i'm going to install it but you can type here psv psycopg2 you can just go and install it this one okay just copy this tip install psycopg2 go into this terminal okay so installing this one so to install this one let me deactivate my current virtual environment okay and here i'm gonna say this the pip install uh psycopg2 but basically i'm using the two versions of python in my local computer so that's the reason i'm actually going through the pip3 okay so by default is uh pointing to the python 2 so i don't want to be installed with python 2 so i'm going to enter here so basically it's going to collect the all the information like pip3 install psycopg2 and on so it may take little time so you can install it okay so you may see some errors and it's very common right and also there is a supported file will be there so what is the supported file psy copg2 iphone binary so let me type that so this is my required when you are working in your windows system and all so psycopg2 iphone binary okay this is a latest version you can download or copy this go to this and download okay so sometimes it may give some issues in the windows system but don't worry guys it's already satisfied so i already installed so if your issues with the psycopg2 then try to install the binary file it supports if you go and read here which take it talks about the same thing so it is also like database adapter only but it depends on which operating system you use but both are same only ps5 cop22 into binary is a supported file maybe some operating system won't support either you can go with the ps5co pg2 or iphone binary okay declare this text let it be now i'm going to write a programming to connect with this database connection equal to you can write your programming so spring is wrong connection is equal to psycopg2 dot connect remember guys so if you if you understand this code and it is same for mysql it is same for oracle it is same for any sql database this connect which is actually yesterday i spoke about python bb api 2 right so basically yesterday i spoke this one right the python db api 2. so basically what is this this is a separate a application programming interface which is given by the python so whatever the method they are telling so same methods we have to use it here so i am going to connect to the postgres database by using this protocol and this is nothing but psycopg2 adapter or in other terms we can say connector so in other terms we can say it is a driver okay different terminologies so let's begin with this first one is the host host is like which which server you are connecting to so basically we are coming to the connecting to the local host okay sometimes you may connect to the remote server sometimes you may connect to the any dedicated server of course hostess is not meant for dedicated servers but you can write it okay it could be possible okay we can write it here and also we can say that databases here database equal to and we can mention the name here what is the database you are creating it i will write it in shortly and the username and this is also we are going to write it and password and equal to can write it here and the port number which is important otherwise by default it will take the default port number since there is an issue with my system default port number is already occupied by with different service so i have to mention this port number in my local so then you can ask me the question what are all these things raja these are all like information we are providing to the adapter saying that this is what the database i want to connect with this okay but if you can look at here these names okay maybe this building is wrong so you can see a host database user password port these are all fixed variables or we can say fixed parameter names are argument names so these are basically default arguments guys these are all default arguments basically we need to pass here you need to pass exactly the same names hostname you should not change it to the host one it won't work so you have to give this left hand side of the equal to the same names will be always fixed host database user password port number and typically this is not required and but since there is a i'm using the different ports i need to specify it okay so i'm using the 5 4 3 3 and the password is the admin okay and the user is okay i think i need to go and check this one okay it's a dios user i will go back to here and use the ddos user and the past database is also kind of right okay what is the database boondocks okay i will give you that same name boondocks okay remember uh i'm also going to show something here here boondocks b is uppercase later but i am providing in the lowercase okay we'll see that and username is same ds user password is same port number is same all is fine now i want to cross i want to check first of all connection is established or not so simple guys there are multiple ways we can check this is one of the way i am telling you to check this one and use this connection object okay if i go and print this connection object if it is printing some object id okay or else some address location that means you have successfully created the connection between the your python programming and the database let me go and print this we'll see that okay it's saying that boondocks does not exist right because i've given the lowercase letter but if you look at here the boondocks is in the uppercase letter b is in uppercase so what i'm going to do right i'm going to change this one to the b2 smaller to the up like uh uppercase letter i'm going to execute this code so the connection is established right it's giving the object the connection object at some memory location there is some memory location here that memory location i'm seeing for example if i give the wrong port number mine is actually 543 but i am giving here five four three two now i'm going to execute this code it's saying that hey man you are using the 5432 but unfortunately that is not available here to connect with this so every information which is very clear guys who is given this information which is given by the this guy psycho pg2 okay this adapter is looking at this port numbers passwords usernames database and everything it's trying to connect to that database but the port number is wrong it is throwing an error okay anything even tomorrow mostly people will forget about the password i'm giving the password is one two three now i'm going to execute this code you also see this a password authentication is failed why i'm sure why i'm showing all these types of errors tomorrow when you are writing this up this kind of code in your real-time applications you may see sometimes this kind of errors in the log table are like log a file okay so the times you understand this password authentication fail means password is wrong and port number is wrong that means it is unable to connect to that port number since it is a local computer you are easily doing doing it but when you are connecting to the remote servers that's that's where the problem is and this localhost you know right we can also write 127.00.0.1 even if i go and execute this code okay the password is still not changed okay executing this code connection is established so localhost is equal to 127.0.0.1 i'm able to connect to the this database okay so moving to the next one okay connection is established that's that should be fine but what i have to do it here i need to create a table inside this okay have to create a table inside this database so how to create this so first before going to create i need to prepare with the query so query is like create table some table name and inside that there is some columns we have to create so let's take some customer table or like student table our employee table person table any table you take okay any table we can take but i am going with the customer table let's see this how it's going to be now i'm going to use the object name is called sql query so sql underscore query and writing a query here so when you're going to write your query this is going to be the multiline code so if there is a multi-line code definitely i will go with the triple character i mean triple quotes string so what is this it's a triple quote string i'm going to write a query here because i'm writing a very clear query so create okay table so create table what is the table name customer and inside this you can create you can put some column names here for example id custom some id okay that we can say customer id row id whatever it is and i will make it one primary key here so primary key and not null so this is what i'm writing a simple i am writing an update case letters just to you can write in lower cases also that should be fine so customer will have the name so name i'm writing in the text and also not null and you can write and if you go to the and also customer will have the age edges if you want to take it you can take it up otherwise it is not required so int it is an integer always and it should not be null so it should not be blank basically name should not be blank a should not be blank i'm just giving this and also we can take address okay so address we can put it as a character here and the characters you can go with the hundred characters or like 50 characters so address it cannot be more than that we can write okay and also we can put something like salary here so okay maybe how much salary this customer has to approve the bank our loan okay that we can put it as here are like so better we didn't keep it as a salary here so how much salary is there to approve the bank loan amount or like i will i will put it is the loan amount better it makes sense basically okay i'm talking about the bank customer here so loan amount this should be you can write under square or like you can keep but generally we will separate by the underscores when you are writing the column names okay and this should be real because uh loan amount it sometimes is the float value so we have to say in the sql language especially in the postgres we are going to use the real okay so real care in text but when when you go when you are writing the same table in the oracle database so what you are going to write so instead of care we are going to use the work here too and we are going to use the say float so different database may be different different in the different databases that we need to understand only for first time because databases data types are different in the multiple databases okay this is a simple sql query where we are creating a table now i want to execute this query through the python programming okay that means i want to create this table in the database using the python programming let's see this okay we have a connection object i don't want to print this every time so i'm just leaving it here so we can see this later and now what i'm going to do right i want to execute this query how to execute this query guys anyone guess we need a connection object by using the connection object we need some pointer okay we are establishing the connection between the database and python but in between some uh postman is required that postman is nothing but here cursor so i will take the connection so connection is the object which we created dot cursor so and this is also actually this cursor we have to use every database whatever you use it is called db api 2.0 the same so connection is one function this connect is one function cursor is another function two functions we are dealing with these two functions are common okay in this cuts we are not going to do anything but we are creating object name for this uh we can say i will put it as a pointer to make it pointer or like postman or something we can say okay so this pointer what we'll do right this pointer dot i want to execute a query okay so if you want to execute a query we need to pass this uh the entire table itself okay enter table query so what i'm going to do right execute and say inside this we can pass this sql query okay so what i'm doing it right we made a connection first after the connection we have the pointer this pointer is nothing but okay i will take this query i will in the sense this this pointer will take this query and directly run into the database environment in the sense postgres database through this connection okay this postman uh use the service of this connection maybe this postman is working for uh some post office okay so that post office information he will take and he will go and execute that information inside the database okay it will deliver the address exactly okay where the location is so now pointer dot execute this sql query i want to execute this after this let me put this print statement saying this table is created so table is created i'll put it here okay now if i go and execute this before that let me show you this pj admin there is no any database any tables are created here because we freshly created this table here yeah you can look at here there is no any table so we just go and see now whether it is going to be created or not think there is no any spelling mistakes under let's see this first of all i'm executing this code um it's saying that katsura has no attribute call i think spelling is wrong okay yeah execute spelling is wrong okay i think this is correct now let me go and clear this text execute again stable is created successfully but i want to cross check whether it is created in the database or not are simply giving the message so i want to refresh the schema you just click on this refresh and go to the tables and still know and we'll try to see this where it is created as if it is not here and i will go to the tables it's not created here create table yeah correct table is not created can anybody guess anybody database experts here why table is not created any white table is not created any guess random guys we are not mentioned i think we are not committed exactly who said that great yeah we are not committed the transaction okay that is very important into the sql okay you think like this code is correct yes you are 100 correct you are not going to do any changes here once transaction is created that means what is the transaction is here you are creating a table creating a table deleting a table or inserting the data deleting the data or updating the data these are all transactions uh as for the database right when you are doing some transactions in your database you should give the message uh you have to tell that commit say for example if you are working on these tools like pg admin are like sql server like management studio and there we don't need any commit statement right but automatically that studio will take care but something you are writing in the python programming or java programming we have to commit the transaction so once table is created so before that we have to create connection dot commit remember guys this is one of the famous interview question where we are writing this commit we are writing this commit on the connection not on the cursor okay so we are committing the entire this this connection okay this connection dot commit this is what we are doing it now what i'm going to do right i'm going to create and i'm going to execute this code now i will go cross check again whether it is created or not because i just given the commit transaction so the table is created okay so that that is very important the we have to do the commit for every transaction uh whatever it is you are doing the ddl script or dmsk this is a ddl ddl means data definition language you are creating a table uh that something is called the ddl you are updating the table uh like you are inserting the data or you are deleting the data it's all about dml okay data manipulation language so that's where like these other concepts will be there now we are done with this but i find by mistakenly you are going to execute this code again you will get an exception is called hey man there is already table is created you are trying to create the table again so duplicate table do this exception is provided by the psycopg2 it is a duplicate exception it should not be so in that case is what we are going to do right we are going to write not this we are going to write this query okay you are executing the sql query that sql query can make it into the try block so you can put it as a try block here and uh you can make the accept and colon and so here we can write whatever the exception it is so i'm just writing this commit here okay and uh in the accept block we can write so there is a db exceptions also there but we don't touch this for example let me go with the exception first i will talk a little later about the different types of exceptions i want to print these exceptions generally we don't print the exceptions i am going with the actual real-time code only we need to import logging and so this logging we have to use it here since this is an error we have to use the locking dot error and you have to give the message for error yes in the sense uh something something went wrong so table is created already our table is duplicated or something we can write some meaningful one that's up to you and if you want to print this exception guys so we can use the as e and you can print this ee stands for exception here it is alias name so that's what we are seeing it here so table is created but one more important thing there is a finally block we have to write most of the people this missed out but if you are good programmer or good developer we have to always close the connection okay so collection dot close so we need to connection closed so because you open the connection you have to close the connection whether the exception rise or not rise it doesn't matter but the connection is opened you have to close the connection once you are done okay so i will use this print statement instead of print statement i will use the logging dot and we can give the message like your info because it's just like information only we are giving that table is created okay so that's where like we can create tables and you can check the access this is actual code guys okay this is actual code and to rename it it is actually table table query so uh especially if you want to say exact name table and a square and we need to give the proper customizer name because these are all makes sense uh when you're working on the big projects it is one table two tables is okay but when you are working on the large applications we need to provide the proper information it is a table customer query and table customer query we are executing it and we are seeing it so now if i go and execute this code i am getting an exception here table this is this kind of an exception we got now okay so table is created uh the table is created is the message right it's a message we are seeing it here so table is created one second whatever it went here or table commit and okay we are executing the table here one second there is something went wrong this is not expected okay so exception as he logging error table is created yeah fine since it is a logging right that's the reason we are getting this message but i will maybe mostly tomorrow session or something i will talk about logging which is very important going forward we use a lot in upcoming codes and all because logging is very important when you have some time also you check that okay so any questions here anybody have any questions okay i'm going to give one small tip when you're using on the ps4 yeah go ahead please uh raja can we use save pointer no here no actually yeah it says comment see here connection we're committing right so we can use the save point right i mean yeah it's a point is not there here basically in this api okay so that's a reason now we need to study these are ap api 2.0 functions connect is api i mean python is mentioned this one python is saying that you use any database adapter whether it is a third party whatever it is but you have to use these functions connect is one function cursor is one function another one is the close another one is the commit these are all but save point is not rollback is there you can roll back the transaction if you don't want to be inside that by mistaking you insert the data but you don't want to be to be permanently stored commit is nothing but you are storing the data persistently so that's where this commit come into the picture so we don't really need it here okay and uh so i'm just giving one small information here it is not related to the database if you're using the vs code guys and go to the settings you see i don't know how many of you know it if you want to save this uh if you want to make this code is the formatting you don't need to worry about it even for example if i write so if i write you can see here equal to after that i am not giving the space if i do the ctrl s automatically space will come this is nothing but formatting the your code whether you are writing the java python whatever it is if you want to make formatting by after clicking the control s it will take care so you can go to this uh vs code settings and you can type your format on save or something is there okay okay you can just click on this so i'm just giving a suggestion here if you are working on any large code so you don't need to avoid even html formatting not like this formatting this vs code is having the so many features guys you just click on this which will give the more features to you okay try this option which works very well okay so moving on to the code just i want to share that information and moving to this this is we are done i want to create uh data i want to insert the data so this query is not required now because since we already done this step so i'm going to do one small thing here i will say that insert insert data so insert data dot py so in this what i will do right already i created that this all the steps i will use the same steps here because what i'm going to do right um i'm making this connection separately in the different file and instead of this table customer query i am going to insert the i'm going to write insert the query okay so what i'm going to do right so it is a insert um insert under square sql query and equal to and we can write a query here so basically it is again triple quotes you can write in even in the double quotes also but okay that's not an issue i'm writing now insert into what is that what is the command guys i want to insert the data anybody knows here i want to insert a data into the database table yeah go ahead and put the parentheses and put all the columns and after that values and all the values that we want to put exactly all right so i'm writing in small letters okay let's see this address and the last one we are given the uh what is the income or like what is the loan amount right okay let's go to this insert and use the loan amount here the values of okay so in these values of we need to provide the data so what values we have to provide here id is okay one and what else is there guys i want to bring down this one to the b okay so here i will put this r else will go with this okay the values of next id is this one and the name is for example i'll put john and the age is example 37 and lived in new york and will go for the loan amount is for example 10 million dollars or something like okay so 1000 million or something whatever it is you assume whatever it is million dollars or billion dollars it's up to you okay this is the query insert sql query now what i'm going to do right guys i want to execute this query okay so what is the execution same code nothing is different we have to make a connection dot cursor and same query i'm writing it here why i'm writing the same code again and again i will tell you i'm going with the flow so here i'm using the point of sync pointer is equal to connection dot cursor and the pointer dot execute we are executing the sql query so let's try to install it execute this one okay now i want to so this query is going to be executed and after this we need to do the commit transaction definitely right so connection dot commit and once you then commit and we can close the uh connection dot close okay i'm not i'm not writing here again um same trying to try and accept block but you have to write it here there is no escape a good programming is always written in the exceptions it's like what we have done in the previous code okay and similar fashion we have to write here but i am not writing here but just you can understand okay i want to execute this code it's simple i want to insert the record into the new create the table which we created okay so just to give one small print statement here just to ensure that there is no issues okay so record is created so yeah execute this code record is created but i want to cross check whether it is created or not and refresh and uh so okay you can this is the table right so when there is a table you can click on the view data and we all rows rs you can directly write a query so anything is fine you can see i just now created the data like john h that is 37 addresses new york and the loan amount is something 1000.45 so basically what i'm gonna say here then you are going to create any sql query okay i mean you are inserting the data or like doing anything you have to pass that query to this execute command it is the execute function on the pointer so it is basically a cursor function so in the cursor we are actually sending this information okay so insert sql query are like anything so now i'm changing this data guys so previously this guy is john i'm changing to this one is the robot okay and so is in the melbourne so i'm just giving the mb and this is 45 and this one is the of 340 or something like this okay now again i'm going to insert the record so it's absolutely fine if so you need to go and check again we all rows already query is there you just go to here there is a command here and this command if you click here and automatically this will execute this select star from the customer order by id so public is the schema here we can create multiple schemas for example dev schema test schema production schema and pre-production schema like that we can create multiple schemas it depends on the the project environment but i can you can see here the default one is the public but if you are working on the sql server dbo database owner will be there it depends on that okay but in postgres by default we'll have the public schema we can create multiple schemas okay so select star from the public dot customer order by id even if i am not giving this one no problem okay even if you are not given this enterprise okay but if you want to execute the same code normally you can go to this sql and you can write it here it will also execute or else you can select here uh this schema and you can you can click on this query tool you can also go and write the query here okay so for example i want to see two ways we can do it if you are not good at writing the queries uh you can go to the click on this customer and click on the view details view data now if you are good at writing the queries and you can use the query tool okay that how we can use the query tool either you can go on the table wise or we can go on the schema wise okay schema wise under it is there so for example i'm writing your customer the customer is a table so by default it will take the public only i'm executing now you can see same thing so either you can go with the view all rows i mean user interface are writing the query both the ways okay now moving to this code what you already have in the one two john robot and 3745 this data is there but when i'm creating this table i said id is the primary key right so what i'm going to do right now this insert data file i'm going to change little bit i will keep this id is the two but i will change this name okay this name i will change to the different names for example pant and here it is the uh 55 and here i can say it's a delete and we can say this one is the 7890 if i go and execute this code do you think is going to be work no right it will throw an error so look at the error instead of looking at the insert query look at the error because mostly we debug the errors right it is giving the clear information guys hey there is a unique violation duplicate key value while it's unique constrained right so basically the customer key it's a primary key a key name is is actually the id here primary key but there is is equal to two which are passing clearly it is showing that id 2 is already exist but because you can see in the database id 2 is there for the robot with the age 45 and all but you are trying to create with the different person but it is not able to take enough because of id but when you change this one id to three it will work okay so that is the significance of primary key so we should not allow the duplicates with the same id because if you take the employee id customer id or student id these ids are always like unique the primary key is equal to okay uniquely unique key plus not null always remember this small tip primary key is equal to unique key it's a unique key and uh i can say plus and not null it should not be null and it should be unique that is always primary key in the database okay you should not it should not send the null data and you should not use the any duplicate value basically that's where the primary comes now i inserted the three rows here and you can see i have let me insert one more row and because we are going to do some operations here okay because we need some data so that's why so i'm going to use the another data here say that for example trump and his age is for example 78 and washington dc and we can give any another different name show so 87654 i'm giving some value okay now i'm going to execute this code it's fine row is created i'll cross-check this one after executing this code query i got the four records yeah new record is getting added okay fine so this is insert data guys okay i will i will come up with this end of this insert delete and all i will give you some information uh because we don't write like this in the real time projects and i will tell you that how we are going to write because every time we can't go and create a connection for insertion creating the table deleting the data it's all really you are making the redundancy code so we should not write like that okay but i will tell you how to do it for time being just understand we need a connection we need a query and after that by using the cursor we are executing the each query and after that we are committing the transaction okay and most importantly people will neglect line number 25 the connection that close which is mandatory otherwise you will you will face a lot of issues in the real time production or like some any environment if you are executing it once connections are opened okay now i'm going to create one more python file delete records can i use the delete records how to delete the records first of all let's focus on update records then we will go with the delete records because we need some data okay so update okay update reports we will quickly finish this one because only so i'm going to copy this entire code guys because don't need off writing the same code and this query is going to be changed okay and this is not it's set query this is the update query update sql query this update sql query only changes okay so i'm just writing it here and what is the query guys can you please tell me so for example let me tell you the scenario i want to update the uh robot age 45 to 65 i want to update robot h from 45 to 65 so how we are going to do it here okay assume that maybe in your case maybe multiple robots are there it can be duplicate right update customer yeah update yeah update customers set it in okay name is okay but in the scenario is okay but in generally not preferred to use because we have to use the primary key yeah name is also works because here robot is only one guy but if we have the duplicate robots i mean multiple uh same entry is there with a different robot and lasting first name is something like this so better always go for the id id in the sense primary key so i will use the primary key is the 2 right correct robot is 2. let's go on now execute this code we are done with the query what is the query update table name set column name is equal to new value where the primary is equal to primary key value so basically this is the query i am executing this code you can see record is updated i want to cross check this one in the database but it is created or not [Music] yes it's created 65 65 is getting added right so that's where like we can update the records so you can update the multiple records and down okay so this is how it works now i want to delete before going to delete more important thing guys i want to work on select deleted i will do at last it's not a big deal okay so next important thing is retrieving this is not the thing which you think like it is easy but we have to understand there are three different functions are there in this um python to get the data so retrieving the data are like selecting the data you can say very simple understanding connecting the data okay dot p1 so what i'm gonna do right i will use the same connections here okay there is no any change so we will use the same connections we go to this selecting the data but here code will change functions will change idea little idea different here okay this is not a table query and we don't need the table query here it's a sql select query so i can say it's disable select query table select query okay our record select query it's not real table because records from the table basically whatever it is so this is the query which we are going to use it here at execute function okay what is the query guys you can give the select star from a into table name what is the table name is the customer okay so right now there is no anywhere conditions here where condition is nothing but if you want only like new york based customers are like you want to is less than 55 people those are the different queries we have it but i am not going in that much but we are writing very simple query here because we are selecting the all the customer information so when you're going to execute this code if i go and say that table is data is retrieving or something it won't work guys okay so we have to change this code little bit so this is what the pointer i am going to use the pointer is equal to what i am going to say that this pointer is a dot cursor this cursor is working so i will say this is the point that already i've written right here i'm writing pointer is there point dot execute pointer dot execute so in this execute function we are writing this table select query but you'll see this results and i'm putting it is as a results results and if you go and print this we don't use the direct link because you are not inserting you are not doing anything on the database you are getting the information from the database to your python object it's a little different guys so for whatever actions you performed that is on database but this is something you are doing getting the data into your python programming so what we can do now so basically we are going to do here something little different okay so what we can do now anybody guess so let me print this result then you will understand okay i'm printing the results here execute this code and none is trending then how i'm executing this query i got the nun results but there is lot of data is there so how to work on it so here in this case so we have to deal with the different manner let me tell you you'll understand this result sets this is nothing but results dot we can use the function okay but this results is not having anything so we should not use the result so we have to use the pointer dot this is this information is there on the pointer fetch all all the rows i want okay and this this all the rows right so i will put it as a rows rows is equal to pointer dot fetch all if i go and print rows you will come to know the actual thing what is happening here if i go and print this i got all the information from the database so basically this data is like this so so instead of looking at the output can you please spend some time here line number 19 okay what we are doing at line number 19 guys we're actually doing right so we are executing the query we are executing the query and by using the pointer i am fetching the data now you can ask me what we are doing with these results are doing anything with this results object okay are doing anything results with this object so in this case we don't want this results object okay just you can leave it as like this okay so pointer is equal to connection dot cursor and pointer dot execute and once you execute that pointer is holding the data the whole the data it's whether the four regards 40 records 40 lakhs records doesn't matter it will hold by this point and it is giving the whole data into the list format this is a list it is a python list hey man i don't want to print in the python list can you please print one by one it's very simple rows is a list for each row and a square and in rows we can say that and print off and each row can you say like this you're printing the each row now it's nothing but each item from the list it's a python code okay executing now it's we got the john 37 you're conduct man i don't want to print the like this tuple each item is a tuple here i understand but i don't want to print i want to print names of the persons so then case what we can do guys can i use the one index this is john is the zeroth index is one and first index is john and a second index is 37 like this because um tuple is actually based on the index placed our data type right so if i go and print this execute this code i'm getting the output is john pant trump and robot these are the names of the customer names from the table right you can go and execute this code and you can see so if you want no no i don't want i don't i i want even their loan amount okay it's very simple so can i use like this i can go here and each row and we can use the last row is that one right so you can give minus one so can i use a minus one guys if we're going to execute this minus one is nothing but what it's a lost item in the given sequence data type whether it is a tuple now has said sorry tuple are a list even in the string right so others you can give length minus 1 when you think is fine so this is where i am seeing the john john and you can mention that uh it is like a loan amount you say that loan amount okay you are just writing you are actually bringing this data from the database and you are writing out so any questions here anybody have any questions any questions okay so fine what is this fetch all first we need to focus on this fetch all is to fetch all the records no raj i don't want all the records i want only few records okay now there is a fetch menu and fetch one fetch one record is also there let's focus on first fetch one and if i'm going to execute this code what will happen guys you'll get an exception okay okay let's see this spelling is wrong i'm executing code again okay basically it is not subscriptable means it is not giving the list guys so you can keep this code into the comment section because it is not required so what i am going to do right i will use the pin statement and rows and execute this code now i am executing this code basically it is one row only not even rows just like one record you are fetching it basically you are you are fetching the first record from the table right the first record from the table okay i don't want first record i want some records from the table then in case again i'm just going to keep this one of which one is only one record that is the first record and fetch many is there so fetch many means you are pulling the like how many records you want you want two records from the given records like uh then in case you can go like this you can give two records means two records will come if you give one record one record we you can fetch uh if you're giving the three credit cards you can get three credit cards okay so in the given data how much samples you want it's nothing but many is nothing but samples so what is all this drama what is what is this what is the fetch menu and where these are all coming up that's what i told you in the previous session guys the fetch one which is there on the uh so i'm going to the python i don't want any other website other than that python.org so this is where like the database api okay so whether you can use the sqlite 3 anything my actual database api specification is mentioned these these these are the functions basically so for example if i go and search this fetch one and you can see here so page one basically this is the function right h1 fetch the next row of the query result set and fetch many is there you need to mention the size how much size you need and fetch all is like getting the all the rows from the query result right this is where like we have so these are the functions these are the functions i've defined it maybe you think like some may be some people may ask you the question can i call the procedures through in the python programming you can say answer is yes we have something called call proc it is the method is optional since it is not database provided stored processors so we can we can call the storage procedures by using this call prop for uh method basically okay when you are dealing with the procedures in your database especially in the oracle under right the close is actually uses the internal dell command to close the connection okay we have to you close the connection like that there are so many things guys so you you may see something called execute mini here i'm executing one query for example in the previous code what i did right not mentioned this one if you go to the insert data here i insert the one one row okay but if you want to insert the multiple rows you can create a sql query one query two query three query portfolio i can you can give those queries in this execute command separate by the comma then all the queries will be executed at a time but i mean to say that if you want to insert the 13 records or 40 records at a time then you create all these insert queries for the 40 records and give those objects into separate by the comma then code will be executed okay this is how it will be there and basically that's how we use this uh posters so any any questions guys here anybody have any questions anyone have any questions here what if we uh don't close the uh connections means any serious i mean any serious issue will happen yeah so in your local competitor it won't happen any issue uh you don't see it also but it is a it's going to be very serious issue when are actually not closing the connection in the production servers so let me tell you one scenario i think already explained to you i mean in previous sessions as in that you open the browser and you open the one website there you are logged in and you are doing something like your inserting are like updating your profile whatever you are doing it but after that you are you closed your laptop but connection is not closed means the developer who developed that application so he or she is not mention the connection close so what will happen right the connection will be still there again one more person is open that website and again he did something and he went off log off also fine okay log off is okay but connection is not closed like that multiple people are logged in the website the connection is not closed and there is a limit in the server and when there is it reaches to the maximum connection pool objects then you will get an exception called server 500 error server not found error or something finite error will get okay so 4.4 is like file not phone or like page not phone but this 500 is something which is related to the server it's okay we can understand internal server error okay the error name is the internal server error because of the connection pool is maximum maybe 30 connections are there 40 connections are there yes it leads to that one yes i'm going to show you this one how it's going to be uh we are going to use the hirokui app to deploy our ai models then definitely you are going to see i will ask you guys to everybody to open that website at a time and try to do it i'll i will make the pool is like five but 10 people log in and don't uh log off just close the browser and i will show you that how it's going to be okay you'll get an experience on this it is a matter like your site will go down so basically it is a big matter so site should not go down right so again you kill the you kill the connections then your site will go and up or restart the server it will go up there are two ways we can do it okay so any other questions okay connection dot cursor please explain this again i don't understand this okay it's very it's very simple uh okay let me tell you so you have the python these guys speak some language that's called python only the another guy is the postgres this guy speaks what which language sql this guy is separate right so in between we need uh basically a connection so connection is nothing but checking the all usernames passwords and host name and everything okay but to do some action on this post to do some action on this postgres we need someone to do action okay see you have a ticket to travel to some other place but without airplane you cannot travel right and something like this i'm just correlating it so we need some cursor here this cursor is nothing but postman so you understand this connection is like a bridge so connection is nothing but a bridge between the postgres and python and but you're actually using the cursor okay cursor is airplane it's taking the data from the python 2 postgres what data it is going to take in from the python in the python you are writing this sql query the sql query will take this cursor when the connection is established connection is bridge when there is a bridge then cursor can travel and if there is no bridge cursor will not travel so cursor will take the information from the python that is the sql query will execute on the database because sql query means what this is sql query this queries works only on the database okay only on the database not in the python but you are using the python code to give the instructions to execute this code basically okay so cursor is a a postman is taking the data from the python executing the query on the postgres when when there is a bridge if the bridge is not connection is failed cursor will not move i hope you got with this example if not i will explain in different way okay fine so next query is the delete the data so i want to delete the one record or two records so can you please guess okay so i'm going to use the delete record so dot poi so you have to remember in this today's session is main thing is fetch all fetch mini and fetch one so this is something you are getting the data into the your database so for example you can see this data once you get you can also send this data to the files see here i am using the print statement rls is instead of this you can use the uh the files okay you open the file here for example you can say file is equal to open the file and say that it is a data.txt okay and we can open the file into the read more okay because why i'm talking about these concepts we spoken right in the previous sessions definitely so file is equal to opendata.txt and w mode and what up what are we going to do right views this file rather than using the print statement just keeping this print statement here use this file dot and you can use the right so in the right function you can know in the right so for example this each row and all is there right this i'm going to take it into the one string unlike you can pass percussion for example let me send this data to the each row you can use the row here and let me do it first then we can see whether it works or not do you think it's going to work right so file dot write and i am passing each row i am going to execute this code write argument must be typical but the file is created here resulted.txt but there is no data do you think what happened here basically we need to pass the string okay but you are passing the tuple so how we can do it so you can see here select the data file dot write and each row this is a data.txt but this file is already created i will use the w plus mode if you want like w is enough again it will be overwritten so how to send this data guys i have the row each row i am sending this each row but it is saying that right argument must be a string not tuple okay let me do it this way uh i will take one one value now it is a string you got it i am going to execute this code i'm not getting any exception i'll go and check in the data.txt yes john trump okay all this data is there but if you want to make this data but before sending to the file you need to format that one that is up to you okay this is uh not formatting well but you need to format it okay so that's something you need to take it before writing this file use something called string formatting and you can send the data even if you have the data in the tuple you can convert the each data into the string and we can send it to basically file.write function which takes the string data not the tuple okay so we can send the line by line also here okay we can take for example let's see this whether it works or not go into this no it's not working because of i understand that because the whole thing is considered as one row so that's why but you can write some code here you can try this this is one of the assignment to you if you can try it try to send the database data into the different file okay then we'll see this how it goes the last one is delete the records so how to delete the record so in the insert data whole thing i am going to copy paste keep it here i will change this insert sql query to the delete query so what is the delete query anybody guess i'm using the delete query delete sql queries so tell me guys how to write this query delete from customer where id is equal to delete table delete from customer important these okay for example okay you want to delete it okay i will delete this three okay so i is equal to three okay this is the delete sql query so here also i'm going to keep the same thing connection record is deleted you can also mention how many requests deleted you have to take the count everything can be possible here so execute this go and execute this query the record is deleted and i want to cross-check this one that's gone row id three uh which is deleted now so we can delete so that's where it works guys you can delete it so basic whole thing is here these commands everything right here i don't know all these comments but you are saying but you should know it because we are to the data science at least the basic queries you should know and it is not going because these queries are almost all same in all the databases okay so maybe i'm not talking much here in depth like i'm not talking about joins i'm not talking about triggers i'm not talking about procedures i'm not talking about functions there are many things are there in the sql but when it comes to the joints there is the inner join outer join left outer join right and that is also there but that is where we are dealing with the multiple databases but in general we don't use the plain python code either we go for the jungle or we go for the flask or something we don't write plain database queries or plain or database code in the real-time projects okay just we use a some mvc framework something like which deals with the database and it deals with the the presentation layer are like model layer uh database model layer so basically we use the a framework that where is a different scenario is maybe there so i'm trying to take uh one jungle session it's going to be two to three hours of life and uh where we are going to show you the complete one project guys django so three hours live i'm going i don't know i mean can stay with me for the project but i will take i'm going to explain some project a new project either i will take the chat box how to prepare not the chat bot a chat box so rls like we can create something like supply change management or we can use the something new project i am going to do presentation may be in this week not in our hours in different hours i am planning to do it uh maybe you will get some idea about the jungle okay and you will get but how many of you people will stay with me in that live coding i don't know if you want to do it parallelly with me also do it but i will go from the scratch to the next level uh another three to four hours it may go for five hours also i don't know if it is one person is also there with me no problem but i i love jungle a lot okay i will write a one small project okay that we can plan in the weekend uh especially on the some time okay that we can discuss after this sometime okay we can discuss at the end of the session okay so we have the delete record insert data yeah go ahead please yeah you told him to run framework can you please tell me what's the framework which one which regarding which framework which one you are talking about just now you spelled one framework we are using something jungle okay so people used to say it is a django but actually we need to pronounce it as a jungle d is silent so people say it is a d jungle but don't say d jango it's a jungle okay okay so it's very important i will i will discuss with you guys i will go with the three four five projects also if you want flask also we are going to do one project because flask and django is going to use to display the our projects okay so we need front and right someone's to upload the images and do the and all okay so i am telling you that what are the sessions is going to be interesting an entire journey on neural networks okay so the deep learning and neural networks is going to be very good sessions because i will tell you very lament language okay you understand very easily uh the concepts which i have planned but to understand those things very much because that time by the time we need to study with these databases under it is good to know okay maybe in the march we can start neural networks so another one enough month we'll go with different tools now okay fine that is okay that's that's we can plan it later yeah in weekend we'll work for django yeah definitely um we can do easily building the applications i don't know do you guys agree or like do you think okay i'm going in out of the topic people sign it is it getting okay 11 15 maybe that is the reason why nobody is not talking no it will be great means uh today's session was uh really awesome and uh working on the weekend for django will be also great yeah so we'll work on it uh we'll try to see it okay fine so this code you try to execute my question now okay okay raja all the story you said but how to get okay i'm going to tell you one website you can follow them i think already i spoke in yesterday but the same website i am repeating here what is that posters i forgot the name foursquares uh sql tutorial or something okay i'm writing something okay you get the first website only you can go to the first website it is dedicated to the postgres i think this is what i even i given the link yesterday go to this whatever i said everything is there here okay i've given only for the select uh and but these are all like information guys if you want to understand the posters sequel you understand this is the website this is only for the postgres where is the python this is not for python this is just for postgres you just understand how to write a query where query limit query fetch query inquiry between query like if you want to be strong this is one of the best website you can easily write for example self join you don't know what is self join just go here this this website is a awesome website and if you go to this you select the table from select some information whatever the list you want from the table t1 inner join table t2 on join predict so you try to do it okay these are the tables like this this is the hierarchy of the tables self joints we can create we can create a tables like this this is understanding the sequel so you can ask me the question is it really needed the sequel to for the data science people yes sql is very important because you're dealing with the data data where it is their database so you should know the sql queries when done so get it done this one by uh next to one or three days and these queries are very important try to understand this joints if you are not understand in the normal flow i will explain you with some diagrams so even if you are not understand that way i will tell you some tips so join tables or sql tables however i study you know guys i study with images because i don't read text because sometimes i feel like it's little bored you can use these kind of images you understand the what is this joins under there are two tables a and b that is a customer and bank and you can join those customer and bank there is employee and employee company are like employee branch or something there are different tables we can use it these allow to read these diagrams you can understand easily what is the left join what is the right join okay so something you struggle anything any subject okay you go to the google images type which one you feel and you'll get some images but it is not always a solution always solution but ninety percent it works you'll get some images and read those images see instead of reading the whole drama of joints here with this diagrams i can easily understand okay left join is with this table right join is this one inner join is this one full outer join is this one so basically this is how we can understand this okay so not only this any anything you type even tomorrow if you're not understand the neural networks even if you're not understand anything else we can take our else there is one more trick is that you can go and type this one sql sheet sheet okay so it is where the index even if you when i go and type here you can see this sql sheet is data science is showing i told you one of the primary skill for the data science is the sql okay of course there are so many uh sheet sheets are there you can download this yeah i think this is the one okay i think you can download these sheet sheets and put it in your own folder i don't know how many of you created the one folder dedicatedly for these courses okay you create one one folder and keep everything there sql1 folder machine learning is one folder and keep all these cheat sheets uh whatever the supported files because we are going to deal with almost 200 tools in this entire journey so definitely we need to maintain the proper uh file system in your local computer so go and download these sheet sheets and if you are interested on the sql but if you can ask me if you want to understand the postgres and sql and one more is the python okay you can go to this uh this is a where is the python guys yeah this is the one i think you can go to this this is with the python okay this link is with the python here the query what whatever the code which i said even process is also calling how to call the procedures with the python code it is that but you need to make a connection here the cursor and execute the procedure okay you are making a call to the procedure and execute and pass the parameters if procedure is taking up and finally you commit the and closely and more importantly i'm not done one step guys here and that is uh the cuts are close okay i'm not closing the cursor here okay yeah you have to do the what is the name cursor here pointer okay pointer dot close this is complete and we think is it enough no tomorrow i will start with one programming called we don't write code like this with the database we write in different way i will tell you how to write it

Channel: IamPython

Views: 2,672

Rating: 4.909091 out of 5

Keywords: Python, python programming, datascience, machine learning, PostgreSQL, sql database, python PostgreSQL, psycopg2, psycopg2-binary, Python programming databases, iampython

Id: d1atQKLFHgY

Length: 72min 1sec (4321 seconds)

Published: Fri Jan 15 2021