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