python访问sql server安装、配置、代码示例

qrqwe

                      freeTDS是能够用Linux和Unix连接MS SQLServer和Sybase数据库，TDS的意思是"表列数据流"

安装gcc组件:
yum install -y gcc
否则configure的时候报错：
configure: error: no acceptable C compiler found in $PATH


Linux下安装freetds-dev:
download source:
http://mirrors.ibiblio.org/freetds/stable/
freetds-stable.tgz
http://www.freetds.org/

在任意目录解压后：
./configure --prefix=/usr/local/freetds --with-tdsver=8.0
make
sudo make install

配置文件：/usr/local/freetds/etc/freetds.conf
添加：
[mssql2005]
        host = x.x.x.x
        port = 1433
        tds version = 8.0
        client charset = UTF-8


测试：
# /usr/local/freetds/bin/tsql -S mssql2005 -H x.x.x.x -p 1433 -U MyUser -P MyPassword
1> select * from MyDB..MyTable
2> go
(正常可以得到查询结果)


下载python setuptools-git
https://pypi.python.org/pypi/setuptools-git/
解压到任意目录，进入目录运行:
python setup.py install

下载安装pymssql:
https://pypi.python.org/pypi/pymssql/2.1.1#downloads
pymssql-2.1.1.tar.gz
解压到任意目录，进入目录运行:
python setup.py install

增加lib路径:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/freetds/lib/

即可在python中import pymssql

增加bin路径:
vi /root/.bashrc   
添加内容如下:   
export FREETDS=/usr/local/freetds  
export $PATH="$PATH:$FREETDS/bin"
使其立即生效:
source /root/.bashrc   

(end)

代码示例:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102

#coding=utf-8 #!/usr/bin/env python import pymssql import ConfigParser class MSSQL:     def __init__(self):         cf = ConfigParser.ConfigParser()         cf.read("mssql.conf")         self.host = cf.get("DB","host")         self.user = cf.get("DB","user")         self.pwd = cf.get("DB","pwd")         self.db = cf.get("DB","db")                  def __GetConnect(self):         """         get connetion info         response: conn.cursor()         """         #if not self.db:         #    raise(NameError,"no db conf file found")                self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,timeout=5,login_timeout=2,charset="utf8")         cur = self.conn.cursor()         if not cur:             raise(NameError,"fail connecting to DB")         else:             return cur     ##verify DB connection     def VerifyConnection(self):         try:             if self.host=='':                 return False             conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,timeout=1,login_timeout=1,charset="utf8")             return True         except:             return False     def ExecQuery(self,sql):         """         execute query         get a list including tuple, elements of list are row of record, elements of tuple is fields         demo                 ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")                 resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")                 for (id,NickName) in resList:                     print str(id),NickName         """         cur = self.__GetConnect()         cur.execute(sql)         resList = cur.fetchall()         #resList = cur.description         #close connection after querying         self.conn.close()         return resList     def ExecNonQuery(self,sql):         """         execute no query         demo             cur = self.__GetConnect()             cur.execute(sql)             self.conn.commit()             self.conn.close()         """         cur = self.__GetConnect()         cur.execute(sql)         self.conn.commit()         self.conn.close()     def ExecStoreProduce(self,sql):         """         execute query         get a list including tuple, elements of list are row of record, elements of tuple is fields         demo:                 ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")                 resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")                 for (id,NickName) in resList:                     print str(id),NickName         """         cur = self.__GetConnect()         cur.execute(sql)         resList = cur.fetchall()         self.conn.commit()         #close connection after querying         self.conn.close()         return resList       def main():     sqlquery="select * from MyDB..MyTable"     sqlconn=MSSQL()     res=sqlconn.ExecQuery(sqlquery)     for data in res:         print data       if __name__=='__main__':     main()

配置文件mssql.conf:

1 2 3 4 5

[DB] host=x.x.x.x db=MyDB user=MyUser pwd=MyPassword

                   

国安信仰

不错，学习了！