Python DB.run_sql方法代码示例

# 需要导入模块: import DB [as 别名]
# 或者: from DB import run_sql [as 别名]
def update_prac_q_count(year, month, day, hour, qtemplate, count, avgscore):
    """ Insert a practice count for the given time/qtemplate """
    sql = """UPDATE stats_prac_q_course SET "number"=%s, "avgscore"=%s
                 WHERE hour=%s
                 AND month=%s
                 AND day=%s
                 AND year=%s
                 AND qtemplate=%s;"""
    params = (count, avgscore, hour, month, day, year, qtemplate)
    DB.run_sql(sql, params)

# 需要导入模块: import DB [as 别名]
# 或者: from DB import run_sql [as 别名]
def add_prac_q_count(year, month, day, hour, qtemplate, count, avgscore):
    """ Insert a practice count for the given time/qtemplate """
    sql = """INSERT INTO stats_prac_q_course ("qtemplate", "hour", "day",
                                              "month", "year", "number",
                                              "when", "avgscore")
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s);"""
    params = (qtemplate,  hour, day,
              month, year, count,
              datetime(year=year, hour=hour, day=day, month=month), avgscore)
    DB.run_sql(sql, params)

# 需要导入模块: import DB [as 别名]
# 或者: from DB import run_sql [as 别名]
def daily_prac_load(start_time, end_time):
    """ Return a list of daily counts of practices for the whole system """
    sql = """SELECT "year", "month", "day", sum("number") from "stats_prac_q_course"
             WHERE "when" >= %s
               AND "when" <= %s
             GROUP BY "year","month","day"
             ORDER BY "year","month","day" ASC;"""
    params = (start_time, end_time)
    res = DB.run_sql(sql, params)
    if not res:
        res = []
    data = []
    first = True
    for row in res:
        if first:  # if the data doesn't start with any values, set a 0 entry,
                   # so graphs scale correctly
            if not (int(row[1]) == start_time.month
                    and int(row[2]) == start_time.day
                    and int(row[0] == start_time.year)):
                data.append(("%04d-%02d-%02d" % (start_time.year, start_time.month, start_time.day), 0))
            first = False
        dt = datetime.strptime("%04d-%02d-%02d" % (int(row[0]), int(row[1]), int(row[2])), "%Y-%m-%d")
        data.append((dt.strftime("%Y-%m-%d"), row[3]))
    if len(res) >= 1 and not data[-1][0] == "%04d-%02d-%02d" % (end_time.year, end_time.month, end_time.day):

        data.append(("%04d-%02d-%02d" % (end_time.year, end_time.month, end_time.day), 0))
    return data

# 需要导入模块: import DB [as 别名]
# 或者: from DB import run_sql [as 别名]
def get_marks(group, exam_id):
    """ Fetch the marks for a given user group.
    """
    results = {}

    sql = """
        SELECT u.id, q.qtemplate, q.score, q.firstview, q.marktime
        FROM users AS u,
             questions AS q,
             usergroups AS ug
        WHERE u.id = ug.userid
          AND ug.groupid = %s
          AND u.id = q.student
          AND q.exam = %s;
    """
    params = (group.id, exam_id)
    ret = DB.run_sql(sql, params)
    results = {}
    for row in ret:
        user_id = row[0]
        if not user_id in results:
            results[user_id] = {}
        qtemplate = row[1]
        results[user_id][qtemplate] = {
            'score': row[2],
            'firstview': row[3],
            'marktime': row[4]
        }

    return results

# 需要导入模块: import DB [as 别名]
# 或者: from DB import run_sql [as 别名]
def prac_q_count(year, month, day, hour, qtemplate):
    """ Fetch the practice count for the given time/qtemplate or return
        False if not found. Can be used when deciding if to INSERT or UPDATE.
        Note: may return 0 if count is zero, is not same as not exist (False)
    """
    sql = """SELECT "qtemplate", "hour", "day", "month", "year", "number", "when"
                FROM stats_prac_q_course
                 WHERE hour=%s
                 AND month=%s
                 AND day=%s
                 AND year=%s
                 AND qtemplate=%s;"""
    params = (hour, month, day, year, qtemplate)
    res = DB.run_sql(sql, params)
    if not res or len(res) == 0:
        return False
    return int(res[0][0])

# 需要导入模块: import DB [as 别名]
# 或者: from DB import run_sql [as 别名]
def populate_prac_q_count(start=None, end=None):
    """  Go through the questions from start to end date and count the number of
         questions practiced. Store the results in stats_prac_q_course
         If start not given, go back to the start of the database. If end not
         given go until now.
    """
    if not end:
        end = datetime.now()
    if not start:
        start = datetime(2000, 1, 1)

    sql = """ SELECT COUNT(question) AS practices,
                EXTRACT (YEAR FROM marktime) as year,
                     EXTRACT (MONTH FROM marktime) as month,
                     EXTRACT (DAY FROM marktime) as day,
                     EXTRACT (HOUR FROM marktime) as hour,
                     qtemplate AS qtemplate,
                     AVG(score) AS avgscore
              FROM questions
              WHERE (exam = '0' or exam is null)
              AND marktime >= %s
              AND marktime <= %s
              GROUP BY
                     EXTRACT (YEAR FROM marktime),
                     EXTRACT (MONTH FROM marktime),
                     EXTRACT (DAY FROM marktime),
                     EXTRACT (HOUR FROM marktime),
                     qtemplate;
                     """
    params = (start, end)
    res = DB.run_sql(sql, params)
    if not res:
        return False
    for row in res:
        data = {
            'count': int(row[0]),
            'year': int(row[1]),
            'month': int(row[2]),
            'day': int(row[3]),
            'hour': int(row[4]),
            'qtemplate': int(row[5]),
            'avgscore': float(row[6])
        }
        exist_count = prac_q_count(data['year'],
                                   data['month'],
                                   data['day'],
                                   data['hour'],
                                   data['qtemplate'])
        if exist_count is False:  # could be 0
            add_prac_q_count(data['year'],
                             data['month'],
                             data['day'],
                             data['hour'],
                             data['qtemplate'],
                             data['count'],
                             data['avgscore'])
        else:
            update_prac_q_count(data['year'],
                                data['month'],
                                data['day'],
                                data['hour'],
                                data['qtemplate'],
                                data['count'],
                                data['avgscore'])