rec10/trunk/src/dbMySQL.py

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