Natural language database query tool based on MCP protocol - access database information through conversation without writing SQL!
\n
\n\t\n\t\n\t\n\t\n
\n\n[English](README.md) | [中文简体](README_CN.md)\n\n## Overview\n\nTextToSQL is an open-source tool that allows users to query databases using natural language. It leverages AI capabilities through the Model Context Protocol (MCP) to interpret user queries, generate appropriate SQL statements, and execute them against a database.\n\n## Project Branches\n\nThis project offers two main branches to accommodate different use cases:\n\n- **main branch**: Complete database query tool\n - Generates SQL statements and executes queries\n - Returns actual query results\n - Suitable for scenarios requiring direct database information retrieval\n\n- **sql_builder branch**: Pure SQL generation tool\n - Only generates SQL statements without executing actual operations\n - Supports all types of SQL operations (SELECT, INSERT, UPDATE, DELETE, CREATE, etc.)\n - Ideal for SQL learning and teaching scenarios\n - Suitable for scenarios where SQL needs to be generated but executed by other systems\n\n## Features\n\n- **Natural Language Queries**: Query your database using plain English or Chinese\n- **SQL Generation**: Automatically converts natural language to optimized SQL queries\n- **Database Schema Caching**: Caches database structure for faster query generation\n- **Support for Complex Queries**: Handles JOINs, subqueries, aggregations, and more\n- **Interactive Query Refinement**: Engage in a conversation to refine your database queries\n\n## Architecture\n\nThe system consists of several components:\n\n1. **MCP Server**: Provides the interface between the AI model and the database tools\n2. **Database Connection**: Connects to your database (currently supports MySQL/MariaDB via PyMySQL)\n3. **Cache Management**: Tools to cache and update database schema information\n4. **Query Execution**: Tools to execute SQL queries and process results\n\n## Installation\n\n### Prerequisites\n\n- Python 3.8 or higher\n- Access to a MySQL/MariaDB database\n\n### Setup\n\n1. Clone the repository:\n ```bash\n git clone https://github.com/yourusername/texttosql.git\n cd texttosql\n ```\n\n2. Install dependencies:\n ```bash\n pip install -r requirements.txt\n ```\n\n3. Configure your database connection:\n Edit the `mcp_servers.py` file to update the `get_apollo_confs()` function with your database credentials.\n\n## Usage\n\n1. Start the MCP server:\n ```bash\n python mcp_servers.py\n ```\n\n2. Configure the MCP server connection in your AI client:\n ```json\n {\n \"mcpServers\": {\n \"sql bot\": {\n \"timeout\": 60,\n \"type\": \"sse\",\n \"url\": \"http://127.0.0.1:8000/sse\"\n }\n }\n }\n ```\n\n3. Use natural language to query your database through the AI interface:\n - Write code to use the AI interface to process queries\n - You can reference the prompts in the `prompt.py` file to guide the model in generating SQL and executing queries\n\n4. Query Result Processing:\n The current approach saves query results to a fixed path JSON file while also returning the results in JSON format:\n ```python\n # Result processing logic in tools/query_table_data.py\n \n # Save to JSON file\n json_file_path = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'data.json')\n os.makedirs(os.path.dirname(json_file_path), exist_ok=True)\n with open(json_file_path, 'w', encoding='utf-8') as f:\n json.dump(result_data, f, ensure_ascii=False, indent=4)\n \n # Also return JSON format data\n return {\n \"status\": \"success\",\n \"message\": f\"Query successful, saved {len(rows)} records to JSON file\",\n \"fields_count\": len(field_names),\n \"records_count\": len(rows),\n \"sql\": sql, # Return the executed SQL statement for debugging\n \"data\": result_data # Contains actual results\n }\n ```\n\n### Example Queries\n\n- \"Show me all employees in the IT department\"\n- \"What's the average salary by department?\"\n- \"Find customers who placed orders in the last 3 months\"\n- \"List the top 5 products by sales volume\"\n\n## Tools\n\nThe system provides several tools through the MCP interface:\n\n- **get_table_data**: Execute SQL queries against the database\n- **get_cache_info**: Retrieve cached table and field information\n- **update_cache_info**: Update the cache with fresh database schema information\n\n## Customization\n\nYou can customize the system by:\n\n1. Modifying the prompt in `prompt.py` to adjust how the AI interprets queries\n2. Extending the tools in the `tools/` directory to add new functionality\n3. Updating the database connection logic to support additional database types\n\n## License\n\nThis project is licensed under the MIT License - see the LICENSE file for details.\n\n## Contributing\n\nContributions are welcome! Please feel free to submit a Pull Request.\n\n## Acknowledgements\n\n- Thanks to all contributors who have helped to improve this project\n- Special thanks to the open-source community for providing the tools and libraries that make this project possible\n\n## Contact & Support\n\n### Contact me\n\n\n### Support This Project\n\n"}
![](\"https://img.shields.io/github/stars/lixu289508/TextToSQL?style=flat-square&logo=GitHub\")
\n\t![](\"https://img.shields.io/github/forks/lixu289508/TextToSQL?style=flat-square&logo=GitHub\")
\n\t![](\"https://img.shields.io/github/watchers/lixu289508/TextToSQL?style=flat-square&logo=GitHub\")
\n\t![](\"https://img.shields.io/github/license/lixu289508/TextToSQL.svg?style=flat-square\")
\n![\"WeChat](\"https://toolkitai.cn/wx.png\")
\n\n### Support This Project\n![\"Appreciation](\"https://toolkitai.cn/zs.jpg\")
\n"}