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 select_byepgtime_over_timeline(self,epgbtime,epgetime):
533 db = self.connect_db()
535 #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 )")
536 dbr = db[1].execute("SELECT \
537 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
539 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
540 ,(epgbtime,epgetime))
541 dbl = db[1].fetchall()
543 #recdblist.printutf8(dbl)
545 recdatum=self.getdic_timeline(dbl)
547 def count_schedule_timeline(self, btime, etime):
550 return [te num,bs/cs num]
552 db = self.connect_db()
553 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
554 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
557 db[1].execute(dbexe, (btime, etime,btime,etime))
559 for typet, bctypet, chtxtt, titlet in dbl:
560 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
561 if bctypet.find("cs") > -1:
563 elif bctypet.find("bs") > -1:
565 elif bctypet.find("te") > -1:
569 def search_schedule_timeline(self,btime,etime):
572 return [(type,bctype,chtxt,title,btime,etime)]
574 db = self.connect_db()
575 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"
576 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
578 db[1].execute(dbexe, (btime, etime,btime,etime))
580 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
581 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
582 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
585 def count_epgschedule_timeline(self, epgbtime, epgetime):
588 return [te num,bs/cs num]
590 db = self.connect_db()
591 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
592 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
595 db[1].execute(dbexe, (epgbtime, epgetime))
597 for typet, bctypet, chtxtt, titlet in dbl:
598 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
599 if bctypet.find("cs") > -1:
601 elif bctypet.find("bs") > -1:
603 elif bctypet.find("te") > -1:
607 def search_epgschedule_timeline(self,epgbtime,epgetime):
610 return [(type,bctype,chtxt,title,btime,etime)]
612 db = self.connect_db()
613 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"
614 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
616 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
618 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
619 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
620 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
623 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
624 db = self.connect_db()
626 #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 )")
627 dbr = db[1].execute("SELECT \
628 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
630 WHERE btime > %s AND \
632 AND chtxt=%s ",(btime,etime,chtxt))
633 dbl = db[1].fetchall()
635 #recdblist.printutf8(dbl)
637 recdatum = self.getdic_timeline(dbl)
639 def getdic_timeline(self,timelinelists):
641 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget in timelinelists:
646 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
647 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
652 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
653 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
655 ret['epgbtime'] = "2010-01-01 00:00:00"
656 ret['epgetime'] = "2010-01-01 00:00:00"
657 ret['epgtitle'] = epgtitlet
658 ret['epgduplicate'] = epgduplicatet
659 ret['epgchange'] = epgchanget
660 if deltatime == None or deltatime == "":
662 if deltaday == None or deltaday == "":
664 if typet == recdblist.REC_KEYWORD:
665 ret['deltatime'] = deltatime
666 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
667 ret['deltatime'] = deltatime
668 ret['deltaday'] = deltaday
671 def delete_old_timeline(self, dhour):
672 db = self.connect_db()
674 DELETE FROM timeline \
676 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
679 def new_in_auto_jbk_key(self):
680 db = self.connect_db()
683 CREATE TABLE in_auto_jbk_key \
685 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
686 keyword VARCHAR(200),\
687 UNIQUE unijbk (keyword)\
692 def add_in_auto_jbk_key(self,key):
693 db = self.connect_db()
695 INSERT IGNORE into in_auto_jbk_key \
701 def select_all_in_auto_jbk_key(self):
702 db = self.connect_db()
703 dbexe = db[1].execute("\
705 FROM in_auto_jbk_key \
709 ret = db[1].fetchall()
712 def drop_in_status(self):
713 db = self.connect_db()
715 db[1].execute('drop table in_status')
719 def new_in_status(self):
720 db = self.connect_db()
723 CREATE TABLE in_status \
725 ts2avi TINYINT DEFAULT 0,\
726 terec TINYINT DEFAULT 0,\
727 bscsrec TINYINT DEFAULT 0,\
728 b252ts TINYINT DEFAULT 0,\
729 installed TINYINT DEFAULT 0\
731 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
735 def select_all_in_status(self):
736 db = self.connect_db()
737 dbexe = db[1].execute("\
738 SELECT ts2avi,terec,bscsrec,b252ts \
744 dls = db[1].fetchall()
755 def change_ts2avi_in_status(self,i):
760 db = self.connect_db()
762 UPDATE in_status SET ts2avi=ts2avi+%s",i)
764 def change_terec_in_status(self,i):
769 db = self.connect_db()
771 UPDATE in_status SET terec=terec+%s",i)
773 def change_bscsrec_in_status(self,i):
778 db = self.connect_db()
780 UPDATE in_status SET bscsrec=bscsrec+%s",i)
782 def change_b252ts_in_status(self,i):
787 db = self.connect_db()
789 UPDATE in_status SET b252ts=b252ts+%s",i)
791 def select_installed_in_status(self):
792 db = self.connect_db()
793 dbexe = db[1].execute("\
794 SELECT ts2avi,terec,bscsrec,b252ts,installed \
800 dls = db[1].fetchall()
806 def change_installed_in_status(self):
810 db = self.connect_db()
812 UPDATE in_status SET installed=1")
814 def new_epg_timeline(self, bctype):
815 db = self.connect_db()
818 DELETE FROM epg_timeline \
819 WHERE bctype = %s", \
825 CREATE TABLE epg_timeline \
828 channel VARCHAR(100) NOT NULL,\
834 category VARCHAR(100),\
835 UNIQUE unitv(bctype,channel,start,stop,title)\
841 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
842 db = self.connect_db()
844 INSERT IGNORE INTO epg_timeline \
845 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
846 (bctype, channel, start, stop, title, desc,longdesc,category))
849 def add_multi_epg_timeline(self, tvlists):
851 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
853 db = self.connect_db()
855 INSERT IGNORE INTO epg_timeline \
856 (bctype,channel,start,stop,title,exp,longexp,category) \
857 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
860 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
861 db = self.connect_db()
864 epg_ch.chtxt,title,start,stop,exp,longexp,category \
867 WHERE epg_ch.ontv=epg_timeline.channel \
874 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
877 retall = db[1].fetchall()
880 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
881 db = self.connect_db()
884 epg_ch.chtxt,title,start,stop,exp,longexp,category \
887 WHERE epg_ch.ontv=epg_timeline.channel \
894 ( title LIKE \'%%"+keyword+"%%\' ) \
896 ( exp LIKE \'%%"+keyword+"%%\' ) \
898 ( longexp LIKE \'%%"+keyword+"%%\' ) \
900 dbcmd = db[1].execute(dbexe,(btime, etime))
903 retall = db[1].fetchall()
906 def new_in_auto_bayes_key(self):
907 db = self.connect_db()
909 db[1].execute('CREATE TABLE in_auto_bayes_key \
911 keychar VARCHAR(10),\
913 ratio_rec DECIMAL(32,14),\
914 ratio_all DECIMAL(32,14),\
915 UNIQUE unibayeskey(keychar,chtxt)\
917 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
922 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
923 db = self.connect_db()
924 ratio_rec=str(ratio_rec)
925 ratio_all=str(ratio_all)
927 INSERT IGNORE INTO in_auto_bayes_key \
928 (keychar,chtxt,ratio_rec,ratio_all) \
929 values (%s,%s,%s,%s)',\
930 (key,chtxt,ratio_rec,ratio_all))
932 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
933 db = self.connect_db()
934 add_rec_num=str(add_rec_num)
935 add_all_num=str(add_all_num)
937 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",\
938 (add_rec_num,add_all_num,chtxt))
940 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
943 db = self.connect_db()
945 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)\
948 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
949 beforenum=str(beforenum)
951 db = self.connect_db()
953 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)\
956 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
957 db = self.connect_db()
959 beforenum=str(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))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
965 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
967 list={key:addnum}のリスト
970 beforenum=str(beforenum)
971 db = self.connect_db()
972 for i,j in list.items():
973 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
975 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
976 VALUES (%s,%s,%s,%s)\
977 ON DUPLICATE KEY UPDATE \
978 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
982 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
983 beforenum=str(beforenum)
985 db = self.connect_db()
987 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)\
990 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
991 db = self.connect_db()
993 beforenum=str(beforenum)
994 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
995 VALUES (%s,%s,%s,%s)\
996 ON DUPLICATE KEY UPDATE \
997 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
999 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1000 beforenumf=beforenum
1001 beforenum=str(beforenum)
1002 db = self.connect_db()
1003 for i,j in list.items():
1004 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1006 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1007 VALUES (%s,%s,%s,%s)\
1008 ON DUPLICATE KEY UPDATE \
1009 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1013 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1014 db = self.connect_db()
1015 dbexe = db[1].execute("\
1016 SELECT keychar,chtxt,ratio_rec,ratio_all \
1017 FROM in_auto_bayes_key \
1018 WHERE keychar = %s AND chtxt = %s", \
1022 dls = db[1].fetchall()
1029 def new_auto_timeline_keyword(self):
1030 db = self.connect_db()
1033 CREATE TABLE auto_timeline_keyword \
1035 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1037 title VARCHAR(100),\
1040 UNIQUE uni (chtxt,title,btime,etime)\
1044 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1045 db = self.connect_db()
1047 INSERT IGNORE into auto_timeline_keyword \
1048 (chtxt,title,btime,etime) \
1049 values (%s,%s,%s,%s)', \
1050 (chtxt, title, btime, etime))
1053 def delete_old_auto_timeline_keyword(self, dhour):
1054 db = self.connect_db()
1056 DELETE FROM auto_timeline_keyword \
1058 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1060 def new_auto_timeline_bayes(self):
1061 db = self.connect_db()
1064 CREATE TABLE auto_timeline_bayes \
1066 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1068 title VARCHAR(100),\
1072 UNIQUE uni (chtxt,title,btime,etime)\
1076 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1077 db = self.connect_db()
1079 INSERT IGNORE into auto_timeline_bayes \
1080 (chtxt,title,btime,etime,point) \
1081 values (%s,%s,%s,%s,%s)', \
1082 (chtxt, title, btime, etime,point))
1084 def delete_old_auto_timeline_bayes(self, dhour):
1085 db = self.connect_db()
1087 DELETE FROM auto_timeline_bayes \
1089 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")