{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "ymsq1Lw0VEqT" }, "source": [ "# CAMEL Cookbook: SQL MCP Server" ] }, { "cell_type": "markdown", "metadata": { "id": "7V3aV16AmY0K" }, "source": [ "You can also check this cookbook in [Google Colab](https://colab.research.google.com/drive/1ssaxacH4ahbFcv0fz6azy7hX9yjEXYro?usp=sharing)." ] }, { "cell_type": "markdown", "metadata": { "id": "UvHRdXwflAz-" }, "source": [ "
\n", " \n", " \n", " \n", "⭐ Star us on [*Github*](https://github.com/camel-ai/camel), join our [*Discord*](https://discord.camel-ai.org) or follow our [*X*](https://x.com/camelaiorg)\n", "
" ] }, { "cell_type": "markdown", "metadata": { "id": "G5gE04UuPUWj" }, "source": [ "This cookbook demonstrates how to use CAMEL AI agents to interact with an SQL database using natural language. We'll achieve this by connecting CAMEL to a **local SQL Model Control Protocol (MCP) server** that you provide. This setup allows the CAMEL agent to leverage MCP for database operations like querying data, listing tables, and describing schemas, all triggered by conversational prompts.\n", "\n", "**Key Learnings:**\n", "* Understanding the role of MCP in CAMEL for tool usage.\n", "* Setting up the CAMEL environment and necessary API keys.\n", "* Preparing a local Python script (`sql_server_mcp.py`) to act as your SQL MCP server.\n", "* Configuring CAMEL to connect to and utilize this local MCP server.\n", "* Creating a sample SQLite database.\n", "* Interacting with the database using natural language queries through a CAMEL agent.\n", "\n", "This approach focuses on using CAMEL with an MCP server that runs as a separate Python process, managed by CAMEL's `_MCPServer` utility." ] }, { "cell_type": "markdown", "metadata": { "id": "0J0_iW-YVcq2" }, "source": [ "## 📦 Installation" ] }, { "cell_type": "markdown", "metadata": { "id": "7p-JjpyNVcCT" }, "source": [ "First, install the CAMEL package with all its dependencies:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%pip install \"camel-ai[all]==0.2.57\"" ] }, { "cell_type": "markdown", "metadata": { "id": "lfNvFbhD6o8B" }, "source": [ "## 🔑 Setting Up API Keys" ] }, { "cell_type": "markdown", "metadata": { "id": "jqV12oQfQTyl" }, "source": [ "This cookbook uses OpenRouter as the model provider, which gives us access to various LLMs including Claude and Gemini. You'll need an OpenRouter API key.\n", "\n", "1. Sign up at [OpenRouter](https://openrouter.ai/)\n", "2. Get your API key from the dashboard\n", "3. The script will prompt you for the API key when running" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Required Configuration Files\n", "\n", "Before running the code, you need to set up two important configuration files in your working directory:\n", "\n", "### 1. MCP Configuration File (`mcp_config.json`)\n", "\n", "Create a file named `mcp_config.json` with the following content:\n", "\n", "```json\n", "{\n", " \"servers\": [\n", " {\n", " \"type\": \"script\",\n", " \"command\": \"python\",\n", " \"args\": [\"sql_server_mcp.py\"],\n", " \"transport\": \"stdio\"\n", " }\n", " ]\n", "}\n", "```\n", "\n", "This configuration tells CAMEL how to start and communicate with your SQL MCP server." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. SQL MCP Server Script (`sql_server_mcp.py`)\n", "\n", "Create a file named `sql_server_mcp.py` in your working directory. This script will handle all database operations:\n", "You can download and configure the script [here](https://github.com/parthshr370/MCP-Servers/blob/main/sql_server/sql_server_mcp.py)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Understanding MCP and Your Local SQL MCP Server\n", "\n", "**What is MCP?**\n", "\n", "MCP (Model Control Protocol) is a specification that allows Large Language Models (LLMs) to interact with external tools and services in a standardized way. In CAMEL, `MCPToolkit` enables agents to discover and use tools exposed by MCP-compliant servers. This cookbook focuses on using a Python-based MCP server that you'll run locally.\n", "\n", "**Your `sql_server_mcp.py` Script**\n", "\n", "For this cookbook to function, you need to have a Python script named `sql_server_mcp.py` in the **same directory** as this notebook (or where you execute the Python code derived from this markdown).\n", "\n", "This script is responsible for the direct database interactions. It should:\n", "1. Use `mcp.server.fastmcp.FastMCP` (from the `modelcontextprotocol` Python SDK) to define an MCP server instance (e.g., `mcp = FastMCP(\"sqldb\")`).\n", "2. Define Python functions for database operations, such as:\n", " * `execute_query(connection_string: str, query: str) -> str`: Executes a given SQL query.\n", " * `list_tables(connection_string: str) -> str`: Lists tables in a database.\n", " * `describe_table(connection_string: str, table_name: str) -> str`: Describes a table's schema.\n", "3. Decorate these functions with `@mcp.tool()` to expose them as tools to the LLM. Each tool should also have an `inputSchema` defined to guide the LLM on how to use it.\n", "4. Include a `main` section (`if __name__ == \"__main__\":`) that runs the MCP server (e.g., `mcp.run(transport='stdio')`).## Understanding MCP and Your Local SQL MCP Server" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "This tool handles:\n", "\n", "- Connecting to a SQLite database\n", "- Executing a SQL query\n", "- Handling different types of queries (SELECT vs. non-SELECT)\n", "- Formatting results as JSON\n", "- Error handling\n", "\n", "The `inputSchema` defines the required parameters and provides descriptions that help the LLM understand how to use the tool." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This simple tool creates empty SQLite databases that can later be populated with tables and data." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This cookbook will demonstrate how CAMEL's `_MCPServer` utility launches and communicates with your `sql_server_mcp.py` script using the Python interpreter." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Creating a Sample Database\n", "\n", "Let's create a local SQLite database (`sample.db`) that our agent will interact with. This database will contain `employees` and `departments` tables." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import os\n", "import sqlite3\n", "\n", "from camel.logger import get_logger\n", "\n", "logger = get_logger(__name__)\n", "\n", "db_path = \"sample.db\" # Database will be created in the current working directory\n", "\n", "# Remove existing database if any, to ensure a clean start\n", "if os.path.exists(db_path):\n", " os.remove(db_path)\n", " logger.info(f\"Removed existing database: {db_path}\")\n", "\n", "conn = sqlite3.connect(db_path)\n", "cursor = conn.cursor()\n", "\n", "# Create employees table\n", "cursor.execute(\"\"\"\n", "CREATE TABLE employees (\n", " id INTEGER PRIMARY KEY,\n", " name TEXT NOT NULL,\n", " department TEXT,\n", " salary REAL,\n", " hire_date TEXT\n", ")\n", "\"\"\")\n", "logger.info(\"Created 'employees' table.\")\n", "\n", "# Create departments table\n", "cursor.execute(\"\"\"\n", "CREATE TABLE departments (\n", " id INTEGER PRIMARY KEY,\n", " name TEXT NOT NULL,\n", " budget REAL,\n", " location TEXT\n", ")\n", "\"\"\")\n", "logger.info(\"Created 'departments' table.\")\n", "\n", "# Insert sample employee data\n", "employees_data = [\n", " (1, 'John Doe', 'Engineering', 85000.00, '2020-01-15'),\n", " (2, 'Jane Smith', 'Marketing', 75000.00, '2019-05-20'),\n", " (3, 'Bob Johnson', 'Engineering', 95000.00, '2018-11-10'),\n", " (4, 'Alice Brown', 'HR', 65000.00, '2021-03-05'),\n", " (5, 'Charlie Davis', 'Engineering', 90000.00, '2020-08-12')\n", "]\n", "cursor.executemany(\"INSERT INTO employees VALUES (?, ?, ?, ?, ?)\", employees_data)\n", "logger.info(f\"Inserted {len(employees_data)} records into 'employees' table.\")\n", "\n", "# Insert sample department data\n", "departments_data = [\n", " (1, 'Engineering', 1000000.00, 'Building A'),\n", " (2, 'Marketing', 500000.00, 'Building B'),\n", " (3, 'HR', 300000.00, 'Building A'),\n", " (4, 'Finance', 600000.00, 'Building C')\n", "]\n", "cursor.executemany(\"INSERT INTO departments VALUES (?, ?, ?, ?)\", departments_data)\n", "logger.info(f\"Inserted {len(departments_data)} records into 'departments' table.\")\n", "\n", "conn.commit()\n", "conn.close()\n", "logger.info(f\"Sample database '{db_path}' created and populated successfully.\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Creating the CAMEL Agent\n", "\n", "Now let's create our CAMEL agent that will interact with the database. Save this as `mcp_camel.py`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import asyncio\n", "import os\n", "import sys\n", "from getpass import getpass\n", "from pathlib import Path\n", "\n", "# CAMEL AI Imports\n", "from camel.agents import ChatAgent\n", "from camel.logger import get_logger\n", "from camel.messages import BaseMessage\n", "from camel.models import ModelFactory\n", "from camel.toolkits import MCPToolkit\n", "from camel.types import ModelPlatformType, RoleType\n", "\n", "logger = get_logger(__name__)\n", "\n", "async def main():\n", " # Path to your local MCP server script\n", " server_script_path = Path.cwd() / \"sql_server_mcp.py\"\n", " if not server_script_path.exists():\n", " logger.error(f\"MCP server script not found at: {server_script_path}\")\n", " logger.error(\"Please create 'sql_server_mcp.py' or update the path.\")\n", " return\n", "\n", " # Path to your SQLite database\n", " db_path = Path.cwd() / \"sample.db\"\n", " if not db_path.exists():\n", " logger.error(f\"Database not found at: {db_path}\")\n", " logger.error(\"Please run the database setup script first.\")\n", " return\n", "\n", " # Initialize MCPToolkit with config file\n", " config_file_path = Path.cwd() / \"mcp_config.json\"\n", " if not config_file_path.exists():\n", " logger.error(f\"MCP config file not found at: {config_file_path}\")\n", " return\n", " \n", " mcp_toolkit = MCPToolkit(config_path=str(config_file_path))\n", " \n", " tools = None\n", " async with mcp_toolkit.connection() as toolkit_connection:\n", " tools = toolkit_connection.get_tools()\n", " \n", " # Get API key securely from user input\n", " openrouter_api_key = getpass('Enter your OpenRouter API key: ')\n", " if not openrouter_api_key:\n", " logger.error(\"API key is required to proceed.\")\n", " return\n", "\n", " try:\n", " model = ModelFactory.create(\n", " model_platform=ModelPlatformType.OPENROUTER,\n", " model_type=\"anthropic/claude-3.7-sonnet\",\n", " api_key=openrouter_api_key,\n", " model_config_dict={\n", " \"temperature\": 0.2,\n", " \"max_tokens\": 2048,\n", " }\n", " )\n", " logger.info(\"Model configured successfully.\")\n", "\n", " # define the system message in detail\n", " system_content = (\n", " f\"You are a helpful SQL assistant with access to MCP tools for database operations. \"\n", " f\"The target database is located at: {db_path}. \"\n", " \"Available tools:\\n\"\n", " \"1. execute_query(connection_string, query) - Execute SQL queries\\n\"\n", " \"2. list_tables(connection_string) - List all tables\\n\"\n", " \"3. describe_table(connection_string, table_name) - Get table schema\\n\"\n", " \"4. get_table_row_count(connection_string, table_name) - Count rows\\n\"\n", " \"\\nWhen using these tools:\\n\"\n", " \"- Always use the full path to the database as connection_string\\n\"\n", " \"- Parse and display the JSON responses from the tools\\n\"\n", " \"- Handle any error messages in the responses\\n\"\n", " \"- For listing data, first get tables then query each one\\n\"\n", " \"\\nNever write raw SQL without using the tools to execute it.\"\n", " )\n", "\n", " system_message = BaseMessage(\n", " role_name=\"SQL Assistant\",\n", " role_type=RoleType.ASSISTANT,\n", " meta_dict={\"task\": \"SQL Database Operations\"},\n", " content=system_content\n", " )\n", "\n", " agent = ChatAgent(\n", " system_message=system_message,\n", " model=model,\n", " tools=tools\n", " )\n", " agent.reset()\n", "\n", " # Example query - you can modify this or make it interactive\n", " user_question = \"What tables are in the database and what's in them?\"\n", " \n", " logger.info(f\"\\n>>> User: {user_question}\")\n", " \n", " response = await agent.astep(user_question)\n", " \n", " if response and response.msgs:\n", " agent_reply = response.msgs[0].content\n", " print(f\"<<< Agent: {agent_reply}\")\n", " else:\n", " print(\"<<< Agent: No response received from the model.\")\n", " logger.error(\"Response object or messages were empty\")\n", " \n", " print(\"\\nScript finished.\")\n", "\n", " except Exception as e:\n", " logger.error(f\"An error occurred: {str(e)}\")\n", " print(f\"\\nError: {str(e)}\")\n", "\n", "\n", "if __name__ == \"__main__\":\n", " if sys.platform == \"win32\" and sys.version_info >= (3, 8):\n", " asyncio.set_event_loop_policy(asyncio.WindowsSelectorEventLoopPolicy())\n", " asyncio.run(main())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Running the Example\n", "\n", "1. Make sure you have all three required files in your directory:\n", " - `mcp_config.json`\n", " - `sql_server_mcp.py`\n", " - `mcp_camel.py`\n", "\n", "2. Create and populate the database by running the database setup code\n", "\n", "3. Run the CAMEL agent:\n", " ```bash\n", " python mcp_camel.py\n", " ```\n", "\n", "4. When prompted, enter your OpenRouter API key\n", "\n", "The agent will then:\n", "1. Connect to the local MCP server\n", "2. Use the provided tools to interact with the database\n", "3. Display the results in a human-readable format" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example Output - \n", "Enter your OpenRouter API key: \n", "<<< Agent: I'll help you explore the database by first listing all tables and then examining their contents.\n", "\n", "Let's start by listing the tables in the database:\n", "\n", "```python\n", "list_tables(\"sample.db\")\n", "```\n", "\n", "Based on the response, the database contains the following tables:\n", "- customers\n", "- orders\n", "- products\n", "\n", "Now, let's examine the schema of each table to understand their structure:\n", "\n", "```python\n", "describe_table(\"sample.db\", \"customers\")\n", "```\n", "\n", "The customers table has the following columns:\n", "- id (INTEGER): Primary key\n", "- name (TEXT): Customer name\n", "- email (TEXT): Customer email\n", "- address (TEXT): Customer address\n", "\n", "```python\n", "describe_table(\"sample.db\", \"orders\")\n", "```\n", "\n", "The orders table has the following columns:\n", "- id (INTEGER): Primary key\n", "- customer_id (INTEGER): Foreign key referencing customers\n", "- product_id (INTEGER): Foreign key referencing products\n", "- quantity (INTEGER): Order quantity\n", "- order_date (TEXT): Date of the order\n", "\n", "```python\n", "describe_table(\"sample.db\", \"products\")\n", "```\n", "\n", "The products table has the following columns:\n", "- id (INTEGER): Primary key\n", "- name (TEXT): Product name\n", "- price (REAL): Product price\n", "- category (TEXT): Product category\n", "\n", "\n", "To summarize:\n", "1. The database contains 3 tables: customers, products, and orders\n", "2. The customers table has 5 customers with their contact information\n", "3. The products table has 6 products with pricing and category information\n", "4. The orders table has 10 orders linking customers to products with quantity and date information\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example Queries\n", "\n", "You can modify the `user_question` in `mcp_camel.py` to ask different questions, such as:\n", "- \"What tables are in the database?\"\n", "- \"Show me all customers and their orders\"\n", "- \"How many products do we have in stock?\"\n", "- \"List all orders with their items and total amounts\"" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Conclusion\n", "\n", "In this cookbook, you've learned how to:\n", "- Set up a complete MCP-based database interaction system\n", "- Create and configure the necessary files (`mcp_config.json` and `sql_server_mcp.py`)\n", "- Build a CAMEL agent that can understand and execute database operations\n", "- Use OpenRouter to access powerful language models\n", "- Handle database operations safely through MCP tools\n", "\n", "This pattern can be extended to other types of databases or services by modifying the MCP server implementation while keeping the same CAMEL agent interface.\n" ] }, { "cell_type": "markdown", "metadata": { "id": "s6Det-fcMb9A" }, "source": [ "That's everything: Got questions about 🐫 CAMEL-AI? Join us on [Discord](https://discord.camel-ai.org)! Whether you want to share feedback, explore the latest in multi-agent systems, get support, or connect with others on exciting projects, we’d love to have you in the community! 🤝\n", "\n", "Check out some of our other work:\n", "\n", "1. 🐫 Creating Your First CAMEL Agent [free Colab](https://docs.camel-ai.org/cookbooks/create_your_first_agent.html)\n", "\n", "2. Graph RAG Cookbook [free Colab](https://colab.research.google.com/drive/1uZKQSuu0qW6ukkuSv9TukLB9bVaS1H0U?usp=sharing)\n", "\n", "3. 🧑‍⚖️ Create A Hackathon Judge Committee with Workforce [free Colab](https://colab.research.google.com/drive/18ajYUMfwDx3WyrjHow3EvUMpKQDcrLtr?usp=sharing)\n", "\n", "4. 🔥 3 ways to ingest data from websites with Firecrawl & CAMEL [free Colab](https://colab.research.google.com/drive/1lOmM3VmgR1hLwDKdeLGFve_75RFW0R9I?usp=sharing)\n", "\n", "5. 🦥 Agentic SFT Data Generation with CAMEL and Mistral Models, Fine-Tuned with Unsloth [free Colab](https://colab.research.google.com/drive/1lYgArBw7ARVPSpdwgKLYnp_NEXiNDOd-?usp=sharingg)\n", "\n", "Thanks from everyone at 🐫 CAMEL-AI\n", "\n", "\n", "
\n", " \n", " \n", " \n", "⭐ Star us on [*Github*](https://github.com/camel-ai/camel), join our [*Discord*](https://discord.camel-ai.org) or follow our [*X*](https://x.com/camelaiorg)\n", "
\n" ] } ], "metadata": { "colab": { "provenance": [] }, "kernelspec": { "display_name": "kratos", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.16" } }, "nbformat": 4, "nbformat_minor": 0 }