在databricks 10.4 LTS运行时使用pyodbc连接sql server时出现错误 &

Question

Please Help…

我得到以下错误，而试图连接到sql server与databricks运行时10.4 LTS，而连接是成功的databricks运行时7.4 LTS

错误:

OperationalError: ('08001', '[08001] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)') 

我在初始化脚本

中使用以下安装步骤

dbutils.fs.put("/databricks/scripts/driversqlodbc.sh", """
#!/bin/bash
sleep 10
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
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 msodbcsql17
apt-get -y install unixodbc-dev
sudo apt-get install python3-pip -y
pip3 install --upgrade pyodbc """, True)

连接字符串是这样的

odbc_driver= '{ODBC Driver 17 for SQL Server}'
conn = pyodbc.connect('DRIVER='+odbc_driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+db_user+';PWD='+ password,autocommit=True)

** EDIT **

以下是cat /etc/os-release

的输出

NAME="Ubuntu"
VERSION="20.04.4 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.4 LTS"
VERSION_ID="20.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=focal
UBUNTU_CODENAME=focal

现在我的脚本看起来像这样

#!/bin/bash
sleep 10
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
sudo apt-get install -y unixodbc-dev
sudo apt-get install python3-pip -y
pip3 install --upgrade pyodbc

和连接字符串

odbc_driver= '{ODBC Driver 18 for SQL Server}'
conn = pyodbc.connect('DRIVER='+odbc_driver+';SERVER=tcp:'+server+',1433;DATABASE='+database+';UID='+db_user+';PWD='+ password,autocommit=True)

Answer 1

可能是pyodbc版本的问题。我们遇到了同样的问题。最新pyodbc更新到pyodbc==4.0.34(5天前)会导致一些问题，例如参见https://github.com/mkleehammer/pyodbc/issues/1079。

所以我们修改了setup.py中的下面一行:

"pyodbc~=4.0.32",

"pyodbc==4.0.32",

注意双==符号。它可以在4.0.32上运行。