Python DB.execute方法代码示例

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import execute [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 execute [as 别名]
 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 ...

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import execute [as 别名]
    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)

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import execute [as 别名]
 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"))

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import execute [as 别名]
    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

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import execute [as 别名]
    def setSelect(self, v):
        if v == 0 or v == 1:
            self.diff_eval = lambda x: 1
            self.nextText()
            return

        hist = time.time() - 86400.0 * Settings.get("history")
        tri = dict(
            DB.execute(
                """
                    select data,agg_median(time) as wpm from statistic
                    where w >= ? and type = 1
                    group by data""",
                (hist,),
            ).fetchall()
        )  # [(t, (m, c)) for t, m, c in

        g = tri.values()
        if len(g) == 0:
            return lambda x: 1
        g.sort(reverse=True)
        expect = g[len(g) // 4]

        def _func(v):
            text = v[2]
            v = 0
            s = 0.0
            for i in xrange(0, len(text) - 2):
                t = text[i : i + 3]
                if t in tri:
                    s += tri[t]
                else:
                    # print "|", t,
                    s += expect
                    v += 1
            avg = s / (len(text) - 2)
            # print text
            # print " v=%d,s=%f" % (v, 12.0/avg), "ex:", expect
            return 12.0 / avg

        self.diff_eval = _func
        self.nextText()

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import execute [as 别名]
    def done(self):
        now = time.time()
        elapsed, chars, times, mis, mistakes = self.typer.getStats()

        assert chars == len(self.text[2])

        accuracy = 1.0 - len(filter(None, mis)) / chars
        spc = elapsed / chars
        
        viscosity = sum(map(lambda x: ((x-spc)/spc)**2, times)) / chars

        DB.execute('insert into result (w,text_id,source,wpm,accuracy,viscosity) values (?,?,?,?,?,?)',
                   (now, self.text[0], self.text[1], 12.0/spc, accuracy, viscosity))

        v2 = DB.fetchone("""select agg_median(wpm),agg_median(acc) from
            (select wpm,100.0*accuracy as acc from result order by w desc limit %d)""" % Settings.get('def_group_by'), (0.0, 100.0))
        self.result.setText("Last: %.1fwpm (%.1f%%), last 10 average: %.1fwpm (%.1f%%)"
            % ((12.0/spc, 100.0*accuracy) + v2))

        self.emit(SIGNAL("statsChanged"))

        stats = collections.defaultdict(Statistic)
        visc = collections.defaultdict(Statistic)
        text = self.text[2]

        for c, t, m in zip(text, times, mis):
            stats[c].append(t, m)
            visc[c].append(((t-spc)/spc)**2)

        def gen_tup(s, e):
            perch = sum(times[s:e])/(e-s)
            visc = sum(map(lambda x: ((x-perch)/perch)**2, times[s:e]))/(e-s)
            return (text[s:e], perch, len(filter(None, mis[s:e])), visc)

        for tri, t, m, v in [gen_tup(i, i+3) for i in xrange(0, chars-2)]:
            stats[tri].append(t, m > 0)
            visc[tri].append(v)

        regex = re.compile(r"(\w|'(?![A-Z]))+(-\w(\w|')*)*")

        for w, t, m, v in [gen_tup(*x.span()) for x in regex.finditer(text) if x.end()-x.start() > 3]:
            stats[w].append(t, m > 0)
            visc[w].append(v)

        def type(k):
            if len(k) == 1:
                return 0
            elif len(k) == 3:
                return 1
            return 2

        vals = []
        for k, s in stats.iteritems():
            v = visc[k].median()
            vals.append( (s.median(), v*100.0, now, len(s), s.flawed(), type(k), k) )

        is_lesson = DB.fetchone("select discount from source where rowid=?", (None,), (self.text[1], ))[0]

        if Settings.get('use_lesson_stats') or not is_lesson:
            DB.executemany_('''insert into statistic
                (time,viscosity,w,count,mistakes,type,data) values (?,?,?,?,?,?,?)''', vals)
            DB.executemany_('insert into mistake (w,target,mistake,count) values (?,?,?,?)',
                    [(now, k[0], k[1], v) for k, v in mistakes.iteritems()])

        if is_lesson:
            mins = (Settings.get("min_lesson_wpm"), Settings.get("min_lesson_acc"))
        else:
            mins = (Settings.get("min_wpm"), Settings.get("min_acc"))

        if 12.0/spc < mins[0] or accuracy < mins[1]/100.0:
            self.setText(self.text)
        elif not is_lesson and Settings.get('auto_review'):
            ws = filter(lambda x: x[5] == 2, vals)
            if len(ws) == 0:
                self.emit(SIGNAL("wantText"))
                return
            ws.sort(key=lambda x: (x[4],x[0]), reverse=True)
            i = 0
            while ws[i][4] != 0:
                i += 1
            i += (len(ws) - i) // 4

            self.emit(SIGNAL("wantReview"), map(lambda x:x[6], ws[0:i]))
        else:
            self.emit(SIGNAL("wantText"))

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import execute [as 别名]
 def enableAll(self):
     DB.execute("update text set disabled = null where disabled is not null")
     self.update()

# 需要导入模块: from Data import DB [as 别名]
# 或者: from Data.DB import execute [as 别名]
 def removeDisabled(self):
     DB.execute("delete from text where disabled is not null")
     self.removeUnused()
     self.update()
     DB.commit()