| Name | py_nl2sql JSON |
| Version |
0.0.2
JSON |
| download |
| home_page | https://github.com/pillarliang/py-nl2sql |
| Summary | A toolkit for converting natural language to SQL statements. |
| upload_time | 2024-09-11 07:48:17 |
| maintainer | None |
| docs_url | None |
| author | liangzhu |
| requires_python | <4.0.0,>=3.8.1 |
| license | MIT |
| keywords |
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
# PY-NL2SQL: 开箱即用的自然语言到SQL查询生成的Python库
<div align="center">
<p>
<a href="https://opensource.org/licenses/MIT">
<img alt="License: MIT" src="https://img.shields.io/badge/License-MIT-yellow.svg" />
</a>
<a href="https://github.com/pillarliang/py-nl2sql/releases">
<img alt="Release Notes" src="https://img.shields.io/github/release/pillarliang/py-nl2sql" />
</a>
<a href="https://github.com/eosphoros-ai/DB-GPT/issues">
<img alt="Open Issues" src="https://img.shields.io/github/issues-raw/pillarliang/py-nl2sql" />
</a>
<a href="https://discord.gg/7uQnPuveTY">
<img alt="Discord" src="https://dcbadge.vercel.app/api/server/7uQnPuveTY?compact=true&style=flat" />
</a>
<a href="https://codespaces.new/pillarliang/py-nl2sql">
<img alt="Open in GitHub Codespaces" src="https://github.com/codespaces/badge.svg" />
</a>
</p>
[**English**](README_EN.md) | [**中文**](README.md)
</div>
## 目录
- [架构方案](#架构方案)
- [安装与使用](#安装与使用)
- [使用说明](#使用说明)
## 架构方案
<p align="center">
<img src="./assets/nl2sql_structure.png" width="800px" />
</p>
## 安装与使用
```python
# pip install py_nl2sql
from py_nl2sql import LLM,DBInstance,NL2SQLWorkflow
llm = LLM()
instance = DBInstance(
db_type="mysql",
db_name="classicmodels",
need_sql_sample=True,
db_user="root",
db_password="",
db_host="127.0.0.1",
db_port="3306",
llm=llm,
)
query = "what is price of `1968 Ford Mustang`"
service = NL2SQLWorkflow(instance, query, llm)
res = service.get_response()
print(res)
```
## 使用说明
在使用本项目时，用户需要提供以下三部分信息：
### 1. OpenAI Key
用户可以通过两种方式提供 `api_key` 和 `base_url`：直接传入参数，或在环境变量中设置 `OPENAI_API_KEY` 和 `OPENAI_BASE_URL`。
- 目前，本项目所使用的大模型仅兼容 OpenAI 模型，后续将支持本地模型及其他模型。
- 由于需要使用 OpenAI 的结构化输出特性，默认模型设定为 `gpt-4o-mini`。
```python
from py_nl2sql.models.llm import LLM base_url
llm = LLM(api_key="sk-xx",base_url="https://xxx")
```
### 2. 数据库配置信息
在新建数据库实例时，需要传入 LLM（大语言模型）。在实例化过程中，将执行以下操作：
1. 使用嵌入（embedding）模型将数据库表信息进行嵌入处理，并将结果存储到向量数据库中（此步骤为必选）。
2. 根据数据库信息生成样本 SQL，以便后续将用户查询转换为 SQL 时作为参考（此步骤为可选）。默认情况下，该功能是开启的；如果不需要生成样本 SQL，可以将 `need_sql_sample` 设置为 `False`。
```python
instance = DBInstance(
db_type="mysql",
db_name="classicmodels",
need_sql_sample=True,
db_user="root",
db_password="",
db_host="127.0.0.1",
db_port="3306",
llm=llm,
)
```
特性：
1. 支持初始化多个数据库实例，以便在存在多个数据库的情况下进行管理。
2. 如果数据库发生变动，可以直接调用 instance.db_update() 方法对数据库进行更新。更新过程中，将重新将数据库表信息进行嵌入处理，并存储到向量数据库中。
说明：
DBInstance 的设计采用多例模式 + 状态机。根据 db_type + db_name 实例化不同的对象。
### 3. 用户查询
用户只需传入要查询的数据库实例及相应的查询语句，然后调用 `get_response()` 方法即可获取最终结果。
```python
service = NL2SQLWorkflow(instance, query)
res = service.get_response()
```
同时，NL2SQLWorkflow 对象中保存了一系列中间过程的元信息，例如
```
service.text_to_sql_query # used for sql generation
service.interpretation_query # used for final response generation
service.related_table_summary # Table information related to the query
service.first_sql_query # SQL query generated from the query for the first time
service.final_sql_query # SQL query generated from the query using the similarity SQL
...
```
## Licence
The MIT License (MIT)
Raw data
{
"_id": null,
"home_page": "https://github.com/pillarliang/py-nl2sql",
"name": "py_nl2sql",
"maintainer": null,
"docs_url": null,
"requires_python": "<4.0.0,>=3.8.1",
"maintainer_email": null,
"keywords": null,
"author": "liangzhu",
"author_email": "pillarliang21@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/78/a1/76cb3df17c7c83a45fb75d50a0c7152c426d31297bedec9ddd7228cb6e53/py_nl2sql-0.0.2.tar.gz",
"platform": null,
"description": "# PY-NL2SQL: \u5f00\u7bb1\u5373\u7528\u7684\u81ea\u7136\u8bed\u8a00\u5230SQL\u67e5\u8be2\u751f\u6210\u7684Python\u5e93\n\n\n<div align=\"center\">\n <p>\n <a href=\"https://opensource.org/licenses/MIT\">\n <img alt=\"License: MIT\" src=\"https://img.shields.io/badge/License-MIT-yellow.svg\" />\n </a>\n <a href=\"https://github.com/pillarliang/py-nl2sql/releases\">\n <img alt=\"Release Notes\" src=\"https://img.shields.io/github/release/pillarliang/py-nl2sql\" />\n </a>\n <a href=\"https://github.com/eosphoros-ai/DB-GPT/issues\">\n <img alt=\"Open Issues\" src=\"https://img.shields.io/github/issues-raw/pillarliang/py-nl2sql\" />\n </a>\n <a href=\"https://discord.gg/7uQnPuveTY\">\n <img alt=\"Discord\" src=\"https://dcbadge.vercel.app/api/server/7uQnPuveTY?compact=true&style=flat\" />\n </a>\n <a href=\"https://codespaces.new/pillarliang/py-nl2sql\">\n <img alt=\"Open in GitHub Codespaces\" src=\"https://github.com/codespaces/badge.svg\" />\n </a>\n </p>\n\n[**English**](README_EN.md) | [**\u4e2d\u6587**](README.md)\n</div>\n\n\n## \u76ee\u5f55\n- [\u67b6\u6784\u65b9\u6848](#\u67b6\u6784\u65b9\u6848)\n- [\u5b89\u88c5\u4e0e\u4f7f\u7528](#\u5b89\u88c5\u4e0e\u4f7f\u7528)\n- [\u4f7f\u7528\u8bf4\u660e](#\u4f7f\u7528\u8bf4\u660e)\n\n## \u67b6\u6784\u65b9\u6848\n\n<p align=\"center\">\n <img src=\"./assets/nl2sql_structure.png\" width=\"800px\" />\n</p>\n\n\n## \u5b89\u88c5\u4e0e\u4f7f\u7528\n```python\n# pip install py_nl2sql \nfrom py_nl2sql import LLM,DBInstance,NL2SQLWorkflow\n\nllm = LLM() \ninstance = DBInstance(\n db_type=\"mysql\", \n db_name=\"classicmodels\", \n need_sql_sample=True, \n db_user=\"root\", \n db_password=\"\", \n db_host=\"127.0.0.1\", \n db_port=\"3306\", \n llm=llm, \n ) \nquery = \"what is price of `1968 Ford Mustang`\" \nservice = NL2SQLWorkflow(instance, query, llm)\nres = service.get_response() \nprint(res)\n```\n## \u4f7f\u7528\u8bf4\u660e\n\n\u5728\u4f7f\u7528\u672c\u9879\u76ee\u65f6\uff0c\u7528\u6237\u9700\u8981\u63d0\u4f9b\u4ee5\u4e0b\u4e09\u90e8\u5206\u4fe1\u606f\uff1a\n### 1. OpenAI Key\n\n \u7528\u6237\u53ef\u4ee5\u901a\u8fc7\u4e24\u79cd\u65b9\u5f0f\u63d0\u4f9b `api_key` \u548c `base_url`\uff1a\u76f4\u63a5\u4f20\u5165\u53c2\u6570\uff0c\u6216\u5728\u73af\u5883\u53d8\u91cf\u4e2d\u8bbe\u7f6e `OPENAI_API_KEY` \u548c `OPENAI_BASE_URL`\u3002\n\n - \u76ee\u524d\uff0c\u672c\u9879\u76ee\u6240\u4f7f\u7528\u7684\u5927\u6a21\u578b\u4ec5\u517c\u5bb9 OpenAI \u6a21\u578b\uff0c\u540e\u7eed\u5c06\u652f\u6301\u672c\u5730\u6a21\u578b\u53ca\u5176\u4ed6\u6a21\u578b\u3002\n- \u7531\u4e8e\u9700\u8981\u4f7f\u7528 OpenAI \u7684\u7ed3\u6784\u5316\u8f93\u51fa\u7279\u6027\uff0c\u9ed8\u8ba4\u6a21\u578b\u8bbe\u5b9a\u4e3a `gpt-4o-mini`\u3002\n ```python\n from py_nl2sql.models.llm import LLM base_url\n \n llm = LLM(api_key=\"sk-xx\",base_url=\"https://xxx\")\n ```\n\n### 2. \u6570\u636e\u5e93\u914d\u7f6e\u4fe1\u606f\n\n\u5728\u65b0\u5efa\u6570\u636e\u5e93\u5b9e\u4f8b\u65f6\uff0c\u9700\u8981\u4f20\u5165 LLM\uff08\u5927\u8bed\u8a00\u6a21\u578b\uff09\u3002\u5728\u5b9e\u4f8b\u5316\u8fc7\u7a0b\u4e2d\uff0c\u5c06\u6267\u884c\u4ee5\u4e0b\u64cd\u4f5c\uff1a\n 1. \u4f7f\u7528\u5d4c\u5165\uff08embedding\uff09\u6a21\u578b\u5c06\u6570\u636e\u5e93\u8868\u4fe1\u606f\u8fdb\u884c\u5d4c\u5165\u5904\u7406\uff0c\u5e76\u5c06\u7ed3\u679c\u5b58\u50a8\u5230\u5411\u91cf\u6570\u636e\u5e93\u4e2d\uff08\u6b64\u6b65\u9aa4\u4e3a\u5fc5\u9009\uff09\u3002\n2. \u6839\u636e\u6570\u636e\u5e93\u4fe1\u606f\u751f\u6210\u6837\u672c SQL\uff0c\u4ee5\u4fbf\u540e\u7eed\u5c06\u7528\u6237\u67e5\u8be2\u8f6c\u6362\u4e3a SQL \u65f6\u4f5c\u4e3a\u53c2\u8003\uff08\u6b64\u6b65\u9aa4\u4e3a\u53ef\u9009\uff09\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u8be5\u529f\u80fd\u662f\u5f00\u542f\u7684\uff1b\u5982\u679c\u4e0d\u9700\u8981\u751f\u6210\u6837\u672c SQL\uff0c\u53ef\u4ee5\u5c06 `need_sql_sample` \u8bbe\u7f6e\u4e3a `False`\u3002\n ```python\n instance = DBInstance(\n db_type=\"mysql\",\n db_name=\"classicmodels\",\n need_sql_sample=True,\n db_user=\"root\",\n db_password=\"\",\n db_host=\"127.0.0.1\",\n db_port=\"3306\",\n llm=llm,\n )\n ```\n \n \u7279\u6027\uff1a \n 1. \u652f\u6301\u521d\u59cb\u5316\u591a\u4e2a\u6570\u636e\u5e93\u5b9e\u4f8b\uff0c\u4ee5\u4fbf\u5728\u5b58\u5728\u591a\u4e2a\u6570\u636e\u5e93\u7684\u60c5\u51b5\u4e0b\u8fdb\u884c\u7ba1\u7406\u3002 \n 2. \u5982\u679c\u6570\u636e\u5e93\u53d1\u751f\u53d8\u52a8\uff0c\u53ef\u4ee5\u76f4\u63a5\u8c03\u7528 instance.db_update() \u65b9\u6cd5\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u66f4\u65b0\u3002\u66f4\u65b0\u8fc7\u7a0b\u4e2d\uff0c\u5c06\u91cd\u65b0\u5c06\u6570\u636e\u5e93\u8868\u4fe1\u606f\u8fdb\u884c\u5d4c\u5165\u5904\u7406\uff0c\u5e76\u5b58\u50a8\u5230\u5411\u91cf\u6570\u636e\u5e93\u4e2d\u3002\n\u8bf4\u660e\uff1a\nDBInstance \u7684\u8bbe\u8ba1\u91c7\u7528\u591a\u4f8b\u6a21\u5f0f + \u72b6\u6001\u673a\u3002\u6839\u636e db_type + db_name \u5b9e\u4f8b\u5316\u4e0d\u540c\u7684\u5bf9\u8c61\u3002\n\n### 3. \u7528\u6237\u67e5\u8be2\n\u7528\u6237\u53ea\u9700\u4f20\u5165\u8981\u67e5\u8be2\u7684\u6570\u636e\u5e93\u5b9e\u4f8b\u53ca\u76f8\u5e94\u7684\u67e5\u8be2\u8bed\u53e5\uff0c\u7136\u540e\u8c03\u7528 `get_response()` \u65b9\u6cd5\u5373\u53ef\u83b7\u53d6\u6700\u7ec8\u7ed3\u679c\u3002\n```python\nservice = NL2SQLWorkflow(instance, query) \nres = service.get_response()\n```\n\u540c\u65f6\uff0cNL2SQLWorkflow \u5bf9\u8c61\u4e2d\u4fdd\u5b58\u4e86\u4e00\u7cfb\u5217\u4e2d\u95f4\u8fc7\u7a0b\u7684\u5143\u4fe1\u606f\uff0c\u4f8b\u5982\n```\nservice.text_to_sql_query # used for sql generation\nservice.interpretation_query # used for final response generation\nservice.related_table_summary # Table information related to the query\nservice.first_sql_query # SQL query generated from the query for the first time\nservice.final_sql_query # SQL query generated from the query using the similarity SQL\n...\n```\n\n## Licence\n\nThe MIT License (MIT)\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "A toolkit for converting natural language to SQL statements.",
"version": "0.0.2",
"project_urls": {
"Homepage": "https://github.com/pillarliang/py-nl2sql",
"Repository": "https://github.com/pillarliang/py-nl2sql"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "910254d725e6063378b94b073deb77a3a5e035eaf0c97572c99efb6b14658b21",
"md5": "eff32733d9e568c9c49ddb545f11bea9",
"sha256": "449c98de369237ff6f3f837de999bc423433afdd5ba15c891d45d95e76367b38"
},
"downloads": -1,
"filename": "py_nl2sql-0.0.2-py3-none-any.whl",
"has_sig": false,
"md5_digest": "eff32733d9e568c9c49ddb545f11bea9",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<4.0.0,>=3.8.1",
"size": 28989,
"upload_time": "2024-09-11T07:48:16",
"upload_time_iso_8601": "2024-09-11T07:48:16.044046Z",
"url": "https://files.pythonhosted.org/packages/91/02/54d725e6063378b94b073deb77a3a5e035eaf0c97572c99efb6b14658b21/py_nl2sql-0.0.2-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "78a176cb3df17c7c83a45fb75d50a0c7152c426d31297bedec9ddd7228cb6e53",
"md5": "2a5d3566a4f3896f9d96b4761d52d982",
"sha256": "8c160fd914c89e957a6f6d827511b7a6b118c68a40e97904a8f1276336e19a8a"
},
"downloads": -1,
"filename": "py_nl2sql-0.0.2.tar.gz",
"has_sig": false,
"md5_digest": "2a5d3566a4f3896f9d96b4761d52d982",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<4.0.0,>=3.8.1",
"size": 23699,
"upload_time": "2024-09-11T07:48:17",
"upload_time_iso_8601": "2024-09-11T07:48:17.520190Z",
"url": "https://files.pythonhosted.org/packages/78/a1/76cb3df17c7c83a45fb75d50a0c7152c426d31297bedec9ddd7228cb6e53/py_nl2sql-0.0.2.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-09-11 07:48:17",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "pillarliang",
"github_project": "py-nl2sql",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "py_nl2sql"
}
