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:[email protected]/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? — Gord Thompson
– Gord Thompson, Commented Jul 21, 2020 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
– Gord Thompson, Commented Jul 21, 2020 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
– Craig Brett, Commented Jul 21, 2020 at 12:40
For the msodbcsql17 package the driver name is ODBC Driver 17 for SQL Server — Gord Thompson
– Gord Thompson, Commented Jul 21, 2020 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
– Craig Brett, Commented Jul 21, 2020 at 12:59

Gord Thompson · Accepted Answer · 2020-07-21 13:56:43Z

1

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%[email protected]/MyDB?ApplicationIntent=ReadOnly&driver=ODBC+Driver+17+for+SQL+Server
"""

answered Jul 21, 2020 at 13:56

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J.C. Gras · Accepted Answer · 2023-07-13 21:05:38Z

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"]

Collectives™ on Stack Overflow

Configuring SQLAlchemy with pyodbc on Docker - getting interface error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related