Python | Client Libraries | Postgresql Tutorial

Introduction

Explanation: This tutorial covers how to use PostgreSQL with Python. It includes instructions on setting up the environment, connecting to a PostgreSQL database, and performing CRUD operations.

Setting Up the Environment

Step 1: Install PostgreSQL

Follow the instructions for installing PostgreSQL on your operating system. Make sure the server is running.

Step 2: Install psycopg2

psycopg2 is a popular PostgreSQL adapter for Python. Install it using pip:


pip install psycopg2

Connecting to PostgreSQL

Step 1: Import psycopg2

Import the psycopg2 module in your Python script:


import psycopg2

Step 2: Establish a Connection

Use the following code to connect to your PostgreSQL database:


try:
    connection = psycopg2.connect(
        user="yourusername",
        password="yourpassword",
        host="127.0.0.1",
        port="5432",
        database="yourdbname"
    )
    cursor = connection.cursor()
    print("Connection to PostgreSQL DB successful")
except Exception as error:
    print(f"Error: {error}")

Connection to PostgreSQL DB successful

Performing CRUD Operations

Creating a Table

Create a new table in your database:


create_table_query = '''CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    joining_date DATE NOT NULL
)'''
cursor.execute(create_table_query)
connection.commit()
print("Table created successfully")

Table created successfully

Inserting Data

Insert data into the table:


insert_query = '''INSERT INTO employees (name, department, joining_date) VALUES 
    ('Alice', 'HR', '2022-01-10'),
    ('Bob', 'IT', '2022-02-15'),
    ('Charlie', 'Finance', '2022-03-20')'''
cursor.execute(insert_query)
connection.commit()
print("Data inserted successfully")

Data inserted successfully

Reading Data

Read data from the table:


select_query = "SELECT * FROM employees"
cursor.execute(select_query)
records = cursor.fetchall()
for row in records:
    print(f"ID: {row[0]}, Name: {row[1]}, Department: {row[2]}, Joining Date: {row[3]}")

ID: 1, Name: Alice, Department: HR, Joining Date: 2022-01-10
ID: 2, Name: Bob, Department: IT, Joining Date: 2022-02-15
ID: 3, Name: Charlie, Department: Finance, Joining Date: 2022-03-20

Updating Data

Update data in the table:


update_query = '''UPDATE employees SET department = 'Marketing' WHERE name = 'Alice' '''
cursor.execute(update_query)
connection.commit()
print("Data updated successfully")

Data updated successfully

Deleting Data

Delete data from the table:


delete_query = "DELETE FROM employees WHERE name = 'Charlie'"
cursor.execute(delete_query)
connection.commit()
print("Data deleted successfully")

Data deleted successfully

Closing the Connection

Always close the database connection when you're done:


if connection:
    cursor.close()
    connection.close()
    print("PostgreSQL connection closed")

PostgreSQL connection closed

Exception Handling

Handle exceptions gracefully to ensure your application doesn't crash unexpectedly:


try:
    # Database operations
except Exception as error:
    print(f"Error: {error}")
finally:
    if connection:
        cursor.close()
        connection.close()
        print("PostgreSQL connection closed")

Conclusion

By following this tutorial, you have learned how to connect to a PostgreSQL database using Python and perform basic CRUD operations. You also learned the importance of handling exceptions and closing database connections properly.

Using PostgreSQL with Python