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

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)

代码示例:

#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:

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