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 modify TINYINT DEFAULT 1,\
51 UNIQUE uni (type,chtxt,title,btime,deltaday)\
56 self.new_epg_timeline("")
57 self.new_in_timeline_log()
58 self.new_in_auto_bayes_key()
59 self.new_in_auto_jbk_key()
61 self.new_in_settings()
62 self.new_auto_timeline_bayes()
63 self.new_auto_timeline_keyword()
68 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
70 cur.execute('set names utf8;')
72 def close_db(self, db):
76 db = self.connect_db()
78 db[1].execute('drop table epg_ch')
86 ontv VARCHAR(30) PRIMARY KEY,\
98 def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
99 db = self.connect_db()
102 VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s)', \
103 (bctype, ontv, chtxt, ch, csch, updatetime, "1","1"))
105 def select_by_ontv_epg_ch(self, ontv):
106 db = self.connect_db()
107 dbexe = db[1].execute("\
108 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
115 dls = db[1].fetchall()
119 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
122 def select_by_chtxt_epg_ch(self, chtxt):
123 db = self.connect_db()
124 dbexe = db[1].execute("\
125 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
132 dls = db[1].fetchall()
136 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
139 def select_by_bctype_epg_ch(self, bctype):
140 db = self.connect_db()
141 dbexe = db[1].execute("\
142 SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
144 WHERE bctype = %s", \
149 dls = db[1].fetchall()
152 #recdblist.printutf8(dl)
154 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
158 def select_by_ch_epg_ch(self, ch):
159 db = self.connect_db()
160 dbexe = db[1].execute("\
162 bctype,ontv,chtxt,ch,csch,updatetime \
169 dls = db[1].fetchall()
173 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
176 def select_all_epg_ch(self):
177 db = self.connect_db()
178 dbexe = db[1].execute("\
179 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
185 dls = db[1].fetchall()
189 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
192 def set_new_status(self,dhour):
193 db = self.connect_db()
194 dbexe = db[1].execute("UPDATE epg_ch \
198 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
203 def select_get_update_epg_ch(self, dhour):
204 db = self.connect_db()
205 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
208 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
214 ORDER BY status DESC")
216 #recdblist.printutf8(dbexe)
218 ret = db[1].fetchall()
221 def update_by_bctype_epg_ch(self, bctype):
222 db = self.connect_db()
228 WHERE bctype = %s", (bctype,))
230 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
231 db = self.connect_db()
237 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
239 def update_status_by_bctype_epg_ch(self, bctype, status):
240 db = self.connect_db()
246 WHERE bctype = %s", \
250 def update_chname_by_ontv_epg_ch(self,ontv,chname):
251 db = self.connect_db()
260 def add_auto_proc_tmp(self,type,title,chtxt):
261 db = self.connect_db()
263 INSERT IGNORE into auto_proc_tmp \
265 values (%s,%s,%s)',(type,title,chtxt))
268 def new_auto_proc_tmp(self):
269 db = self.connect_db()
271 db[1].execute('drop table auto_proc_tmp')
276 CREATE TABLE auto_proc_tmp \
279 title VARCHAR(100) PRIMARY KEY,\
281 UNIQUE unibayeskey(title)\
286 def update_auto_proc(self):
287 db = self.connect_db()
289 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
293 def new_auto_proc(self):
294 db = self.connect_db()
296 db[1].execute('drop table auto_proc')
301 CREATE TABLE auto_proc \
304 title VARCHAR(100) PRIMARY KEY,\
306 UNIQUE unibayeskey(title)\
311 def add_auto_proc(self,type,title,chtxt):
312 db = self.connect_db()
314 INSERT IGNORE into auto_proc \
316 values (%s,%s,%s)',(type,title,chtxt))
319 def drop_in_settings(self):
320 db = self.connect_db()
322 db[1].execute('drop table in_settings')
326 def new_in_settings(self):
327 db = self.connect_db()
330 CREATE TABLE in_settings \
334 auto_opt VARCHAR(20),\
335 auto_del_tmp TINYINT\
337 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"G\",1)")
341 def select_all_in_settings(self):
342 db = self.connect_db()
343 dbexe = db[1].execute("\
344 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
350 dls = db[1].fetchall()
360 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
361 db = self.connect_db()
363 INSERT IGNORE into in_timeline_log \
364 (chtxt,title,btime,etime,opt,exp,longexp,category) \
365 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
366 ( chtxt, title, btime, etime, opt,exp,longexp,category))
369 def del_in_timeline_log(self, title="", chtxt="", btime=""):
373 db = self.connect_db()
375 DELETE FROM in_timeline_log \
376 WHERE title = %s AND chtxt = %s AND btime = %s", \
377 (title, chtxt, btime))
380 def new_in_timeline_log(self):
381 db = self.connect_db()
384 CREATE TABLE in_timeline_log \
386 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
394 category VARCHAR(100),\
395 UNIQUE uni (chtxt,title,btime,category)\
400 def select_chtxt_by_title_timeline_log(self,title):
401 db = self.connect_db()
402 dbexe = db[1].execute("\
404 FROM in_timeline_log \
405 WHERE title LIKE \"%"+title+"%\"\
407 ORDER by sum(1) DESC limit 1")
408 retdb=db[1].fetchall()
415 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
416 db = self.connect_db()
418 INSERT IGNORE into timeline \
419 (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
420 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
421 (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
424 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle):
425 db = self.connect_db()
428 SET epgbtime=%s,epgetime=%s,epgtitle=%s \
429 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
430 (epgbtime,epgetime,epgtitle,type, chtxt, title, btime))
433 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
434 db = self.connect_db()
438 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
439 (epgchange , type, chtxt, title, btime))
442 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
443 db = self.connect_db()
446 SET epgduplicate =%s \
447 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
448 (epgduplicate , type, chtxt, title, btime))
451 def update_status_modify_timeline(self,type,chtxt,title,btime,modify):
452 db = self.connect_db()
456 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
457 (epgduplicate , type, chtxt, title, btime))
459 def del_timeline(self, type="", title="", chtxt="", btime=""):
463 db = self.connect_db()
465 DELETE FROM timeline \
466 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
467 (type, title, chtxt, btime))
470 def select_all_timeline(self):
471 db = self.connect_db()
473 dbr = db[1].execute("\
474 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
476 dbl = db[1].fetchall()
479 recdata = self.getdic_timeline(dbl)
481 def select_bytime_timeline(self, dminutes):
482 db = self.connect_db()
484 #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 )")
485 dbr = db[1].execute("SELECT \
486 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
488 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
489 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
490 dbl = db[1].fetchall()
492 #recdblist.printutf8(dbl)
494 recdatum = self.getdic_timeline(dbl)
496 def select_by_name_time_timeline(self,title,btime,btime2):
497 db = self.connect_db()
499 #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 )")
500 dbr = db[1].execute("SELECT \
501 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
503 WHERE btime > %s AND \
504 btime < %s AND title = %s",(btime,btime2,title))
505 dbl = db[1].fetchall()
507 #recdblist.printutf8(dbl)
509 recdatum = self.getdic_timeline(dbl)
511 def select_bytime_all_timeline(self,btime,etime):
512 db = self.connect_db()
514 #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 )")
515 dbr = db[1].execute("SELECT \
516 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
518 WHERE btime > %s AND \
519 etime < %s",(btime,etime))
520 dbl = db[1].fetchall()
522 #recdblist.printutf8(dbl)
524 recdatum = self.getdic_timeline(dbl)
526 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
527 db = self.connect_db()
529 #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 )")
530 dbr = db[1].execute("SELECT \
531 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
533 WHERE epgbtime >= %s AND \
534 epgetime <= %s",(epgbtime,epgetime))
535 dbl = db[1].fetchall()
537 #recdblist.printutf8(dbl)
539 recdatum=self.getdic_timeline(dbl)
541 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
542 db = self.connect_db()
544 #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 )")
545 dbr = db[1].execute("SELECT \
546 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
548 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
549 ,(epgbtime,epgetime))
550 dbl = db[1].fetchall()
552 #recdblist.printutf8(dbl)
554 recdatum=self.getdic_timeline(dbl)
556 def select_bymodify_all_timeline(self):
557 db = self.connect_db()
559 #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 )")
560 dbr = db[1].execute("SELECT \
561 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
564 dbl = db[1].fetchall()
567 recdatum=self.getdic_timeline(dbl)
569 def count_schedule_timeline(self, btime, etime):
572 return [te num,bs/cs num]
574 db = self.connect_db()
575 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
576 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
579 db[1].execute(dbexe, (btime, etime,btime,etime))
581 for typet, bctypet, chtxtt, titlet in dbl:
582 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
583 if bctypet.find("cs") > -1:
585 elif bctypet.find("bs") > -1:
587 elif bctypet.find("te") > -1:
591 def search_schedule_timeline(self,btime,etime):
594 return [(type,bctype,chtxt,title,btime,etime)]
596 db = self.connect_db()
597 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"
598 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
600 db[1].execute(dbexe, (btime, etime,btime,etime))
602 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
603 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
604 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
607 def count_epgschedule_timeline(self, epgbtime, epgetime):
610 return [te num,bs/cs num]
612 db = self.connect_db()
613 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
614 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
617 db[1].execute(dbexe, (epgbtime, epgetime))
619 for typet, bctypet, chtxtt, titlet in dbl:
620 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
621 if bctypet.find("cs") > -1:
623 elif bctypet.find("bs") > -1:
625 elif bctypet.find("te") > -1:
629 def search_epgschedule_timeline(self,epgbtime,epgetime):
632 return [(type,bctype,chtxt,title,btime,etime)]
634 db = self.connect_db()
635 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"
636 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
638 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
640 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
641 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
642 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
645 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
646 db = self.connect_db()
648 #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 )")
649 dbr = db[1].execute("SELECT \
650 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
652 WHERE btime > %s AND \
654 AND chtxt=%s ",(btime,etime,chtxt))
655 dbl = db[1].fetchall()
657 #recdblist.printutf8(dbl)
659 recdatum = self.getdic_timeline(dbl)
661 def getdic_timeline(self,timelinelists):
663 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget in timelinelists:
668 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
669 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
674 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
675 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
677 ret['epgbtime'] = "2010-01-01 00:00:00"
678 ret['epgetime'] = "2010-01-01 00:00:00"
679 ret['epgtitle'] = epgtitlet
680 ret['epgduplicate'] = epgduplicatet
681 ret['epgchange'] = epgchanget
682 if deltatime == None or deltatime == "":
684 if deltaday == None or deltaday == "":
686 if typet == recdblist.REC_KEYWORD:
687 ret['deltatime'] = deltatime
688 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
689 ret['deltatime'] = deltatime
690 ret['deltaday'] = deltaday
693 def delete_old_timeline(self, dhour):
694 db = self.connect_db()
696 DELETE FROM timeline \
698 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
701 def new_in_auto_jbk_key(self):
702 db = self.connect_db()
705 CREATE TABLE in_auto_jbk_key \
707 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
708 keyword VARCHAR(200),\
709 UNIQUE unijbk (keyword)\
714 def add_in_auto_jbk_key(self,key):
715 db = self.connect_db()
717 INSERT IGNORE into in_auto_jbk_key \
723 def select_all_in_auto_jbk_key(self):
724 db = self.connect_db()
725 dbexe = db[1].execute("\
727 FROM in_auto_jbk_key \
731 ret = db[1].fetchall()
734 def drop_in_status(self):
735 db = self.connect_db()
737 db[1].execute('drop table in_status')
741 def new_in_status(self):
742 db = self.connect_db()
745 CREATE TABLE in_status \
747 ts2avi TINYINT DEFAULT 0,\
748 terec TINYINT DEFAULT 0,\
749 bscsrec TINYINT DEFAULT 0,\
750 b252ts TINYINT DEFAULT 0,\
751 installed TINYINT DEFAULT 0\
753 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
757 def select_all_in_status(self):
758 db = self.connect_db()
759 dbexe = db[1].execute("\
760 SELECT ts2avi,terec,bscsrec,b252ts \
766 dls = db[1].fetchall()
777 def change_ts2avi_in_status(self,i):
782 db = self.connect_db()
784 UPDATE in_status SET ts2avi=ts2avi+%s",i)
786 def change_terec_in_status(self,i):
791 db = self.connect_db()
793 UPDATE in_status SET terec=terec+%s",i)
795 def change_bscsrec_in_status(self,i):
800 db = self.connect_db()
802 UPDATE in_status SET bscsrec=bscsrec+%s",i)
804 def change_b252ts_in_status(self,i):
809 db = self.connect_db()
811 UPDATE in_status SET b252ts=b252ts+%s",i)
813 def select_installed_in_status(self):
814 db = self.connect_db()
815 dbexe = db[1].execute("\
816 SELECT ts2avi,terec,bscsrec,b252ts,installed \
822 dls = db[1].fetchall()
828 def change_installed_in_status(self):
832 db = self.connect_db()
834 UPDATE in_status SET installed=1")
836 def new_epg_timeline(self, bctype):
837 db = self.connect_db()
840 DELETE FROM epg_timeline \
841 WHERE bctype = %s", \
847 CREATE TABLE epg_timeline \
850 channel VARCHAR(100) NOT NULL,\
856 category VARCHAR(100),\
857 UNIQUE unitv(bctype,channel,start,stop,title)\
863 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
864 db = self.connect_db()
866 INSERT IGNORE INTO epg_timeline \
867 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
868 (bctype, channel, start, stop, title, desc,longdesc,category))
871 def add_multi_epg_timeline(self, tvlists):
873 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
875 db = self.connect_db()
877 INSERT IGNORE INTO epg_timeline \
878 (bctype,channel,start,stop,title,exp,longexp,category) \
879 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
882 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
883 db = self.connect_db()
886 epg_ch.chtxt,title,start,stop,exp,longexp,category \
889 WHERE epg_ch.ontv=epg_timeline.channel \
896 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
899 retall = db[1].fetchall()
902 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
903 db = self.connect_db()
906 epg_ch.chtxt,title,start,stop,exp,longexp,category \
909 WHERE epg_ch.ontv=epg_timeline.channel \
916 ( title LIKE \'%%"+keyword+"%%\' ) \
918 ( exp LIKE \'%%"+keyword+"%%\' ) \
920 ( longexp LIKE \'%%"+keyword+"%%\' ) \
922 dbcmd = db[1].execute(dbexe,(btime, etime))
925 retall = db[1].fetchall()
928 def new_in_auto_bayes_key(self):
929 db = self.connect_db()
931 db[1].execute('CREATE TABLE in_auto_bayes_key \
933 keychar VARCHAR(10),\
935 ratio_rec DECIMAL(32,14),\
936 ratio_all DECIMAL(32,14),\
937 UNIQUE unibayeskey(keychar,chtxt)\
939 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
944 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
945 db = self.connect_db()
946 ratio_rec=str(ratio_rec)
947 ratio_all=str(ratio_all)
949 INSERT IGNORE INTO in_auto_bayes_key \
950 (keychar,chtxt,ratio_rec,ratio_all) \
951 values (%s,%s,%s,%s)',\
952 (key,chtxt,ratio_rec,ratio_all))
954 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
955 db = self.connect_db()
956 add_rec_num=str(add_rec_num)
957 add_all_num=str(add_all_num)
959 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",\
960 (add_rec_num,add_all_num,chtxt))
962 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
965 db = self.connect_db()
967 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)\
970 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
971 beforenum=str(beforenum)
973 db = self.connect_db()
975 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)\
978 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
979 db = self.connect_db()
981 beforenum=str(beforenum)
982 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
983 VALUES (%s,%s,%s,%s)\
984 ON DUPLICATE KEY UPDATE \
985 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
987 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
989 list={key:addnum}のリスト
992 beforenum=str(beforenum)
993 db = self.connect_db()
994 for i,j in list.items():
995 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
997 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
998 VALUES (%s,%s,%s,%s)\
999 ON DUPLICATE KEY UPDATE \
1000 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1004 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1005 beforenum=str(beforenum)
1007 db = self.connect_db()
1009 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)\
1012 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1013 db = self.connect_db()
1014 beforenumf=beforenum
1015 beforenum=str(beforenum)
1016 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1017 VALUES (%s,%s,%s,%s)\
1018 ON DUPLICATE KEY UPDATE \
1019 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1021 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1022 beforenumf=beforenum
1023 beforenum=str(beforenum)
1024 db = self.connect_db()
1025 for i,j in list.items():
1026 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1028 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1029 VALUES (%s,%s,%s,%s)\
1030 ON DUPLICATE KEY UPDATE \
1031 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1035 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1036 db = self.connect_db()
1037 dbexe = db[1].execute("\
1038 SELECT keychar,chtxt,ratio_rec,ratio_all \
1039 FROM in_auto_bayes_key \
1040 WHERE keychar = %s AND chtxt = %s", \
1044 dls = db[1].fetchall()
1051 def new_auto_timeline_keyword(self):
1052 db = self.connect_db()
1055 CREATE TABLE auto_timeline_keyword \
1057 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1059 title VARCHAR(100),\
1062 UNIQUE uni (chtxt,title,btime,etime)\
1066 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1067 db = self.connect_db()
1069 INSERT IGNORE into auto_timeline_keyword \
1070 (chtxt,title,btime,etime) \
1071 values (%s,%s,%s,%s)', \
1072 (chtxt, title, btime, etime))
1075 def delete_old_auto_timeline_keyword(self, dhour):
1076 db = self.connect_db()
1078 DELETE FROM auto_timeline_keyword \
1080 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1082 def new_auto_timeline_bayes(self):
1083 db = self.connect_db()
1086 CREATE TABLE auto_timeline_bayes \
1088 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1090 title VARCHAR(100),\
1094 UNIQUE uni (chtxt,title,btime,etime)\
1098 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1099 db = self.connect_db()
1101 INSERT IGNORE into auto_timeline_bayes \
1102 (chtxt,title,btime,etime,point) \
1103 values (%s,%s,%s,%s,%s)', \
1104 (chtxt, title, btime, etime,point))
1106 def delete_old_auto_timeline_bayes(self, dhour):
1107 db = self.connect_db()
1109 DELETE FROM auto_timeline_bayes \
1111 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")