Docker image for pyodbc

[LuisBosquez]

trafficstars

I made this Docker image for pyodbc: https://hub.docker.com/r/lbosqmsft/mssql-python-pyodbc/

Let me know if there's any feedback!

Here's the Dockerfile and other resources: https://github.com/Microsoft/mssql-docker/tree/master/oss-drivers/pyodbc

[mkleehammer]

I won't be much help here (I don't use Docker yet), but this might be something good for the wiki.

[keitherskine]

If I understand this Docker image correctly, you have to upload your Python code to the Docker instance and run it there. Is that correct? Or can you run Python code on the host machine, where the Python code makes calls into the Docker image?

[LuisBosquez]

@mkleehammer I can definitely get together some good "getting started" content for new Docker devs!

@keitherskine That is correct but there's a few details. The environment and python libraries would be running in the container, so you can either copy the code into the container, or map a volume from the host to the container. I will include these use cases and write up some tutorials.

[alexisrolland]

It would be nice to have a Docker image running Alpine Linux, Python 3 and a bunch of ODBC drivers which would be used by Pyodbc. Have you guys seen such an image / Dockerfile?

[alexisrolland]

This gist looks like a good beginning: https://gist.github.com/stianlagstad/0978a151639f55a27672454fd46fcce9

[Relent97]

Hi @LuisBosquez thanks a lot for the docker file, however these lines below from the docker file downloads python 2.7 when instead I need python 3.8 to be installed in my docker image. "# python libraries RUN apt-get update && apt-get install -y
python-pip python-dev python-setuptools
--no-install-recommends
&& rm -rf /var/lib/apt/lists/*"

[v-chojas]

You should be able to install python3.

[Relent97]

Thanks @v-chojas I found a way to do it. Here is the code syntax below in case anyone comes across the problem.

FROM ubuntu:16.04



# apt-get and system utilities
RUN apt-get update && apt-get install -y \
    curl apt-utils apt-transport-https debconf-utils gcc build-essential g++-5\
    && rm -rf /var/lib/apt/lists/*

# adding custom MS repository
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

# install SQL Server drivers
RUN apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql unixodbc-dev

# install SQL Server tools
RUN apt-get update && ACCEPT_EULA=Y apt-get install -y mssql-tools
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN /bin/bash -c "source ~/.bashrc"

# python libraries
RUN apt-get update \
    && apt-get --yes --no-install-recommends install \
        python3 python3-dev \
        python3-pip python3-venv python3-wheel python3-setuptools \
        build-essential cmake \
        graphviz git openssh-client \
        libssl-dev libffi-dev \
    && rm -rf /var/lib/apt/lists/*




# install necessary locales
RUN apt-get update && apt-get install -y locales \
    && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen


#Install python 3.5
RUN python3.5 -m pip install pip --upgrade && \
        python3.5 -m pip install wheel

# install SQL Server Python SQL Server connector module - pyodbc
RUN pip install pyodbc

# install additional utilities
RUN apt-get update && apt-get install gettext nano vim -y

RUN mkdir /app4
WORKDIR /app4


ADD requirements.txt /app4/

RUN pip install -r requirements.txt



ADD . /app4/

ENTRYPOINT ["python3", "app4.py"]

[owlas]

@LuisBosquez this is great!

I'm trying to get this working with a multistage build so I don't have g++ etc in the final image.

Do you know what files are necessary for pyodbc? Is it the *.so files and /etc/odbcinst.ini only?

[v-chojas]

pyodbc is only the .so, yes.

odbcinst.ini is part of the driver manager - which you will need in order to use pyodbc.

[owlas]

Update: I actually needed all of these. Maybe I could thin out the /opt/microsoft directory but haven't tried. If anybody knows the minimum number of files needed for pyodbc to operate, that would be awesome

COPY --from=builder /usr/lib/x86_64-linux-gnu/libodbc* /usr/lib/x86_64-linux-gnu/
COPY --from=builder /etc/odbcinst.ini /etc/odbcinst.ini
COPY --from=builder /opt/microsoft /opt/microsoft

[manikcd]

I have used the above approach in the docker file and the build was working successfully but when the "import pyodbc" was running in the main code, its throwing "no module found error: pyodbc" , Any suggestion why could this be happening

[VorobevPavel-dev]

This comment is about multistage build. On one-staged Docker images works perfectly

Well I've been struggling a lot with this and found solution for my particular case. It might not fit in yours

TL:DR: Try pip install pyodbc inside and outside container (or on images in different stages during building docker image). Compare files generated from this command. Maybe Python inside container cannot access libraries from COPYed virtual environment via differences in names. Command find / -name *pyodbc* can be helpful.

Heres a deal: One image with Microsoft ODBC driver and Python 3.11 + pip on board is built with next Dockerfile:

FROM docker.io/python@sha256:08562255375a653cae9041adb2612e2607a352d497d1d2cd9eedcd6f32bcc627

ENV PATH="/app/venv/bin:$PATH"
WORKDIR /app
RUN apk update && \
    apk add --no-cache \
        curl \
        unixodbc-dev \
        gcc \
        g++ \
        gnupg && \
    curl -O https://download.microsoft.com/download/b/9/f/b9f3cce4-3925-46d4-9f46-da08869c6486/msodbcsql18_18.1.1.1-1_amd64.apk && \
    curl -O https://download.microsoft.com/download/b/9/f/b9f3cce4-3925-46d4-9f46-da08869c6486/msodbcsql18_18.1.1.1-1_amd64.sig && \
    curl https://packages.microsoft.com/keys/microsoft.asc  | gpg --import - && \
    gpg --verify msodbcsql18_18.1.1.1-1_amd64.sig msodbcsql18_18.1.1.1-1_amd64.apk && \
    apk add --allow-untrusted msodbcsql18_18.1.1.1-1_amd64.apk && \
    rm -f /app/msodbcsql18_18.1.1.1-1_amd64.apk && \
    rm -f /app/msodbcsql18_18.1.1.1-1_amd64.sig

I use two different images of python 3.11: python:3.11-alpine and python:3.11-slim-buster. First I call a builder, the second one is runner. Main problem is that with next Dockerfile i got error No module named pyomdbc:

# ------ Builder stage ------
FROM docker.io/python@sha256:09b30e3221996c42d14b05ee6bbc7a38e9a81d805748b43d50ce19a597ecabd3 as builder

ENV UID=65535
ENV GID=65535
ENV USERNAME=worker

RUN addgroup --gid $GID $USERNAME && \
        adduser \
            --shell /sbin/nologin \
            --disabled-password \
            --no-create-home \
            --uid $UID \
            --ingroup $USERNAME $USERNAME && \
    apt update && \
    apt install -y \
        unixodbc-dev \
        build-essential

WORKDIR /app

RUN python -m pip install virtualenv && \
    python -m virtualenv /app/venv

ENV PATH="/app/venv/bin:$PATH"

COPY requirements.txt /app/requirements.txt

RUN /app/venv/bin/python -m pip install -r requirements.txt && \
    rm -f requirements.txt

# ------ Runner stage ------
FROM <base image name here>
WORKDIR /app

COPY --from=builder /etc/shadow /etc/shadow
COPY --from=builder /etc/passwd /etc/passwd
COPY --from=builder /etc/group /etc/group

COPY --from=builder --chown=worker:worker /app/venv /app/venv

ENV PATH="/app/venv/bin:$PATH"
ENV PYTHONPATH="/app/venv/bin/lib"
ENV VIRTUAL_ENV="/app/venv"

COPY --chown=worker:worker . /app

CMD /app/venv/bin/python /app/db_insert.py

During inspection was found that pyodbc during pip installation generates different (not sure but names are different) files on different images. It's obvious but a little confusing. For example, in python:3.11.0-slim-buster, pip and setup.py will add file named pyodbc.cpython-311-x86_64-linux-gnu.so. However running pip install pyodbc on python:3.11.0-alpine3.15 file python3.11/site-packages/pyodbc.cpython-311-x86_64-linux-musl.so was added (All in <virtualenv>/lib/python3.11/site-packages directory)

So adding COPY --from=builder --chown=worker:worker /app/venv/lib/python3.11/site-packages/pyodbc.cpython-311-x86_64-linux-gnu.so /app/venv/lib/python3.11/site-packages/pyodbc.cpython-311-x86_64-linux-musl.so fixes my problem.

# ------ Runner stage ------
FROM <image with OMDB driver here>

WORKDIR /app

COPY --from=builder /etc/shadow /etc/shadow
COPY --from=builder /etc/passwd /etc/passwd
COPY --from=builder /etc/group /etc/group

# Adding basic venv
COPY --from=builder --chown=worker:worker /app/venv /app/venv
# Renaming lib inside worker image
COPY --from=builder --chown=worker:worker  /app/venv/lib/python3.11/site-packages/pyodbc.cpython-311-x86_64-linux-gnu.so /app/venv/lib/python3.11/site-packages/pyodbc.cpython-311-x86_64-linux-musl.so 

ENV PATH="/app/venv/bin:$PATH"
ENV PYTHONPATH="/app/venv/bin/lib"
ENV VIRTUAL_ENV="/app/venv"

COPY --chown=worker:worker . /app

CMD /app/venv/bin/python /app/db_insert.py

Hope it will help someone

[Sylvain303]

Because I found this issue, may it would help another. The trick is to use wheels binaries of the pyodbc package.

note: Not all version of pyodbc have wheel binaries ERROR: Could not find a version that satisfies the requirement pyodbc==4.0.32 (from versions: 4.0.34, 4.0.35, 4.0.38, 4.0.39)

4.0.39 does have binaries. See also https://roman.pt/posts/optimize-pip-install-with-wheels/ for more details on filtering requirements with pip

$ cat requirements.txt
pyodbc==4.0.39

based on debian distro, adapt with other.

Changing the mssql ODBC driver (using version ODBC 18 for example)

FROM python:3.10-slim-bullseye

ENV  DEBIAN_FRONTEND noninteractive
RUN apt-get update -y && apt-get install -y \
    locales \
    ca-certificates \
    curl \
    gnupg \
    unixodbc \
    && rm -rf /var/lib/apt/lists/*

# locales
RUN localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8
ENV LANG en_US.utf8

# mssql odbc driver
ENV ACCEPT_EULA=y
RUN 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  -y \
  && apt-get install -y msodbcsql17 \                                                                                                                                        
  && rm -rf /var/lib/apt/lists/*

WORKDIR /app

# install dependencies
COPY requirements.txt ./
RUN pip install --only-binary=:all: --no-cache-dir -r requirements.txt

worked for me