How to Connect to PostgreSQL from Python with psycopg2?

Connect to PostgreSQL from Python with psycopg2

In this tutorial, we will learn how to connect to PostgreSQL Database from Python application using psycopg2 library.

psycopg2 library is one of the most widely used library for connecting and performing operations on PostgreSQL database.

To connect to a PostgreSQL database from Python application, follow these steps.

1. Import psycopg2 package.
2. Call connect method on psycopg2 with the details: host, database, user and password. host specifies the machine on which the PostgreSQL server is running; database specifies the specific database among many databases to which connection has to be made; user and password are the credentials to access the database.
3. connect function returns a connection object which can be used to run SQL queries on the database.

import psycopg2

conn = psycopg2.connect(host="localhost",database="mydb", user="postgres", password="postgres")

if conn is not None:
	print('Connection established to PostgreSQL.')
else:
	print('Connection not established to PostgreSQL.')

Connection to PostgreSQL Database from your Python application is successful.

You have to close the connection after you are done with the connection object. Also, for database operations, use try catch.

import psycopg2

try:
	conn = psycopg2.connect(host="localhost",database="mydb", user="postgres", password="postgres")

	if conn is not None:
		print('Connection established to PostgreSQL.')
	else:
		print('Connection not established to PostgreSQL.')
		
except (Exception, psycopg2.DatabaseError) as error:
	print(error)

finally:
	if conn is not None:
		conn.close()
		print('Finally, connection closed.')

Non-successful Scenarios when connecting to PostgreSQL from Python

Wrong Database

When you provide a wrong database, you will get a FATAL error saying database does not exist.

Wrong Username

When you provide a wrong username or password, you will get a FATAL error with message “password authentication failed for user”.

Conclusion

In this PostgreSQL Tutorial, we have learned how to make a connection to PostgreSQL Database.