Building an MCP Server for SQLite Database Interaction

Complete Code Implementation (sqlite_mcpserver.py)

Below is the complete implementation of the MCP server for SQLite database interaction:

import sqlite3
from mcp.server.fastmcp import FastMCP

mcp = FastMCP("SQLite Explorer")


@mcp.resource("schema://main")
def get_schema() -> str:
    """提供資料庫的模式資訊作為資源"""
    conn = sqlite3.connect("database.db")
    schema = conn.execute("SELECT sql FROM sqlite_master WHERE type='table'").fetchall()
    return "\n".join(sql[0] for sql in schema if sql[0])


@mcp.tool()
def query_data(sql: str) -> str:
    """安全地執行 SQL 查詢"""
    conn = sqlite3.connect("database.db")
    try:
        result = conn.execute(sql).fetchall()
        return "\n".join(str(row) for row in result)
    except Exception as e:
        return f"錯誤：{str(e)}"
            

Code Explanation

This implementation:

• Creates a new FastMCP server named "SQLite Explorer"
• Defines a resource function get_schema() that returns the database schema information
• Defines a tool function query_data() that executes SQL queries and returns the results
• Includes error handling to safely manage invalid queries

English Summary

This tutorial demonstrates how to create an MCP (Model Control Protocol) server that interacts with a local SQLite database. The server provides two main functionalities:

Database Structure

The example uses a simple SQLite database with a table called "demo" containing two fields: "idno" (ID number) and a name field.

Server Functionalities

1. Resource Function (Schema): Lists all tables and their schema from the SQLite database
2. Tool Function (Query Data): Allows running SQL queries against the database and returns the results

Implementation

The code requires two main modules:

import sqlite3
import fastmcp  # For simplified MCP server creation and management
            

The implementation follows these steps:

1. Create an MCP server instance using FASTMCP
2. Define a resource function to retrieve database schema information
3. Define a tool function to execute SQL queries
4. Handle potential errors in query execution

Usage Examples

The tutorial demonstrates:

• Listing database resources to view available tables and their schema
• Executing SQL queries like SELECT * FROM demo
• Running queries with conditions like SELECT * FROM demo WHERE IDNO = 3
• Error handling for invalid queries

This implementation enables large language models to interact directly with local databases, providing a seamless way to integrate database functionality with AI tools.

中文摘要

本教程演示如何創建一個與本地SQLite數據庫交互的MCP（Model Control Protocol）服務器。該服務器提供兩個主要功能：

數據庫結構

示例使用一個簡單的SQLite數據庫，其中包含一個名為"demo"的表，該表包含兩個字段："idno"（ID號碼）和姓名字段。

服務器功能

1. 資源功能（Schema）：列出SQLite數據庫中的所有表及其模式結構
2. 工具功能（Query Data）：允許對數據庫運行SQL查詢並返回結果

實現方法

代碼需要兩個主要模塊：

import sqlite3
import fastmcp  # 用於簡化MCP服務器創建和管理
            

實現步驟如下：

1. 使用FASTMCP創建MCP服務器實例
2. 定義資源函數以檢索數據庫模式信息
3. 定義工具函數以執行SQL查詢
4. 處理查詢執行中的潛在錯誤

使用示例

教程演示了：

• 列出數據庫資源以查看可用表及其模式
• 執行SQL查詢，如 SELECT * FROM demo
• 運行帶條件的查詢，如 SELECT * FROM demo WHERE IDNO = 3
• 處理無效查詢的錯誤

這種實現使大型語言模型能夠直接與本地數據庫交互，提供了一種將數據庫功能與AI工具無縫集成的方式。