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的文件，输入以下内容并保存：
Description = FreeTDS Driver for Linux & MSSQL on Win32
Driver = /usr/local/lib/libtdsodbc.so
添加配置
# odbcinst -i -d -f tds.driver.template
然后，配置数据源
创建一个tds.datasource.template文件，输入以下内容并保存：
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 |
| 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)
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，否则会找不到数据源
# 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
页:
[1]