在OS X上使用pypyodbc连接MSSQL Server
我在用 pypyodbc 在 OS X 上连接 MSSQL 数据库时遇到了一些问题。
我已经通过 Homebrew 安装了 unixodbc 和 freetds。
brew install unixodbc
brew install freetds
然后我安装了 pypyodbc。
mkvirtualenv test
pip install pypyodbc
但是当我尝试建立连接时，连接失败，出现了：
$ python -i test.py
Traceback (most recent call last):
File "test.py", line 20, in <module>
c = p.connect(dsn)
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 2434, in __init__
self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 2483, in connect
check_success(self, ret)
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 988, in check_success
ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 966, in ctrl_err
raise DatabaseError(state,err_text)
pypyodbc.DatabaseError: (u'01000', u"[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found")
>>>
我的基本 test.py 文件看起来是这样的：
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pypyodbc as p
settings = {
"driver": "FreeTDS",
"hostname": "mssql.local",
"username": "testuser",
"password": "testpass",
"database": "testdb",
"port": 1433
}
dsn = "DRIVER={{{driver:s}}};SERVER={hostname:s};PORT={port:d};DATABASE={database:s};UID={username:s};PWD={password:s};CHARSET=UTF8;TDS_Version=8.0".format(**settings) # noqa
c = p.connect(dsn)
xs = c.execute("SELECT name FROM master..sysdatabases").fetchall()
有几个类似的问题，但似乎没有解决我遇到的问题，这个问题似乎和驱动有关。
$ python -i test.py
Username: IRMA_RO
Password:
Traceback (most recent call last):
File "test.py", line 15, in <module>
c = p.connect("DSN=na-dev;UID={0:s};PWD={1:s}".format(username, password))
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 2434, in __init__
self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 2483, in connect
check_success(self, ret)
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 988, in check_success
ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 975, in ctrl_err
err_list.append((from_buffer_u(state), from_buffer_u(Message), NativeError.value))
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 482, in UCS_dec
uchar = buffer.raw[i:i + ucs_length].decode(odbc_decoding)
File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/encodings/utf_32.py", line 11, in decode
return codecs.utf_32_decode(input, errors, True)
UnicodeDecodeError: 'utf32' codec can't decode bytes in position 0-1: truncated data
>>>
这里似乎有一个相关的错误报告： https://code.google.com/p/pypyodbc/issues/detail?id=31
1 个回答
3
我不确定你是否解决了你的问题，但我刚刚遇到了类似的情况并且解决了它。我之前也遇到了同样的错误：“'utf32' 编码无法解码位置 0-1 的字节：数据被截断”。
你需要仔细检查的是，在 odbc.ini 文件中，ServerName 这一部分的名字要和 freetds.conf 文件中的名字一致。
比如说：
odbc.ini
[old_flood]
Driver = FreeTDS
Description = RTS Old Flood
ServerName = old_flood <----- this must be the same as the name in freetds.conf
freetds.conf
[old_flood] <----- This is the same as the ServerName above
host = <server ip address>
port = <server port>
tds version = 8.0
希望这能帮到你。
--------更多信息--------
你还需要确保你创建的数据库用户有适当的权限来访问你想要读写的数据库。对我来说，我只需要读取权限。你可以在 SQL Server Management Studio 的安全性节点中设置这个。右键点击用户，然后选择用户映射。勾选合适的数据库，确保选中数据库，然后在底部设置数据库角色。