Pymssql学习，对依时利一卡通考勤记录的修改，pymssql一卡通,vmssql.py# c

Pymssql学习，对依时利一卡通考勤记录的修改，pymssql一卡通,vmssql.py# c

vmssql.py

# coding=UTF-8#!/usr/bin/env pythonimport pymssqlclass VMSSQL:    """    pymssql     """    version = 0.2    def __init__(self,host,user,pwd,db):        self.host = host        self.user = user        self.pwd = pwd        self.db = db    def __GetConnect(self):        if not self.db:            raise(NameError, "Not set database")        self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db)        cur = self.conn.cursor()        if not cur:            raise(NameError, "Connect to database failure")        else:            return cur    def ExecCardid(self, emp_id):        cur = self.__GetConnect()        sql = 'select * from Employee where (emp_id = %s)'        cur.execute(sql, emp_id)        result = cur.fetchone()        card_id = result[1]        self.conn.close()        return card_id    def ExecNotes(self, emp_id, dateNotes):        cur = self.__GetConnect()        sql = 'select notes from Report_Day where (emp_id=%s and sign_date=%s)'        cur.execute(sql,(emp_id,dateNotes))        notes = cur.fetchone()        self.conn.close()        return notes    def ExecTimeRecords(self, emp_id, date):        cur = self.__GetConnect()        sql = 'select * from TimeRecords where (emp_id=%s and sign_time=%s)'        cur.execute(sql,(emp_id, date))        signTime = cur.fetchcll()        self.conn.close()        return signTime    def InsertInto(self, emp_id, card_id, date):        cur = self.__GetConnect()        sign_time0 = date + ' 08:30:00'        sign_time1 = date + ' 12:01:02'        sign_time2 = date + ' 13:51:03'        sign_time3 = date + ' 18:32:04'        sql = "insert into TimeRecords (clock_id,emp_id,card_id,sign_time) values(6, %s, %s, %s)"        cur.execute(sql,(emp_id, card_id, sign_time0))        cur.execute(sql,(emp_id, card_id, sign_time1))        cur.execute(sql,(emp_id, card_id, sign_time2))        cur.execute(sql,(emp_id, card_id, sign_time3))        self.conn.commit()        self.conn.close()    def InsertIntoGoWorkAM(self, emp_id, card_id, date):        cur = self.__GetConnect()        sign_time = date + ' 08:30:00'        sql = "insert into TimeRecords (clock_id,emp_id,card_id,sign_time) values(6, %s, %s, %s)"        cur.execute(sql,(emp_id, card_id, sign_time))        self.conn.commit()        self.conn.close()    def InsertIntoOffWorkAM(self, emp_id, card_id, date):        cur = self.__GetConnect()        sign_time = date + ' 12:01:00'        sql = "insert into TimeRecords (clock_id,emp_id,card_id,sign_time) values(6, %s, %s, %s)"        cur.execute(sql,(emp_id, card_id, sign_time))        self.conn.commit()        self.conn.close()    def InsertIntoGoWorkPM(self, emp_id, card_id, date):        cur = self.__GetConnect()        sign_time = date + ' 13:50:00'        sql = "insert into TimeRecords (clock_id,emp_id,card_id,sign_time) values(6, %s, %s, %s)"        cur.execute(sql,(emp_id, card_id, sign_time))        self.conn.commit()        self.conn.close()    def InsertIntoOffWorkPM(self, emp_id, card_id, date):        cur = self.__GetConnect()        sign_time = date + ' 18:30:00'        sql = "insert into TimeRecords (clock_id,emp_id,card_id,sign_time) values(6, %s, %s, %s)"        cur.execute(sql,(emp_id, card_id, sign_time))        self.conn.commit()        self.conn.close()def main():    ms = VMSSQL(host="localhost",user="sa",pwd="",db="EASTRIVER")    print("***********************************")    print(u'*请输入：1 查看考勤记录:          *')    print(u'*请输入：2 修改打卡记录:          *')    print(u'*请输入：3 查看帮助               *')    print("***********************************")    print(u"*请输入 1或2或3:"),    choose = input()    if choose == 3:        print(u'*1.工号位数为六位。(例:001122)')        print(u'*2.日期的格式一定要输入正确。(例：2012-07-01)')        print(u'*3.修改打卡记录后，考勤记录不会实时更新。')        print(u'*请按回车退出.')        raw_input()    elif choose == 1 or choose == 2:        print(u"*请输入工号(例:001122):"),        emp_id = raw_input()        if choose == 1:            print(u"*请输入日期(格式:2012-07-01):"),            dateNotes = raw_input()            notes = ms.ExecNotes(emp_id, dateNotes)            print(u'%s') % notes            print(u"按回车退出.")            raw_input()        elif choose == 2:            card_id = ms.ExecCardid(emp_id)            print(u"*请输入日期(格式:2012-07-01):"),            date = raw_input()            print(u"*修改上午上班请输入1:")            print(u"*修改上午下班请输入2:")            print(u"*修改下午上班请输入3:")            print(u"*修改下午下班请输入4:")            print(u"*修改全天请输入5:")            print(u"*请输入:"),            workchoose = input()            if workchoose == 1:                ms.InsertIntoGoWorkAM(emp_id, card_id, date)                print(u"*修改完成，请按回车退出。")                raw_input()            elif workchoose == 2:                ms.InsertIntoOffWorkAM(emp_id, card_id, date)                print(u"*修改完成，请按回车退出。")                raw_input()            elif workchoose == 3:                ms.InsertIntoGoWorkPM(emp_id, card_id, date)                print(u"*修改完成，请按回车退出。")                raw_input()            elif workchoose == 4:                ms.InsertIntoOffWorkPM(emp_id, card_id, date)                print(u"*修改完成，请按回车退出。")                raw_input()            elif workchoose == 5:                ms.InsertInto(emp_id, card_id, date)                print(u"*修改完成，请按回车退出。")                raw_input()            else:                print(u"*输入错误,请按回车退出。")                raw_input()    else:        print(u"*输入错误,请按回车退出。")        raw_input()if __name__ == '__main__':    main()