It works! Now we’re going to customize, create other PostgreSQL commands to create, read, update or delete via Python. # Import libraries from _conn import load_db_table from nfig import get_project_root # Project root PROJECT_ROOT = get_project_root() # Read database - PostgreSQL df = load_db_table(config_db = 'database.ini', query = 'SELECT * FROM tablename LIMIT 5') print(df) Wait! There is main.py file and what is it? Okay then, it must be a Python script to test our PostgreSQL connection via Python. The recommended directory for running the scripts (Image by Author) In order to compile our scripts, we set our directory to locate database.ini, config.pyand db_conn.py. # Import libraries import pandas as pd import psycopg2 from nfig import config # Take in a PostgreSQL table and outputs a pandas dataframe def load_db_table(config_db, query): params = config(config_db) engine = nnect(**params) data = pd.read_sql(query, con = engine) return data It will load the table that fits with our input ( config_db and query). When our works need to read the table on the database regularly, we can create a function with the filename db_conn.py as follows. # Import libraries from configparser import ConfigParser from pathlib import Path def get_project_root() -> Path: """Returns project root folder.""" return Path(_file_).parents def config(config_db): section = 'postgresql' config_file_path = 'config/' + config_db if(len(config_file_path) > 0 and len(section) > 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)): # Read the options of the section config_params = config_ems(section) # 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 value = config_param # Add the key value pair in the dictionary object db_conn_dict = value # Get connection object use above dictionary object return db_conn_dict Note: section = ‘postgresql’ is set depends on the header of database.ini file. It also creates a function that returns the project root folder. To parse the information from the database.ini file, we create config.py file with a script as follows.
$pip3 install psycopg2 How to connect with PostgreSQL Use the following command on the terminal to install the psycopg2 module. It's the core module for this tutorial, so make sure we have installed it on our machine. The psycopg2 is the PostgreSQL connector commonly used by Python developers to connect to Python.
The psycopg module to connect a PostgreSQL
So that in this tutorial, we will discuss how to connect to PostgreSQL using psycopg2. However, psycopg2 becomes the most popular one. In Python, we have several modules to connect to PostgreSQL such as SQLAlchemy, pg8000, py-postgresql, etc. PostgreSQL is one of the relational database management systems which is open-source and also a lot of functionality.