环境配置

• 这里需要注意的是，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 pymysql


def db_connet():
    conn = pymysql.connect(
        host='127.0.0.1',  # 连接名称，默认127.0.0.1
        user='root',  # 用户名
        passwd='123456',  # 密码
        port=3306,  # 端口，默认为3306
        db='zentao',  # 数据库名称
        charset='utf8',  # 字符编码
    )
    print("----数据库连接成功------", conn)
    return conn
print(db_connet())

向量化模型下载配置

1. 模型下载

2. 缺少环境包，自行安装一下，下载向量化onnx嵌入模型。下载成功后，模型会保存在本地home/.cach/modelscope/下。

#模型下载
from modelscope import snapshot_download
model_dir = snapshot_download('wengad/all-MiniLM-L6-v2')
 #在本地./cach/modelscope/

2. 向量化模型配置
   home/.cach/modelscope/all-MiniLM-L6-v2文件下的压缩包加压后，放在 /home/.cache/chroma/onnx_models/all-MiniLM-L6-v2/onnx目录下。

vann 本地运行

1. vanna本地环境测试
2. 导入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)
#使用pymysql直接连接本地禅道数据库，获取数据库表结构及其备注信息。
#/opt/zbox/zbox 基本配置信息
import pymysql
import csv
from datetime import datetime
#使用pymysql直接连接本地禅道数据库，获取数据库表结构及其备注信息。
#/opt/zbox/zbox 基本配置信息
import os.path

import pymysql
import csv
from datetime import datetime
def sql_create():
    create_list=[]
    conn = pymysql.connect(
        host='127.0.0.1',  # 连接名称，默认127.0.0.1
        user='root',  # 用户名
        passwd='123456',  # 密码
        port=3306,  # 端口，默认为3306
        db='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_teamWork
    sql_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_list


vn = 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)

3. 页面测试效果
   http://localhost:5001