1 #!/usr/bin/env python 2 # -*- coding: utf-8 -*- 3  4  5 import pyodbc 6  7  8 class MSSQL: 9     """10     封装pyodbc11     """12 13     def __init__(self,host,user,pwd,db=‘master‘, charset=‘utf8‘):14         self._host = host15         self._user = user16         self._pwd = pwd17         self._db = db18         self._charset = charset19 20     def __get_connect(self):21         """22         得到连接信息23         返回: conn.cursor()24         """25         if not self._db:26             raise(NameError,"没有设置数据库信息")27         conn_info = ‘DRIVER={SQL Server};DATABASE=%s;SERVER=%s;UID=%s;PWD=%s‘ % (self._db, self._host, self._user, self._pwd)28         self.conn = pyodbc.connect(conn_info, charset=self._charset)29         cur = self.conn.cursor()30         if not cur:31             raise(NameError,"连接数据库失败")32         else:33             return cur34 35     def __exec_query(self,sql):36         """37         执行查询语句38         返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段39 40         调用示例：41                 ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")42                 resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")43                 for (id,NickName) in resList:44                     print str(id),NickName45         """46         cur = self.__get_connect()47         cur.execute(sql)48         resList = cur.fetchall()49 50         #查询完毕后必须关闭连接51         self.conn.close()52         return resList53 54     def exec_query_tuple(self, sql):55         """结果集以元组返回"""56         return self.__exec_query(sql)57     58     def exec_query_dict(self, sql):59         result = []60         for row in self.__exec_query(sql):61             result.append( dict([(desc[0], row[index]) for index, desc in enumerate(row.cursor_description)]) )62         63         return result64 65     def exec_nonquery(self,sql):66         """67         执行非查询语句68 69         调用示例：70             cur = self.__GetConnect()71             cur.execute(sql)72             self.conn.commit()73             self.conn.close()74         """75         cur = self.__get_connect()76         cur.execute(sql)77         self.conn.commit()78         self.conn.close()79         80 81 if __name__ == ‘__main__‘:82     conn = MSSQL(‘192.168.1.124‘, ‘sa‘, ‘Hinac0m‘, ‘HINAMIIS‘, ‘GBK‘)83     84     print conn.exec_query_dict("select * from RISDevice where deviceid=1")