MCP Server ODBC via SQLAlchemy

一个使用 FastAPI、pyodbc 和 SQLAlchemy 构建的轻量级 MCP（Model Context Protocol）ODBC 服务器。该服务器与 Virtuoso DBMS 及其他实现了 SQLAlchemy 提供者的数据库管理系统后端兼容。

功能

• 获取模式：从连接的数据库中获取并列出所有模式名称。
• 获取表：检索特定模式或所有模式的表信息。
• 描述表：生成表结构的详细描述，包括：
  • 列名和数据类型
  • 可空属性
  • 主键和外键
• 搜索表：根据名称子字符串过滤并检索表。
• 执行存储过程：在 Virtuoso 的情况下，执行存储过程并检索结果。
• 执行查询：
  • JSONL 结果格式：优化为结构化响应。
  • Markdown 表格格式：适用于报告和可视化。

前提条件

1. 安装 uv：

   pip install uv
   

   或者使用 Homebrew：

   brew install uv
   

2. unixODBC 运行时环境检查：

3. 通过运行 odbcinst -j 检查安装配置（即关键 INI 文件的位置）。

4. 通过运行 odbcinst -q -s 列出可用的数据源名称。

5. ODBC DSN 设置：为目标数据库配置您的 ODBC 数据源名称 (~/.odbc.ini)。Virtuoso DBMS 的示例：

   [VOS]
   Description = OpenLink Virtuoso
   Driver = /path/to/virtodbcu_r.so
   Database = Demo
   Address = localhost:1111
   WideAsUTF16 = Yes
   

6. SQLAlchemy URL 绑定：使用以下格式：

   virtuoso+pyodbc://user:password@VOS
   

安装

克隆此仓库：

git clone https://github.com/OpenLinkSoftware/mcp-sqlalchemy-server.git
cd mcp-sqlalchemy-server

环境变量

通过覆盖默认值更新您的 .env 以匹配您的偏好

ODBC_DSN=VOS
ODBC_USER=dba
ODBC_PASSWORD=dba
API_KEY=xxx

配置

对于 Claude Desktop 用户： 将以下内容添加到 claude_desktop_config.json 中：

{
  "mcpServers": {
    "my_database": {
      "command": "uv",
      "args": ["--directory", "/path/to/mcp-sqlalchemy-server", "run", "mcp-sqlalchemy-server"],
      "env": {
        "ODBC_DSN": "dsn_name",
        "ODBC_USER": "username",
        "ODBC_PASSWORD": "password",
        "API_KEY": "sk-xxx"
      }
    }
  }
}

使用

数据库管理系统 (DBMS) 连接 URL

以下是已使用此 mcp-server 测试过的 DBMS 系统的 pyodbc URL 示例。

一旦连接成功，您可以通过 Claude 与您的 WhatsApp 联系人互动，在 WhatsApp 对话中利用 Claude 的 AI 能力。

提供的工具

概览

详细说明

注意：原文中没有具体“详细说明”部分的内容，因此这里保持标题不变，但内容为空。如果你有更多关于这些功能的具体信息需要翻译，请补充提供。

• podbc_get_schemas

  • Retrieve and return a list of all schema names from the connected database.
  • Input parameters:
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns a JSON string array of schema names.

• podbc_get_tables

  • Retrieve and return a list containing information about tables in a specified schema. If no schema is provided, uses the connection's default schema.
  • Input parameters:
    • schema (string, optional): Database schema to filter tables. Defaults to connection default.
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns a JSON string containing table information (e.g., TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE).

• podbc_filter_table_names

  • Filters and returns information about tables whose names contain a specific substring.
  • Input parameters:
    • q (string, required): The substring to search for within table names.
    • schema (string, optional): Database schema to filter tables. Defaults to connection default.
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns a JSON string containing information for matching tables.

• podbc_describe_table

  • Retrieve and return detailed information about the columns of a specific table.
  • Input parameters:
    • schema (string, required): The database schema name containing the table.
    • table (string, required): The name of the table to describe.
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns a JSON string describing the table's columns (e.g., COLUMN_NAME, TYPE_NAME, COLUMN_SIZE, IS_NULLABLE).

• podbc_query_database

  • Execute a standard SQL query and return the results in JSON format.
  • Input parameters:
    • query (string, required): The SQL query string to execute.
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns query results as a JSON string.

• podbc_query_database_md

  • Execute a standard SQL query and return the results formatted as a Markdown table.
  • Input parameters:
    • query (string, required): The SQL query string to execute.
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns query results as a Markdown table string.

• podbc_query_database_jsonl

  • Execute a standard SQL query and return the results in JSON Lines (JSONL) format (one JSON object per line).
  • Input parameters:
    • query (string, required): The SQL query string to execute.
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns query results as a JSONL string.

• podbc_spasql_query

  • Execute a SPASQL (SQL/SPARQL hybrid) query return results. This is a Virtuoso-specific feature.
  • Input parameters:
    • query (string, required): The SPASQL query string.
    • max_rows (number, optional): Maximum number of rows to return. Defaults to 20.
    • timeout (number, optional): Query timeout in milliseconds. Defaults to 30000.
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns the result from the underlying stored procedure call (e.g., Demo.demo.execute_spasql_query).

• podbc_sparql_query

  • Execute a SPARQL query and return results. This is a Virtuoso-specific feature.
  • Input parameters:
    • query (string, required): The SPARQL query string.
    • format (string, optional): Desired result format. Defaults to 'json'.
    • timeout (number, optional): Query timeout in milliseconds. Defaults to 30000.
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns the result from the underlying function call (e.g., "UB".dba."sparqlQuery").

• podbc_virtuoso_support_ai

  • Utilizes a Virtuoso-specific AI Assistant function, passing a prompt and optional API key. This is a Virtuoso-specific feature.
  • Input parameters:
    • prompt (string, required): The prompt text for the AI function.
    • api_key (string, optional): API key for the AI service. Defaults to "none".
    • user (string, optional): Database username. Defaults to "demo".
    • password (string, optional): Database password. Defaults to "demo".
    • dsn (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
  • Returns the result from the AI Support Assistant function call (e.g., DEMO.DBA.OAI_VIRTUOSO_SUPPORT_AI).

故障排除

为了更方便地进行故障排除：

1. 安装 MCP Inspector:

   npm install -g @modelcontextprotocol/inspector
   

2. 启动 inspector:

   npx @modelcontextprotocol/inspector uv --directory /path/to/mcp-sqlalchemy-server run mcp-sqlalchemy-server
   

访问提供的 URL 以排查服务器交互问题。