前文http://www.cnblogs.com/fengpingfan/p/7675291.html，讲述了python操作mssql的步骤、环境创建、常用方法等，本文将实例演示python操作mssql的常规功能脚本。
多不闲述，直接上码。
1.3、简单示例脚本
python操作mssql简单示例脚本如下所示：
#coding=utf-8
#!/usr/bin/env python
#-------------------------------------------------------------------------------
# Name: DbsUtil.py
# Purpose: 数据库操作工具类
#
# Author: 范丰平 2017-10-01
#
#-------------------------------------------------------------------------------
import sys
import pymssql class MSSQL(object):
"""
对pymssql的简单封装，pymssql库，该库到这里下载：http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
使用该库时，需要在Sql Server Configuration Manager里面将TCP/IP协议开启
""" def __init__(self,host,user,pwd,db):
self.host = host
self.user = user
self.pwd = pwd
self.db = db def __GetConnect(self):
"""
获取连接信息，返回: conn.cursor()
"""
try:
if not self.db:
raise(NameError,"没有设置数据库信息") self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")
cur = self.conn.cursor()
return cur
except Exception, err:
print "连接数据库失败：%s" % str(err) return None def ExecQuery(self,sql):
"""
执行查询语句，返回包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
调用示例：
ms = MSSQL(host="localhost",user="sa",pwd="123456",db="autoTestMaster")
resList = ms.ExecQuery("SELECT id,NickName FROM atm_User")
for (id,NickName) in resList:
print str(id),NickName
"""
try:
cur = self.__GetConnect()
cur.execute(sql)
resList = cur.fetchall() #查询完毕后必须关闭连接
self.conn.close()
return resList
except Exception, err:
print "查询结果失败。SQL语句：%s，失败原因：%s" % (sql, str(err))
sys.exit(1) def ExecNonQuery(self,sql):
"""
执行非查询语句
调用示例：
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()
"""
try:
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()
except Exception, err:
print "执行失败。SQL语句：%s，失败原因：%s" % (sql, str(err))
sys.exit(1) def query():
## ms = MSSQL(host="localhost",user="sa",pwd="123456",db="autoTestMaster")
## 返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
## ms.ExecNonQuery("insert into WeiBoUser values('2','3')")
sql = "SELECT count(*) as counts_up from Student"
ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")
resList = ms.ExecQuery(sql)
for (counts_up) in resList:
print str(counts_up).decode("utf8")
print counts_up[0] resList = ms.ExecQuery("SELECT no, name, phone, email, addr, description from Student")
for (no, name, phone, email, addr, desc) in resList:
print "no:%s name:%s phone:%s email:%s addr:%s desc:%s" % (no, name, phone, email, addr, desc) def insert():
sql = "INSERT INTO Student values ('chs000000000003', '雪狼', '15212345673', 'shewoqishui20080808@126.com', '华夏', '博客：http://www.cnblogs.com/fengpingfan')"
ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")
resList = ms.ExecNonQuery(sql) def delete():
sql = "DELETE FROM Student WHERE NO = 'chs000000000003'"
ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")
resList = ms.ExecNonQuery(sql) def update():
sql = "UPDATE Student SET email = '1234567890@126.com' WHERE NO = 'chs000000000003'"
ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")
resList = ms.ExecNonQuery(sql) def test_fail():
sql = "UPDATE Student SET emails = '1234567890@126.com' WHERE NO = 'chs000000000003'"
ms = MSSQL(host="127.0.0.1",user="fanfengping",pwd="python_mssql",db="python_mssql")
print ms
print type(ms)
resList = ms.ExecNonQuery(sql) if __name__ == '__main__':
print "query demo:"
query() print "\ninsert demo:"
delete()
insert()
query() print "\nupdate demo:"
update()
query() print "\ndelete demo:"
delete()
query() print "\ntest fail:"
test_fail()
1.4、sqlserver创建数据表脚本如下所示：
-- ----------------------------
-- Table structure for Student
-- ----------------------------
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = object_id(N'Student') AND OBJECTPROPERTY(ID, 'IsTable') = 1)
DROP TABLE [dbo].[Student]
GO
CREATE TABLE [dbo].[Student] (
[no] varchar(15) NOT NULL ,
[name] varchar(50) NOT NULL DEFAULT '' ,
[phone] varchar(11) NOT NULL DEFAULT '' ,
[email] varchar(100) NULL DEFAULT '' ,
[addr] varchar(100) NULL DEFAULT '' ,
[description] varchar(100) NULL DEFAULT ''
) GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'Student',
NULL, NULL)) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'学生信息表'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'学生信息表'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'Student',
'COLUMN', N'no')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'学号'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'no'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'学号'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'no'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'Student',
'COLUMN', N'name')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'姓名'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'name'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'姓名'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'name'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'Student',
'COLUMN', N'phone')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'手机'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'phone'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'手机'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'phone'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'Student',
'COLUMN', N'email')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'邮箱'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'email'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'邮箱'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'email'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'Student',
'COLUMN', N'addr')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'住址'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'addr'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'住址'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'addr'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'Student',
'COLUMN', N'description')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'个人简介'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'description'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'个人简介'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Student'
, @level2type = 'COLUMN', @level2name = N'description'
GO
-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO Student(no, name, phone, email, addr, description) values ('chs000000000000', '范丰平', '', 'shewoqishui20080808@126.com', '华夏', '博客：http://www.cnblogs.com/fengpingfan')
INSERT INTO Student(no, name, phone, email, addr, description) values ('chs000000000001', '蓝剑', '', 'shewoqishui20080808@126.com', '华夏', '博客：http://www.cnblogs.com/fengpingfan')
INSERT INTO Student(no, name, phone, email, addr, description) values ('chs000000000002', '狼牙', '', 'shewoqishui20080808@126.com', '华夏', '博客：http://www.cnblogs.com/fengpingfan') -- ----------------------------
-- Indexes structure for table Student
-- ---------------------------- -- ----------------------------
-- Primary Key structure for table Student
-- ----------------------------
ALTER TABLE [dbo].[Student] ADD PRIMARY KEY ([no])
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#