MCP Sqlite Server

🚀 SQLite MCP Server

This repository contains an MCP (Model Context Protocol) server written in Python, which connects to an SQLite database containing startup financing data. The server exposes table structures as resources, provides a read-only SQL query tool, and offers prompt templates for common data analysis tasks. It is designed to work with MCP clients and large language models (LLMs) and communicates via the STDIO protocol.

🚀 Quick Start

This MCP server uses the MCP Python SDK (with CLI extras) to achieve the following functions:

• Connect to an SQLite database (e.g., a database containing startup financing information).
• Expose the structure of specific tables as MCP resources.
• Provide a tool for executing read-only SQL queries.
• Offer prompt templates to assist language models in generating data analysis insights.
• Communicate via the STDIO protocol, reading JSON-RPC messages from standard input and writing responses to standard output.

✨ Features

• Resources

  • schema://sqlite/{table}: Returns the SQL structure of a specific table.
  • schema://sqlite/all: Returns a JSON map of all table structures.

• Tools

  • sql_query: Executes read-only SQL queries. Only SELECT statements are allowed.

• Prompts

  • analyze_table_prompt: Generates an analysis prompt for a specific table.
  • describe_query_prompt: Generates a prompt to explain an SQL query.

• STDIO Protocol

  • Reads from stdin and writes responses to stdout, making integration simple.

• Logging

  • Uses Python's logging module to track activities and debug errors.

📦 Installation

Create a Sample SQLite Database

Save the following script as create_db.py:

<同上>

Run the command:

python create_db.py

Create a Virtual Environment

python -m venv venv

Activate the environment:

• macOS/Linux:

  source venv/bin/activate
  

• Windows:

  venv\Scripts\activate
  

Install dependencies:

pip install "mcp[cli]"

Run the MCP Server

1. Save your server code as sqlite_mcp_server.py.
2. Run the server:

python sqlite_mcp_server.py

Optionally, use uv:

uv run sqlite_mcp_server.py

Install to Claude Desktop

Save the following content as install_to_claude.py and run it:

python install_to_claude.py

💻 Usage Examples

Basic Usage

1. Get the table structure from the MCP server

   mcp server --host localhost:5000 --get schema://sqlite/sales
   

2. Execute an SQL query

   mcp server --host localhost:5000 --query "SELECT * FROM sales LIMIT 5"
   

3. Generate a query description

   mcp server --host localhost:5000 --describe "SELECT COUNT(*) FROM customers WHERE state = 'CA'"
   

📚 Documentation

Test the Server Using the MCP CLI Tool

1. Install the MCP CLI:

   pip install mcp-cli
   

2. Test getting the table structure:

   mcp test --host localhost:5000 --test-get schema://sqlite/sales
   

3. Test executing an SQL query:

   mcp test --host localhost:5000 --test-query "SELECT * FROM sales LIMIT 5"
   

Configure the Log File

Add the following content to logging_config.json:

{
  "version": 1,
  "handlers": {
    "file_handler": {
      "class": "logging.FileHandler",
      "filename": "mcp_server.log",
      "level": "INFO"
    }
  },
  "root": {
    "level": "INFO",
    "handlers": ["file_handler"]
  }
}

Enable Logging

Add the following content to sqlite_mcp_server.py:

import logging
from logging.config import fileConfig

fileConfig("logging_config.json")
logger = logging.getLogger()

def main():
    # ...
    logger.info("Starting MCP Server")
    # ...

if __name__ == "__main__":
    main()

📄 License

This project is licensed under the MIT License.