SQLite MCP Server

# Quick Start Guide - MCP Client for sahabat-ai ## ✅ Setup Complete! All dependencies are now installed and the MCP client is ready to use with your sahabat-ai chatbot. --- ## 🚀 Running the MCP HTTP Server ### Start the Server ```bash # Terminal 1: Start the HTTP server python http_wrapper.py # Expected output: # 🚀 Starting SQLite MCP HTTP Server... # 📚 API Documentation: http://localhost:8000/docs # 🔗 Health Check: http://localhost:8000/health # 🛠️ Tools: http://localhost:8000/tools # INFO: Uvicorn running on http://0.0.0.0:8000 ``` ### Verify Server is Running ```bash # In another terminal curl http://localhost:8000/health # Response: # { # "status": "healthy", # "database_connected": false, # "service": "SQLite MCP HTTP Wrapper" # } ``` --- ## 💬 Using the MCP Client with Your Chatbot ### Option 1: Use the Ready-Made Chatbot (Easiest) ```bash # Terminal 2: Set up credentials export SAHABAT_AI_API_KEY="your-api-key-here" export SAHABAT_AI_BASE_URL="http://your-sahabat-ai-server" # Run the chatbot python chatbot_example.py # Then chat: # You: Open the spam database # Bot: I've opened the database... # You: Show me all spam numbers # Bot: I'll query the database... ``` ### Option 2: Use in Your Python Code ```python from chatbot_example import SQLiteChatbot # Initialize chatbot = SQLiteChatbot( api_key="your-sahabat-ai-api-key", base_url="http://your-sahabat-ai-server", mcp_server_url="http://localhost:8000" ) # Chat with database response = chatbot.chat("Show me all spam numbers") print(response) # Another query response = chatbot.chat("How many KTP records are there?") print(response) ``` ### Option 3: Direct MCP Client (No sahabat-ai needed) ```python from mcp_client import MCPClient # Create client client = MCPClient("http://localhost:8000") # Open database client.open_database("./data/sample_data.db") # Query data result = client.execute_query("SELECT * FROM Spam_number LIMIT 5") print(f"Found {result.record_count} records") for row in result.rows: print(f" {row['mobileNumber']} - {row['Name']}") # Insert data insert_result = client.insert("Spam_number", { "mobileNumber": "081234567890", "Name": "Test User", "Description": "Test" }) print(f"Inserted with ID: {insert_result.lastID}") # Close database client.close_database() ``` --- ## 🧪 Test the Setup Run the test script to verify everything works: ```bash python test_setup.py ``` Expected output: ``` 🧪 Testing SQLite MCP Setup ============================================================ 1️⃣ Checking imports... ✅ SQLiteDatabase imported successfully ✅ MCPClient imported successfully ✅ openai_tools imported successfully ✅ FastAPI imported successfully 2️⃣ Testing SQLiteDatabase... ✅ Database connected. Found tables: ['Spam_number', 'KTP'] 3️⃣ Testing OpenAI tools... ✅ Generated 9 OpenAI-compatible tools 4️⃣ Testing http_wrapper... ✅ http_wrapper module imported successfully ============================================================ ✅ All setup checks passed! ``` --- ## 📊 Available Database Tools All 9 tools are available through the HTTP API: 1. **open_database** - Open/create database 2. **close_database** - Close connection 3. **execute_query** - Query SELECT statements 4. **insert** - Insert rows 5. **update** - Update rows 6. **delete** - Delete rows 7. **create_table** - Create tables 8. **list_tables** - List all tables 9. **get_table_schema** - Get table structure --- ## 📚 Sample Data The database comes with sample data: - **Spam_number table**: 100 spam phone numbers - **KTP table**: 100 Indonesian citizen records ### Query Examples ```python client = MCPClient() client.open_database("./data/sample_data.db") # Count records result = client.execute_query("SELECT COUNT(*) as count FROM Spam_number") # Filter by description result = client.execute_query( "SELECT * FROM Spam_number WHERE Description = ?", parameters=["Scam"] ) # Group by type result = client.execute_query( "SELECT Description, COUNT(*) as count FROM Spam_number GROUP BY Description" ) # Get KTP records by religion result = client.execute_query( "SELECT * FROM KTP WHERE Religion = ?", parameters=["Islam"] ) ``` --- ## 🔧 Using with Function Calling The `openai_tools.py` module provides OpenAI-compatible function definitions: ```python from openai import OpenAI from openai_tools import get_openai_tools client = OpenAI( api_key="your-key", base_url="http://your-sahabat-ai-server" ) tools = get_openai_tools() response = client.chat.completions.create( model="sahabat-ai", messages=[ {"role": "user", "content": "Query the database for scam numbers"} ], tools=tools, tool_choice="auto" ) # Check for tool calls for tool_call in response.choices[0].message.tool_calls: print(f"Tool: {tool_call.function.name}") print(f"Args: {tool_call.function.arguments}") ``` --- ## 📡 API Endpoints Access the API directly with HTTP requests: ```bash # Health check curl http://localhost:8000/health # List tools curl http://localhost:8000/tools # Open database curl -X POST http://localhost:8000/tools/open_database \ -H "Content-Type: application/json" \ -d '{"path": "./data/sample_data.db"}' # Execute query curl -X POST http://localhost:8000/tools/execute_query \ -H "Content-Type: application/json" \ -d '{ "query": "SELECT * FROM Spam_number LIMIT 3", "parameters": [] }' # Insert data curl -X POST http://localhost:8000/tools/insert \ -H "Content-Type: application/json" \ -d '{ "table": "Spam_number", "data": { "mobileNumber": "081234567890", "Name": "Test", "Description": "Test" } }' ``` --- ## 🎯 Common Use Cases ### 1. Chatbot Query Database ```python response = chatbot.chat("How many spam numbers are in the database?") ``` ### 2. Chatbot Insert Data ```python response = chatbot.chat( "Add this spam number: 081234567890, name: John, description: Phishing" ) ``` ### 3. Chatbot Update Records ```python response = chatbot.chat("Update record 1 to status 'blocked'") ``` ### 4. Check Database Schema ```python response = chatbot.chat("Show me the schema of the KTP table") ``` ### 5. Statistics ```python response = chatbot.chat("How many people of each religion are in the database?") ``` --- ## 🐛 Troubleshooting ### Server won't start ```bash # Make sure port 8000 is not in use lsof -i :8000 # Or use a different port uvicorn http_wrapper:app --port 8001 ``` ### Can't connect to MCP server ```bash # Check server is running curl http://localhost:8000/health # Check firewall/network telnet localhost 8000 ``` ### Database not found ```python # Always open database first client = MCPClient() client.open_database("./data/sample_data.db") # Use absolute paths if needed client.open_database("/Users/supriyadi/Projects/MCP/SQL-MCP-SERVER/sqlite-mcp/data/sample_data.db") ``` ### sahabat-ai connection issues ```bash # Verify credentials export SAHABAT_AI_API_KEY="your-key" export SAHABAT_AI_BASE_URL="http://your-server:port" # Test with simple query python -c "from openai import OpenAI; \ client = OpenAI(api_key='$SAHABAT_AI_API_KEY', base_url='$SAHABAT_AI_BASE_URL'); \ print(client.chat.completions.create(model='sahabat-ai', messages=[{'role': 'user', 'content': 'test'}]))" ``` --- ## 📖 Full Documentation For complete documentation, see: - [CHATBOT_INTEGRATION_GUIDE.md](CHATBOT_INTEGRATION_GUIDE.md) - Complete integration guide - [README.md](README.md) - Tool documentation - [SAMPLE_DATA_GUIDE.md](SAMPLE_DATA_GUIDE.md) - Sample data details --- ## ✨ Next Steps 1. ✅ Install dependencies: `pip install -r requirements.txt` 2. ✅ Start HTTP server: `python http_wrapper.py` 3. ✅ Run chatbot: `python chatbot_example.py` 4. ✅ Chat with database! That's it! Your MCP server is now connected to your sahabat-ai chatbot! 🎉