AWS Lambda layer to connect to SQL Server using PyODBC, UnixODBC and Microsoft ODBC 18 driver for SQL Server.
-
Builds for Python 3.11 and 3.12
-
Also builds for x86_64 and ARM64
-
Microsoft ODBC 18 for SQL Server
-
Latest UnixODBC
I'm pulling the latest version from the Amazon Linux 2023 repository so that'll tick your security boxes.
https://github.com/davidcomerford/pyodbc-mssql-lambda-layer/releases/latest
-
Download the Dockerfile
-
Build image
docker buildx build --file=Dockerfile.python.3.12 --tag pyodbc-mssql .
-
Run a temporary container to get the zip file
docker run --rm --volume $(pwd):/tmp pyodbc-mssql cp /layer.zip /tmp/
Download, unzip and run python from inside a new container.
unzip layer.zip -d layer-test
cd layer-test
docker run --rm -v $(pwd):/opt -it public.ecr.aws/sam/build-python3.12 python
When you add a layer to a function, Lambda extracts the layer contents into the /opt directory in your function’s execution environment. All Lambda runtimes include paths to specific directories within the /opt directory. This gives your function access to your layer content.
We'll simulate this behaviour locally with sys.path.append().
import sys
sys.path.append('/opt/python')
import pyodbc
With the pyodbc module loaded, lets connect to a database and run a simple query.
SERVER = 'db.microsoft.com'
DATABASE = 'sampleDB'
USERNAME = 'sa'
PASSWORD = 'M3g@hrtz'
connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD};TrustServerCertificate=yes'
conn = pyodbc.connect(connectionString)
SQL_QUERY="""
SELECT @@VERSION
GO
"""
cursor = conn.cursor()
cursor.execute(SQL_QUERY)
records = cursor.fetchall()
for r in records:
print(r)