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

前文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#

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

巴特西

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

最新文章

热门文章