如何在Windows环境下实现MS SQL与AWS Lambda连接并创建Python部署包

Got it, let's walk through creating your Python deployment package for Lambda to connect to MS SQL on Windows—since you already have pip and virtualenv set up, we can jump right into the steps. A critical thing to note upfront: Lambda runs on Linux, so directly installing Windows-compatible dependencies will break things. We'll focus on getting Linux-compatible packages for your deployment.

Method 1: Virtualenv + Precompiled Linux Wheels (No Docker Needed) #

This works great if you don't want to set up Docker:

1. Set up and activate your virtual environment

   • First, create a dedicated folder for your package (e.g., lambda-mssql-package) and navigate into it via Command Prompt or PowerShell:

     mkdir lambda-mssql-package
     cd lambda-mssql-package
     

   • Create the virtual environment:

     virtualenv venv
     

   • Activate it (Windows-specific command):

     venv\Scripts\activate
     

     You'll see (venv) at the start of your command line once activated.

2. Install Linux-compatible dependencies

   • We'll use pyodbc for MS SQL connections. Uninstall any existing Windows versions first, then install a Linux-compatible build:

     pip uninstall -y pyodbc
     pip install pyodbc==4.0.39 --platform manylinux_2_17_x86_64 --only-binary=:all: --target .
     

   • Add any other dependencies your code needs (e.g., python-dotenv for environment variables):

     pip install python-dotenv --target .
     

     The --target . flag installs packages directly into your current folder (not the virtualenv's site-packages), making it easy to package everything later.

3. Add your Lambda code

   • Create a lambda_function.py file in the same folder with your connection logic. Example:

     import pyodbc
     import os
     
     def lambda_handler(event, context):
         # Pull DB credentials from Lambda environment variables
         server = os.environ['DB_SERVER']
         database = os.environ['DB_DATABASE']
         username = os.environ['DB_USERNAME']
         password = os.environ['DB_PASSWORD']
         driver = '{ODBC Driver 17 for SQL Server}'
     
         try:
             conn = pyodbc.connect(f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}')
             cursor = conn.cursor()
             cursor.execute("SELECT @@VERSION;")
             row = cursor.fetchone()
             print(f"Connected to SQL Server: {row[0]}")
             return {'statusCode': 200, 'body': f"Connection successful. SQL Server version: {row[0]}"}
         except Exception as e:
             print(f"Connection error: {str(e)}")
             return {'statusCode': 500, 'body': f"Failed to connect: {str(e)}"}
         finally:
             if conn:
                 conn.close()
     

4. Package everything into a ZIP

   • Select all files and folders in your lambda-mssql-package directory (including lambda_function.py and dependency folders like pyodbc).
   • Right-click → "Send to" → "Compressed (zipped) folder". Name it something like lambda-deployment-package.zip.

   Important: Don't zip the entire lambda-mssql-package folder—put all contents directly in the ZIP root so Lambda can find lambda_function.py.

Method 2: Docker (More Reliable for Complex Dependencies) #

If you need full compatibility with Lambda's Linux environment, use Docker to replicate it:

1. Install Docker Desktop

   • Enable Hyper-V on your Windows Server 2016 instance, then install Docker Desktop and start it.

2. Pull a Lambda-compatible Python image

   • Open PowerShell and pull the official Lambda Python image (we'll use Python 3.9, a common Lambda runtime):

     docker pull public.ecr.aws/lambda/python:3.9
     

3. Run the container and install dependencies

   • Mount your local package folder to the container and launch a bash shell:

     docker run -v ${PWD}/lambda-mssql-package:/var/task -it public.ecr.aws/lambda/python:3.9 /bin/bash
     

   • Inside the container, install your dependencies to the mounted folder:

     pip install pyodbc boto3 python-dotenv --target /var/task
     

4. Exit and package

   • Type exit to leave the container. Add your lambda_function.py to the local lambda-mssql-package folder, then zip everything as described in Method 1.

Next Steps After Packaging #

• Go to the AWS Lambda console, create a new function, and upload your ZIP package via "Upload from" → ".zip file".
• In your Lambda function's Configuration → Environment Variables, add your RDS credentials: DB_SERVER (your RDS endpoint), DB_DATABASE, DB_USERNAME, DB_PASSWORD.
• Make sure your Lambda's IAM role has permissions to access your RDS instance, and that your RDS security group allows incoming traffic from Lambda's VPC (if your RDS is in a VPC).

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