PostgreSQL (2) Database Connection With Python Using "Psycopg2" Module

today we are going to learn how to connect postgresql database to python program as a matter of fact there are several python modules such as psycho pg2 which will be the module that we will be working with in this tutorial cycle pg is the most popular postgresql database adapter for the python programming language this is the website for psycho pg2 can go ahead and install it via pip install psycho pg2 and let me tell you why we will stick to psycho pg2 module one cyclo pg2 is the most popular python driver for postgresql two it's required for most python and postgres frameworks three it's actively maintained and four it was designed for heavily multi-threaded applications i will assume that you have already installed psycho pg2 on your computer and let's go ahead to visual studio code or any text editor of your choice and let's import psycho pg2 before we move on let's actually create a new database using psql or sql shell like we have seen in the last video server localhost database is postgres port 5432 which is the default port username postgres which is the super user and the password go ahead and enter your password and hit enter then let's check out what we have in the list of databases okay we still have our master database which we have created last time let's actually drop it or delete it by using the statement drop database master space and semicolon and it has been deleted to verify let's check out the new list of databases and indeed it has been deleted or dropped out of the list of databases let's create it one more time we will say create database master space and semicolon okay so let's check out again and indeed we have our master database added again to our list of databases okay good so now we have our master database ready to be connected to our python program let's get back to our visual studio code to connect to the master database we will need to create a connection object so we can create a new cursor to execute any sql statements so simply we will say connection which will be equal to psycho pg dot a method called connect and the connect function or method it creates a new database session and returns a new instance of the connection class so inside the parenthesis we will need to specify the postgresql database parameters and to do that we will use a list of keyword arguments so for instance the host which will be equal to the localhost the port which will be equal to 5 4 inside the string 5432 the database which will be equal to the name of our database which is master user which is postgres and the password which is one two three four five six so to recap the host is a database server address it's either the localhost or an ip address the port which is the port number that defaults to 5432 if it's not provided the database is the name of the database that we have created the user and password of course username and password that are used to authenticate in fact i don't prefer this method this method is not robust and let me show you a real life practical way for connecting your database to your python program so what we will do is we will need to create a separate configuration file with ini extension so let's comment this line out we don't need it and let's create a file here and i will call it database dot i n i an ini file is a configuration file for computer software that consists of a text based content with a structure and syntax comprising key value pairs for properties and sections that organize the properties and i will show you the key value pairs and the sections in a minute but let me tell you first why i prefer this method by using database.ini you can change the postcressql connection parameters when you move the code to production environment without modifying the code so in time of deployment you don't have to change every time inside your code instead you will just make your modifications separately inside your database.ini file okay so as we have here our keyword arguments host port database user and password we will do the same thing inside our database.ini the first thing that we need to write is a very important part of that file which is the section or the header and by default it's called postgresql then host is equal to localhost database is equal to master user is equal to postgres and the password this is it this is our database.ini file but we are not done because this file is a configuration file that main.py cannot read so we will need a second python file we will call it config.py and conflict.py will parse the data inside the database dot ini and will return everything here in the form of dictionary so main.py can read it from the config.py okay so let's go ahead and import from the config parser module and as you can see here it's a file parser and it consists of sections led by section or header so the section or the header which is this one right here and this is very important without it you will get an error that will say file contains no section headers we will import config parser which is the class next we will define a function we'll call it config and inside here we need two parameters a file name and section the file name which will be equal to our database dot i and i and the section which will be equal to postgresql let's go ahead and create a parser so i will create an object i will call it parser and this will be equal to the config parser class then i want to read the config file which is the name passed here inside our file name so parser which is actually the config parser class dot read method and we're going to read the file name so let me tell you what we want to do we want to iterate over the file name and return each element inside that file and put it inside a dictionary so first thing to do is we need to create an empty dictionary then we want to get the section which is postgresql by default so we want to have a condition we want to say that if parser dot a method called has section and this is a verification step because if database dot ini does not have a section or a header we want to raise an exception so if parser dot has section and inside here we want to pass this section which is postgresql so if parser.has section is true then the params will be equal to parser dot items section let's have a for loop to iterate over the params which contains everything inside database dot i and i so we will say for param in params then db which is empty dictionary actually the param with the index of zero will be assigned to param with index of one so this is our way to say that we want to assign each element inside database dot i and i inside the db dictionary i hope you're following and everything is clear so far else we want to raise an exception and inside our string we will say section and whatever section it is which is the postgresql is not found in the and whatever file name we have in our case database dot ini dot format and here we will pass the section and the file name to show you or to give you a better understanding of what db has right now let's go ahead and print db and let's actually invoke the config function let's open the terminal and we'll say python config.py and there it is we have a dictionary as you can see and it has all the items inside the database.ini file so this file has been parsed inside the config.py and we have iterated over the database.ini file and assigned all the elements inside the db dictionary and only now we can read the database dot ini file inside main.py because we will import everything from the config.py inside main.py all right so let's go ahead and see what we can do next here we want to return db let's save that and let's get back to our main.py and now we can import the config so from config file we want to import the config function it's just a coincidence that the name of the file is exactly the name of the function so from config file we want to import config function so now let's go ahead and create a function we'll call it connect and remember that the purpose of this video is to show you how we can connect the database or the postgresql database to our python program and connect function will connect you to the postgresql server so let's have a connection object and we will assign it to none and the non-type is the type for the non-object which is an object that indicates actually no value so none is the return value a function that don't return anything basically it plays the role of a flag somehow then we'll have our params and this will be equal to our config function that we have created so let's print the statement saying that connecting to the postgresql database and connection so this will be equal to psycpg class dot connect method and here if you remember in the quarks or the double asterisks video we said that the double asterisk or the quirks or the keyword arguments work with dictionaries and as this is a dictionary that contains everything inside the database dot i and i so we will use the double asterisk params which is a way to say that we want to extract everything inside the params inside those two parentheses and to connect using the psychopg2 module all right now let's go ahead and create a cursor so we'll give it the name of crsr which will be equal to connection.cursor and if you recall our tutorial for sqlite it had exactly the same logic and let's actually print the database version for postgresql so we'll say print post grass ql database version and we will use our cursor dot execute so we are executing a statement and the statement here is select and version like that so to actually display the postgresql database server version we need to read the result set so we will have an object or a variable we'll call it db version and this will be equal to the cursor but if you remember also in our sqlite tutorial we had a method called fetch all to fetch everything from the data set so here we will say fetch one not all because we only want to fetch the database version and we will print that dbversion variable what if we have an error or a problem well what i will do is i will wrap this code block here in try accept code block so we'll say try and let's indent the scope block and we'll say accept and we'll raise the exception i will use the psychopj2 class or module dot database error as error and we will print that error so this is my way of handling any errors that might occur in the code finally we'll say that if the connection is not none anymore because we initially set the connection to none but after we have assigned the connection to the cycle pg.connect everything here in the params which means that the connection is not none anymore so finally if connection is not none we will close the connection there is something that we didn't do we didn't close also the cursor which is a very important step two things always that we need to close them the cursor because the cursor and the connection is the communication way between the python file and the database so first we need to close the cursor and then we need to close the connection then we will print database connection terminated so we'll say if name is equal to main let's go ahead and invoke our connect function okay so let's give this a try python main dot py okay great so connecting to the postgresql database postgresql database version and then it displayed the version it's version 13 compiled by visual c plus build 1914 64-bit database connection terminated great so we are now connected to the postgresql database our master this one right here our master database let me show you very quickly if we have a problem here let's just omit the q and l save that and let's play again the file so you see here section postgresql is not found in the database.ini file i should probably make a space here just to show you that better so section postgresql is not found in the database.ini file this is our error handling system what we did we raised the exception and it's working perfectly what if we will change the password connecting to the postgresql database then fatal password authentication failed for user postgres this is the code that we have written right here this one except exception psychopj2 the database error has error then we print there and therefore we got this displayed password authentication failed for user postgres okay in the next video we are going to see how we can create tables inside our database i hope you liked the video thank you for watching and i will see you in the next video

Channel: Bek Brace

Views: 49,721

Keywords:

Id: Q8iYj2ypWss

Length: 18min 51sec (1131 seconds)

Published: Sun Oct 25 2020