How to connect to a PostgreSQL database in Python

To connect with a PostgreSQL database, you have to install a specific module with pip:

pip install psycopg2

Then you import psycopg2 and to create a connection you call psycopg2.connect(), passing the database, user, password, host, and port.

In this example we are connecting to a PostgreSQL instance with a database called "test_db", a user called "test_user", a password "test_password", host named "test_host", and the database port "5432".

After that we create a cursor, which is the object we are going to call the execute the commands in the database.

We are making a simple query from a table called "employees" and this query is executed by calling cursor.execute(sql).

Then we use cursor.fetchall() to bring the results and print each record with a for loop.

Finally, we close the connection at the end to release resources as a good practice.

import psycopg2

db_name = 'test_db'
user_name = "test_user"
user_password = "test_password"
host_name = "test_host"
db_port = '5432'

connection = psycopg2.connect(
    database=db_name,
    user=user_name,
    password=user_password,
    host=host_name,
    port=db_port,
)

cursor = connection.cursor()

sql = "SELECT * FROM employees"

cursor.execute(sql)

records = cursor.fetchall()

for record in records:
    print(record)

connection.commit()

connection.close()