Why does connection pooling not work with pyodbc in my Linux image?

Question

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?

Answer 1

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!

• The release notes of version 2.3.12 has some mentioning of pooling issues, but I am not sure I can directly associate it with mine.
• Relevant thread in pyodbc project.