使用 pyODBC、unixODBC 和 FreeTDS 连接到 MS SQL Server 时出错(在 Mac 上) [英] Error connecting to MS SQL Server using pyODBC, unixODBC and FreeTDS (on a Mac)

问题描述

尝试使用 pyODBC --> unixODBC --> FreeTDS --> MS SQL 堆栈在 python 中连接到 MS SQL 服务器时出现错误.我在这方面花了很多时间，如果您在解决这个问题时遇到了更基本的问题，让连接正常工作，那么这里有一些很好的资源 这里 和 此处.

I am getting an error when trying to connect to a MS SQL server in python, using the pyODBC --> unixODBC --> FreeTDS --> MS SQL stack. I’ve spent a lot of time on this, and there are some great resources if you’ve reached this question with more fundamental problems getting a connection to work here and here.

然而，我的问题是关于一个错误，(我认为)非常接近这个非常令人沮丧的经历的终点线.具体来说，jupyter notebook 中的这段代码:

However, my question is regarding an error that (I think) is very close to the finish line of this very frustrating experience. Specifically, this code in jupyter notebook:

pyodbc.connect(
    'DRIVER=/usr/local/lib/libtdsodbc.so;'
    'SERVER=MyServerIP;'
    'PORT=1433;'
    'DATABASE= DatabaseName;'
    'UID=MyUsername;'
    'PWD=MyPassword')

给我这个错误:

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
<ipython-input-7-d6b29b647116> in <module>()
      1 pyodbc.connect(
----> 2     'DRIVER = /usr/local/lib/libtdsodbc.so;'
      3     'SERVER = MyServerIP;'
      4     'PORT = 1433;'
      5     'DATABASE = DatabaseName'

Error: ('HY000', '[]  (20013) (SQLDriverConnect)’)

如果我替换 'DRIVER=/usr/local/lib/libtdsodbc.so;''DRIVER=FreeTDS;'我得到:

And if I replace 'DRIVER=/usr/local/lib/libtdsodbc.so;' with 'DRIVER=FreeTDS;' I get:

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
<ipython-input-12-607f0d66e615> in <module>()
      1 pyodbc.connect(
----> 2     'DRIVER=FreeTDS;'
      3     'SERVER= MyServerIP;'
      4     'PORT=1433;'
      5     'DATABASE= DatabaseName;'

Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen(FreeTDS, 6): image not found (0) (SQLDriverConnect)')

这让我相信 unixODBC --> 由于对 iODBC 的引用，FreeTDS 连接有问题.换种说法，除非我专门提供 FreeTDS 驱动程序的路径，否则它似乎忽略了我的 odbcinst.ini 和 odbc.ini 文件，这些文件将 FreeTDS 及其位置引用为我的驱动程序(见下文)

Which leads me to believe there is something wrong with the unixODBC --> FreeTDS connection because of the reference to iODBC. Said differently, unless I specifically provide a path to the FreeTDS driver, it seems to ignore my odbcinst.ini and odbc.ini files, which reference FreeTDS and its location as my driver (see below)

当从终端运行 tsql 和 isql 时，两者都能与服务器建立良好的连接.

When running tsql and isql from the terminal, both yield good connections with the server.

但是，当我运行 osql 时，出现以下错误:

However, when I run the osql, I get the following error:

$ osql -S MyServerIP -U MyUsername -P MyPassword
checking shared odbc libraries linked to isql for default directories...
/usr/local/bin/osql: line 53: ldd: command not found
error: /Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/strings: can't open file:  (No such file or directory)
osql: problem: no potential directory strings in "/usr/local/bin/isql"
osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
isql strings are:
checking odbc.ini files
    reading /Users/myname/.odbc.ini
[MyServerIP] not found in /Users/myname/.odbc.ini
    cannot read "/odbc.ini"
osql: error: unable to locate MyServerIP in any odbc.ini

我的设置背景

我的连接是使用第一段中链接的两个资源建立(并重建和重建)的，我的完整设置如下所示:

My connection was built (and rebuilt and rebuilt) using the two resources linked in the first paragraph and my complete set up looks like this:

环境

Mac OSX 10.11.5

Mac OSX 10.11.5

Microsoft SQL Server 2012 – AWS EC2 实例(云)

Microsoft SQL Server 2012 – AWS EC2 instance (cloud)

蟒蛇 4.0

Python 3.5.1

Python 3.5.1

Jupyter 笔记本 4.1.0

Jupyter Notebook 4.1.0

连接栈

unixODBC – 使用自制软件安装

unixODBC – installed using homebrew

FreeTDS – 使用自制软件安装，命令如下:`$ brew install

FreeTDS – installed using homebrew with the command: `$ brew install

freetds --with-unixodbc`

freetds --with-unixodbc`

pyODBC 3.0.10 – 使用 conda install 安装

pyODBC 3.0.10 – installed using conda install

MS SQL – AWS EC2 实例(云)

MS SQL – AWS EC2 instance (cloud)

参考文件

我的 freetds.conf 文件是这样的:

My freetds.conf file reads like this:

[MYSERVERNAME]
    host = MyServerIP
    port = 1433
    tds version = 7.3
    client charset = UTF-8

我的 odbcinst.ini 文件是这样的:

My odbcinst.ini file reads like this:

[FreeTDS]
Description = TD Driver (MSSQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
FileUsage = 1

我的 odbc.ini 文件是这样的:

My odbc.ini file reads like this:

[MYSERVERNAME]
Driver = FreeTDS
Server = MyServerIP
Port = 1433

我完全不知所措，在这件事上花费的时间远远超过我应有的时间.如果有人有任何建议，我将永远感激.

I am at a complete loss, having spent far more hours than I should have on this. If anyone has any suggestions, I’d be forever grateful.

谢谢.

推荐答案

好吧，我们解决了它——在本页及以上许多人的帮助下这里，追了很多死胡同.

Well, we solved it -- with the help of a lot of people on this page and over here, chasing down a lot of blind alleys.

正如(最终)怀疑的那样，这是连接中的 pyodbc 链接.我使用的是 pyodbc v3.0.10，从 Anaconda 包存储库下载.解决方案是 v.3.0.9.一旦我卸载了 v3.0.10，从 pypi 存储库下载了 v3.0.9，然后构建并安装了我自己的 conda 包......它起作用了.

As (eventually) suspected, it was the pyodbc link in the connection. I was using pyodbc v3.0.10, by downloading from the Anaconda package repository. The solution was v.3.0.9. Once I uninstalled v3.0.10, downloaded v3.0.9 from the pypi repository and then built and installed my own conda package... it worked.

我采取的步骤如下(注意这些特定于 anaconda 环境):

The steps I took were as follows (note these were specific to an anaconda environment):

conda uninstall pyodbc

conda skeleton pypi pyodbc --version 3.0.9

conda build pyodbc

conda install pyodbc=3.0.9 --use-local

一旦我回到我的 Jupyter 笔记本并运行上面相同的代码，它就建立了良好的连接.

Once I went back to my Jupyter notebook and ran the same code above, it created a good connection.

我不知道 v.3.0.10 有什么问题，或者是否只是 anaconda.org 存储库中的文件.我也在 pyodbc github 页面上发布了一些东西，但它看起来并不那么活跃.

I do not know what is wrong with v.3.0.10, or if it's just the files that anaconda.org has on their repository. I've posted something on the pyodbc github page also, but it doesn't look that active.

总之，谢谢大家的帮助.我希望这可以节省一些时间.

Anyway, thank you for everyone's help. I hope this saves someone some time.

这篇关于使用 pyODBC、unixODBC 和 FreeTDS 连接到 MS SQL Server 时出错(在 Mac 上)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！