vannaai本地部署安装
- 环境配置
- 数据库配置
- 向量化模型下载配置
- vann 本地运行
环境配置
- 这里需要注意的是，python版本3.7，安装会报错，最好用更高版本的Python.
conda create -n vanna -y python=3.9
conda activate vanna
pip install 'vanna[chromadb,ollama,mysql]'
pip install ipykernel
数据库配置
本人用的是开源的禅道系统数据库，请保证本地的mysql数据库已经正常运行，并且数据库已经连接成功。
import pymysqldef db_connet():conn = pymysql.connect(host='127.0.0.1', # 连接名称，默认127.0.0.1user='root', # 用户名passwd='123456', # 密码port=3306, # 端口，默认为3306db='zentao', # 数据库名称charset='utf8', # 字符编码)print("----数据库连接成功------", conn)return conn
print(db_connet())
向量化模型下载配置
-
模型下载
-
缺少环境包，自行安装一下，下载向量化onnx嵌入模型。下载成功后，模型会保存在本地home/.cach/modelscope/下。
#模型下载
from modelscope import snapshot_download
model_dir = snapshot_download('wengad/all-MiniLM-L6-v2')#在本地./cach/modelscope/
- 向量化模型配置
home/.cach/modelscope/all-MiniLM-L6-v2文件下的压缩包加压后，放在 /home/.cache/chroma/onnx_models/all-MiniLM-L6-v2/onnx目录下。
vann 本地运行
- vanna本地环境测试
- 导入mysql数据库中的数据、
from vanna.ollama import Ollama
from vanna.chromadb import ChromaDB_VectorStoreclass MyVanna(ChromaDB_VectorStore, Ollama):def __init__(self, config=None):ChromaDB_VectorStore.__init__(self, config=config)Ollama.__init__(self, config=config)
#使用pymysql直接连接本地禅道数据库，获取数据库表结构及其备注信息。
#/opt/zbox/zbox 基本配置信息
import pymysql
import csv
from datetime import datetime
#使用pymysql直接连接本地禅道数据库，获取数据库表结构及其备注信息。
#/opt/zbox/zbox 基本配置信息
import os.pathimport pymysql
import csv
from datetime import datetime
def sql_create():create_list=[]conn = pymysql.connect(host='127.0.0.1', # 连接名称，默认127.0.0.1user='root', # 用户名passwd='123456', # 密码port=3306, # 端口，默认为3306db='zentao', # 数据库名称charset='utf8', # 字符编码)print(conn)# 获取游标cursor = conn.cursor()print(cursor)# 执行sql语句execute和executemany# 定义要执行的SQL语句列表# ex_bid，ex_bidPmProject，ex_bidProject，# ex_bidSocialInsurance，ex_bidTeam，ex_company，# ex_dept，ex_file，ex_project，ex_team，ex_teamProject，# ex_teamQualification，ex_teamWorksql_statements = ["SHOW CREATE TABLE ex_bid;","SHOW CREATE TABLE ex_bidPmProject;","SHOW CREATE TABLE ex_bidProject;","SHOW CREATE TABLE ex_bidSocialInsurance;","SHOW CREATE TABLE ex_bidTeam;","SHOW CREATE TABLE ex_company;","SHOW CREATE TABLE ex_dept;","SHOW CREATE TABLE ex_file;","SHOW CREATE TABLE ex_project;","SHOW CREATE TABLE ex_team;","SHOW CREATE TABLE ex_teamProject;","SHOW CREATE TABLE ex_teamQualification;","SHOW CREATE TABLE ex_teamWork;"]# 循环执行每条SQL语句current_time=datetime.now().strftime('%Y%m%d%H%M%S')csv_file_path = f'./data_out/table_structures_{current_time}.csv'if not os.path.exists('./data_out/'):os.mkdir('./data_out/')with open(csv_file_path, 'w', newline='', encoding='utf-8') as csvfile:csv_writer = csv.writer(csvfile)# 写入标题行csv_writer.writerow(['Table', 'Create Statement'])for sql in sql_statements:cursor.execute(sql)# cursor.execute("SHOW CREATE TABLE zt_story;")# 循环执行每条SQL语句results = cursor.fetchall()for result in results:# result[0]是表名，result[1]是创建表的完整语句csv_writer.writerow(result)create_list.append(result)cursor.close() # 关闭查询游标conn.commit() # 事务的提交conn.close() # 查询完毕，需要关闭连接，释放计算机资源print('sql执行成功')return create_listvn = MyVanna(config={'model': 'deepseek-r1:7b','ollama_host':'http://localhost:11434'})vn.connect_to_mysql(host='127.0.0.1', dbname='zentao', user='root', password='123456', port=3306)# The information schema query may need some tweaking depending on your database. This is a good starting point.
df_information_schema = vn.run_sql("SELECT * FROM INFORMATION_SCHEMA.COLUMNS")# This will break up the information schema into bite-sized chunks that can be referenced by the LLM
plan = vn.get_training_plan_generic(df_information_schema)results=sql_create()
for result in results:vn.train(ddl=result[1])
# with open('./sql_data/ex_bidProject.sql', 'r', encoding='utf-8') as file:
# sql_script = file.read()
# vn.train(ddl=sql_script)
# # 执行SQL脚本from vanna.flask import VannaFlaskApp
app = VannaFlaskApp(vn)
app.run(port=5001)- 页面测试效果
http://localhost:5001
