rec10/trunk/src/dbMySQL.py

   1 #!/usr/bin/python
   2 # coding: UTF-8
   3 # Rec10 TS Recording Tools
   4 # Copyright (C) 2009 Yukikaze
   5 import MySQLdb
   6 import recdblist
   7 from decimal import Decimal
   8 class DB_MySQL:
   9     dbname = ""
  10     dbhost = ""
  11     dbusr = ""
  12     dbpasswd = ""
  13     dbport = 0
  14     def __init__(self, dbname, user, passwd, host="localhost", port=3306):
  15         self.dbname = dbname
  16         self.dbhost = host
  17         self.dbusr = user
  18         self.dbpasswd = passwd
  19         self.dbport = port
  20         try:
  21             con = MySQLdb.connect(user=user, passwd=passwd)
  22             cur = con.cursor()
  23             cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8")
  24             cur.close()
  25             con.close()
  26         except:
  27             ""
  28         db = self.connect_db()
  29         try:
  30             db[1].execute('\
  31             CREATE TABLE timeline \
  32             (\
  33             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
  34             type VARCHAR(40),\
  35             chtxt VARCHAR(40),\
  36             title VARCHAR(100),\
  37             btime DATETIME,\
  38             etime DATETIME,\
  39             deltatime VARCHAR(5),\
  40             deltaday VARCHAR(5),\
  41             opt VARCHAR(20),\
  42             UNIQUE uni (type,chtxt,title,btime,deltaday)\
  43             )')
  44         except:
  45             ""
  46         self.close_db(db)
  47         self.new_epg_timeline("")
  48         self.new_in_timeline_log()
  49         self.new_in_auto_bayes_key()
  50         self.new_in_auto_jbk_key()
  51         self.new_auto_proc()
  52         self.new_in_status()
  53         self.new_auto_timeline_bayes()
  54         self.new_auto_timeline_keyword()
  55     def connect_db(self):
  56         """
  57         dbへの接続
  58         """
  59         con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
  60         cur = con.cursor()
  61         cur.execute('set names utf8;')
  62         return [con, cur]
  63     def close_db(self, db):
  64         db[1].close()
  65         db[0].close()
  66     def new_epg_ch(self):
  67         db = self.connect_db()
  68         try:
  69             db[1].execute('drop table epg_ch')
  70         except:
  71             ""
  72         try:
  73             db[1].execute('\
  74             CREATE TABLE epg_ch \
  75             (\
  76             bctype VARCHAR(15),\
  77             ontv VARCHAR(30) PRIMARY KEY,\
  78             chtxt VARCHAR(20),\
  79             ch VARCHAR(20),\
  80             csch VARCHAR(20),\
  81             chname VARCHAR(100),\
  82             updatetime DATETIME,\
  83             status TINYINT,\
  84             isshow TINYINT\
  85             )')
  86         except:
  87             ""
  88         self.close_db(db)
  89     def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
  90         db = self.connect_db()
  91         db[1].execute('\
  92         INSERT INTO epg_ch \
  93         VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s)', \
  94                       (bctype, ontv, chtxt, ch, csch, updatetime, "1","1"))
  95         self.close_db(db)
  96     def select_by_ontv_epg_ch(self, ontv):
  97         db = self.connect_db()
  98         dbexe = db[1].execute("\
  99         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 100         FROM epg_ch \
 101         WHERE ontv = %s", \
 102                               (ontv,))
 103         ret = []
 104         dls = []
 105         if dbexe > 0:
 106             dls = db[1].fetchall()
 107         self.close_db(db)
 108         for dl in dls:
 109             r = list(dl)
 110             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 111             ret.append(r)
 112         return ret
 113     def select_by_chtxt_epg_ch(self, chtxt):
 114         db = self.connect_db()
 115         dbexe = db[1].execute("\
 116         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 117         FROM epg_ch \
 118         WHERE chtxt = %s", \
 119                               (chtxt,))
 120         ret = []
 121         dls = []
 122         if dbexe > 0:
 123             dls = db[1].fetchall()
 124         self.close_db(db)
 125         for dl in dls:
 126             r = list(dl)
 127             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 128             ret.append(r)
 129         return ret
 130     def select_by_bctype_epg_ch(self, bctype):
 131         db = self.connect_db()
 132         dbexe = db[1].execute("\
 133         SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
 134         FROM epg_ch \
 135         WHERE bctype = %s", \
 136                               (bctype,))
 137         ret = []
 138         dls = []
 139         if dbexe > 0:
 140             dls = db[1].fetchall()
 141         self.close_db(db)
 142         for dl in dls:
 143             #recdblist.printutf8(dl)
 144             r = list(dl)
 145             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 146             r[6] = str(r[6])
 147             ret.append(r)
 148         return ret
 149     def select_by_ch_epg_ch(self, ch):
 150         db = self.connect_db()
 151         dbexe = db[1].execute("\
 152         SELECT \
 153         bctype,ontv,chtxt,ch,csch,updatetime \
 154         FROM epg_ch \
 155         WHERE ch = %s", \
 156                               (ch,))
 157         ret = []
 158         dls = []
 159         if dbexe > 0:
 160             dls = db[1].fetchall()
 161         self.close_db(db)
 162         for dl in dls:
 163             r = list(dl)
 164             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 165             ret.append(r)
 166         return ret
 167     def select_all_epg_ch(self):
 168         db = self.connect_db()
 169         dbexe = db[1].execute("\
 170         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 171         FROM epg_ch \
 172         ")
 173         ret = []
 174         dls = []
 175         if dbexe > 0:
 176             dls = db[1].fetchall()
 177         self.close_db(db)
 178         for dl in dls:
 179             r = list(dl)
 180             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 181             ret.append(r)
 182         return ret
 183     def set_new_status(self,dhour):
 184         db = self.connect_db()
 185         dbexe = db[1].execute("UPDATE epg_ch \
 186         SET status = 1 \
 187         WHERE \
 188         ( \
 189         updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
 190         AND \
 191         status = 0 \
 192         )"\
 193         )
 194     def select_get_update_epg_ch(self, dhour):
 195         db = self.connect_db()
 196         dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
 197         WHERE (\
 198         ( \
 199         updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
 200         AND \
 201         status = 1 \
 202         ) \
 203         OR \
 204         status > 1 )\
 205         ORDER BY status DESC")
 206         ret = []
 207         #recdblist.printutf8(dbexe)
 208         if dbexe > 0:
 209             ret = db[1].fetchall()
 210         self.close_db(db)
 211         return ret
 212     def update_by_bctype_epg_ch(self, bctype):
 213         db = self.connect_db()
 214         db[1].execute("\
 215         UPDATE epg_ch \
 216         SET \
 217         updatetime=now() , \
 218         status = 1 \
 219         WHERE bctype = %s", (bctype,))
 220         self.close_db(db)
 221     def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
 222         db = self.connect_db()
 223         db[1].execute("\
 224         UPDATE epg_ch \
 225         SET \
 226         updatetime=now() , \
 227         status = 1\
 228         WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
 229         self.close_db(db)
 230     def update_status_by_bctype_epg_ch(self, bctype, status):
 231         db = self.connect_db()
 232         db[1].execute("\
 233         UPDATE epg_ch \
 234         SET \
 235         status=%s \
 236         WHERE bctype = %s", \
 237                       (status, bctype)\
 238                       )
 239         self.close_db(db)
 240     def update_chname_by_ontv_epg_ch(self,ontv,chname):
 241         db = self.connect_db()
 242         db[1].execute("\
 243         UPDATE epg_ch \
 244         SET \
 245         chname=%s \
 246         WHERE ontv = %s", \
 247                       (chname, ontv)\
 248                       )
 249         self.close_db(db)
 250     def add_auto_proc(self,type,title,chtxt):
 251         db = self.connect_db()
 252         db[1].execute('\
 253         INSERT IGNORE into auto_proc \
 254         (type,title,chtxt) \
 255         values (%s,%s,%s)',(type,title,chtxt))
 256         ##db.commit()
 257         self.close_db(db)
 258     def new_auto_proc(self):
 259         db = self.connect_db()
 260         try:
 261             db[1].execute('drop table auto_proc')
 262         except:
 263             ""
 264         try:
 265             db[1].execute('\
 266             CREATE TABLE auto_proc \
 267             (\
 268             type VARCHAR(20),\
 269             title VARCHAR(100) PRIMARY KEY,\
 270             chtxt VARCHAR(30),\
 271             UNIQUE unibayeskey(title)\
 272             )')
 273         except:
 274             ""
 275         self.close_db(db)
 276     def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
 277         db = self.connect_db()
 278         db[1].execute('\
 279         INSERT IGNORE into in_timeline_log \
 280         (chtxt,title,btime,etime,opt,exp,longexp,category) \
 281         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 282                       ( chtxt, title, btime, etime, opt,exp,longexp,category))
 283         ##db.commit()
 284         self.close_db(db)
 285     def del_in_timeline_log(self, title="", chtxt="", btime=""):
 286         """
 287
 288         """
 289         db = self.connect_db()
 290         db[1].execute("\
 291         DELETE FROM in_timeline_log \
 292         WHERE title = %s AND chtxt = %s AND btime = %s", \
 293                       (title, chtxt, btime))
 294         #db.commit()
 295         self.close_db(db)
 296     def new_in_timeline_log(self):
 297         db = self.connect_db()
 298         try:
 299             db[1].execute('\
 300             CREATE TABLE in_timeline_log \
 301             (\
 302             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 303             chtxt VARCHAR(20),\
 304             title VARCHAR(100),\
 305             btime DATETIME,\
 306             etime DATETIME,\
 307             opt VARCHAR(20),\
 308             exp VARCHAR(200),\
 309             longexp TEXT,\
 310             category VARCHAR(100),\
 311             UNIQUE uni (chtxt,title,btime,category)\
 312             )')
 313         except:
 314             ""
 315         self.close_db(db)
 316     def select_chtxt_by_title_timeline_log(self,title):
 317         db = self.connect_db()
 318         dbexe = db[1].execute("\
 319         SELECT chtxt \
 320         FROM in_timeline_log \
 321         WHERE title LIKE \"%"+title+"%\"\
 322         GROUP by chtxt\
 323         ORDER by sum(1) DESC limit 1")
 324         retdb=db[1].fetchall()
 325         ret=""
 326         if ret!=None:
 327             if len(retdb)>0:
 328                 ret=retdb[0][0]
 329         self.close_db(db)
 330         return ret
 331     def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 332         db = self.connect_db()
 333         db[1].execute('\
 334         INSERT IGNORE into timeline \
 335         (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
 336         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 337                       (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 338         ##db.commit()
 339         self.close_db(db)
 340     def del_timeline(self, type="", title="", chtxt="", btime=""):
 341         """
 342
 343         """
 344         db = self.connect_db()
 345         db[1].execute("\
 346         DELETE FROM timeline \
 347         WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
 348                       (type, title, chtxt, btime))
 349         #db.commit()
 350         self.close_db(db)
 351     def select_all_timeline(self):
 352         db = self.connect_db()
 353         recdata = []
 354         dbr = db[1].execute("\
 355         SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 356         FROM timeline")
 357         dbl = db[1].fetchall()
 358         self.close_db(db)
 359         if dbr > 0:
 360             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 361                 ret = {}
 362                 ret['type'] = typet
 363                 ret['chtxt'] = chtxt
 364                 ret['title'] = title
 365                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 366                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 367                 ret['btime'] = btime
 368                 ret['etime'] = etime
 369                 ret['opt'] = opt
 370                 ret['deltatime'] = ""
 371                 ret['deltaday'] = ""
 372                 if deltatime == None:
 373                     deltatime = "3"
 374                 if deltaday == None:
 375                     deltaday = "7"
 376                 if typet == 'key':
 377                     ret['deltatime'] = deltatime
 378                 elif typet == 'keyevery':
 379                     ret['deltatime'] = deltatime
 380                     ret['deltaday'] = deltaday
 381                 recdata.append(ret)
 382         self.close_db(db)
 383         return recdata
 384     def select_bytime_timeline(self, dminutes):
 385         db = self.connect_db()
 386         recdatum = []
 387         #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 )")
 388         dbr = db[1].execute("SELECT \
 389         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 390         FROM timeline \
 391         WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
 392         DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
 393         dbl = db[1].fetchall()
 394         self.close_db(db)
 395         #recdblist.printutf8(dbl)
 396         if dbr > 0:
 397             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 398                 ret = {}
 399                 ret['type'] = typet
 400                 ret['chtxt'] = chtxt
 401                 ret['title'] = title
 402                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 403                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 404                 ret['btime'] = btime
 405                 ret['etime'] = etime
 406                 ret['opt'] = opt
 407                 if deltatime == None or deltatime == "":
 408                     deltatime = "3"
 409                 if deltaday == None or deltaday == "":
 410                     deltaday = "7"
 411                 if typet == recdblist.REC_KEYWORD:
 412                     ret['deltatime'] = deltatime
 413                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 414                     ret['deltatime'] = deltatime
 415                     ret['deltaday'] = deltaday
 416                 recdatum.append(ret)
 417         return recdatum
 418     def select_bytime_all_timeline(self,btime,etime,chtxt):
 419         db = self.connect_db()
 420         recdatum = []
 421         #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 )")
 422         dbr = db[1].execute("SELECT \
 423         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 424         FROM timeline \
 425         WHERE btime > %s AND \
 426         etime < %s\
 427         AND chtxt=%s ",(btime,etime,chtxt))
 428         dbl = db[1].fetchall()
 429         self.close_db(db)
 430         #recdblist.printutf8(dbl)
 431         if dbr > 0:
 432             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 433                 ret = {}
 434                 ret['type'] = typet
 435                 ret['chtxt'] = chtxt
 436                 ret['title'] = title
 437                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 438                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 439                 ret['btime'] = btime
 440                 ret['etime'] = etime
 441                 ret['opt'] = opt
 442                 if deltatime == None or deltatime == "":
 443                     deltatime = "3"
 444                 if deltaday == None or deltaday == "":
 445                     deltaday = "7"
 446                 if typet == recdblist.REC_KEYWORD:
 447                     ret['deltatime'] = deltatime
 448                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 449                     ret['deltatime'] = deltatime
 450                     ret['deltaday'] = deltaday
 451                 recdatum.append(ret)
 452         return recdatum
 453     def delete_old_timeline(self, dhour):
 454         db = self.connect_db()
 455         db[1].execute("\
 456         DELETE FROM timeline \
 457         WHERE \
 458         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 459         #db.commit()
 460         self.close_db(db)
 461     def new_in_auto_jbk_key(self):
 462         db = self.connect_db()
 463         try:
 464             db[1].execute("\
 465             CREATE TABLE in_auto_jbk_key \
 466             (\
 467             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 468             keyword VARCHAR(200),\
 469             UNIQUE unijbk (keyword)\
 470             )")
 471         except:
 472             ""
 473         self.close_db(db)
 474     def add_in_auto_jbk_key(self,key):
 475         db = self.connect_db()
 476         db[1].execute('\
 477         INSERT IGNORE into in_auto_jbk_key \
 478         (keyword) \
 479         values (%s)', \
 480                       (key,))
 481         ##db.commit()
 482         self.close_db(db)
 483     def select_all_in_auto_jbk_key(self):
 484         db = self.connect_db()
 485         dbexe = db[1].execute("\
 486         SELECT keyword \
 487         FROM in_auto_jbk_key \
 488         ")
 489         ret = []
 490         if dbexe > 0:
 491             ret = db[1].fetchall()
 492         self.close_db(db)
 493         return ret
 494     def new_in_status(self):
 495         db = self.connect_db()
 496         try:
 497             db[1].execute("\
 498             CREATE TABLE in_status \
 499             (\
 500             ts2avi TINYINT DEFAULT 0,\
 501             terec TINYINT DEFAULT 0,\
 502             bscsrec TINYINT DEFAULT 0,\
 503             b252ts TINYINT DEFAULT 0,\
 504             installed TINYINT DEFAULT 0\
 505             )")
 506             db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
 507         except:
 508             ""
 509         self.close_db(db)
 510     def select_all_in_status(self):
 511         db = self.connect_db()
 512         dbexe = db[1].execute("\
 513         SELECT ts2avi,terec,bscsrec,b252ts \
 514         FROM in_status \
 515         ")
 516         ret = []
 517         dls = []
 518         if dbexe > 0:
 519             dls = db[1].fetchall()
 520         self.close_db(db)
 521         for dl in dls:
 522             r = list(dl)
 523             r[0]=str(r[0])
 524             r[1]=str(r[1])
 525             r[2]=str(r[2])
 526             r[3]=str(r[3])
 527             ret.append(r)
 528         return ret
 529
 530     def change_ts2avi_in_status(self,i):
 531         """
 532         statuをiだけ増減する
 533         iはint
 534         """
 535         db = self.connect_db()
 536         db[1].execute("\
 537         UPDATE in_status SET ts2avi=ts2avi+%s",i)
 538         self.close_db(db)
 539     def change_terec_in_status(self,i):
 540         """
 541         statuをiだけ増減する
 542         iはint
 543         """
 544         db = self.connect_db()
 545         db[1].execute("\
 546         UPDATE in_status SET terec=terec+%s",i)
 547         self.close_db(db)
 548     def change_bscsrec_in_status(self,i):
 549         """
 550         statuをiだけ増減する
 551         iはint
 552         """
 553         db = self.connect_db()
 554         db[1].execute("\
 555         UPDATE in_status SET bscsrec=bscsrec+%s",i)
 556         self.close_db(db)
 557     def change_b252ts_in_status(self,i):
 558         """
 559         statuをiだけ増減する
 560         iはint
 561         """
 562         db = self.connect_db()
 563         db[1].execute("\
 564         UPDATE in_status SET b252ts=b252ts+%s",i)
 565         self.close_db(db)
 566     def select_installed_in_status(self):
 567         db = self.connect_db()
 568         dbexe = db[1].execute("\
 569         SELECT ts2avi,terec,bscsrec,b252ts,installed \
 570         FROM in_status \
 571         ")
 572         ret = 0
 573         dls = []
 574         if dbexe > 0:
 575             dls = db[1].fetchall()
 576         self.close_db(db)
 577         for dl in dls:
 578             r = list(dl)
 579             ret=r[4]
 580         return ret
 581     def change_installed_in_status(self):
 582         """
 583         installedを設定する
 584         """
 585         db = self.connect_db()
 586         db[1].execute("\
 587         UPDATE in_status SET installed=1")
 588         self.close_db(db)
 589     def new_epg_timeline(self, bctype):
 590         db = self.connect_db()
 591         try:
 592             db[1].execute("\
 593             DELETE FROM epg_timeline \
 594             WHERE bctype = %s", \
 595                           (bctype,))
 596         except:
 597             ""
 598         try:
 599             db[1].execute("\
 600             CREATE TABLE epg_timeline \
 601             (\
 602             bctype VARCHAR(20),\
 603             channel VARCHAR(100) NOT NULL,\
 604             start VARCHAR(30),\
 605             stop  VARCHAR(30),\
 606             title VARCHAR(100),\
 607             exp VARCHAR(200),\
 608             longexp TEXT,\
 609             category VARCHAR(100),\
 610             UNIQUE unitv(bctype,channel,start,stop,title)\
 611             )")
 612         except:
 613             ""
 614         #db.commit()
 615         self.close_db(db)
 616     def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
 617         db = self.connect_db()
 618         db[1].execute('\
 619         INSERT IGNORE INTO epg_timeline \
 620         VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
 621                       (bctype, channel, start, stop, title, desc,longdesc,category))
 622         #db.commit()
 623         self.close_db(db)
 624     def add_multi_epg_timeline(self, tvlists):
 625         """
 626         tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
 627         """
 628         db = self.connect_db()
 629         db[1].executemany('\
 630         INSERT IGNORE INTO epg_timeline \
 631         (bctype,channel,start,stop,title,exp,longexp,category) \
 632         values(%s,%s,%s,%s,%s,%s,%s,%s)', \
 633                           tvlists)
 634         self.close_db(db)
 635     def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
 636         db = self.connect_db()
 637         dbexe = "\
 638         SELECT \
 639         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 640         FROM epg_timeline \
 641         INNER JOIN epg_ch \
 642         WHERE epg_ch.ontv=epg_timeline.channel \
 643         AND \
 644         start >= %s \
 645         AND \
 646         start <= %s \
 647         AND \
 648         epg_ch.chtxt=%s"
 649         dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
 650         retall = []
 651         if dbcmd > 0:
 652             retall = db[1].fetchall()
 653         self.close_db(db)
 654         return retall
 655     def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
 656         db = self.connect_db()
 657         dbexe = "\
 658         SELECT \
 659         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 660         FROM epg_timeline \
 661         INNER JOIN epg_ch \
 662         WHERE epg_ch.ontv=epg_timeline.channel \
 663         AND \
 664         start >= %s \
 665         AND \
 666         stop <= %s \
 667         AND \
 668         ( \
 669         ( title LIKE \'%%"+keyword+"%%\' ) \
 670         OR \
 671         ( exp LIKE \'%%"+keyword+"%%\' ) \
 672         OR \
 673         ( longexp LIKE \'%%"+keyword+"%%\' ) \
 674         )"
 675         dbcmd = db[1].execute(dbexe,(btime, etime))
 676         retall = []
 677         if dbcmd > 0:
 678             retall = db[1].fetchall()
 679         self.close_db(db)
 680         return retall
 681     def new_in_auto_bayes_key(self):
 682         db = self.connect_db()
 683         try:
 684             db[1].execute('CREATE TABLE in_auto_bayes_key \
 685             (\
 686             keychar VARCHAR(10),\
 687             chtxt VARCHAR(20),\
 688             ratio_rec DECIMAL(32,14),\
 689             ratio_all DECIMAL(32,14),\
 690             UNIQUE unibayeskey(keychar,chtxt)\
 691             )')
 692             db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
 693         except:
 694             ""
 695         self.close_db(db)
 696
 697     def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
 698         db = self.connect_db()
 699         ratio_rec=str(ratio_rec)
 700         ratio_all=str(ratio_all)
 701         db[1].execute('\
 702         INSERT IGNORE INTO in_auto_bayes_key \
 703         (keychar,chtxt,ratio_rec,ratio_all) \
 704         values (%s,%s,%s,%s)',\
 705         (key,chtxt,ratio_rec,ratio_all))
 706         self.close_db(db)
 707     def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
 708         db = self.connect_db()
 709         add_rec_num=str(add_rec_num)
 710         add_all_num=str(add_all_num)
 711         db[1].execute("\
 712         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",\
 713         (add_rec_num,add_all_num,chtxt))
 714         self.close_db(db)
 715     def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
 716         """
 717         """
 718         db = self.connect_db()
 719         db[1].execute("\
 720         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)\
 721         )
 722         self.close_db(db)
 723     def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 724         beforenum=str(beforenum)
 725         newnum=str(newnum)
 726         db = self.connect_db()
 727         db[1].execute("\
 728         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)\
 729         )
 730         self.close_db(db)
 731     def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 732         db = self.connect_db()
 733         beforenumf=beforenum
 734         beforenum=str(beforenum)
 735         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 736         VALUES (%s,%s,%s,%s)\
 737         ON DUPLICATE KEY UPDATE \
 738         ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
 739         self.close_db(db)
 740     def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
 741         """
 742         list={key:addnum}のリスト
 743         """
 744         beforenumf=beforenum
 745         beforenum=str(beforenum)
 746         db = self.connect_db()
 747         for i,j in list.items():
 748             retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
 749             db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 750             VALUES (%s,%s,%s,%s)\
 751             ON DUPLICATE KEY UPDATE \
 752             ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
 753         self.close_db(db)
 754     def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 755         beforenum=str(beforenum)
 756         newnum=str(newnum)
 757         db = self.connect_db()
 758         db[1].execute("\
 759         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)\
 760         )
 761         self.close_db(db)
 762     def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 763         db = self.connect_db()
 764         beforenumf=beforenum
 765         beforenum=str(beforenum)
 766         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 767         VALUES (%s,%s,%s,%s)\
 768         ON DUPLICATE KEY UPDATE \
 769         ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
 770         self.close_db(db)
 771     def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
 772         beforenumf=beforenum
 773         beforenum=str(beforenum)
 774         db = self.connect_db()
 775         for i,j in list.items():
 776             retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
 777             db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 778             VALUES (%s,%s,%s,%s)\
 779             ON DUPLICATE KEY UPDATE \
 780             ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
 781         self.close_db(db)
 782     def select_by_key_in_auto_bayes_key(self,key,chtxt):
 783         db = self.connect_db()
 784         dbexe = db[1].execute("\
 785         SELECT keychar,chtxt,ratio_rec,ratio_all \
 786         FROM in_auto_bayes_key \
 787         WHERE keychar = %s AND chtxt = %s", \
 788                               (key,chtxt))
 789         dls = []
 790         if dbexe > 0:
 791             dls = db[1].fetchall()
 792         self.close_db(db)
 793         if len(dls)>0:
 794             return dls[0]
 795         else:
 796             return dls
 797
 798     def new_auto_timeline_keyword(self):
 799         db = self.connect_db()
 800         try:
 801             db[1].execute('\
 802             CREATE TABLE auto_timeline_keyword \
 803             (\
 804             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 805             chtxt VARCHAR(40),\
 806             title VARCHAR(100),\
 807             btime DATETIME,\
 808             etime DATETIME,\
 809             UNIQUE uni (chtxt,title,btime,etime)\
 810             )')
 811         except:
 812             ""
 813     def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
 814         db = self.connect_db()
 815         db[1].execute('\
 816         INSERT IGNORE into auto_timeline_keyword \
 817         (chtxt,title,btime,etime) \
 818         values (%s,%s,%s,%s)', \
 819                        (chtxt, title, btime, etime))
 820         ##db.commit()
 821         self.close_db(db)
 822     def delete_old_auto_timeline_keyword(self, dhour):
 823         db = self.connect_db()
 824         db[1].execute("\
 825         DELETE FROM auto_timeline_keyword \
 826         WHERE \
 827         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 828         self.close_db(db)
 829     def new_auto_timeline_bayes(self):
 830         db = self.connect_db()
 831         try:
 832             db[1].execute('\
 833             CREATE TABLE auto_timeline_bayes \
 834             (\
 835             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 836             chtxt VARCHAR(40),\
 837             title VARCHAR(100),\
 838             btime DATETIME,\
 839             etime DATETIME,\
 840             point INT,\
 841             UNIQUE uni (chtxt,title,btime,etime)\
 842             )')
 843         except:
 844             ""
 845     def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
 846         db = self.connect_db()
 847         db[1].execute('\
 848         INSERT IGNORE into auto_timeline_bayes \
 849         (chtxt,title,btime,etime,point) \
 850         values (%s,%s,%s,%s,%s)', \
 851                       (chtxt, title, btime, etime,point))
 852         self.close_db(db)
 853     def delete_old_auto_timeline_bayes(self, dhour):
 854         db = self.connect_db()
 855         db[1].execute("\
 856         DELETE FROM auto_timeline_bayes \
 857         WHERE \
 858         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 859         self.close_db(db)