使用Python读取JSON文件后SQL OPENJSON无数据返回的问题解决

问题重现 #

直接定义JSON变量执行SQL查询可正常返回数据：

DECLARE @json nvarchar(max) = N'{
    "isMoneyClient": false,
    "showPower": true,
    "removeGamePlay": true,
    "visualTweaks": {
      "0": {
        "value": true,
        "name": "Clock"
      },
      "1": {
        "value": true,
        "name": "CopperIcon"
      }
   }
}'

SELECT *
FROM OPENJSON (@json) WITH ( 
   IsMoneyClient Varchar(50) '$.isMoneyClient',
   ShowPower Varchar(50) '$.showPower',
   RemoveGamePlay Varchar(50) '$.removeGamePlay'
)

返回结果：

但使用Python读取本地JSON文件传入SQL后，执行无报错但返回空表：

DECLARE @Scripts_Python     nvarchar(max)
DECLARE @json           nvarchar(max)
SET @Scripts_Python =
CONCAT('
import json
import io

with open("','F:/SQLFiles/pythonread.json','") as data_file:
    json_data = json.load(data_file)


json_output = json.dumps(json_data)

'
)


EXECUTE sp_execute_external_script 
    @language = N'Python'
    , @script = @Scripts_Python
    , @params = N'@json_output nvarchar(max) output'
    , @json_output = @json

SELECT *
FROM OPENJSON (@json) WITH ( 
   IsMoneyClient Varchar(50) '$.isMoneyClient',
   ShowPower Varchar(50) '$.showPower',
   RemoveGamePlay Varchar(50) '$.removeGamePlay'
)

返回空表：

原因分析 #

1. 输出参数未正确标记：原SQL脚本调用sp_execute_external_script时，未给@json_output = @json添加OUTPUT关键字，导致SQL无法接收Python返回的json_output值，@json变量始终为空，最终OPENJSON返回空表。
2. 潜在权限问题：当前无报错说明文件读取正常，但若后续出现访问错误，需检查SQL Server Launchpad服务账户（通常为NT Service\MSSQLLaunchpad）是否拥有F:/SQLFiles/目录的读取权限。

解决方案 #

步骤1：修正SQL的输出参数传递 #

在sp_execute_external_script调用中添加OUTPUT关键字，确保SQL能接收Python返回的JSON字符串：

DECLARE @Scripts_Python nvarchar(max)
DECLARE @json nvarchar(max)

SET @Scripts_Python = N'
import json

# 读取JSON文件，指定编码避免乱码
with open("F:/SQLFiles/pythonread.json", encoding="utf-8") as data_file:
    json_data = json.load(data_file)

# 转换为JSON字符串，保留非ASCII字符
json_output = json.dumps(json_data, ensure_ascii=False)
'

EXECUTE sp_execute_external_script 
    @language = N'Python'
    , @script = @Scripts_Python
    , @params = N'@json_output nvarchar(max) output'
    , @json_output = @json OUTPUT; -- 添加OUTPUT关键字

SELECT *
FROM OPENJSON (@json) WITH ( 
   IsMoneyClient Varchar(50) '$.isMoneyClient',
   ShowPower Varchar(50) '$.showPower',
   RemoveGamePlay Varchar(50) '$.removeGamePlay'
)

步骤2：排查权限问题（若仍无法运行） #

如果执行时出现文件访问错误，按以下步骤处理：

• 打开Windows服务管理器，找到SQL Server Launchpad服务，查看其登录账户（默认是NT Service\MSSQLLaunchpad）。
• 右键F:/SQLFiles/目录，选择「属性」→「安全」→「编辑」，添加上述服务账户并赋予读取权限。
• 重启SQL Server Launchpad服务后重新执行脚本。

内容的提问来源于stack exchange，提问作者John Son