1 #!/usr/bin/env python
2 # -*- coding: utf-8 -*-
3
4
5 import pyodbc
6
7
8 class MSSQL:
9 """
10 封装pyodbc
11 """
12
13 def __init__(self,host,user,pwd,db='master', charset='utf8'):
14 self._host = host
15 self._user = user
16 self._pwd = pwd
17 self._db = db
18 self._charset = charset
19
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;U % (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 cur
34
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),NickName
45 """
46 cur = self.__get_connect()
47 cur.execute(sql)
48 resList = cur.fetchall()
49
50 #查询完毕后必须关闭连接
51 self.conn.close()
52 return resList
53
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 result
64
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', 'Password', 'Demo', 'GBK')
83
84 print conn.exec_query_dict("select * from Staff where code=0001")