def upload_feedback(db):
"""Upload feedback"""
upload = request.files.get("feedback")
reader = csv.DictReader(codecs.iterdecode(upload.file, "utf-8"))
for fb in reader:
# check to see if we already have it
db.execute("""
select id
from post P
where P.onyen = %(onyen)s and P.key = %(key)s""",
fb,
)
fb["time"] = timestring.Date(fb["time"]).date
existing = db.fetchone()
if not existing:
db.execute("""
insert into post (time, onyen, key, ip)
values (%(time)s, %(onyen)s, %(key)s, '')
returning id""",
fb)
fb["postid"] = db.fetchone()[0]
else:
fb["postid"] = existing[0]
db.execute("""
insert into feedback (postid, score, msg)
values (%(postid)s, %(score)s, %(msg)s)
on conflict (postid) do update
set score = %(score)s, msg = %(msg)s""",
fb)
return {}
def get_ege_content_hard(ege_content_id: int) -> EgeContentHard:
""" Возвращает тяжелый контент с выгрузкой из БД blob_data """
ege_content_row = db.fetchone("ege_content", [
"id", "tasks_id", "content_type", "blob_data", "telegram_file_id",
"file_name", "description", "file_extension", "mime_type"
],
where=f"WHERE id={ege_content_id}")
row_id = ege_content_row[0]
task_id = ege_content_row[1]
content_type = ege_content_row[2]
blob_data = ege_content_row[3]
telegram_file_id = ege_content_row[4]
file_name = ege_content_row[5]
description = ege_content_row[6]
file_extension = ege_content_row[7]
mime_type = ege_content_row[8]
file_fullname = file_name + file_extension
file_full_path = _write_file_from_blob(blob_data, file_fullname)
ege_content_hard = EgeContentHard(
id=row_id,
task_id=task_id,
content_type=content_type,
blob_data=blob_data,
telegram_file_id=telegram_file_id,
description=description if description else file_fullname,
file_fullname=file_fullname,
mime_type=mime_type,
file_full_path=file_full_path)
return ege_content_hard
def main(db=None):
"""Process questions"""
# process all the content to get answers and codes
paths = []
for folder in assessment_folders:
paths.extend(glob(f"content/{folder}/*.tmd"))
allCodes = {}
for path in paths:
if args.verbose:
print(f"Processing {path}")
key, problems, codes, total, count, info = getAnswers(path)
mtime = datetime.fromtimestamp(osp.getmtime(path))
# scream on code conflict
for code in codes:
if code in allCodes:
print(code, path)
assert code not in allCodes
allCodes[code] = codes[code]
if total > 0 and args.verbose:
print(f"{key}: total {total}/count {count}")
# see if we need to update the db
db.execute("""select time from problems where key = %s""", [key])
row = db.fetchone()
if row is None or row.time < mtime or args.force:
updateProblems(db, key, problems, info, mtime)
# update the codes in the db
for code in allCodes:
db.execute(
"""insert into codes (time, code)
values (%(expires)s, %(code)s)
on conflict (code) do
update set time=%(expires)s""",
{"code": code, "expires": allCodes[code]},
)
def findSensor(userid, deviceid, sensorid):
sql_select = '''Select state,value from devices
where userID=? and deviceID=? and sensorID=?'''
r = db.fetchone(
db.get_conn(DBPath),
'''Select state from devices where deviceID=? and userID=? and sensorID=?''',
(userid, deviceid, sensorid))
return r
def add_item(self, item_no):
db.execute("""
INSERT INTO line_items
(quote_no, item_no)
VALUES (%s, %s)
RETURNING line_id;""",
(self.quote_no, item_no))
line_id = db.fetchone()['line_id']
self.items.append(QuoteLineItem(line_id))
def inputindex(input):
"""Handler for showing keyboard or mouse page with day and total links."""
stats = {}
countminmax = "SUM(count) AS count, MIN(day) AS first, MAX(day) AS last"
tables = ("moves", "clicks", "scrolls") if "mouse" == input else ("keys", "combos")
for table in tables:
stats[table] = db.fetchone("counts", countminmax, type=table)
stats[table]["days"] = db.fetch("counts", order="day DESC", type=table)
return bottle.template("input.tpl", locals(), conf=conf)
def add_option(self, option_no, length=None, width=None, height=None, qty=None):
db.execute("""
INSERT INTO line_item_feature_options
(line_item_feature_id, option_no, length, width, height, qty)
VALUES
(%s, %s, %s, %s, %s, %s)
RETURNING line_item_feature_option_id;""",
(self.line_item_feature_id, option_no, length, width, height, qty))
self.options.append(QuoteLineItemFeatureOption(db.fetchone()['line_item_feature_option_id']))
def user_in_roll(user, db):
''' Return boolean if user is in the class roll '''
db.execute('''
select exists (select onyen
from roll
where onyen = %s and section in ('001', '002', '003'))''',
[user],
)
return db.fetchone().exists
def __init__(self, line_item_feature_option_id):
db.execute("""
SELECT * FROM line_item_feature_options
WHERE line_item_feature_option_id = %s;""",
(line_item_feature_option_id, ))
result = db.fetchone()
for k, v in result.items():
setattr(self, k, v)
super().__init__(self.option_no)
def index():
"""Handler for showing the GUI index page."""
stats = dict((k, {"count": 0}) for k, tt in conf.InputTables)
countminmax = "SUM(count) AS count, MIN(day) AS first, MAX(day) AS last"
for input, table in [(x, t) for x, tt in conf.InputTables for t in tt]:
row = db.fetchone("counts", countminmax, type=table)
if not row["count"]: continue # for input, table
stats[input]["count"] += row["count"]
for func, key in [(min, "first"), (max, "last")]:
stats[input][key] = (row[key] if key not in stats[input]
else func(stats[input][key], row[key]))
return bottle.template("index.tpl", locals(), conf=conf)
def __init__(self, quote_no=None):
self.items = []
self.projectName = "TEST PROJECT"
if not quote_no:
db.execute("""
INSERT INTO quotes(field)
VALUES ('test')
RETURNING quote_no;""")
self.quote_no = str(db.fetchone()['quote_no'])
else:
db.execute("""
SELECT * FROM quotes
WHERE quote_no = %s;""",
(quote_no, ))
for k, v in db.fetchone().items():
setattr(self, k, v)
self.quote_no = str(self.quote_no)
db.execute("""
SELECT * FROM line_items
WHERE quote_no = %s;""",
(self.quote_no,))
self.items = [QuoteLineItem(x['line_id']) for x in db.fetchall()]
def __init__(self, line_item_feature_id):
db.execute("""
SELECT * FROM line_item_features
WHERE line_item_feature_id = %s;""",
(line_item_feature_id, ))
result = db.fetchone()
for k, v in result.items():
setattr(self, k, v)
db.execute("""
SELECT line_item_feature_option_id FROM line_item_feature_options
WHERE line_item_feature_id = %s;""",
(line_item_feature_id, ))
self.options = [QuoteLineItemFeatureOption(x['line_item_feature_option_id']) for x in db.fetchall()]
super().__init__(self.feature_no)
def inputindex(input):
"""Handler for showing keyboard or mouse page with day and total links."""
stats = {}
countminmax = "SUM(count) AS count, MIN(day) AS first, MAX(day) AS last"
tables = ("moves", "clicks", "scrolls") if "mouse" == input else ("keys", "combos")
for table in tables:
stats[table] = db.fetchone("counts", countminmax, type=table)
periods, month = [], None
for data in db.fetch("counts", "day AS period, count, 'day' AS class", order="day DESC", type=table):
if not month or month["period"][:7] != data["period"][:7]:
month = {"class": "month", "period": data["period"][:7], "count": 0}
periods.append(month)
month["count"] += data["count"]
periods.append(data)
stats[table]["periods"] = periods
dbinfo = stats_db(conf.DbPath)
return bottle.template("input.tpl", locals(), conf=conf)
def get_ege_content_lite(ege_content_id: int) -> EgeContentLite:
""" Возвращает контент без выгрузки из БД blob_data """
ege_content_row = db.fetchone("ege_content", [
"id", "tasks_id", "content_type", "telegram_file_id", "file_name",
"description", "file_extension"
],
where=f"WHERE id={ege_content_id}")
row_id = ege_content_row[0]
task_id = ege_content_row[1]
content_type = ege_content_row[2]
telegram_file_id = ege_content_row[3]
file_name = ege_content_row[4]
description = ege_content_row[5]
file_extension = ege_content_row[6]
ege_content_lite = EgeContentLite(
id=row_id,
task_id=task_id,
content_type=content_type,
telegram_file_id=telegram_file_id,
description=description if description else file_name + file_extension)
return ege_content_lite
def get_dict(dict_id: int) -> Dictation:
""" Получаем диктант """
row = db.fetchone("dictations", ["themes_id", "dictation"],
where=f"where id={dict_id}")
return Dictation(id=dict_id, themes_id=row[0], dictation=row[1])
def products(asin):
conn = db.get_conn()
product = db.fetchone(
conn, """
select *
from products p
where asin = {asin}
""".format(asin=db.quote(asin)))
q = request.args.get("q", '')
topic = request.args.get("topic", '')
filters = "where r.asin = {}".format(db.quote(asin))
if q:
query = ' & '.join(re.split('\s+', q.strip()))
filters += """ and to_tsvector('english', summary || ' ' || "reviewText") @@ to_tsquery('english', {query})
""".format(query=db.quote(query))
if topic:
filters = 'join review_topics t on t.asin = r.asin and t."reviewerID" = r."reviewerID" ' + filters
filters += """ and t.topic = {}""".format(db.quote(topic))
reviews_best = db.fetchall(
conn, """
select *
from reviews r
{filters}
and sentiment = 1
limit 10
""".format(filters=filters))
reviews_worst = db.fetchall(
conn, """
select *
from reviews r
{filters}
and sentiment = 0
limit 10
""".format(filters=filters))
topics_sentiment = db.fetchall(
conn, """
select
topic,
sentiment,
count(*)
from review_topics t
left join reviews r on t.asin = r.asin and t."reviewerID" = r."reviewerID"
where t.asin = {asin}
group by topic, sentiment
""".format(asin=db.quote(asin)))
reviews_time = db.fetchall(
conn, """
select
sentiment,
(cast(extract(year from "reviewTime") as text) || '-' || cast(extract(month from "reviewTime") as text)) as t,
count(*)
from reviews r
where r.asin = {asin}
group by sentiment, t
""".format(asin=db.quote(asin)))
topic_names = set([t[0] for t in topics_sentiment])
def sort_key(row):
t = row[1].split('-')
return (int(t[0]), int(t[1]))
reviews_time = sorted(reviews_time, key=sort_key)
return render_template('product.html',
topics_sentiment=topics_sentiment,
product=product,
reviews_best=reviews_best,
reviews_worst=reviews_worst,
topic_names=topic_names,
q=q,
topic=topic,
reviews_time=reviews_time)
def status(db):
db.execute("SELECT 'running' AS 'status';")
return db.fetchone()
def is_trade_day(date):
r = db.fetchone("SELECT isopen FROM ts_trade_cal WHERE calendar='%s'" %
date)
return True if r[0] == 1 else False
# create conncetion object
conn = db.connect('test.db')
db.version(conn)
# insert statement
query = "INSERT INTO student (name,rollnumber,age) VALUES ('Suresh',1004,29)"
db.insert(conn, query)
# select multiple records
query = "SELECT * from student"
result = db.fetchall(conn, query)
for r in result:
print(r)
# select one record
query = "SELECT * from student where id = 1"
result = db.fetchone(conn, query)
print(result)
# update a record
query = "UPDATE student SET age=30 WHERE id=2"
db.update(conn, query)
# delete a record
query = "DELETE from student WHERE id = 3"
db.delete(conn, query)
# close connection
db.close(conn)
def next_trade_day(date, n=1):
r = db.fetchone(
"SELECT calendar FROM ts_trade_cal WHERE calendar>='%s' AND isopen=1 ORDER BY calendar ASC OFFSET %d LIMIT 1"
% (date, n))
return datetime.datetime.strptime(r[0], "%Y-%m-%d").date()
def get_theme(theme_id: int) -> Theme:
""" Получает тему """
row = db.fetchone("themes", ["id", "themes_grade_number", "theme_name"],
where=f"where id={theme_id}")
return Theme(id=row[0], themes_grade_number=row[1], theme_name=row[2])
def _get_daily_limit() -> int:
return db.fetchone("budget", ["daily_limit"])["daily_limit"]
def getValue(sensorid, top=20):
sql_select = '''select * from readOnlyData
where SensorID=? order by id desc
Limit ?
'''
return db.fetchone(db.get_conn(DBPath), sql_select, [sensorid, top])
def status(db):
db.execute("SELECT 'running' AS 'status';")
return db.fetchone()
def parse(file_name, user, agenda_type, db):
"""Main routine called by home.tmd to parse agenda.tmd"""
db.execute('''select section from roll where onyen=%(onyen)s''',
dict(onyen=user))
row = db.fetchone()
section = None if row is None else row.section
# Get Recitation zoom
db.execute("""select url from zoom where type='recitation'""")
row = db.fetchone()
recitation_zoom_url = row.url if row else None
# Get lecture zoom
lecture_zoom_urls = []
if section in ['001', '003']:
db.execute(
"""select url from zoom where type='lecture' and section='001'""")
lecture_zoom_urls.append(db.fetchone().url)
if section in ['002', '003']:
db.execute(
"""select url from zoom where type='lecture' and section='002'""")
lecture_zoom_urls.append(db.fetchone().url)
# Get checklist information
checklist_info = get_checklist_info(db, user, agenda_type)
if agenda_type == 'la':
first_day_of_class = date(2021, 1, 12)
else:
first_day_of_class = date(2021, 1, 19)
last_day_of_classes = date(2021, 5, 5)
today = date.today()
with open(file_name, "rt") as fp:
agenda = fp.read().split("\n| ")
day = first_day_of_class
result = []
for one_days_details in agenda:
lines = one_days_details.split("\n")
title = lines[0]
output_lines = []
for line in lines[1:]:
if line.startswith("S "):
line = slide_line(line)
elif line.startswith("#"):
line = comment_line(line, user)
elif line.startswith("Z"):
line = zoom_line(line, day, section, lecture_zoom_urls,
recitation_zoom_url)
elif line.startswith("CL"):
line = checklist_line(line, day, checklist_info)
output_lines.append(line)
when = set_when(day, today)
result.append({
"date":
day,
"title":
title,
"when":
when,
"body":
renderMarkdown(renderTemplate("\n".join(output_lines)))
})
day = increment_day(day, last_day_of_classes, result, agenda_type)
return result
import db
# to print the version
conn =db.connect("sample.db")
# query1 = "select sqlite_version()"
# print(db.execute(conn,query1))
# query2 = "SELECT * FROM student"
# print(db.fetchall(conn,query2))
# query4 = "INSERT INTO student (name,rollnumber,age) VALUES ('Naveen',1006,45)"
# db.insert(conn,query4)
query2 = "SELECT * FROM student where id = 5"
print(db.fetchone(conn,query2))
# query5 ="UPDATE student SET age=50 WHERE id=5"
# db.update(conn,query5)
# query3 = "SELECT * FROM student where id = 5"
# print(db.fetchone(conn,query3))
# query5 = "DELETE FROM student WHERE id =5"
# db.delete(conn,query5)
