Python002-操作MSSQL（Microsoft sql server）基础示例（二）

前文 http://www.cnblogs.com/fengpingfan/p/7675291.html，讲述了python操作mssql的步骤、环境创建、常用方法等，本文将实例演示python操作mssql的常规功能脚本。

多不闲述，直接上码。

1.3、简单示例脚本

python操作mssql简单示例脚本如下所示：

  1 #coding=utf-8 
  2 #!/usr/bin/env python
  3 #-------------------------------------------------------------------------------
  4 # Name: DbsUtil.py
  5 # Purpose: 数据库操作工具类
  6 #
  7 # Author: 范丰平 2017-10-01
  8 # 
  9 #-------------------------------------------------------------------------------
 10 import sys
 11 import pymssql
 12 
 13 class MSSQL(object):
 14     """
 15     对pymssql的简单封装，pymssql库，该库到这里下载：http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
 16     使用该库时，需要在Sql Server Configuration Manager里面将TCP/IP协议开启
 17     """
 18 
 19     def __init__(self,host,user,pwd,db):
 20         self.host = host
 21         self.user = user
 22         self.pwd = pwd
 23         self.db = db
 24 
 25     def __GetConnect(self):
 26         """
 27         获取连接信息，返回: conn.cursor()
 28         """
 29         try:
 30             if not self.db:
 31                raise(NameError,"没有设置数据库信息")
 32 
 33             self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")
 34             cur = self.conn.cursor()
 35             return cur
 36         except Exception, err:
 37             print "连接数据库失败：%s" % str(err)
 38         
 39         return None
 40 
 41 
 42     def ExecQuery(self,sql):
 43         """
 44         执行查询语句，返回包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
 45         调用示例：
 46                 ms = MSSQL(host="localhost",user="sa",pwd="123456",db="autoTestMaster")
 47                 resList = ms.ExecQuery("SELECT id,NickName FROM atm_User")
 48                 for (id,NickName) in resList:
 49                     print str(id),NickName
 50         """
 51         try:
 52             cur = self.__GetConnect()
 53             cur.execute(sql)
 54             resList = cur.fetchall()
 55 
 56             #查询完毕后必须关闭连接
 57             self.conn.close()
 58             return resList
 59         except Exception, err: 
 60             print "查询结果失败。SQL语句：%s，失败原因：%s" % (sql, str(err))
 61             sys.exit(1)
 62 
 63     def ExecNonQuery(self,sql):
 64         """
 65         执行非查询语句
 66         调用示例：
 67             cur = self.__GetConnect()
 68             cur.execute(sql)
 69             self.conn.commit()
 70             self.conn.close()
 71         """
 72         try:
 73             cur = self.__GetConnect()
 74             cur.execute(sql)
 75             self.conn.commit()
 76             self.conn.close()
 77         except Exception, err:
 78             print "执行失败。SQL语句：%s，失败原因：%s" % (sql, str(err))
 79             sys.exit(1)
 80 
 81 
 82 def query():
 83     ## ms = MSSQL(host="localhost",user="sa",pwd="123456",db="autoTestMaster")
 84     ## 返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
 85     ## ms.ExecNonQuery("insert into WeiBoUser values('2','3')")
 86     sql = "SELECT count(*) as counts_up from Student"
 87     ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")
 88     resList = ms.ExecQuery(sql)
 89     for (counts_up) in resList:
 90         print str(counts_up).decode("utf8")
 91         print counts_up[0]
 92 
 93     resList = ms.ExecQuery("SELECT no, name, phone, email, addr, description from Student")
 94     for (no, name, phone, email, addr, desc) in resList:
 95         print "no:%s  name:%s  phone:%s  email:%s addr:%s desc:%s" % (no, name, phone, email, addr, desc)
 96 
 97 def insert():
 98     sql = "INSERT INTO Student values ('chs000000000003', '雪狼', '15212345673', 'shewoqishui20080808@126.com', '华夏', '博客：http://www.cnblogs.com/fengpingfan')"
 99     ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")
100     resList = ms.ExecNonQuery(sql)
101 
102 def delete():
103     sql = "DELETE FROM Student WHERE NO = 'chs000000000003'"
104     ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")    
105     resList = ms.ExecNonQuery(sql)
106 
107 
108 def update():
109     sql = "UPDATE Student SET email = '1234567890@126.com' WHERE NO = 'chs000000000003'"
110     ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")        
111     resList = ms.ExecNonQuery(sql)
112 
113 def test_fail():
114     sql = "UPDATE Student SET emails = '1234567890@126.com' WHERE NO = 'chs000000000003'"
115     ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")
116     print ms
117     print type(ms)
118     resList = ms.ExecNonQuery(sql)
119 
120 
121 if __name__ == '__main__':
122     print "query demo:" 
123     query()
124 
125     print "
insert demo:"
126     delete()
127     insert()
128     query()
129 
130     print "
update demo:"
131     update()
132     query()
133 
134     print "
delete demo:"
135     delete()
136     query()
137 
138     print "
test fail:"
139     test_fail()

1.4、sqlserver创建数据表脚本如下所示：

  1 -- ----------------------------
  2 -- Table structure for Student
  3 -- ----------------------------
  4 IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'Student') AND OBJECTPROPERTY(ID, 'IsTable') = 1) 
  5     DROP TABLE [dbo].[Student]
  6 GO
  7 CREATE TABLE [dbo].[Student] (
  8 [no] varchar(15) NOT NULL ,
  9 [name] varchar(50) NOT NULL DEFAULT '' ,
 10 [phone] varchar(11) NOT NULL DEFAULT '' ,
 11 [email] varchar(100) NULL DEFAULT '' ,
 12 [addr] varchar(100) NULL DEFAULT '' ,
 13 [description] varchar(100) NULL DEFAULT '' 
 14 )
 15 
 16 
 17 GO
 18 IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
 19 'SCHEMA', N'dbo', 
 20 'TABLE', N'Student', 
 21 NULL, NULL)) > 0) 
 22 EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'学生信息表'
 23 , @level0type = 'SCHEMA', @level0name = N'dbo'
 24 , @level1type = 'TABLE', @level1name = N'Student'
 25 ELSE
 26 EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'学生信息表'
 27 , @level0type = 'SCHEMA', @level0name = N'dbo'
 28 , @level1type = 'TABLE', @level1name = N'Student'
 29 GO
 30 IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
 31 'SCHEMA', N'dbo', 
 32 'TABLE', N'Student', 
 33 'COLUMN', N'no')) > 0) 
 34 EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'学号'
 35 , @level0type = 'SCHEMA', @level0name = N'dbo'
 36 , @level1type = 'TABLE', @level1name = N'Student'
 37 , @level2type = 'COLUMN', @level2name = N'no'
 38 ELSE
 39 EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'学号'
 40 , @level0type = 'SCHEMA', @level0name = N'dbo'
 41 , @level1type = 'TABLE', @level1name = N'Student'
 42 , @level2type = 'COLUMN', @level2name = N'no'
 43 GO
 44 IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
 45 'SCHEMA', N'dbo', 
 46 'TABLE', N'Student', 
 47 'COLUMN', N'name')) > 0) 
 48 EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'姓名'
 49 , @level0type = 'SCHEMA', @level0name = N'dbo'
 50 , @level1type = 'TABLE', @level1name = N'Student'
 51 , @level2type = 'COLUMN', @level2name = N'name'
 52 ELSE
 53 EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'姓名'
 54 , @level0type = 'SCHEMA', @level0name = N'dbo'
 55 , @level1type = 'TABLE', @level1name = N'Student'
 56 , @level2type = 'COLUMN', @level2name = N'name'
 57 GO
 58 IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
 59 'SCHEMA', N'dbo', 
 60 'TABLE', N'Student', 
 61 'COLUMN', N'phone')) > 0) 
 62 EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'手机'
 63 , @level0type = 'SCHEMA', @level0name = N'dbo'
 64 , @level1type = 'TABLE', @level1name = N'Student'
 65 , @level2type = 'COLUMN', @level2name = N'phone'
 66 ELSE
 67 EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'手机'
 68 , @level0type = 'SCHEMA', @level0name = N'dbo'
 69 , @level1type = 'TABLE', @level1name = N'Student'
 70 , @level2type = 'COLUMN', @level2name = N'phone'
 71 GO
 72 IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
 73 'SCHEMA', N'dbo', 
 74 'TABLE', N'Student', 
 75 'COLUMN', N'email')) > 0) 
 76 EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'邮箱'
 77 , @level0type = 'SCHEMA', @level0name = N'dbo'
 78 , @level1type = 'TABLE', @level1name = N'Student'
 79 , @level2type = 'COLUMN', @level2name = N'email'
 80 ELSE
 81 EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'邮箱'
 82 , @level0type = 'SCHEMA', @level0name = N'dbo'
 83 , @level1type = 'TABLE', @level1name = N'Student'
 84 , @level2type = 'COLUMN', @level2name = N'email'
 85 GO
 86 IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
 87 'SCHEMA', N'dbo', 
 88 'TABLE', N'Student', 
 89 'COLUMN', N'addr')) > 0) 
 90 EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'住址'
 91 , @level0type = 'SCHEMA', @level0name = N'dbo'
 92 , @level1type = 'TABLE', @level1name = N'Student'
 93 , @level2type = 'COLUMN', @level2name = N'addr'
 94 ELSE
 95 EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'住址'
 96 , @level0type = 'SCHEMA', @level0name = N'dbo'
 97 , @level1type = 'TABLE', @level1name = N'Student'
 98 , @level2type = 'COLUMN', @level2name = N'addr'
 99 GO
100 IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
101 'SCHEMA', N'dbo', 
102 'TABLE', N'Student', 
103 'COLUMN', N'description')) > 0) 
104 EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'个人简介'
105 , @level0type = 'SCHEMA', @level0name = N'dbo'
106 , @level1type = 'TABLE', @level1name = N'Student'
107 , @level2type = 'COLUMN', @level2name = N'description'
108 ELSE
109 EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'个人简介'
110 , @level0type = 'SCHEMA', @level0name = N'dbo'
111 , @level1type = 'TABLE', @level1name = N'Student'
112 , @level2type = 'COLUMN', @level2name = N'description'
113 GO
114 -- ----------------------------
115 -- Records of Student
116 -- ----------------------------
117 INSERT INTO Student(no, name, phone, email, addr, description) values ('chs000000000000', '范丰平', '15212345670', 'shewoqishui20080808@126.com', '华夏', '博客：http://www.cnblogs.com/fengpingfan')
118 INSERT INTO Student(no, name, phone, email, addr, description) values ('chs000000000001', '蓝剑', '15212345671', 'shewoqishui20080808@126.com', '华夏', '博客：http://www.cnblogs.com/fengpingfan')
119 INSERT INTO Student(no, name, phone, email, addr, description) values ('chs000000000002', '狼牙', '15212345672', 'shewoqishui20080808@126.com', '华夏', '博客：http://www.cnblogs.com/fengpingfan')
120 
121 -- ----------------------------
122 -- Indexes structure for table Student
123 -- ----------------------------
124 
125 -- ----------------------------
126 -- Primary Key structure for table Student
127 -- ----------------------------
128 ALTER TABLE [dbo].[Student] ADD PRIMARY KEY ([no])
129 GO

插入数据后，数据查询结果如下所示：

1.5、脚本执行

脚本执行结果如下所示：

1.6、参考文档：

官方文档：http://pymssql.org/en/stable/

接口文档：http://www.pymssql.org/en/latest/ref/pymssql.html

示例文档：http://www.pymssql.org/en/latest/pymssql_examples.html#

GitHub：https://github.com/pymssql/pymssql