This is the code I am using to check if pooling is working or not:
import os
import sys
import pyodbc
SERVER = os.environ['SERVER']
DATABASE = os.environ['DATABASE']
USERNAME = os.environ['USERNAME']
PASSWORD = os.environ['PASSWORD']
CONNECTION_STR = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD}'
QUERY = "select MAX(login_time) as login_time from sys.dm_exec_sessions where login_name = ? and program_name like 'python%'"
pyodbc.pooling = sys.argv[1].lower() == 'true'
for i in range(0, 5):
with pyodbc.connect(CONNECTION_STR) as connection:
cursor = connection.cursor()
cursor.execute(QUERY, USERNAME)
row = cursor.fetchone()
print(row[0])
The idea is that login_time is set once by the server on the connection initiation and can be used as a discriminator of new and old connections.
This code works as expected on my computer (MacOS) and obeys the script argument that sets pyodbc.pooling to true/false, but my goal is to check how it works in the prod setup, so I am testing it on the image:
FROM mcr.microsoft.com/azure-functions/python:4-python3.10-core-tools
RUN curl -s https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
RUN curl -s https://packages.microsoft.com/config/debian/11/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql18
RUN pip install --upgrade pip --user
RUN pip install pyodbc
ENV PYTHONUNBUFFERED=TRUE
WORKDIR /app
ADD check_pooling.py ./
docker build -t pyodbc_cp .
docker run -e ... pyodbc_cp python check_pooling.py true
Here the situation is different: pyodbc.pooling has no effect and pooling is not working (new connection every time).
Why is the behaviour different and how to fix the problem?
After an hour of investigation I decided to check versions of components used on my machine and in the image. I found that driver manager unixodbc is of a newer version 2.3.12 on my computer, whereas the image has version 2.3.11, because it is the latest version in the Debian feed as of March 2024.
I followed the steps to install version 2.3.12 manually:
FROM mcr.microsoft.com/azure-functions/python:4-python3.10-core-tools
WORKDIR /root
# Download and install unixodbc 2.3.12
RUN curl -sO https://www.unixodbc.org/unixODBC-2.3.12.tar.gz
RUN gunzip unixODBC*.tar.gz
RUN tar xvf unixODBC*.tar
WORKDIR /root/unixODBC-2.3.12
RUN ./configure
RUN make
RUN make install
WORKDIR /root
RUN apt-get update
# Install dependencies of msodbcsql18 except unixodbc
RUN apt-get install -y libc6 libstdc++6 libkrb5-3 openssl debconf odbcinst
RUN curl -s https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
RUN curl -s https://packages.microsoft.com/config/debian/11/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
# Install msodbcsql18 ignoring unixodbc dependency
RUN apt-get download msodbcsql18
RUN ACCEPT_EULA=Y dpkg --ignore-depends=unixodbc -i msodbcsql18_18.3.2.1-1_amd64.deb
RUN pip install --upgrade pip --user
RUN pip install pyodbc
ENV PYTHONUNBUFFERED=TRUE
ADD check_pooling.py ./
And voila! The problem is fixed!