How to Use PostgreSQL with Python (psycopg2)

PostgreSQL and Python are a powerful combination for building modern, data-driven applications. PostgreSQL is known for its reliability, performance, and advanced features, while Python is loved for its simplicity and extensive ecosystem.

In this guide, you’ll learn how to use PostgreSQL with Python using psycopg2, the most widely used PostgreSQL adapter for Python. This tutorial covers installation, database connection, CRUD operations, transaction handling, error management, and best practices for production use.

Why Use PostgreSQL with Python?

Python is commonly used for:

• Web applications (Django, Flask, FastAPI)
• Data analysis and automation
• Backend services and APIs

PostgreSQL complements Python by providing:

• ACID-compliant transactions
• Advanced indexing and query optimization
• JSONB and complex data types
• Excellent scalability and stability

Using psycopg2, Python can communicate efficiently with PostgreSQL using native SQL.

What Is psycopg2?

psycopg2 is a PostgreSQL database adapter for Python. It is:

• Fully compliant with Python DB-API 2.0
• Fast and battle-tested in production
• Actively maintained and widely supported

psycopg2 allows you to:

• Execute SQL queries
• Manage transactions
• Fetch query results
• Handle PostgreSQL-specific data types

Prerequisites

Before starting, make sure you have:

• Python 3.8 or newer
• PostgreSQL installed and running
• Basic knowledge of SQL and Python
• pip (Python package manager)

Installing psycopg2

Option 1: Install psycopg2-binary (Recommended for Beginners)

pip install psycopg2-binary

This version includes precompiled binaries and is easier to install.

Option 2: Install psycopg2 (Source Build)

pip install psycopg2

This option is recommended for production environments where you want full control over dependencies.

Connecting to PostgreSQL from Python

Below is a basic example of connecting to a PostgreSQL database:

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="tutorialdb",
    user="postgres",
    password="yourpassword",
    port=5432
)

print("Connection successful")

Key Connection Parameters

• host: Database server address
• database: Database name
• user: PostgreSQL user
• password: User password
• port: Default is 5432

Always keep credentials secure by using environment variables instead of hardcoding them.

Creating a Cursor

A cursor allows Python to execute PostgreSQL commands:

cursor = conn.cursor()

Cursors are used to:

• Execute SQL statements
• Fetch data from query results

Creating a Table Example

create_table_query = """
CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""

cursor.execute(create_table_query)
conn.commit()

The commit() call ensures the transaction is saved.

Inserting Data into PostgreSQL

insert_query = """
INSERT INTO customers (name, email)
VALUES (%s, %s)
"""

cursor.execute(insert_query, ("John Doe", "john@example.com"))
conn.commit()

Why Use Placeholders?

• Prevents SQL injection
• Improves query safety
• Handles data escaping automatically

Selecting Data from PostgreSQL

select_query = "SELECT id, name, email FROM customers"
cursor.execute(select_query)

rows = cursor.fetchall()

for row in rows:
    print(row)

Fetching Methods

• fetchone() – retrieves one row
• fetchmany(n) – retrieves n rows
• fetchall() – retrieves all rows

Updating Data

update_query = """
UPDATE customers
SET email = %s
WHERE id = %s
"""

cursor.execute(update_query, ("newemail@example.com", 1))
conn.commit()

Always verify affected rows in critical updates.

Deleting Data

delete_query = "DELETE FROM customers WHERE id = %s"
cursor.execute(delete_query, (1,))
conn.commit()

For production systems, consider soft deletes using a status flag instead of deleting records permanently.

Handling Transactions Properly

PostgreSQL supports full transaction control.

try:
    cursor.execute("INSERT INTO customers (name, email) VALUES (%s, %s)", 
                   ("Alice", "alice@example.com"))
    conn.commit()
except Exception as e:
    conn.rollback()
    print("Transaction failed:", e)

Best Practice

• Always use try-except blocks
• Roll back transactions on failure
• Commit only when all operations succeed

Using Context Managers (Recommended)

with psycopg2.connect(...) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT COUNT(*) FROM customers")
        result = cursor.fetchone()
        print(result)

Benefits:

• Automatically commits or rolls back
• Ensures resources are closed properly
• Cleaner and safer code

Handling Errors and Exceptions

psycopg2 provides specific exceptions:

from psycopg2 import OperationalError, DatabaseError

try:
    cursor.execute("SELECT * FROM invalid_table")
except OperationalError as e:
    print("Operational error:", e)
except DatabaseError as e:
    print("Database error:", e)

Proper error handling improves application reliability and debugging.

Performance Tips for psycopg2

• Use connection pooling (e.g., psycopg2.pool)
• Fetch only required columns
• Use indexes on frequently queried fields
• Avoid SELECT * in large tables
• Batch inserts using execute_batch() or execute_values()

psycopg2 vs psycopg3

Feature	psycopg2	psycopg3
Stability	Very mature	Newer
Async support	Limited	Native
Adoption	Very high	Growing

For most existing applications, psycopg2 remains a solid and stable choice.

Common Use Cases

• Web applications (Flask, Django backends)
• Automation scripts
• Data migration tools
• Reporting and analytics
• ETL pipelines

Conclusion

Using PostgreSQL with Python via psycopg2 gives you a robust and efficient way to interact with relational data. From simple CRUD operations to complex transactions, psycopg2 provides everything you need to build reliable database-driven applications.

By following best practices—secure connections, parameterized queries, proper transaction handling—you can confidently use PostgreSQL and Python in production environments.