3 # Rec10 TS Recording Tools
4 # Copyright (C) 2009-2010 Yukikaze
9 from decimal import Decimal
16 def __init__(self, dbname, user, passwd, host="localhost", port=3306):
17 warnings.filterwarnings('ignore', "Data truncated for column")
21 self.dbpasswd = passwd
24 con = MySQLdb.connect(user=user, passwd=passwd)
26 cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8")
31 db = self.connect_db()
34 CREATE TABLE timeline \
36 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
42 deltatime VARCHAR(5),\
45 epgtitle VARCHAR(100),\
48 epgduplicate TINYINT DEFAULT 0,\
49 epgchange TINYINT DEFAULT 0,\
50 UNIQUE uni (type,chtxt,title,btime,deltaday)\
55 self.new_epg_timeline("")
56 self.new_in_timeline_log()
57 self.new_in_auto_bayes_key()
58 self.new_in_auto_jbk_key()
60 self.new_in_settings()
61 self.new_auto_timeline_bayes()
62 self.new_auto_timeline_keyword()
67 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
69 cur.execute('set names utf8;')
71 def close_db(self, db):
75 db = self.connect_db()
77 db[1].execute('drop table epg_ch')
85 ontv VARCHAR(30) PRIMARY KEY,\
97 def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
98 db = self.connect_db()
101 VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s)', \
102 (bctype, ontv, chtxt, ch, csch, updatetime, "1","1"))
104 def select_by_ontv_epg_ch(self, ontv):
105 db = self.connect_db()
106 dbexe = db[1].execute("\
107 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
114 dls = db[1].fetchall()
118 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
121 def select_by_chtxt_epg_ch(self, chtxt):
122 db = self.connect_db()
123 dbexe = db[1].execute("\
124 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
131 dls = db[1].fetchall()
135 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
138 def select_by_bctype_epg_ch(self, bctype):
139 db = self.connect_db()
140 dbexe = db[1].execute("\
141 SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
143 WHERE bctype = %s", \
148 dls = db[1].fetchall()
151 #recdblist.printutf8(dl)
153 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
157 def select_by_ch_epg_ch(self, ch):
158 db = self.connect_db()
159 dbexe = db[1].execute("\
161 bctype,ontv,chtxt,ch,csch,updatetime \
168 dls = db[1].fetchall()
172 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
175 def select_all_epg_ch(self):
176 db = self.connect_db()
177 dbexe = db[1].execute("\
178 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
184 dls = db[1].fetchall()
188 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
191 def set_new_status(self,dhour):
192 db = self.connect_db()
193 dbexe = db[1].execute("UPDATE epg_ch \
197 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
202 def select_get_update_epg_ch(self, dhour):
203 db = self.connect_db()
204 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
207 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
213 ORDER BY status DESC")
215 #recdblist.printutf8(dbexe)
217 ret = db[1].fetchall()
220 def update_by_bctype_epg_ch(self, bctype):
221 db = self.connect_db()
227 WHERE bctype = %s", (bctype,))
229 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
230 db = self.connect_db()
236 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
238 def update_status_by_bctype_epg_ch(self, bctype, status):
239 db = self.connect_db()
245 WHERE bctype = %s", \
249 def update_chname_by_ontv_epg_ch(self,ontv,chname):
250 db = self.connect_db()
259 def add_auto_proc_tmp(self,type,title,chtxt):
260 db = self.connect_db()
262 INSERT IGNORE into auto_proc_tmp \
264 values (%s,%s,%s)',(type,title,chtxt))
267 def new_auto_proc_tmp(self):
268 db = self.connect_db()
270 db[1].execute('drop table auto_proc_tmp')
275 CREATE TABLE auto_proc_tmp \
278 title VARCHAR(100) PRIMARY KEY,\
280 UNIQUE unibayeskey(title)\
285 def update_auto_proc(self):
286 db = self.connect_db()
288 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
292 def new_auto_proc(self):
293 db = self.connect_db()
295 db[1].execute('drop table auto_proc')
300 CREATE TABLE auto_proc \
303 title VARCHAR(100) PRIMARY KEY,\
305 UNIQUE unibayeskey(title)\
310 def add_auto_proc(self,type,title,chtxt):
311 db = self.connect_db()
313 INSERT IGNORE into auto_proc \
315 values (%s,%s,%s)',(type,title,chtxt))
318 def drop_in_settings(self):
319 db = self.connect_db()
321 db[1].execute('drop table in_settings')
325 def new_in_settings(self):
326 db = self.connect_db()
329 CREATE TABLE in_settings \
333 auto_opt VARCHAR(20),\
334 auto_del_tmp TINYINT\
336 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"G\",1)")
340 def select_all_in_settings(self):
341 db = self.connect_db()
342 dbexe = db[1].execute("\
343 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
349 dls = db[1].fetchall()
359 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
360 db = self.connect_db()
362 INSERT IGNORE into in_timeline_log \
363 (chtxt,title,btime,etime,opt,exp,longexp,category) \
364 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
365 ( chtxt, title, btime, etime, opt,exp,longexp,category))
368 def del_in_timeline_log(self, title="", chtxt="", btime=""):
372 db = self.connect_db()
374 DELETE FROM in_timeline_log \
375 WHERE title = %s AND chtxt = %s AND btime = %s", \
376 (title, chtxt, btime))
379 def new_in_timeline_log(self):
380 db = self.connect_db()
383 CREATE TABLE in_timeline_log \
385 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
393 category VARCHAR(100),\
394 UNIQUE uni (chtxt,title,btime,category)\
399 def select_chtxt_by_title_timeline_log(self,title):
400 db = self.connect_db()
401 dbexe = db[1].execute("\
403 FROM in_timeline_log \
404 WHERE title LIKE \"%"+title+"%\"\
406 ORDER by sum(1) DESC limit 1")
407 retdb=db[1].fetchall()
414 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
415 db = self.connect_db()
417 INSERT IGNORE into timeline \
418 (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
419 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
420 (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
423 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle):
424 db = self.connect_db()
427 SET epgbtime=%s,epgetime=%s,epgtitle=%s \
428 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
429 (epgbtime,epgetime,epgtitle,type, chtxt, title, btime))
432 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
433 db = self.connect_db()
437 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
438 (epgchange , type, chtxt, title, btime))
441 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
442 db = self.connect_db()
445 SET epgduplicate =%s \
446 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
447 (epgduplicate , type, chtxt, title, btime))
450 def del_timeline(self, type="", title="", chtxt="", btime=""):
454 db = self.connect_db()
456 DELETE FROM timeline \
457 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
458 (type, title, chtxt, btime))
461 def select_all_timeline(self):
462 db = self.connect_db()
464 dbr = db[1].execute("\
465 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
467 dbl = db[1].fetchall()
470 recdata = getdic_timeline(dbl)
473 def select_bytime_timeline(self, dminutes):
474 db = self.connect_db()
476 #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
477 dbr = db[1].execute("SELECT \
478 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
480 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
481 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
482 dbl = db[1].fetchall()
484 #recdblist.printutf8(dbl)
486 recdatum = getdic_timeline(dbl)
489 def select_by_name_time_timeline(self,title,btime,btime2):
490 db = self.connect_db()
492 #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
493 dbr = db[1].execute("SELECT \
494 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
496 WHERE btime > %s AND \
497 btime < %s AND title = %s",(btime,btime2,title))
498 dbl = db[1].fetchall()
500 #recdblist.printutf8(dbl)
502 recdatum = getdic_timeline(dbl)
505 def select_bytime_all_timeline(self,btime,etime):
506 db = self.connect_db()
508 #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
509 dbr = db[1].execute("SELECT \
510 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
512 WHERE btime > %s AND \
513 etime < %s",(btime,etime))
514 dbl = db[1].fetchall()
516 #recdblist.printutf8(dbl)
518 recdatum = getdic_timeline(dbl)
521 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
522 db = self.connect_db()
524 #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
525 dbr = db[1].execute("SELECT \
526 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
528 WHERE epgbtime > %s AND \
529 epgetime < %s",(epgbtime,epgetime))
530 dbl = db[1].fetchall()
532 #recdblist.printutf8(dbl)
534 recdatum=getdic_timeline(dbl)
537 def count_schedule_timeline(self, btime, etime):
540 return [te num,bs/cs num]
542 db = self.connect_db()
543 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
544 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
547 db[1].execute(dbexe, (btime, etime,btime,etime))
549 for typet, bctypet, chtxtt, titlet in dbl:
550 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
551 if bctypet.find("cs") > -1:
553 elif bctypet.find("bs") > -1:
555 elif bctypet.find("te") > -1:
559 def search_schedule_timeline(self,btime,etime):
562 return [(type,bctype,chtxt,title,btime,etime)]
564 db = self.connect_db()
565 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title,timeline.btime,timeline.etime FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
566 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
568 db[1].execute(dbexe, (btime, etime,btime,etime))
570 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
571 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
572 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
575 def count_epgschedule_timeline(self, epgbtime, epgetime):
578 return [te num,bs/cs num]
580 db = self.connect_db()
581 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
582 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
585 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
587 for typet, bctypet, chtxtt, titlet in dbl:
588 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
589 if bctypet.find("cs") > -1:
591 elif bctypet.find("bs") > -1:
593 elif bctypet.find("te") > -1:
597 def search_epgschedule_timeline(self,epgbtime,epgetime):
600 return [(type,bctype,chtxt,title,btime,etime)]
602 db = self.connect_db()
603 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title,timeline.btime,timeline.etime FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
604 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
606 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
608 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
609 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
610 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
613 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
614 db = self.connect_db()
616 #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
617 dbr = db[1].execute("SELECT \
618 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
620 WHERE btime > %s AND \
622 AND chtxt=%s ",(btime,etime,chtxt))
623 dbl = db[1].fetchall()
625 #recdblist.printutf8(dbl)
627 recdatum = getdic_timeline(dbl)
630 def getdic_timeline(self,timelinelists):
632 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget in timelinelists:
637 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
638 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
642 ret['epgbtime'] = epgbtimet
643 ret['epgetime'] = epgetimet
644 ret['epgtitle'] = epgtitlet
645 ret['epgduplicate'] = epgduplicatet
646 ret['epgchange'] = epgchanget
647 if deltatime == None or deltatime == "":
649 if deltaday == None or deltaday == "":
651 if typet == recdblist.REC_KEYWORD:
652 ret['deltatime'] = deltatime
653 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
654 ret['deltatime'] = deltatime
655 ret['deltaday'] = deltaday
658 def delete_old_timeline(self, dhour):
659 db = self.connect_db()
661 DELETE FROM timeline \
663 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
666 def new_in_auto_jbk_key(self):
667 db = self.connect_db()
670 CREATE TABLE in_auto_jbk_key \
672 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
673 keyword VARCHAR(200),\
674 UNIQUE unijbk (keyword)\
679 def add_in_auto_jbk_key(self,key):
680 db = self.connect_db()
682 INSERT IGNORE into in_auto_jbk_key \
688 def select_all_in_auto_jbk_key(self):
689 db = self.connect_db()
690 dbexe = db[1].execute("\
692 FROM in_auto_jbk_key \
696 ret = db[1].fetchall()
699 def drop_in_status(self):
700 db = self.connect_db()
702 db[1].execute('drop table in_status')
706 def new_in_status(self):
707 db = self.connect_db()
710 CREATE TABLE in_status \
712 ts2avi TINYINT DEFAULT 0,\
713 terec TINYINT DEFAULT 0,\
714 bscsrec TINYINT DEFAULT 0,\
715 b252ts TINYINT DEFAULT 0,\
716 installed TINYINT DEFAULT 0\
718 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
722 def select_all_in_status(self):
723 db = self.connect_db()
724 dbexe = db[1].execute("\
725 SELECT ts2avi,terec,bscsrec,b252ts \
731 dls = db[1].fetchall()
742 def change_ts2avi_in_status(self,i):
747 db = self.connect_db()
749 UPDATE in_status SET ts2avi=ts2avi+%s",i)
751 def change_terec_in_status(self,i):
756 db = self.connect_db()
758 UPDATE in_status SET terec=terec+%s",i)
760 def change_bscsrec_in_status(self,i):
765 db = self.connect_db()
767 UPDATE in_status SET bscsrec=bscsrec+%s",i)
769 def change_b252ts_in_status(self,i):
774 db = self.connect_db()
776 UPDATE in_status SET b252ts=b252ts+%s",i)
778 def select_installed_in_status(self):
779 db = self.connect_db()
780 dbexe = db[1].execute("\
781 SELECT ts2avi,terec,bscsrec,b252ts,installed \
787 dls = db[1].fetchall()
793 def change_installed_in_status(self):
797 db = self.connect_db()
799 UPDATE in_status SET installed=1")
801 def new_epg_timeline(self, bctype):
802 db = self.connect_db()
805 DELETE FROM epg_timeline \
806 WHERE bctype = %s", \
812 CREATE TABLE epg_timeline \
815 channel VARCHAR(100) NOT NULL,\
821 category VARCHAR(100),\
822 UNIQUE unitv(bctype,channel,start,stop,title)\
828 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
829 db = self.connect_db()
831 INSERT IGNORE INTO epg_timeline \
832 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
833 (bctype, channel, start, stop, title, desc,longdesc,category))
836 def add_multi_epg_timeline(self, tvlists):
838 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
840 db = self.connect_db()
842 INSERT IGNORE INTO epg_timeline \
843 (bctype,channel,start,stop,title,exp,longexp,category) \
844 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
847 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
848 db = self.connect_db()
851 epg_ch.chtxt,title,start,stop,exp,longexp,category \
854 WHERE epg_ch.ontv=epg_timeline.channel \
861 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
864 retall = db[1].fetchall()
867 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
868 db = self.connect_db()
871 epg_ch.chtxt,title,start,stop,exp,longexp,category \
874 WHERE epg_ch.ontv=epg_timeline.channel \
881 ( title LIKE \'%%"+keyword+"%%\' ) \
883 ( exp LIKE \'%%"+keyword+"%%\' ) \
885 ( longexp LIKE \'%%"+keyword+"%%\' ) \
887 dbcmd = db[1].execute(dbexe,(btime, etime))
890 retall = db[1].fetchall()
893 def new_in_auto_bayes_key(self):
894 db = self.connect_db()
896 db[1].execute('CREATE TABLE in_auto_bayes_key \
898 keychar VARCHAR(10),\
900 ratio_rec DECIMAL(32,14),\
901 ratio_all DECIMAL(32,14),\
902 UNIQUE unibayeskey(keychar,chtxt)\
904 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
909 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
910 db = self.connect_db()
911 ratio_rec=str(ratio_rec)
912 ratio_all=str(ratio_all)
914 INSERT IGNORE INTO in_auto_bayes_key \
915 (keychar,chtxt,ratio_rec,ratio_all) \
916 values (%s,%s,%s,%s)',\
917 (key,chtxt,ratio_rec,ratio_all))
919 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
920 db = self.connect_db()
921 add_rec_num=str(add_rec_num)
922 add_all_num=str(add_all_num)
924 UPDATE in_auto_bayes_key SET ratio_rec=CONVERT(ratio_rec+%s,DECIMAL(32,14)),ratio_all=CONVERT(ratio_all+%s,DECIMAL(32,14)) WHERE keychar=\"NUM\" AND chtxt=%s",\
925 (add_rec_num,add_all_num,chtxt))
927 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
930 db = self.connect_db()
932 UPDATE in_auto_bayes_key SET ratio_rec=%s,ratio_all=%s WHERE keychar=%s AND chtxt=%s",(str(new_ratio_rec),str(new_ratio_all),key,chtxt)\
935 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
936 beforenum=str(beforenum)
938 db = self.connect_db()
940 UPDATE in_auto_bayes_key SET ratio_all=CONVERT(ratio_all*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
943 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
944 db = self.connect_db()
946 beforenum=str(beforenum)
947 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
948 VALUES (%s,%s,%s,%s)\
949 ON DUPLICATE KEY UPDATE \
950 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
952 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
954 list={key:addnum}のリスト
957 beforenum=str(beforenum)
958 db = self.connect_db()
959 for i,j in list.items():
960 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
962 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
963 VALUES (%s,%s,%s,%s)\
964 ON DUPLICATE KEY UPDATE \
965 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
969 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
970 beforenum=str(beforenum)
972 db = self.connect_db()
974 UPDATE in_auto_bayes_key SET ratio_rec=CONVERT(ratio_rec*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
977 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
978 db = self.connect_db()
980 beforenum=str(beforenum)
981 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
982 VALUES (%s,%s,%s,%s)\
983 ON DUPLICATE KEY UPDATE \
984 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
986 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
988 beforenum=str(beforenum)
989 db = self.connect_db()
990 for i,j in list.items():
991 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
993 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
994 VALUES (%s,%s,%s,%s)\
995 ON DUPLICATE KEY UPDATE \
996 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1000 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1001 db = self.connect_db()
1002 dbexe = db[1].execute("\
1003 SELECT keychar,chtxt,ratio_rec,ratio_all \
1004 FROM in_auto_bayes_key \
1005 WHERE keychar = %s AND chtxt = %s", \
1009 dls = db[1].fetchall()
1016 def new_auto_timeline_keyword(self):
1017 db = self.connect_db()
1020 CREATE TABLE auto_timeline_keyword \
1022 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1024 title VARCHAR(100),\
1027 UNIQUE uni (chtxt,title,btime,etime)\
1031 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1032 db = self.connect_db()
1034 INSERT IGNORE into auto_timeline_keyword \
1035 (chtxt,title,btime,etime) \
1036 values (%s,%s,%s,%s)', \
1037 (chtxt, title, btime, etime))
1040 def delete_old_auto_timeline_keyword(self, dhour):
1041 db = self.connect_db()
1043 DELETE FROM auto_timeline_keyword \
1045 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1047 def new_auto_timeline_bayes(self):
1048 db = self.connect_db()
1051 CREATE TABLE auto_timeline_bayes \
1053 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1055 title VARCHAR(100),\
1059 UNIQUE uni (chtxt,title,btime,etime)\
1063 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1064 db = self.connect_db()
1066 INSERT IGNORE into auto_timeline_bayes \
1067 (chtxt,title,btime,etime,point) \
1068 values (%s,%s,%s,%s,%s)', \
1069 (chtxt, title, btime, etime,point))
1071 def delete_old_auto_timeline_bayes(self, dhour):
1072 db = self.connect_db()
1074 DELETE FROM auto_timeline_bayes \
1076 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")