# -*- coding: utf8 -*-
import Class_MySql
import pymssql
import wx
import wx.grid #命名空间grid
import os
import types
import time
#解决_mssql缺失等一系列错误,主要是import pymssql引起
import _mssql
import uuid
import decimal
#-------------------------
# 文件名称:dormitoryRecord.py
# 作 者: ghf
# 日 期: 2014-3-3
# 功 能: 宿舍查询请假人员名单
# 版 本: v0.1
# 功 能: 1、自动查询
# 2、按照请假先后顺序排列
#
# 版 本: v0.2
# 功 能: 1、不再自动更新,提供单击查询
# 2、结果按照宿舍号,床位号排序
# 3、提供字体改变功能
# 版 本: v0.3
# 功 能: 1、将日期显示格式更新为:03-12 11:02 --> 04-12 11:02
# 2、颜色交替显示
# 3、背景色增加
#-------------------------
#-------
# 类名称:MainFrame
# 功 能:主界面
# 日 期:2014-3-3
#-------
class MainFrame(wx.Frame):
dbStr = "host='', user='sa', pwd='', db=''"
updateTime = 5000 #更新时间间隔
noDisColNum = 4 #不显示的记录集数目
fontSize = 16 #字体大小
def __init__(self, parent, id=-1, title='宿舍查询',
position=wx.DefaultPosition, size=(500,450)):
self.frame = wx.Frame.__init__(self, parent, id, title, position, size)
self.InitUI()
self.Show()
def InitUI(self):
#容器,布局
panel = wx.Panel(self, -1)
vBox = wx.BoxSizer(wx.VERTICAL)
hHeadBox = wx.BoxSizer(wx.HORIZONTAL)
#获取系统字体信息
font = wx.SystemSettings_GetFont(wx.SYS_DEFAULT_GUI_FONT)
name = font.GetFaceName()
size = font.GetPointSize()
#设置颜色
#----单选按钮框
sampleList = ['自信苑', '勤奋苑', '责任苑', '诚实苑','感恩苑男', '感恩苑女']
self.radBox = wx.RadioBox(panel, -1, "宿舍", (10, 10), wx.DefaultSize,
sampleList, 1, wx.RA_SPECIFY_ROWS)
#----查询按钮
self.btnQuery = wx.Button(panel, -1, '单击查询')
#---静态文本:显示提示信息
self.labInfor = wx.StaticText(panel, -1, '滑动滑块调整字体大小:', pos=(20,20), size=(200, 20), style=wx.ALIGN_CENTER)
#----滑块,调整字体大小
self.sliderFontSize = wx.Slider(panel, -1, 16, 1, 100, size=(350,10), style=wx.SL_HORIZONTAL | wx.SL_AUTOTICKS | wx.SL_LABELS )
self.sliderFontSize.SetTickFreq(5, 1)
#----静态文本框
self.txtCtrl = wx.TextCtrl(panel, -1, value='当前更新时间: '+time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
+' 当前宿舍楼: '+self.radBox.GetStringSelection())
txtCtrlFont = wx.Font(20, wx.SWISS, wx.NORMAL, wx.BOLD)
self.txtCtrl.SetFont(txtCtrlFont)
#绑定事件
self.radBox.Bind(wx.EVT_RADIOBOX, self.OnRadioBox)
self.Bind(wx.EVT_BUTTON, self.OnClick, self.btnQuery)
self.Bind(wx.EVT_SCROLL_ENDSCROLL, self.OnEndScroll, self.sliderFontSize)
#获取请假学校的信息
resList = self.GetData()
#表格显示
self.grid = wx.grid.Grid(panel)#创建表格
self.grid.CreateGrid(len(resList), len(resList[0]))#设置表格数目
#设置行高
for row in range(len(resList)):
self.grid.SetRowSize(row, self.fontSize*2)
#设置列宽
for col in range(len(resList[0])):
if (type(resList[0][col]) is types.UnicodeType):
self.grid.SetColSize(col, (4+len(resList[0][col]))*(self.fontSize))
#给单元格赋值
for row in range(len(resList)):
for col in range(len(resList[row])):
resCol = col
self.grid.SetCellValue(row,col,
"%s"%(resList[row][col]))
#设置字体
self.grid.SetCellFont(row, col, wx.Font(self.fontSize, wx.SWISS, wx.NORMAL, wx.BOLD))
#设置颜色,交替
if row % 2 :
self.grid.SetCellBackgroundColour(row, col, "Light Blue")
#添加布局管理器
hHeadBox.Add(self.radBox, 0, wx.EXPAND, 1)
hHeadBox.Add(self.btnQuery, 0, wx.EXPAND, 5)
hHeadBox.Add(self.labInfor, 0, wx.EXPAND|wx.ALL, 10)
hHeadBox.Add(self.sliderFontSize, 0, wx.EXPAND, 5)
vBox.Add(hHeadBox ,0, wx.EXPAND|wx.ALL, 2)
vBox.Add(self.txtCtrl, 0, wx.EXPAND, 2)
vBox.Add(self.grid, 2, wx.EXPAND | wx.ALL, 2)
panel.SetSizer(vBox)
#设定定时器
self.timer = wx.Timer(self)
self.Bind(wx.EVT_TIMER, self.OnTimer, self.timer)
#self.timer.Start(self.updateTime)#间隔时间
#####################
# 名称:GetData
# 功能:读取数据库请假学生名单
# 日期:
####################
def GetData(self):
dorName = self.radBox.GetStringSelection()#'责任苑'
ms = Class_MySql.MSSQL(host='', user='sa', pwd='', db='')
resList = ms.ExecQuery("exec db_dorRecord @dorName='"+dorName+"'")
#resList = ms.ExecQuery("exec db_dorRecord @dorName='勤奋苑'")
#清除以前的请假记录
ms.ExecNonQuery("exec db_deleteDorRecord")
#修改列数,对数据进行调整,不再更改Sql语句,保持对以前版本的支持尤其v0.2
#对获取后的数据进行修改成为v0.3
returnList = []
for row in resList:
returnList.append((row[2], row[3], row[4]+' —— '+row[5], row[6]+'-'+row[7]))
#return resList
return returnList
#按钮选择改变时更新显示数据
def OnRadioBox(self, event):
rowCount = self.grid.GetNumberRows()
if (rowCount>0):
self.grid.DeleteRows(0, rowCount)
resList = self.GetData()
#表格显示
self.grid.AppendRows(len(resList))
#设置列宽
for col in range(len(resList[0])):
if (type(resList[0][col]) is types.UnicodeType):
self.grid.SetColSize(col, (4+len(resList[0][col]))*(self.fontSize))
#设置新值
self.grid.BeginBatch()
for row in range(len(resList)):
#设置行高
self.grid.SetRowSize(row, self.fontSize*2)
for col in range(len(resList[row])):
resCol = col
self.grid.SetCellValue(row,col,
"%s"%(resList[row][resCol]))
#设置字体
self.grid.SetCellFont(row, col, wx.Font(self.fontSize, wx.SWISS, wx.NORMAL, wx.BOLD))
#设置颜色,交替
if row % 2 :
self.grid.SetCellBackgroundColour(row, col, "Light Blue")
self.grid.EndBatch()
#显示更新时间与宿舍楼
self.txtCtrl.SetValue('当前更新时间: '+time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
+' 当前宿舍楼: '+self.radBox.GetStringSelection())
#时间更新时更新数据显示
def OnTimer(self, event):
self.OnRadioBox(event)
#单击查询
def OnClick(self, event):
self.OnTimer(event)
#滑块事件
def OnEndScroll(self, event):
#修改字体大小值
self.fontSize = self.sliderFontSize.GetValue()
#获取焦点
self.grid.SetFocus()
self.OnRadioBox(event)
def main():
#print 'running dormitoryRecord.py...\n'
app = wx.App(redirect=False)
mainFrame = MainFrame(None,-1)
app.MainLoop()
#print '\nend running dormitoryRecord.py...'
if __name__ == '__main__':
main()
Class_MySql.py
#coding=utf8
#!/usr/bin/env python
#-------------------------------------------------------------------------------
# Name: pymssqlTest.py
# Purpose: 测试 pymssql库,该库到这里下载:http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
#
# Author: scott
#
# Created: 04/02/2012
#-------------------------------------------------------------------------------
import pymssql
import sys
import decimal
reload(sys)
sys.setdefaultencoding( "utf8" )
class MSSQL:
"""
对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()
"""
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()
if not cur:
raise(NameError,"连接数据库失败")
else:
return cur
def ExecQuery(self,sql):
"""
执行查询语句
返回的是一个包含tuple的list,list的元素是记录行,tuple的元素是每行记录的字段
调用示例:
ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")
resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")
for (id,NickName) in resList:
print str(id),NickName
"""
cur = self.__GetConnect()
cur.execute(sql)
resList = cur.fetchall()
#查询完毕后必须关闭连接
self.conn.close()
return resList
def ExecNonQuery(self,sql):
"""
执行非查询语句
调用示例:
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()
"""
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()