PostgreSQL – Connecting to the database using Python
Last Updated :
30 Aug, 2020
PostgreSQL API for Python allows user to interact with the PostgreSQL database using the psycopg2 module. In this article we will look into the process of connecting to a PostgreSQL database using Python.
Prerequisites:
First we will need to install the psycopg2 module with the below command in the command prompt:
pip install psycopg2
Creating a Database:
For the purpose of example we will be needing a sample database. To create so, follow the below steps:
- First open a PostgreSQL client tool like pgadmin4 or psql.
- Second login to the database using your credentials.
- Finally use the below command to create a database (say, School)
CREATE DATABASE school;
Connecting to the database:
To connect to the above created database (ie, school), we use the connect () function. The connect() function is used to create a new database session and it returns a new connection class instance.
To do so use the below syntax:
Syntax: conn = psycopg2.connect("dbname=suppliers user=postgres password=postgres")
To make it convenient to use you can use the below method:
1. First create a database.ini file with the credentials as shown below:
[postgresql]
host=localhost
database=school
user=postgres
password=5555
Now, the following config() function reads the database.ini file and returns connection parameters. The same config() function is added in the config.py file:
Python3
from configparser import ConfigParser
def config(filename = 'database.ini' , section = 'postgresql' ):
parser = ConfigParser()
parser.read(filename)
db = {}
if parser.has_section(section):
params = parser.items(section)
for param in params:
db[param[ 0 ]] = param[ 1 ]
else :
raise Exception( 'Section {0} not found in the {1} file' . format (section, filename))
return db
|
Example:
The following connect() function connects to the school database that we created earlier and returns the PostgreSQL database version.
Python3
import psycopg2
from config import config
def connect():
conn = None
try :
params = config()
print ( 'Connecting to the PostgreSQL database...' )
conn = psycopg2.connect( * * params)
cur = conn.cursor()
print ( 'PostgreSQL database version:' )
cur.execute( 'SELECT version()' )
db_version = cur.fetchone()
print (db_version)
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print (error)
finally :
if conn is not None :
conn.close()
print ( 'Database connection closed.' )
if __name__ = = '__main__' :
connect()
|
Output:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...