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_in_status()
  52         self.new_auto_timeline_bayes()
  53         self.new_auto_timeline_keyword()
  54         self.new_auto_proc_tmp()
  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_tmp(self,type,title,chtxt):
 251         db = self.connect_db()
 252         db[1].execute('\
 253         INSERT IGNORE into auto_proc_tmp \
 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_tmp(self):
 259         db = self.connect_db()
 260         try:
 261             db[1].execute('drop table auto_proc_tmp')
 262         except:
 263             ""
 264         try:
 265             db[1].execute('\
 266             CREATE TABLE auto_proc_tmp \
 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 update_auto_proc(self):
 277         db = self.connect_db()
 278         try:
 279             db[1].execute('\
 280             INSERT INTO auto_proc SELECT * FROM auto_proc_tmp\
 281             )')
 282         except:
 283             ""
 284         self.close_db(db)
 285     def new_auto_proc(self):
 286         db = self.connect_db()
 287         try:
 288             db[1].execute('drop table auto_proc')
 289         except:
 290             ""
 291         try:
 292             db[1].execute('\
 293             CREATE TABLE auto_proc \
 294             (\
 295             type VARCHAR(20),\
 296             title VARCHAR(100) PRIMARY KEY,\
 297             chtxt VARCHAR(30),\
 298             UNIQUE unibayeskey(title)\
 299             )')
 300         except:
 301             ""
 302         self.close_db(db)
 303     def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
 304         db = self.connect_db()
 305         db[1].execute('\
 306         INSERT IGNORE into in_timeline_log \
 307         (chtxt,title,btime,etime,opt,exp,longexp,category) \
 308         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 309                       ( chtxt, title, btime, etime, opt,exp,longexp,category))
 310         ##db.commit()
 311         self.close_db(db)
 312     def del_in_timeline_log(self, title="", chtxt="", btime=""):
 313         """
 314
 315         """
 316         db = self.connect_db()
 317         db[1].execute("\
 318         DELETE FROM in_timeline_log \
 319         WHERE title = %s AND chtxt = %s AND btime = %s", \
 320                       (title, chtxt, btime))
 321         #db.commit()
 322         self.close_db(db)
 323     def new_in_timeline_log(self):
 324         db = self.connect_db()
 325         try:
 326             db[1].execute('\
 327             CREATE TABLE in_timeline_log \
 328             (\
 329             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 330             chtxt VARCHAR(20),\
 331             title VARCHAR(100),\
 332             btime DATETIME,\
 333             etime DATETIME,\
 334             opt VARCHAR(20),\
 335             exp VARCHAR(200),\
 336             longexp TEXT,\
 337             category VARCHAR(100),\
 338             UNIQUE uni (chtxt,title,btime,category)\
 339             )')
 340         except:
 341             ""
 342         self.close_db(db)
 343     def select_chtxt_by_title_timeline_log(self,title):
 344         db = self.connect_db()
 345         dbexe = db[1].execute("\
 346         SELECT chtxt \
 347         FROM in_timeline_log \
 348         WHERE title LIKE \"%"+title+"%\"\
 349         GROUP by chtxt\
 350         ORDER by sum(1) DESC limit 1")
 351         retdb=db[1].fetchall()
 352         ret=""
 353         if ret!=None:
 354             if len(retdb)>0:
 355                 ret=retdb[0][0]
 356         self.close_db(db)
 357         return ret
 358     def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 359         db = self.connect_db()
 360         db[1].execute('\
 361         INSERT IGNORE into timeline \
 362         (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
 363         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 364                       (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 365         ##db.commit()
 366         self.close_db(db)
 367     def del_timeline(self, type="", title="", chtxt="", btime=""):
 368         """
 369
 370         """
 371         db = self.connect_db()
 372         db[1].execute("\
 373         DELETE FROM timeline \
 374         WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
 375                       (type, title, chtxt, btime))
 376         #db.commit()
 377         self.close_db(db)
 378     def select_all_timeline(self):
 379         db = self.connect_db()
 380         recdata = []
 381         dbr = db[1].execute("\
 382         SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 383         FROM timeline")
 384         dbl = db[1].fetchall()
 385         self.close_db(db)
 386         if dbr > 0:
 387             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 388                 ret = {}
 389                 ret['type'] = typet
 390                 ret['chtxt'] = chtxt
 391                 ret['title'] = title
 392                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 393                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 394                 ret['btime'] = btime
 395                 ret['etime'] = etime
 396                 ret['opt'] = opt
 397                 ret['deltatime'] = ""
 398                 ret['deltaday'] = ""
 399                 if deltatime == None:
 400                     deltatime = "3"
 401                 if deltaday == None:
 402                     deltaday = "7"
 403                 if typet == 'key':
 404                     ret['deltatime'] = deltatime
 405                 elif typet == 'keyevery':
 406                     ret['deltatime'] = deltatime
 407                     ret['deltaday'] = deltaday
 408                 recdata.append(ret)
 409         self.close_db(db)
 410         return recdata
 411     def select_bytime_timeline(self, dminutes):
 412         db = self.connect_db()
 413         recdatum = []
 414         #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 )")
 415         dbr = db[1].execute("SELECT \
 416         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 417         FROM timeline \
 418         WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
 419         DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
 420         dbl = db[1].fetchall()
 421         self.close_db(db)
 422         #recdblist.printutf8(dbl)
 423         if dbr > 0:
 424             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 425                 ret = {}
 426                 ret['type'] = typet
 427                 ret['chtxt'] = chtxt
 428                 ret['title'] = title
 429                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 430                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 431                 ret['btime'] = btime
 432                 ret['etime'] = etime
 433                 ret['opt'] = opt
 434                 if deltatime == None or deltatime == "":
 435                     deltatime = "3"
 436                 if deltaday == None or deltaday == "":
 437                     deltaday = "7"
 438                 if typet == recdblist.REC_KEYWORD:
 439                     ret['deltatime'] = deltatime
 440                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 441                     ret['deltatime'] = deltatime
 442                     ret['deltaday'] = deltaday
 443                 recdatum.append(ret)
 444         return recdatum
 445     def select_bytime_all_timeline(self,btime,etime,chtxt):
 446         db = self.connect_db()
 447         recdatum = []
 448         #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 )")
 449         dbr = db[1].execute("SELECT \
 450         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 451         FROM timeline \
 452         WHERE btime > %s AND \
 453         etime < %s\
 454         AND chtxt=%s ",(btime,etime,chtxt))
 455         dbl = db[1].fetchall()
 456         self.close_db(db)
 457         #recdblist.printutf8(dbl)
 458         if dbr > 0:
 459             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 460                 ret = {}
 461                 ret['type'] = typet
 462                 ret['chtxt'] = chtxt
 463                 ret['title'] = title
 464                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 465                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 466                 ret['btime'] = btime
 467                 ret['etime'] = etime
 468                 ret['opt'] = opt
 469                 if deltatime == None or deltatime == "":
 470                     deltatime = "3"
 471                 if deltaday == None or deltaday == "":
 472                     deltaday = "7"
 473                 if typet == recdblist.REC_KEYWORD:
 474                     ret['deltatime'] = deltatime
 475                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 476                     ret['deltatime'] = deltatime
 477                     ret['deltaday'] = deltaday
 478                 recdatum.append(ret)
 479         return recdatum
 480     def delete_old_timeline(self, dhour):
 481         db = self.connect_db()
 482         db[1].execute("\
 483         DELETE FROM timeline \
 484         WHERE \
 485         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 486         #db.commit()
 487         self.close_db(db)
 488     def new_in_auto_jbk_key(self):
 489         db = self.connect_db()
 490         try:
 491             db[1].execute("\
 492             CREATE TABLE in_auto_jbk_key \
 493             (\
 494             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 495             keyword VARCHAR(200),\
 496             UNIQUE unijbk (keyword)\
 497             )")
 498         except:
 499             ""
 500         self.close_db(db)
 501     def add_in_auto_jbk_key(self,key):
 502         db = self.connect_db()
 503         db[1].execute('\
 504         INSERT IGNORE into in_auto_jbk_key \
 505         (keyword) \
 506         values (%s)', \
 507                       (key,))
 508         ##db.commit()
 509         self.close_db(db)
 510     def select_all_in_auto_jbk_key(self):
 511         db = self.connect_db()
 512         dbexe = db[1].execute("\
 513         SELECT keyword \
 514         FROM in_auto_jbk_key \
 515         ")
 516         ret = []
 517         if dbexe > 0:
 518             ret = db[1].fetchall()
 519         self.close_db(db)
 520         return ret
 521     def new_in_status(self):
 522         db = self.connect_db()
 523         try:
 524             db[1].execute("\
 525             CREATE TABLE in_status \
 526             (\
 527             ts2avi TINYINT DEFAULT 0,\
 528             terec TINYINT DEFAULT 0,\
 529             bscsrec TINYINT DEFAULT 0,\
 530             b252ts TINYINT DEFAULT 0,\
 531             installed TINYINT DEFAULT 0\
 532             )")
 533             db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
 534         except:
 535             ""
 536         self.close_db(db)
 537     def select_all_in_status(self):
 538         db = self.connect_db()
 539         dbexe = db[1].execute("\
 540         SELECT ts2avi,terec,bscsrec,b252ts \
 541         FROM in_status \
 542         ")
 543         ret = []
 544         dls = []
 545         if dbexe > 0:
 546             dls = db[1].fetchall()
 547         self.close_db(db)
 548         for dl in dls:
 549             r = list(dl)
 550             r[0]=str(r[0])
 551             r[1]=str(r[1])
 552             r[2]=str(r[2])
 553             r[3]=str(r[3])
 554             ret.append(r)
 555         return ret
 556
 557     def change_ts2avi_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 ts2avi=ts2avi+%s",i)
 565         self.close_db(db)
 566     def change_terec_in_status(self,i):
 567         """
 568         statuをiだけ増減する
 569         iはint
 570         """
 571         db = self.connect_db()
 572         db[1].execute("\
 573         UPDATE in_status SET terec=terec+%s",i)
 574         self.close_db(db)
 575     def change_bscsrec_in_status(self,i):
 576         """
 577         statuをiだけ増減する
 578         iはint
 579         """
 580         db = self.connect_db()
 581         db[1].execute("\
 582         UPDATE in_status SET bscsrec=bscsrec+%s",i)
 583         self.close_db(db)
 584     def change_b252ts_in_status(self,i):
 585         """
 586         statuをiだけ増減する
 587         iはint
 588         """
 589         db = self.connect_db()
 590         db[1].execute("\
 591         UPDATE in_status SET b252ts=b252ts+%s",i)
 592         self.close_db(db)
 593     def select_installed_in_status(self):
 594         db = self.connect_db()
 595         dbexe = db[1].execute("\
 596         SELECT ts2avi,terec,bscsrec,b252ts,installed \
 597         FROM in_status \
 598         ")
 599         ret = 0
 600         dls = []
 601         if dbexe > 0:
 602             dls = db[1].fetchall()
 603         self.close_db(db)
 604         for dl in dls:
 605             r = list(dl)
 606             ret=r[4]
 607         return ret
 608     def change_installed_in_status(self):
 609         """
 610         installedを設定する
 611         """
 612         db = self.connect_db()
 613         db[1].execute("\
 614         UPDATE in_status SET installed=1")
 615         self.close_db(db)
 616     def new_epg_timeline(self, bctype):
 617         db = self.connect_db()
 618         try:
 619             db[1].execute("\
 620             DELETE FROM epg_timeline \
 621             WHERE bctype = %s", \
 622                           (bctype,))
 623         except:
 624             ""
 625         try:
 626             db[1].execute("\
 627             CREATE TABLE epg_timeline \
 628             (\
 629             bctype VARCHAR(20),\
 630             channel VARCHAR(100) NOT NULL,\
 631             start VARCHAR(30),\
 632             stop  VARCHAR(30),\
 633             title VARCHAR(100),\
 634             exp VARCHAR(200),\
 635             longexp TEXT,\
 636             category VARCHAR(100),\
 637             UNIQUE unitv(bctype,channel,start,stop,title)\
 638             )")
 639         except:
 640             ""
 641         #db.commit()
 642         self.close_db(db)
 643     def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
 644         db = self.connect_db()
 645         db[1].execute('\
 646         INSERT IGNORE INTO epg_timeline \
 647         VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
 648                       (bctype, channel, start, stop, title, desc,longdesc,category))
 649         #db.commit()
 650         self.close_db(db)
 651     def add_multi_epg_timeline(self, tvlists):
 652         """
 653         tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
 654         """
 655         db = self.connect_db()
 656         db[1].executemany('\
 657         INSERT IGNORE INTO epg_timeline \
 658         (bctype,channel,start,stop,title,exp,longexp,category) \
 659         values(%s,%s,%s,%s,%s,%s,%s,%s)', \
 660                           tvlists)
 661         self.close_db(db)
 662     def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
 663         db = self.connect_db()
 664         dbexe = "\
 665         SELECT \
 666         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 667         FROM epg_timeline \
 668         INNER JOIN epg_ch \
 669         WHERE epg_ch.ontv=epg_timeline.channel \
 670         AND \
 671         start >= %s \
 672         AND \
 673         start <= %s \
 674         AND \
 675         epg_ch.chtxt=%s"
 676         dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
 677         retall = []
 678         if dbcmd > 0:
 679             retall = db[1].fetchall()
 680         self.close_db(db)
 681         return retall
 682     def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
 683         db = self.connect_db()
 684         dbexe = "\
 685         SELECT \
 686         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 687         FROM epg_timeline \
 688         INNER JOIN epg_ch \
 689         WHERE epg_ch.ontv=epg_timeline.channel \
 690         AND \
 691         start >= %s \
 692         AND \
 693         stop <= %s \
 694         AND \
 695         ( \
 696         ( title LIKE \'%%"+keyword+"%%\' ) \
 697         OR \
 698         ( exp LIKE \'%%"+keyword+"%%\' ) \
 699         OR \
 700         ( longexp LIKE \'%%"+keyword+"%%\' ) \
 701         )"
 702         dbcmd = db[1].execute(dbexe,(btime, etime))
 703         retall = []
 704         if dbcmd > 0:
 705             retall = db[1].fetchall()
 706         self.close_db(db)
 707         return retall
 708     def new_in_auto_bayes_key(self):
 709         db = self.connect_db()
 710         try:
 711             db[1].execute('CREATE TABLE in_auto_bayes_key \
 712             (\
 713             keychar VARCHAR(10),\
 714             chtxt VARCHAR(20),\
 715             ratio_rec DECIMAL(32,14),\
 716             ratio_all DECIMAL(32,14),\
 717             UNIQUE unibayeskey(keychar,chtxt)\
 718             )')
 719             db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
 720         except:
 721             ""
 722         self.close_db(db)
 723
 724     def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
 725         db = self.connect_db()
 726         ratio_rec=str(ratio_rec)
 727         ratio_all=str(ratio_all)
 728         db[1].execute('\
 729         INSERT IGNORE INTO in_auto_bayes_key \
 730         (keychar,chtxt,ratio_rec,ratio_all) \
 731         values (%s,%s,%s,%s)',\
 732         (key,chtxt,ratio_rec,ratio_all))
 733         self.close_db(db)
 734     def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
 735         db = self.connect_db()
 736         add_rec_num=str(add_rec_num)
 737         add_all_num=str(add_all_num)
 738         db[1].execute("\
 739         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",\
 740         (add_rec_num,add_all_num,chtxt))
 741         self.close_db(db)
 742     def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
 743         """
 744         """
 745         db = self.connect_db()
 746         db[1].execute("\
 747         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)\
 748         )
 749         self.close_db(db)
 750     def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 751         beforenum=str(beforenum)
 752         newnum=str(newnum)
 753         db = self.connect_db()
 754         db[1].execute("\
 755         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)\
 756         )
 757         self.close_db(db)
 758     def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 759         db = self.connect_db()
 760         beforenumf=beforenum
 761         beforenum=str(beforenum)
 762         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 763         VALUES (%s,%s,%s,%s)\
 764         ON DUPLICATE KEY UPDATE \
 765         ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
 766         self.close_db(db)
 767     def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
 768         """
 769         list={key:addnum}のリスト
 770         """
 771         beforenumf=beforenum
 772         beforenum=str(beforenum)
 773         db = self.connect_db()
 774         for i,j in list.items():
 775             retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
 776             db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 777             VALUES (%s,%s,%s,%s)\
 778             ON DUPLICATE KEY UPDATE \
 779             ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
 780         self.close_db(db)
 781     def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 782         beforenum=str(beforenum)
 783         newnum=str(newnum)
 784         db = self.connect_db()
 785         db[1].execute("\
 786         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)\
 787         )
 788         self.close_db(db)
 789     def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 790         db = self.connect_db()
 791         beforenumf=beforenum
 792         beforenum=str(beforenum)
 793         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 794         VALUES (%s,%s,%s,%s)\
 795         ON DUPLICATE KEY UPDATE \
 796         ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
 797         self.close_db(db)
 798     def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
 799         beforenumf=beforenum
 800         beforenum=str(beforenum)
 801         db = self.connect_db()
 802         for i,j in list.items():
 803             retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
 804             db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 805             VALUES (%s,%s,%s,%s)\
 806             ON DUPLICATE KEY UPDATE \
 807             ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
 808         self.close_db(db)
 809     def select_by_key_in_auto_bayes_key(self,key,chtxt):
 810         db = self.connect_db()
 811         dbexe = db[1].execute("\
 812         SELECT keychar,chtxt,ratio_rec,ratio_all \
 813         FROM in_auto_bayes_key \
 814         WHERE keychar = %s AND chtxt = %s", \
 815                               (key,chtxt))
 816         dls = []
 817         if dbexe > 0:
 818             dls = db[1].fetchall()
 819         self.close_db(db)
 820         if len(dls)>0:
 821             return dls[0]
 822         else:
 823             return dls
 824
 825     def new_auto_timeline_keyword(self):
 826         db = self.connect_db()
 827         try:
 828             db[1].execute('\
 829             CREATE TABLE auto_timeline_keyword \
 830             (\
 831             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 832             chtxt VARCHAR(40),\
 833             title VARCHAR(100),\
 834             btime DATETIME,\
 835             etime DATETIME,\
 836             UNIQUE uni (chtxt,title,btime,etime)\
 837             )')
 838         except:
 839             ""
 840     def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
 841         db = self.connect_db()
 842         db[1].execute('\
 843         INSERT IGNORE into auto_timeline_keyword \
 844         (chtxt,title,btime,etime) \
 845         values (%s,%s,%s,%s)', \
 846                        (chtxt, title, btime, etime))
 847         ##db.commit()
 848         self.close_db(db)
 849     def delete_old_auto_timeline_keyword(self, dhour):
 850         db = self.connect_db()
 851         db[1].execute("\
 852         DELETE FROM auto_timeline_keyword \
 853         WHERE \
 854         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 855         self.close_db(db)
 856     def new_auto_timeline_bayes(self):
 857         db = self.connect_db()
 858         try:
 859             db[1].execute('\
 860             CREATE TABLE auto_timeline_bayes \
 861             (\
 862             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 863             chtxt VARCHAR(40),\
 864             title VARCHAR(100),\
 865             btime DATETIME,\
 866             etime DATETIME,\
 867             point INT,\
 868             UNIQUE uni (chtxt,title,btime,etime)\
 869             )')
 870         except:
 871             ""
 872     def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
 873         db = self.connect_db()
 874         db[1].execute('\
 875         INSERT IGNORE into auto_timeline_bayes \
 876         (chtxt,title,btime,etime,point) \
 877         values (%s,%s,%s,%s,%s)', \
 878                       (chtxt, title, btime, etime,point))
 879         self.close_db(db)
 880     def delete_old_auto_timeline_bayes(self, dhour):
 881         db = self.connect_db()
 882         db[1].execute("\
 883         DELETE FROM auto_timeline_bayes \
 884         WHERE \
 885         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 886         self.close_db(db)