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 = self.getdic_timeline(dbl)
472 def select_bytime_timeline(self, dminutes):
473 db = self.connect_db()
475 #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 )")
476 dbr = db[1].execute("SELECT \
477 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
479 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
480 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
481 dbl = db[1].fetchall()
483 #recdblist.printutf8(dbl)
485 recdatum = self.getdic_timeline(dbl)
487 def select_by_name_time_timeline(self,title,btime,btime2):
488 db = self.connect_db()
490 #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 )")
491 dbr = db[1].execute("SELECT \
492 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
494 WHERE btime > %s AND \
495 btime < %s AND title = %s",(btime,btime2,title))
496 dbl = db[1].fetchall()
498 #recdblist.printutf8(dbl)
500 recdatum = self.getdic_timeline(dbl)
502 def select_bytime_all_timeline(self,btime,etime):
503 db = self.connect_db()
505 #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 )")
506 dbr = db[1].execute("SELECT \
507 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
509 WHERE btime > %s AND \
510 etime < %s",(btime,etime))
511 dbl = db[1].fetchall()
513 #recdblist.printutf8(dbl)
515 recdatum = self.getdic_timeline(dbl)
517 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
518 db = self.connect_db()
520 #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 )")
521 dbr = db[1].execute("SELECT \
522 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
524 WHERE epgbtime >= %s AND \
525 epgetime <= %s",(epgbtime,epgetime))
526 dbl = db[1].fetchall()
528 #recdblist.printutf8(dbl)
530 recdatum=self.getdic_timeline(dbl)
532 def count_schedule_timeline(self, btime, etime):
535 return [te num,bs/cs num]
537 db = self.connect_db()
538 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
539 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
542 db[1].execute(dbexe, (btime, etime,btime,etime))
544 for typet, bctypet, chtxtt, titlet in dbl:
545 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
546 if bctypet.find("cs") > -1:
548 elif bctypet.find("bs") > -1:
550 elif bctypet.find("te") > -1:
554 def search_schedule_timeline(self,btime,etime):
557 return [(type,bctype,chtxt,title,btime,etime)]
559 db = self.connect_db()
560 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"
561 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
563 db[1].execute(dbexe, (btime, etime,btime,etime))
565 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
566 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
567 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
570 def count_epgschedule_timeline(self, epgbtime, epgetime):
573 return [te num,bs/cs num]
575 db = self.connect_db()
576 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
577 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
580 db[1].execute(dbexe, (epgbtime, epgetime))
582 for typet, bctypet, chtxtt, titlet in dbl:
583 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
584 if bctypet.find("cs") > -1:
586 elif bctypet.find("bs") > -1:
588 elif bctypet.find("te") > -1:
592 def search_epgschedule_timeline(self,epgbtime,epgetime):
595 return [(type,bctype,chtxt,title,btime,etime)]
597 db = self.connect_db()
598 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"
599 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
601 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
603 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
604 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
605 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
608 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
609 db = self.connect_db()
611 #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 )")
612 dbr = db[1].execute("SELECT \
613 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
615 WHERE btime > %s AND \
617 AND chtxt=%s ",(btime,etime,chtxt))
618 dbl = db[1].fetchall()
620 #recdblist.printutf8(dbl)
622 recdatum = self.getdic_timeline(dbl)
624 def getdic_timeline(self,timelinelists):
626 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget in timelinelists:
631 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
632 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
637 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
638 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
640 ret['epgbtime'] = "2010-01-01 00:00:00"
641 ret['epgetime'] = "2010-01-01 00:00:00"
642 ret['epgtitle'] = epgtitlet
643 ret['epgduplicate'] = epgduplicatet
644 ret['epgchange'] = epgchanget
645 if deltatime == None or deltatime == "":
647 if deltaday == None or deltaday == "":
649 if typet == recdblist.REC_KEYWORD:
650 ret['deltatime'] = deltatime
651 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
652 ret['deltatime'] = deltatime
653 ret['deltaday'] = deltaday
656 def delete_old_timeline(self, dhour):
657 db = self.connect_db()
659 DELETE FROM timeline \
661 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
664 def new_in_auto_jbk_key(self):
665 db = self.connect_db()
668 CREATE TABLE in_auto_jbk_key \
670 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
671 keyword VARCHAR(200),\
672 UNIQUE unijbk (keyword)\
677 def add_in_auto_jbk_key(self,key):
678 db = self.connect_db()
680 INSERT IGNORE into in_auto_jbk_key \
686 def select_all_in_auto_jbk_key(self):
687 db = self.connect_db()
688 dbexe = db[1].execute("\
690 FROM in_auto_jbk_key \
694 ret = db[1].fetchall()
697 def drop_in_status(self):
698 db = self.connect_db()
700 db[1].execute('drop table in_status')
704 def new_in_status(self):
705 db = self.connect_db()
708 CREATE TABLE in_status \
710 ts2avi TINYINT DEFAULT 0,\
711 terec TINYINT DEFAULT 0,\
712 bscsrec TINYINT DEFAULT 0,\
713 b252ts TINYINT DEFAULT 0,\
714 installed TINYINT DEFAULT 0\
716 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
720 def select_all_in_status(self):
721 db = self.connect_db()
722 dbexe = db[1].execute("\
723 SELECT ts2avi,terec,bscsrec,b252ts \
729 dls = db[1].fetchall()
740 def change_ts2avi_in_status(self,i):
745 db = self.connect_db()
747 UPDATE in_status SET ts2avi=ts2avi+%s",i)
749 def change_terec_in_status(self,i):
754 db = self.connect_db()
756 UPDATE in_status SET terec=terec+%s",i)
758 def change_bscsrec_in_status(self,i):
763 db = self.connect_db()
765 UPDATE in_status SET bscsrec=bscsrec+%s",i)
767 def change_b252ts_in_status(self,i):
772 db = self.connect_db()
774 UPDATE in_status SET b252ts=b252ts+%s",i)
776 def select_installed_in_status(self):
777 db = self.connect_db()
778 dbexe = db[1].execute("\
779 SELECT ts2avi,terec,bscsrec,b252ts,installed \
785 dls = db[1].fetchall()
791 def change_installed_in_status(self):
795 db = self.connect_db()
797 UPDATE in_status SET installed=1")
799 def new_epg_timeline(self, bctype):
800 db = self.connect_db()
803 DELETE FROM epg_timeline \
804 WHERE bctype = %s", \
810 CREATE TABLE epg_timeline \
813 channel VARCHAR(100) NOT NULL,\
819 category VARCHAR(100),\
820 UNIQUE unitv(bctype,channel,start,stop,title)\
826 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
827 db = self.connect_db()
829 INSERT IGNORE INTO epg_timeline \
830 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
831 (bctype, channel, start, stop, title, desc,longdesc,category))
834 def add_multi_epg_timeline(self, tvlists):
836 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
838 db = self.connect_db()
840 INSERT IGNORE INTO epg_timeline \
841 (bctype,channel,start,stop,title,exp,longexp,category) \
842 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
845 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
846 db = self.connect_db()
849 epg_ch.chtxt,title,start,stop,exp,longexp,category \
852 WHERE epg_ch.ontv=epg_timeline.channel \
859 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
862 retall = db[1].fetchall()
865 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
866 db = self.connect_db()
869 epg_ch.chtxt,title,start,stop,exp,longexp,category \
872 WHERE epg_ch.ontv=epg_timeline.channel \
879 ( title LIKE \'%%"+keyword+"%%\' ) \
881 ( exp LIKE \'%%"+keyword+"%%\' ) \
883 ( longexp LIKE \'%%"+keyword+"%%\' ) \
885 dbcmd = db[1].execute(dbexe,(btime, etime))
888 retall = db[1].fetchall()
891 def new_in_auto_bayes_key(self):
892 db = self.connect_db()
894 db[1].execute('CREATE TABLE in_auto_bayes_key \
896 keychar VARCHAR(10),\
898 ratio_rec DECIMAL(32,14),\
899 ratio_all DECIMAL(32,14),\
900 UNIQUE unibayeskey(keychar,chtxt)\
902 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
907 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
908 db = self.connect_db()
909 ratio_rec=str(ratio_rec)
910 ratio_all=str(ratio_all)
912 INSERT IGNORE INTO in_auto_bayes_key \
913 (keychar,chtxt,ratio_rec,ratio_all) \
914 values (%s,%s,%s,%s)',\
915 (key,chtxt,ratio_rec,ratio_all))
917 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
918 db = self.connect_db()
919 add_rec_num=str(add_rec_num)
920 add_all_num=str(add_all_num)
922 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",\
923 (add_rec_num,add_all_num,chtxt))
925 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
928 db = self.connect_db()
930 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)\
933 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
934 beforenum=str(beforenum)
936 db = self.connect_db()
938 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)\
941 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
942 db = self.connect_db()
944 beforenum=str(beforenum)
945 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
946 VALUES (%s,%s,%s,%s)\
947 ON DUPLICATE KEY UPDATE \
948 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
950 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
952 list={key:addnum}のリスト
955 beforenum=str(beforenum)
956 db = self.connect_db()
957 for i,j in list.items():
958 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
960 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
961 VALUES (%s,%s,%s,%s)\
962 ON DUPLICATE KEY UPDATE \
963 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
967 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
968 beforenum=str(beforenum)
970 db = self.connect_db()
972 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)\
975 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
976 db = self.connect_db()
978 beforenum=str(beforenum)
979 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
980 VALUES (%s,%s,%s,%s)\
981 ON DUPLICATE KEY UPDATE \
982 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
984 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
986 beforenum=str(beforenum)
987 db = self.connect_db()
988 for i,j in list.items():
989 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
991 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
992 VALUES (%s,%s,%s,%s)\
993 ON DUPLICATE KEY UPDATE \
994 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
998 def select_by_key_in_auto_bayes_key(self,key,chtxt):
999 db = self.connect_db()
1000 dbexe = db[1].execute("\
1001 SELECT keychar,chtxt,ratio_rec,ratio_all \
1002 FROM in_auto_bayes_key \
1003 WHERE keychar = %s AND chtxt = %s", \
1007 dls = db[1].fetchall()
1014 def new_auto_timeline_keyword(self):
1015 db = self.connect_db()
1018 CREATE TABLE auto_timeline_keyword \
1020 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1022 title VARCHAR(100),\
1025 UNIQUE uni (chtxt,title,btime,etime)\
1029 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1030 db = self.connect_db()
1032 INSERT IGNORE into auto_timeline_keyword \
1033 (chtxt,title,btime,etime) \
1034 values (%s,%s,%s,%s)', \
1035 (chtxt, title, btime, etime))
1038 def delete_old_auto_timeline_keyword(self, dhour):
1039 db = self.connect_db()
1041 DELETE FROM auto_timeline_keyword \
1043 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1045 def new_auto_timeline_bayes(self):
1046 db = self.connect_db()
1049 CREATE TABLE auto_timeline_bayes \
1051 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1053 title VARCHAR(100),\
1057 UNIQUE uni (chtxt,title,btime,etime)\
1061 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1062 db = self.connect_db()
1064 INSERT IGNORE into auto_timeline_bayes \
1065 (chtxt,title,btime,etime,point) \
1066 values (%s,%s,%s,%s,%s)', \
1067 (chtxt, title, btime, etime,point))
1069 def delete_old_auto_timeline_bayes(self, dhour):
1070 db = self.connect_db()
1072 DELETE FROM auto_timeline_bayes \
1074 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")