SQL Server Configuring SQLAlchemy with pyodbc on Docker - getting interface error

p3rjfoxz 于 2023-08-02 发布在 Docker

关注(0)|答案(2)|浏览(108)

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?

sql-server

来源：https://stackoverflow.com/questions/63012538/configuring-sqlalchemy-with-pyodbc-on-docker-getting-interface-error

2条答案

按热度按时间

gg0vcinb1#

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

赞(0）回复(0）举报 2023-08-02

fhity93d2#

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

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Configuring SQLAlchemy with pyodbc on Docker - getting interface error

2条答案

相关问题

热门标签

最新问答