无法连接到数据库：Pyodb

Question

我正在尝试从Linux上的python连接到MSSQL数据库。 每当我试图通过python连接到数据库时。我得到以下错误：

>>> import pyodbc 
>>> connection=pyodbc.connect('DRIVER={FreeTDS};SERVER=xx.xx.xxx.xxx;DATABASE=xxxxxxxxx;UID=xxxxx;PWD=xxxxxxx;')

Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
pyodbc.Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

这是freetds.log文件的输出：

log.c:196:Starting log file for FreeTDS 0.91
on 2013-05-27 15:11:50 with debug flags 0x4fff.
iconv.c:330:tds_iconv_open(0x89fda68, ISO-8859-1)
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:349:setting up conversions for client charset "ISO-8859-1"
iconv.c:351:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "ISO-8859-1" conversion
iconv.c:394:tds_iconv_open: done
login.c:436:invalid port number
mem.c:615:tds_free_all_results()
error.c:412:odbc_errs_add: "Unable to connect to data source"
error.c:585:SQLError((nil), 0x8a744b0, (nil), 0xbfd23d93, 0xbfd23978, 0xbfd23d99, 513, 0xbfd23972)
error.c:517:SQLGetDiagRec(2, 0x8a744b0, 1, 0xbfd23d93, 0xbfd23978, 0xbfd23d99, 513, 0xbfd23972)
error.c:566:SQLGetDiagRec: "[FreeTDS][SQL Server]Unable to connect to data source"
error.c:585:SQLError((nil), 0x8a744b0, (nil), 0xbfd23d93, 0xbfd23978, 0xbfd23d99, 513, 0xbfd23972)
error.c:517:SQLGetDiagRec(2, 0x8a744b0, 1, 0xbfd23d93, 0xbfd23978, 0xbfd23d99, 513, 0xbfd23972)
odbc.c:3980:SQLFreeHandle(2, 0x8a744b0)
odbc.c:4006:_SQLFreeConnect(0x8a744b0)
odbc.c:3980:SQLFreeHandle(1, 0x89fdba0)
odbc.c:4050:_SQLFreeEnv(0x89fdba0)
odbc.c:1454:SQLAllocHandle(1, (nil), 0x8a20c1c)
odbc.c:1543:_SQLAllocEnv(0x8a20c1c, 3)
locale.c:69:Attempting to read locales.conf file
odbc.c:1240:SQLSetEnvAttr(0x8a39c88, 200, 0x3, 0)
odbc.c:1279:SQLGetEnvAttr(0x8a39c88, 200, 0xbfd21778, 0, (nil))
odbc.c:1454:SQLAllocHandle(2, 0x8a39c88, 0x8a20c20)
odbc.c:1482:_SQLAllocConnect(0x8a39c88, 0x8a20c20)
odbc.c:527:SQLDriverConnect(0x8a1a200, (nil), DRIVER=   {FreeTDS};SERVER=xx.xxx.xxx.xxx;DATABASE=xxxxx;UID=xxxxxxx;PWD=xxxxxxx;, -3, 0xbfd23f6c, 2048, (nil), 0)
config.c:714:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
iconv.c:330:tds_iconv_open(0x8a58840, ISO-8859-1)
iconv.c:349:setting up conversions for client charset "ISO-8859-1"
iconv.c:351:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "ISO-8859-1" conversion
iconv.c:394:tds_iconv_open: done
login.c:436:invalid port number
mem.c:615:tds_free_all_results()
error.c:412:odbc_errs_add: "Unable to connect to data source"
error.c:585:SQLError((nil), 0x8a1a200, (nil), 0xbfd22513, 0xbfd220f0, 0xbfd22599, 513, 0xbfd220ea)
error.c:517:SQLGetDiagRec(2, 0x8a1a200, 1, 0xbfd22513, 0xbfd220f0, 0xbfd22599, 513, 0xbfd220ea)
error.c:566:SQLGetDiagRec: "[FreeTDS][SQL Server]Unable to connect to data source"
error.c:585:SQLError((nil), 0x8a1a200, (nil), 0xbfd22513, 0xbfd220f0, 0xbfd22599, 513, 0xbfd220ea)
error.c:517:SQLGetDiagRec(2, 0x8a1a200, 1, 0xbfd22513, 0xbfd220f0, 0xbfd22599, 513, 0xbfd220ea)
odbc.c:3980:SQLFreeHandle(2, 0x8a1a200)
odbc.c:4006:_SQLFreeConnect(0x8a1a200)
odbc.c:3980:SQLFreeHandle(1, 0x8a39c88)
odbc.c:4050:_SQLFreeEnv(0x8a39c88)

odbc.log文件的输出是：

[ODBC][15328][1369647481.150055][__handles.c][450]
    Exit:[SQL_SUCCESS]
        Environment = 0x9198d88
[ODBC][15328][1369647481.150109][SQLSetEnvAttr.c][182]
    Entry:            
        Environment = 0x9198d88            
        Attribute = SQL_ATTR_ODBC_VERSION            
        Value = 0x3            
        StrLen = 4
[ODBC][15328][1369647481.150139][SQLSetEnvAttr.c][349]
    Exit:[SQL_SUCCESS]
[ODBC][15328][1369647481.150167][SQLAllocHandle.c][364]
    Entry:
        Handle Type = 2
        Input Handle = 0x9198d88
[ODBC][15328][1369647481.150194][SQLAllocHandle.c][482]
    Exit:[SQL_SUCCESS]
        Output Handle = 0x91956c0
[ODBC][15328][1369647481.150229][SQLDriverConnectW.c][286]
    Entry:            
        Connection = 0x91956c0            
        Window Hdl = (nil)            
        Str In = [DRIVER={FreeTDS};SERVER=xxxxxxxx;DATABASE=xxxxxx;UID=xxxxxxxxx;PWD=xxxxxxxx;][length = 91]            
        Str Out = (nil)            
        Str Out Max = 0            
        Str Out Ptr = (nil)            
        Completion = 0
    UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

[ODBC][15328][1369647481.152483][SQLDriverConnectW.c][777]
    Exit:[SQL_ERROR]
[ODBC][15328][1369647481.152523][SQLDriverConnect.c][687]
    Entry:            
        Connection = 0x91956c0            
        Window Hdl = (nil)            
        Str In = [DRIVER={FreeTDS};SERVER=xxxxxxxxxx;DATABASE=xxxxxxxxxx;UID=xxxxxx;PWD=**********;][length = 91 (SQL_NTS)]            
        Str Out = 0xbfa8336c            
        Str Out Max = 2048            
        Str Out Ptr = (nil)            
        Completion = 0
    UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

    DIAG [08001] [FreeTDS][SQL Server]Unable to connect to data source

[ODBC][15328][1369647481.153501][SQLDriverConnect.c][1273]
    Exit:[SQL_ERROR]
[ODBC][15328][1369647481.153536][SQLGetDiagRec.c][661]
    Entry:                
        Connection = 0x91956c0                
        Rec Number = 1                
        SQLState = 0xbfa83f46                
        Native = 0xbfa83f3c                
        Message Text = 0xbfa83f4c                
        Buffer Length = 1023                
        Text Len Ptr = 0xbfa83f3a
[ODBC][15328][1369647481.153569][SQLGetDiagRec.c][698]
    Exit:[SQL_SUCCESS]                    
        SQLState = 08001                    
        Native = 0xbfa83f3c -> 0                    
        Message Text = [[unixODBC][FreeTDS][SQL Server]Unable to connect to data source]
[ODBC][15328][1369647481.153606][SQLFreeHandle.c][279]
    Entry:
        Handle Type = 2
        Input Handle = 0x91956c0
[ODBC][15328][1369647481.153630][SQLFreeHandle.c][330]
    Exit:[SQL_SUCCESS]
[ODBC][15362][1369647710.940455][__handles.c][450]
    Exit:[SQL_SUCCESS]
        Environment = 0x8a23f08
[ODBC][15362][1369647710.940519][SQLSetEnvAttr.c][182]
    Entry:            
        Environment = 0x8a23f08            
        Attribute = SQL_ATTR_ODBC_VERSION            
        Value = 0x3            
        StrLen = 4
[ODBC][15362][1369647710.941280][SQLSetEnvAttr.c][349]
    Exit:[SQL_SUCCESS]
[ODBC][15362][1369647710.941324][SQLAllocHandle.c][364]
    Entry:
        Handle Type = 2
        Input Handle = 0x8a23f08
[ODBC][15362][1369647710.941352][SQLAllocHandle.c][482]
    Exit:[SQL_SUCCESS]
        Output Handle = 0x8a206c0
[ODBC][15362][1369647710.941390][SQLDriverConnectW.c][286]
    Entry:            
        Connection = 0x8a206c0            
        Window Hdl = (nil)            
        Str In = [DRIVER={FreeTDS};SERVER=xxxxxxx;DATABASE=xxxxxxx;UID=xxxxxx;PWD=xxxxxxx;][length = 91]            
        Str Out = (nil)            
        Str Out Max = 0            
        Str Out Ptr = (nil)            
        Completion = 0
    UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

[ODBC][15362][1369647710.943626][SQLDriverConnectW.c][777]
    Exit:[SQL_ERROR]
[ODBC][15362][1369647710.943668][SQLDriverConnect.c][687]
    Entry:            
        Connection = 0x8a206c0            
        Window Hdl = (nil)            
        Str In = [DRIVER={FreeTDS};SERVER=xxxxxxxx;DATABASE=xxxxxxx;UID=xxxxxx;PWD=**********;][length = 91 (SQL_NTS)]            
        Str Out = 0xbfd23f6c            
        Str Out Max = 2048            
        Str Out Ptr = (nil)            
        Completion = 0
    UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

    DIAG [08001] [FreeTDS][SQL Server]Unable to connect to data source

[ODBC][15362][1369647710.944642][SQLDriverConnect.c][1273]
    Exit:[SQL_ERROR]
[ODBC][15362][1369647710.944675][SQLGetDiagRec.c][661]
    Entry:                
        Connection = 0x8a206c0                
        Rec Number = 1                
        SQLState = 0xbfd24b46                
        Native = 0xbfd24b3c                
        Message Text = 0xbfd24b4c                
        Buffer Length = 1023                
        Text Len Ptr = 0xbfd24b3a
[ODBC][15362][1369647710.944706][SQLGetDiagRec.c][698]
    Exit:[SQL_SUCCESS]                    
        SQLState = 08001                    
        Native = 0xbfd24b3c -> 0                    
        Message Text = [[unixODBC][FreeTDS][SQL Server]Unable to connect to data source]
[ODBC][15362][1369647710.944744][SQLFreeHandle.c][279]
    Entry:
        Handle Type = 2
        Input Handle = 0x8a206c0
[ODBC][15362][1369647710.944766][SQLFreeHandle.c][330]
    Exit:[SQL_SUCCESS]

我的odbcinst.ini文件是：

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so  
Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so      
UsageCount = 1
CPTimeout =
CPReuse =
TDS Version = 7.2
client charset = utf-8

请帮助我消除这个错误。

Answer 1

当连接到MicrosoftSQLServer2008时，我需要按照Tushar的建议在连接字符串中添加选项;PORT=1433。1433是默认端口，在没有显式端口定义的情况下，连接工作正常。