def readNovena(): wb = xlrd.open_workbook(filename = './data/novena.xls') sheet = wb.sheet_by_index(0) max_rows = sheet.nrows col_b = 1 #column 'B' col_g = 6 #column 'G' bar = progressbar.ProgressBar(maxval=max_rows, widgets=[ progressbar.Bar(left='novena Progress: [', marker='*', right=']')]).start() for index in range(max_rows): row = sheet.row_values(index) name = row[col_b] price = row[col_g] if (name != None) and (price not in (None, 'Цена', 'Цена3', 'Грин', 'Дилер', ' ', '')): record = Competitor(comp_name='Novena', name=name, low_price=float(price)) db.session.add(record) db.session.commit() bar.update(index) bar.finish()
def readGreens(): wb = xlrd.open_workbook(filename = './data/greenspark.xlsx') sheet = wb.sheet_by_index(0) max_rows = sheet.nrows col_a = 0 #column 'A' col_p = 15 #column 'P' bar = progressbar.ProgressBar(maxval=max_rows, widgets=[ progressbar.Bar(left='Green Progress: [', marker='*', right=']')]).start() for index in range(max_rows): row = sheet.row_values(index) name = row[col_a] price = row[col_p] if (name != None) and (price not in (None, 'Цена', 'Грин', 'Дилер', ' ', '')): record = Competitor(comp_name='GreenSpark', name=name, low_price=float(price)) db.session.add(record) db.session.commit() bar.update(index) bar.finish()
def loadAndUpdate(path,file_name): fullpath = os.path.join(path, file_name) base=os.path.basename(fullpath) wb = xlrd.open_workbook(fullpath) file_name = os.path.splitext(base)[0] sheet = wb.sheet_by_index(0) max_rows = sheet.nrows col_a = 0 #column 'C' col_b = 1 #column 'F' if file_name == 'AppleHelp.ru': for index in range(max_rows): row = sheet.row_values(index) name = row[col_a] price = row[col_b] try: record = MainPrice.query.filter_by(name=name).first() if record is not None: rows = MainPrice.query.filter_by(name=name).update({'low_price': float(price)}) else: if (name != None) and (price not in (None, 'ОПТ 3', 'Цена', 'Грин', 'Дилер', ' ', '')): rows = MainPrice(name=name, low_price=float(price)) db.session.add(rows) db.session.commit() except: db.session.rollback() else: for index in range(max_rows): row = sheet.row_values(index) name = row[col_a] price = row[col_b] try: record = Competitor.query.filter_by(comp_name=file_name,name=name).first() if record is not None: rows = Competitor.query.filter_by(name = name).update({'low_price': float(price)}) else: if (name != None) and (price not in (None, 'ОПТ 3', 'Цена', 'Грин', 'Дилер', ' ', '')): rows = Competitor(comp_name=file_name, name=name, low_price=float(price)) db.session.add(rows) db.session.commit() except: db.session.rollback() # if (name != None) and (price not in (None, 'Цена', 'Грин', 'Дилер', ' ', '')): # record = MainPrice(name=name, # low_price=float(price)) # db.session.add(record) # db.session.commit() return ''
def update_model(cleanse=True): data = UsersSheet.get_all_users() if not data: pass else: stored_in_sheet_records = set() row_num = 1 for row in data: existing_data = Competitor.objects( name=row[1], legacy_number=row[0] ).first() if existing_data is None: cfg = get_config() new_data = Competitor( legacy_number=row[0], name=row[1], status=COMPETITOR_STATUS.UNAUTHORIZED, level=to_int(row[3], None), matches=to_int(row[4]), wins=to_int(row[5]), losses=to_int(row[6]), performance=to_int(row[7]), used_vacation_time=0 ) new_data.save() new_data.reload() stored_in_sheet_records.add(new_data.id) else: stored_in_sheet_records.add(existing_data.id) UsersSheet.update_competitor_db_record(row, existing_data) row_num += 1 for new_record in Competitor.objects(id__not__in=stored_in_sheet_records): if not cleanse: row_num += 1 UsersSheet.insert_competitor_in_table(new_record, at_row=row_num) else: new_record.delete() hr_logger.info('Оновлено список гравців з гугл-таблиці')
def readDici(): #Парсинг прайса Диси wb = xlrd.open_workbook(filename = './data/dici.xlsx') for sheet_num in range(0, 5): sheet = wb.sheet_by_index(sheet_num) max_rows = sheet.nrows col_a = 0 #column 'A' col_b = 1 #column 'B' col_d = 3 #column 'D' col_e = 4 #column 'E' bar = progressbar.ProgressBar(maxval=max_rows, widgets=[ progressbar.Bar(left='Dici Progress: [', marker='*', right=']')]).start() for index in range(max_rows): row = sheet.row_values(index) if sheet_num == 0: name = row[col_a] + row[col_b] price = row[col_e] else: name = row[col_a] price = row[col_d] if (name != None) and (price not in (None, 'ОПТ 3', 'Цена', 'Грин', 'Дилер', ' ', '')): record = Competitor(comp_name='Dici', name=name, low_price=float(price)) db.session.add(record) db.session.commit() bar.update(index) bar.finish()