OpenTelemetry Psycopg2 Instrumentation

import psycopg2
from opentelemetry import trace
from opentelemetry.sdk.resources import Resource
from opentelemetry.sdk.trace import TracerProvider
from opentelemetry.sdk.trace.export import ConsoleSpanExporter, SimpleSpanProcessor
from opentelemetry.instrumentation.psycopg2 import Psycopg2Instrumentor
import os

# Configure OpenTelemetry SDK
resource = Resource.create({"service.name": "my-psycopg2-app"})
provider = TracerProvider(resource=resource)
processor = SimpleSpanProcessor(ConsoleSpanExporter())
provider.add_span_processor(processor)
trace.set_tracer_provider(provider)

# Instrument psycopg2
# Pass enable_commenter=True and enable_attribute_commenter=True for SQL Commenter support
# Psycopg2Instrumentor().instrument(enable_commenter=True, enable_attribute_commenter=True, skip_dep_check=True)
Psycopg2Instrumentor().instrument()

# Database connection details (use environment variables or sensible defaults for quickstart)
db_name = os.environ.get('POSTGRES_DB', 'testdb')
db_user = os.environ.get('POSTGRES_USER', 'user')
db_password = os.environ.get('POSTGRES_PASSWORD', 'password')
db_host = os.environ.get('POSTGRES_HOST', 'localhost')
db_port = os.environ.get('POSTGRES_PORT', '5432')

try:
    # Connect to PostgreSQL
    conn = psycopg2.connect(dbname=db_name, user=db_user, password=db_password, host=db_host, port=db_port)
    cursor = conn.cursor()

    # Execute some SQL queries
    with trace.get_tracer(__name__).start_as_current_span("db_operations"):
        cursor.execute("CREATE TABLE IF NOT EXISTS otel_test (id serial PRIMARY KEY, name VARCHAR(255))")
        cursor.execute("INSERT INTO otel_test (name) VALUES (%s)", ("OpenTelemetry",))
        cursor.execute("SELECT * FROM otel_test")
        result = cursor.fetchall()
        print(f"Fetched result: {result}")

    conn.commit()
    cursor.close()
    conn.close()
    print("Database operations completed and traces should be visible.")
except Exception as e:
    print(f"An error occurred: {e}")
    print("Please ensure a PostgreSQL database is running and accessible (e.g., via Docker):")
    print("docker run --rm --name some-postgres -e POSTGRES_DB=testdb -e POSTGRES_USER=user -e POSTGRES_PASSWORD=password -p 5432:5432 -d postgres")