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