async def create_sheet(sheet: models.Sheet) -> models.SheetInDB: sheet = models.SheetInDB.parse_obj(sheet) sheet.clean_empty_strings() sheet.clean_tags() result = await db.sheets.insert_one(sheet.dict()) created = await db.sheets.find_one({"_id": result.inserted_id}) return models.SheetInDB.parse_obj(created)
def sheet(): id = request.args.get('id', None) if id is None: sheet = Sheet(id='null', name='', run_at='never', asheet='{"data": null}') else: sheet = Sheet.get(Sheet.id==id) if sheet.asheet is None: sheet.asheet = '{"data": null}' sheet_form = SheetForm(None, sheet) return render_template('sheet.html', current_user=current_user, form=sheet_form, sheet_id=sheet.id, sheet_name=sheet.name, sheet_run_at=sheet.run_at, sheet_updated_at=sheet.updated_at, asheet=sheet.asheet )
def sheet_delete(): flash('Sheet deletion is disabled for this demo!') return redirect(url_for('sheets.sheets_list')) id = request.args.get('id', None) if id is None: flash('Error: sheet id is missing!') else: q = Sheet.delete().where(Sheet.id==id) res = q.execute() flash('Sheet was deleted') return redirect(url_for('sheets.sheets_list'))
def sheet_delete(): flash('Sheet deletion is disabled for this demo!') return redirect(url_for('sheets.sheets_list')) id = request.args.get('id', None) if id is None: flash('Error: sheet id is missing!') else: q = Sheet.delete().where(Sheet.id == id) res = q.execute() flash('Sheet was deleted') return redirect(url_for('sheets.sheets_list'))
def sheet(): id = request.args.get('id', None) if id is None: sheet = Sheet(id='null', name='', run_at='never', asheet='{"data": null}') else: sheet = Sheet.get(Sheet.id == id) if sheet.asheet is None: sheet.asheet = '{"data": null}' sheet_form = SheetForm(None, sheet) return render_template('sheet.html', current_user=current_user, form=sheet_form, sheet_id=sheet.id, sheet_name=sheet.name, sheet_run_at=sheet.run_at, sheet_updated_at=sheet.updated_at, asheet=sheet.asheet)
def odesk_rss(run_at, query): format = '%Y/%m/%d %T' sheets = Sheet.select() for sheet in sheets: if sheet.name.lower()[0:9] != 'odesk rss': continue if sheet.run_at == 'never': continue elif sheet.run_at == run_at: processed_sheet = [] buf = StringIO(sheet.asheet) sheet_dict = json.load(buf) sheet_list = sheet_dict['data'] sheet_list = clear_sheet(sheet_list) try: feeder = feedparser.parse('https://www.odesk.com/jobs/rss?q=' + query) for posted_job in feeder.entries: new_row = [] # pubdate = str(parse_date(str(posted_job.published)).strftime(format)) # new_row.append(str(pubdate)) # new_row.append(str(posted_job.title)) # new_row.append(str(posted_job.link)) pubdate = parse_date(str( posted_job.published)).strftime(format) new_row.append(pubdate) new_row.append(posted_job.title.encode('ascii', 'ignore')) new_row.append(posted_job.link.encode('ascii', 'ignore')) # now = str(parse_date(str(datetime.utcnow())).strftime(format)) # new_row.append(str(now)) processed_sheet.append(new_row) except Exception as e: # ERROR: Error: running function: odesk_rss ... exception: 'ascii' codec can't encode character u'\u0421' in position 0: ordinal not in range(128) # a solution: ".encode('ascii', 'ignore')" as "str()" doesn't work error = "Error: running function: odesk_rss ... exception: %s" % e new_row.append(str(error)) processed_sheet.append(new_row) for ps in processed_sheet: sheet_list.append(ps) # store in db with the same format as handsontable i.e. JSON.stringify(hot.getData()): new_sheet_str = "{'data': " + str(sheet_list).replace( 'None', "").replace('null', "") + "}" new_sheet_str = new_sheet_str.replace("'", '"') sheet.asheet = new_sheet_str sheet.updated_at = datetime.utcnow() print("updated=%s name=%s" % (sheet.updated_at, sheet.name)) ssresp = sheet.save() print("sheet.save() response:") print(ssresp) print( "%s Completed '%s' autorun of oDesk RSS job: query: '%s' processed sheet '%s'" % (datetime.utcnow(), run_at, query, sheet.name))
def trending(run_at): # a sheet template for trending must be like this: # row 1 is the headers # row 2: # col 1+ are the function params (url/user) - may be multiples # trailing col's are the ?func(x2)'s to be performed # after each run row 3+ will be the results: # row 3: # col 1 is the timestamp # col 2+ are the result of each ?func call # note: without some fixed layout this would difficult to perform, also # since it's basically a report it would be ugly/unreadable if # there wasn't a layout # print("%s run_at=%s"%(datetime.utcnow(),run_at)) # return sheets = Sheet.select() for sheet in sheets: if sheet.name.lower()[0:9] == 'odesk rss': continue if sheet.run_at == 'never': continue elif sheet.run_at == run_at: processed_sheet = [] buf = StringIO(sheet.asheet) sheet_dict = json.load(buf) sheet_list = sheet_dict['data'] sheet_list = remove_empty_rows(sheet_list) for x, row in enumerate(sheet_list): # ignore the first row, x == 0 # process the second row, x == 1 # ignore all other rows/cols if (x == 0) or all_nones(row): continue elif x == 1: # only process row 2 processed_sheet.append(process_row(row, sheet_list)) else: continue for ps in processed_sheet: sheet_list.append(ps) # store in db with the same format as handsontable i.e. JSON.stringify(hot.getData()): new_sheet_str = "{'data': " + str(sheet_list).replace( 'None', '').replace('null', '') + "}" new_sheet_str = new_sheet_str.replace("'", '"') sheet.asheet = new_sheet_str sheet.updated_at = datetime.utcnow() sheet.save() print("%s Completed autorun trending job: processed sheet '%s'" % (datetime.utcnow(), sheet.name))
def odesk_rss(run_at, query): format='%Y/%m/%d %T' sheets = Sheet.select() for sheet in sheets: if sheet.name.lower()[0:9] != 'odesk rss': continue if sheet.run_at == 'never': continue elif sheet.run_at == run_at: processed_sheet = [] buf = StringIO(sheet.asheet) sheet_dict = json.load(buf) sheet_list = sheet_dict['data'] sheet_list = clear_sheet(sheet_list) try: feeder = feedparser.parse('https://www.odesk.com/jobs/rss?q=' + query) for posted_job in feeder.entries: new_row = [] # pubdate = str(parse_date(str(posted_job.published)).strftime(format)) # new_row.append(str(pubdate)) # new_row.append(str(posted_job.title)) # new_row.append(str(posted_job.link)) pubdate = parse_date(str(posted_job.published)).strftime(format) new_row.append(pubdate) new_row.append(posted_job.title.encode('ascii', 'ignore')) new_row.append(posted_job.link.encode('ascii', 'ignore')) # now = str(parse_date(str(datetime.utcnow())).strftime(format)) # new_row.append(str(now)) processed_sheet.append(new_row) except Exception as e: # ERROR: Error: running function: odesk_rss ... exception: 'ascii' codec can't encode character u'\u0421' in position 0: ordinal not in range(128) # a solution: ".encode('ascii', 'ignore')" as "str()" doesn't work error = "Error: running function: odesk_rss ... exception: %s" % e new_row.append(str(error)) processed_sheet.append(new_row) for ps in processed_sheet: sheet_list.append(ps) # store in db with the same format as handsontable i.e. JSON.stringify(hot.getData()): new_sheet_str = "{'data': " + str(sheet_list).replace('None', "").replace('null', "") + "}" new_sheet_str = new_sheet_str.replace("'", '"') sheet.asheet = new_sheet_str sheet.updated_at = datetime.utcnow() print("updated=%s name=%s"%(sheet.updated_at,sheet.name)) ssresp = sheet.save() print("sheet.save() response:") print(ssresp) print("%s Completed '%s' autorun of oDesk RSS job: query: '%s' processed sheet '%s'" % (datetime.utcnow(), run_at, query, sheet.name))
def trending(run_at): # a sheet template for trending must be like this: # row 1 is the headers # row 2: # col 1+ are the function params (url/user) - may be multiples # trailing col's are the ?func(x2)'s to be performed # after each run row 3+ will be the results: # row 3: # col 1 is the timestamp # col 2+ are the result of each ?func call # note: without some fixed layout this would difficult to perform, also # since it's basically a report it would be ugly/unreadable if # there wasn't a layout # print("%s run_at=%s"%(datetime.utcnow(),run_at)) # return sheets = Sheet.select() for sheet in sheets: if sheet.name.lower()[0:9] == 'odesk rss': continue if sheet.run_at == 'never': continue elif sheet.run_at == run_at: processed_sheet = [] buf = StringIO(sheet.asheet) sheet_dict = json.load(buf) sheet_list = sheet_dict['data'] sheet_list = remove_empty_rows(sheet_list) for x, row in enumerate(sheet_list): # ignore the first row, x == 0 # process the second row, x == 1 # ignore all other rows/cols if (x == 0) or all_nones(row): continue elif x == 1: # only process row 2 processed_sheet.append(process_row(row, sheet_list)) else: continue for ps in processed_sheet: sheet_list.append(ps) # store in db with the same format as handsontable i.e. JSON.stringify(hot.getData()): new_sheet_str = "{'data': " + str(sheet_list).replace('None', '').replace('null', '') + "}" new_sheet_str = new_sheet_str.replace("'", '"') sheet.asheet = new_sheet_str sheet.updated_at = datetime.utcnow() sheet.save() print("%s Completed autorun trending job: processed sheet '%s'" % (datetime.utcnow(), sheet.name))
def sheet_as_table(): # in a cell in a google spreadsheet do: # =IMPORTHTML("http://cellipede.com/sheet_as_table?id=2","table",1) # which will import the 1st html table found at this url: i.e. the "table",1 sheet_list = [] id = request.args.get('id', None) if id is None: return 'Error: sheet id is missing!' else: try: sheet = Sheet.get(Sheet.id==id) buf = StringIO(sheet.asheet) sheet_dict = json.load(buf) sheet_list = sheet_dict['data'] except Exception as e: return 'Error: sheet id or data is invalid!' return render_template('sheet_as_table.html', current_user=current_user, asheet=sheet_list )
def sheet_save(): id = request.form.get('id', None) # coz blank is something and not None: id = None if id == '' else id if id is None: sheet = Sheet(name='') else: sheet = Sheet.get(Sheet.id == id) name = request.form.get('name', None) # coz blank is something and not None: name = None if name == '' else name if name is None: return jsonify({'result': 'Error: a sheet name is required!'}) run_at = request.form.get('run_at', 'never') # # coz blank is something and not None: # run_at = None if run_at == '' else run_at # if run_at is None: # run_at = 'never' sheet.name = name sheet.run_at = run_at sheet.asheet = """{"data": """ + request.form.get('asheet', None) + "}" sheet.updated_at = datetime.datetime.utcnow() sheet.save() return jsonify({'result': 'ok'})
def sheet_as_table(): # in a cell in a google spreadsheet do: # =IMPORTHTML("http://cellipede.com/sheet_as_table?id=2","table",1) # which will import the 1st html table found at this url: i.e. the "table",1 sheet_list = [] id = request.args.get('id', None) if id is None: return 'Error: sheet id is missing!' else: try: sheet = Sheet.get(Sheet.id == id) buf = StringIO(sheet.asheet) sheet_dict = json.load(buf) sheet_list = sheet_dict['data'] except Exception as e: return 'Error: sheet id or data is invalid!' return render_template('sheet_as_table.html', current_user=current_user, asheet=sheet_list)
def sheet_save(): id = request.form.get('id', None) # coz blank is something and not None: id = None if id == '' else id if id is None: sheet = Sheet(name='') else: sheet = Sheet.get(Sheet.id==id) name = request.form.get('name', None) # coz blank is something and not None: name = None if name == '' else name if name is None: return jsonify({'result': 'Error: a sheet name is required!'}) run_at = request.form.get('run_at', 'never') # # coz blank is something and not None: # run_at = None if run_at == '' else run_at # if run_at is None: # run_at = 'never' sheet.name = name sheet.run_at = run_at sheet.asheet = """{"data": """ + request.form.get('asheet', None) + "}" sheet.updated_at = datetime.datetime.utcnow() sheet.save() return jsonify({'result': 'ok'})
async def update_sheet(old_sheet: models.SheetWithVersions, new_sheet: models.Sheet) -> models.SheetInDB: if new_sheet.sheet_id == old_sheet.sheet_id: raise DuplicateID() new_sheet = models.SheetInDB.parse_obj(new_sheet) old_sheet.current = False old_sheet.clean_empty_strings() old_sheet.clean_tags() await db.sheets.find_one_and_replace({"sheet_id": old_sheet.sheet_id}, old_sheet.dict()) new_sheet.prev_versions = [] new_sheet.prev_versions.append( (old_sheet.sheet_id, datetime.datetime.now())) if old_sheet.prev_versions: new_sheet.prev_versions.extend(old_sheet.prev_versions) new_sheet.clean_empty_strings() new_sheet.clean_tags() result = await db.sheets.insert_one(new_sheet.dict()) created = await db.sheets.find_one({"_id": result.inserted_id}) return models.SheetInDB.parse_obj(created)
def db_create(): db.connect() Sheet.drop_table(True) Sheet.create_table(True) db.close()
def sheets_list(): sheets = Sheet.select() return render_template('sheets_list.html', current_user=current_user, sheets=sheets)