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