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