MCP PyODBC Server

MCP Server ODBC via PyODBC

A lightweight MCP (Model Context Protocol) server for ODBC built with FastAPI and pyodbc. This server is compatible with Virtuoso DBMS and other DBMS backends that has ODBC driver.

Features

• Get Schemas: Fetch and list all schema names from the connected database.
• Get Tables: Retrieve table information for specific schemas or all schemas.
• Describe Table: Generate a detailed description of table structures, including:
  • Column names and data types
  • Nullable attributes
  • Primary and foreign keys
• Search Tables: Filter and retrieve tables based on name substrings.
• Execute Stored Procedures: In the case of Virtuoso, execute stored procedures and retrieve results.
• Execute Queries:
  • JSONL result format: Optimized for structured responses.
  • Markdown table format: Ideal for reporting and visualization.

Prerequisites

1. Install uv:
   pip install uv
   Or use Homebrew:
   brew install uv
2. unixODBC Runtime Environment Checks:
3. Check installation configuration (i.e., location of key INI files) by running: odbcinst -j
4. List available data source names by running: odbcinst -q -s
5. ODBC DSN Setup: Configure your ODBC Data Source Name (~/.odbc.ini) for the target database. Example for Virtuoso DBMS:
   [VOS] Description = OpenLink Virtuoso Driver = /path/to/virtodbcu_r.so Database = Demo Address = localhost:1111 WideAsUTF16 = Yes

Installation

Clone this repository:

Environment Variables

Update your .envby overriding the defaults to match your preferences

Configuration

For Claude Desktop users: Add the following to claude_desktop_config.json:

Usage

Tools Provided

After successful installation, the following tools will be available to MCP client applications.

Overview

Detailed Description

• 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).

Troubleshooting

For easier troubleshooting:

1. Install the MCP Inspector:
   npm install -g @modelcontextprotocol/inspector
2. Start the inspector:
   npx @modelcontextprotocol/inspector uv --directory /path/to/mcp-pyodbc-server run mcp-pyodbc-server

Access the provided URL to troubleshoot server interactions.