根据前章,我们部署了ollama,此章刚好用来测试text2sql -----vanna
安装部署
1.安装部署
https://vanna.ai/docs/mysql-ollama-chromadb/
根据官网安装部署就可以了,官网很详细,我这次选择Ollama、ChromaDB和MySQL
from vanna.ollama import Ollama
from vanna.chromadb import ChromaDB_VectorStore
class MyVanna(ChromaDB_VectorStore, Ollama):
def __init__(self, config=None):
ChromaDB_VectorStore.__init__(self, config=config)
Ollama.__init__(self, config=config)
vn = MyVanna(config={'model': 'mistral'})
vn.connect_to_mysql(host='my-host', dbname='my-db', user='my-user', password='my-password', port=123)
以上就是官网代码,根据需要,改成自己的配置
以下是我的配置
from vanna.ollama import Ollama
from vanna.chromadb import ChromaDB_VectorStore
class MyVanna(ChromaDB_VectorStore, Ollama):
def __init__(self, config=None):
ChromaDB_VectorStore.__init__(self, config=config)
Ollama.__init__(self, config=config)
vn = MyVanna(config={'model': 'qwen2:latest','ollama_host':'http://localhost:11434'})
vn.connect_to_mysql(host='xx.xx.xx.xx', dbname='demodb', user='root', password='xxxxx@202', port=30306)
这中间我遇到了个问题,总结一下
1.报错:
“AttributeError: ‘Collection‘ object has no attribute ‘model_fields‘
因为chromadb版本问题,版本更新以后可能把这个属性给更新没了之类的,降下版本就可以了
pip uninstall chromadb
pip install chromadb==0.5.3
创建表
执行以上代码就可以连接上vanna了,接下来需要将sql喂入大模型,我们先创建一个表,插入连接的数据库demodb
CREATE TABLE IF NOT EXISTS vuser (
`id` INT PRIMARY KEY COMMENT '用户ID',
username VARCHAR(50) COMMENT '用户名',
email VARCHAR(100) COMMENT '电子邮件',
age INT COMMENT '年龄',
gender VARCHAR(10) COMMENT '性别(男/女)',
city VARCHAR(50) COMMENT '城市'
) COMMENT='用户信息表' CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO vuser (`id`, username, email, age, gender, city) VALUES
(1, '张三', 'zhangsan@example.com', 30, '男', '北京'),
(2, '李四', 'lisi@example.com', 25, '女', '上海'),
(3, '王五', 'wangwu@example.com', 40, '男', '广州'),
(4, '赵六', 'zhaoliu@example.com', 35, '女', '深圳'),
(5, '小明', 'xiaoming@example.com', 28, '男', '成都'),
(6, '小红', 'xiaohong@example.com', 45, '女', '重庆'),
(7, '小华', 'xiaohua@example.com', 32, '男', '天津'),
(8, '小丽', 'xiaoli@example.com', 27, '女', '南京'),
(9, '小李', 'xiaoli2@example.com', 38, '男', '武汉'),
(10, '小美', 'xiaomei@example.com', 33, '女', '西安');
训练
from vanna.ollama import Ollama
from vanna.chromadb import ChromaDB_VectorStore
class MyVanna(ChromaDB_VectorStore, Ollama):
def __init__(self, config=None):
ChromaDB_VectorStore.__init__(self, config=config)
Ollama.__init__(self, config=config)
vn = MyVanna(config={'model': 'qwen2:latest','ollama_host':'http://localhost:11434'})
vn.connect_to_mysql(host='10.5.2.28', dbname='demodb', user='root', password='sungrow@2023', port=30306)
vn.train(ddl="""
CREATE TABLE `vuser` (
`id` int NOT NULL COMMENT '用户ID',
`username` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
`email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '电子邮件',
`age` int DEFAULT NULL COMMENT '年龄',
`gender` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别(男/女)',
`city` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '城市',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户信息表';
""")
如此,训练完成
web界面
对话,启动内置的集成flask框架的web界面
from vanna.flask import VannaFlaskApp
from vanna.ollama import Ollama
from vanna.chromadb import ChromaDB_VectorStore
class MyVanna(ChromaDB_VectorStore, Ollama):
def __init__(self, config=None):
ChromaDB_VectorStore.__init__(self, config=config)
Ollama.__init__(self, config=config)
vn = MyVanna(config={'model': 'qwen2:latest', 'ollama_host': 'http://localhost:11434'})
vn.connect_to_mysql(host='10.5.2.28', dbname='demodb', user='root', password='sungrow@2023', port=30306)
app = VannaFlaskApp(vn)
app.run()
这里可能回遇到问题
ImportError: cannot import name ‘Markup’ from ‘flask’
同样也是flask版本的问题,以下版本没有问题
pip install flask==2.3.1
进入web界面,提问,提问成功!