Configuring SQLAlchemy with pyodbc on Docker - getting interface error

Question

I'm changing a Python service to move to using readonly connection strings. I'm moving from PyMSSQL to PyODBC. I've seen from this question answer that readonly connection strings aren't supported in pymssql, and furthermore from this GH issue that pymssql is being discontinued anyway. That's the background.

This service uses sqlalchemy to connect to the DB. So my first step was changing the URLs it uses, and I have this now:

CONNECTION_STRING=mssql+pyodbc://user:Pass@sqlserver.com/MyDB?ApplicationIntent=ReadOnly

My code using this looks like this:

self.engine = sa.create_engine(connection_string)

I then changed my requirements file to have PyODBC rather than pymssql, then changed the dockerfile to pull in ODBC rather than freetds. I also made sure to pull in the Microsoft ODBC unix drivers too, to try and get around the problem in this question, to no avail. Here's how that bit looks in my Dockerfile

# install the Microsoft ODBC driver for SQL Server
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
  #Ubuntu 16.04 package source
  curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
  apt-get update && \
  ACCEPT_EULA=Y apt-get install -y msodbcsql17 unixodbc-dev && \
  echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile && \
  echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

When I try and use this engine, however, I hit this error:

InterfaceError: (pyodbc.InterfaceError) ('IM002', u'[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')

The first question that comes up for this issue is this one, which is about some config files I don't even have and nothing tells me to create. Also, having DB connection strings pre-configured in our images is a no-no, since we manage our config separately to our builds.

I also found another one that I can't find now that said that the way to do this is to configure FreeTDS. However, I moved away from FreeTDS since I needed the readonly intents.

Am I missing something here?

Can you build the connection URI as described in [this answer](https://stackoverflow.com/a/62985228/2144390)? — Gord Thompson, Jul 21 '20 at 12:20
Also be aware that `ApplicationIntent=ReadOnly` is merely a hint to the backend database indicating that you do not *intend* to perform any updates; it does not actually *prevent* your app from doing so. — Gord Thompson, Jul 21 '20 at 12:24
@GordThompson: This looks promising! I didn't know about the Driver argument. I'll try that one. I just need to find the name needed. As to your second comment. Thanks. I'm fine with that though, as the intent will mean they get directed at a readonly replica, so that won't be an issue. — Craig Brett, Jul 21 '20 at 12:40
For the `msodbcsql17` package the driver name is `ODBC Driver 17 for SQL Server` — Gord Thompson, Jul 21 '20 at 12:52
Ha sorry, found it just before I read your reply. If anyone else needs to find it, just "cat /etc/odbcinst.ini" and take the first category (I think that's what they're called in .ini files) from there. I'm just trying the change now. — Craig Brett, Jul 21 '20 at 12:59
@GordThompson: Bingo! Thanks! Feel free to submit adding the driver name as an answer if you're after the rep. It also lead me to reading the SQL Server bit on sqlalchemy which probably helped too: https://docs.sqlalchemy.org/en/13/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc. It could've been obvious, but I wasn't sure if it was a general ODBC error, a Docker error, etc — Craig Brett, Jul 21 '20 at 13:08

score 1 · Accepted Answer · answered Jul 21 '20 at 13:56

As mentioned in the mssql+pyodbc dialect documentation, a "hostname connection" must include the name of the ODBC driver as a driver= parameter in the connection URI. Using the sqlalchemy.engine.url.URL method to construct the connection URI would look like this:

import sqlalchemy as sa

connection_uri = sa.engine.url.URL(
    "mssql+pyodbc",
    username="user",
    password="P@ss",
    host="sqlserver.com",
    database="MyDB",
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "ApplicationIntent": "ReadOnly",
    },
)
print(connection_uri)
"""console output:
mssql+pyodbc://user:P%40ss@sqlserver.com/MyDB?ApplicationIntent=ReadOnly&driver=ODBC+Driver+17+for+SQL+Server
"""

score 0 · Answer 2 · answered Jul 13 '23 at 21:05

Keep in mind that the docker container will run under a linux environment. So, you can include in the Dockerfile the Microsoft ODBC driver for SQL Server (Linux) installation according to your OS and driver version.

This would be a Dockerfile for python:3.11.1 (Debian) and Microsoft ODBC 17:

FROM python:3.11.1

WORKDIR /code
COPY ./requirements.txt /code/requirements.txt

# Microsoft ODBC 17 installation for Debian
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 && ACCEPT_EULA=Y apt-get install -y msodbcsql17

RUN pip install --no-cache-dir --upgrade -r /code/requirements.txt

COPY ./app /code/app
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]

Configuring SQLAlchemy with pyodbc on Docker - getting interface error

2 Answers2