# 更新系统包
sudo apt update && sudo apt upgrade -y

# 安装Python和pip
sudo apt install python3 python3-pip -y

# 安装MySQL客户端和开发库
sudo apt install mysql-client libmysqlclient-dev -y

# 安装Python虚拟环境工具
pip3 install virtualenv

mkdir langchain-mysql-vectorization && cd langchain-mysql-vectorization

python3 -m virtualenv venv
source venv/bin/activate

pip install langchain openai mysql-connector-python tiktoken faiss-cpu numpy pandas

CREATE DATABASE IF NOT EXISTS product_db;
USE product_db;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入示例数据
INSERT INTO products (name, description, category, price) VALUES 
('智能手机X', '最新款智能手机，6.5英寸屏幕，128GB存储', '电子', 599.99),
('无线耳机Pro', '降噪无线耳机，30小时续航', '电子', 199.99),
('棉质T恤', '100%纯棉，多种颜色可选', '服装', 29.99),
('咖啡机', '全自动咖啡机，15bar压力', '家电', 349.99);

# config.py
MYSQL_CONFIG = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'product_db'
}

# mysql_loader.py
import mysql.connector
from config import MYSQL_CONFIG

class MySQLLoader:
    def __init__(self):
        self.connection = mysql.connector.connect(**MYSQL_CONFIG)

    def load_products(self):
        """从MySQL加载产品数据"""
        cursor = self.connection.cursor(dictionary=True)
        query = "SELECT id, name, description, category FROM products"
        cursor.execute(query)
        return cursor.fetchall()

    def close(self):
        """关闭数据库连接"""
        self.connection.close()

# vectorizer.py
from langchain.text_splitter import CharacterTextSplitter
from langchain.document_loaders import TextLoader
from langchain.document_loaders import DataFrameLoader
from langchain.embeddings.openai import OpenAIEmbeddings
from langchain.vectorstores import FAISS
import pandas as pd

class DataVectorizer:
    def __init__(self, openai_api_key):
        self.openai_api_key = openai_api_key

    def vectorize_data(self, data):
        """
        将结构化数据转换为向量

        参数:
            data: List[dict] - MySQL查询结果

        返回:
            FAISS向量存储对象
        """
        # 转换为DataFrame以便处理
        df = pd.DataFrame(data)

        # LangChain DataFrame加载器会自动处理文本字段的拆分和嵌入生成

        # OpenAI嵌入模型初始化 (确保已设置OPENAI_API_KEY环境变量)
        embeddings = OpenAIEmbeddings(openai_api_key=self.openai_api_key)

        # FAISS作为本地向量存储 (无需额外服务)

        # LangChain文档格式转换 (每个产品作为一个文档)
        documents = []

        for _, row in df.iterrows():
            content = f"Name: {row['name']}\nDescription: {row['description']}\nCategory: {row['category']}"
            metadata = {"id": row["id"], "source": "mysql"}
            documents.append({"page_content": content, "metadata": metadata})

        # FAISS向量存储创建和索引构建 (内存中)

        db = FAISS.from_documents(
            documents=[Document(**doc) for doc in documents],
            embedding=embeddings,
            metadatas=[doc["metadata"] for doc in documents]
        )

        return db

    def save_vector_store(self, db, path="faiss_index"):
        """保存向量存储到本地"""
        db.save_local(path)

    def load_vector_store(self, path="faiss_index"):
        """从本地加载向量存储"""
        embeddings = OpenAIEmbeddings(openai_api_key=self.openai_api_key)
        return FAISS.load_local(path, embeddings)

# main.py
from mysql_loader import MySQLLoader 
from vectorizer import DataVectorizer 
import os 

def main():
    # MySQL数据加载 
    loader = MySQLLoader() 

    try:
        products = loader.load_products() 

        if not products:
            print("没有获取到产品数据")
            return

         # OpenAI API密钥 (建议通过环境变量设置) 
         openai_api_key = os.getenv("OPENAI_API_KEY") 

         if not openai_api_key:
             raise ValueError("请设置OPENAI_API_KEY环境变量")

          # 数据向量化处理 
          vectorizer = DataVectorizer(openai_api_key) 
          vector_db = vectorizer.vectorize_data(products) 

          #保存向量索引 
          vectorizer.save_vector_store(vector_db) 

          print(f"成功处理{len(products)}条记录并生成向量索引")

     finally:
         loader.close() 

if __name__ == "__main__":
     main()

# query_test.py from vectorizer import DataVectorizer from config import MYSQL_CONFIG import os 

def test_query(query): """测试语义搜索""" openai_api_key=os.getenv("OPENAI_API_KEY") vectorizer=DataVectorizer(openai_api_key) db=vectorizer.load_vector_store() results=db.similarity_search(query,k=2) for i,doc in enumerate(results): print(f"\n结果{i+1}:") print(f"内容:{doc.page_content}") print(f"元数据:{doc.metadata}") print(f"相似度得分:{db.similarity_search_with_score(query)[i][1]}") if __name__=="__main__": query=input("请输入搜索查询:") test_query(query)