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
#!/usr/bin/pythonfrom configparser import ConfigParser def config(filename='database.ini', section='postgresql'): # create a parser parser = ConfigParser() # read config file parser.read(filename) # get section, default to postgresql 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
#!/usr/bin/pythonimport psycopg2from config import config def connect(): """ Connect to the PostgreSQL database server """ conn = None try: # read connection parameters params = config() # connect to the PostgreSQL server print('Connecting to the PostgreSQL database...') conn = psycopg2.connect(**params) # create a cursor cur = conn.cursor() # execute a statement print('PostgreSQL database version:') cur.execute('SELECT version()') # display the PostgreSQL database server version db_version = cur.fetchone() print(db_version) # close the communication with the PostgreSQL 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:
