Dify使用代码执行节点，用python3对接SQL Server

需要先到Dify的 dify-sandbox:<版本号>容器内部安装pyodbc模组。docker exec直接进入容器安装 可能是一次性的，容器重启重构之后 就没了。所以需要将pyodbc环境写在docker-compose.yaml文件里，这样就能永久存在。

1、创建一个 Dockerfile（放在 sandbox 目录）

进入dify的docker文件夹下，创建一个dockerfile文件，比如sandbox.Dockerfile

内容如下，顺便装下常用的网络工具(别问为什么装=.= ，装就完事了)

FROM langgenius/dify-sandbox:0.2.11

# 安装 pyodbc 依赖、常用网络工具、pip 国内加速源、vi 编辑器
RUN pip config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple && \
    apt-get update && \
    apt-get install -y --no-install-recommends \
        gcc \
        g++ \
        unixodbc-dev \
        iproute2 \
        iputils-ping \
        net-tools \
        dnsutils \
        curl \
        vim-tiny && \
    pip install pyodbc && \
    apt-get clean && rm -rf /var/lib/apt/lists/*

2、修改 docker-compose.yaml 中的 sandbox 配置

使用/  # The DifySandbox 搜索找到sandbox配置

将 image: 改为 build:，指定新写的 Dockerfile

  sandbox:
    build:
      context: .
      dockerfile: sandbox.Dockerfile
    restart: always
    environment:
      API_KEY: ${SANDBOX_API_KEY:-dify-sandbox}
      GIN_MODE: ${SANDBOX_GIN_MODE:-release}
      WORKER_TIMEOUT: ${SANDBOX_WORKER_TIMEOUT:-15}
      ENABLE_NETWORK: ${SANDBOX_ENABLE_NETWORK:-true}
      HTTP_PROXY: ${SANDBOX_HTTP_PROXY:-http://ssrf_proxy:3128}
      HTTPS_PROXY: ${SANDBOX_HTTPS_PROXY:-http://ssrf_proxy:3128}
      SANDBOX_PORT: ${SANDBOX_PORT:-8194}
      PIP_MIRROR_URL: ${PIP_MIRROR_URL:-}
    volumes:
      - ./volumes/sandbox/dependencies:/dependencies
      - ./volumes/sandbox/conf:/conf
    healthcheck:
      test: [ 'CMD', 'curl', '-f', 'http://localhost:8194/health' ]
    networks:
      - ssrf_proxy_network

3、重新构建并启动容器

docker-compose build sandbox
docker-compose up -d sandbox

4、修改docker-compose.yaml中internal: true配置，true改成false。

internal: true 的意思是不允洗容器访问外部网络，会导致sandbox无法访问我们外部的数据库

保存后重启dify

docker compose down
docker compose up -d

进入容器内测试，到外部网络已经通了

MaxKB对接SQLServer

使用pymssql模块，

import pymssql

def get_data_as_string():
    # 数据库连接信息
    server = '10.0.0.120'
    port = 1433
    database = 'KPDB'
    username = 'kpmis'
    password = 'KPM6admin_'

    try:
        # 建立数据库连接
        conn = pymssql.connect(server=server, port=port, user=username, password=password, database=database)
        cursor = conn.cursor()

        # 执行 SQL 查询
        query = '''WITH
  CDP_V_CustomDetailReport AS (
    SELECT
      MAX(AA.DR_XID) AS DR_XID,
      AA.PRODUCT_ID AS PRODUCT_ID,
      DY.PCode AS HPBH1,
      DY.PModule AS GGXH1,
      DY.PEnabled AS QYZT1,
      AA.BASIC_UNIT_ID AS BASIC_UNIT_ID,
      SUM(AA.IN_PRODUCT_NUM) AS IN_PRODUCT_NUM,
      SUM(AA.PRODUCT_AMOUNT) AS PRODUCT_AMOUNT,
      SUM(AA.AUXILIARY_QUANTITY) AS AUXILIARY_QUANTITY,
      SUM(AA.IN_PRODUCT_SUM) AS IN_PRODUCT_SUM,
      SUM(AA.OUT_PRODUCT_NUM) AS OUT_PRODUCT_NUM,
      SUM(AA.OUT_PRODUCT_SUM) AS OUT_PRODUCT_SUM,
      SUM(AA.BALANCE_QUANTITY) AS BALANCE_QUANTITY,
      SUM(AA.BALANCE_AMOUNT) AS BALANCE_AMOUNT,
      SUM(AA.AMOUNT) AS AMOUNT,
      AA.OUT_STORE_HOUSE_ID AS OUT_STORE_HOUSE_ID,
      AA.OUT_CARGO_SPACE_ID AS OUT_CARGO_SPACE_ID,
      AA.BATCH_ID AS BATCH_ID
    FROM
      ERP_V_CustomDetailReport210002887977000025 AA
      LEFT JOIN Common_Product DY ON DY.PId = AA.PRODUCT_ID
      LEFT JOIN Common_ProductGroup EA ON EA.GROUP_ID = DY.Group_ID
      LEFT JOIN Common_ProductBrand EB ON EB.BRAND_ID = DY.BRAND_ID
    WHERE
      1 = 1
    GROUP BY
      AA.PRODUCT_ID,
      DY.PCode,
      DY.PModule,
      DY.PEnabled,
      AA.BASIC_UNIT_ID,
      AA.OUT_STORE_HOUSE_ID,
      AA.OUT_CARGO_SPACE_ID,
      AA.BATCH_ID
  )
SELECT
  GGXH1,
  BALANCE_QUANTITY,
  CASE
    WHEN (ISNULL(AA.BALANCE_QUANTITY, 0)) = 0 THEN 0
    ELSE CAST(ISNULL(AA.AMOUNT, 0) AS decimal(25,8)) / ISNULL(AA.BALANCE_QUANTITY, 0)
  END AS AVERAGE_PRICE,
  GD.PName AS pname,
  GH.BASE_NAME AS unitname
FROM
  CDP_V_CustomDetailReport AA
  LEFT JOIN Common_Product GD ON GD.PId = AA.PRODUCT_ID
  LEFT JOIN COMMON_UNIT GH ON GH.XID = AA.BASIC_UNIT_ID
  LEFT JOIN Common_StoreHouse GJ ON GJ.SHId = AA.OUT_STORE_HOUSE_ID
  LEFT JOIN CSM_RepairCargoSpace GM ON GM.CARGOSPACE_XID = AA.OUT_CARGO_SPACE_ID
  LEFT JOIN ERP_BatchCode GP ON GP.BATCH_ID = AA.BATCH_ID
ORDER BY
  AA.DR_XID ASC'''

        cursor.execute(query)

        # 获取查询结果并转换为字符串
        result_string = ''
        for row in cursor.fetchall():
            row_string = ' '.join(str(col)+'|' for col in row)
            result_string += row_string + '\n'

        return result_string

    except Exception as e:
        print(f"Error: {e}")
        return None
    finally:
        cursor.close()
        conn.close()

查询结果