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     def connect_db(self):
  53         """
  54         dbへの接続
  55         """
  56         con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
  57         cur = con.cursor()
  58         cur.execute('set names utf8;')
  59         return [con, cur]
  60     def close_db(self, db):
  61         db[1].close()
  62         db[0].close()
  63     def new_epg_ch(self):
  64         db = self.connect_db()
  65         try:
  66             db[1].execute('drop table epg_ch')
  67         except:
  68             ""
  69         try:
  70             db[1].execute('\
  71             CREATE TABLE epg_ch \
  72             (\
  73             bctype VARCHAR(15),\
  74             ontv VARCHAR(30) PRIMARY KEY,\
  75             chtxt VARCHAR(15),\
  76             ch VARCHAR(20),\
  77             csch VARCHAR(20),\
  78             chname VARCHAR(40),\
  79             updatetime DATETIME,\
  80             status TINYINT\
  81             )')
  82         except:
  83             ""
  84         self.close_db(db)
  85     def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
  86         db = self.connect_db()
  87         db[1].execute('\
  88         INSERT INTO epg_ch \
  89         VALUES (%s,%s,%s,%s,%s,"",%s,%s)', \
  90                       (bctype, ontv, chtxt, ch, csch, updatetime, "1"))
  91         self.close_db(db)
  92     def select_by_ontv_epg_ch(self, ontv):
  93         db = self.connect_db()
  94         dbexe = db[1].execute("\
  95         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
  96         FROM epg_ch \
  97         WHERE ontv = %s", \
  98                               (ontv,))
  99         ret = []
 100         dls = []
 101         if dbexe > 0:
 102             dls = db[1].fetchall()
 103         self.close_db(db)
 104         for dl in dls:
 105             r = list(dl)
 106             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 107             ret.append(r)
 108         return ret
 109     def select_by_chtxt_epg_ch(self, chtxt):
 110         db = self.connect_db()
 111         dbexe = db[1].execute("\
 112         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 113         FROM epg_ch \
 114         WHERE chtxt = %s", \
 115                               (chtxt,))
 116         ret = []
 117         dls = []
 118         if dbexe > 0:
 119             dls = db[1].fetchall()
 120         self.close_db(db)
 121         for dl in dls:
 122             r = list(dl)
 123             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 124             ret.append(r)
 125         return ret
 126     def select_by_bctype_epg_ch(self, bctype):
 127         db = self.connect_db()
 128         dbexe = db[1].execute("\
 129         SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
 130         FROM epg_ch \
 131         WHERE bctype = %s", \
 132                               (bctype,))
 133         ret = []
 134         dls = []
 135         if dbexe > 0:
 136             dls = db[1].fetchall()
 137         self.close_db(db)
 138         for dl in dls:
 139             #print dl
 140             r = list(dl)
 141             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 142             r[6] = str(r[6])
 143             ret.append(r)
 144         return ret
 145     def select_by_ch_epg_ch(self, ch):
 146         db = self.connect_db()
 147         dbexe = db[1].execute("\
 148         SELECT \
 149         bctype,ontv,chtxt,ch,csch,updatetime \
 150         FROM epg_ch \
 151         WHERE ch = %s", \
 152                               (ch,))
 153         ret = []
 154         dls = []
 155         if dbexe > 0:
 156             dls = db[1].fetchall()
 157         self.close_db(db)
 158         for dl in dls:
 159             r = list(dl)
 160             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 161             ret.append(r)
 162         return ret
 163     def select_all_epg_ch(self):
 164         db = self.connect_db()
 165         dbexe = db[1].execute("\
 166         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 167         FROM epg_ch \
 168         ")
 169         ret = []
 170         dls = []
 171         if dbexe > 0:
 172             dls = db[1].fetchall()
 173         self.close_db(db)
 174         for dl in dls:
 175             r = list(dl)
 176             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 177             ret.append(r)
 178         return ret
 179     def select_get_update_epg_ch(self, dhour):
 180         db = self.connect_db()
 181         dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
 182         WHERE \
 183         ( \
 184         updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
 185         AND \
 186         status = 1 \
 187         ) \
 188         OR \
 189         status > 1 \
 190         ORDER BY status DESC")
 191         ret = []
 192         #print dbexe
 193         if dbexe > 0:
 194             ret = db[1].fetchall()
 195         self.close_db(db)
 196         return ret
 197     def update_by_bctype_epg_ch(self, bctype):
 198         db = self.connect_db()
 199         db[1].execute("\
 200         UPDATE epg_ch \
 201         SET \
 202         updatetime=now() , \
 203         status = 1 \
 204         WHERE bctype = %s", (bctype,))
 205         self.close_db(db)
 206     def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
 207         db = self.connect_db()
 208         db[1].execute("\
 209         UPDATE epg_ch \
 210         SET \
 211         updatetime=now() , \
 212         status = 1\
 213         WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
 214         self.close_db(db)
 215     def update_status_by_bctype_epg_ch(self, bctype, status):
 216         db = self.connect_db()
 217         db[1].execute("\
 218         UPDATE epg_ch \
 219         SET \
 220         status=%s \
 221         WHERE bctype = %s", \
 222                       (status, bctype)\
 223                       )
 224         self.close_db(db)
 225     def update_chname_by_ontv_epg_ch(self,ontv,chname):
 226         db = self.connect_db()
 227         db[1].execute("\
 228         UPDATE epg_ch \
 229         SET \
 230         chname=%s \
 231         WHERE ontv = %s", \
 232                       (chname, ontv)\
 233                       )
 234         self.close_db(db)
 235     def add_auto_proc(self,type,title):
 236         db = self.connect_db()
 237         db[1].execute('\
 238         INSERT IGNORE into auto_proc \
 239         (type,title) \
 240         values (%s,%s)', \
 241                       ( type, title))
 242         ##db.commit()
 243         self.close_db(db)
 244     def new_auto_proc(self):
 245         db = self.connect_db()
 246         try:
 247             db[1].execute('drop table auto_proc')
 248         except:
 249             ""
 250         try:
 251             db[1].execute('\
 252             CREATE TABLE auto_proc \
 253             (\
 254             type VARCHAR(20),\
 255             title VARCHAR(100) PRIMARY KEY,\
 256             UNIQUE unibayeskey(title)\
 257             )')
 258         except:
 259             ""
 260         self.close_db(db)
 261     def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
 262         db = self.connect_db()
 263         db[1].execute('\
 264         INSERT IGNORE into in_timeline_log \
 265         (chtxt,title,btime,etime,opt,exp,longexp,category) \
 266         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 267                       ( chtxt, title, btime, etime, opt,exp,longexp,category))
 268         ##db.commit()
 269         self.close_db(db)
 270     def del_in_timeline_log(self, title="", chtxt="", btime=""):
 271         """
 272
 273         """
 274         db = self.connect_db()
 275         db[1].execute("\
 276         DELETE FROM in_timeline_log \
 277         WHERE title = %s AND chtxt = %s AND btime = %s", \
 278                       (title, chtxt, btime))
 279         #db.commit()
 280         self.close_db(db)
 281     def new_in_timeline_log(self):
 282         db = self.connect_db()
 283         try:
 284             db[1].execute('\
 285             CREATE TABLE in_timeline_log \
 286             (\
 287             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 288             chtxt VARCHAR(20),\
 289             title VARCHAR(100),\
 290             btime DATETIME,\
 291             etime DATETIME,\
 292             opt VARCHAR(20),\
 293             exp VARCHAR(200),\
 294             longexp TEXT,\
 295             category VARCHAR(100),\
 296             UNIQUE uni (chtxt,title,btime,category)\
 297             )')
 298         except:
 299             ""
 300         self.close_db(db)
 301     def add_main_timetable(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 302         db = self.connect_db()
 303         db[1].execute('\
 304         INSERT IGNORE into main_timetable \
 305         (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
 306         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 307                       (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 308         ##db.commit()
 309         self.close_db(db)
 310     def del_main_timetable(self, type="", title="", chtxt="", btime=""):
 311         """
 312
 313         """
 314         db = self.connect_db()
 315         db[1].execute("\
 316         DELETE FROM main_timetable \
 317         WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
 318                       (type, title, chtxt, btime))
 319         #db.commit()
 320         self.close_db(db)
 321     def select_all_main_timetable(self):
 322         db = self.connect_db()
 323         recdata = []
 324         dbr = db[1].execute("\
 325         SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 326         FROM main_timetable")
 327         dbl = db[1].fetchall()
 328         self.close_db(db)
 329         if dbr > 0:
 330             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 331                 ret = {}
 332                 ret['type'] = typet
 333                 ret['chtxt'] = chtxt
 334                 ret['title'] = title.encode('utf-8')
 335                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 336                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 337                 ret['btime'] = btime
 338                 ret['etime'] = etime
 339                 ret['opt'] = opt
 340                 ret['deltatime'] = ""
 341                 ret['deltaday'] = ""
 342                 if deltatime == None:
 343                     deltatime = "3"
 344                 if deltaday == None:
 345                     deltaday = "7"
 346                 if typet == 'key':
 347                     ret['deltatime'] = deltatime
 348                 elif typet == 'keyevery':
 349                     ret['deltatime'] = deltatime
 350                     ret['deltaday'] = deltaday
 351                 recdata.append(ret)
 352         self.close_db(db)
 353         return recdata
 354     def select_bytime_main_timetable(self, dminutes):
 355         db = self.connect_db()
 356         recdatum = []
 357         #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 )")
 358         dbr = db[1].execute("SELECT \
 359         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 360         FROM main_timetable \
 361         WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
 362         DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
 363         dbl = db[1].fetchall()
 364         self.close_db(db)
 365         #print dbl
 366         if dbr > 0:
 367             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 368                 ret = {}
 369                 ret['type'] = typet
 370                 ret['chtxt'] = chtxt
 371                 ret['title'] = title.encode('utf-8')
 372                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 373                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 374                 ret['btime'] = btime
 375                 ret['etime'] = etime
 376                 ret['opt'] = opt
 377                 if deltatime == None or deltatime == "":
 378                     deltatime = "3"
 379                 if deltaday == None or deltaday == "":
 380                     deltaday = "7"
 381                 if typet == 'key':
 382                     ret['deltatime'] = deltatime
 383                 elif typet == 'keyevery':
 384                     ret['deltatime'] = deltatime
 385                     ret['deltaday'] = deltaday
 386                 recdatum.append(ret)
 387         return recdatum
 388     def delete_old_main_timetable(self, dhour):
 389         db = self.connect_db()
 390         db[1].execute("\
 391         DELETE FROM main_timetable \
 392         WHERE \
 393         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 394         #db.commit()
 395         self.close_db(db)
 396     def new_in_auto_jbk_key(self):
 397         db = self.connect_db()
 398         try:
 399             db[1].execute("\
 400             CREATE TABLE in_auto_jbk_key \
 401             (\
 402             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 403             keyword VARCHAR(200),\
 404             UNIQUE unijbk (keyword)\
 405             )")
 406         except:
 407             ""
 408         self.close_db(db)
 409     def add_in_auto_jbk_key(self,key):
 410         db = self.connect_db()
 411         db[1].execute('\
 412         INSERT IGNORE into in_auto_jbk_key \
 413         (keyword) \
 414         values (%s)', \
 415                       (key,))
 416         ##db.commit()
 417         self.close_db(db)
 418     def select_all_in_auto_jbk_key(self):
 419         db = self.connect_db()
 420         dbexe = db[1].execute("\
 421         SELECT keyword \
 422         FROM in_auto_jbk_key \
 423         ")
 424         ret = []
 425         if dbexe > 0:
 426             ret = db[1].fetchall()
 427         self.close_db(db)
 428         return ret
 429     def new_in_status(self):
 430         db = self.connect_db()
 431         try:
 432             db[1].execute("\
 433             CREATE TABLE in_status \
 434             (\
 435             ts2avi TINYINT DEFAULT 0,\
 436             terec TINYINT DEFAULT 0,\
 437             bscsrec TINYINT DEFAULT 0,\
 438             b252ts TINYINT DEFAULT 0\
 439             )")
 440             db[1].execute("INSERT IGNORE into status VALUE (0,0,0,0)")
 441         except:
 442             ""
 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         self.close_db(db)
 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