AutoGen Tutorial (Python): connecting to PostgreSQL for intermediate developers

This tutorial shows you how to wire an AutoGen Python agent to PostgreSQL so the agent can read and write real database data through tool calls. You need this when your agent has to answer questions from live business data, run SQL safely, or persist results instead of working from static prompts.

What You'll Need

• •Python 3.10+
• •A running PostgreSQL instance
• •A database, user, and password with access to that database
• •An OpenAI API key set as OPENAI_API_KEY
• •Packages:
  • •pyautogen
  • •psycopg2-binary
  • •python-dotenv optional, if you want .env loading
• •A table to query, for example customers or orders

Install the dependencies:

pip install pyautogen psycopg2-binary python-dotenv

Step-by-Step

1. •Set up your environment variables and verify that Python can see them. Keep credentials out of source code; for production work, use a secrets manager instead of a .env file.

import os
from dotenv import load_dotenv

load_dotenv()

required = ["OPENAI_API_KEY", "PGHOST", "PGPORT", "PGDATABASE", "PGUSER", "PGPASSWORD"]
missing = [key for key in required if not os.getenv(key)]

if missing:
    raise RuntimeError(f"Missing environment variables: {', '.join(missing)}")

print("Environment is ready.")

2. •Create a small PostgreSQL helper module that exposes safe read/write functions. AutoGen will call these functions as tools, so keep them focused and deterministic.

import os
import psycopg2
from psycopg2.extras import RealDictCursor

def get_conn():
    return psycopg2.connect(
        host=os.environ["PGHOST"],
        port=os.environ["PGPORT"],
        dbname=os.environ["PGDATABASE"],
        user=os.environ["PGUSER"],
        password=os.environ["PGPASSWORD"],
    )

def fetch_customers(limit: int = 5):
    with get_conn() as conn:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(
                "SELECT id, name, email FROM customers ORDER BY id LIMIT %s",
                (limit,),
            )
            return cur.fetchall()

3. •Add a write helper only if you need the agent to persist data. In banking and insurance workflows, this is where you should be strict about validation and scope; do not expose generic SQL execution unless you absolutely need it.

def add_audit_log(actor: str, action: str):
    with get_conn() as conn:
        with conn.cursor() as cur:
            cur.execute(
                """
                INSERT INTO audit_log (actor, action)
                VALUES (%s, %s)
                RETURNING id
                """,
                (actor, action),
            )
            new_id = cur.fetchone()[0]
            conn.commit()
            return {"id": new_id, "actor": actor, "action": action}

4. •Register those Python functions as AutoGen tools and create an assistant that can call them. This uses the standard AssistantAgent plus a local executor pattern for function calling.

from autogen import AssistantAgent

llm_config = {
    "model": "gpt-4o-mini",
    "api_key": os.environ["OPENAI_API_KEY"],
}

assistant = AssistantAgent(
    name="postgres_assistant",
    llm_config=llm_config,
    system_message=(
        "You are a data assistant. "
        "Use fetch_customers to inspect customer rows. "
        "Use add_audit_log only when asked to record an audit event."
    ),
)

tools = {
    "fetch_customers": fetch_customers,
    "add_audit_log": add_audit_log,
}

5. •Run a simple conversation loop and dispatch tool calls yourself. This keeps the integration explicit and easy to debug before you move it behind an API or into a larger agent workflow.

from autogen.agentchat.contrib.function_calling import register_function

register_function(
    fetch_customers,
    caller=assistant,
    executor=assistant,
    name="fetch_customers",
    description="Fetch customer records from PostgreSQL.",
)

register_function(
    add_audit_log,
    caller=assistant,
    executor=assistant,
    name="add_audit_log",
    description="Insert an audit log row into PostgreSQL.",
)

reply = assistant.generate_reply(
    messages=[{"role": "user", "content": "Show me 3 customers."}]
)
print(reply)

Testing It

Run the script against a database that actually contains the tables referenced above. If customers or audit_log does not exist, create them first or change the SQL to match your schema.

A good first test is a read-only prompt like “Show me 3 customers.” You should see either structured rows returned or a natural-language summary based on those rows.

Then test the write path with something explicit like “Record an audit event for user_jane logging in.” Check that a new row appears in audit_log.

If the agent fails to call the tool, inspect three things first: your model name, your OpenAI key, and whether AutoGen is installed at a version that supports function registration in your environment.

Next Steps

• •Add schema-aware validation before any write operation.
• •Wrap PostgreSQL access in a repository layer so your agent never sees raw connection logic.
• •Move from single-agent function calls to a multi-agent setup where one agent plans and another executes database operations.

Keep learning

• •The complete AI Agents Roadmap — my full 8-step breakdown
• •Free: The AI Agent Starter Kit — PDF checklist + starter code
• •Work with me — I build AI for banks and insurance companies

By Cyprian Aarons, AI Consultant at Topiax.