macos SQL Server、Python 和 OS X

Question

提问by Ben Hamner

What's a good way to interface Python running on OS X with a cloud-based SQL Server database?

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

EDIT:

编辑：

With pyodbc I'm getting this error:

使用 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

采纳答案by Colin Dunklau

SQLAlchemyis probably your best bet. It has an ORM, but doesn't require its use. MS SQL is supported through a number of DBAPI projects.

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

As for lower-level interfaces, here's the three DBAPI projects listed at SQLAlchemy's site that have vanilla Python and Unix support:

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

pymssqlappears to be the simplest to set up; it doesn't require FreeTDS.
pyodbcappears to be under more active development than pymssql.
mxODBCis a commercially-licensed interface to many databases.

pymssql似乎是最容易设置的；它不需要 FreeTDS。
pyodbc似乎比 pymssql 处于更活跃的开发阶段。
mxODBC是许多数据库的商业许可接口。

Answer 2

回答by Will

Summary

概括

I'm using a Mac on Yosemite Version 10.10.1 trying to connect to a MS SQL Server database. I searched and couldn't find an updated detailed answer so here's a writeup that is mostly from this amazing article here. I'm adding it on stackoverflow in case the link dies. The idea is that we'll have the following layers to setup/connect.

我在 Yosemite 版本 10.10.1 上使用 Mac 尝试连接到 MS SQL Server 数据库。我搜索，但没有找到更新的详细的解答这里有一个书面记录是大多是从这个惊人的文章在这里。我将它添加到 stackoverflow 以防链接失效。这个想法是我们将有以下层来设置/连接。

Layers

图层

PART 1 - pyodbc
PART 2 - freeTDS (can check with tsql)
PART 3 - unixODBC (can check with isql)
PART 4 - MS SQL (can check with a regular python program)

第 1 部分 - pyodbc
第 2 部分 - freeTDS（可以用 tsql 检查）
第 3 部分 - unixODBC（可以使用 isql 检查）
第 4 部分 - MS SQL（可以使用常规的 Python 程序进行检查）

Steps

脚步

Install Homebrewfrom here- this is a package manager for Mac OSX. The article shows how to use another package manager 'MacPorts'. For my instructions, they're with homebrew. Basically homebrew has a folder 'cellar' that holds different versions of packages. Instead of modifying your normal files, it instead points to these homebrew packages.
We need to install Pyodbc, but pyodbc uses iODBC drivers by default (which comes installed with mac), but many people have issues making it work. So, we're going to use an alternative called unixodbc, which we would install in the future. For now, we need to configure the pyodbc installation such that it works with unixodbc.

从这里安装Homebrew- 这是 Mac OSX 的包管理器。这篇文章展示了如何使用另一个包管理器“MacPorts”。根据我的指示，他们使用自制软件。基本上自制软件有一个文件夹“地窖”，其中包含不同版本的软件包。它没有修改您的普通文件，而是指向这些自制程序包。
我们需要安装 Pyodbc，但 pyodbc 默认使用 iODBC 驱动程序（mac 附带安装），但是很多人在使其工作时遇到问题。所以，我们将使用一个名为的替代方案unixodbc，我们将在未来安装它。现在，我们需要配置 pyodbc 安装，使其与 unixodbc 一起工作。

Go to PyPi and download pyodbc tarball and uncompress it. Then change these lines in setup.py:

转到 PyPi 并下载 pyodbc tarball 并解压缩它。然后将这些行更改为setup.py：

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

to:

到：

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

and now run python setup.py install.

现在运行python setup.py install。

This makes our pyodbc installation use unixodbc drivers by default. Perfect!

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

Install FreeTDSwith brew install freetds --with-unixodbc(FreeTDS is the driver that sits between the Mac ODBC and MS SQL Server, thischart here shows which version of TDS you should be using based on your specific Microsoft Server version; e.g. tds protocol 7.2 for Microsoft SQL Server 2008).
Configure freetds.conffile (The file should be in '/usr/local/etc/freetds.conf', which for Homebrew is a link to say '/usr/local/Cellar/freetds/0.91_2/etc', but yours might be somewhere different depending on version). I edited the global and added my database info to the end (for some reason 'tds version = 7.2' would throw an error, but still work, while 8.0 just works):
```
[global]
# TDS protocol version
tds version = 8.0

[MYSERVER]
host = MYSERVER
port = 1433
tds version = 8.0
```
Verify FreeTDS installedcorrectly with: tsql -S myserver -U myuser -P mypassword(you should see a prompt like this if it worked)
```
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
```
Install unixODBCwith brew install unixodbc.
Setup your unixODBC config files, which includes odbcinst.ini(driver configuration), and odbc.ini(DSN configuration file). By default, my files were in: /Library/ODBC(Note: NOT my user library aka /Users/williamliu/Library). Or they could also be in your homebrew installation directory /usr/local/Cellar/unixodbc/<version>/etc.
Open up your 'odbcinst.ini' file and then add the following (Note: Different if you use MacPorts. For Homebrew, this file is a link to the homebrew version e.g. mine is in '/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
```

Open up your 'odbc.ini' and then add the following (this is usually along with 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

Verify unixODBCinstalled correctly with: isql MYSERVER MYUSER MYPASSWORD. If you get an error that you cannot connect, then add -vto check what the verbose output is and fix it. Otherwise, you should see this:

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

Now verify pyodbc workswith a python program. Run python in the shell or a .py file with this and you should get your query back:

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)

安装freetds的有brew install freetds --with-unixodbc（freetds的是，在Mac ODBC和MS SQL Server之间的坐在驾驶员，这个;针对Microsoft SQL Server 2008如TDS协议7.2此图你应该使用根据您的具体Microsoft服务器版本，其中TDS的版本所示）。
配置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
```
使用以下方法验证 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>
```
安装了unixODBC用brew install unixodbc。
设置您的unixODBC 配置文件，其中包括odbcinst.ini（驱动程序配置）和odbc.ini（DSN 配置文件）。默认情况下，我的文件位于：（/Library/ODBC注意：不是我的用户库又名 /Users/williamliu/Library）。或者它们也可以在您的自制软件安装目录中/usr/local/Cellar/unixodbc/<version>/etc。
打开您的“ odbcinst.ini”文件，然后添加以下内容（注意：如果您使用 MacPorts，则不同。对于 Homebrew，此文件是指向 homebrew 版本的链接，例如我的位于 '/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
```

打开您的“ 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

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

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

现在验证 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)

You can refer to the documentationof pyodbc to get more help after this.

之后您可以参考pyodbc的文档以获得更多帮助。

Answer 3

回答by nerdwaller

I've been able to simplify this and repeatedly have it work in my environments as of May 2016:

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

Install FreeTDS

安装 FreeTDS

brew install freetds --with-unixodbc

Install PYODBC

安装 PYODBC

Extrapolated from Reference

从参考推断

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

Tell UnixODBC about the FreeTDS Driver

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

Note: You may have a different version

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

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

From there, I had to tell pyodbc to use the FreeTDSDriver:

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

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

This is fantastic as now you can use it with aioodbcif you are doing async programming in Python 3.x:

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

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)

Alternatively: You can use pymssqlflat out, but that won't work if you want to use odbc or asyncio.

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

Answer 4

回答by JL Peyret

Pyodbc + MS's own odbc provider, msodbcsql, rather than FreeTDS. My reasoning was simple - who is most motivated to have good SQL Server support? MS.

Pyodbc + MS 自己的 odbc 提供程序，msodbcsql，而不是FreeTDS。我的理由很简单——谁最有动力获得良好的 SQL Server 支持？多发性硬化症。

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

It really was pretty simple, the main hassle is that their installer only works with Homebrew, rather than macportswhich is what I usually use. I first tried to install Homebrew to my home directory, but the actual drivers aren't "seen" by pyodbc that way, so did a standard Homebrew install, then brew install msodbcsql. This resulted in the following packages:

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

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

Connect string that worked for me:

连接对我有用的字符串：

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

And, for SQL Alchemy:

并且，对于 SQL Alchemy：

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

If you're also installing MS SQL Server (I got the 2016 Developer Edition), remember to : 1) use SQL Server Configuration Manager(SQLServerManager13.msc) to enable TCPIP on port 1433, for your IP. 2) open up port 1433 in Windows Firewall(wf.msc). MS config instructions

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

Versions: Sierra, Python 2.7, SQL Server 2016 Dev edition, Win 10 Pro.

版本：Sierra、Python 2.7、SQL Server 2016 开发版、Win 10 Pro。

Note: be careful around MS's brew install. I think it used to take the initiative to install Homebrew. Not sure if it would've been an issue in practice.

注意：在 MS 的 brew install 周围要小心。我想以前是主动安装Homebrew的。不确定在实践中是否会成为问题。

Answer 5

回答by DavidC

Will's answerwas really helpful to me.

威尔的回答对我很有帮助。

Here are some notes on a couple differences I experienced along the way, in case they help others:

以下是我在此过程中遇到的一些差异的一些说明，以防它们对其他人有所帮助：

The pyodbc tarballalready had the change required, so all I had to do was download it and run python setup.py install. (Note: The version I had installed with pipwas still using iodbc, so that didn't work.
The Verify FreeTDS installedstep didn't allow me to connect to the database because I don't have access to the master, and there is apparently no way to specify. This seems to be a well known issue. I wasted a bunch of time trying to solve it, but failed and in the end it didn't block other steps from working.
These instructions say to put the username and password in odbc.ini. Since we need to say the login credentials again when we log in, I tried removing the UserName and Password from odbc.ini, hoping they weren't really necessary. (I'd rather have my password written down in fewer places!) This worked fine.
I had to add the host to the userid in isql MYSERVER [email protected] MYPASSWORD(and in the Python code).

该pyodbc压缩包已经有了所需要的变化，因此，所有我需要做的就是下载并运行程序python setup.py install。（注意：我安装的版本pip仍在使用 iodbc，因此不起作用。
该Verify FreeTDS installed步骤不允许我连接到数据库，因为我无权访问master，并且显然无法指定。这似乎是一个众所周知的问题。我浪费了很多时间试图解决它，但失败了，最终它并没有阻止其他步骤的工作。
这些说明说将用户名和密码放在odbc.ini. 由于我们需要在登录时再次说出登录凭据，因此我尝试从中删除用户名和密码odbc.ini，希望它们不是真正必要的。（我宁愿在更少的地方写下我的密码！）这很好用。
我必须将主机添加到用户 ID 中isql MYSERVER [email protected] MYPASSWORD（和 Python 代码中）。

(I hoped that this would mean I don't need the host in freetds.conf, but alas, that has to stay.)

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

Answer 6

回答by intrepidkarthi

I am on macOS Sierra 10.12.3. pymssql did the job perfectly. If nothing works from other upvoted answers, follow this:

我在 macOS Sierra 10.12.3 上。pymssql 完美地完成了这项工作。如果其他支持的答案无效，请按照以下步骤操作：

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

and here is a sample snippet to establish connection:

这是建立连接的示例片段：

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

Answer 7

回答by toast38coza

There are a lot of hoops to jump through. Will's answer outlines a good number of them.

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

After much struggle, I managed to get this working with Docker (so this should work anywhere that runs docker).

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

I have tested the setup with Python 3.6 and Python 2.7: with pyodbc==3.0.10, django-pyodbc-azure and Django 1.10.4 (this setup is for Django, but works for vanilla python as well).

我已经使用 Python 3.6 和 Python 2.7 测试了设置：使用 pyodbc==3.0.10、django-pyodbc-azure 和 Django 1.10.4（此设置适用于 Django，但也适用于 vanilla python）。

I've created a public image which you can use: https://hub.docker.com/r/toast38coza/python-mssql/

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

Here is a simple working docker setup:

这是一个简单的工作 docker 设置：

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

Now you can run:

现在你可以运行：

docker-compose run --rm py python

Which will run the python cli inside the pyservice above

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

Then try create a database:

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

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

That should create a database called mydb(the DB_NAMEfrom the docker-compose file environment variable). Note: because we've created the link to the dbservice (running MS SQL), we can use the hostname db. If you are connecting to an external MS SQL setup, you obviously don't need the dbservice (and edit your connection string accordingly)

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

If you're using Django, there is a more complete example in the repo, but, just a heads up, you will need your settingsto look something like this:

如果您使用的是 Django，则repo 中有一个更完整的示例，但是，请注意，您需要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",
        }
    }
}

macos SQL Server、Python 和 OS X

提问by Ben Hamner

采纳答案by Colin Dunklau

回答by Will

回答by nerdwaller

Install FreeTDS

安装 FreeTDS

Install PYODBC

安装 PYODBC

Tell UnixODBC about the FreeTDS Driver

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

回答by JL Peyret

回答by DavidC

回答by intrepidkarthi

回答by toast38coza

相关推荐

最近更新

标签

macos SQL Server、Python 和 OS X

提问by Ben Hamner

采纳答案by Colin Dunklau

回答by Will

回答by nerdwaller

Install FreeTDS

安装 FreeTDS

Install PYODBC

安装 PYODBC

Tell UnixODBC about the FreeTDS Driver

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

回答by JL Peyret

回答by DavidC

回答by intrepidkarthi

回答by toast38coza

相关推荐

macos 在 OS X 上的 Java swing 中设置默认应用程序图标图像

macos OS X 上的 GLUT 和 OpenGL 3.2 核心配置文件

macos 用于确定规格的终端命令

macos 如何解压和打包pkg文件？

相关推荐

最近更新

标签