SQL Server, Python, and OS X

Question

将运行在OS X上的Python与基于云的SQL Server数据库连接起来的好方法是什么？

编辑：

使用 pyodbc 我收到此错误：

>>> import pyodbc
>>> cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=adsf.com;DATABASE=asdf;UID=asdf;PWD=asdf')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen({SQL Server}, 6): image not found (0) (SQLDriverConnect)')

Answer 1

总结

我在优胜美地版本10.10.1上使用Mac，尝试连接到MS SQL Server数据库。 我搜索了一下，找不到更新的详细答案，所以这里有一篇文章，主要来自这篇惊人的文章。 我正在将其添加到堆栈溢出上，以防链接死亡。 这个想法是，我们将有以下层来设置/连接。

层

• 第 1 部分 - pyodbc
• 第 2 部分 - freeTDS(可以使用 tsql 检查(
• 第 3 部分 - unixODBC(可以与 isql 核对(
• 第4部分 - MS SQL(可以使用常规的python程序进行检查(

步骤

1. 从这里安装Homebrew - 这是Mac OSX的包管理器。 本文展示了如何使用另一个包管理器"MacPorts"。 对于我的指示，他们使用自制软件。 基本上，自制软件有一个文件夹"cellar"，其中包含不同版本的软件包。 它不是修改您的普通文件，而是指向这些自制软件包。

2. 我们需要安装 Pyodbc，但 pyodbc 默认使用 iODBC 驱动程序(随 mac 一起安装(，但许多人在使其工作时遇到问题。因此，我们将使用一个名为 unixodbc ，我们将来会安装它。现在，我们需要配置 pyodbc 安装，使其与 unixodbc 配合使用。

转到 PyPi 并下载 pyodbc 压缩包并解压缩它。然后在setup.py中更改这些行：

elif sys.platform == 'darwin':
        # OS/X now ships with iODBC.
        settings['libraries'].append('iodbc')

自：

elif sys.platform == 'darwin':
        # OS/X now ships with iODBC.
        settings['libraries'].append('odbc')

现在运行python setup.py install.

这使得我们的 pyodbc 安装默认使用 unixodbc 驱动程序。完善！

3. 使用 brew install freetds --with-unixodbc 安装 FreeTDS(FreeTDS 是介于 Mac ODBC 和 MS SQL Server 之间的驱动程序，此处的图表显示了您应该根据您的特定 Microsoft Server 版本使用哪个版本的 TDS;例如，Microsoft SQL Server 2008 的 tds 协议 7.2(。

4. 配置freetds.conf文件(该文件应该在"/usr/local/etc/freetds.conf"中，对于Homebrew来说，这是一个链接，上面写着"/usr/local/Cellar/freetds/0.91_2/etc"，但根据版本的不同，你的文件可能会有所不同(。 我编辑了全局并将我的数据库信息添加到末尾(出于某种原因，"tds version = 7.2"会抛出错误，但仍然有效，而 8.0 只是工作(：

   [global]
   # TDS protocol version
   tds version = 8.0
   [MYSERVER]
   host = MYSERVER
   port = 1433
   tds version = 8.0
   

5. 验证 FreeTDS 是否正确安装： tsql -S myserver -U myuser -P mypassword (如果它有效，您应该会看到这样的提示(

   locale is "en_US.UTF-8"
   locale charset is "UTF-8"
   using default charset "UTF-8"
   1>
   

6. 使用 brew install unixodbc 安装 unixODBC 。

7. 设置 unixODBC 配置文件，其中包括 odbcinst.ini(驱动程序配置(和 odbc.ini(DSN 配置文件(。 默认情况下，我的文件位于：/Library/ODBC(注意：不是我的用户库又名/Users/williamliu/Library(。或者它们也可能在您的自制安装目录中 /usr/local/Cellar/unixodbc/<version>/etc .

8. 打开您的">odbcinst.ini"文件，然后添加以下内容(注意：如果您使用 MacPorts，则不同。 对于自制软件，这个文件是自制版本的链接，例如我的文件在'/usr/local/Cellar/freetds/0.91_2/lib/libtdsodbc.so'中：

   [FreeTDS]
   Description=FreeTDS Driver for Linux & MSSQL on Win32
   Driver=/usr/local/lib/libtdsodbc.so
   Setup=/usr/local/lib/libtdsodbc.so
   UsageCount=1
   

9. 打开您的">odbc.ini"，然后添加以下内容(这通常与odbcinst.ini一起：

   [MYSERVER]
   Description         = Test to SQLServer
   Driver              = FreeTDS
   Trace               = Yes
   TraceFile           = /tmp/sql.log
   Database            = MYDATABASE
   Servername          = MYSERVER
   UserName            = MYUSER
   Password            = MYPASSWORD
   Port                = 1433
   Protocol            = 8.0
   ReadOnly            = No
   RowVersioning       = No
   ShowSystemTables    = No
   ShowOidColumn       = No
   FakeOidIndex        = No
   

10. 使用以下命令验证是否正确安装了 unixODBC：isql MYSERVER MYUSER MYPASSWORD 。 如果收到无法连接的错误，请添加-v以检查详细输出是什么并修复它。 否则，您应该看到以下内容：

    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+ 
    

11. 现在验证 pyodbc 是否适用于 python 程序。 在 shell 或 .py 文件中运行 python，你应该得到你的查询：

    import pyodbc
    import pandas
    import pandas.io.sql as psql
    cnxn = pyodbc.connect('DSN=MYSERVER;UID=MYUSER;PWD=MYPASSWORD')
    cursor = cnxn.cursor()
    sql = ("SELECT * FROM dbo.MYDATABASE")
    df = psql.frame_query(sql, cnxn)
    

在此之后，您可以参考 pyodbc 的文档以获取更多帮助。

Answer 2

SQLAlchemy 可能是你最好的选择。它有一个ORM，但不需要使用它。MS SQL通过许多DBAPI项目得到支持。

至于较低级别的接口，以下是SQLAlchemy网站上列出的三个DBAPI项目，它们支持vanilla Python和Unix：

• pymssql 似乎是最简单的设置;它不需要 FreeTDS。
• pyodbc似乎比pymssql更活跃。
• mxODBC 是许多数据库的商业许可接口。

Answer 3

截至 2016 年 5 月，我已经能够简化这一点并反复让它在我的环境中工作：

安装免费TDS

brew install freetds --with-unixodbc

安装 PYODBC

从参考资料推断

pip install -U 
    --global-option=build_ext 
    --global-option="-I/usr/local/include" 
    --global-option="-L/usr/local/lib" 
    pyodbc

告诉 UnixODBC 有关 FreeTDS 驱动程序的信息

注意：您可能有不同的版本

cat <<'EOF' >> /usr/local/Cellar/unixodbc/2.3.4/etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL on Win32
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1
EOF

从那里，我不得不告诉pyodbc使用FreeTDS驱动程序：

dsn = 'DRIVER=FreeTDS;DATABASE=MyDb;SERVER=...'

这太棒了，因为如果您正在 Python 3.x 中进行异步编程，现在您可以aioodbc使用它：

async with aioodbc.connect(dsn=dsn, loop=asyncio.get_event_loop()) as conn:
    async with conn.cursor() as cur:
        await cur.execute('SELECT 42')
        r = await cur.fetchall()
        print(r)

或者：您可以完全使用pymssql，但如果您想使用 odbc 或 asyncio，这将不起作用。

Answer 4

Will的回答对我真的很有帮助。

以下是我在此过程中遇到的一些差异的注意事项，以防它们帮助他人：

1. pyodbc 压缩包已经有了所需的更改，所以我所要做的就是下载它并运行 python setup.py install .(注意：我与pip一起安装的版本仍在使用 iodbc，因此不起作用。

2. Verify FreeTDS installed步骤不允许我连接到数据库，因为我无权访问master，显然无法指定。这似乎是一个众所周知的问题。我浪费了很多时间试图解决它，但失败了，最终它并没有阻止其他步骤的工作。

3. 这些说明说 将用户名和密码放在odbc.ini .由于我们需要在登录时再次说出登录凭据，因此我尝试从odbc.ini中删除用户名和密码，希望它们不是真正必要的。(我宁愿在更少的地方写下我的密码！这工作正常。

4. 我不得不在isql MYSERVER [e-mail] MYPASSWORD(和 Python 代码(中将主机添加到用户 ID 中。

(我希望这意味着我不需要freetds.conf中的主机，但唉，这必须保留。

Answer 5

Pyodbc + MS 自己的 odbc 提供程序 msodbcsql，而不是 FreeTDS。 我的理由很简单 - 谁最有动力获得良好的SQL Server支持？ 女士。

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-python

这真的非常简单，主要的麻烦是他们的安装程序仅适用于Homebrew，而不是我通常使用的macports。 我首先尝试将 Homebrew 安装到我的主目录中，但 pyodbc 并没有以这种方式"看到"实际的驱动程序，因此标准 Homebrew 安装也是如此，然后brew install msodbcsql。 这导致了以下软件包：

(venv) jluc@sandbox$ brew list
 msodbcsql  openssl     unixodbc

对我有用的连接字符串：

Driver={ODBC Driver 13 for SQL Server};Server=192.168.1.xxx;Database=mydb;Uid=myuser;Pwd=mypassword;

对于 SQL Alchemy：

"mssql+pyodbc://%(user)s:%(password)s@%(hostname)s:%(port)s/%(dbname)s?driver=ODBC+Driver+13+for+SQL+Server"

如果您还安装了MS SQL Server(我获得了2016开发人员版(，请记住：1(使用SQL Server配置管理器(SQLServerManager13.msc(在端口1433上为您的IP启用TCPIP。 2( 在 Windows 防火墙 ( wf.msc 中打开端口 1433 。 质谱配置说明

版本：Sierra，Python 2.7，SQL Server 2016 Dev Edition，Win 10 Pro。

注意：在MS的brew安装时要小心。 我认为它曾经主动安装自制软件。不确定这在实践中是否会成为一个问题。

Answer 6

我使用的是macOS Sierra 10.12.3。 pymssql 完美地完成了这项工作。如果其他投票的答案没有任何效果，请遵循以下内容：

brew unlink freetds
brew install homebrew/versions/freetds091
pip install pymssql

下面是建立连接的示例代码段：

conn = pymssql.connect(serverhostname, username, password, dbname)
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')

Answer 7

有很多箍要跳过。威尔的回答概述了其中的很多。

经过一番挣扎，我设法让它与 Docker 一起工作(所以这应该可以在运行 docker 的任何地方工作(。

我已经用Python 3.6和Python 2.7测试了设置：pyodbc==3.0.10，django-pyodbc-Azure和Django 1.10.4(这个设置适用于Django，但也适用于vanilla python(。

我创建了一个公共映像，您可以使用： https://hub.docker.com/r/toast38coza/python-mssql/

这是一个简单的工作码头工人设置：

version: "3"
services:
  db:
    restart: on-failure:10
    image: microsoft/mssql-server-linux:latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=SuperSecret(!)100
    ports:
      - "1433:1433"
  py:
    image: toast38coza/python-mssql
    links:
      - db
    environment:
      - SA_PASSWORD=SuperSecret(!)100
      - DB_NAME=mydb

现在您可以运行：

docker-compose run --rm py python

这将在上面的py服务中运行python cli

然后尝试创建一个数据库：

>>> import pyodbc, os
>>> db_name = os.environ.get('DB_NAME')
>>> pwd = os.environ.get('SA_PASSWORD')
>>> connection_string = "driver=FreeTDS;server=db;PORT=1433 database=master;UID=sa;PWD={};TDS_Version=8.0;".format(pwd)
>>> conn = pyodbc.connect(connection_string, autocommit=True)
>>> conn.execute('create database {}'.format(db_name))
<pyodbc.Cursor object at 0x7fb3067f0e70>

这应该创建一个名为 mydb 的数据库(来自 docker-compose 文件环境变量的DB_NAME(。注意：由于我们已经创建了指向db服务(运行 MS SQL(的链接，因此可以使用主机名 db 。如果要连接到外部 MS SQL 安装程序，则显然不需要db服务(并相应地编辑连接字符串(

如果你使用的是 Django，仓库中有一个更完整的示例，但是，请注意，你需要你的settings看起来像这样：

DATABASES = {
    'default': {
        'ENGINE': "sql_server.pyodbc",
        'HOST': "db",
        'PORT':'1433',
        'USER': "sa",
        'PASSWORD': os.environ.get('SA_PASSWORD'),
        'NAME': os.environ.get('DB_NAME'),
        'OPTIONS': {
            "driver": "FreeTDS",
            "host_is_server": True,
            "unicode_results": True,
            "extra_params": "tds_version=8.0",
        }
    }
}