python操作sqlserver

 1 # coding=utf-8
 2 import pymssql
 3 
 4 class SQLServer:   
 5     def __init__(self,server,user,password,database):
 6     # 类的构造函数，初始化DBC连接信息
 7         self.server = server
 8         self.user = user
 9         self.password = password
10         self.database = database
11 
12     def __GetConnect(self):
13     # 得到数据库连接信息，返回conn.cursor()
14         if not self.database:
15             raise(NameError,"没有设置数据库信息")
16         self.conn = pymssql.connect(server=self.server,user=self.user,password=self.password,database=self.database)
17         cur = self.conn.cursor()
18         if not cur:
19             raise(NameError,"连接数据库失败")  # 将DBC信息赋值给cur
20         else:
21             return cur
22              
23     def ExecQuery(self,sql):
24         '''
25         执行查询语句
26         返回一个包含tuple的list，list是元素的记录行，tuple记录每行的字段数值
27         '''
28         cur = self.__GetConnect()
29         cur.execute(sql) # 执行查询语句
30         result = cur.fetchall() # fetchall()获取查询结果
31         # 查询完毕关闭数据库连接
32         self.conn.close()
33         return result
34 
35 def main():
36     msg = SQLServer(server="127.0.0.1",user="test",password="Test321",database="TEST")
37     result = msg.ExecQuery("SELECT TOP 1 Value FROM t_Security_Code WHERE Mobile = '18501007700' ORDER BY InsertTime DESC")
38     for (Value) in result:
39         print(Value)
40  
41 if __name__ == '__main__':
42     main()
复制代码