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
   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 rectime \
  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_tv("")
  47         self.new_ch("")
  48         self.new_reclog()
  49     def connect_db(self):
  50         """
  51         dbへの接続
  52         """
  53         con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
  54         cur = con.cursor()
  55         cur.execute('set names utf8;')
  56         return [con, cur]
  57     def close_db(self, db):
  58         db[1].close()
  59         db[0].close()
  60     def new_chdata(self):
  61         db = self.connect_db()
  62         try:
  63             db[1].execute('drop table chdata')
  64         except:
  65             ""
  66         try:
  67             db[1].execute('\
  68             CREATE TABLE chdata \
  69             (\
  70             bctype VARCHAR(15),\
  71             ontv VARCHAR(30) PRIMARY KEY,\
  72             chtxt VARCHAR(15),\
  73             ch VARCHAR(20),\
  74             csch VARCHAR(20),\
  75             updatetime DATETIME,\
  76             status TINYINT\
  77             )')
  78         except:
  79             ""
  80         self.close_db(db)
  81     def add_chdata(self, bctype, ontv, chtxt, ch, csch, updatetime):
  82         db = self.connect_db()
  83         db[1].execute('\
  84         INSERT INTO chdata \
  85         VALUES (%s,%s,%s,%s,%s,%s,%s)', \
  86                       (bctype, ontv, chtxt, ch, csch, updatetime, "1"))
  87         self.close_db(db)
  88     def select_by_ontv_chdata(self, ontv):
  89         db = self.connect_db()
  90         dbexe = db[1].execute("\
  91         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
  92         FROM chdata \
  93         WHERE ontv = %s", \
  94                               (ontv,))
  95         ret = []
  96         dls = []
  97         if dbexe > 0:
  98             dls = db[1].fetchall()
  99         self.close_db(db)
 100         for dl in dls:
 101             r = list(dl)
 102             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 103             ret.append(r)
 104         return ret
 105     def select_by_chtxt_chdata(self, chtxt):
 106         db = self.connect_db()
 107         dbexe = db[1].execute("\
 108         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 109         FROM chdata \
 110         WHERE chtxt = %s", \
 111                               (chtxt,))
 112         ret = []
 113         dls = []
 114         if dbexe > 0:
 115             dls = db[1].fetchall()
 116         self.close_db(db)
 117         for dl in dls:
 118             r = list(dl)
 119             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 120             ret.append(r)
 121         return ret
 122     def select_by_bctype_chdata(self, bctype):
 123         db = self.connect_db()
 124         dbexe = db[1].execute("\
 125         SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
 126         FROM chdata \
 127         WHERE bctype = %s", \
 128                               (bctype,))
 129         ret = []
 130         dls = []
 131         if dbexe > 0:
 132             dls = db[1].fetchall()
 133         self.close_db(db)
 134         for dl in dls:
 135             #print dl
 136             r = list(dl)
 137             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 138             r[6] = str(r[6])
 139             ret.append(r)
 140         return ret
 141     def select_by_ch_chdata(self, ch):
 142         db = self.connect_db()
 143         dbexe = db[1].execute("\
 144         SELECT \
 145         bctype,ontv,chtxt,ch,csch,updatetime \
 146         FROM chdata \
 147         WHERE ch = %s", \
 148                               (ch,))
 149         ret = []
 150         dls = []
 151         if dbexe > 0:
 152             dls = db[1].fetchall()
 153         self.close_db(db)
 154         for dl in dls:
 155             r = list(dl)
 156             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 157             ret.append(r)
 158         return ret
 159     def select_all_chdata(self):
 160         db = self.connect_db()
 161         dbexe = db[1].execute("\
 162         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 163         FROM chdata \
 164         ")
 165         ret = []
 166         dls = []
 167         if dbexe > 0:
 168             dls = db[1].fetchall()
 169         self.close_db(db)
 170         for dl in dls:
 171             r = list(dl)
 172             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 173             ret.append(r)
 174         return ret
 175     def select_get_update_chdata(self, dhour):
 176         db = self.connect_db()
 177         dbexe = db[1].execute("SELECT bctype,chtxt,status FROM chdata \
 178         WHERE \
 179         ( \
 180         updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
 181         AND \
 182         status = 1 \
 183         ) \
 184         OR \
 185         status > 1 \
 186         ORDER BY status DESC")
 187         ret = []
 188         #print dbexe
 189         if dbexe > 0:
 190             ret = db[1].fetchall()
 191         self.close_db(db)
 192         return ret
 193     def update_by_bctype_chdata(self, bctype):
 194         db = self.connect_db()
 195         db[1].execute("\
 196         UPDATE chdata \
 197         SET \
 198         updatetime=now() , \
 199         status = 1 \
 200         WHERE bctype = %s", (bctype,))
 201         self.close_db(db)
 202     def update_by_bctype_and_chtxt_chdata(self, bctype, chtxt):
 203         db = self.connect_db()
 204         db[1].execute("\
 205         UPDATE chdata \
 206         SET \
 207         updatetime=now() , \
 208         status = 1\
 209         WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
 210         self.close_db(db)
 211     def update_status_by_bctype_chdata(self, bctype, status):
 212         db = self.connect_db()
 213         db[1].execute("\
 214         UPDATE chdata \
 215         SET \
 216         status=%s \
 217         WHERE bctype = %s", \
 218                       (status, bctype)\
 219                       )
 220         self.close_db(db)
 221     def add_reclog(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
 222         db = self.connect_db()
 223         db[1].execute('\
 224         INSERT IGNORE into reclog \
 225         (chtxt,title,btime,etime,opt,exp,longexp,category) \
 226         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 227                       ( chtxt, title, btime, etime, opt,exp,longexp,category))
 228         ##db.commit()
 229         self.close_db(db)
 230     def del_reclog(self, title="", chtxt="", btime=""):
 231         """
 232
 233         """
 234         db = self.connect_db()
 235         db[1].execute("\
 236         DELETE FROM reclog \
 237         WHERE title = %s AND chtxt = %s AND btime = %s", \
 238                       (title, chtxt, btime))
 239         #db.commit()
 240         self.close_db(db)
 241     def new_reclog(self):
 242         db = self.connect_db()
 243         try:
 244             db[1].execute('\
 245             CREATE TABLE reclog \
 246             (\
 247             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 248             chtxt VARCHAR(20),\
 249             title VARCHAR(100),\
 250             btime DATETIME,\
 251             etime DATETIME,\
 252             opt VARCHAR(20),\
 253             exp VARCHAR(200),\
 254             longexp TEXT,\
 255             category VARCHAR(100),\
 256             UNIQUE uni (chtxt,title,btime,category)\
 257             )')
 258         except:
 259             ""
 260         self.close_db(db)
 261     def add_rectime(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 262         db = self.connect_db()
 263         db[1].execute('\
 264         INSERT IGNORE into rectime \
 265         (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
 266         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 267                       (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 268         ##db.commit()
 269         self.close_db(db)
 270     def del_rectime(self, type="", title="", chtxt="", btime=""):
 271         """
 272
 273         """
 274         db = self.connect_db()
 275         db[1].execute("\
 276         DELETE FROM rectime \
 277         WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
 278                       (type, title, chtxt, btime))
 279         #db.commit()
 280         self.close_db(db)
 281     def select_all_rectime(self):
 282         db = self.connect_db()
 283         recdata = []
 284         dbr = db[1].execute("\
 285         SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 286         FROM rectime")
 287         dbl = db[1].fetchall()
 288         self.close_db(db)
 289         if dbr > 0:
 290             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 291                 ret = {}
 292                 ret['type'] = typet
 293                 ret['chtxt'] = chtxt
 294                 ret['title'] = title.encode('utf-8')
 295                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 296                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 297                 ret['btime'] = btime
 298                 ret['etime'] = etime
 299                 ret['opt'] = opt
 300                 ret['deltatime'] = ""
 301                 ret['deltaday'] = ""
 302                 if deltatime == None:
 303                     deltatime = "3"
 304                 if deltaday == None:
 305                     deltaday = "7"
 306                 if typet == 'key':
 307                     ret['deltatime'] = deltatime
 308                 elif typet == 'keyevery':
 309                     ret['deltatime'] = deltatime
 310                     ret['deltaday'] = deltaday
 311                 recdata.append(ret)
 312         self.close_db(db)
 313         return recdata
 314     def select_bytime_rectime(self, dminutes):
 315         db = self.connect_db()
 316         recdatum = []
 317         #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 )")
 318         dbr = db[1].execute("SELECT \
 319         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 320         FROM rectime \
 321         WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
 322         DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
 323         dbl = db[1].fetchall()
 324         self.close_db(db)
 325         #print dbl
 326         if dbr > 0:
 327             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 328                 ret = {}
 329                 ret['type'] = typet
 330                 ret['chtxt'] = chtxt
 331                 ret['title'] = title.encode('utf-8')
 332                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 333                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 334                 ret['btime'] = btime
 335                 ret['etime'] = etime
 336                 ret['opt'] = opt
 337                 if deltatime == None or deltatime == "":
 338                     deltatime = "3"
 339                 if deltaday == None or deltaday == "":
 340                     deltaday = "7"
 341                 if typet == 'key':
 342                     ret['deltatime'] = deltatime
 343                 elif typet == 'keyevery':
 344                     ret['deltatime'] = deltatime
 345                     ret['deltaday'] = deltaday
 346                 recdatum.append(ret)
 347         return recdatum
 348     def delete_old_rectime(self, dhour):
 349         db = self.connect_db()
 350         db[1].execute("\
 351         DELETE FROM rectime \
 352         WHERE \
 353         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 354         #db.commit()
 355         self.close_db(db)
 356     def new_status(self):
 357         db = self.connect_db()
 358         try:
 359             db[1].execute("\
 360             CREATE TABLE status \
 361             (\
 362             ts2avi TINYINT DEFAULT 0,\
 363             terec TINYINT DEFAULT 0,\
 364             bscsrec TINYINT DEFAULT 0,\
 365             b252ts TINYINT DEFAULT 0\
 366             )")
 367             db[1].execute("INSERT IGNORE into status VALUE (0,0,0,0)")
 368         except:
 369             ""
 370
 371         self.close_db(db)
 372     def select_all_status(self):
 373         db = self.connect_db()
 374         dbexe = db[1].execute("\
 375         SELECT ts2avi,terec,bscsrec,b252ts \
 376         FROM status \
 377         ")
 378         ret = []
 379         dls = []
 380         if dbexe > 0:
 381             dls = db[1].fetchall()
 382         self.close_db(db)
 383         for dl in dls:
 384             r = list(dl)
 385             r[0]=str(r[0])
 386             r[1]=str(r[1])
 387             r[2]=str(r[2])
 388             r[3]=str(r[3])
 389             ret.append(r)
 390         return ret
 391         self.close_db(db)
 392     def change_ts2avi_status(self,i):
 393         """
 394         statuをiだけ増減する
 395         iはint
 396         """
 397         db = self.connect_db()
 398         db[1].execute("\
 399         UPDATE status SET ts2avi=ts2avi+%s",i)
 400         self.close_db(db)
 401     def change_terec_status(self,i):
 402         """
 403         statuをiだけ増減する
 404         iはint
 405         """
 406         db = self.connect_db()
 407         db[1].execute("\
 408         UPDATE status SET terec=terec+%s",i)
 409         self.close_db(db)
 410     def change_bscsrec_status(self,i):
 411         """
 412         statuをiだけ増減する
 413         iはint
 414         """
 415         db = self.connect_db()
 416         db[1].execute("\
 417         UPDATE status SET bscsrec=bscsrec+%s",i)
 418         self.close_db(db)
 419     def change_b252ts_status(self,i):
 420         """
 421         statuをiだけ増減する
 422         iはint
 423         """
 424         db = self.connect_db()
 425         db[1].execute("\
 426         UPDATE status SET b252ts=b252ts+%s",i)
 427         self.close_db(db)
 428     def new_tv(self, bctype):
 429         db = self.connect_db()
 430         try:
 431             db[1].execute("\
 432             DELETE FROM tv \
 433             WHERE bctype = %s", \
 434                           (bctype,))
 435         except:
 436             ""
 437         try:
 438             db[1].execute("\
 439             CREATE TABLE tv \
 440             (\
 441             bctype VARCHAR(20),\
 442             channel VARCHAR(100) NOT NULL,\
 443             start VARCHAR(30),\
 444             stop  VARCHAR(30),\
 445             title VARCHAR(100),\
 446             exp VARCHAR(200),\
 447             longexp TEXT,\
 448             category VARCHAR(100),\
 449             UNIQUE unitv(bctype,channel,start,stop,title)\
 450             )")
 451         except:
 452             ""
 453         #db.commit()
 454         self.close_db(db)
 455     def add_tv(self, bctype, channel, start, stop, title, desc,longdesc, category):
 456         db = self.connect_db()
 457         db[1].execute('\
 458         INSERT IGNORE INTO tv \
 459         VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
 460                       (bctype, channel, start, stop, title, desc,longdesc,category))
 461         #db.commit()
 462         self.close_db(db)
 463     def add_multi_tv(self, tvlists):
 464         """
 465         tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
 466         """
 467         db = self.connect_db()
 468         db[1].executemany('\
 469         INSERT IGNORE INTO tv \
 470         (bctype,channel,start,stop,title,exp,longexp,category) \
 471         values(%s,%s,%s,%s,%s,%s,%s,%s)', \
 472                           tvlists)
 473         #db.commit()
 474         self.close_db(db)
 475     def select_by_time_ngram_tv(self, btime, etime, chtxt):
 476         db = self.connect_db()
 477         dbexe = "\
 478         SELECT \
 479         chdata.chtxt,title,start,stop,exp,longexp,category \
 480         FROM tv \
 481         INNER JOIN chdata \
 482         WHERE chdata.ontv=tv.channel \
 483         AND \
 484         start >= %s \
 485         AND \
 486         start <= %s \
 487         AND \
 488         chdata.chtxt=%s"
 489         dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
 490         retall = []
 491         if dbcmd > 0:
 492             retall = db[1].fetchall()
 493         self.close_db(db)
 494         return retall
 495     def new_ch(self, bctype):
 496         db = self.connect_db()
 497         try:
 498             db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,))
 499         except:
 500             ""
 501         try:
 502             db[1].execute('\
 503             CREATE TABLE ch \
 504             (\
 505             bctype VARCHAR(20),\
 506             channel VARCHAR(20) NOT NULL,\
 507             display VARCHAR(100),\
 508             UNIQUE unich(bctype,channel)\
 509             )')
 510         except:
 511             ""
 512         #db.commit()
 513         self.close_db(db)
 514     def add_ch(self, bctype, channel, display):
 515         db = self.connect_db()
 516         db[1].execute('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', (bctype, channel, display))
 517         #db.commit()
 518         self.close_db(db)
 519     def add_multi_ch(self, chlists):
 520         """
 521         chlists is (bctype,channel,display) lists
 522         """
 523         db = self.connect_db()
 524         db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
 525         self.close_db(db)
 526     def new_bayeskey(self):
 527         db = self.connect_db()
 528         try:
 529             db[1].execute('\
 530             CREATE TABLE bayeskey \
 531             (\
 532             key VARCHAR(10),\
 533             chtxt VARCHAR(20),\
 534             ratio_rec DECIMAL(24,8),\
 535             ratio_all DECIMAL(24,8),\
 536             )')
 537         except:
 538             ""
 539         self.close_db(db)
 540     def add_bayeskey(self,key,chtxt,ratio_rec,ratio_all):
 541         db = self.connect_db()
 542         db[1].executemany('\
 543         INSERT IGNORE INTO bayeskey \
 544         (key,chtxt,ratio_rec,ratio_all) \
 545         values(%s,%s,%s,%s)',\
 546                           (key,chtxt,ratio_rec,ratio_all))
 547         self.close_db(db)
 548     def change_bayeskey(self,key,chtxt,new_ratio_rec,new_ratio_all):
 549         """
 550         statuをiだけ増減する
 551         iはint
 552         """
 553         db = self.connect_db()
 554         db[1].execute("\
 555         UPDATE bayeskey SET ratio_rec=%s,ratio_all=%s WHERE key=%s AND chtxt=%s",(new_ratio_rec,new_ratio_all,key,chtxt)\
 556         )
 557         self.close_db(db)
 558     def change_ratio_all_bayeskey(self,chtxt,beforenum,newnum):
 559         db = self.connect_db()
 560         db[1].execute("\
 561         UPDATE bayeskey SET ratio_all=ratio_all*%s/(%s+%s) WHERE chtxt=%s",(beforenum,newnum,beforenum,chtxt)\
 562         )
 563         self.close_db(db)
 564     def change_ratio_rec_bayeskey(self,key,chtxt,beforenum,addnum):
 565         db = self.connect_db()
 566         db[1].execute("\
 567         UPDATE bayeskey SET ratio_rec=(ratio_rec*%s+%s)/%s WHERE chtxt=%s AND key=%s",\
 568         (beforenum,addnum,beforenum,chtxt,key)\
 569         )
 570         self.close_db(db)
 571     def select_by_key_bayeskey(self,chtxt,key):
 572         db = self.connect_db()
 573         dbexe = db[1].execute("\
 574         SELECT key,chtxt,ratio_rec,ratio_all \
 575         FROM bayeskey \
 576         WHERE key = %s AND chtxt = %s", \
 577                               (key,chtxt))
 578         dls = []
 579         if dbexe > 0:
 580             dls = db[1].fetchall()
 581         self.close_db(db)
 582         if len(dls)>0:
 583             return dls[0]
 584         else:
 585             return dls