PySe-008-操作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 "\ninsert demo:" 126 delete() 127 insert() 128 query() 129 130 print "\nupdate demo:" 131 update() 132 query() 133 134 print "\ndelete demo:" 135 delete() 136 query() 137 138 print "\ntest 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
欢迎 【 留言 || 关注 || 打赏 】 。您的每一份心意都是对我的鼓励和支持!非常感谢!欢迎互加,相互交流学习!
作者:范丰平,本文链接:https://www.cnblogs.com/fengpingfan/p/7675297.html
Copyright @范丰平 版权所有,如需转载请标明本文原始链接出处,严禁商业用途! 我的个人博客链接地址:http://www.cnblogs.com/fengpingfan
![](https://images.cnblogs.com/cnblogs_com/fengpingfan/2153325/o_220506115434_gzgqrb.png)