services:
  mysql:
    image: mysql:5.7
    container_name: mysql5.7
    ports:
      - "3306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_ALLOW_EMPTY_PASSWORD=yes
      - TZ=Asia/Shanghai
    volumes:
      - ./volumes:/var/lib/mysql
    command: --character-set-server=utf8mb4

create database testdb;

use testdb;

CREATE TABLE `finance` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(36) NOT NULL DEFAULT '' COMMENT '用戶ID',
  `date` datetime NOT NULL COMMENT '金額發(fā)生日期',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '收入支出金額(收入記為正數(shù)，支出記為負(fù)數(shù))',
  `category` varchar(32) NOT NULL DEFAULT '' COMMENT '收支類別',
  `remark` varchar(100) NOT NULL DEFAULT '' COMMENT '收支具體類目',
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日常收支';

python -m venv .venv
# 激活環(huán)境
.\.venv\Scripts\activate
# 安裝依賴包
pip install -r requirements.txt

import json
import mysql.connector
from contextlib import contextmanager
from threading import Lock


# 單例模式
class DbManagerSingleton:
    _instance = None
    _lock = Lock()  # 線程鎖，確保線程安全

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            with cls._lock:
                if not cls._instance:
                    cls._instance = super().__new__(cls)
                    cls._instance.__init__(*args, **kwargs)
        return cls._instance

    def __init__(self, host, port, user, password, database):
        self.connection_pool = mysql.connector.pooling.MySQLConnectionPool(
            pool_name="db_pool",
            pool_size=5,
            pool_reset_sessinotallow=True,
            host=host,  # 數(shù)據(jù)庫服務(wù)器Host
            port=port,  # 數(shù)據(jù)庫服務(wù)器端口
            user=user,  # 數(shù)據(jù)庫用戶名
            password=password,  # 數(shù)據(jù)庫密碼
            database=database,  # 數(shù)據(jù)庫名
        )

    @contextmanager
    def get_cursor(self):
        with self.connection_pool.get_connection() as connection:
            cursor = None
            try:
                cursor = connection.cursor()
                yield cursor
                connection.commit()
            except Exception as e:
                connection.rollback()
                raise e
            finally:
                if cursor:
                    cursor.close()

    def execute_sql(self, sql: str) -> str:
        with self.get_cursor() as cursor:
            cursor.execute(sql)
            if cursor.description is not None:
                rows = cursor.fetchall()
                result = {
                    "columns": [desc[0] for desc in cursor.description],
                    "rows": rows,
                }
                return json.dumps(result, default=str)
            else:
                return f"row affected:{cursor.rowcount}"

identity:
  author: testuser
  name: mysql
  label:
    en_US: mysql
    zh_Hans: mysql
  description:
    en_US: mysql tools
    zh_Hans: mysql tools
  icon: icon.svg
tools: # 插件包含的工具列表
  - tools/get_table_definition.yaml
  - tools/execute_sql.yaml

extra:
  python:
    source: provider/mysql.py

credentials_for_provider:
  host: # 數(shù)據(jù)庫HOST
    type: text-input # 輸入類型為普通文本
    required: true # 此憑證是必需的
    label:  # 在 Dify UI 中顯示的標(biāo)簽 (支持多語言)
        en_US: MySQL Server Host
        zh_Hans: MySQL Server主機(jī)
  port: # 數(shù)據(jù)庫端口
    type: text-input # 輸入類型為普通文本
    required: true # 此憑證是必需的
    label: # 在 Dify UI 中顯示的標(biāo)簽 (支持多語言)
        en_US: MySQL Server Port
        zh_Hans: MySQL Server端口
  user: # 數(shù)據(jù)庫用戶名
    type: text-input # 輸入類型為普通文本
    required: true # 此憑證是必需的
    label: # 在 Dify UI 中顯示的標(biāo)簽 (支持多語言)
        en_US: user name
        zh_Hans: 用戶名
  password: # 數(shù)據(jù)庫密碼
    type: secret-input # 輸入類型為密碼框
    required: true # 此憑證是必需的
    label: # 在 Dify UI 中顯示的標(biāo)簽 (支持多語言)
        en_US: password
        zh_Hans: 密碼
  database: # 數(shù)據(jù)庫名
    type: text-input # 輸入類型為普通文本
    required: true # 此憑證是必需的
    label: # 在 Dify UI 中顯示的標(biāo)簽 (支持多語言)
        en_US: database name
        zh_Hans: 數(shù)據(jù)庫名

from typing import Any

from dify_plugin import ToolProvider
from dify_plugin.errors.tool import ToolProviderCredentialValidationError


class MysqlProvider(ToolProvider):
    def _validate_credentials(self, credentials: dict[str, Any]) -> None:
        try:
            """
            IMPLEMENT YOUR VALIDATION HERE
            """
            from tools.db import DbManagerSingleton

            dbManager = DbManagerSingleton(
                host=credentials["host"],
                port=credentials["port"],
                user=credentials["user"],
                password=credentials["password"],
                database=credentials["database"],
            )
            dbManager.execute_sql("show tables")
        except Exception as e:
            raise ToolProviderCredentialValidationError(str(e))

tools: # 插件包含的工具列表
  - tools/get_table_definition.yaml
  - tools/execute_sql.yaml

identity:
  name: get_table_definition
  author: testuser
  label: # 在 Dify UI 中顯示的工具名稱 (多語言)
    en_US: get database table definition
    zh_Hans: 獲取數(shù)據(jù)庫表定義
description:
  human: # 給人類用戶看的工具描述 (多語言)
    en_US: get database table definition
    zh_Hans: 獲取數(shù)據(jù)庫表定義
  llm: get database table definition # 給 LLM 看的工具描述 (用于 Agent 模式) 
parameters: # 定義工具的輸入?yún)?shù)列表
  - name: table
    type: string
    required: true
    label: # 在 Dify UI 中顯示的參數(shù)標(biāo)簽 (多語言)
      en_US: database table name
      zh_Hans: 數(shù)據(jù)庫表名
    human_description: # 給人類用戶看的參數(shù)描述 (多語言)
      en_US: database table name
      zh_Hans: 數(shù)據(jù)庫表名
    llm_description: database table name # 給 LLM 看的參數(shù)描述 (指導(dǎo) Agent 如何填充)
    form: llm # 參數(shù)表單類型 ('llm' 或 'form')
extra:
  python:
    source: tools/get_table_definition.py

from collections.abc import Generator
from typing import Any
from dify_plugin import Tool
from dify_plugin.entities.tool import ToolInvokeMessage
from tools.db import DbManagerSingleton


class GetTableDefinitionTool(Tool):
    def __init__(self, runtime, session):
        super().__init__(runtime, session)
        self.dbManager = DbManagerSingleton(
            host=runtime.credentials["host"],
            port=runtime.credentials["port"],
            user=runtime.credentials["user"],
            password=runtime.credentials["password"],
            database=runtime.credentials["database"],
        )

    def _invoke(self, tool_parameters: dict[str, Any]) -> Generator[ToolInvokeMessage]:
        table = tool_parameters["table"]
        sql = f"show create table {table}"
        yield self.create_text_message(self.dbManager.execute_sql(sql))

identity:
  name: execute_sql
  author: testuser
  label: # 在 Dify UI 中顯示的工具名稱 (多語言)
    en_US: execute sql
    zh_Hans: 執(zhí)行sql語句
description:
  human: # 給人類用戶看的工具描述 (多語言)
    en_US: execute sql
    zh_Hans: 執(zhí)行sql語句
  llm: execute sql # 給 LLM 看的工具描述 (用于 Agent 模式) 
parameters: # 定義工具的輸入?yún)?shù)列表
  - name: sql
    type: string
    required: true
    label:  # 在 Dify UI 中顯示的參數(shù)標(biāo)簽 (多語言)
      en_US: sql
      zh_Hans: sql語句
    human_description: # 給人類用戶看的參數(shù)描述 (多語言)
      en_US: the sql to execute
      zh_Hans: 要執(zhí)行的sql語句
    llm_description: sql # 給 LLM 看的參數(shù)描述 (指導(dǎo) Agent 如何填充)
    form: llm # 參數(shù)表單類型 ('llm' 或 'form')
extra:
  python:
    source: tools/execute_sql.py

from collections.abc import Generator
from typing import Any
from dify_plugin import Tool
from dify_plugin.entities.tool import ToolInvokeMessage
from tools.db import DbManagerSingleton


class ExecuteSqlTool(Tool):
    def __init__(self, runtime, session):
        super().__init__(runtime, session)
        self.dbManager = DbManagerSingleton(
            host=runtime.credentials["host"],
            port=runtime.credentials["port"],
            user=runtime.credentials["user"],
            password=runtime.credentials["password"],
            database=runtime.credentials["database"],
        )

    def _invoke(self, tool_parameters: dict[str, Any]) -> Generator[ToolInvokeMessage]:
        sql = tool_parameters["sql"]
        yield self.create_text_message(self.dbManager.execute_sql(sql))

storage:
      enabled: false
      size: 1024

dify-plugin-windows-amd64.exe plugin package mysql

docker compose down
docker compose up -d

# 角色
你是記賬助手，可以通過調(diào)用數(shù)據(jù)庫工具完成記錄日常收入和支出并作分析。
為了完成記賬操作，需要先獲取數(shù)據(jù)庫表finance的定義。
記賬的用戶ID取值為{{#sys.user_id#}}

# 收支類別
收入：工資薪金，勞務(wù)報(bào)酬，投資收益，分紅收入，租金收入，其它收入
支出：住房，交通，通訊，保險(xiǎn)，餐飲，電子產(chǎn)品，日用品，服飾，旅行，娛樂，醫(yī)療，學(xué)習(xí)，其它支出

# 技能
## 技能1：記錄日常開支
將開支信息記錄到數(shù)據(jù)庫表finance

## 技能2：統(tǒng)計(jì)日常開支
根據(jù)用戶輸入信息分析統(tǒng)計(jì)日常開支

# 限制
僅處理記賬相關(guān)問題，不回復(fù)其它問題