Python Data.DB类代码示例

    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,
            )
        )

 def removeSelected(self):
     cats, texts = self.getSelected()
     DB.executemany("delete from text where rowid = ?",
                    map(lambda x:(x, ), texts))
     self.removeUnused()
     self.update()
     DB.commit()

    def nextText(self):

        type = Settings.get('select_method')

        if type != 1:
            # Not in order
            v = DB.execute("select id,source,text from text where disabled is null order by random() limit %d" % Settings.get('num_rand')).fetchall()
            if len(v) == 0:
                v = None
            elif type == 2:
                v = min(v, key=self.diff_eval)
            elif type == 3:
                v = max(v, key=self.diff_eval)
            else:
                v = v[0] # random, just pick the first
        else:
            # Fetch in order
            lastid = (0,)
            g = DB.fetchone("""select r.text_id
                from result as r left join source as s on (r.source = s.rowid)
                where (s.discount is null) or (s.discount = 1) order by r.w desc limit 1""", None)
            if g is not None:
                lastid = DB.fetchone("select rowid from text where id = ?", lastid, g)
            v = DB.fetchone("select id,source,text from text where rowid > ? and disabled is null order by rowid asc limit 1", None, lastid)

        if v is None:
            v = self.defaultText

        self.emit(SIGNAL("setText"), v)

    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],),
            ),
        )

    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()

    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()

    def setImpList(self, files):
        self.sender().hide()
        self.progress.show()
        for x in map(unicode, files):
            self.progress.setValue(0)
            fname = path.basename(x)
            lm = LessonMiner(x)
            self.connect(lm, SIGNAL("progress(int)"), self.progress.setValue)
            self.addTexts(fname, lm, update=False)

        self.progress.hide()
        self.update()
        DB.commit()

 def addTexts(self, source, texts, lesson=None, update=True):
     id = DB.getSource(source, lesson)
     r = []
     for x in texts:
         h = hashlib.sha1()
         h.update(x.encode('utf-8'))
         txt_id = h.hexdigest()
         dis = 1 if lesson == 2 else None
         try:
             DB.execute("insert into text (id,text,source,disabled) values (?,?,?,?)",
                        (txt_id, x, id, dis))
             r.append(txt_id)
         except Exception, e:
             pass # silently skip ...

 def newReview(self, review):
     q = self.addTexts("<Reviews>", [review], lesson=2, update=False)
     if q:
         v = DB.fetchone("select id,source,text from text where id = ?", self.defaultText, q)
         self.emit(SIGNAL("setText"), v)
     else:
         self.nextText()

 def getStats(self):
     if self.when[0] == -1:
         t = self.times[1:]
         t.sort(reverse=True)
         v = DB.fetchone('select time from statistic where type = 0 and data = ? order by rowid desc limit 1', (t[len(t)//5], ), (self.target[0], ))
         self.times[0] = v[0]
         self.when[0] = self.when[1] - self.times[0]
     return self.when[self.where]-self.when[0], self.where, self.times, self.mistake, self.getMistakes()

 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)

 def removeUnused(self):
     DB.execute('''
         delete from source where rowid in (
             select s.rowid from source as s
                 left join result as r on (s.rowid=r.source)
                 left join text as t on (t.source=s.rowid)
             group by s.rowid
             having count(r.rowid) = 0 and count(t.rowid) = 0
         )''')
     DB.execute('''
         update source set disabled = 1 where rowid in (
             select s.rowid from source as s
                 left join result as r on (s.rowid=r.source)
                 left join text as t on (t.source=s.rowid)
             group by s.rowid
             having count(r.rowid) > 0 and count(t.rowid) = 0
         )''')
     self.emit(SIGNAL("refreshSources"))

    def doubleClicked(self, idx):
        r = self.model.rows[idx.row()]

        v = DB.fetchone('select id,source,text from text where id = ?', None, (r[0], ))
        if v == None:
            return # silently ignore

        self.emit(SIGNAL("setText"), v)
        self.emit(SIGNAL("gotoText"))

    def addTexts(self, source, texts, lesson=None, update=True):
        id = DB.getSource(source, lesson)

        r = []
        for x in texts:
            x = re.sub(Settings.get('sentence_strip'), ' ', x)
            h = hashlib.sha1()
            h.update(x.encode('utf-8'))
            txt_id = h.hexdigest()
            dis = 1 if lesson == 2 else None
            try:
                DB.execute("insert into text (id, text, source, disabled) values (?, ?, ?, ?)", (txt_id, x, id, dis))
            except Exception:
                pass # silently skip ...
        r.append(txt_id)
        if update:
            self.update()
        if lesson:
            DB.commit()
        return r

    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"))