从Google Cloud Function连接Cloud SQL的MSSQL实例失败求助

1. 修复代码中的两处错误 #

你的代码存在两个会导致连接失败的问题：

• connect_tcp_socket函数未返回创建的engine，导致后续db.connect()调用报错
• 连接字符串中PORT与DATABASE之间缺少分号，格式无效

修改后的代码：

import sqlalchemy
import pyodbc

def hello_world(request):
        db = connect_tcp_socket()
        a = execute_request(db)
        return str(a)
    
def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
    db_host = 'my_private_ip'  
    db_user = 'my_db_user'
    db_pass = 'my_db_pass'
    db_name = 'my_db_name'
    db_port = 'my_db_port' 

    # 补上PORT与DATABASE之间的分号
    connection_string = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER='+db_host+';PORT='+db_port+';DATABASE='+db_name+';UID='+db_user+';PWD='+ db_pass+';Encrypt=no'
    connection_url = sqlalchemy.engine.url.URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
    engine = sqlalchemy.create_engine(connection_url)
    return engine  # 新增返回engine的语句

def execute_request(db: sqlalchemy.engine.base.Engine):
        print('ok')
        with db.connect() as conn:
            result = conn.execute(sqlalchemy.text('SELECT @@VERSION'))
            barray= []
            for row in result:
                barray.append(row)
        return barray

2. 解决ODBC驱动缺失问题 #

Google Cloud Function的Python运行环境默认未预装ODBC Driver 17 for SQL Server，需要通过构建步骤手动安装系统依赖和驱动：

步骤1：创建requirements.txt #

sqlalchemy>=2.0.0
pyodbc>=4.0.39

步骤2：创建cloudbuild.yaml构建配置 #

steps:
# 安装系统依赖和ODBC驱动
- name: 'debian:bullseye'
  entrypoint: 'bash'
  args:
  - '-c'
  - |
    apt-get update && apt-get install -y unixodbc unixodbc-dev curl gnupg2
    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
    curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list
    apt-get update
    ACCEPT_EULA=Y apt-get install -y msodbcsql17
# 部署Cloud Function
- name: 'gcr.io/cloud-builders/gcloud'
  args:
  - 'functions'
  - 'deploy'
  - 'hello_world'
  - '--runtime'
  - 'python311'
  - '--trigger-http'
  - '--vpc-connector'
  - '你的VPC连接器名称'
  - '--region'
  - '你的部署区域'
  - '--source'
  - '.'

步骤3：执行部署 #

在本地终端运行：

gcloud builds submit --config cloudbuild.yaml .

3. 更简便的替代方案：使用Cloud SQL Python Connector #

如果不想处理ODBC驱动的安装，可以使用Google官方的Cloud SQL Python Connector，它无需依赖ODBC，更适配Serverless环境：

替换后的代码 #

import sqlalchemy
from google.cloud.sql.connector import Connector, IPTypes

def hello_world(request):
    db = connect_with_connector()
    a = execute_request(db)
    return str(a)

def connect_with_connector() -> sqlalchemy.engine.base.Engine:
    # 替换为你的Cloud SQL实例连接名：项目ID:区域:实例名称
    instance_connection_name = "your-project-id:your-region:your-instance-name"
    db_user = "my_db_user"
    db_pass = "my_db_pass"
    db_name = "my_db_name"

    connector = Connector()

    def getconn():
        conn = connector.connect(
            instance_connection_name,
            "pytds",
            user=db_user,
            password=db_pass,
            db=db_name,
            ip_type=IPTypes.PRIVATE,  # 使用私有IP连接
        )
        return conn

    engine = sqlalchemy.create_engine(
        "mssql+pytds://",
        creator=getconn,
    )
    engine.dialect.description_encoding = None
    return engine

def execute_request(db: sqlalchemy.engine.base.Engine):
        print('ok')
        with db.connect() as conn:
            result = conn.execute(sqlalchemy.text('SELECT @@VERSION'))
            barray = []
            for row in result:
                barray.append(row)
        return barray

对应的requirements.txt #

sqlalchemy>=2.0.0
cloud-sql-python-connector>=1.1.0
pytds>=1.10.0

直接使用常规的Cloud Function部署命令即可，无需额外安装系统驱动。

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