sudo apt update
sudo apt upgrade -y

sudo apt install mysql-server -y

sudo mysql_secure_installation

sudo systemctl status mysql

# 启动服务
sudo systemctl start mysql

# 停止服务
sudo systemctl stop mysql

# 重启服务
sudo systemctl restart mysql

# 设置开机启动
sudo systemctl enable mysql

mysql -u root -p

-- MySQL命令行中执行以下SQL语句：
CREATE DATABASE langchain_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'langchain_user'@'localhost' IDENTIFIED BY 'YourSecurePassword123!';

GRANT ALL PRIVILEGES ON langchain_db.* TO 'langchain_user'@'localhost';

FLUSH PRIVILEGES;

python3 -m venv langchain_env
source langchain_env/bin/activate

pip install langchain langchain-community pymysql python-dotenv sqlalchemy openai tiktoken

from langchain_community.utilities import SQLDatabase
from dotenv import load_dotenv
import os

# Load environment variables from .env file if present (recommended for production)
load_dotenv()

# Database connection configuration (replace with your actual credentials)
db_config = {
    "host": "localhost",
    "port": "3306",
    "database": "langchain_db",
    "user": "langchain_user",
    "password": os.getenv("DB_PASSWORD", "YourSecurePassword123!"), # Better to use environment variables for production!
    "charset": "utf8mb4"
}

# Create database URI for SQLAlchemy (required by LangChain)
db_uri = f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}"

# Initialize the SQLDatabase instance from LangChain
try:
    db = SQLDatabase.from_uri(db_uri)
    print("✅ Successfully connected to MySQL database!")
except Exception as e:
    print(f"❌ Connection failed: {e}")
    exit(1)

# Create a simple table and insert some data for demonstration purposes (optional)
def setup_demo_data():
    try:
        # Execute raw SQL to create a table if it doesn't exist
        db.run("""
            CREATE TABLE IF NOT EXISTS documents (
                id INT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(255) NOT NULL,
                content TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                embedding TEXT COMMENT 'For storing vector embeddings'
            )
        """)

        # Insert some sample data if the table is empty (optional)
        result = db.run("SELECT COUNT(*) FROM documents")
        if int(result) ==  0:
            db.run("""
                INSERT INTO documents (title, content) VALUES 
                ('Getting Started with LangChain', 'This is a guide for beginners...'),
                ('Advanced MySQL Techniques', 'Learn about indexing and optimization...'),
                ('AI and Databases', 'How AI is transforming database management...')
            """)
            print("📊 Added demo data to the documents table")

        return True

    except Exception as e:
        print(f"Error setting up demo data: {e}")
        return False

if setup_demo_data():
    # Example query using LangChain's SQLDatabase functionality

    # Get table info (schema)
    print("\n📋 Table information:")
    print(db.get_table_info())

    # Run a simple query through LangChain interface (returns raw results)
    query = "SELECT id, title FROM documents LIMIT  3"

    print(f"\n🔍 Executing query: {query}")

    try:
        result = db.run(query)
        print("\n📄 Query results:")
        print(result)

        # More advanced example: get structured results as dictionaries/JSON

        from sqlalchemy import text

        with db._engine.connect() as connection:
            stmt = text(query)
            structured_result = connection.execute(stmt).fetchall()

            print("\n💡 Structured results:")
            for row in structured_result:
                print(dict(row))

    except Exception as e:
        print(f"Query execution error: {e}")

from langchain_community.chat_models import ChatOpenAI  
from langchain_experimental.sql import SQLDatabaseChain  

def setup_sql_agent():
    try:
        llm = ChatOpenAI(
            temperature=0, 
            model="gpt-3.5-turbo",
            openai_api_key=os.getenv("OPENAI_API_KEY") # Set this in your .env file!
        )

        db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)

        return db_chain

    except Exception as e:
        print(f"Error setting up SQL agent: {e}")
        return None

if __name__ == "__main__":
    chain = setup_sql_agent()

    if chain:
        questions = [
            "How many documents are there in the database?",
            "List all document titles in the database",  
            "What's the average length of document titles?"
        ]

        for question in questions:
            print(f"\n🤖 Question: {question}")

            try:
                response = chain.run(question)  
                print(f"🧠 Answer: {response}")

            except Exception as e:  
                print(f"Error processing question: {e}")

DB_PASSWORD=YourActualSecurePasswordHere  
OPENAI_API_KEY=your-openai-api-key-here  

pip install --upgrade --force-reinstall pymysql sqlalchemy