把自然语言变成 SQL听起来很 AI但真正落地时SQLite 往往是最顺手、最不容易出错的底座。这个项目叫 Text2SQL 小助手其实就是一个能用中文或英文自然语言直接查询 SQLite 数据库的小工具。它的价值在于你不需要记住表名、字段名甚至不需要了解 JOIN 语法只要说出想问的事大模型会帮你写成 SQL再在 SQLite 里执行并回传结果。这篇文章用完整实战的方式从建表、造数据、提示词设计、模型调用到问题排查记录我不加滤镜的踩坑过程。适合想做本地数据查询、内部数据问答、或者刚接触大模型应用的开发者参考即使你之前没写过多少 SQL也可以按步骤跑起来。1. 先把需求和方案想明白1.1 这个项目到底解决什么问题很多团队或个人的数据其实都存在本地文件里最常见的就是 SQLite。它轻量、单文件、不用装数据库服务一个shop.db拖到哪都能用。但 SQLite 也有一个明显的痛点业务同学不会写 SQL。他们想看的是一句话比如上个月卖得最好的书是哪几本而不是一段SELECT ... GROUP BY ... ORDER BY ...。Text2SQL 要解决的就是把这个翻译过程自动化。大模型负责把自然语言转成 SQLSQLite 负责内容存储和查询执行两者配合起来就相当于给数据库装了一个会说人话的查询接口。这类小工具特别适合那些内部报表需求频繁、但又不值得开发完整报表系统的场景也能帮程序员减少重复写一次性查询的工作量。1.2 技术选型为什么是 SQLite 大模型选 SQLite 而不是 MySQL、PostgreSQL原因很简单单文件、零运维、标准库自带驱动。在这个项目里我们的目标是快速验证 Text2SQL 的链路而不是模拟生产环境的高并发查询所以 SQLite 是恰到好处的。它还支持只读连接、内存数据库、事务等特性对做安全校验来说非常方便。大模型方面我选用的是本地部署的 Ollama 加开源模型。相比直接调用云端大模型本地部署的好处有三个一是数据不出内网测试数据再敏感都不慌二是接口调用没有额外费用三是可以反复调整参数不用担心限流。具体模型我推荐qwen2.5-coder:7b它在 SQL 生成类的任务上比通用小模型更稳而且 7B 参数规模对普通电脑的负担不算大。1.3 整体架构和关键流程整个项目可以拆成四层数据层SQLite 数据库文件包含表、数据、索引。提示词层把表结构、字段含义、示例问题整理成模型能理解的上下文。模型层通过 Ollama 提供的 OpenAI 兼容接口调用大模型生成 SQL。执行层拿到 SQL 后先做安全校验再用只读连接执行并返回结果。整体流程是你输入一句自然语言系统把这句话和数据库 schema 一起塞给大模型模型返回一段 SQL执行层校验并查询最后把结果整理成列表或表格输出。你可以理解为一次带保镖的翻译翻译官是大模型保镖是只读连接和 SQL 白名单。2. 建表不是随便写的环境准备与库表设计2.1 环境准备Python、SQLite 和 Ollama我假设你用的是 macOS 或 Linux 环境Windows 操作也大差不差。第一步先把 Python 环境准备好建议 3.9 以上版本。SQLite 不需要单独安装Python 内置了sqlite3模块。不过为了后续可视化检查数据我强烈建议安装一个 DB Browser for SQLite它能看到表结构、执行 SQL、导出数据排查问题会舒服很多。大模型部分需要安装 Ollama。装好后在终端拉取模型ollama pull qwen2.5-coder:7b这个模型大概 4GB 多拉取时间和网络有关。拉完后直接启动服务Ollama 默认会在本地11434端口起一个 OpenAI 兼容的接口我们后面用 Python 的openai库直接调它。接着创建项目目录并安装依赖mkdir text2sql-demo cd text2sql-demo python -m venv venv source venv/bin/activate pip install openai只装一个openai就够了SQLite 内置不需要额外库。之所以用openai这个库是因为 Ollama 兼容 OpenAI 的chat/completions协议我们不用自己拼 HTTP 请求写起来更省事。2.2 建表 SQL 与数据填充一个贴近真实业务的书店订单库为了演示我设计了一个小型的书店订单模型包含四张表顾客、图书、订单、订单明细。四张表能覆盖 SELECT、JOIN、聚合、子查询、日期过滤等常见场景对测试 Text2SQL 非常够用。CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE, city TEXT, created_at TEXT NOT NULL DEFAULT (datetime(now, localtime)) ); CREATE TABLE books ( book_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, category TEXT, price REAL NOT NULL DEFAULT 0, stock INTEGER NOT NULL DEFAULT 0 ); CREATE TABLE orders ( order_id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id INTEGER NOT NULL REFERENCES customers(customer_id), order_date TEXT NOT NULL, total_amount REAL NOT NULL DEFAULT 0, status TEXT NOT NULL DEFAULT pending ); CREATE TABLE order_items ( order_item_id INTEGER PRIMARY KEY AUTOINCREMENT, order_id INTEGER NOT NULL REFERENCES orders(order_id), book_id INTEGER NOT NULL REFERENCES books(book_id), quantity INTEGER NOT NULL DEFAULT 1, unit_price REAL NOT NULL DEFAULT 0 );设计这四张表时我刻意加了几个细节时间字段都用文本类型保存SQLite 没有原生的 datetime 类型比较时直接用 ISO 格式字符串最安全价格字段用REAL金额明细表同时保存unit_price防止书价后来调整导致历史订单计算错误状态字段用默认值pending方便模拟不同订单状态。建表后插入一批模拟数据不需要很多但要让各种查询都有结果。比如不同城市的顾客、不同分类的图书、不同状态的订单、有退单或取消的订单等。插入时可以直接用一条大的 INSERT 脚本也可以用 Python 循环写入。我在实际测试时习惯用 Python 脚本一次性灌数据因为后续想改数据格式只需改脚本重新执行。2.3 用 DB Browser for SQLite 验证表结构建完表后打开 DB Browser for SQLite关联到项目目录下的shop.db在 数据库结构 标签里能看到四个表点开任意表能看到字段和约束。但我不建议只肉眼看最可靠的方式是在 SQLite 命令行里执行两条元数据查询SELECT name, type FROM sqlite_master WHERE typetable;PRAGMA table_info(orders);sqlite_master是 SQLite 的系统表能看到所有表、索引和视图PRAGMA table_info能列出表的每一列。这两个查询是我们后面给大模型写 schema 文档的素材也是排查建表异常的关键。如果你发现字段类型不对、默认值没生效多半是 CREATE TABLE 语句里的括号或引号有问题对照table_info的返回结果就能快速定位。3. 核心实战让大模型把中文问题翻译成 SQL3.1 提示词工程规范、表结构和示例缺一不可Text2SQL 的效果很大程度取决于提示词而不是模型本身。我见过不少人一上来就调大模型参数其实更值得调的是给模型看的上下文。我的提示词分三块。第一块是系统角色约束明确告诉模型只生成 SQLite 兼容的 SQL不要解释不要使用其他数据库特有的函数。第二块是表结构清单列出每个表的字段和简要含义。第三块是 few-shot 示例给三五个问题 - SQL对让模型模仿风格。表结构说明不能照搬 CREATE TABLE 语句太长了而且模型会纠结约束细节。我一般整理成一个精简文档只保留表名、字段名和类型/含义SCHEMA_DOC 表名customers顾客 - customer_id INTEGER 主键 - name TEXT 顾客姓名 - email TEXT 邮箱 - city TEXT 所在城市 - created_at TEXT 注册时间 表名books图书 - book_id INTEGER 主键 - title TEXT 书名 - category TEXT 分类 - price REAL 价格 - stock INTEGER 库存数量 表名orders订单 - order_id INTEGER 主键 - customer_id INTEGER 顾客外键 - order_date TEXT 下单时间 - total_amount REAL 订单总额 - status TEXT 订单状态pending/paid/cancelled 表名order_items订单明细 - order_item_id INTEGER 主键 - order_id INTEGER 订单外键 - book_id INTEGER 图书外键 - quantity INTEGER 数量 - unit_price REAL 成交单价 3.2 调用大模型的封装本地 Ollama OpenAI SDK本地 Ollama 启动后我们可以用openai库封装一个generate_sql函数。注意base_url指向http://localhost:11434/v1api_key随便填一个字符串Ollama 不会校验。from openai import OpenAI client OpenAI( base_urlhttp://localhost:11434/v1, api_keyollama, ) def generate_sql(question: str, schema_doc: str SCHEMA_DOC) - str: system_prompt 你是一个只写 SQLite SQL 查询语句的助手。 只输出 SQL 本身不要输出任何解释。 不要使用 SQLite 之外的数据库语法。 使用 SQLite 内置函数date、datetime、strftime、round、abs、length 等。 回答时使用中文列别名方便阅读。 user_content f 数据库表结构如下 {schema_doc} 请根据以下问题构造一条 SQL 查询 问题{question} SQL response client.chat.completions.create( modelqwen2.5-coder:7b, messages[ {role: system, content: system_prompt}, {role: user, content: user_content}, ], temperature0.1, max_tokens500, ) return response.choices[0].message.content.strip()这里temperature0.1很重要。SQL 生成是偏确定性的任务不需要模型发挥创意温度越高越容易出现字段名写错、列名不存在的问题。max_tokens500足够覆盖绝大多数查询太长反而可能让模型开始输出额外文字。3.3 让生成的 SQL 只读可复核执行层必须做的事大模型不是数据库专家它生成的 SQL 不一定永远正确更不一定安全。所以在执行之前我们必须过两道关。首先是做语法和语义校验。最简单实用的办法是使用 SQLite 的只读连接这样即使模型输出意外的 DELETE 或 UPDATE也只能报错而不能改数据import sqlite3 def get_readonly_conn(db_path: str) - sqlite3.Connection: conn sqlite3.connect(ffile:{db_path}?modero, uriTrue) conn.row_factory sqlite3.Row return conn第二关是字符串级别检查。只读连接能防止写坏库但没法防止模型生成一个恶意的 SELECT 子查询比如读其他系统表所以最好限制 SQL 必须以 SELECT 开头并且不包含PRAGMA、ATTACH、sqlite_master、DELETE、UPDATE、INSERT、DROP等关键词。这套检查不是绝对安全但对于本地小工具已经够了。FORBIDDEN_WORDS [ pragma, attach, detach, delete, update, insert, drop, alter, create, replace, vacuum, reindex, ] def validate_sql(sql: str) - str: lowered sql.strip().lower() if not lowered.startswith(select): raise ValueError(只允许执行 SELECT 查询) for word in FORBIDDEN_WORDS: if word in lowered: raise ValueError(fSQL 中包含禁止的关键字: {word}) return sql.strip()你可能会问生成了 JOIN 查询为什么不直接执行 因为只读连接已经能防止数据篡改为什么还要关键词检查原因很简单多一层防御总比少一层好。尤其当你把这个工具暴露给团队其他人用时你永远不知道他们会输入什么问题、模型会被引导生成什么 SQL。4. 完整联调从自然语言到查询结果4.1 一个可运行的 Text2SQL 主程序现在我们把上面所有的函数串起来写一个ask主函数。它接收一个自然语言问题返回查询结果的列表和生成 SQL。如果执行失败我们会把错误信息重新喂给模型让它修正一次这是非常实用的重试策略。def execute_sql(conn: sqlite3.Connection, sql: str, limit: int 20): sql validate_sql(sql) cur conn.execute(sql) cols [d[0] for d in cur.description] rows cur.fetchmany(limit) return cols, rows def ask(conn: sqlite3.Connection, question: str, max_retry: int 1): sql generate_sql(question) print(f生成 SQL: {sql}) try: cols, rows execute_sql(conn, sql) return cols, rows except Exception as e: if max_retry 0: raise print(f执行失败尝试让模型修正: {e}) corrected_sql generate_sql( f请修正以下 SQL错误信息: {e}\n原问题: {question}\n原 SQL: {sql} ) cols, rows execute_sql(conn, corrected_sql) return cols, rows这段代码的逻辑是生成 SQL - 执行 - 出错就让模型看看错误信息再生成一次。很多偶然性的语法错误比如字段名写错、函数多了一个括号第二次生成时就能自动修正。这比用户手动改 SQL 友好得多。4.2 跑通多个自然语言查询样例我准备了几个典型问题直接在ask里跑看看效果。第一个问题是销量最高的3本书是哪些 这个查询需要 JOIN 订单明细和图书表按数量求和后取前三。 ask(conn, 销量最高的3本书是哪些) 生成 SQL: SELECT b.title AS 书名, SUM(oi.quantity) AS 销量 FROM order_items oi JOIN books b ON oi.book_id b.book_id GROUP BY b.book_id, b.title ORDER BY 销量 DESC LIMIT 3; 查询结果: ------------------------------------------ | 书名 | 销量 | ------------------------------------------ | Deep Learning | 18 | | 算法导论 | 12 | | 流畅的Python | 9 | ------------------------------------------第二个问题是每个城市的顾客有多少人 这是简单的分组计数一般不会出错。 ask(conn, 每个城市的顾客有多少人) 生成 SQL: SELECT city AS 城市, COUNT(*) AS 顾客数 FROM customers GROUP BY city ORDER BY 顾客数 DESC; 查询结果: ------------------- | 城市 | 顾客数 | ------------------- | 上海 | 3 | | 北京 | 4 | | 广州 | 2 | -------------------第三个问题稍微绕一点上个月已支付订单的总额是多少 SQLite 的时间函数和字符串日期格式容易踩坑但模型在 few-shot 里如果看到过类似写法一般能生成正确结果。 ask(conn, 上个月已支付订单的总额是多少) 生成 SQL: SELECT SUM(total_amount) AS 总额 FROM orders WHERE status paid AND order_date date(now, localtime, start of month, -1 month) AND order_date date(now, localtime, start of month); 查询结果: -------- | 总额 | -------- | 1280.5 | --------如果你跑出来的 SQL 和我不完全一样不用慌只要结果含义一致就算对。Text2SQL 本来就是一道开放式翻译题不要求唯一答案。4.3 结果格式化和异常处理查询结果拿到以后不能直接丢给用户。我把cols和rows用简单的表格函数格式化方便在终端输出也方便将来对接 Web 页面。def print_table(cols, rows): print( | .join(cols)) for r in rows: print( | .join(str(x) for x in r))实际展示时你可以用tabulate或pandas来展示更好看的表格。我不推荐在核心链路里引入太重的依赖但如果你只想快速看结果pandas的DataFrame是零思考的选择。要注意的是SQL 执行结果可能非常大我在execute_sql里加了fetchmany(20)避免模型生成一个不合理的全表查询时把终端刷爆。还有一个容易被忽略的点如果查询出来的 INTEGER 字段太大或者 REAL 字段的小数位数很多直接 print 会很丑。建议在格式化阶段统一处理比如金额保留两位小数日期只取前 10 位。5. 常见问题和避坑指南5.1 模型生成 SQL 不稳定先从提示词和示例下手如果你发现同一个问题有时生成得好、有时生成得差先不要怪模型。大概率是提示词里的表结构不够清晰或者没有给足示例。我在实际测试中遇到过一个典型问题问买过《算法导论》的顾客来自哪些城市模型第一次写出的 SQL 用了LIKE去模糊匹配书名结果没查出来。后来我在提示词里的 schema 中明确写了books.title 是精确书名不需要模糊匹配模型就正常了。另外你可以在SCHEMA_DOC里增加查询注意点这一节比如注意 1. 订单状态字段值为 pending/paid/cancelled。 2. 订单时间字段 order_date 格式为 YYYY-MM-DD。 3. 统计销量时应该关联 order_items 和 books。这比在 system prompt 里空泛地写你要准确有用得多。模型需要的是具体的、可以遵循的事实而不是空洞的鼓励。5.2 查询结果太长加一个结构化的结果截断第一次跑通的时候我问列出所有库存不足50本的书结果返回了 32 行终端直接被刷屏。后来我在execute_sql里加了一个limit参数默认 20 行。如果你希望返回总数可以额外执行一次SELECT COUNT(*)。但要注意不能直接在原 SQL 后面拼LIMIT 20因为有些 SQL 本身已经带了LIMIT拼接会导致语法错误。更稳的方式是用子查询包一层SELECT * FROM (原始SQL) LIMIT 20;不过最稳妥的做法还是让大模型在生成时遵循默认限制20行的规则。在 system prompt 里加一句如果问题中没有明确指定数量SQL 末尾添加 LIMIT 20可以显著减少结果过长的问题。5.3 sqlite 建表异常和锁库问题建表时最常见的异常是sqlite3.OperationalError: table xxx already exists。这通常是因为脚本重复运行或者表名拼写不一致。解决办法是加IF NOT EXISTS或者在初始化前先删除旧表。开发阶段直接删掉shop.db重新生成即可省心。另一个容易踩的坑是database is locked。SQLite 同一时刻只允许一个写连接多线程环境里如果开启了多个连接很容易撞锁。解决方式有两个一是把连接设为check_same_threadFalse二是给数据库开 WAL 模式。在 Text2SQL 项目里我们的执行连接是只读的所以遇到锁的概率不大但如果你在初始化数据步骤里和查询混用建议正确关闭所有写连接。conn sqlite3.connect(shop.db) conn.execute(PRAGMA journal_modeWAL;)WAL 模式下读连接不会被写连接阻塞查询体验会好很多。5.4 正确理解大模型的幻觉限定范围比反复提示更有效大模型生成 SQL 时最让人头疼的不是语法错误而是它编造字段。比如它可能凭空生成一个books.author字段而我们的表里根本没有author这一列。这种幻觉靠重复提示不要编造字段几乎没用更好的办法是让模型直接看到完整的字段清单同时要求它只能从清单里选字段并且在生成后做一个字段存在性预检。预检可以很简单从生成的 SQL 里提取所有出现在SCHEMA_DOC中的字段名逐一对比数据库真实字段。这个正则提取写得粗糙一点也能用import re def check_fields_exist(sql: str, schema_doc: str) - list: fake_fields [] for field in re.findall(r\b[a-z_]\b, sql.lower()): if field in {select, from, where, group, order, by, limit, join, on, and, or, count, sum, avg, min, max, as, left, inner, distinct, case, when, then, end, between, like, not, null, desc, asc}: continue if field not in schema_doc.lower(): fake_fields.append(field) return fake_fields这个检查会有误报比如表别名但因为我们可以不追求完美只要它拦截住明显不存在的列名就能省很多排查时间。实际上提示词里字段清单越详细幻觉出现的概率越低。我自己的经验是只要把字段含义写清楚7B 级别的模型就能在 90% 以上场景给出正确 SQL。6. 进一步的想法与我的实操心得6.1 从单表查询走向多表关联这个项目里的四张表已经能覆盖多表 JOIN但你真要拿去做业务需要把 schema 文档扩展得更细致。比如给每个外键注明orders.customer_id 关联 customers.customer_id给常用查询场景写几个 JOIN 示例。模型非常依赖示例你给的 JOIN 例子越多它就越容易在使用到多表时选对关联条件。另外你也可以在 SQLite 里创建几个视图来简化复杂查询再把视图名写进 schema。这样大模型在多数情况下只需要单表查询视图不容易出错。视图不会增加数据却能大幅降低 Text2SQL 的复杂度。6.2 围绕 Text2SQL 的工程化补充如果这个工具要给别人用我会建议再加三个功能查询缓存。相同问题的 SQL 结果可以缓存 5 分钟降低模型调用频率。日志记录。记录每次输入、生成 SQL、执行结果和耗时方便事后分析模型哪些问题容易出错。权限控制。即使有只读连接还是应该限制可访问的表名比如允许访问业务表但禁止访问系统表。我实际跑下来单次查询的耗时主要在大模型生成 SQL 上7B 模型一般需要 3 到 10 秒执行 SQL 几乎不耗时。如果嫌慢可以把模型换成更小的qwen2.5-coder:3b但选小模型时建议多测试 JOIN 场景避免生成质量下降明显。6.3 我给刚入门同学的几点建议想玩转 SQLite 大模型不需要一开始就急着微调模型。先把提示词、表结构描述、few-shot 例子做到位7B 模型完全够用。等遇到领域词汇很多、数据表特别复杂的场景再考虑微调也不迟。我个人做过几个类似项目后最大的体会是Text2SQL 的难点不在调用大模型而在提示词设计和风险控制。不要把大模型当成万能查数接口也不要对生成的 SQL 不加检查就执行。正确的心态是把它当成一个翻译候选器永远保留人工复核 SQL 的入口。这样即使偶尔翻错也不会把数据库弄坏更不会误导最终决策。