文件名：mssqlDB.py
1、导入模块：
# -*- coding:utf-8 -*- import pymssql #import numpy as np
2、配置辅助类：
class Configration: """数据库设置""" host="10.10.15.55" port=1433 user="sa" pwd="Aa123456" db="testDB" def __init__(self): pass
3、连接数据库辅助类：
class UniqueObject(Configration): #only offer a support of connecting db """description of class""" cur=None conn=None def __init__(self): Configration.__init__(self) def __del__(self): if self.conn!=None: self.conn.close() print(">>>>>>>>>>>>>>>>>Connection has been closed!<<<<<<<<<<<<<<<<<<<") @staticmethod def GetObject(): if UniqueObject.cur==None: print(">>>>>>>>>>>>>>>>>Connecting to Database.....<<<<<<<<<<<<<<<<<<") return UniqueObject.__GetConnect() return UniqueObject.conn,UniqueObject.cur def __GetConnect(): if not Configration.db: raise(NameError,"no db Info") UniqueObject.conn =pymssql.connect(host=Configration.host,port=Configration.port,\ user=Configration.user,password=Configration.pwd,\ database=Configration.db,charset="utf8") UniqueObject.cur=UniqueObject.conn.cursor() if not UniqueObject.cur: raise(NameError,"Connection error!") else: return UniqueObject.conn,UniqueObject.cur
4、数据操作类：
class MSSQL(): conn,cur=UniqueObject.GetObject() def __init__(self): pass def __del__(self): #print("\n MSSQL object has been realsed!") pass #普通查询 def GetQuery(self,sql): # MSSQL.cur.execute(sql) # reslist=self.cur.fetchall() # return reslist #cur = conn.cursor() result = [] try: MSSQL.cur.execute(sql) index = MSSQL.cur.description for res in MSSQL.cur.fetchall(): row = {} for i in range(len(index)): row[index[i][0]] = res[i] result.append(row) #MSSQL.cur.close() except: print(">>>>>>>>>>>>>>>>>GetQuery exception.....<<<<<<<<<<<<<<<<<<") return result #带分页查询 def GetQueryPage(self,sql): result = [] jieguo = {} try: MSSQL.cur.execute(sql) index = MSSQL.cur.description for res in MSSQL.cur.fetchall(): row = {} for i in range(len(index)): row[index[i][0]] = res[i] result.append(row) jieguo['datalist'] = result #循环多个结果集 for k in range(1,10) : if MSSQL.cur.nextset(): result1 = [] index = MSSQL.cur.description for res1 in MSSQL.cur.fetchall(): row = {} for i in range(len(index)): row[index[i][0]] = res1[i] result1.append(row) jieguo['datalist'+str(k)] = result1 else: print('循环结束') break; #MSSQL.cur.close() except: print(">>>>>>>>>>>>>>>>>GetQueryPage exception.....<<<<<<<<<<<<<<<<<<") return jieguo #执行SQL def ExecQuery(self,sql): try: MSSQL.cur.execute(sql) result = MSSQL.cur.rowcount MSSQL.conn.commit() return result except: print("\nInserting exception!") return -1
5、整合版源码：
# -*- coding:utf-8 -*- import pymssql #import numpy as np class Configration: """数据库设置""" host="10.10.15.55" port=1433 user="sa" pwd="Aa123456" db="testDB" def __init__(self): pass class UniqueObject(Configration): #only offer a support of connecting db """description of class""" cur=None conn=None def __init__(self): Configration.__init__(self) def __del__(self): if self.conn!=None: self.conn.close() print(">>>>>>>>>>>>>>>>>Connection has been closed!<<<<<<<<<<<<<<<<<<<") @staticmethod def GetObject(): if UniqueObject.cur==None: print(">>>>>>>>>>>>>>>>>Connecting to Database.....<<<<<<<<<<<<<<<<<<") return UniqueObject.__GetConnect() return UniqueObject.conn,UniqueObject.cur def __GetConnect(): if not Configration.db: raise(NameError,"no db Info") UniqueObject.conn =pymssql.connect(host=Configration.host,port=Configration.port,\ user=Configration.user,password=Configration.pwd,\ database=Configration.db,charset="utf8") UniqueObject.cur=UniqueObject.conn.cursor() if not UniqueObject.cur: raise(NameError,"Connection error!") else: return UniqueObject.conn,UniqueObject.cur class MSSQL(): conn,cur=UniqueObject.GetObject() def __init__(self): pass def __del__(self): #print("\n MSSQL object has been realsed!") pass def GetQuery(self,sql): # MSSQL.cur.execute(sql) # reslist=self.cur.fetchall() # return reslist #cur = conn.cursor() result = [] try: MSSQL.cur.execute(sql) index = MSSQL.cur.description for res in MSSQL.cur.fetchall(): row = {} for i in range(len(index)): row[index[i][0]] = res[i] result.append(row) #MSSQL.cur.close() except: print(">>>>>>>>>>>>>>>>>GetQuery exception.....<<<<<<<<<<<<<<<<<<") return result #带分页查询 def GetQueryPage(self,sql): result = [] jieguo = {} try: MSSQL.cur.execute(sql) index = MSSQL.cur.description for res in MSSQL.cur.fetchall(): row = {} for i in range(len(index)): row[index[i][0]] = res[i] result.append(row) jieguo = { "datalist":result} #2.下一个结果集 if MSSQL.cur.nextset(): result1 = [] index = MSSQL.cur.description for res in MSSQL.cur.fetchall(): row = {} for i in range(len(index)): row[index[i][0]] = res[i] result1.append(row) jieguo = { "datalist":result,"datapage":result1} #MSSQL.cur.close() except: print(">>>>>>>>>>>>>>>>>GetQueryPage exception.....<<<<<<<<<<<<<<<<<<") return jieguo def ExecQuery(self,sql): try: MSSQL.cur.execute(sql) result = MSSQL.cur.rowcount MSSQL.conn.commit() return result except: print("\nInserting exception!") return -1
6、调用实例：
from django.shortcuts import render from HelloWorld import mssqlDB from django.http import HttpResponse from django.views.decorators.csrf import csrf_exempt import json from HelloWorld import pager db = mssqlDB.MSSQL() # Create your views here. def blog_index(request): #ms = mssqlDB.MSSQL(host="10.10.15.55",user="sa",pwd="Aa123456",db="testDB") #resList = ms.GetQuery("select * from tUsers") blog_list = db.GetQuery("select * from tUsers") # 获取所有数据 #test=json.dumps(blog_list) return render(request,'index.html', {'blog_list':blog_list}) # 返回index.html #视图-添加用户 def blog_Add_V(request): return render(request,'add.html') #视图-分页 def pagelist(request): pagenum = request.GET.get('page') if pagenum == None: pagenum = 1 datalist = db.GetQueryPage("""select * from (select ROW_NUMBER() OVER (order by id desc )AS RowNumber,* from tUsers) as temp where RowNumber between 10*({0}-1) and 10*{0};SELECT COUNT(1) total FROM tUsers""".format(pagenum)) all_count = datalist.get("datapage")[0].get("total") # 获取要显示数据库的总数据条数 page_info = pager.PageInfo(request.GET.get('page'), all_count, 'pagelist.htm',) print(page_info.start_data()) print(page_info.end_data()) #获取数据集合 #page_info.pager() # 利用分页对象获取当前页显示数据 return render(request, 'pagelist.html', {'page_info': page_info,'datalist':datalist}) #DB-添加用户 @csrf_exempt #增加装饰器，作用是跳过 csrf 中间件的保护 def blog_Add_DB(request): sql="" if request.POST: sql = "INSERT INTO tUsers(name,age) VALUES('%s',%s)"%(request.POST['xingMing'],request.POST['nianLing']) else: sql = "INSERT INTO tUsers(name,age) VALUES('%s',%s)"%(request.GET['xingMing'],request.GET['nianLing']) print(sql) ret = db.ExecQuery(sql) if ret > 0: return HttpResponse('操作成功') else: return HttpResponse('操作失败')
9万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
