使用说明
🤖 用于薪酬比较的会话式SQL AI助手
该项目实现了一个复杂、安全和对话式的人工智能助手，允许用户使用自然语言查询薪酬比较数据库。它利用具有模型中心协议（MCP）的客户端-服务器架构来确保数据库交互被安全地沙盒化。
应用程序的核心是 LangGraph REACT试剂 在客户端中运行，它智能地使用本地和远程工具的组合来回答用户查询。
✨ 特性
- 会话界面：用户可以用简单的英语提问，代理会维护对话历史记录，以便进行上下文感知的跟进。
- 自然语言到SQL：自动将用户问题转换为安全的只读SQL查询。
- [引用_开始]安全、沙盒式执行：使用客户端-服务器模型，其中服务器专门处理数据库连接和查询执行，防止客户端或LLM直接访问数据库。
- [引用_开始]混合工具架构：通过MCP将本地LLM驱动的工具（例如用于详细摘要）与远程服务器端工具（例如，用于查询执行和简要摘要）相结合。
- 安全第一：系统提示执行严格的安全规则，例如仅执行
SELECT查询，将行限制为100，并防止数据修改。 - [引用_开始]基于环境的配置：所有敏感信息（如API密钥和数据库凭据）都通过
.env文件。
🏗️ 建筑
[cite_start]应用程序分为两个主要组件，通过 stdio 使用以模型为中心的协议（MCP）。
-
client.py（大脑）:- 管理面向用户的命令行界面。
- 托管LangGraph代理，该代理协调整个工作流。
- 连接到OpenAI，利用LLM进行推理并生成用户友好的响应。
- 决定使用哪种工具（例如，安全SQL代理或摘要器）。
- 通过MCP客户端会话与服务器通信以调用远程工具。
-
[引用_开始]
server.py（安全工作者）:- 作为客户端管理的后台进程运行。
- [cite_start]建立并保持与PostgreSQL数据库的唯一连接。
- [cite_start]公开一组安全工具（例如。，
run_query,summarize_result)通过MCP服务器。 - [cite_start]执行从客户端收到的SQL查询并返回结果，确保没有直接的数据库暴露。
🚀 入门指南
按照以下说明在本地设置和运行项目。
先决条件
- Python 3.9+
- PostgreSQL数据库正在运行且可访问。
- 一个OpenAI API密钥。
1.克隆存储库
git clone <your-repository-url>
cd <your-repository-directory>
### 2. Set Up a Virtual Environment
It's highly recommended to use a virtual environment to manage dependencies.
**On macOS/Linux:**
```bash
python3 -m venv venv
source venv/bin/activate
**On Windows:**
```bash
python -m venv venv
.\venv\Scripts\activate
### 3. Install Dependencies
Install all the required Python packages using the `requirements.txt` file.
```bash
pip install -r requirements.txt
### 4. Configure Environment Variables
Create a file named `.env` in the root of the project directory. You can copy the example below and fill in your actual credentials.
**⚠️ Important:** Never commit your `.env` file to version control.
```ini
# .env file
# OpenAI Configuration
OPENAI_API_KEY="sk-..."
OPENAI_MODEL="gpt-4-turbo"
# PostgreSQL Database Configuration
DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="your_database_name"
DB_USER="your_database_user"
DB_PASS="your_database_password"
### 5. Run the Application
Execute the `client.py` script to start the interactive assistant. The client will automatically start and manage the `server.py` process in the background.
```bash
python client.py
-You should see a welcome message, and you can start asking questions!
SQL Agent is ready! Type your questions (or 'exit' to quit)
This agent maintains conversation history to provide context-aware responses.
## 🛠️ How It Works
This section details the inner workings of the client and server components.
---
### Client (`client.py`)
* **Agent Initialization**: `create_react_agent` from LangGraph is used to create an agent that can reason and decide which tool to use next.
* **Tool Aggregation**: It combines three types of tools to give the agent a wide range of capabilities.
* **MCP Tools**: Loaded from the server using `load_mcp_tools`, these are primarily used for database operations.
* **SQL Agent Tool**: A LangChain `SQLDatabaseToolkit` is wrapped into a single tool, `secure_sql_agent`, for generating and running SQL from natural language.
* **Hybrid Summarizer**: A custom tool, `hybrid_summarize`, can call a brief, rule-based summarizer on the server or a more detailed LLM-based summarizer on the client.
* **Conversation Memory**: `MemorySaver` keeps track of the conversation, allowing the agent to understand context from previous messages.
* **Main Loop**: The application enters a `while` loop, waiting for user input, invoking the agent to get a response, and then printing the final, user-friendly output.
---
### Server (`server.py`)
* **MCP Server**: A `FastMCP` instance is created to listen for tool invocation requests from the client.
* **Database Connection**: The server establishes a persistent connection to the PostgreSQL database using `psycopg2`.
* **`@mcp.tool()` Decorator**: Functions like `run_query` and `summarize_result` are exposed as tools that the client can call remotely by using this decorator.
* **Safe Query Execution**: The `run_query` tool is designed to be safe. It can inject additional filters (like `project_name`) and handles the entire query lifecycle, returning only the results and not the connection object.
## Example Usage
Here is an example of a conversation with the assistant:
```text
SQL Agent is ready! Type your questions (or 'exit' to quit)
This agent maintains conversation history to provide context-aware responses.
You: What are the average salaries for Senior Software Engineers?
================================ Human Message =================================
What are the average salaries for Senior Software Engineers?
================================== AI Message ===================================
I can help with that. The average salary for a Senior Software Engineer is $155,000 per year, based on the data from our latest pay comparison analysis.
You: How does that compare to a Staff Engineer?
================================ Human Message =================================
How does that compare to a Staff Engineer?
================================== AI Message ===================================
A Staff Engineer has an average salary of $190,000 per year, which is approximately 22.6% higher than the average for a Senior Software Engineer.````
