PostgreSQL Database Operations

Pure Python PostgreSQL skill using psycopg2 (no psql client needed).

When to Use

Use this skill when the user needs to:

• List database tables: "What tables are in the database?", "show tables"
• View table structure: "What columns does the users table have?", "describe users"
• Execute SQL queries: "Get all users", "SELECT * FROM users"
• Get schema summary: "What's the database structure?", "schema overview"
• Modify data: Insert, update, or delete records

When NOT to Use

• User asks conceptual questions (e.g., "What is PostgreSQL?")
• User needs data analysis or business insights (requires LLM reasoning)
• User wants natural language to SQL conversion (should be handled by upper-layer Agent)
• Database is not PostgreSQL

Usage Guidelines for AI Agents

Step 1: Check Prerequisites

Before using this skill, ensure:

1. Python 3 is installed
2. Dependencies are installed: pip install psycopg2-binary pyyaml
3. config.yaml exists with valid database connection details

If config.yaml is missing, instruct the user to:

cp config.example.yaml config.yaml
# Then edit config.yaml with real database credentials

Step 2: Choose the Right Command

Step 3: Parse Results

All commands return JSON (except schema-summary):

list-tables output:

{"tables": ["users", "orders", "products"]}

describe-table output:

{
  "table": "users",
  "columns": [
    {"name": "id", "type": "integer", "nullable": false, "default": null},
    {"name": "username", "type": "varchar", "nullable": false, "default": null}
  ]
}

execute-sql SELECT output:

{
  "columns": ["id", "username", "email"],
  "rows": [
    {"values": ["1", "alice", "[email protected]"]},
    {"values": ["2", "bob", "[email protected]"]}
  ]
}

execute-sql INSERT/UPDATE/DELETE output:

{"affected_rows": 1}

Step 4: Handle Errors

If a command fails, check the error message:

• "error": "config.yaml not found" → Guide user to create config
• "error": "psycopg2 not installed" → Run pip install psycopg2-binary
• "error": "connection failed" → Verify database credentials
• "error": "Forbidden operation" → SQL violates safety rules

Safety Rules

This skill enforces strict SQL safety:

✅ Allowed:

• SELECT queries
• INSERT (single row only)
• UPDATE (must have WHERE clause)
• DELETE (must have WHERE clause)

❌ Blocked:

• DROP TABLE/DATABASE
• TRUNCATE TABLE
• ALTER TABLE
• Batch INSERT (multiple rows)
• UPDATE/DELETE without WHERE

Example of blocked query:

python scripts/pgsql_skill.py execute-sql "DROP TABLE users"
# Output: {"error": "Forbidden operation: DROP"}

Programmatic Integration

For advanced usage, import directly in Python:

import sys
from pathlib import Path
sys.path.insert(0, 'scripts')

from pgsql_skill import Database, load_config

# Initialize database connection
config = load_config()
db = Database(
    host=config['host'],
    port=config['port'],
    dbname=config['dbname'],
    user=config['user'],
    password=config.get('password', '')
)

# Use database methods
tables = db.list_tables()
structure = db.describe_table("users")
result = db.execute_sql("SELECT count(*) FROM users")

# Always close connection
db.close()

Troubleshooting

• ModuleNotFoundError: psycopg2: Run pip install psycopg2-binary
  • On macOS with managed Python: pip install psycopg2-binary --no-binary :all:
• config.yaml not found: Copy from config.example.yaml
• Connection failed: Verify host/port/user/password in config.yaml
• Permission denied: Check database user permissions

Best Practices

1. Add LIMIT to SELECT queries to avoid large result sets
2. Always check errors before proceeding
3. Close connections when using programmatic API
4. Warn users before data modifications (INSERT/UPDATE/DELETE) and confirm intent