mcp-pyodbc-server

--- # OpenLink MCP Server for 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 any other DBMS backend that has an 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**: When connected to 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**: ```bash pip install uv ``` Or use Homebrew: ```bash 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 (typically in `~/.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: ```bash git clone https://github.com/OpenLinkSoftware/mcp-pyodbc-server.git cd mcp-pyodbc-server ``` ## Environment Variables Update your `.env` by overriding the defaults to match your preferences. ``` ODBC_DSN=VOS ODBC_USER=dba ODBC_PASSWORD=dba API_KEY=xxx ``` --- ## Configuration For **Claude Desktop** users: Add the following to `claude_desktop_config.json`: ```json { "mcpServers": { "my_database": { "command": "uv", "args": ["--directory", "/path/to/mcp-pyodbc-server", "run", "mcp-pyodbc-server"], "env": { "ODBC_DSN": "dsn_name", "ODBC_USER": "username", "ODBC_PASSWORD": "password", "API_KEY": "sk-xxx" } } } } ``` --- ## Usage ### Tools Provided After successful installation, the following tools will be available to MCP client applications. #### Overview |name|description| |---|---| |`podbc_get_schemas`|List database schemas accessible to connected database management system (DBMS).| |`podbc_get_tables`|List tables associated with a selected database schema.| |`podbc_describe_table`|Provide the description of a table associated with a designated database schema. This includes information about column names, data types, null handling, autoincrement, primary keys, and foreign keys| |`podbc_filter_table_names`|List tables, based on a substring pattern from the `q` input field, associated with a selected database schema.| |`podbc_query_database`|Execute a SQL query and return results in JSONL format.| |`podbc_execute_query`|Execute a SQL query and return results in JSONL format.| |`podbc_execute_query_md`|Execute a SQL query and return results in Markdown table format.| |`podbc_spasql_query`|Execute a SPASQL query and return results.| |`podbc_virtuoso_support_ai`|Interact with the Virtuoso Support Assistant/Agent -- a Virtuoso-specific feature for interacting with LLMs| #### 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, it 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_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: ```bash npm install -g @modelcontextprotocol/inspector ``` 2. Start the inspector: ```bash npx @modelcontextprotocol/inspector uv --directory /path/to/mcp-pyodbc-server run mcp-pyodbc-server ``` Access the provided URL to troubleshoot server interactions. [](https://mseep.ai/app/498645ed-425b-4a6e-bfea-14fa11457da6)