Пример #1
0
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 {}
Пример #2
0
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
Пример #3
0
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]},
        )
Пример #4
0
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
Пример #5
0
 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))
Пример #6
0
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)
Пример #7
0
 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']))
Пример #8
0
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
Пример #9
0
 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)
Пример #10
0
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)
Пример #11
0
    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()]
Пример #12
0
    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)
Пример #13
0
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)
Пример #14
0
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
Пример #15
0
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])
Пример #16
0
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)
Пример #17
0
def status(db):
    db.execute("SELECT 'running' AS 'status';")
    return db.fetchone()
Пример #18
0
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
Пример #19
0
# 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)
Пример #20
0
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()
Пример #21
0
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])
Пример #22
0
def _get_daily_limit() -> int:
    return db.fetchone("budget", ["daily_limit"])["daily_limit"]
Пример #23
0
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])
Пример #24
0
def status(db):
    db.execute("SELECT 'running' AS 'status';")
    return db.fetchone()
Пример #25
0
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
Пример #26
0
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)