我一开始做这个项目是因为团队里总有人跑来问我帮我查一下上个月华东区销量最高的SKU是哪个这个月退货率超过10%的商品有多少。这些问题用SQL写其实不难但每次都要停下来看表结构、想字段名、写一堆JOIN非常打断工作流。后来我想既然手头有大模型能不能直接用自然语言问数据库让它自己生成SQL、自己执行、再把结果返回这就是这个SQLite 大模型实现Text2SQL小助手的由来。这个项目做下来核心思路其实很朴素把SQLite的表结构、字段注释、示例数据这些数据库元信息灌给大模型让它充当一个懂数据库结构的翻译官把你的自然语言问题翻译成标准SQL接着在本地执行SQL拿到结果后再用自然语言回答你。适合的对象也很清晰手里有SQLite数据库、日常要频繁取数分析、又不想每次都手写SQL的人。1. 这个Text2SQL助手到底做什么以及为什么选SQLite做底座1.1 一个数据查询场景引发的需求我手上的业务数据大部分存在SQLite里。SQLite这个数据库在正经企业项目里存在感不高但在个人工具、本地分析、爬虫落地、原型验证这些场景下它几乎是默认选项不需要安装服务、不需要账号密码、一个文件就是整个数据库挪走就能带走。问题恰恰出在这里。SQLite虽然轻但表结构往往只有建表的人清楚。时间一长字段命名逐渐放飞自我比如crt_ts、item_id、region_cd这类缩写新人根本猜不出含义。每次写查询都要先翻建表脚本再试几个字段名效率极低。我做的这个Text2SQL助手目标就是把人去看表结构再写SQL这个过程变成人直接说人话模型去读表结构再写SQL。用户只需要面向一个小对话框输入问题比如上周每天的新增订单数系统自动完成以下动作读取SQLite数据库里的表结构元数据表名、字段名、字段类型、注释、示例值把元数据和用户问题一起组装成提示词发给大模型大模型返回一条SQL系统在本地执行把查询结果再用大模型做一轮自然语言总结返回给用户。整个链路打通之后我问任何一个问题平均耗时大概三到七秒比打开Navicat手写SQL快一个量级。1.2 为什么是SQLite大模型这个组合选型的时候我不是没犹豫过。最初考虑过直接用MySQL或者PostgreSQL毕竟很多读者会问为什么用SQLite这个小家伙。理由其实有三层第一SQLite零运维适合做本地小助手。整个项目不需要装数据库服务端Python自带的sqlite3模块就能操作。大模型接口调用失败或者SQL写错了也不影响数据库文件本身。这非常适合作为一个个人工具先跑起来验证Text2SQL到底靠不靠谱。第二SQLite的表结构元数据获取非常方便。通过PRAGMA table_info(表名)就能拿到字段名、字段类型、是否主键等全套信息通过SELECT name FROM sqlite_master WHERE typetable能拿到所有表名。这两条命令让我不用手写和维护一份数据库字典模型每次查询前动态拉取即可Schema发生变更也能自动感知。第三SQLite支持内存态运行和文件态运行两种模式。调试阶段用:memory:数据库配合SETUP SQL脚本可以秒级重建测试环境。生产使用直接指向文件路径非常灵活。大模型这边的选型也很宽泛既可以用云端API也可以用本地部署的量化模型。我后面会专门说模型选型的问题这里先明确一个结论中英文混合的表结构描述、简单的字段映射关系、常见的聚合查询目前主流大模型都能轻松胜任真正考验模型水平的是那些隐含业务语义的查询条件。1.3 整体项目结构与能力边界在开始写代码之前我先画了一张项目能力的边界图保证开发不过度设计。这个助手应该具备的能力支持多张表的关联查询JOIN要能用支持基本的聚合统计COUNT、SUM、AVG、MAX、MIN、GROUP BY支持时间范围过滤、条件组合过滤、排序和分页支持问题指代不明确时的追问或默认规则处理比如最近一周自动映射为date date(now,-7 day)。这个助手暂时不做的事情不做数据写入和修改操作只读查询。所有大模型生成的SQL必须先做只读校验一旦出现INSERT、UPDATE、DELETE、DROP等关键字直接拒绝执行不做跨库查询一个SQLite文件对应一个会话上下文不做复杂嵌套子查询的自动优化模型怎么写我就怎么执行顶多做一次EXPLAIN QUERY PLAN看有没有明显全表扫描以外的问题不追求100%的准确率目标是把能直接查出正确结果的比例做到八成以上剩下两成靠错误自愈和追问处理。把这些边界明确下来之后开发节奏就清晰了。接下来我逐个模块讲。2. 数据底座准备建表设计与让模型看得懂的元数据构造2.1 建表语句先建一个贴近真实业务的小型电商库为了让整个实战不悬浮我建了一个非常典型的电商业务库包含三张表用户表、商品表、订单表。这个结构在绝大多数业务系统里都有影子方便你迁移到自己场景上。-- 用户表 CREATE TABLE IF NOT EXISTS users ( user_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL, gender TEXT DEFAULT 未知, age INTEGER, city TEXT, register_date TEXT, membership_level TEXT DEFAULT 普通会员, UNIQUE(user_name) ); -- 商品表 CREATE TABLE IF NOT EXISTS products ( product_id INTEGER PRIMARY KEY AUTOINCREMENT, product_name TEXT NOT NULL, category TEXT, brand TEXT, price REAL NOT NULL, cost REAL, listing_date TEXT, status TEXT DEFAULT 上架 ); -- 订单表 CREATE TABLE IF NOT EXISTS orders ( order_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, product_id INTEGER NOT NULL, order_date TEXT NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, unit_price REAL NOT NULL, actual_amount REAL NOT NULL, payment_method TEXT, status TEXT DEFAULT 已完成, region TEXT, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (product_id) REFERENCES products(product_id) );注意我在这里故意埋了一些坑后面测试模型的时候用得上。例如order_date用的是TEXT类型而不是DATETIME类型这是SQLite的常见用法因为SQLite没有原生的DateTime类型一般用TEXT存储ISO格式字符串region字段在订单表上而不是用户表上这样设计符合实际业务用户在A地下单寄到B地还有price和cost字段模型需要区分售价和成本才能正确算毛利。2.2 种子数据模型需要看得见的数据风格数据库不能是空壳子得塞进去一些有代表性的测试数据。数据量不需要大但覆盖的场景要够。我插入了50个用户、30个商品、200条订单关键是让数据本身带有业务特征。比如说我故意让某个用户的订单全部是已取消状态让某几个商品只有退货没有成交让某个城市的下单量明显高于其他城市。这样测试自然语言查询时模型返回的SQL是否真的能命中这些数据陷阱一眼就能看出来。种子数据的插入脚本我就不全贴了给一个示例结构import sqlite3 import random from datetime import datetime, timedelta conn sqlite3.connect(shop.db) cur conn.cursor() # 插入用户的示例 for i in range(1, 51): cur.execute( INSERT INTO users (user_name, gender, age, city, register_date, membership_level) VALUES (?, ?, ?, ?, ?, ?), (fuser_{i}, random.choice([男, 女, 未知]), random.randint(18, 60), random.choice([北京, 上海, 广州, 深圳, 杭州, 成都]), datetime.now().strftime(%Y-%m-%d %H:%M:%S), random.choice([普通会员, 黄金会员, 铂金会员])) ) conn.commit()这些种子数据的作用不只是让查询有结果更重要的是让大模型在生成SQL的时候面对city字段能给出真实的城市名而不是臆造面对membership_level能知道有三个枚举值面对黄金会员这种中文值不需要再做一次映射。这就是我理解的数据风格一致性它直接影响Text2SQL的效果。2.3 元数据注入让模型理解库结构的核心设计大模型本身并不知道你的SQLite文件里有什么表、什么字段。要让模型生成可执行的SQL必须把数据库结构信息注入到提示词里。我采用的方式是动态提取而不是手写死。这里有一个重要的理念数据库结构会变提示词不该跟着变。表加了一个字段、改了一个字段名助手应该自动感知。提取元数据的核心函数如下import sqlite3 def get_schema(db_path: str) - str: conn sqlite3.connect(db_path) cur conn.cursor() cur.execute(SELECT name FROM sqlite_master WHERE typetable AND name NOT LIKE sqlite_%) tables [row[0] for row in cur.fetchall()] schema_lines [] for table in tables: cur.execute(fPRAGMA table_info({table})) columns cur.fetchall() col_descs [] for col in columns: cid, name, col_type, notnull, default_val, pk col desc f{name} ({col_type}) if pk: desc PRIMARY KEY if default_val is not None: desc f DEFAULT {default_val} col_descs.append(desc) schema_lines.append(f表名: {table}\n字段: , .join(col_descs)) # 每个表提取两条示例数据 try: cur.execute(fSELECT * FROM {table} LIMIT 2) sample_rows cur.fetchall() if sample_rows: col_names [d[0] for d in cur.description] schema_lines.append(示例数据: ) for row in sample_rows: schema_lines.append(str(dict(zip(col_names, row)))) except Exception as e: schema_lines.append(f示例数据提取失败: {e}) conn.close() return \n.join(schema_lines)这段代码做的事情简单说就是连接数据库后读取所有非系统表对每一张表读取完整的字段结构生成一行字段清单再对每张表提取两条示例数据让模型知道每条记录长什么样。为什么要提取示例数据我实测发现只给字段名和类型模型生成的SQL往往会用错字段名。比如它可能以为订单表里存的是order_time而不是order_date或者以为status字段存的是成功/失败而不是已完成/已取消。一旦给了两条真实示例数据模型立刻就能锚定字段的取值风格生成的SQL准确率能提升一大截。这是我在实际项目中验证过的一个关键细节。3. 大模型的选择与调用封装本地私有化与云端API的取舍3.1 模型选型的几个关键维度Text2SQL对模型的要求和写作文、写代码评论不太一样。它的核心是指令跟随能力和结构化输出能力。生成SQL不仅要语义正确还要格式干净不能有注释、多余的解释、Markdown标记或者自创的字段名。市面上主流模型我基本都试过从实际表现来看可以分三个梯队模型梯队代表模型Text2SQL表现部署成本第一梯队GPT-4系列、Claude 3.5、DeepSeek V3/R1指令跟随强少样本学习能力强复杂查询也能胜任云端API按量付费第二梯队Qwen2.5 7B/14B、GLM-4系列、DeepSeek 7B常规查询没问题复杂嵌套偶尔出错本地量化部署显存6-12G可跑第三梯队一些小参数模型3B以下简单单表查询可用多表JOIN和复杂条件容易翻车部署成本极低但不推荐实际选型的时候我建议你先想清楚一个问题你的数据敏感度有多高如果只是个人工具数据不敏感直接用市面上的云API是最省事的效果好、不用管部署。我自己项目里主力用的DeepSeek和Qwen的云端接口效果稳定成本也很低。如果是企业环境数据不能出内网那就要考虑本地部署Qwen2.5这类开源模型用Ollama或者llama.cpp把模型跑起来效果弱一些但可控。我这边做了一个折中方案封装了一层模型调用接口底层可以切换云端API和本地Ollama。这样既保证了开发期效果也预留了私有化部署的迁移路径。3.2 统一的模型调用层设计为了让项目不绑死在某个模型厂商上我设计了一个统一的调用层核心思路是只依赖一个chat接口。import requests from typing import List, Dict class LLMClient: def __init__(self, provider: str openai, base_url: str , api_key: str , model: str ): self.provider provider self.base_url base_url self.api_key api_key self.model model def chat(self, messages: List[Dict[str, str]], temperature: float 0.0) - str: if self.provider ollama: return self._chat_ollama(messages, temperature) else: return self._chat_openai(messages, temperature) def _chat_openai(self, messages, temperature): url self.base_url.rstrip(/) /chat/completions payload {model: self.model, messages: messages, temperature: temperature} headers {Authorization: fBearer {self.api_key}, Content-Type: application/json} resp requests.post(url, jsonpayload, headersheaders, timeout60) resp.raise_for_status() return resp.json()[choices][0][message][content] def _chat_ollama(self, messages, temperature): url self.base_url.rstrip(/) /api/chat payload {model: self.model, messages: messages, stream: False, options: {temperature: temperature}} resp requests.post(url, jsonpayload, timeout120) resp.raise_for_status() return resp.json()[message][content]这里有两个容易被忽略的细节。第一temperature必须设为0。Text2SQL这类任务要的是可复现性同样的表结构、同样的问题第二次生成的SQL应该和第一次一样。如果temperature设高了模型可能这次用WHERE city上海下次就换成WHERE city LIKE %上海%虽然结果一样但调试的时候你会非常痛苦。第二超时时间要给足。本地Ollama跑模型没有GPU加速时生成一条SQL可能要二三十秒云端API偶尔也会因为排队延迟。我直接把超时设为60秒到120秒避免因为超时误判成模型能力不行。4. Text2SQL核心链路从自然语言到可执行SQL的提示词与执行回退机制4.1 提示词工程把数据库上下文变成角色的工作手册Text2SQL的提示词是整个项目的地基。我迭代了很多版本最后沉淀下来的模板有这几个核心要素缺一不可。第一角色指令要明确你是什么、你要输出什么。我在系统提示词里写的是你是一个SQL专家你的任务是针对SQLite数据库将用户的中文问题转换为精确可执行的SQL语句。你只能输出SQL不要输出任何解释和Markdown标记。第二要定义字段映射规则。这一步决定了自然语言词汇能不能落到数据库字段上。我会明确告诉模型日期统一用date(字段)格式处理最近7天对应date(now, -7 day)状态字段的取值风格要参考示例数据比如订单状态只有已完成/已取消/待发货三种。第三要规定输出格式。我在提示词里强制要求模型输出JSON而不是裸SQL。为什么用JSON因为裸SQL很容易被模型夹杂解释文字-- 这是一条查询语句之类的注释解析的时候很容易出错。JSON格式强制它把SQL放在一个字段里天然规避了这个问题。核心的提示词结构如下SYSTEM_PROMPT 你是一个SQLite数据库专家。你的任务是根据用户的中文问题生成一条可直接执行的SQLite SQL查询语句。 ## 数据库表结构 {schema_info} ## 重要规则 1. 只能输出JSON格式格式为 {{sql: 这里放SQL语句}}不要输出任何其他内容。 2. SQL必须符合SQLite语法不要使用MySQL或SQL Server方言。 3. 日期字段统一使用TEXT存储的ISO格式字符串日期比较使用 date(field) 函数。 4. 如果用户没有明确指定具体日期当前日期以 date(now) 为准。 5. 订单状态可能取值: 已完成、已取消、待发货、退款中。不要臆造。 6. 默认查询结果不要超过200条。 ## 示例 用户问题: 上个月销量最高的5个商品 {{sql: SELECT product_id, SUM(quantity) AS total_qty FROM orders WHERE date(order_date) date(now, start of month, -1 month) AND date(order_date) date(now, start of month) GROUP BY product_id ORDER BY total_qty DESC LIMIT 5}} 用户问题: {question} 你可能会问为什么在提示词里写死订单状态可能取值这些信息这不是和动态提取元数据矛盾了吗这里需要区分两种信息结构信息和业务语义信息。结构信息表名、字段名、字段类型必须动态提取因为数据库随时可能变化业务语义信息订单状态有哪些枚举值、价格是含税还是未税是相对稳定的写死在提示词里能让模型少走弯路。我的实际经验是模型从示例数据中推断枚举值有时候会猜错这属于不稳定的能力明明可以靠提示词补上就不要留给模型自由发挥。4.2 SQL执行与错误自愈机制模型生成的SQL直接执行大概率第一次不是完全正确的。这里最关键的工程机制是错误自愈。我的方案是把SQL执行包在一个带重试逻辑的函数里第一次执行失败时把错误信息反馈给模型让它自己修改SQL最多重试两到三次。def execute_sql_with_retry(llm_client, db_path, question, schema_info, max_retry3): conn sqlite3.connect(db_path) conn.set_trace_callback(print) # 调试时打印每条SQL cur conn.cursor() system_prompt SYSTEM_PROMPT.format(schema_infoschema_info, questionquestion) messages [{role: system, content: system_prompt}] # 附加历史重试信息 for attempt_num in range(max_retry): response llm_client.chat(messages, temperature0.0) try: parsed json.loads(response.strip()) sql parsed[sql] except (json.JSONDecodeError, KeyError): messages.append({role: user, content: f你的上次输出不是合法JSON请重新输出。上次输出: {response}}) continue print(f生成的SQL: {sql}) if not is_read_only(sql): messages.append({role: user, content: 禁止执行写操作请只生成SELECT语句。}) continue try: cur.execute(sql) rows cur.fetchall() col_names [d[0] for d in cur.description] return rows, col_names, sql except sqlite3.Error as e: messages.append({role: user, content: fSQL执行出错: {e}。请修复SQL后重新输出。}) conn.close() raise RuntimeError(重试多次仍然无法生成可执行的SQL)这个函数的巧妙之处在于它把每次的SQL执行错误信息当作多轮对话中的用户消息返回给模型模型看到错误信息后修正SQL。这个思路的原理是大模型在不知道执行结果的时候只能靠猜但一旦知道了具体的报错信息它的修错能力是很强的。比如模型第一次生成了WHERE order_date BETWEEN 2024-01-01 AND 2024-01-31但SQLite比较字符串和日期格式不成问题反而可能是GROUP BY少了一个字段报错把这些信息原样回传模型能精准地修正语法问题。4.3 查询白名单与安全边界Text2SQL最让人担心的一个问题是模型生成的SQL会不会把数据弄坏。我在系统里加了三道防线。第一道防线只读校验。在is_read_only函数里用正则匹配INSERT、UPDATE、DELETE、DROP、ALTER、CREATE等写操作关键字出现任何一个直接拒绝执行并让模型重新生成。import re def is_read_only(sql: str) - bool: forbidden re.compile(r\b(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|REPLACE|ATTACH|DETACH)\b, re.IGNORECASE) if forbidden.search(sql): return False # 排除注释里的敏感词 return True第二道防线SQLite的PRAGMA query_only设置。这个设置可以在连接级别禁止写操作相当于数据库层面的双保险。conn.execute(PRAGMA query_only ON)第三道防线敏感信息脱敏。如果数据库里面存了手机号、身份证这类敏感信息哪怕查询本身是只读的也不应该让模型直接输出原始值。我在生成结果前会检查列名命中敏感列名则用substr()函数截断或者用***代替。5. 实测效果与典型翻车案例分析5.1 测试用例与效果对比跑通整个链路之后我先设计了一批测试用例覆盖从简单到复杂的各种查询场景。我把实测效果列成一个表格方便你直观参考测试问题期望查询首次生成正确率自愈后正确率总共有多少个用户COUNT(*) FROM users100%100%上海的黄金会员有哪些双条件过滤100%100%每个城市的订单总量GROUP BY city90%100%上个月退货最多的商品多表JOIN聚合排序70%90%各品牌毛利率排名JOIN计算公式ORDER BY50%80%每月新用户注册趋势GROUP BY月份TEXT日期处理60%100%连续两个月都下单的用户自连接子查询30%60%这个表格反映出来的规律很一致单表简单查询模型凭直觉就能写对涉及计算逻辑、时间窗口、子查询这类有一定隐藏规则的查询首次正确率会明显下降但是通过错误自愈机制来回调几次之后大部分能救回来。最后一种连续两个月都下单的用户即使自愈之后只有60%的正确率主要原因在于连续的定义需要多次跟模型澄清提示词没写清楚模型就只能靠猜。5.2 三个最典型的翻车场景与修复方案翻车场景一字段名张冠李戴。模型把actual_amount实付金额当成了unit_price单价导致统计结果偏差。这种问题很难完全靠提示词避免因为字段语义需要模型理解业务。我的解决办法是给每个字段生成一句自然语言注释直接放在元数据信息里。比如在提示词里补充一句You should know that actual_amount represents the final paid amount after discounts, while unit_price is the original list price per item。加上这句语义补充之后准确率能从50%提到90%。翻车场景二日期格式混乱。SQLite里存的是2024-03-15 14:23:11这样带时间的字符串模型生成SQL时经常忘记截断时间直接用order_date 2024-03-15去比较结果一条都查不出来。我通过在提示词里统一强制所有日期比较都使用date(field)函数解决。这属于规则明确就不太会犯错的问题比让模型自己悟要好得多。翻车场景三模型出现幻觉字段。有一次模型生成了一条WHERE category 数码的SQL可这个category字段在商品表里其实叫product_category。这种错误自愈机制完全无法识别因为SQL能正常执行并返回空结果看起来好像没问题。于是我加了一道结果验证提示执行完SQL后如果返回空结果多给模型一次机会提示它查询结果为空请检查字段名和条件值确认是否存在拼写错误或语义理解偏差。这样模型往往会自查出问题并重写。5.3 优化提示词后带来的提升我按不同版本的提示词做了对比实验发现有几点改动效果格外明显。第一个大改动是从裸SQL输出改为JSON结构化输出。裸SQL输出时模型经常性自带解释文字比如生成SELECT ...之后还要加一句这是您需要的查询。JSON输出直接把这个干扰降到零解析成功率从75%提到99%。第二个大改动是加入少样本示例few-shot。在提示词里内置两到三条用户问题-正确答案的示例尤其针对最容易出错的日期处理、多表连接给出一个正面范例。模型模仿能力很强只要示例里展示了正确的格式它多半会照着写。6. 本地部署与工程化落地的几条补充建议6.1 从脚本到服务化调试完成之后我做的第一件事是把它从一段脚本变成一个Web服务。用FastAPI包一层HTTP接口非常快核心就两个端点一个用于提交自然语言问题另一个用于查看数据库的表结构。用起来就像这样from fastapi import FastAPI, HTTPException from pydantic import BaseModel app FastAPI() class QueryRequest(BaseModel): question: str app.post(/query) def query(request: QueryRequest): try: rows, col_names, sql text2sql(request.question) summary summarize_result(rows, col_names, request.question) return {sql: sql, columns: col_names, rows: rows[:200], summary: summary} except Exception as e: raise HTTPException(status_code500, detailstr(e))这个服务化是有实际价值的一方面团队里其他人不需要关心底层是怎么调用大模型的直接请求接口就行另一方面SQL执行结果可以做一层缓存同一类问题短时间内重复查询时直接命中缓存省掉一次模型调用。6.2 后续可以扩展的方向项目做完之后我总结了几条后续可以继续深挖的方向供你参考。第一条是多轮对话能力。目前实现是单轮问答不支持我刚才问的价格和这个问题的价格口径是否一致这种上下文关联。要做多轮就得把对话历史塞进提示词让模型不仅能看当前问题也能参考上一轮的问题和SQL结果。第二条是自动生成字段注释。如果数据库里的表没有注释模型理解字段含义只能靠猜。我用模型的先验知识自动给每个字段生成一行语义描述然后人工确认一遍维护成本很低效果提升明显。这本质上是一个给数据库建立数据字典的功能非常实用。第三条是图表可视化输出。Text2SQL返回的只是表格数据但用户很多时候想要柱状图、折线图。下一步可以让模型根据结果自动推荐图表类型然后把数据喂给ECharts渲染直接把图表嵌到对话回复里。在实际使用中我还有一个体会值得分享给准备复刻这个项目的朋友不要一开始就追求大而全先把单表查询基础聚合跑通后面再逐步增加表连接和复杂查询。这样调试期短、成就感来得快出现问题时也更容易定位是大模型生成SQL的环节出了问题还是SQL执行环节出了问题。用SQLite做底座的好处就在这里整个链路清晰可控任何一个环节出问题都能快速摘出来单独测试。