def dump_program_reports(): print('Running program report dump...') dataset = Dataset() dataset.headers = ['Site ID', 'Mobile', 'Timestamp', 'Group', 'Program', 'Period code', 'Period number', 'Atot', 'Arel', 'Tin', 'Tout', 'Dead', 'DefT', 'Dcur', 'Dmed'] for report in ProgramReport.objects.select_related('group', 'program').order_by('created'): if not report.reporter.mobile.startswith('+'): continue dataset.append([ report.site.hcid, report.reporter.mobile, timegm(report.created.utctimetuple()), report.group.code, report.program.code, report.period_code, report.period_number, report.new_marasmic_patients, report.readmitted_patients, report.patients_transferred_in, report.patients_transferred_out, report.patient_deaths, report.unconfirmed_patient_defaults, report.patients_cured, report.unresponsive_patients ]) with open('program_reports.csv', 'w') as f: f.write(dataset.csv) print('Done')
def export(request): contacts_resource = ExportResource() dataset = contacts_resource.export() #convert Dataset to List my_list = [] for i in dataset: my_list.append(list(i)) #Replace blank to the ID of the user count = 0 for x in my_list: my_list[count][0] = '' my_list[count][1] = request.user count = count + 1 #creating new dataset then add Headers my_data = Dataset() my_data.headers = ([ 'id', 'created_by', 'first_name', 'last_name', 'contact_number', 'address' ]) for x in my_list: my_data.append(x) response = HttpResponse(my_data.csv, content_type='text/csv') response['Content-Disposition'] = 'attachment; filename="contacts.csv"' return response
def member_query(db): node_id = request.params.get('node_id') realname = request.params.get('realname') idcard = request.params.get('idcard') mobile = request.params.get('mobile') _query = db.query( models.SlcMember, models.SlcNode.node_name ).filter( models.SlcNode.id == models.SlcMember.node_id ) if idcard: _query = _query.filter(models.SlcMember.idcard==idcard) if mobile: _query = _query.filter(models.SlcMember.mobile==mobile) if node_id: _query = _query.filter(models.SlcMember.node_id == node_id) if realname: _query = _query.filter(models.SlcMember.realname.like('%'+realname+'%')) if request.path == '/member': return render("bus_member_list", page_data = get_page_data(_query), node_list=db.query(models.SlcNode),**request.params) elif request.path == "/member/export": data = Dataset() data.append((u'区域',u'姓名',u'用户名',u'证件号',u'邮箱', u'联系电话', u'地址', u'创建时间')) for i,_node_name in _query: data.append(( _node_name, i.realname, i.member_name,i.idcard, i.email,i.mobile, i.address,i.create_time )) name = u"RADIUS-MEMBER-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls" with open(u'./static/xls/%s' % name, 'wb') as f: f.write(data.xls) return static_file(name, root='./static/xls',download=True)
def exportDivSchedulesRefFormat(self, startgameday, prefix=""): headers = ['Game#', 'Game#', 'Tourn Match#','Date', 'Day', 'Time', 'Division', 'Round', 'Home', 'Visitor', 'Field', 'cr_trust', 'ar_trust', 'm_trust'] datasheet = Dataset(title=prefix) datasheet.headers = list(headers) schedule_list = self.dbinterface.findDivisionSchedulePHMSARefFormat(startgameday) tabformat_list = [(_offset+x[match_id_CONST], x[match_id_CONST], tournMapGamedayIdToCalendar(x[gameday_id_CONST]), tournMapGamedayIdToDate(x[gameday_id_CONST]), datetime.strptime(x[start_time_CONST],"%H:%M").strftime("%I:%M %p"), x[age_CONST]+x[gen_CONST], x[round_CONST], x[home_CONST], x[away_CONST], self.fieldinfo[self.findexerGet(x[venue_CONST])]['name'], _reftrust_level[_rindexerGet(getTournDivID(x[age_CONST], x[gen_CONST]))]['cr'], _reftrust_level[_rindexerGet(getTournDivID(x[age_CONST], x[gen_CONST]))]['ar'], _reftrust_level[_rindexerGet(getTournDivID(x[age_CONST], x[gen_CONST]))]['ment']) for x in schedule_list] if prefix else [(mapGamedayIdToCalendar(x[gameday_id_CONST],format=1), 'Saturday', datetime.strptime(x[start_time_CONST],"%H:%M").strftime("%I:%M %p"), x[age_CONST]+x[gen_CONST], x[home_CONST], x[away_CONST], self.fieldinfo[self.findexerGet(x[venue_CONST])]['name']) for x in schedule_list] if prefix: atabformat_list = [(_offset+i, j[0], j[1], j[2], j[3], j[4], j[5], j[6], j[7], j[8], j[9], j[10], j[11], j[12]) for i,j in enumerate(tabformat_list)] else: atabformat_list = tabformat_list for tabformat in atabformat_list: datasheet.append(tabformat) sheet_xls_relpath = prefix+'_RefFormat.xls' sheet_xls_abspath = os.path.join('/home/henry/workspace/datagraph/bottle_baseball/download/xls', sheet_xls_relpath) with open(sheet_xls_abspath,'wb') as f: f.write(datasheet.xls) f.close()
def generate_divxls(self, genxls_id): headers = [ 'Game Date', 'Day', 'Time', 'Division', 'Home', 'Visitor', 'Venue' ] datasheet_list = list() for divinfo in self.divinfo_list: div_id = divinfo[genxls_id] div_age = divinfo['div_age'] div_gen = divinfo['div_gen'] div_str = div_age + div_gen datasheet = Dataset(title=div_str) datasheet.headers = list(headers) match_list = self.sdbinterface.get_schedule(genxls_id, div_age=div_age, div_gen=div_gen) # note conversions for time from 24-hour to am/pm format tabformat_list = [ (x['game_date'], parser.parse(x['game_date']).strftime("%a"), datetime.strptime(x['start_time'], "%H:%M").strftime("%I:%M%p"), div_str, y['home'], y['away'], self.fieldinfo_list[self.findexerGet( y['venue'])]['field_name']) for x in match_list for y in x['gameday_data'] ] for tabformat in tabformat_list: datasheet.append(tabformat) datasheet_list.append(datasheet) book = Databook(datasheet_list) bookname_xls_relpath = self.schedcol_name + "_byDiv.xls" bookname_xls_fullpath = os.path.join(self.dir_path, bookname_xls_relpath) with open(bookname_xls_fullpath, 'wb') as f: f.write(book.xls) f.close() return [{'path': bookname_xls_relpath}]
def generate_fieldxls(self): headers = [ 'Game Date', 'Day', 'Time', 'Division', 'Home', 'Visitor', 'Venue' ] datasheet_list = list() for fieldinfo in self.fieldinfo_list: field_name = fieldinfo['field_name'] field_id = fieldinfo['field_id'] datasheet = Dataset(title=field_name) datasheet.headers = list(headers) match_list = self.sdbinterface.get_schedule('field_id', field_id=field_id) tabformat_list = [ (x['game_date'], parser.parse(x['game_date']).strftime("%a"), datetime.strptime(x['start_time'], "%H:%M").strftime("%I:%M%p"), x['div_age'] + x['div_gen'], x['home'], x['away'], field_name) for x in match_list ] for tabformat in tabformat_list: datasheet.append(tabformat) datasheet_list.append(datasheet) book = Databook(datasheet_list) bookname_xls_relpath = self.schedcol_name + "_byField.xls" bookname_xls_fullpath = os.path.join(self.dir_path, bookname_xls_relpath) with open(bookname_xls_fullpath, 'wb') as f: f.write(book.xls) f.close() return [{'path': bookname_xls_relpath}]
def emergency(current_user): # prepare user info user_data = user_schema.dump( User.query.filter_by(id=current_user.id).first()) User.query.filter_by(id=current_user.id).first().set_critical_state() data = Dataset() data.headers = [ 'First Name', 'Last Name', 'Email', 'Address', 'State', 'Age', 'Travel History', 'Telephone' ] for i in [(user_data['first_name'], user_data['last_name'], user_data['email'], user_data['address'], user_data['state'], user_data['age'], user_data['travel_history'], user_data['tel']) ]: data.append(i) with open(f'{os.getcwd()}/user_dat.xlsx', 'wb') as file: print(file.name) file.write(data.export('xlsx')) # actually send the message try: result = EmergencyMail("Emergency Report!", render_template('Emergency.html'), file.name) if result: return jsonify({'Sent Email': True}), 200 else: return jsonify({'Email not sent': True}), 500 except Exception as e: raise e return jsonify({'Sent Email': False}), 500 file.close()
def build_csv_from_data(self, form_data, response_data): id_to_label = self.get_header_maps(form_data) headers = ["id"] for c in id_to_label.values(): headers.append(re.sub("[\,\n\r]+", "", c)) data = Dataset(headers=headers) for response_info in response_data: response = response_info.get("responses", {}) row_id = response_info["id"] row = [row_id] for pk in response.keys(): value = response.get(pk) if isinstance(value, str) or not value: row.append(value) # attachment elif isinstance(value, list) and value[0].get("filename"): links = " ".join( [self.attachment_link(rec) for rec in value]) row.append(links) elif value.get("checked"): row.append(", ".join(value.get("checked"))) elif value.get("other_text"): row.append(value.get("other_text")) else: logger.error("Unhandled value type: %s (%s)." % (value, type(value))) logger.error("Response data structure: %s" % (response)) row.append(None) data.append(row) return data.export("csv")
def get_dataset(ufile): #stream = ufile.read().decode('utf-8') stream = ufile.read().decode('ISO-8859-1') #print('stream',stream) lines = stream.split('\r\n') logger.info('%d lines found in imported file %s', len(lines), ufile) #print('lines',lines) hdr = None val = [] for x in range(len(lines)): if x == 0: hdr = lines[x].split(',') else: val.append(lines[x].split(',')) #print('hdr',hdr) #val = val[:-1] #print('val',val) dataset = Dataset(headers=hdr) for i in range(len(val)): try: dataset.append(val[i]) except: logger.warning('Ingnoring row %d due to errors', i + 1) logger.debug('dataset.csv : %s', dataset.csv) return dataset
def render_to_response(self, context, **response_kwargs): """If exporting, generate a csv.""" if 'export' in self.request.GET: data = Dataset() data.headers = ( u'Name', u'Email', u'Phone', u'Zip', u'State', u'Joined', u'Last login', u'Total Groups Joined', u'Flags received', u'Messages sent', u'Staff?', u'Superuser?', u'Banned?', u'Visits' ) for user in self.get_queryset(): data.append(( user.get_real_name(), user.email, user.phone, user.zip_code, user.state, user.date_joined, user.last_login, user.total_groups_joined, user.flags_received, user.messages_sent, user.is_staff, user.is_superuser, user.is_banned, user.visit_count )) response = HttpResponse( data.csv, content_type='text/csv' ) response['Content-Disposition'] = 'attachment; filename=users.csv' return response else: return super(UserReportListView, self).render_to_response( context, **response_kwargs)
def test_file(self): # Simulate pyreports.io.File object file = MagicMock() file.raw_data = ['Matteo\n', 'Guadrini\n', '35'] data = Dataset() for line in file.raw_data: data.append([line]) # Read file data file.read = MagicMock(return_value=data) lines = file.read() self.assertIsInstance(lines, Dataset) # Write file data read_data = ''.join(file.raw_data) with open(f'{tmp_folder}/test_file.txt', 'w') as wf: wf.write(read_data) with patch('__main__.open', mock_open(read_data=read_data)): with open(f'{tmp_folder}/test_file.txt') as rf: result = rf.read() self.assertEqual(read_data, result) # Real pyreports.io.File object file_real = pyreports.io.TextFile(f'{tmp_folder}/test_file.txt') real_data = file_real.read() self.assertIsInstance(real_data, Dataset) file_real.write(real_data) self.assertEqual(file_real.read()[0][0], 'Matteo')
def exportDivSchedules(self, startgameday, prefix=""): headers = ['Match ID', 'Gameday#', 'Game Date', 'Day', 'Time', 'Division', 'Home', 'Away', 'Field', '', 'Comment'] datasheet_list = [] for division in self.leaguedivinfo: div_id = division['div_id'] div_age = division['div_age'] div_gen = division['div_gen'] div_str = div_age + div_gen datasheet = Dataset(title=div_str) datasheet.headers = list(headers) divdata_list = self.dbinterface.findElimTournDivisionSchedule(div_age, div_gen, min_game_id=startgameday) tabformat_list = [(y[match_id_CONST], x[gameday_id_CONST], tournMapGamedayIdToCalendar(x[gameday_id_CONST]), tournMapGamedayIdToDate(x[gameday_id_CONST]), datetime.strptime(x[start_time_CONST],"%H:%M").strftime("%I:%M %p"), div_str, y[home_CONST], y[away_CONST], self.fieldinfo[self.findexerGet(y[venue_CONST])]['name'], '', y[comment_CONST]) for x in divdata_list for y in x[gameday_data_CONST]] for tabformat in tabformat_list: datasheet.append(tabformat) datasheet.append_separator("Prefix Legend: 'S'-Seeded Team#, 'W'-Winning Team (See Match ID), 'L'-Losing Team)") datasheet_list.append(datasheet) book = Databook(datasheet_list) cdir = os.path.dirname(__file__) bookname_xls = prefix+'.xls' bookname_html = prefix+'.html' booknamefull_xls = os.path.join('/home/henry/workspace/datagraph/bottle_baseball/download/xls', bookname_xls) booknamefull_html = os.path.join('~/workspace/datagraph/bottle_baseball/download/html', bookname_html) with open(booknamefull_xls,'wb') as f: f.write(book.xls) f.close()
def import_data(self, dataset, dry_run=False, raise_errors=False, use_transactions=None, collect_failed_rows=False, **kwargs): headers = [] for header in dataset.headers: if header in self.__header_map: headers.append(self.__header_map[header]) else: headers.append(header) _dataset = Dataset(headers=headers) for row in dataset: _dataset.append(row) dataset = _dataset return super(self.__class__, self).import_data(dataset=dataset, dry_run=dry_run, raise_errors=raise_errors, use_transactions=use_transactions, collect_failed_rows=collect_failed_rows, **kwargs)
def exportDivTeamSchedules(self, div_id, age, gen, numteams, prefix=""): headers = ['Gameday#', 'Game Date', 'Day', 'Start Time', 'Venue', 'Home Team', 'Away Team'] datasheet_list = [] for team_id in range(1, numteams+1): team_str = age+gen+str(team_id) datasheet = Dataset(title=team_str) datasheet.headers = list(headers) teamdata_list = self.dbinterface.findTeamSchedule(age, gen, team_id) tabformat_list = [(x[gameday_id_CONST], tournMapGamedayIdToCalendar(x[gameday_id_CONST]), tournMapGamedayIdToDate(x[gameday_id_CONST]), datetime.strptime(x[start_time_CONST],"%H:%M").strftime("%I:%M %p"), self.fieldinfo[self.findexerGet(x[venue_CONST])]['name'], x[home_CONST], x[away_CONST]) for x in teamdata_list] for tabformat in tabformat_list: datasheet.append(tabformat) datasheet_list.append(datasheet) book = Databook(datasheet_list) cdir = os.path.dirname(__file__) bookname_xls = prefix+age + gen +'_schedule.xls' bookname_html = prefix+age + gen +'_schedule.html' booknamefull_xls = os.path.join('/home/henry/workspace/datagraph/bottle_baseball/download/xls', bookname_xls) booknamefull_html = os.path.join('~/workspace/datagraph/bottle_baseball/download/html', bookname_html) with open(booknamefull_xls,'wb') as f: f.write(book.xls) f.close() '''
def member_query(db): node_id = request.params.get('node_id') realname = request.params.get('realname') idcard = request.params.get('idcard') mobile = request.params.get('mobile') _query = db.query(models.SlcMember, models.SlcNode.node_name).filter( models.SlcNode.id == models.SlcMember.node_id) if idcard: _query = _query.filter(models.SlcMember.idcard == idcard) if mobile: _query = _query.filter(models.SlcMember.mobile == mobile) if node_id: _query = _query.filter(models.SlcMember.node_id == node_id) if realname: _query = _query.filter( models.SlcMember.realname.like('%' + realname + '%')) if request.path == '/member': return render("bus_member_list", page_data=get_page_data(_query), node_list=db.query(models.SlcNode), **request.params) elif request.path == "/member/export": data = Dataset() data.append( (u'区域', u'姓名', u'用户名', u'证件号', u'邮箱', u'联系电话', u'地址', u'创建时间')) for i, _node_name in _query: data.append((_node_name, i.realname, i.member_name, i.idcard, i.email, i.mobile, i.address, i.create_time)) name = u"RADIUS-MEMBER-" + datetime.datetime.now().strftime( "%Y%m%d-%H%M%S") + ".xls" with open(u'./static/xls/%s' % name, 'wb') as f: f.write(data.xls) return static_file(name, root='./static/xls', download=True)
def render_to_response(self, context, **response_kwargs): """If exporting, generate a csv.""" if 'export' in self.request.GET: data = Dataset() data.headers = ('Name', 'Messages', 'Threads', 'Replies', 'Posters', 'Flagged messages', 'Category', 'Tags', 'State', 'Members', 'Admins', 'Private', 'Published', 'Moderated', 'Featured', 'Member list published', 'Created', 'Created By', 'Photos', 'Photo clicks', 'Links', 'Link clicks') for group in self.get_queryset(): data.append( (group.group.name, group.message_count, group.thread_count, group.reply_count, group.posters, group.flagged, group.category.name, groups_tags_string([group]), group.state, group.member_count, group.owner_count, group.private, group.published, group.moderated, group.featured, group.member_list_published, group.created_at, group.created_by, group.image_count, group.image_clicks or 0, group.link_count, group.link_clicks or 0)) response = HttpResponse(data.csv, content_type='text/csv') response['Content-Disposition'] = 'attachment; filename=groups.csv' return response else: return super(GroupReportListView, self).render_to_response(context, **response_kwargs)
def export(self, queryset=None, task_meta=None): if queryset is None: queryset = self.get_queryset() headers = self.get_export_headers() data = Dataset(headers=headers) if isinstance(queryset, QuerySet): # Iterate without the queryset cache, to avoid wasting memory when # exporting large datasets. iterable = queryset.iterator() else: iterable = queryset if task_meta is not None: # initialize the total amount accross multiple resources self.num_done = task_meta['done'] for obj in iterable: data.append(self.export_resource(obj)) if task_meta is not None: self._update_task_state(task_meta) logger.debug('Num done: %d' % self.num_done) return data
def render_to_response(self, context, **response_kwargs): """If exporting, generate a csv.""" if 'export' in self.request.GET: data = Dataset() data.headers = ( u'Name', u'Email', u'Phone', u'Zip', u'State', u'Joined', u'Last login', u'Total Groups Joined', u'Flags received', u'Messages sent', u'Staff?', u'Superuser?', u'Banned?', u'Visits' ) for user in self.get_queryset(): data.append(( user, user.email, user.phone, user.zip_code, user.state, user.date_joined, user.last_login, user.total_groups_joined, user.flags_received, user.messages_sent, user.is_staff, user.is_superuser, user.is_banned, user.visit_count )) response = HttpResponse( data.csv, content_type='text/csv' ) response['Content-Disposition'] = 'attachment; filename=users.csv' return response else: return super(UserReportListView, self).render_to_response( context, **response_kwargs)
def render_to_response(self, context, **response_kwargs): """If exporting, generate a csv.""" if 'export' in self.request.GET: data = Dataset() data.headers = ( 'Name', 'Messages', 'Threads', 'Replies', 'Posters', 'Category', 'Tags', 'State', 'Members', 'Admins', 'Private', 'Published', 'Moderated', 'Featured', 'Member list published', 'Created', 'Created By' ) for group in self.get_queryset(): data.append(( group.group.name, group.message_count, group.thread_count, group.reply_count, group.posters, group.category.name, groups_tags_string([group]), group.state, group.member_count, group.owner_count, group.private, group.published, group.moderated, group.featured, group.member_list_published, group.created_at, group.created_by )) response = HttpResponse( data.csv, content_type='text/csv' ) response['Content-Disposition'] = 'attachment; filename=groups.csv' return response else: return super(GroupReportListView, self).render_to_response( context, **response_kwargs)
def generate_elimdivxls(self, genxls_id): headers = ['Match ID', 'Game Date', 'Day', 'Time', 'Division', 'Home', 'Visitor', 'Venue', 'Round', 'Comment'] datasheet_list = list() for divinfo in self.divinfo_list: div_id = divinfo[genxls_id] div_age = divinfo['div_age'] div_gen = divinfo['div_gen'] div_str = div_age + div_gen datasheet = Dataset(title=div_str) datasheet.headers = list(headers) match_list = self.sdbinterface.get_schedule(genxls_id, div_age=div_age, div_gen=div_gen, elim_flag=True) # note conversions for time from 24-hour to am/pm format tabformat_list = [(y['match_id'], x['game_date'], parser.parse(x['game_date']).strftime("%a"), datetime.strptime(x['start_time'], "%H:%M").strftime("%I:%M%p"), div_str, self.team_map(div_id, y['home']), self.team_map(div_id, y['away']), self.fieldinfo_list[self.findexerGet(y['venue'])]['field_name'], y['around'], y['comment']) for x in match_list for y in x['gameday_data']] for tabformat in tabformat_list: datasheet.append(tabformat) datasheet_list.append(datasheet) book = Databook(datasheet_list) bookname_xls_relpath = self.schedcol_name + "_byDivision.xls" bookname_xls_fullpath = os.path.join(self.dir_path, bookname_xls_relpath) with open(bookname_xls_fullpath,'wb') as f: f.write(book.xls) f.close() return [{'path':bookname_xls_relpath}]
def display_table( records: Sequence[Any], headers: Sequence[str], attrs: Sequence[str], tablefmt: str = "fancy_grid", ) -> None: """ Args: records: Sequence[Any]: headers: Sequence[str]: attrs: Sequence[str]: tablefmt: str: (Default value = "fancy_grid") Returns: """ if len(records) == 0: display_error("No results found") else: data = Dataset() data.headers = headers for record in records: data.append([getattr(record, a) for a in attrs]) click.echo(data.export("cli", tablefmt=tablefmt))
def csv_output_computers(): """Utility function to generate a CSV output of computers information from Incredibus data, for audit/cleansing purposes. """ computers = Computer.objects.all() d = Dataset() d.headers = [ 'ID', 'HOSTNAME', 'CHASSIS', 'PROBABLE OWNER EMAIL', 'PROBABLE OWNER CC', 'MANAGED BY EMAIL', 'ASSET NO.', 'SERIAL NO.' ] for i in computers: row = [i.pk, i.sam_account_name[:-1], i.chassis] if i.probable_owner: row += [ i.probable_owner.email.lower(), i.probable_owner.cost_centre ] else: row += ['', ''] if i.managed_by: row += [i.managed_by.email.lower()] else: row += [''] row += ['', i.serial_number] d.append(row) f = open('/tmp/computers.csv', 'w') f.write(d.csv) f.close()
def generate_fieldxls(self): headers = ['Game Date', 'Day', 'Time', 'Division', 'Home', 'Visitor', 'Venue'] datasheet_list = list() for fieldinfo in self.fieldinfo_list: field_name = fieldinfo['field_name'] field_id = fieldinfo['field_id'] datasheet = Dataset(title=field_name) datasheet.headers = list(headers) match_list = self.sdbinterface.get_schedule('field_id', field_id=field_id) tabformat_list = [(x['game_date'], parser.parse(x['game_date']).strftime("%a"), datetime.strptime(x['start_time'], "%H:%M").strftime("%I:%M%p"), x['div_age']+x['div_gen'], x['home'], x['away'], field_name) for x in match_list] for tabformat in tabformat_list: datasheet.append(tabformat) datasheet_list.append(datasheet) book = Databook(datasheet_list) bookname_xls_relpath = self.schedcol_name + "_byField.xls" bookname_xls_fullpath = os.path.join(self.dir_path, bookname_xls_relpath) with open(bookname_xls_fullpath,'wb') as f: f.write(book.xls) f.close() return [{'path':bookname_xls_relpath}]
def exportTeamSchedules(self, div_id, age, gen, numteams, prefix=""): headers = [ 'Gameday#', 'Game Date', 'Start Time', 'Venue', 'Home Team', 'Away Team' ] cdir = os.path.dirname(__file__) for team_id in range(1, numteams + 1): team_str = age + gen + str(team_id) datasheet = Dataset(title=team_str) datasheet.headers = list(headers) teamdata_list = self.dbinterface.findTeamSchedule( age, gen, team_id) tabformat_list = [ (x[gameday_id_CONST], mapGamedayIdToCalendar(x[gameday_id_CONST]), datetime.strptime(x[start_time_CONST], "%H:%M").strftime("%I:%M %p"), self.fieldinfo[self.findexerGet(x[venue_CONST])]['name'], x[home_CONST], x[away_CONST]) for x in teamdata_list ] for tabformat in tabformat_list: datasheet.append(tabformat) if team_id < 10: team_id_str = '0' + str(team_id) else: team_id_str = str(team_id) sheet_xls_relpath = prefix + age + gen + team_id_str + '_schedule.xls' sheet_xls_abspath = os.path.join( '/home/henry/workspace/datagraph/bottle_baseball/download/xls', sheet_xls_relpath) with open(sheet_xls_abspath, 'wb') as f: f.write(datasheet.xls) f.close()
def csv_output_computers(): """Utility function to generate a CSV output of computers information from Incredibus data, for audit/cleansing purposes. """ computers = Computer.objects.all() d = Dataset() d.headers = [ 'ID', 'HOSTNAME', 'CHASSIS', 'PROBABLE OWNER EMAIL', 'PROBABLE OWNER CC', 'MANAGED BY EMAIL', 'ASSET NO.', 'SERIAL NO.' ] for i in computers: row = [i.pk, i.sam_account_name[:-1], i.chassis] if i.probable_owner: row += [i.probable_owner.email.lower(), i.probable_owner.cost_centre] else: row += ['', ''] if i.managed_by: row += [i.managed_by.email.lower()] else: row += [''] row += ['', i.serial_number] d.append(row) f = open('/tmp/computers.csv', 'w') f.write(d.csv) f.close()
def create(self, request): """ To import CSV file to DB URL Structure: /assignment/api/ Required Fields: myfile """ adult_resource = AdultResource() dataset = Dataset() myfile = request.FILES['myfile'] file_data = dataset.load(myfile.read().decode('utf-8'), format='csv') data = Dataset() data.headers = ('id', 'age', 'work', 'fnlwgt', 'education', 'education_num', 'marital_status', 'occupation', 'relationship', 'race', 'sex', 'capital_gain', 'capital_loss', 'hours_per_week', 'native_country', 'salary') for i in range(0, len(file_data) - 1): l = list(file_data[i]) l.insert(0, i + 1) data.append(tuple(l)) result = adult_resource.import_data( data, dry_run=True) # Test the data import if not result.has_errors(): adult_resource.import_data(data, dry_run=False) # Actually import now pass return Response('Success')
def export(self, queryset=None, task_meta=None): if queryset is None: queryset = self.get_queryset() headers = self.get_export_headers() data = Dataset(headers=headers) if isinstance(queryset, QuerySet): # Iterate without the queryset cache, to avoid wasting memory when # exporting large datasets. iterable = queryset.iterator() else: iterable = queryset if task_meta is not None: # initialize the total amount across multiple resources self.num_done = task_meta['done'] for obj in iterable: data.append(self.export_resource(obj)) if task_meta is not None: self._update_task_state(task_meta) logger.debug('Num done: %d' % self.num_done) return data
def post(self): node_id = self.get_argument('node_id',None) accept_type = self.get_argument('accept_type',None) account_number = self.get_argument('account_number',None) operator_name = self.get_argument('operator_name',None) query_begin_time = self.get_argument('query_begin_time',None) query_end_time = self.get_argument('query_end_time',None) opr_nodes = self.get_opr_nodes() _query = self.db.query( models.TrAcceptLog.id, models.TrAcceptLog.accept_type, models.TrAcceptLog.accept_time, models.TrAcceptLog.accept_desc, models.TrAcceptLog.operator_name, models.TrAcceptLog.accept_source, models.TrAcceptLog.account_number, models.TrCustomer.node_id, models.TrNode.node_name ).filter( models.TrAcceptLog.account_number == models.TrAccount.account_number, models.TrCustomer.customer_id == models.TrAccount.customer_id, models.TrNode.id == models.TrCustomer.node_id ) if operator_name: _query = _query.filter(models.TrAcceptLog.operator_name == operator_name) if node_id: _query = _query.filter(models.TrCustomer.node_id == node_id) else: _query = _query.filter(models.TrCustomer.node_id.in_([i.id for i in opr_nodes])) if account_number: _query = _query.filter(models.TrAcceptLog.account_number.like('%' + account_number + '%')) if accept_type: _query = _query.filter(models.TrAcceptLog.accept_type == accept_type) if query_begin_time: _query = _query.filter(models.TrAcceptLog.accept_time >= query_begin_time + ' 00:00:00') if query_end_time: _query = _query.filter(models.TrAcceptLog.accept_time <= query_end_time + ' 23:59:59') _query = _query.order_by(models.TrAcceptLog.accept_time.desc()) type_map = ACCEPT_TYPES if self.request.path == '/admin/customer/acceptlog': return self.render( "acceptlog_list.html", page_data=self.get_page_data(_query), node_list=opr_nodes, type_map=type_map, get_orderid=lambda aid: self.db.query(models.TrCustomerOrder.order_id).filter_by(accept_id=aid).scalar(), **self.get_params() ) elif self.request.path == '/admin/customer/acceptlog/export': data = Dataset() data.append((u'区域', u'上网账号', u'受理类型', u'受理时间', u'受理渠道', u'操作员', u'受理描述')) for i in _query: data.append(( i.node_name, i.account_number, type_map.get(i.accept_type), i.accept_time, i.accept_source, i.operator_name, i.accept_desc )) name = u"RADIUS-ACCEPTLOG-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls" return self.export_file(name, data)
def acceptlog_query(db,render): node_id = request.params.get('node_id') accept_type = request.params.get('accept_type') account_number = request.params.get('account_number') operator_name = request.params.get('operator_name') query_begin_time = request.params.get('query_begin_time') query_end_time = request.params.get('query_end_time') opr_nodes = get_opr_nodes(db) _query = db.query( models.SlcRadAcceptLog.id, models.SlcRadAcceptLog.accept_type, models.SlcRadAcceptLog.accept_time, models.SlcRadAcceptLog.accept_desc, models.SlcRadAcceptLog.operator_name, models.SlcRadAcceptLog.accept_source, models.SlcRadAcceptLog.account_number, models.SlcMember.node_id, models.SlcNode.node_name ).filter( models.SlcRadAcceptLog.account_number == models.SlcRadAccount.account_number, models.SlcMember.member_id == models.SlcRadAccount.member_id, models.SlcNode.id == models.SlcMember.node_id ) if operator_name: _query = _query.filter(models.SlcRadAcceptLog.operator_name == operator_name) if node_id: _query = _query.filter(models.SlcMember.node_id == node_id) else: _query = _query.filter(models.SlcMember.node_id.in_([i.id for i in opr_nodes])) if account_number: _query = _query.filter(models.SlcRadAcceptLog.account_number.like('%' + account_number + '%')) if accept_type: _query = _query.filter(models.SlcRadAcceptLog.accept_type == accept_type) if query_begin_time: _query = _query.filter(models.SlcRadAcceptLog.accept_time >= query_begin_time + ' 00:00:00') if query_end_time: _query = _query.filter(models.SlcRadAcceptLog.accept_time <= query_end_time + ' 23:59:59') _query = _query.order_by(models.SlcRadAcceptLog.accept_time.desc()) type_map = ACCEPT_TYPES if request.path == '/': return render( "bus_acceptlog_list", page_data=get_page_data(_query), node_list=opr_nodes, type_map=type_map, get_orderid=lambda aid: db.query(models.SlcMemberOrder.order_id).filter_by(accept_id=aid).scalar(), **request.params ) elif request.path == '/export': data = Dataset() data.append((u'区域', u'上网账号', u'受理类型', u'受理时间', u'受理渠道', u'操作员', u'受理描述')) for i in _query: data.append(( i.node_name, i.account_number, type_map.get(i.accept_type), i.accept_time, i.accept_source, i.operator_name, i.accept_desc )) name = u"RADIUS-ACCEPTLOG-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls" return export_file(name, data)
def order_query(db, render): node_id = request.params.get('node_id') product_id = request.params.get('product_id') pay_status = request.params.get('pay_status') account_number = request.params.get('account_number') query_begin_time = request.params.get('query_begin_time') query_end_time = request.params.get('query_end_time') opr_nodes = get_opr_nodes(db) _query = db.query( models.SlcMemberOrder, models.SlcMember.node_id, models.SlcMember.realname, models.SlcRadProduct.product_name, models.SlcNode.node_name).filter( models.SlcMemberOrder.product_id == models.SlcRadProduct.id, models.SlcMemberOrder.member_id == models.SlcMember.member_id, models.SlcNode.id == models.SlcMember.node_id) if node_id: _query = _query.filter(models.SlcMember.node_id == node_id) else: _query = _query.filter( models.SlcMember.node_id.in_([i.id for i in opr_nodes])) if account_number: _query = _query.filter( models.SlcMemberOrder.account_number.like('%' + account_number + '%')) if product_id: _query = _query.filter(models.SlcMemberOrder.product_id == product_id) if pay_status: _query = _query.filter(models.SlcMemberOrder.pay_status == pay_status) if query_begin_time: _query = _query.filter( models.SlcMemberOrder.create_time >= query_begin_time + ' 00:00:00') if query_end_time: _query = _query.filter( models.SlcMemberOrder.create_time <= query_end_time + ' 23:59:59') _query = _query.order_by(models.SlcMemberOrder.create_time.desc()) if request.path == '/': return render("bus_order_list", node_list=opr_nodes, products=db.query( models.SlcRadProduct).filter_by(product_status=0), page_data=get_page_data(_query), **request.params) elif request.path == '/export': data = Dataset() data.append((u'区域', u"用户姓名", u'上网账号', u'资费', u"订购时间", u'订单费用', u'实缴费用', u'支付状态', u'订购渠道', u'订单描述')) _f2y = utils.fen2yuan _fms = utils.fmt_second _pst = {0: u'未支付', 1: u'已支付', 2: u'已取消'} for i, _, _realname, _product_name, _node_name in _query: data.append( (_node_name, _realname, i.account_number, _product_name, i.create_time, _f2y(i.order_fee), _f2y(i.actual_fee), _pst.get(i.pay_status), i.order_source, i.order_desc)) name = u"RADIUS-ORDERS-" + datetime.datetime.now().strftime( "%Y%m%d-%H%M%S") + ".xls" return export_file(name, data)
def query_dataset(cursor, stmt, *args, **kwargs) -> Dataset: cursor.execute(stmt, args) table_name = kwargs.get("table_name", "") dataset = Dataset(title=table_name) dataset.headers = (desc[0] for desc in cursor.description) for row in cursor: dataset.append(row) return dataset
def compare_webcrawl_ga_with_accounts(): from tablib import Dataset domain_names = Dataset().load(open(DATA_DIR + 'domain_names.csv').read()) ga_domains = Dataset().load(open(DATA_DIR + 'ga_domains.csv').read()) ga_billing_subscribers = Dataset().load( open(DATA_DIR + 'analytics_usage_201905.csv').read()) ga_billing_subscriber_codes = [ subscriber['ID'] for subscriber in ga_billing_subscribers.dict ] ga_data_subscribers = Dataset().load( open(DATA_DIR + 'ga_accounts_views_index.csv').read()) ga_data_subscribers_codes = [ subscriber['property_id'] for subscriber in ga_data_subscribers.dict ] ga_subscriber_codes = list(set().union(ga_billing_subscriber_codes, ga_data_subscribers_codes)) agency_hostnames = {domain: agency for domain, agency in domain_names} subscriber_agencies = set() for domain in ga_domains.dict: agency = agency_hostnames.get( domain['hostname'], agency_hostnames.get(domain['domain_name'], "Unknown Agency")) if "GTM" not in domain['ga_code']: if domain['ga_code'] in ga_subscriber_codes: subscriber_agencies.add(agency) non_subscriber_websites = Dataset() non_subscriber_websites.headers = ["agency", "hostname", "ga_code"] subscriber_websites_not_subscribed = Dataset() subscriber_websites_not_subscribed.headers = [ "agency", "hostname", "ga_code" ] for domain in ga_domains.dict: agency = agency_hostnames.get( domain['hostname'], agency_hostnames.get(domain['domain_name'], "Unknown Agency")) if "GTM" not in domain['ga_code']: if domain[ 'ga_code'] not in ga_subscriber_codes and agency not in subscriber_agencies: print("{}: {} has non-subscriber UA code: {}".format( agency, domain['hostname'], domain['ga_code'])) non_subscriber_websites.append( [agency, domain['hostname'], domain['ga_code']]) elif domain[ 'ga_code'] not in ga_subscriber_codes and agency in subscriber_agencies: print( "{}: {} has non-subscriber UA code but is a subscribing agency: {}" .format(agency, domain['hostname'], domain['ga_code'])) subscriber_websites_not_subscribed.append( [agency, domain['hostname'], domain['ga_code']]) with open(DATA_DIR + '/non_subscriber_websites.csv', 'wt', newline='') as f: f.write(non_subscriber_websites.csv) with open(DATA_DIR + '/subscriber_websites_not_subscribed.csv', 'wt', newline='') as f: f.write(subscriber_websites_not_subscribed.csv)
def order_query(db, render): node_id = request.params.get('node_id') product_id = request.params.get('product_id') pay_status = request.params.get('pay_status') account_number = request.params.get('account_number') query_begin_time = request.params.get('query_begin_time') query_end_time = request.params.get('query_end_time') opr_nodes = get_opr_nodes(db) _query = db.query( models.SlcMemberOrder, models.SlcMember.node_id, models.SlcMember.realname, models.SlcRadProduct.product_name, models.SlcNode.node_name ).filter( models.SlcMemberOrder.product_id == models.SlcRadProduct.id, models.SlcMemberOrder.member_id == models.SlcMember.member_id, models.SlcNode.id == models.SlcMember.node_id ) if node_id: _query = _query.filter(models.SlcMember.node_id == node_id) else: _query = _query.filter(models.SlcMember.node_id.in_([i.id for i in opr_nodes])) if account_number: _query = _query.filter(models.SlcMemberOrder.account_number.like('%' + account_number + '%')) if product_id: _query = _query.filter(models.SlcMemberOrder.product_id == product_id) if pay_status: _query = _query.filter(models.SlcMemberOrder.pay_status == pay_status) if query_begin_time: _query = _query.filter(models.SlcMemberOrder.create_time >= query_begin_time + ' 00:00:00') if query_end_time: _query = _query.filter(models.SlcMemberOrder.create_time <= query_end_time + ' 23:59:59') _query = _query.order_by(models.SlcMemberOrder.create_time.desc()) if request.path == '/': return render("bus_order_list", node_list=opr_nodes, products=db.query(models.SlcRadProduct).filter_by(product_status=0), page_data=get_page_data(_query), **request.params) elif request.path == '/export': data = Dataset() data.append(( u'区域', u"用户姓名", u'上网账号', u'资费', u"订购时间", u'订单费用', u'实缴费用', u'支付状态', u'订购渠道', u'订单描述' )) _f2y = utils.fen2yuan _fms = utils.fmt_second _pst = {0: u'未支付', 1: u'已支付', 2: u'已取消'} for i, _, _realname, _product_name, _node_name in _query: data.append(( _node_name, _realname, i.account_number, _product_name, i.create_time, _f2y(i.order_fee), _f2y(i.actual_fee), _pst.get(i.pay_status), i.order_source, i.order_desc )) name = u"RADIUS-ORDERS-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls" return export_file(name, data)
def post(self): node_id = self.get_argument('node_id',None) product_id = self.get_argument('product_id',None) pay_status = self.get_argument('pay_status',None) account_number = self.get_argument('account_number',None) query_begin_time = self.get_argument('query_begin_time',None) query_end_time = self.get_argument('query_end_time',None) opr_nodes = self.get_opr_nodes() _query = self.db.query( models.TrCustomerOrder, models.TrCustomer.node_id, models.TrCustomer.realname, models.TrProduct.product_name, models.TrNode.node_name ).filter( models.TrCustomerOrder.product_id == models.TrProduct.id, models.TrCustomerOrder.customer_id == models.TrCustomer.customer_id, models.TrNode.id == models.TrCustomer.node_id ) if node_id: _query = _query.filter(models.TrCustomer.node_id == node_id) else: _query = _query.filter(models.TrCustomer.node_id.in_([i.id for i in opr_nodes])) if account_number: _query = _query.filter(models.TrCustomerOrder.account_number.like('%' + account_number + '%')) if product_id: _query = _query.filter(models.TrCustomerOrder.product_id == product_id) if pay_status: _query = _query.filter(models.TrCustomerOrder.pay_status == pay_status) if query_begin_time: _query = _query.filter(models.TrCustomerOrder.create_time >= query_begin_time + ' 00:00:00') if query_end_time: _query = _query.filter(models.TrCustomerOrder.create_time <= query_end_time + ' 23:59:59') _query = _query.order_by(models.TrCustomerOrder.create_time.desc()) if self.request.path == '/admin/customer/order': return self.render("order_list.html", node_list=opr_nodes, products=self.db.query(models.TrProduct).filter_by(product_status=0), page_data=self.get_page_data(_query), **self.get_params()) elif self.request.path == '/admin/customer/order/export': data = Dataset() data.append(( u'区域', u"用户姓名", u'上网账号', u'资费', u"订购时间", u'订单费用', u'实缴费用', u'支付状态', u'订购渠道', u'订单描述' )) _f2y = utils.fen2yuan _fms = utils.fmt_second _pst = {0: u'未支付', 1: u'已支付', 2: u'已取消'} for i, _, _realname, _product_name, _node_name in _query: data.append(( _node_name, _realname, i.account_number, _product_name, i.create_time, _f2y(i.order_fee), _f2y(i.actual_fee), _pst.get(i.pay_status), i.order_source, i.order_desc )) name = u"RADIUS-ORDERS-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls" return self.export_file(name, data)
def test_logentry_creation_with_import_obj_exception(self): # from https://mail.python.org/pipermail/python-dev/2008-January/076194.html def monkeypatch_method(cls): def decorator(func): setattr(cls, func.__name__, func) return func return decorator # Cause an exception in import_row, but only after import is confirmed, # so a failure only occurs when ImportMixin.process_import is called. class R(BookResource): def import_obj(self, obj, data, dry_run): if dry_run: super().import_obj(obj, data, dry_run) else: raise Exception @monkeypatch_method(BookAdmin) def get_resource_class(self): return R # Verify that when an exception occurs in import_row, when raise_errors is False, # the returned row result has a correct import_type value, # so generating log entries does not fail. @monkeypatch_method(BookAdmin) def process_dataset(self, dataset, confirm_form, request, *args, **kwargs): resource = self.get_import_resource_class()( **self.get_import_resource_kwargs(request, *args, **kwargs)) return resource.import_data( dataset, dry_run=False, raise_errors=False, file_name=confirm_form.cleaned_data['original_file_name'], user=request.user, **kwargs) dataset = Dataset(headers=["id", "name", "author_email"]) dataset.append([1, "Test 1", "*****@*****.**"]) input_format = '0' content = dataset.csv f = SimpleUploadedFile("data.csv", content.encode(), content_type="text/csv") data = { "input_format": input_format, "import_file": f, } response = self.client.post('/admin/core/book/import/', data) self.assertEqual(response.status_code, 200) confirm_form = response.context['confirm_form'] data = confirm_form.initial response = self.client.post('/admin/core/book/process_import/', data, follow=True) self.assertEqual(response.status_code, 200)
def acceptlog_query(db): node_id = request.params.get('node_id') accept_type = request.params.get('accept_type') account_number = request.params.get('account_number') operator_name = request.params.get('operator_name') query_begin_time = request.params.get('query_begin_time') query_end_time = request.params.get('query_end_time') _query = db.query( models.SlcRadAcceptLog.id, models.SlcRadAcceptLog.accept_type, models.SlcRadAcceptLog.accept_time, models.SlcRadAcceptLog.accept_desc, models.SlcRadAcceptLog.operator_name, models.SlcRadAcceptLog.accept_source, models.SlcRadAcceptLog.account_number, models.SlcMember.node_id, models.SlcNode.node_name ).filter( models.SlcRadAcceptLog.account_number == models.SlcRadAccount.account_number, models.SlcMember.member_id == models.SlcRadAccount.member_id, models.SlcNode.id == models.SlcMember.node_id ) if operator_name: _query = _query.filter(models.SlcRadAcceptLog.operator_name == operator_name) if node_id: _query = _query.filter(models.SlcMember.node_id == node_id) if account_number: _query = _query.filter(models.SlcRadAcceptLog.account_number.like('%'+account_number+'%')) if accept_type: _query = _query.filter(models.SlcRadAcceptLog.accept_type == accept_type) if query_begin_time: _query = _query.filter(models.SlcRadAcceptLog.accept_time >= query_begin_time+' 00:00:00') if query_end_time: _query = _query.filter(models.SlcRadAcceptLog.accept_time <= query_end_time+' 23:59:59') _query = _query.order_by(models.SlcRadAcceptLog.accept_time.desc()) type_map = {'open':u'开户','pause':u'停机','resume':u'复机','cancel':u'销户','next':u'续费','charge':u'充值'} if request.path == '/acceptlog': return render( "bus_acceptlog_list", page_data = get_page_data(_query), node_list=db.query(models.SlcNode), type_map = type_map, get_orderid = lambda aid:db.query(models.SlcMemberOrder.order_id).filter_by(accept_id=aid).scalar(), **request.params ) elif request.path == '/acceptlog/export': data = Dataset() data.append((u'区域',u'上网账号',u'受理类型',u'受理时间',u'受理渠道',u'操作员',u'受理描述')) for i in _query: data.append(( i.node_name, i.account_number, type_map.get(i.accept_type), i.accept_time,i.accept_source,i.operator_name,i.accept_desc )) name = u"RADIUS-ACCEPTLOG-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls" with open(u'./static/xls/%s' % name, 'wb') as f: f.write(data.xls) return static_file(name, root='./static/xls',download=True)
def post(self): node_id = self.get_argument('node_id', None) account_number = self.get_argument('account_number', None) query_begin_time = self.get_argument('query_begin_time', None) query_end_time = self.get_argument('query_end_time', None) opr_nodes = self.get_opr_nodes() _query = self.db.query( models.TrBilling, models.TrCustomer.node_id, models.TrNode.node_name).filter( models.TrBilling.account_number == models.TrAccount.account_number, models.TrCustomer.customer_id == models.TrAccount.customer_id, models.TrNode.id == models.TrCustomer.node_id) if node_id: _query = _query.filter(models.TrCustomer.node_id == node_id) else: _query = _query.filter( models.TrCustomer.node_id.in_(i.id for i in opr_nodes)) if account_number: _query = _query.filter( models.TrBilling.account_number.like('%' + account_number + '%')) if query_begin_time: _query = _query.filter( models.TrBilling.create_time >= query_begin_time + ' 00:00:00') if query_end_time: _query = _query.filter( models.TrBilling.create_time <= query_end_time + ' 23:59:59') _query = _query.order_by(models.TrBilling.create_time.desc()) if self.request.path == '/admin/customer/billing': return self.render("billing_list.html", node_list=opr_nodes, page_data=self.get_page_data(_query), **self.get_params()) elif self.request.path == '/admin/customer/billing/export': data = Dataset() data.append((u'区域', u'上网账号', u'BAS地址', u'会话编号', u'记账开始时间', u'会话时长', u'已扣时长', u"已扣流量", u'应扣费用', u'实扣费用', u'剩余余额', u'剩余时长', u'剩余流量', u'是否扣费', u'扣费时间')) _f2y = utils.fen2yuan _fms = utils.fmt_second _k2m = utils.kb2mb _s2h = utils.sec2hour for i, _, _node_name in _query: data.append( (_node_name, i.account_number, i.nas_addr, i.acct_session_id, i.acct_start_time, _fms(i.acct_session_time), _fms(i.acct_times), _k2m(i.acct_flows), _f2y(i.acct_fee), _f2y(i.actual_fee), _f2y(i.balance), _s2h(i.time_length), _k2m(i.flow_length), (i.is_deduct == 0 and u'否' or u'是'), i.create_time)) name = u"RADIUS-BILLING-" + datetime.datetime.now().strftime( "%Y%m%d-%H%M%S") + ".xls" return self.export_file(name, data)
def table_to_dataset(self, table, exclude_columns): """Transform a table to a tablib dataset.""" dataset = Dataset() for i, row in enumerate( table.as_values(exclude_columns=exclude_columns)): if i == 0: dataset.headers = row else: dataset.append(row) return dataset
def delete_row(self, n): n = n - 2 if n == -1: new_data = Dataset() new_data.headers = self.data[0] for row in self.data[1:]: new_data.append(row) self.data = new_data else: del self.data[n]
def drop(self): n = len(self.data) - 1 if n == -1: new_data = Dataset() new_data.headers = self.data[0] for row in self.data[1:]: new_data.append(row) self.data = new_data else: del self.data[n]
def billing_query(db, render): node_id = request.params.get('node_id') account_number = request.params.get('account_number') query_begin_time = request.params.get('query_begin_time') query_end_time = request.params.get('query_end_time') opr_nodes = get_opr_nodes(db) _query = db.query( models.SlcRadBilling, models.SlcMember.node_id, models.SlcNode.node_name).filter( models.SlcRadBilling.account_number == models.SlcRadAccount.account_number, models.SlcMember.member_id == models.SlcRadAccount.member_id, models.SlcNode.id == models.SlcMember.node_id) if node_id: _query = _query.filter(models.SlcMember.node_id == node_id) else: _query = _query.filter( models.SlcMember.node_id.in_(i.id for i in opr_nodes)) if account_number: _query = _query.filter( models.SlcRadBilling.account_number.like('%' + account_number + '%')) if query_begin_time: _query = _query.filter( models.SlcRadBilling.create_time >= query_begin_time + ' 00:00:00') if query_end_time: _query = _query.filter( models.SlcRadBilling.create_time <= query_end_time + ' 23:59:59') _query = _query.order_by(models.SlcRadBilling.create_time.desc()) if request.path == '/': return render("bus_billing_list", node_list=opr_nodes, page_data=get_page_data(_query), **request.params) elif request.path == '/export': data = Dataset() data.append((u'区域', u'上网账号', u'BAS地址', u'会话编号', u'记账开始时间', u'会话时长', u'已扣时长', u"已扣流量", u'应扣费用', u'实扣费用', u'剩余余额', u'剩余时长', u'剩余流量', u'是否扣费', u'扣费时间')) _f2y = utils.fen2yuan _fms = utils.fmt_second _k2m = utils.kb2mb _s2h = utils.sec2hour for i, _, _node_name in _query: data.append( (_node_name, i.account_number, i.nas_addr, i.acct_session_id, i.acct_start_time, _fms(i.acct_session_time), _fms(i.acct_times), _k2m(i.acct_flows), _f2y(i.acct_fee), _f2y(i.actual_fee), _f2y(i.balance), _s2h(i.time_length), _k2m(i.flow_length), (i.is_deduct == 0 and u'否' or u'是'), i.create_time)) name = u"RADIUS-BILLING-" + datetime.datetime.now().strftime( "%Y%m%d-%H%M%S") + ".xls" return export_file(name, data)
def combine_tally(): from tablib import Dataset data = Dataset() for f in glob.glob(DATA_DIR + 'tally_69211100_20190425.csv-*'): d = Dataset().load(open(f, 'rt').read()) for row in d: data.append(row) with open(DATA_DIR + 'tally_69211100_20190425.csv', 'wt', newline='') as f: f.write('path,hits\n') f.write(data.csv)
def post(self): node_id = self.get_argument('node_id',None) account_number = self.get_argument('account_number',None) query_begin_time = self.get_argument('query_begin_time',None) query_end_time = self.get_argument('query_end_time',None) opr_nodes = self.get_opr_nodes() _query = self.db.query( models.TrBilling, models.TrCustomer.node_id, models.TrNode.node_name ).filter( models.TrBilling.account_number == models.TrAccount.account_number, models.TrCustomer.customer_id == models.TrAccount.customer_id, models.TrNode.id == models.TrCustomer.node_id ) if node_id: _query = _query.filter(models.TrCustomer.node_id == node_id) else: _query = _query.filter(models.TrCustomer.node_id.in_(i.id for i in opr_nodes)) if account_number: _query = _query.filter(models.TrBilling.account_number.like('%' + account_number + '%')) if query_begin_time: _query = _query.filter(models.TrBilling.create_time >= query_begin_time + ' 00:00:00') if query_end_time: _query = _query.filter(models.TrBilling.create_time <= query_end_time + ' 23:59:59') _query = _query.order_by(models.TrBilling.create_time.desc()) if self.request.path == '/admin/customer/billing': return self.render("billing_list.html", node_list=opr_nodes, page_data=self.get_page_data(_query), **self.get_params()) elif request.path == '/admin/customer/billing/export': data = Dataset() data.append(( u'区域', u'上网账号', u'BAS地址', u'会话编号', u'记账开始时间', u'会话时长', u'已扣时长', u"已扣流量", u'应扣费用', u'实扣费用', u'剩余余额', u'剩余时长', u'剩余流量', u'是否扣费', u'扣费时间' )) _f2y = utils.fen2yuan _fms = utils.fmt_second _k2m = utils.kb2mb _s2h = utils.sec2hour for i, _, _node_name in _query: data.append(( _node_name, i.account_number, i.nas_addr, i.acct_session_id, i.acct_start_time, _fms(i.acct_session_time), _fms(i.acct_times), _k2m(i.acct_flows), _f2y(i.acct_fee), _f2y(i.actual_fee), _f2y(i.balance), _s2h(i.time_length), _k2m(i.flow_length), (i.is_deduct == 0 and u'否' or u'是'), i.create_time )) name = u"RADIUS-BILLING-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls" return self.export_file(name, data)
def skip_rows(dataset: Dataset, num_rows: int = 0, col_skip: int = 1) -> None: if num_rows <= 0: return dataset_headers = dataset[num_rows - 1] dataset_data = dataset[num_rows:] dataset.wipe() dataset.headers = dataset_headers for r in dataset_data: vals = set(("" if c is None else c) for c in r[col_skip:]) if len(vals) == 1 and "" in vals: continue dataset.append(tuple(str_normalize(c) if isinstance(c, str) else ("" if c is None else c) for c in r))
def card_list(db): product_id = request.params.get('product_id') card_type = request.params.get('card_type') card_status = request.params.get('card_status') batch_no = request.params.get('batch_no') query_begin_time = request.params.get('query_begin_time') query_end_time = request.params.get('query_end_time') _query = db.query(models.SlcRechargerCard) if product_id and card_type == '0': _query = _query.filter(models.SlcRechargerCard.product_id==product_id) if card_type: _query = _query.filter(models.SlcRechargerCard.card_type==card_type) if batch_no: _query = _query.filter(models.SlcRechargerCard.batch_no==batch_no) if card_status: _query = _query.filter(models.SlcRechargerCard.card_status==card_status) if query_begin_time: _query = _query.filter(models.SlcRechargerCard.create_time >= query_begin_time+' 00:00:00') if query_end_time: _query = _query.filter(models.SlcRechargerCard.create_time <= query_end_time+' 23:59:59') products = db.query(models.SlcRadProduct).filter( models.SlcRadProduct.product_status == 0, models.SlcRadProduct.product_policy.in_([0,2]) ) if request.path == '/list': print "total:",_query.count() return render("card_list", page_data = get_page_data(_query), card_types = forms.card_types, card_states = forms.card_states, products = products, colors = {0:'',1:'class="success"',2:'class="warning"',3:'class="danger"'}, **request.params ) elif request.path == '/export': data = Dataset() data.append(( u'批次号',u'充值卡号',u'充值卡密码',u'充值卡类型',u'状态', u'资费id', u'面值/售价',u"授权月数",u"过期时间",u'创建时间' )) print "total:",_query.count() for i in _query: data.append(( i.batch_no, i.card_number, utils.decrypt(i.card_passwd),forms.card_types[i.card_type], forms.card_states[i.card_status],get_product_name(db,i.product_id),utils.fen2yuan(i.fee_value), i.months,i.expire_date,i.create_time )) name = u"RADIUS-CARD-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls" with open(u'./static/xls/%s' % name, 'wb') as f: f.write(data.xls) return static_file(name, root='./static/xls',download=True)
def seven_largest_single_data_sheet(self): work_sheet = Dataset(title="7 Largest Disbursements") work_sheet.headers = ['iso', 'country', 'position', 'shown amount', 'shown donor'] for country in models.Recipient.objects.all(): table = LargestDisbursementTable(country=country).as_dictionary()["table"] for position, disbursement in enumerate(table): formatted = disbursement["disbursement"] donor = disbursement["donor"] work_sheet.append([country.iso3, country.name, position + 1, formatted, donor]) return work_sheet
def five_largest_graph_data_sheet(self): work_sheet = Dataset(title="Five Largest Graph") work_sheet.headers = ['iso', 'country', 'position', 'shown percentage', 'real percentage', 'donor'] for country in models.Recipient.objects.all(): table = FiveLargestGraph(country=country).as_list() for position, disbursement in enumerate(table): real = disbursement["percentage"]["real"] formatted = disbursement["percentage"]["formatted"] donor = disbursement["name"] work_sheet.append([country.iso3, country.name, position + 1, formatted, real, donor]) return work_sheet
def clean_csv_headers(csv): """ Remove commas, line breaks, etc, anything that will screw up the translation from CSV -> database table. CSVKit, in particular, doesn't like header columns with these chars in it. """ data = Dataset().load(csv, format="csv") headers = [re.sub("[,\"'\n]", "", h) for h in data.headers] new_data = Dataset(headers=headers) for row in data: new_data.append(row) return new_data.export("csv")
def export(self, empty=False): serializer = self.empty_serializer dataset = Dataset(headers=self.get_export_header(serializer)) if not empty: for instance in self.get_export_queryset(): dataset.append(self.get_export_row(serializer, instance)) return ExportResult( filename=self.get_export_filename(), dataset=dataset, file_formats=self.file_formats )
def other_disbursements_data_sheet(self): work_sheet = Dataset(title="Other disbursements") work_sheet.headers = ['iso', 'country', 'amount of other disbursements vs 7 largest'] for country in models.Recipient.objects.all(): re_disb = re.compile("Other (\d+) Disb\s*") disbursements = models.Disbursement.objects.filter(country=country) other_disbursements = disbursements.get(donor__contains="Other ") ndisb = int(re_disb.match(other_disbursements.donor).groups()[0]) total_disbursements_count = disbursements.count() - 1 + ndisb work_sheet.append([country.iso3, country.name, total_disbursements_count]) return work_sheet
def multilateral_and_foundation_table_data_sheet(self): work_sheet = Dataset(title="Multilateral-Foundation") work_sheet.headers = ['iso', 'country', 'number of disbursements', 'total shown disbursement', 'total real disbursement'] for country in models.Recipient.objects.all(): table = MultilateralAndFoundationDisbursementSourcesTable(country=country).as_dictionary() if len(table): number = table["total"]["number_of_disbursements"] amount_formatted = table["total"]["amount"]["formatted"] amount_real = table["total"]["amount"].get("real", '') work_sheet.append([country.iso3, country.name, number, amount_formatted, amount_real]) return work_sheet
def test_logentry_creation_with_import_obj_exception(self): # from https://mail.python.org/pipermail/python-dev/2008-January/076194.html def monkeypatch_method(cls): def decorator(func): setattr(cls, func.__name__, func) return func return decorator # Cause an exception in import_row, but only after import is confirmed, # so a failure only occurs when ImportMixin.process_import is called. class R(BookResource): def import_obj(self, obj, data, dry_run): if dry_run: super().import_obj(obj, data, dry_run) else: raise Exception @monkeypatch_method(BookAdmin) def get_resource_class(self): return R # Verify that when an exception occurs in import_row, when raise_errors is False, # the returned row result has a correct import_type value, # so generating log entries does not fail. @monkeypatch_method(BookAdmin) def process_dataset(self, dataset, confirm_form, request, *args, **kwargs): resource = self.get_import_resource_class()(**self.get_import_resource_kwargs(request, *args, **kwargs)) return resource.import_data(dataset, dry_run=False, raise_errors=False, file_name=confirm_form.cleaned_data['original_file_name'], user=request.user, **kwargs) dataset = Dataset(headers=["id","name","author_email"]) dataset.append([1, "Test 1", "*****@*****.**"]) input_format = '0' content = dataset.csv f = SimpleUploadedFile("data.csv", content.encode(), content_type="text/csv") data = { "input_format": input_format, "import_file": f, } response = self.client.post('/admin/core/book/import/', data) self.assertEqual(response.status_code, 200) confirm_form = response.context['confirm_form'] data = confirm_form.initial response = self.client.post('/admin/core/book/process_import/', data, follow=True) self.assertEqual(response.status_code, 200)
def dump_connections(): print('Running connection dump...') dataset = Dataset() dataset.headers = ['Identity', 'Backend'] for connection in Connection.objects.filter(identity__startswith='+'): dataset.append([ connection.identity, connection.backend.name ]) with open('connections.csv', 'w') as f: f.write(dataset.csv) print('Done')
def _import(self): """Execute the import.""" import_dataset = Dataset() fieldtypes = self._prepare_dataset_to_import() import_dataset.headers = self.read_dataset.headers inProj = Proj(init='epsg:25831') outProj = Proj(init='epsg:4326') for row in self.read_dataset.dict: # Ignore rows with emtpy lat or lon if row['lon'] is not None and row['lat'] is not None: row['lon'], row['lat'] = transform( inProj, outProj, row['lon'], row['lat'] ) row = self._check_fieldtypes(row, fieldtypes) new = [] for key in row: new.append(row[key]) import_dataset.append(new) db = connection.cursor() import_dataset.headers = None with tempfile.NamedTemporaryFile() as f: f.write(import_dataset.csv) f.seek(0) try: db.copy_from(f, 'storm_drain', columns=(self.read_dataset.headers), sep=",", null='null') self._add_version( self.request, self.read_dataset.dict[0]['version'] ) self.response = { 'success': True, 'headers': self.read_dataset.headers } except Exception as e: error = str(e).replace('\n', ' ').replace('\r', '') self.response = {'success': False, 'err': error}
def billing_query(db): node_id = request.params.get('node_id') account_number = request.params.get('account_number') query_begin_time = request.params.get('query_begin_time') query_end_time = request.params.get('query_end_time') _query = db.query( models.SlcRadBilling, models.SlcMember.node_id, models.SlcNode.node_name ).filter( models.SlcRadBilling.account_number == models.SlcRadAccount.account_number, models.SlcMember.member_id == models.SlcRadAccount.member_id, models.SlcNode.id == models.SlcMember.node_id ) if node_id: _query = _query.filter(models.SlcMember.node_id == node_id) if account_number: _query = _query.filter(models.SlcRadBilling.account_number.like('%'+account_number+'%')) if query_begin_time: _query = _query.filter(models.SlcRadBilling.create_time >= query_begin_time+' 00:00:00') if query_end_time: _query = _query.filter(models.SlcRadBilling.create_time <= query_end_time+' 23:59:59') _query = _query.order_by(models.SlcRadBilling.create_time.desc()) if request.path == '/billing': return render("bus_billing_list", node_list=db.query(models.SlcNode), page_data=get_page_data(_query),**request.params) elif request.path == '/billing/export': data = Dataset() data.append(( u'区域',u'上网账号',u'BAS地址',u'会话编号',u'记账开始时间',u'会话时长', u'扣费时长',u'应扣费用',u'实扣费用',u'当前余额',u'是否扣费',u'扣费时间' )) _f2y = utils.fen2yuan _fms = utils.fmt_second for i,_,_node_name in _query: data.append(( _node_name, i.account_number, i.nas_addr,i.acct_session_id, i.acct_start_time,_fms(i.acct_session_time),_fms(i.acct_length), _f2y(i.acct_fee),_f2y(i.actual_fee),_f2y(i.balance), (i.is_deduct==0 and u'否' or u'是'),i.create_time )) name = u"RADIUS-BILLING-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls" with open(u'./static/xls/%s' % name, 'wb') as f: f.write(data.xls) return static_file(name, root='./static/xls',download=True)
def get(self, request): work_sheet = Dataset() work_sheet.headers = ['iso', 'recipient', 'year', 'mdgpurpose', 'real value', 'shown value'] for country in models.Recipient.objects.all(): data = DisbursementPurposeTable(country).as_dictionary() for year in data['years']: for purpose in data['names']: try: real = data['data'][year][purpose]['real'] except: real = '' shown = data['data'][year][purpose]['formatted'] work_sheet.append([country.iso3, country.name, year, purpose, real, shown]) response = HttpResponse(work_sheet.xls, mimetype='application/ms-excel') response['Content-Disposition'] = 'attachment; filename=%s.xls' % u"purpose_disbursements" return response
def main(): writer = open("/Users/Kristen/PycharmProjects/proj/Other/utils/pypi.csv", "a+") Cache = dict() incomplete = set() Data = Dataset(title="packages", headers=["name", "dict"]) for package in by_key: try: print("trying {}".format(package)) _package = PyPiPackage(package) Cache[package] = _package writer = open("/Users/Kristen/PycharmProjects/proj/Other/utils/pypi.json", "w+") Data.append(package, _package.__reduce__()) writer.write(Data.json) print("{} added to file ".format(package)) except AttributeError: print("XXXX for {}".format(package)) incomplete.add(package) pass
def get_dataset(self, fields): headers = [field.rpartition('-')[0] for field in fields] dataset = Dataset(headers=headers) for submission in self.queryset.only('data').iterator(): row_data = [] form_fields = [field for field in submission.get_form_data() if field.field_id in fields] for header in fields: for field in form_fields: if field.field_id == header: row_data.append(field.value) break else: row_data.append('') if row_data: dataset.append(row_data) return dataset