Python2.7/3.6连接PostgreSQL及psycopg2问题排查求助

Hey there! Let’s walk through why you’re hitting these psycopg2 problems with Python 2.7 and 3.6, plus give you a working codebase that works for both versions.

First, Let’s Diagnose the Error Causes #

1. Python 2.7: NameError Even Though psycopg2 is Installed #

You mentioned you had successful inserts before, so this is almost certainly an environment mismatch or corrupted installation:

• Maybe you installed psycopg2 for a different Python 2.7 instance (e.g., a virtualenv you’re not currently using, vs. the system Python).
• System updates or pip operations might have overwritten or damaged the psycopg2 installation.
• A less likely (but possible) issue: your code references an undefined variable (like a misspelled psycopg2 in an exception handler, or using conn before initializing it).

2. Python 3.6: "No module named psycopg2" & ImportError on pip3 Install #

This is a common pain point on Fedora. Psycopg2 requires system-level development libraries to compile from source, and you’re missing them:

• Without postgresql-devel, python3-devel, and gcc, pip3 can’t compile the psycopg2 extension, leading to the ImportError during installation. Once those dependencies are in place, the install will work.

Step-by-Step Fixes for Your Environment #

Fix Python 2.7’s psycopg2 Setup #

1. First, verify if psycopg2 is actually available to your Python 2.7:

   python2 -c "import psycopg2; print(psycopg2.__version__)"
   

   If this throws an error, reinstall with system dependencies:
2. Install required system packages:

   sudo dnf install postgresql-devel python2-devel gcc
   

3. Force-reinstall psycopg2 to fix any corruption:

   sudo pip install --force-reinstall psycopg2
   

4. Double-check your code for undefined variables (e.g., make sure you’re not using except psycopg2.Error as e: if psycopg2 failed to import).

Fix Python 3.6’s psycopg2 Setup #

1. Install the necessary system development libraries first:

   sudo dnf install postgresql-devel python3-devel gcc
   

2. Install psycopg2 via pip3:

   sudo pip3 install psycopg2
   

3. Verify the installation:

   python3 -c "import psycopg2; print(psycopg2.__version__)"
   

Cross-Version Compatible Python Code #

Here’s a robust script that works with both Python 2.7 and 3.6, with proper error handling:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

try:
    import psycopg2
    from psycopg2 import OperationalError, ProgrammingError
except ImportError as import_err:
    print("Failed to import psycopg2: {}".format(import_err))
    exit(1)

# Update these with your actual database credentials
DB_SETTINGS = {
    "dbname": "your_database",
    "user": "your_username",
    "password": "your_password",
    "host": "localhost",
    "port": "5432"
}

def insert_sample_data():
    db_connection = None
    cursor = None
    try:
        # Establish database connection
        db_connection = psycopg2.connect(**DB_SETTINGS)
        cursor = db_connection.cursor()

        # Example insert query (replace with your table/columns)
        insert_stmt = """
            INSERT INTO your_table (col1, col2, col3)
            VALUES (%s, %s, %s);
        """
        # Sample data (match the number of columns in your query)
        record = ("sample_val1", 42, "2024-05-20")
        
        cursor.execute(insert_stmt, record)
        db_connection.commit()
        print("Data inserted successfully!")

    except OperationalError as conn_err:
        print("Database connection failed: {}".format(conn_err))
        if db_connection:
            db_connection.rollback()
    except ProgrammingError as query_err:
        print("Invalid SQL query error: {}".format(query_err))
        if db_connection:
            db_connection.rollback()
    except Exception as general_err:
        print("Unexpected error: {}".format(general_err))
        if db_connection:
            db_connection.rollback()
    finally:
        # Clean up resources regardless of success/failure
        if cursor:
            cursor.close()
        if db_connection:
            db_connection.close()

if __name__ == "__main__":
    insert_sample_data()

Key Compatibility Notes: #

• Uses str.format() instead of Python 2’s % formatting (works in both versions)
• Explicitly catches psycopg2-specific exceptions for better debugging
• Ensures database connections/cursors are always closed to avoid resource leaks
• Includes a top-level ImportError catch to immediately flag missing psycopg2

内容的提问来源于stack exchange，提问作者Vérace