Python DB.fetchall方法代码示例

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import fetchall [as 别名]
    def populateData(self, idxs):
        if len(idxs) == 0:
            return map(
                list,
                DB.fetchall(
                    """
            select s.rowid,s.name,t.count,r.count,r.wpm,ifelse(nullif(t.dis,t.count),'No','Yes')
                    from source as s
                    left join (select source,count(*) as count,count(disabled) as dis from text group by source) as t
                        on (s.rowid = t.source)
                    left join (select source,count(*) as count,avg(wpm) as wpm from result group by source) as r
                        on (t.source = r.source)
                    where s.disabled is null
                    order by s.name"""
                ),
            )

        if len(idxs) > 1:
            return []

        r = self.rows[idxs[0]]

        return map(
            list,
            DB.fetchall(
                """select t.rowid,substr(t.text,0,40)||"...",length(t.text),r.count,r.m,ifelse(t.disabled,'Yes','No')
                from (select rowid,* from text where source = ?) as t
                left join (select text_id,count(*) as count,agg_median(wpm) as m from result group by text_id) as r
                    on (t.id = r.text_id)
                order by t.rowid""",
                (r[0],),
            ),
        )

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import fetchall [as 别名]
    def update(self):
        self.progress_.show()
        n_text = DB.fetchone("""select count(*) from text""", (0,))[0]
        self.progress_.inc(2)
        n_res = DB.fetchone("""select count(*) from result""", (0,))[0]
        self.progress_.inc(2)
        n_words = DB.fetchall(
            """select count(*),sum(count) from statistic
            group by type order by type"""
        )
        self.progress_.inc(2)
        if len(n_words) != 3:
            n_words = [(0, 0), (0, 0), (0, 0)]
        n_first = DB.fetchone("""select w from result order by w asc limit 1""", (time.time(),))[0]
        self.progress_.hide()

        self.stats_.setText(
            locale.format_string(
                """Texts: %d
Results: %d
Analysis data: %d (%d keys, %d trigrams, %d words)
  %d characters and %d words typed total\n"""
                + ("First result was %.2f days ago.\n" % ((time.time() - n_first) / 86400.0)),
                tuple(
                    [n_text, n_res, sum(map(lambda x: x[0], n_words))]
                    + map(lambda x: x[0], n_words)
                    + [n_words[0][1], n_words[2][1]]
                ),
                True,
            )
        )

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import fetchall [as 别名]
    def cleanup(self):
        day = 24*60*60
        now = time.time()
        q = []

        self.progress_.show()
        for grp, lim in [(30.0, Settings.get('group_month')),
                (7.0, Settings.get('group_week')),
                (1.0, Settings.get('group_day'))]:

            w = now - day*lim
            g = grp * day
            q.extend(DB.fetchall('''
                select avg(w), data, type, agg_mean(time, count), sum(count), sum(mistakes), agg_median(viscosity)
                from statistic where w <= %f
                group by data, type, cast(w/%f as int)''' % (w, g)))
            self.progress_.inc()

            DB.execute('''delete from statistic where w <= ?''', (w, ))
            self.progress_.inc()

        DB.executemany('''insert into statistic (w, data, type, time, count, mistakes, viscosity)
            VALUES (?, ?, ?, ?, ?, ?, ?)''', q)
        self.progress_.inc()
        DB.execute('vacuum')
        self.progress_.inc()
        DB.commit()
        self.progress_.hide()

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import fetchall [as 别名]
 def addFromTyped(self):
     words = [
         x[0]
         for x in DB.fetchall(
             "select distinct data from statistic where type = 2 order by random()"
         )
     ]
     self.filterWords(words)

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import fetchall [as 别名]
    def updateData(self, *args):
        if self.editflag:
            return
        where = []
        if self.cb_source.currentIndex() <= 0:
            pass
        elif self.cb_source.currentIndex() == 1:  # last text
            where.append(
                "r.text_id = (select text_id from result order by w desc limit 1)"
            )
        elif self.cb_source.currentIndex() == 2:  # all texts
            where.append("s.discount is null")
        elif self.cb_source.currentIndex() == 3:  # all lessons texts
            where.append("s.discount is not null")
        else:
            s = self.cb_source.itemData(self.cb_source.currentIndex())
            where.append("r.source = %d" % s.toInt()[0])

        if len(where) > 0:
            where = "where " + " and ".join(where)
        else:
            where = ""

        g = Settings.get("perf_group_by")
        if g == 0:  # no grouping
            sql = """select text_id,w,s.name,wpm,100.0*accuracy,viscosity
                from result as r left join source as s on (r.source = s.rowid)
                %s %s
                order by w desc limit %d"""
        elif g:
            sql = """select agg_first(text_id),avg(r.w) as w,count(r.rowid) || ' result(s)',agg_median(r.wpm),
                        100.0*agg_median(r.accuracy),agg_median(r.viscosity)
                from result as r left join source as s on (r.source = s.rowid)
                %s %s
                order by w desc limit %d"""

        group = ""
        if g == 1:  # by Settings.get('def_group_by')
            DB.resetCounter()
            gn = Settings.get("def_group_by")
            if gn <= 1:
                gn = 1
            group = "group by cast(counter()/%d as int)" % gn
        elif g == 2:  # by sitting
            mis = Settings.get("minutes_in_sitting") * 60.0
            DB.resetTimeGroup()
            group = "group by time_group(%f, r.w)" % mis
        elif g == 3:  # by day
            group = "group by cast((r.w+4*3600)/86400 as int)"

        n = Settings.get("perf_items")

        sql = sql % (where, group, n)

        self.model.setData(map(list, DB.fetchall(sql)))
        self.updateGraph()

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import fetchall [as 别名]
    def doubleClicked(self, idx):
        p = idx.parent()
        if not p.isValid():
            return

        q = self.model.data(idx, Qt.UserRole)
        v = DB.fetchall("select id,source,text from text where rowid = ?", (q[0],))

        self.cur = v[0] if len(v) > 0 else self.defaultText
        self.emit(SIGNAL("setText"), self.cur)
        self.emit(SIGNAL("gotoText"))

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import fetchall [as 别名]
    def refreshSources(self):
        self.editflag = True
        self.cb_source.clear()
        self.cb_source.addItem("<ALL>")
        self.cb_source.addItem("<LAST TEXT>")
        self.cb_source.addItem("<ALL TEXTS>")
        self.cb_source.addItem("<ALL LESSONS>")

        for id, v in DB.fetchall('select rowid,abbreviate(name,30) from source order by name'):
            self.cb_source.addItem(v, QVariant(id))
        self.editflag = False

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import fetchall [as 别名]
    def update(self, *arg):

        ord = Settings.get('ana_which')
        cat = Settings.get('ana_what')
        limit = Settings.get('ana_many')
        count = Settings.get('ana_count')
        hist = time.time() - Settings.get('history') * 86400.0

        sql = """select data,12.0/time as wpm,
            100.0-100.0*misses/cast(total as real) as accuracy,
            viscosity,total,misses,
            total*time*time*(1.0+misses/total) as damage
                from
                    (select data,agg_median(time) as time,agg_median(viscosity) as viscosity,
                    sum(count) as total,sum(mistakes) as misses
                    from statistic where w >= ? and type = ? group by data)
                where total >= ?
                order by %s limit %d""" % (ord, limit)

        self.model.setData(DB.fetchall(sql, (hist, cat, count)))