无法通过Python使用高山Docker映像连接到Azure SQL [英] Cannot connect to Azure SQL using an alpine docker image with Python

问题描述

我有一个基于高山的docker映像，支持Python，通过它我试图连接到Azure SQL服务。这是我的简单连接代码。在Azure中连接到SQL Server时出现错误。

I have an alpine based docker image, with support for Python, through which I am trying to connect to Azure SQL service. Here is my simple connection code. I am getting an error when connecting to SQL server in Azure.

conn = pyodbc.connect（'DRIVER ='+ driver +'; SERVER ='+ server +'; PORT = 1433; DATABASE ='+ database +'; UID =' +用户名+'; PWD ='+密码）
pyodbc。错误：（'01000'， [01000] [unixODBC] [驱动程序管理器]无法打开lib'SQL Server的ODBC驱动程序17'：文件未打开找到（0）（SQLDriverConnect））nect））

conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")nect)")

import pyodbc
server = 'blah1.database.windows.net'
database = 'mydb1'
username = 'myadmin'
password = 'XXXXXX'
driver= 'ODBC Driver 17 for SQL Server'
conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
c = conn.cursor()
c.execute("SELECT * FROM dbo.customers")

print(c.fetchall())
print(type(c.fetchall()))

conn.commit()
conn.close()


Here is my Dockerfile: 

FROM tiangolo/uwsgi-nginx:python3.7-alpine3.8

RUN apk update
RUN apk add gcc libc-dev g++ libffi-dev libxml2 unixodbc-dev

LABEL Name=code9 Version=0.0.1
EXPOSE 8000
ENV LISTEN_PORT=8000

ENV UWSGI_INI uwsgi.ini

WORKDIR /app
ADD . /app

RUN chmod g+w /app
RUN chmod g+w /app/db.sqlite3


RUN python3 -m pip install -r requirements.txt

我假设我是unixODBC，将负责与SQL Server的连接在Azure中还是需要为Alpine安装MS SQL驱动程序？有没有一个？我找不到一个。请帮忙。

I am assuming that I am unixODBC will take care of the connection with SQL server in Azure or do I need to install MS SQL driver for alpine? Is there one available? I couldn't find one. Please help.

推荐答案

以确认设置：

apk update && apk add build-base unixodbc-dev freetds-dev
pip install pyodbc

为什么要同时安装unixodbc和freetds？ Pyodbc的pip安装需要unixodbc-dev中的软件包以及build-base中的gcc库，因此无法解决。 freetds驱动程序与pyodbc的问题往往较少，而 pymssql 则在很大程度上依赖于我，我一直在docker中使用它来代替pyodbc。不过，这是个人喜好，您可以只包含unixodbc驱动程序。
现在，找到驱动程序

Why install both unixodbc and freetds? Pyodbc's pip install requires the packages in unixodbc-dev and the gcc libraries in build-base, so no getting around that. The freetds driver tends to have fewer issues with pyodbc, and is leaned on heavily by pymssql, which I've been using in docker in lieu of pyodbc. That's a personal preference, though, you could just include the unixodbc driver. Now, to find the driver

import pyodbc
pyodbc.drivers()
# []

Pyodbc找不到它们，但它们确实已安装，因此我们可以使用shell脚本找到它们：

Pyodbc can't locate them, but they are definitely installed, so we can find them with a shell script:

find / -name *odbc.so
/usr/lib/libtdsodbc.so
/usr/lib/libodbc.so

现在，我们可以使用子进程库将其自动化以手动设置驱动程序位置：

Now, we can automate this with the subprocess library to set the driver location manually:

import subprocess

s = subprocess.Popen('find / -name *odbc.so -type f', stdout=subprocess.PIPE, shell=True).communicate()

f, _ = s

# You can change this particular loop to select whatever driver you prefer
driver = [driver for driver in f.decode().split() if 'tds' in driver][0]

driver
# '/usr/lib/libtdsodbc.so'

username = 'someuser'
server = 'someserver'
database = 'somedatabase'

conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)

或者，您可以将配置添加到 /etc/odbcinst.ini 中，如中：

Alternatively, you can add the configuration to /etc/odbcinst.ini as mentioned here:

[FreeTDS]
Description=FreeTDS Driver 
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

然后

import pyodbc

pyodbc.drivers()
['FreeTDS']

这篇关于无法通过Python使用高山Docker映像连接到Azure SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！