AutoGen Tutorial (Python): connecting to PostgreSQL for beginners

This tutorial shows you how to wire an AutoGen Python agent to PostgreSQL, run SQL queries safely, and return structured results the agent can reason over. You’d use this when you want an LLM-powered assistant to inspect business data, answer questions from a database, or automate reporting without hand-rolling every query.

What You'll Need

• •Python 3.10+
• •A running PostgreSQL instance
• •A PostgreSQL database, user, and password
• •OpenAI API key set as OPENAI_API_KEY
• •Packages:
  • •pyautogen
  • •psycopg2-binary
  • •python-dotenv optional, if you want .env support
• •Basic AutoGen knowledge:
  • •AssistantAgent
  • •UserProxyAgent
  • •tool/function calling

Step-by-Step

1. •Install the dependencies and make sure your environment can talk to both OpenAI and PostgreSQL. If you’re using a virtual environment, activate it first.

pip install pyautogen psycopg2-binary python-dotenv

2. •Set your connection details and API key. For local development, environment variables are enough and keep credentials out of code.

export OPENAI_API_KEY="your-openai-key"
export PGHOST="localhost"
export PGPORT="5432"
export PGDATABASE="sales_db"
export PGUSER="postgres"
export PGPASSWORD="postgres_password"

3. •Create a small database access layer. Keep the SQL execution in one function so the agent only calls a controlled interface instead of building raw connections everywhere.

import os
import json
import psycopg2
from psycopg2.extras import RealDictCursor

def run_sql(query: str):
    conn = psycopg2.connect(
        host=os.environ["PGHOST"],
        port=os.environ["PGPORT"],
        dbname=os.environ["PGDATABASE"],
        user=os.environ["PGUSER"],
        password=os.environ["PGPASSWORD"],
    )
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(query)
            if cur.description is None:
                conn.commit()
                return json.dumps({"status": "ok", "rows_affected": cur.rowcount})
            rows = cur.fetchall()
            return json.dumps(rows, default=str)
    finally:
        conn.close()

4. •Register that function as an AutoGen tool and create your agents. The assistant will decide when to call the tool; the user proxy executes it locally.

import autogen

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

llm_config = {"config_list": config_list, "temperature": 0}

assistant = autogen.AssistantAgent(
    name="sql_assistant",
    llm_config=llm_config,
    system_message=(
        "You are a helpful data assistant. "
        "Use run_sql to query PostgreSQL. "
        "Only write SELECT queries unless explicitly asked otherwise."
    ),
)

user_proxy = autogen.UserProxyAgent(
    name="user_proxy",
    human_input_mode="NEVER",
    code_execution_config=False,
)

user_proxy.register_function(
    function_map={
        "run_sql": run_sql,
    }
)

5. •Give the assistant a clear task and let it query the database. This example asks for a simple aggregate query that is easy to verify.

message = """
Find total revenue by region from the orders table.
Return the top 5 regions ordered by revenue descending.
Use SQL only.
"""

user_proxy.initiate_chat(
    assistant,
    message=message,
)

6. •If you want a more controlled setup, expose only read-only queries at first. That means creating a DB user with SELECT permissions and rejecting anything except SELECT in your tool wrapper.

def run_readonly_sql(query: str):
    q = query.strip().lower()
    if not q.startswith("select"):
        return json.dumps({"error": "Only SELECT queries are allowed"})
    return run_sql(query)

user_proxy.register_function(
    function_map={
        "run_sql": run_readonly_sql,
    }
)

Testing It

Run the script against a database that has at least one table with data, such as orders. The easiest test is to ask for something deterministic like row counts or totals, because you can compare the result directly in psql or pgAdmin.

If the agent responds with a tool call and then returns rows, your wiring is correct. If it fails immediately, check these first:

• •OPENAI_API_KEY is set
• •PostgreSQL host/port/database/user/password are correct
• •The target table exists and the user has permission to read it

For debugging, test the SQL function directly before involving AutoGen:

print(run_sql("SELECT COUNT(*) AS total_orders FROM orders;"))

If that works but AutoGen does not, the issue is usually in agent registration or model configuration rather than PostgreSQL itself.

Next Steps

• •Add schema introspection so the agent can list tables and columns before writing SQL.
• •Wrap writes behind explicit approval so updates and deletes require human confirmation.
• •Move from raw SQL strings to parameterized queries for any user-supplied filters.

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.