Azure Function SQL输出绑定报错：ConnectionString属性未初始化求助

环境信息 #

• Python版本：3.9.0
• Core Tools版本: 4.0.4736 (64-bit)
• Function Runtime版本: 4.8.1.18957
• 扩展包版本: "[4.*, 5.0.0)"

functions.json配置 #

{
  "scriptFile": "__init__.py",
  "bindings": [
    {
      "authLevel": "anonymous",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req",
      "methods": [
        "post"
      ]
    },
    {
      "type": "http",
      "direction": "out",
      "name": "$return"
    },
    {
      "name": "transactions",
      "type": "sql",
      "direction": "out",
      "commandText": "dbo.[table]",
      "connectionStringSetting": "Driver={ODBC Driver 13 for SQL Server} etcetc."
    }
  ]
}

init.py代码 #

import logging
import json

import azure.functions as func


def main(
    req: func.HttpRequest, transactions: func.Out[func.SqlRow]
) -> func.HttpResponse:
    logging.info("Python HTTP trigger function processed a request.")

    # 客户端使用Vue的JSON.Stringify({})发送POST请求
    body = json.loads(req.get_body())
    row = func.SqlRow.from_dict(body)
    transactions.set(row)

    return func.HttpResponse(
        body=json.dumps(body), status_code=201, mimetype="application/json"
    )

错误日志 #

[2022-09-12T17:49:13.731Z] Executed 'Functions.RegisterNewBatch' (Failed, Id=..., Duration=9ms)
[2022-09-12T17:49:13.732Z] System.Private.CoreLib: Exception while executing function:  
 Functions.RegisterNewBatch. Microsoft.Azure.WebJobs.Host: 
Error while handling parameter _binder after function returned:  
 Microsoft.Data.SqlClient: The ConnectionString property has not been initialized.

解决方案 #

错误根源是connectionStringSetting的配置方式错误：它应该指向配置文件中的键名，而非直接填写连接字符串内容。按以下步骤修正：

1. 在local.settings.json中添加SQL连接字符串
   在项目根目录的local.settings.json文件的Values节点下添加连接字符串配置，建议使用ODBC Driver 17（Driver 13已过时）：

   {
     "IsEncrypted": false,
     "Values": {
       "AzureWebJobsStorage": "UseDevelopmentStorage=true",
       "FUNCTIONS_WORKER_RUNTIME": "python",
       "SqlConnectionString": "Driver={ODBC Driver 17 for SQL Server};Server=tcp:你的服务器名.database.windows.net,1433;Database=你的数据库名;Uid=用户名;Pwd=密码;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
     }
   }
   

2. 修改functions.json中的connectionStringSetting
   将SQL绑定的connectionStringSetting值改为上面配置的键名SqlConnectionString：

   {
     "name": "transactions",
     "type": "sql",
     "direction": "out",
     "commandText": "dbo.[table]",
     "connectionStringSetting": "SqlConnectionString"
   }
   

3. 额外验证项

   • 确保Azure SQL服务器防火墙已允许本地IP访问（如果使用Azure SQL）。
   • 检查连接字符串中的服务器名、数据库名、用户名、密码是否完全正确。
   • 本地环境已安装对应版本的ODBC驱动：Windows可通过「ODBC数据源管理器」查看，Linux/macOS需安装对应系统的驱动包。

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