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