在OS X上使用pypyodbc连接MSSQL

Question

我在使用OS上的吡咯烷酮与MSSQL服务器建立基本连接时遇到了问题。

我已经通过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的错误报告。

Answer 1

我不知道你是否解决了你的问题，但我只是打了一个类似的问题，并解决了它。我得到了同样的"'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

希望这能帮上忙。

-更多的信息对我来说，我只是需要阅读权限。您可以在的安全节点中设置此设置。右键单击用户，然后转到用户映射。检查适当的数据库，确保突出显示数据库，然后在底部设置数据库角色。