Centos5+python+pyodbc+freetds+unixODBC+sql server

python->pyodbc->unixODBC->freetds->SQL Server

 

一、安裝freetds

1、安裝

# ./configure --prefix=/etc/freetds --with-tdsver=8.0 --enable-msdblib --enable-dbmfix --with-gnu-ld --enable-shared --enable-static
# make
# make install

2、串連資料庫

# tsql -H 192.168.0.204 -p 1433 -U sa
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
1>

註：如果出現下列錯誤

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20017 (severity 9):
        Unexpected EOF from the server
        OS error 115, "Operation now in progress"
Error 20002 (severity 9):
        Adaptive Server connection failed
There was a problem connecting to the server

可能和版本有關係請使用下列方式串連：

# TDSVER=8.0 tsql -H 10.88.51.167 -p 1433 -U sa

二、安裝unixODBC

1、安裝

# ./configure --prefix=/usr/local/unixODBC-2.3.0 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc
# make
# make install

2、配置ODBC

配置ODBC的時候，我們可以直接往兩個配置裡面增加內容，但這種方式不推薦，而推薦使用odbcinst命令來安裝驅動資訊和資料來源資訊。

首先，配置ODBC驅動

建立一個tds.driver.template的檔案，輸入以下內容並儲存：

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

添加配置

# odbcinst -i -d -f tds.driver.template

然後，配置資料來源

建立一個tds.datasource.template檔案，輸入以下內容並儲存：

[10_88_51_167]
Description     = Connection to windows virtual machine
Driver          = TDS
Trace           = No
Database        = tempdb
Server          = 10.88.51.167
Port            = 1433
TDS_Version     = 7.0

添加配置

# odbcinst -i -s -l -f tds.datasource.template

3、串連資料庫

# isql 10_88_51_167 sa password

+---------------------------------------+

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

如果出現這個提示表示已串連成功。

三、安裝pyodbc

# python setup.py build
# python setup.py install

註：如果出現下列錯誤：

libodbc.so.1: open failed: No such file or directory

 

是因為串連庫沒有找到，請添加：

# export LD_LIBRARY_PATH=/usr/local/lib

添加後再進行安裝

# python
Python 2.6 (r26:66714, Nov 10 2011, 09:56:55)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-44)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>>

四、串連資料庫

# -*- coding: utf-8 -*-
import pyodbc

db = pyodbc.connect('DRIVER={TDS};Server=10.88.51.167,1433;DATABASE=tempdb;UID=sa;PWD=sa_DnMirr12;TDS_Version=8.0')
cursor = db.cursor()
recSet = cursor.execute('select top 10 name from sys.objects').fetchall()
for recOne in recSet:
print(recOne.name)
cursor.close()
db.close()

一定要記得加上TDS_Version，否則會找不到資料來源

[root@localhost python]# python test.py
sysrscols
sysrowsets
sysallocunits
sysfiles1
syspriorities
sysfgfrag
sysphfg
sysprufiles
sysftinds
sysowners

研究了很久才搞定希望對大家有協助

五、參考資料

http://hi.baidu.com/sunny_xinxue/blog/item/f1bf6c33df54dea35edf0eb9.html

http://stackoverflow.com/questions/6973371/freetds-problem-connecting-to-sql-server-on-mac-unexpected-eof-from-the-server

http://www.ibm.com/developerworks/cn/linux/database/odbc/

http://www.unixodbc.org/doc/FreeTDS.html#GettingIt

http://www.easysoft.com/support/kb/kb01004.html