# nl2sql2025

**Repository Path**: zhouweng/nl2sql2025

## Basic Information

- **Project Name**: nl2sql2025
- **Description**: 组队参加2025年羊城工匠杯的nl2sql比赛的项目源代码
- **Primary Language**: Python
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 0
- **Created**: 2025-10-08
- **Last Updated**: 2025-10-21

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# nl2sql2025

## 介绍
组队参加2025年羊城工匠杯nl2sql比赛的项目源代码

## 执行流程
### 1. 把 data.db 文件复制到 ./data目录下 下载路径：https://pan.baidu.com/s/1tiRT1G6WbQE5ob_ck5EegA?pwd=2025  （密码：nl2sql2025)
### 2. 安装必要的python依赖包(参看下面的安装说明)
### 3. aliyun申请api_key并且修改配置文件 
```bash
$ cp ./src/config.py.example config.py
# 手工修改 llm_api_key内容
```
### 4. 工作根目录执行以下命令
```bash
sh start.sh
```
### 5. 检查生成文件 ./result/part1_results.jsonl


## 软件架构
### 向量化流程
``` mermaid
flowchart TD
    A["数据预处理阶段</br>(build_vector.sh)"] --> B["Excel文件->16个建表语句(excel2sql.py)"]
    A --> C1["官方发布和人工标记samples集"]
    C1 --> C2["建立问题和库表关系(question2table.py)"]
    C1 --> C3["建立模板问题与SQL关系(template2sql.py)"]
    C2 --> D["库表定义附加上问题集信息(sql_markdown.py)"]
    C3 --> D
    B --> D
    D --> E["调用嵌入模型生成多维向量</br>(sqls_embedding.py)"]
    E --> F["向量化库表及问题集</br>(vector2chroma.py)"]
```
### 批量生成SQL流程
``` mermaid
flowchart TD
    A[src/multi_extraction.py<br>读取 data/questions.jsonl] --> B[逐行处理每个问题]
    B --> C[src/query/get_sql_answer.py get_sql_answer ]
    C --> D[src/query/query_table.py<br>query_table]
    D --> E{可以找到对应的 table_name?}
    E -- 是 --> F1[src/query/query_sql.py<br>query_sql<br>返回 table_define]
    F1 --> G1[src/query/query_example.py<br>query_example<br>返回example_hint]
    E -- 否 --> F2[src/query/query_ddl.py<br>query_ddl<br>返回 table_define]
    F2 --> G2[src/query/query_vector.py<br>&query_reranker.py<br>&hit_keywords.py]
    C --> H[根据问题关键字增加提示词<br>src/build_extra_hint.py<br>build_extra_hint]
    H --> I[返回 extra_hint]
    G1 --> M[src/utils.py<br>create_sql_template<br>生成 prompt]
    G2 --> M
    I --> M
    M --> N[调用大模型接口 llm返回生成的 SQL]
    N --> O[src/multi_extraction.py<br>写入 result/part* 文件] 
```
## 安装教程

### 1. 安装pyenv和python3.10
 参考文章：https://blog.csdn.net/u010593516/article/details/147855362

#### 确认正常安装
```bash
$ ls ~/.pyenv/versions
3.10.17  3.12.10
```

#### 切换3.10版本python
```bash
$ pyenv global 3.10
$ python --version
Python 3.10.17
```


### 2. 创建虚拟环境并下载依赖包
```bash
$ apt install python3-venv
$ source ~/.profile 
$ pyenv local 3.10
$ python --version
$ python -m venv env_nls2sql
$ source env_nls2sql/bin/activate
$ pip install -r requirements.txt -i https://pypi.tuna.tsinghua.edu.cn/simple
```


## 主要源代码介绍
### start.sh : 一键启动脚本
### build_vector.sh : 构建向量库
### clean.sh: 清理和备份中间文件
### refresh_vector.sh : 刷新向量
### ./src/multi_extraction.py : 主程序入口，支持多线程启动
### ./src/vector : 【文件夹】构建向量库相关代码
#### src/vector/excel2sql.py : excel文件 data/表结构.xlsx 转为包含 sqlite建表ddl 的json文件，写入 ./sql_output 文件夹
#### src/vector/question2table.py ：建立库表与问题模板库关联，生成tmp_question2sql.json文件
#### src/vector/template2sql.py ：建立库表与问题模板库关联，生成tmp_question2sql.json文件
#### src/vector/sql_markdown.py ：对./sql_output 文件夹打问题模板标记
#### src/vector/sqls_embedding.py ：调用嵌入模型，生成tmp_embedding_waiting.txt 待向量化的文件
#### src/vector/vector2chroma.py ：读取tmp_embedding_waiting.txt 写入chroma向量数据库
#### ./src/vector/show_vectors.py : 显示向量库中text信息
#### ./src/vector/remove_vectors.py : 清空向量库信息
### ./src/query : 【文件夹】构建SQL相关检索
#### src/query/get_sql_answer.py : 根据Qustion得到匹配度最高库表
#### src/query/query_sql.py : 根据table_name找到sql_output下面对应的建表语句
#### src/query/query_table.py : 根据question找到到tmp_quesiton2.sql.json 找到对应的table
#### src/query/build_extra_hint.py : 根据Question生成特殊提示词
#### src/query/extract_tables.py : 提取SQL里面的库表
#### src/query/hit_keywords.py : 根据问题与向量搜索问题集的关键字匹配数组
#### src/query/query_reranker.py : question和documents的调用reranker模型排序
#### src/query/query_vector.py ： 根据text查找向量库中相似度TOP5记录 （需要用到embedding模型）
#### src/query/data_reranker.py : 根据tmp_embedding.txt的 query和documents 找到匹配度最高的段落（需要用到reranker模型）
### ./src/tools : 【文件夹】各类辅助工具
#### ./src/tools/eval_question2sql.py: 评估问题模板question_template的数量并输出新问题集到 tmp_newquestion.txt
#### ./src/tools/build_checklist.py: 没有问题模板样本支持的新问题清单 tmp_newquestions.txt 构建人工待确认清单
#### ./src/tools/build_lastcheck.py: 提交结果前，results结果集进行问题模板分组展现，人工检查非样本问题与样本问题的SQL脚本的大模型生成一致性
#### ./src/tools/eval_embeddings.py: 评估通过embeddings模型拿到的top1结果是否就是samples的库表
#### ./src/tools/eval_reranker.py: 评估通过embeddings和reranker模型后，拿到的库表是否就是samples的库表
#### ./src/tools/build_check.py: (第一遍SQL生成后）根据tmp_question2sql.json已经归集的问题大类分组，生成人工友好的待标记清单
#### ./src/tools/build_middle.py: (可由build_check.py替代)根据questions.jsonl和results.jsonl 生成middle.jsonl待人工处理
#### ./src/tools/build_requestion.py: (阶段性排查成功后）已经标记0和-1的check文件，归集生成 requestion文件，手工copy到 questions.jsonl重新运行SQL生成
#### ./src/tools/build_samples.py: (阶段性排查成功后）把data目录下的check开头文件flag=1 or -1 归集到 samples.jsons作为样本库，向量数据库重建后，可以提高命中率
#### ./src/tools/check_dataset.py: (非常实用) 根据id检查数据集的SQL的执行数据集结果
#### ./src/tools/check_jsonl.py : (非常实用) 检查jsonl文件是否准确，在比赛最后提交前，手动检查一下
#### ./src/tools/check_resultsql.py: 检查results.jsonl的 SQL 执行失败或结果为空，结果写入 tmp_err_sql.log
#### ./src/tools/clean_question.py: 正则表达式对问题进行大类归集
#### ./src/tools/diff_tables.py: (基本废弃) results和samples的库表差异
#### ./src/tools/scan_question.py: (刚拿到问题集运行) 扫描问题集，看看哪些问题还没有归集大类
#### ./src/tools/result_merge.py: 合并两个 JSON Lines 格式的文件，以 base 文件为基础，用 new 文件中相同 id 的记录进行更新。
#### ./src/tools/simulate_score.py: 比对result和sample文件，得到分数
#### ./src/tools/sqlite_table.py: (基本废弃) 看看sqlite下面有哪些库表(一般用来测试sqlite是否可用)


## 主要其他文件介绍
### ./data/parameters.txt : 问题参数化json(scan_columns.py生成)
### ./data/keywords.txt : 用于关键字匹配的业务术语，用|分隔开(手工维护)
### ./question.round01.jsonl : 官方公布的第一轮问题集包含10道样本题型
### ./question.round02.jsonl : 官方公布的初赛问题集包含800道样本题型(原文件名称test.jsonl)
### ./question.round01.jsonl : 官方公布的第一轮问题答案样本集共267题
### ./question.round02.jsonl : 初赛结束后，手工标记完成的样本集
### ./result.results.round01.jsonl : 程序批量运行出来的结果集
### ./result.results.round02.jsonl : 初赛提交官方作为比赛成绩的结果集
### ./data/checklist.part1&2.txt : 两个人工标注的样本集