Connecting Python with PostgreSQL allows you to interact with a PostgreSQL database directly from your Python applications.

You can:

Insert data
Fetch records
Update data
Delete data
Build backend systems
Develop data pipelines

This is widely used in:

Web development
Data engineering
Analytics systems
Backend APIs

Step 1: Install PostgreSQL Driver

The most common library is psycopg2.

Install it:

pip install psycopg2-binary

Step 2: Import and Connect to Database

Example:

import psycopg2connection = psycopg2.connect(
    host="localhost",
    database="your_database",
    user="your_username",
    password="your_password",
    port="5432"
)print("Connected successfully!")

If connection succeeds, Python is now connected to PostgreSQL.

Step 3: Create a Cursor

Cursor is used to execute SQL queries.

cursor = connection.cursor()

Step 4: Create a Table

cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
)
""")connection.commit()

Note:

PostgreSQL uses SERIAL for auto-increment.

Step 5: Insert Data

Use parameterized queries to prevent SQL injection.

query = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("Ali", "ali@email.com")cursor.execute(query, values)
connection.commit()print("Data inserted successfully!")

Step 6: Fetch Data

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()for row in rows:
    print(row)

Other options:

fetchone() → Single record
fetchmany(n) → Limited records

Step 7: Update Data

query = "UPDATE users SET name = %s WHERE id = %s"
values = ("Ahmed", 1)cursor.execute(query, values)
connection.commit()

Step 8: Delete Data

query = "DELETE FROM users WHERE id = %s"
values = (1,)cursor.execute(query, values)
connection.commit()

Step 9: Close Connection

Always close resources properly.

cursor.close()
connection.close()

Handling Exceptions

Use try-except for error handling:

import psycopg2
from psycopg2 import Errortry:
    connection = psycopg2.connect(
        host="localhost",
        database="your_database",
        user="your_username",
        password="your_password"
    )
    print("Connected successfully!")except Error as e:
    print("Error:", e)

Using Context Manager (Recommended)

Cleaner and safer approach:

import psycopg2with psycopg2.connect(
    host="localhost",
    database="your_database",
    user="your_username",
    password="your_password"
) as connection:
    
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM users")
        print(cursor.fetchall())

Connections automatically commit or rollback.

Best Practices

Use parameterized queries
Avoid hardcoding credentials
Use environment variables
Close connections properly
Use connection pooling in production
Handle transactions carefully

Real-World Example

Backend Application:

User submits form
Python stores data in PostgreSQL
Admin dashboard retrieves data
Analytics team queries database

PostgreSQL is widely used in production systems due to reliability and performance.

Key Takeaway

Connecting Python with PostgreSQL using psycopg2 allows secure and efficient database operations.

By following best practices like parameterized queries, error handling, and proper connection management, you can build scalable and production-ready database applications.