Connect to PostgreSQL from Python (Using SQL in Python) | Python to PostgreSQL

hey guys my name is tawfiq and in this video let us understand how to connect to postgresql database from our python script i will be using the psyco pg2 module in order to do this connection we'll start by creating a table and then loading some data into this table from our python script we'll then see how to retrieve data from database into our python script and also perform a few other dml operations as well at the end of this video i'm also going to show you how you can use context manager to perform all of these operations which i believe is the recommended way of how you should be connecting to a database in python as always before i can start if you like this video and you find this useful then please make sure to subscribe to the channel and give me a thumbs up thank you and let's begin okay so there are a couple of things that we need to first do before we can start writing python scripts to connect to our postgresql database the very first thing is to install the psycopg2 module to do that you can just go to your terminal if you're on mac or linux and if you're on windows you can just go to your command prompt and then just type pip 3 install cycle pg2 and this should install the module if you're on windows the command that you should be using is pip install cycle pg2 now once you have installed the module the next thing is to identify all the different credentials that you need to connect to your database generally the information that you would need is the host name the database name the username password and port id now it does not matter if your database is installed on your local machine or if it is installed on a server or if it is installed on a cloud the process of connecting to the database and all the different credentials that you need will always be the same and also the process of how you would be processing all the different data from your database would again be the same now if you're using pg admin then you can get most of this information from pg admin pretty easily so just go to your pg admin right click on the server name go to properties go to the connections tab and here you would find your host name as well as the port once you get this information then in order to identify the database name and the schema just go to the database that you plan to use in this case i'm planning to use the demo database right click on that go to properties and here you would find the database name and then you would also find the owner which basically is your username so you have got all of this information from pg admin the only additional information that you need is the password the password is basically the same that you have entered when you installed your database so once you have got all of this information now we are all set to start writing your python program to connect to the postgresql database the first thing that you need to do is to import the required module which in this case is the psycho pg2 i'm just going to input that and once you have imported this module now in order to connect to the database you just need to use a function called as connect which is inside our psycopg2 module now to do that i'm just going to call this module and then call the connect function inside this and i need to pass a few parameters and whatever is going to return from this connect function i'll be storing it into a variable called as con now the parameters that i need to pass here are basically everything that i just listed down here now all of these credentials i have already stored it into this five variables now this is not the way that we would do when we are working on a real real time project or when we are building a real-time application uh generally what we would do is we would maintain a configuration file which would have all these credentials and all the different settings of the database and then we would use this configuration file in our program to connect to the database now since i want to keep this video as simple as possible i will not be using the configuration file rather maintain all all of these credentials in all of these variables and then i will be passing these variables as a parameter into this function now the parameters that i need to mention here is i'll say host equal to hostname which i have already stored and then i will say db name equal to database and then i need the user equal to username and then password equal to pwd and then finally port equal to port id that's all so this is all we need to connect to our database now since this command here that is a connect function from the psycopg2 vid would open a database connection we also need to make sure that we close the database connection before we can exit from the program so i am just going to use the close method inside this con variable now this con will hold a connection object which will be returned from this connection function and this connection object will have a few methods that we can use and one of them is the close this will basically close the database connection and we'll make sure that all the code that we are going to write will be in between this connection database connection open and the database connection close commands now the program that have written here should be sufficient to connect to our database and you can see that if i execute there's no error so the connection is successful but let's say if i change the hostname to some dummy value and if i try to execute it it will throw me an error saying that the hostname is not recognized now basically what this means is if we do not get any error then the connection is successful and if the connection is not successful then it would generally throw an error so in this case what we should be doing is we should place our database connection commands inside the try and exit block so in case the database connection fails it does not crash our program but we'll be able to capture the exception so i'm just going to place whole of this into a try and accept block and i'm just going to use the exception class to capture the exception and i'm going to pass it into a variable called error and then i'm just going to print this error so now if i execute this you can see that it's still throwing the error but it's just my exception block is capturing the error and it's just printing the error message here so that is how it basically works now let me just fix this localhost now what this connect function will do is it's going to open a database connection which basically means that it's going to open a session in our database where we will be allowed to perform any database transactions like ddl dml dql or any other but in order to perform any of the sql transactions or the database transactions we will need to open a cursor a cursor is something that can help you to perform any of the sql operations it kind of stores the values that will be returned from those sql operations now to create a cursor i just need to assign a variable like i'm just going to name it like cur and then i'm going to use the connection object dot i'll say cursor so this will basically open a cursor and similar to database connection once we open a cursor before we end our program we also need to close the cursor so these are the two additional things that we always need to do make sure that the database connection is always closed and the cursor is also always closed when we exit the program so that is why i have added the close command here now this is fine but this is not the best way of closing your connections or closing your cursor the reason is if there was an unexpected exception that occurred in our try block then there is possibility that the cursor and the connection would never be closed now to avoid that what we will need to do is we will need to place both of this inside our final block so finally block is again another block part of our triangle block so what this will basically do is whether there is an exception or no exception whatever block of code that you have written inside finally will always be executed so what i'm just going to do is the closure of cursor and connection i'll just place it here now this is not the best way of doing it because let's say if your database connection never succeeded then the con variable would never have the connection object and meaning that you would never have the close method so basically this statement here would throw an error so in order to avoid that what i'm just going to do is in the beginning of the program itself i'm going to assign a none value into both of these variables so i'll just say con equal to none and cur equal to none so once i have these values as none and in the final block so i'm just going to say if cur is not none meaning that the cursor was open if the cursor is open only then i want to close it and same way i'll do for the database connection as well so if con is not none that means if the database connection was open only then close it if the database connection was never open then this statement will never be done so we can eliminate this error so this is basically how our outline should look like how when we want to try to write a python program to connect to a database so we have created a database connection and we have opened the cursor now it's time to create some database table and then do some operations on that table now for this demo what i'm going to do is i'm going to create a table called as employee and as you can see in the demo database as of now you will not find the employee table so what we are going to do is we are going to create the employee table now to create the employee table the syntax would be exactly the same as you would use in your sql so it's not just create table any other commands that you want to do whether it's ddl dml dql or anything the syntax and the code of sql is exactly the same you just need to place that code inside of our python program so what i'm going to do here is i'm going to have a variable by the name create script and inside this variable i will place the create table script and let me do it like a multi-line script so it becomes more neat so i'll just say create table and then i'll say employee and i'll pass in a few columns so i'll say the first column to be something like id and it will be let's say integer and i want this to be my primary key and then my next column would be name which will be a varchar and i'll say a max size of 40 characters in this column and it will be a not null column so not null and then i'll have my third column as salary which will be an integer and then finally i will have my department id which will be aware and i'll say a limit of 30 characters for this department id and i'll close my table so that's all this is the same script that you would use to create your table in your sql as well i'll add one more condition here i'll just tell create table if not exist so if the table does not exist only then create table else just don't do anything okay now i have created my script and have placed that script into a variable now in order to execute this create table script we will need to use the cursor.execute method so i can just say cur dot execute and then i will just pass in this variable which has the create table script so hopefully if i run this it should create my table but it will not and we'll see why so if i have executed it there is no error and if i go and check my table you can see that the table is still not created and the reason for that is all the transactions that we do in our python program we will need to commit it only then it will be saved to our database now in order to commit a transaction i will just need to say con dot commit and this basically will tell database to basically save any transactions that we have done into the database so now if i just execute this and if i go back to my database and if i check you can see that the table is created and it's having the four columns that we just mentioned here so we have successfully created a table in the postgresql database from our python program next let's try to add some data into this table so i'll just remove this and in order to add some data into this table i just need to use the insert script in sql so i'm again going to create another variable and i'm going to call it like let's say insert script and this is where i'm going to mention my insert command so i'm just going to say insert into the table name that is employee and then i'm just going to mention all the columns that is id name salary and department id and then i'm just going to mention the values and i need to specify all the different values now i will not be specifying all the values here so that i can avoid the sql injection but i'm just going to use a placeholder that is percentage yes and i'm just going to mention four different placeholder for four different columns and this is basically my insert script now in order to mention the values that should replace these placeholders i'm going to place them into another variable and i'm going to call them like insert value and this should be a tuple of four values so my id will be one and then my name let's say it will be james and then it will be my salary let's say 12 000 and then it will be the department id let's say d1 okay so i have basically created the insert script and i have passed the values which needs to be inserted into this table in another variable now in order to execute this insert script i can again use the curl.execute method and here i need to pass two arguments the first argument is the insert script and the second argument will be the value that needs to replace the placeholders present in the first argument and now if i just execute it you can see that i'm not having any error and if i look at this table you will find one day one record has been inserted into this table so our insert script has worked fine now this is fine but let's say if you wanted to insert multiple records then what you can just do is instead of passing a single tuple i can pass multiple tuples which needs to be placed inside a list so i'm just going to copy this whole tuple here and i'm going to let's say pass in three employees so i'll just copy it three times and i'm just going to pass in a unique employee id one two and three it will be james and then let's say it will be robin and his salary will be let's say 15 000 and then it will be let's say xavier and his salary will be let's say 20 000 and he belongs to the department d2 okay so this is fine now one additional thing that i need to do is i've already inserted the employee james once and if i try to execute this command again it's going to throw me an error because id is a primary key and i cannot insert it multiple times and i plan to execute this command a few times so instead of having different values inserted into the table every single time i'll just make sure to drop this table and create this table newly every time i execute this query so what i'm just going to do is i'm just going to use the command like cur dot execute and here i'll just pass in my drop command so i'll just tell drop table if exist employee so this will make sure that every time i execute the command it will drop the table first and then it will create the table and then it will insert all the data that we wanted to insert so this should work fine now one additional thing to do here is since we are trying to insert multiple values i cannot just pass a car execute and then pass all this list of values here what i need to do is i need to use a for loop and i'm just going to say for record in insert that is value and i'm just going to change this variable name to values because there are multiple values now just to make it more meaningful so i'll say for record and insert values and then here my second argument will be my record which will basically hold each one tuple at a time now if i just execute this so there is no error and if i go back to my table you can see that there are three records inserted into this table so this is basically how you can insert multiple records into your table okay so now we have created the table and loaded some data into that table next let's see how we can fetch all the data from the database table into our python script now in order to do that i can just use the select clause so i'm just going to remove this and here instead of assigning the select clause into a variable i'm just going to use it straight away in my cut.execute so i'm just going to mention like select start from employee this should just fetch all the records from the employee table and what this will do is this will just execute the command and it will return the data and place it in the cursor now in order to view that data i need to use another method called as car dot fetch all there are a few other methods as well if i wanted to fetch only one record then i can use fetch one but in this case i just want to fetch all the records so this fetch all will return all the records from my cursor now in order to view the data i will just place it inside the print statement and now if i just execute it you can see that it's basically printing a list of tuples each tuple holds one record this is fine but if you want to weave each record in its own line then what i can just do is i can use a for loop and saying that for record in i'll just copy this whole thing that is car dot fetch all and then i'll just print the record okay so if i just clear this and if i run it now you can see that each record has its own line now this is fine but the thing is each of this record is a tuple and if you wanted to access a specific column then the only way to access that would be based on index so let's say if i wanted to access the name and the salary of each employee then i had to say that the name belongs to the index one and then the salary belongs to the index two so i had to say record one and record two and now if i just execute it now it's just printing me the employee name and the salary this works fine since it's a very small table but let's say if you are working on a table which has let's say hundreds of different columns then identifying what is the index of a particular column would be a tricky part to do so to overcome that we can also tell our cursor to return the data in the form of a dictionary now in order to do that what you just need to do is let me just clear this you just need to import another module which basically lies inside the cycle pg2 and it's called as the extras psycho pg2 dot extras i'm just going to copy this thing and when i'm calling my cursor i need to pass saying that cursor factory equal to the psychopg2 dot extras dot dict cursor okay so that's all what this will just tell the cursor is to return the data in the form of a dictionary so now if i wanted to access the name and salary of the employee i can just use the column name of that table so i'll just say name and the salary would be having the column name like salvi and if i just execute this you can see that you're getting the same data now the advantage here is that instead of mentioning the index i can just mention the table column name which i believe is a more simpler way to fetch a particular record okay so now we have seen how to fetch the data from our database table into our python script now let's also see how we can update some data from our table in our table and also delete some record in our table now i'm going to use the update and the delete statements to do that to do the update i'm just going to create another variable which i'm where i'm going to store my update script i'm just going to call it like update script and i'm just going to use my update sql command that is update table name set let's say i just want to change the salary i want to give an increment of 50 to each of these employees so i'm just going to say salary salary plus i'll just say salary into 0.5 which should basically uh increase the salary by 50 so this is basically my update script in order to execute that i'm just going to use the cursor.execute again so i'm just going to pass in my script that is update script so this should basically update the record one additional thing i'll do is i'll place this update command let's say before i can fetch the data so i can just see the changes that it will do to this table okay so now if i just execute you can see that i'm just printing the salary and the employee name here and you can see the salary is already increased initially the salary that we entered was twelve thousand fifteen thousand and twenty thousand and now the salary is eighteen twenty two thousand five hundred and thirty thousand so this is basically how we have updated uh the data in our table using the update command uh now finally let's also see if i wanted to delete a particular employee let's say if i wanted to delete james from this table i can just use a delete script so i'll again create a variable called as delete script equal to my delete statement so i'll say delete from employee where and i'll say name equal to something okay so here i will not be specifying the exact employee name and rather than that i'll mention the exact employee name in another variable which i'll be calling like delete record and here i'll just pass a tuple and i'm going to mention something like james because that is employee i want to delete and since it's a tuple i need to mention a comma at the end and now let me try to execute this delete statement by using the cursor.execute method and i'm just going to pass in two arguments here so first will be my delete script which has a placeholder and its replacement value will be present in my second argument which is the delete record so this should hopefully delete the james record from this table and let me clear this and if i execute this you can see that i only have two employees now so if i go back to my database and check it you can see that i only have two employees so this is basically how we have done almost all of the dml operations so we have seen how to create a table and insert some record into a table we have also seen how to insert multiple records we have seen how to update the data how to delete a record and we have also seen how to fetch the data from the database and show it in our python script finally the last thing that i want to show here is how to use context manager to basically perform all of these operations so in order to use the context manager we just need to use the with clause so we need to use the width clause when we are trying to create the database connection and also we can use the width clause when we are trying to open the cursor now in order to use the width clause when creating the database connection i can just replace this con equal to with the with clause and then i can just use my psycho pg2 dot connect function pass in all the parameters and at the end i can mention the variable name which i'll again use con and i need to pass a colon which basically means the next line needs to be indented and i'll do the same for my cursor opening as well i'll use a width clause to open my cursor and at the end i'll mention the cursor variable name so again this needs to be intended so let me first indent this cursor and then let me also indent all the remaining piece of code inside the cursor so that's all that's how easy it is to use a width class now there are a few advantages of using a bit class the first advantage is that a bit clause will take care of closing the cursor for you so i don't need to specifically or manually mention to close the cursor as soon as our program execution exits the width clause of the cursor it will close the cursor for us so i don't need to mention the closing of cursor command specifically here so that is one advantage the second advantage is that i don't need to mention to commit the database transactions so the with clause will take care of that as soon as our program will exit the with clause of the database connection it will commit the transaction so if there were no exceptions then all the transactions would be committed and if there were exceptions then all the transactions would be rolled back so this is the advantage of using a width loss one thing to remember is that the bit clause will not take care of closing the database connection for us so you will still need to manually close the database connection so i'll still maintain the connection close logic here and then i don't need to have this cursor equal to none so i'm just going to remove that and that's all now if i just execute this program you can see that it's working fine i'm still getting the same data if i look at my table i still have the same data so everything works exactly the same just the advantage that what with class gives us is it will make sure to commit the transactions and it will also make sure to close the cursor so i think it is always recommended that you should be using a width class when you are trying to open a database connection or a cursor or even whenever you are using to open a file i hope all of this was useful if you found this video useful and you learned something from it then please make sure to subscribe to the channel and give me your feedback on what you felt and if there were anything else that you would like me to cover or if you have any other doubts you can just leave your comment below thank you and see you soon in the next one bye

Channel: techTFQ

Views: 804

Rating: 5 out of 5

Keywords: Connect to PostgreSQL from Python, Using SQL in Python, Python to postgresql, connecting to postgresql database from python, SQL in python, how to connect to postgresql database from python, connect to database from python program, postgresql and python, use postgres in python, using sql in python, execute sql commands in python, python tutorial, postgresql tutorial, python postgresql, data science

Id: M2NzvnfS-hI

Length: 22min 58sec (1378 seconds)

Published: Mon Sep 13 2021