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)
def sheets_list():
sheets = Sheet.select()
return render_template('sheets_list.html', current_user=current_user, sheets=sheets)