How To Connect PostgreSQL Server Use Configuration File In Python

We have learnt how to connect to postgresql database server use psycopg2 in article How To Connect To PostgreSQL Database And Execute SQL Statement Use Psycopg2 In Python. But in that article, all the database connection data is hardcoded in python source code which is not esay for maintain. So to fix this issue, in this article we will save the database connection data in a configuration file ( database.ini ), and then load and use the configuration data to connect to postgresql database server.

1. Save Database Connection Data In Configuration File.

Save below content in file database.ini, this ini file is saved in the same directory as the python file. Please note below ini file’s [postgresql_conn_data] section’s options name should be same with the psycopg2.connect method’s keyword name. We will use the option’s name directly in python source code to connect to postgresql db server.

[postgresql_conn_data]
host=localhost
port=5432
database=dev2qa
user=test_user
password=test_user

2. Load Configuration Data Use configparser.ConfigParser Class.

Now we will add a new get connection method in PostgresqlManager.py file, we will use configparser.ConfigParser class’s read method to load postgresql_conn_data section and it’s options’s key and value, then use those data to connect to postgresql database server.

1. First we should import ConfigParser class.

   from configparser import ConfigParser

2. Then add below method in PostgresqlManager.py.
   

   '''
           This method will use the connection data saved in configuration file to get postgresql database server connection.
   
           config_file_path : Is the configuration file saved path, the configuration file is database.ini in this example, and it is saved in the same path of PostgresqlManager.py file.
   
           section_name : This is the section name in above configuration file. The options in this section record the postgresql database server connection info.
   
       '''
       def get_connection_by_config(self, config_file_path, section_name):
   
           if(len(config_file_path) > 0 and len(section_name) > 0):
   
               # Create an instance of ConfigParser class.
               config_parser = ConfigParser()
   
               # read the configuration file.
               config_parser.read(config_file_path)
   
               # if the configuration file contains the provided section name.
               if(config_parser.has_section(section_name)):
   
                   # read the options of the section. the config_params is a list object.
                   config_params = config_parser.items(section_name)
   
                   # so we need below code to convert the list object to a python dictionary object.
   
                   # define an empty dictionary.
                   db_conn_dict = {}
   
                   # loop in the list.
                   for config_param in config_params:
   
                       # get options key and value.
                       key = config_param[0]
                       value = config_param[1]
   
                       # add the key value pair in the dictionary object.
                       db_conn_dict[key] = value
   
                   # get connection object use above dictionary object.
                   conn = psycopg2.connect(**db_conn_dict)
   
                   self._conn = conn
   
                   print("******* get postgresql database connection with configuration file ********", "\n")
   

3. Now use above method to get postgresql database connection. You should provide the configuration file path and name and the section name.

   postgresql_manager.get_connection_by_config('database.ini', 'postgresql_conn_data')