class ControladorDB: """ Implementación de Controlador de Usuario, correspondiente a una persistencia de DB. El acceso a una DB se hace desde un controlador dbApi, quien hace de intermediaria para tomar los datos y convertirlo en objetos. """ def __init__(self): self.dbApi = DBApi() self.keyUser = DEFAULT self.keyState = 0 self.pattern = re.compile('^(\w+)$') def login(self, jobt): match = self.pattern.match(jobt['nick']) if match == None: return False usr = self.dbApi.getUserByNick(jobt['nick']) # TODO --Falta de implementación-- try: if not usr: return False if usr.getPasswd() == jobt['passwd']: return True else: return False except: return False def setKeyUser(self, jobt): if jobt: self.keyUser = jobt['nick'] else: self.keyUser = DEFAULT self.keyState = 0 def getKeyUser(self): return self.keyUser def isKeyUser(self): return not self.keyUser == DEFAULT def sameUser(self, jobj): return self.keyUser == jobj['nick'] def newUser(self, nick, passwd): # TODO --Revisar-- return self.dbApi.newUser(nick, passwd) def setKeyState(self, jobj): if jobj['nick'] != self.keyUser: return False if not isinstance(jobj['estado'], int) or not jobj['estado'] in state_range: return False else: self.keyState = jobj['estado'] return True def getKeyState(self): return self.keyState
class ControladorDB: """ Implementación de Controlador de Usuario, correspondiente a una persistencia de DB. El acceso a una DB se hace desde un controlador dbApi, quien hace de intermediaria para tomar los datos y convertirlo en objetos. """ def __init__(self): self.dbApi = DBApi() self.keyUser = DEFAULT self.keyState = 0 self.pattern = re.compile('^(\w+)$') def login(self,jobt): match = self.pattern.match(jobt['nick']) if match == None: return False usr = self.dbApi.getUserByNick(jobt['nick']) # TODO --Falta de implementación-- try: if not usr: return False if usr.getPasswd() == jobt['passwd']: return True else: return False except: return False def setKeyUser(self,jobt): if jobt: self.keyUser = jobt['nick'] else : self.keyUser = DEFAULT self.keyState = 0 def getKeyUser(self): return self.keyUser def isKeyUser(self): return not self.keyUser == DEFAULT def sameUser(self,jobj): return self.keyUser == jobj['nick'] def newUser(self,nick,passwd): # TODO --Revisar-- return self.dbApi.newUser(nick,passwd) def setKeyState(self,jobj): if jobj['nick'] != self.keyUser: return False if not isinstance(jobj['estado'],int) or not jobj['estado'] in state_range: return False else : self.keyState = jobj['estado'] return True def getKeyState(self): return self.keyState
def listAttributeNameOfTable(self, db, tablename): attributes = [] str_sql = "DESC %s" % tablename dbService = DBApi(db) tableStructure = dbService.listDataBySQL(str_sql) for sturcture in tableStructure: attributes.append(sturcture[0]) #print(attributes) return attributes
def listStructureByTablename(self, db, tablename): ''' 根据表名获取该表的表结构 :param db:数据库信息,dbtype,user/password@ip:port/databases :param tablename:表名 :return:表结构列表 ''' str_sql = "DESC %s" % tablename #str_sql = "select ordinal_position,colume_name,data_type from information_schema.'columns' where table_name ='%s' order by ordinal_position" % tablename #str_sql = "SELECT ORDINAL_POSITION,COLUME_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.'COLUMNS' WHERE TABLE_NAME ='%S' ORDER BY ORDINAL_POSITION" % tablename dbService = DBApi(db) tableStructure = dbService.listDataBySQL(str_sql) return tableStructure
def listTablesInDb(self, db): ''' 获取数据库用户表属性信息 :param db:dbtype,user/password@ip:port/databases :return:表名列表 ''' tables = [] str_sql = "show tables" dbService = DBApi(db) list_table = dbService.listDataBySQL(str_sql) for table in list_table: tables.append(table[0]) return tables
def listStructureByTablenameAsDict(self, db, tablename): ''' 根据表名获取该表的表结构 :param db:数据库信息 :param tablename:表名 :return:表结构字典,{字段名:字段信息} ''' tableStructureAsDict = {} str_sql = "DESC %s" % tablename dbService = DBApi(db) tableStructure = dbService.listDataBySQL(str_sql) for sturcture in tableStructure: tableStructureAsDict[sturcture[0]] = sturcture #print(tableStructureAsDict) return tableStructureAsDict
def assertTabledataBetweenDbByAttribute( self, referdb="mysql,root/[email protected]:50016/pdw", targetdb="mysql,root/[email protected]:50016/pdw", tables="", attribute="period", attr_value="201705"): ''' 根据table 的 att 字段 获取数据,比较在referdb 与targetdb 中的数据是否一致 :param referdb: :param targetdb: :param attribute: :param attr_value: :return: ''' # 验证表在数据库中是否存在 str_tables_ref = self.assertTablesInDb(referdb, tables) if len(str_tables_ref) == 0: print("referanceDb:%s has no tables:%s" % (referdb, tables)) return str_tables_target = self.assertTablesInDb(targetdb, str_tables_ref) if len(str_tables_target) == 0: print("targetDb:%s has no tables:%s" % (targetdb, str_tables_target)) return #验证 list_table = str_tables_target.split(",") bool_rs = True if len(list_table) > 0: for tableName in list_table: str_sql = "SELECT * FROM %s t WHERE t.%s = '%s'" % ( tableName, attribute, attr_value) referdbService = DBApi(referdb) rs1 = referdbService.listDataBySQL(str_sql) targetdbService = DBApi(targetdb) rs2 = targetdbService.listDataBySQL(str_sql) if len(rs1) != len(rs2): print( "*ERROR* Table %-34s %s:%s miss marched! excepted:%d rows, actural:%d rows" % (tableName, attribute, attr_value, len(rs1), len(rs2))) bool_rs = False else: sum_rows = len(rs1) for row in range(sum_rows): if rs1[row] not in rs2: print( "*ERROR* Table %s %s:%s miss marched!\nLine %d miss data:%s\n" % (tableName, attribute, attr_value, row, rs1[row])) bool_rs = False if bool_rs: print("*INFO* table %s-34s %s:%s rows:%-5d pass" % (tableName, attribute, attr_value, sum_rows)) else: print("*ERROR* Empty tables!") bool_rs = False if not bool_rs: self.fail()
def assertTabledataBetweenDb(self, referdb, targetdb, tables): ''' 比较两个数据库中的所有数据 :param referdb: dbtype,user/password@ip:port/databases :param targetdb: dbtype,user/password@ip:port/databases :param tables: tablename or tablename1,tablename2,tablename3 :return: 示例: db:"mysql,root/[email protected]:50016/pdw" tables:"BUSI_REALIZABLE_ASSET, BUSI_HOUSE_DETAIL" ''' print("hell") msg = [] msg_single_tabledata = [] bool_db = True # 验证需要验证的表在数据库中是否存在,表结构是否一致 marched_tables_str, msg_structure = self.assertTableStructureBetweenDb( referdb, targetdb, tables) if marched_tables_str != tables: bool_db = False msg.append(msg_structure) #验证表数据 if len(marched_tables_str) > 0: msg.append( "*****************************data detail*********************************" ) list_tables = marched_tables_str.split(",") data_marched_tables = [] data_unmarched_tables = [] #断言数据 for tableName in list_tables: msg_single_tabledata.append("\n*********%s*********" % (tableName)) bool_table = True print("tablename:", tableName) str_sql = "SELECT * FROM %s" % tableName referdbService = DBApi(referdb) rs1 = referdbService.listDataBySQL(str_sql) targetdbService = DBApi(targetdb) rs2 = targetdbService.listDataBySQL(str_sql) if len(rs1) != len(rs2): # print("数据条数不一致!参照数据库:%d条,目标数据库:%d条" %(len(rs1),len(rs2))) msg_single_tabledata.append( "Data count not marched!referdb:%d,targetdb:%d" % (len(rs1), len(rs2))) bool_db = False bool_table = False #获取目标表中新增数据信息: data_add = [] for row in range(0, len(rs2)): if rs2[row] not in rs1: data_add.append(rs2[row]) if len(data_add) > 0: bool_db = False bool_table = False data_add_list = [] for data in data_add: data_add_list.append(str(data)) # msg_single_tabledata.append("data added:") # msg_single_tabledata.append("\n".join(data_add_list)) #获取目标表中减少的数据 data_del = [] for row in range(0, len(rs1)): if rs1[row] not in rs2: data_del.append(rs1[row]) if len(data_del) > 0: bool_db = False bool_table = False data_del_list = [] for data in data_del: data_del_list.append(str(data)) # msg_single_tabledata.append("data deleted:") # msg_single_tabledata.append("\n".join(data_del_list)) #获取数据一致的表 if bool_table: data_marched_tables.append(tableName) else: data_unmarched_tables.append(tableName) if bool_db: print("*INFO* DB data marched!") else: msg.append("DB data not marched") if len(data_marched_tables) > 0: msg.append("data marched tables:%s" % (",".join(data_marched_tables))) if len(data_unmarched_tables) > 0: msg.append("data not marched tables:%s" % (",".join(data_unmarched_tables))) msg.append( "***************************details**************************************" ) # msg.append("\n".join(msg_single_tabledata)) print("*ERROR* %s" % ("\n".join(msg))) else: print("*ERROR* DB data not marched!") # msg.append("两个数据库中表结构不一致!") return "\n".join(msg)
def __init__(self): self.dbApi = DBApi() self.keyUser = DEFAULT self.keyState = 0 self.pattern = re.compile('^(\w+)$')
def __init__(self, db_name): self.user = None self.userRole = None self.functions = None self.quitProgram = False self.api = DBApi(db_name)
class Main(): def __init__(self, db_name): self.user = None self.userRole = None self.functions = None self.quitProgram = False self.api = DBApi(db_name) def setup(self): """ Load and intialize data and databases """ self.api.loadData() def execute(self, userIn): """ Exectute a user instruction. Format should be COMMAND [ARGUEMENTS] """ args = userIn.split()[1:] cmd = userIn.split()[0] if not self.user: raise Exception("User not logged in! Killing program") elif cmd not in self.functions.keys(): print("Invalid command") else: # process input try: if len(args) > 0: self.functions[cmd](args) else: self.functions[cmd]() except Exception as e: print(str(e)) def help(self): """ Print a list of commands for the avaliable user """ for cmd in HELP_COMMANDS: if cmd in self.functions: print("{} -> {}".format(cmd, HELP_COMMANDS[cmd])) def login(self, uid, pwd): """ Log in a user """ user = self.api.getUser(uid, pwd) if user: print("Logging in...") # assign user object and role self.user = user self.userRole = user[2] # build set of avaible functions based on user roles self.functions = {x: getattr(self, x[1:]) for x in ROLES[self.userRole]} else: raise Exception("Invalid user id or password!") def logout(self): """ Log out current user """ print("Logging out...") self.user = self.userRole = self.functions = None def register(self, args): """ Register an object, either a marriage or a birth """ if args[0] == "-b" and len(args) == 10: fname = args[1] lname = args[2] gender = args[3] bdate = args[4] bplace = args[5] mFname = args[6] mLname = args[7] fFname = args[8] fLname = args[9] regno = randint(0, 999999) regdate = str(dt.now()) regplace = self.user[5] # TODO: Make a user class so we dont have to directly access a tuple mother = self.api.getPerson(mFname, mLname) father = self.api.getPerson(fFname, fLname) if not mother: # get information about the mother from the user print("No entry for mother. Please enter the following information") # enter into persons db self.api.createPerson(mFname, mLname) if not father: # get information about the mother from the user print("No entry for father. Please enter the following information") # enter into persons db self.api.createPerson(fFname, fLname) # enter new person into births and persons if not self.api.getPerson(fname, lname): # enter Mom's phone and address for the newborn values = self.api.executeQuery( "SELECT address, phone FROM persons WHERE fname = ? AND lname = ?", (mFname, mLname) ) self.api.executeQuery( """ INSERT INTO persons (fname, lname, bdate, bplace, address, phone) VALUES (?,?,?,?,?,?) """, (fname, lname, bdate, bplace, values[0], values[1]) ) self.api.executeQuery( """ INSERT INTO births (regno, fname, lname, regdate, regplace, gender, f_fname, f_lname, m_fname, m_lname) VALUES (?,?,?,?,?,?,?,?,?,?) """, ( regno, fname, lname, regdate, regplace, gender, fFname, fLname, mFname, mLname ) ) print("added new birth to database") elif args[0] == "-m" and len(args) == 5: p1Fname = args[1] p1Lname = args[2] p2Fname = args[3] p2Lname = args[4] regno = randint(0, 999999) regdate = dt.now() regplace = self.user[5] p1 = self.api.getPerson(p1Fname, p1Lname) p2 = self.api.getPerson(p2Fname, p2Lname) if not p1: # get p1 from user print("Cannot find partner 1") self.api.getPerson(p1Fname, p1Lname) if not p2: # get p2 from user print("Cannot find partner 2") self.api.getPerson(p2Fname, p2Lname) # enter marriage self.api.executeQuery( """ INSERT INTO marriages (regdate, regplace, p1_fname, p1_lname, p2_fname, p2_lname) VALUES (?,?,?,?,?,?) """, ( regdate, regplace, p1Fname, p1Lname, p2Fname, p2Lname ) ) print("added new marriage to database") else: raise Exception("Missing Argument(s)") def renew(self, args): """ Renew an object """ if len(args) == 1: regno = args[0] # get the registration from the db registration = self.api.executeQuery( "SELECT * FROM registrations WHERE regno = ?", (regno,) ) if registration: currentExpiry = dt.strptime(registration[2], "%Y-%m-%d") # TODO: Parse Correctly newExpiry = currentExpiry + datetime.timedelta(days=(365)) if currentExpiry <= dt.now(): newExpiry = dt.now() + datetime.timedelta(days=(365)) self.api.executeQuery( "UPDATE registrations SET expiry = ? WHERE regno = ?", (newExpiry, regno), True ) print("updated registration") else: raise Exception("Missing Argument(s)") def process(self, args): """ Process an object """ if args[0] == "-s": # TODO: Finish error handling vin = args[1] currentOwnerFname = args[2] currentOwnerLname = args[3] newOwnerFname = args[4] newOwnerLname = args[5] plate = args[6] # check if current owner matches most recent owner of the car prevRegistraion = self.api.executeQuery( """ SELECT * FROM vehicles v, registrations r WHERE r.vin = v.vin AND v.vin = ? AND r.fname = ? and r.lname = ? AND r.expiry > ? """, (vin, currentOwnerFname, currentOwnerLname, dt.now()) ) if not prevRegistraion: raise Exception("Transaction cannot be processed. Current owner does not match registered.") # set the expiry date of the current registration regnoOld = self.api.executeQuery( """ SELECT regno FROM registrations r, vehicles v WHERE WHERE r.vin = v.vin AND r.fname = ? and r.lname = ? """, (currentOwnerFname, currentOwnerLname) ) newExpiry = dt.now() self.api.executeQuery( "UPDATE registrations SET expiry = ? WHERE regno = ?", (newExpiry, regnoOld), True ) # set the registration and expiry date of the new registration regno = randint(0, 999999) regdate = dt.now() expiry = dt.now() + datetime.timedelta(days=365) # create new registration in the db self.api.executeQuery( """ INSERT INTO registrations (regno, regdate, expiry, plate, vin, fname, lname) VALUES (?,?,?,?,?,?,?) """, (regno, regdate, expiry, plate, vin, newOwnerFname, newOwnerLname), True ) print("completed processing bill of sale") # Process a payment elif args[0] == "-p": tno = args[1] amount = args[2] fine = args[3] pdate = dt.now() # calculate the amount paid for a given ticket amountPaid = self.api.executeQuery( """ SELECT SUM(amount) FROM payments p, tickets t WHERE p.tno = t.tno AND p.date < dt.now() """ ) # accept payment if amountPaid + payment Amount <= fine, otherwise raise error totalAmount = amountPaid + amount if fine >= totalAmount: # Update fine amount for the ticket fineRemaining = fine - totalAmount self.api.executeQuery( "UPDATE tickets SET fine = ? WHERE tno = ?", (fineRemaining, tno), True ) # Insert amount into payments table self.api.executeQuery( """ INSERT INTO payments (tno, pdate, amount) VALUES (?,?,?) """, (tno, pdate, amount), True ) else: raise Exception("Amount paid exceeds ticket fine") else: raise Exception("Missing Argument(s)") def getAbstract(self, args): # get the person's name from the user and validate it personInDB = False while not personInDB: fname = input("Enter first name of the person: ").strip() if fname == "": return lname = input("Enter last name of the person: ").strip() if lname == "": return # get person person = api.getPerson(fname, lname) if person == None: print("Invalid person entry")