sql-server - 使用 pyodbc 从 Azure Synapse Notebook 连接到 SQL Server 时出错

Question

尝试使用 pyodbc 从 Azure Synapse Spark 笔记本连接到 Azure Synapse 专用 SQL 池时，我遇到以下错误

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

下面是我正在使用的简单代码 -

import pyodbc
server = 'myServer'
database = 'myDB'
username = 'myUser'
password = 'myPassword'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password)

我认为与 Azure Functions 相比，Synapse Workspace 没有预装 SQL Server 的 ODBC 驱动程序。相同的代码从 Azure Functions 成功运行。

我已浏览此链接 - https://datathirst.net/blog/2018/10/12/executing-sql-server-stored-procedures-on-databricks-pyspark/并尝试从我的笔记本单元运行以下代码 -

%%sh
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

但它给了我以下错误 -

E: This command can only be used by root.
sh: 2: cannot create /etc/apt/sources.list.d/mssql-release.list: Permission denied
E: Could not open lock file /var/lib/apt/lists/lock - open (13: Permission denied)
E: Unable to lock directory /var/lib/apt/lists/
W: Problem unlinking the file /var/cache/apt/pkgcache.bin - RemoveCaches (13: Permission denied)
W: Problem unlinking the file /var/cache/apt/srcpkgcache.bin - RemoveCaches (13: Permission denied)
E: Could not open lock file /var/lib/dpkg/lock-frontend - open (13: Permission denied)
E: Unable to acquire the dpkg frontend lock (/var/lib/dpkg/lock-frontend), are you root?
E: Could not open lock file /var/lib/dpkg/lock-frontend - open (13: Permission denied)
E: Unable to acquire the dpkg frontend lock (/var/lib/dpkg/lock-frontend), are you root?
sudo: no tty present and no askpass program specified

有人可以指导我如何解决此问题或从 Synapse 笔记本安装 ODBC 驱动程序吗？

Answer 1

我尝试在我的环境中重现该错误并得到类似的错误。

错误原因是您用于连接的驱动程序不存在于您的 synapse 笔记本中。

要检查 synapse 笔记本中安装的驱动程序，请使用以下命令。

import pyodbc
pyodbc.drivers()

然后使用该驱动程序与 SQL 连接: