def insert(self): cur, conn = self.checks() try: cur.execute(self.sql) conn.commit() sqlMsg = 'Executed SQL: {}.'.format(self.sql) msg = 'Insert succ.' log1 = logs(sqlMsg) log1.mgt() log2 = logs(msg) log2.mgt() return msg, True except Exception as e: conn.rollback() msg = str(e) log = logs(msg) log.mgt() return msg, False finally: inserMsg = '[Insert] MySQL disconnect.' log = logs(inserMsg) log.mgt() cur.close() conn.close()
def conns(self): filename = 'config.ini' users = configs(filename, 'Mysql', 'user') word = configs(filename, 'Mysql', 'password') hosts = configs(filename, 'Mysql', 'host') data = configs(filename, 'Mysql', 'database') ports = configs(filename, 'Mysql', 'port') username, ok = users.read() password, ok = word.read() host, ok = hosts.read() database, ok = data.read() port, ok = ports.read() try: conn = pymysql.connect( host=host, user=username, password=password, database=database, port=int(port), ) except Exception as e: mes = str(e) log = logs(mes) log.mgt() sys.exit(mes) else: mes = 'Mysql connection successfully.' log = logs(mes) log.mgt() return conn, mes
def select(self): cur, conn = self.checks() try: cur.execute(self.sql) except Exception as e: msg = str(e) log = logs(msg) log.mgt() return msg, False else: rows = cur.fetchall() if len(rows) == 0: retdata = [] # return 'No data found in the table.', False return retdata, False else: fields = [ 'UserID', 'Name', 'Phone', 'Company', 'Address', 'Email' ] retdata = [dict(zip(fields, i)) for i in rows] # 生成列表格式 # data = json.dumps(retdata, indent=4) #生成json格式 return retdata, True finally: msg = '[select] MySQL disconnect.' log = logs(msg) log.mgt() cur.close() conn.close()
def save(self): ok = self.leng('save', 1) if ok: x.clear_rows() tempDelList = [] stuList, mes, ok = self.load() tempDict = self.temp() for Row in stuList: for Item in tempDict.keys(): if Item == Row['UserID']: tempDelList.append(Item) # 获取数据库内的用户ID for Item in tempDelList: tempDict.pop(Item) # 判断待录入的用户ID 与数据库中的用户 ID 是否冲突 mes = '用户ID {} 已存在,信息\033[1;31m录入失败\033[0m.'.format(Item) log = logs(mes) log.mgt() print(mes + '\n') if len(tempDict) >= 1: # 至少存在一个有效的待录入的用户信息 for Item in tempDict.keys(): stuId = Item stuName = tempDict[Item]['name'] stuPhone = tempDict[Item]['Phone'] stuCompany = tempDict[Item]['Company'] stuAddress = tempDict[Item]['Address'] stuEmail = tempDict[Item]['Email'] sql = '''insert into yusers values('{}','{}','{}','{}','{}','{}');'''.format( stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail) x.add_row([ stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail ]) retdata = db('insert', sql) stuList, ok = retdata.insert() if not ok: mes = '用户ID {} 保存失败.'.format(stuId) log = logs(mes) log.mgt() print(mes) else: mes = '用户ID {} 保存成功.'.format(stuId) log = logs(mes) log.mgt() print(mes) print('保存成功的用户信息如下: ') print(x) else: # 如果待录入的用户 ID 都和本地文件中的用户 ID 冲突 mes = '缓存列表为空, 保存失败.' log = logs(mes) log.mgt() print(mes) else: mes = '指令错误' log = logs(mes) log.mgt() print(mes)
def modify(self): cur, conn = self.checks() try: cur.execute(self.sql) if cur.rowcount == 0: msg = '{} fail.'.format(self.ops) log = logs(msg) log.mgt() return msg, False else: sqlMsg = 'Executed SQL: {}.'.format(self.sql) msg = '{} succ'.format(self.ops) log1 = logs(msg) log1.mgt() conn.commit() log2 = logs(sqlMsg) log2.mgt() return msg, True except Exception as e: conn.rollback() msg = str(e) log = logs(msg) log.mgt() return msg, False finally: msg = '[{}] MySQL disconnect.'.format(self.ops) log = logs(msg) log.mgt() cur.close() conn.close()
def delTemp(self): ok = self.leng('deltemp', 2) if ok: tempDict = self.temp() try: userID = self.args[1] if userID.find('stu') == -1: delUserID = 'stu' + str(int(userID)) else: delUserID = userID except Exception as e: mes = str(e) log = logs(mes) log.mgt() print(mes) else: if tempDict.__contains__(delUserID) == True: tempDict.pop(delUserID) mes = '用户ID {} 从队列中删除成功.'.format(delUserID) log = logs(mes) log.mgt() print(mes) else: mes = '用户ID {} 不存在.'.format(delUserID) log = logs(mes) log.mgt() print(mes)
def temp(self): if len(self.args) >= 3 and len(self.args) <= 7: # 用户输入的长度符合条件才允许进入 stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail, ok, rmeg = self.checks( ) if stuId != 'None': if ok == True: if tempDict.__contains__(stuId) == False: tempDict[stuId] = {} tempDict[stuId]['name'] = stuName tempDict[stuId]['Phone'] = stuPhone tempDict[stuId]['Company'] = stuCompany tempDict[stuId]['Address'] = stuAddress tempDict[stuId]['Email'] = stuEmail x.add_row([ stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail ]) mes = '用户信息 {} 已加入队列.'.format(stuName) log = logs(mes) log.mgt() print(mes + '\n') else: mes = '该用户ID {} 已存在.添加失败.'.format(stuId) log = logs(mes) log.mgt() print(mes) else: print(rmeg) return tempDict
def load(self): sql = '''select * from yusers;''' try: retdata = db('select', sql) stuList, ok = retdata.select() except Exception as e: mes = str(e) log = logs(mes) log.mgt() print(mes) mes = 'User Information Loading Failed.' log = logs(mes) log.mgt() stuList = 'None' ok = False else: if stuList == []: mes = "The 'ops.yusers'table is empty,now." log = logs(mes) log.mgt() print(mes) else: mes = 'User Information Loading successfully.' log = logs(mes) log.mgt() ok = True return stuList, mes, ok
def Del(self): ok = self.leng('del', 2) if ok: x.clear_rows() try: userID = self.args[1] if userID.find('stu') == -1: delUserID = 'stu' + str(int(userID)) else: delUserID = userID except Exception as e: mes = str(e) log = logs(mes) log.mgt() print(mes) else: tempUser = [] stuList, mes, ok = self.load() if stuList == []: print('用户ID {} 不存在.'.format(delUserID)) for Row in stuList: tempUser.append(Row['UserID']) if delUserID not in tempUser: mes = '用户ID {} 不存在.'.format(delUserID) log = logs(mes) log.mgt() print(mes) else: for Row in stuList: if Row['UserID'] == delUserID: stuId = delUserID stuName = Row['Name'] stuPhone = Row['Phone'] stuCompany = Row['Company'] stuAddress = Row['Address'] stuEmail = Row['Email'] Sql = '''delete from yusers where UserID = "{}"'''.format( delUserID) x.add_row([ stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail ]) retdata = db('delete', Sql) result, ok = retdata.delete() if not ok: mes = '用户ID {} 删除失败.'.format(delUserID) log = logs(mes) log.mgt() print(mes) else: mes = '用户ID {} 删除成功.'.format(delUserID) log = logs(mes) log.mgt() print(mes)
def checks(self): if len(self.args) < 3: # 判断用户输入的格式长度 mes = '格式输入错误.' log = logs(mes) log.mgt() print(mes) elif len(self.args) >= 3 and len(self.args) <= 7: try: # 输入非 int 类型会报错 stuId = 'stu' + str(int(self.args[1])) except Exception as e: mes = str(e) # try 捕获的内容也写入日志 log = logs(mes) log.mgt() print(mes) stuId = 'None' # try 捕获到异常后,stuId 需要传一个默认值,否则报错 stuName = self.args[2] if len(self.args) == 3: stuPhone, stuCompany, stuAddress, stuEmail, ok, rmes = 'None', 'None', 'None', 'None', True, '' elif len(self.args) == 4: if len(self.args[3]) == 11: # 手机号长度判断 stuPhone, stuCompany, stuAddress, stuEmail, ok, rmes = self.args[ 3], 'None', 'None', 'None', True, '' else: stuPhone, stuCompany, stuAddress, stuEmail, ok = 'None', 'None', 'None', 'None', False rmes = '用户电话号码\033[1;31m长度为11位\033[0m,格式输入有误,请重新输入.' log = logs(rmes) log.mgt() elif len(self.args) == 5: stuPhone, stuCompany, stuAddress, stuEmail, ok, rmes = self.args[ 3], self.args[4], 'None', 'None', True, '' elif len(self.args) == 6: stuPhone, stuCompany, stuAddress, stuEmail, ok, rmes = self.args[ 3], self.args[4], self.args[5], 'None', True, '' elif len(self.args) == 7: if self.args[6].find('@') == -1: # 邮件格式是否包含 @ 判断 stuPhone, stuCompany, stuAddress, stuEmail, ok = self.args[ 3], self.args[4], self.args[5], 'None', False rmes = '用户Email地址\033[1;31m必须包含@\033[0m,格式输入有误,请重新输入.' log = logs(rmes) log.mgt() else: stuPhone, stuCompany, stuAddress, stuEmail, ok, rmes = self.args[ 3], self.args[4], self.args[5], self.args[6], True, '' return stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail, ok, rmes elif len(self.args) > 6: mes = '用户格式输入错误.' log = logs(mes) log.mgt() print(mes)
def search(self): ok = self.leng('search', 2) if ok: x.clear_rows() stuList, mes, ok = self.load() searchID = self.args[1] tempList = [] for Row in stuList: stuID = Row['UserID'] tempList.append(stuID) # 获取数据库内的用户ID if searchID not in tempList: mes = '用户ID {} 不存在.'.format(searchID) log = logs(mes) log.mgt() print(mes) else: for Row in stuList: if Row['UserID'] == searchID: stuId = searchID stuName = Row['Name'] stuPhone = Row['Phone'] stuCompany = Row['Company'] stuAddress = Row['Address'] stuEmail = Row['Email'] x.add_row([ stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail ]) print('用户ID {} 搜索的信息如下: '.format(searchID)) print(x)
def checks(self): conn, mes = self.conns() if not conn: meg = '[{}] MySQL connection failed.'.format(self.ops) log = logs(mes) log.mgt() return meg, False cur = conn.cursor() return cur, conn
def export(self): # 导出用户信息,获取用户是否输入导出的文件名 if len(self.args) == 1: exportCsv = 'StuInfo.csv' # 用户未输入导出的文件名,这是默认值 else: csvName = ''.join(self.args[1]) if csvName.find('.csv') == -1: # 用户输入的文件名不包含csv,则自动补上 exportCsv = csvName + '.csv' else: exportCsv = csvName # 用户输入的文件名格式符合条件 stuList, mes, ok = self.load() if ok == True: try: with open(exportCsv, 'w') as csvfile: writeCsv = csv.writer(csvfile) writeCsv.writerow([ "UserID", "Name", "Phone", "Company", "Address", "Email" ]) # 先写入columns_name for Row in stuList: stuId = Row['UserID'] stuName = Row['Name'] stuPhone = Row['Phone'] stuCompany = Row['Company'] stuAddress = Row['Address'] stuEmail = Row['Email'] writeCsv.writerow([ stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail ]) except Exception as e: mes = str(e) log = logs(mes) log.mgt() print(mes) else: mes = '用户信息导出到 \033[31;1m{}\033[0m 成功'.format(exportCsv) log = logs(mes) log.mgt() print(mes) else: # 如数据库内容为空 mes = '\033[31;1m导出失败.\033[0m' log = logs(mes) log.mgt() print(mes)
def leng(self, condition, length): #指令长度判断 cond = condition.strip().lower() if cond == 'deltemp' or cond == 'del' or cond == 'save' or cond == 'search': if len(self.args) != length: mes = '指令输入错误.' log = logs(mes) log.mgt() print(mes) return False else: return True if cond == 'update': if len(self.args) < length: mes = '指令输入错误.' log = logs(mes) log.mgt() print(mes) return False else: return True
def login(self): # 从配置文件中读取登录验证的用户密码 retdata = configs('config.ini', 'Login', 'username') result = configs('config.ini', 'Login', 'password') username, ok = retdata.read() password, ok = result.read() if self.inUsername == username and self.inPassword == password: mes = 'username: {}, password: {}, Login succ.'.format( self.inUsername, self.inPassword) log = logs(mes) log.mgt() print('Login succ.') return True # 返回当前状态 else: mes = 'username: {}, password: {}, Login failed.'.format( self.inUsername, self.inPassword) log = logs(mes) log.mgt() print('Login failed.') return False
def read(self): if not os.path.exists(self.filename): # 判断配置文件是否存在 msg = '{} 配置文件丢失, 程序退出.'.format(self.filename) log = logs(msg) log.mgt() sys.exit(msg) # 读取配置文件 config = configparser.ConfigParser() config.read(self.filename) if not config.sections(): msg = '{} configuration init is empty'.format(self.filename) log = logs(msg) log.mgt() return msg, False if self.key: if self.section in config.sections(): msg = 'configuration file {} {} {} loaded successfully.'.format(self.filename,self.section,self.key) log = logs(msg) log.mgt() return dict(config[self.section])[self.key], True else: msg = 'configuration file {} format error.'.format(self.filename) log = logs(msg) log.mgt() return msg, False else: msg = 'ConfigMgt has no key parameter.' log = logs(msg) log.mgt() return dict(config[self.section]), True # Config = config('config.ini','Login','username') # print(Config.read())
def ops(self): while True: info = input('请: ').strip().lower().split(' ') command = info[0] if command == 'add': retdata = users(info) retdata.add() elif command == 'list': retdata = users(info) retdata.list() elif command == 'deltemp': retdata = users(info) retdata.delTemp() elif command == 'load': retdata = users('') retdata.load() elif command == 'del': retdata = users(info) retdata.Del() elif command == 'save': retdata = users(info) retdata.save() elif command == 'update': retdata = users(info) retdata.update() elif command == 'search': retdata = users(info) retdata.search() elif command == 'display': retdata = users(info) retdata.display() elif command == 'export': retdata = users(info) retdata.export() elif command == 'exit': retdata = auth('', '') retdata.logout() elif command == 'help': print(helpMenu) else: mes = '输入指令错误,请输入 help 查看帮助.' log = logs(mes) log.mgt() print(mes)
def entry(self): UCount = 0 while True: UCount += 1 if UCount > 3: # 用户密码次数校验 mes = '用户名或密码输入次数大于3次,程序退出.' log = logs(mes) log.mgt() sys.exit(mes) else: inUser = input('Username: '******'Password: '******'')
def update(self): ok = self.leng('save', 6) if ok: x.clear_rows() updateID = self.args[1] ops = self.args[3].strip().lower() value = self.args[5].strip().lower() if self.args[2].strip().lower() != 'set' and self.args[4].strip( ).lower() != '=': # 格式判断,格式为: update stu1 set Phone = 13288888888 mes = '指令输入错误.' log = logs(mes) log.mgt() print(mes) else: if ops != 'userid' and ops != 'name' and ops != 'phone' and ops != 'company' and ops != 'address' and ops != 'email': mes = '指令输出错误.不包含关键字参数 UserID,Name,Phone,Company,Address,Email.' log = logs(mes) log.mgt() print(mes) else: tempUser = [] stuList, mes, ok = self.load() # 读取数据库用户信息 for Row in stuList: tempUser.append(Row['UserID']) if updateID not in tempUser: mes = '用户ID {} 不存在.'.format(updateID) log = logs(mes) log.mgt() print(mes) else: for Row in stuList: if Row['UserID'] == updateID: stuId = updateID stuName = Row['Name'] stuPhone = Row['Phone'] stuCompany = Row['Company'] stuAddress = Row['Address'] stuEmail = Row['Email'] x.add_row([ stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail ]) sql = '''update yusers set {} = "{}" where UserID = "{}";'''.format( ops, value, stuId) retdata = db('update', sql) stuList, ok = retdata.update() if not ok: mes = '用户ID {} 更新失败.'.format(updateID) log = logs(mes) log.mgt() print(mes) else: print('更新前的用户信息: ') print(x) x.clear_rows() if ops == 'userid': x.add_row([ value, stuName, stuPhone, stuCompany, stuAddress, stuEmail ]) elif ops == 'name': x.add_row([ updateID, value, stuPhone, stuCompany, stuAddress, stuEmail ]) elif ops == 'phone': x.add_row([ updateID, stuName, value, stuCompany, stuAddress, stuEmail ]) elif ops == 'company': x.add_row([ updateID, stuName, stuPhone, value, stuAddress, stuEmail ]) elif ops == 'address': x.add_row([ updateID, stuName, stuPhone, stuCompany, value, stuEmail ]) elif ops == 'email': x.add_row([ updateID, stuName, stuPhone, stuCompany, stuAddress, value ]) mes = '用户ID {} 更新成功.'.format(updateID) log = logs(mes) log.mgt() print('\n' + mes + ' 更新后的用户信息如下: ') print(x)
def display(self): x.clear_rows() stuList, mes, ok = self.load() cnt = len(stuList) # 获取数据库中用户信息的数量 mes = '当前一共有 {} 条用户数据.'.format(cnt) log = logs(mes) log.mgt() print(mes + '\n') if ok: # 成功读取本地用户文件后返回的状态码 for Row in stuList: stuId = Row['UserID'] stuName = Row['Name'] stuPhone = Row['Phone'] stuCompany = Row['Company'] stuAddress = Row['Address'] stuEmail = Row['Email'] x.add_row([ stuId, stuName, stuPhone, stuCompany, stuAddress, stuEmail ]) # 设置显示属性,_Linfo为列表 if len(self.args) == 1: page, page1, item = 1, 0, 5 # 设置页脚数和每页显示行数 mes = '当前第 {} 页, 从 {} 行开始读取, 每页显示 {} 行'.format( page, page1, item) log = logs(mes) log.mgt() print(mes) elif len(self.args) == 3: page, item, page1 = int(self.args[1]), int(self.args[2]), int( self.args[2]) mes = ('当前第 {} 页, 从 {} 行开始读取, 每页显示 {} 行'.format( page, page1, item)) log = logs(mes) log.mgt() print(mes) if page * item > cnt: mes = '当前查询的数据超过当前用户总数,将显示最后 {} 条用户数据'.format(self.args[2]) print(mes) Page, Page1 = int(round( cnt / item)), int(cnt - int(self.args[2])) mes = '当前第 {} 页, 从 {} 行开始读取, 每页显示 {} 行'.format( Page, Page1, item) log = logs(mes) log.mgt() print(mes) else: mes = '格式输入错误.' log = logs(mes) log.mgt() print(mes) for i in range(0, cnt): Page2 = int(page1 + item) # 设置起始页和结束页 # print(_Page1,_Page2) try: print(x.get_string(start=page1, end=Page2)) # 分页显示用户信息 except Exception as e: mes = '请输出正确的查询格式.eg: display 4 1' log = logs(mes) log.mgt() print(mes) else: mes = '当前第 {} 页, 一共 {} 页.'.format( page, int(math.ceil(cnt / item))) # 设置当前页和总页数 log = logs(mes) log.mgt() print(mes + '\n') page1 = int(Page2) i += item page += 1 if page1 >= cnt and page >= int(round( cnt / item)): # 判断当前页是否大于等于总页数 mes = '到底了.' log = logs(mes) log.mgt() print(mes) break mes = '请按回车翻页: ' log = logs(mes) log.mgt() input(mes) else: # 数据库内容为空等 mes = '\033[31;1m显示出错.\033[0m' log = logs(mes) log.mgt() print(mes)
def logout(self): x.clear_rows() mes = 'User Management System Exit Successfully.' log = logs(mes) # 记录日志 log.mgt() sys.exit(mes)