centos下python访问sqlserver

以root权限操作

 

1.安装unixODBC 【下载地址】

sh configure --prefix=/usr/local/unixODBC

make 

make install

 

 

2.安装freetds 【下载地址】

sh configure -prefix=/usr/local/freetds --with-unixodbc=/usr/local/unixODBC /

-with-tdsver=8.0 -enable-msdblib -enable-dbmfix -with-gnu-ld -enable-shared -enable-static

make 

make install

注：指明--with-unixodbc=/usr/local/unixODBC可以生成libtdsodbc.so

3、连接数据库

# 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

3.配置unixODBC

进入/usr/local/unixODBC

创建文件tds.driver.template

[TDS]

Description = FreeTDS Driver for Linux & MSSQL on Win32

Driver      = /usr/local/freetds/lib/libtdsodbc.so

Setup       = /usr/local/freetds/lib/libtdsS.so

 

保存后执行命令

bin/odbcinst -i -d -f tds.driver.template

 

 

创建文件tds.datasource.template

[my_data]

Description = Connection to windows virtual machine

Driver      = TDS

Trace       = No

Database    = aa

Server      = 192.168.7.3

Port        = 1433

TDS_Version = 8.0

 

保存后执行命令

bin/odbcinst -i -s -l -f tds.datasource.template

 

4.测试freetds和unixODBC

进入/usr/local/freetds/目录下

执行命令

bin/tsql -H192.168.7.3 -p1433 -Usa -Padmin1234

1>use aa

2>select * from sysfiles

3>go

 

再进入

/usr/local/unixODBC

执行命令

bin/isql my_data sa admin1234

SQL>select * from sysfiles

 

 

5.安装pyODBC 【下载地址】

首先安装python2.4.4版，步骤：

./configure

make

make install

 

然后编译安装

python setup.py install

 

注：如果编译出错，请

cp /usr/local/unixODBC/include/* /usr/include/

cp /usr/local/unixODBC/lib/* /usr/lib/

然后再运行

 

 

6.python测试代码

四、连接数据库


# -*- 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，否则会找不到数据源

 

 

*7.如果其他账户下使用pyodbc

将第5步生成的文件pyodbc.so复制到zenoss的python目录下

cp /usr/local/lib/python2.4/site-packages/pyodbc.so /opt/zenoss/lib/python2.4/site-packages/

【pyodbc.so可能位于/usr/lib/python2.4/site-packages/目录下】

这样就可以zenoss用户使用pyodbc了