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 recdb
   6 import MySQLdb
   7 import dbSQLite
   8
   9 class DB_MySQL:
  10     dbname=""
  11     dbhost=""
  12     dbusr=""
  13     dbpasswd=""
  14     dbport=0
  15     def __init__(self,dbname,user,passwd,host="localhost",port=3306):
  16         self.dbname = dbname
  17         self.dbhost=host
  18         self.dbusr=user
  19         self.dbpasswd=passwd
  20         self.dbport=port
  21         try:
  22             con = MySQLdb.connect(user= user, passwd = passwd)
  23             cur=con.cursor()
  24             cur.execute('CREATE DATABASE '+dbname+" DEFAULT CHARACTER SET utf8")
  25             cur.close()
  26             con.close()
  27         except:
  28             ""
  29         db = self.connect_db()
  30         try:
  31             db[1].execute('\
  32             CREATE TABLE rectime \
  33             (\
  34             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
  35             type VARCHAR(20),\
  36             chtxt VARCHAR(20),\
  37             title VARCHAR(100),\
  38             btime DATETIME,\
  39             etime DATETIME,\
  40             deltatime VARCHAR(5),\
  41             deltaday VARCHAR(5),\
  42             opt VARCHAR(20),\
  43             UNIQUE uni (type,chtxt,title,btime,deltaday)\
  44             )')
  45         except:
  46             ""
  47         self.new_tv("")
  48         self.new_ch("")
  49         self.close_db(db)
  50     def connect_db(self):
  51         """
  52         dbへの接続
  53         """
  54         con=MySQLdb.connect(db=self.dbname,host=self.dbhost,port=self.dbport,user=self.dbusr,passwd=self.dbpasswd,charset="utf8")
  55         cur=con.cursor()
  56         cur.execute('set names utf8;')
  57         return [con,cur]
  58     def close_db(self,db):
  59         db[1].close()
  60         db[0].close()
  61     def new_chdata(self):
  62         db = self.connect_db()
  63         try:
  64             db[1].execute('drop table chdata')
  65         except:
  66             ""
  67         try:
  68             db[1].execute('\
  69             CREATE TABLE chdata \
  70             (\
  71             bctype VARCHAR(15),\
  72             ontv VARCHAR(30) PRIMARY KEY,\
  73             chtxt VARCHAR(15),\
  74             ch VARCHAR(20),\
  75             csch VARCHAR(20),\
  76             updatetime DATETIME,\
  77             status TINYINT\
  78             )')
  79         except:
  80             ""
  81         self.close_db(db)
  82     def add_chdata(self,bctype, ontv, chtxt, ch, csch,updatetime):
  83         db = self.connect_db()
  84         db[1].execute('\
  85         INSERT INTO chdata \
  86         VALUES (%s,%s,%s,%s,%s,%s,%s)',\
  87         (bctype,ontv,chtxt,ch,csch,updatetime,"1"))
  88         self.close_db(db)
  89     def select_by_ontv_chdata(self,ontv):
  90         db = self.connect_db()
  91         dbexe=db[1].execute("\
  92         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
  93         FROM chdata \
  94         WHERE ontv = %s",\
  95         (ontv,))
  96         ret=[]
  97         dls=[]
  98         if dbexe>0:
  99             dls=db[1].fetchall()
 100         self.close_db(db)
 101         for dl in dls:
 102             r=list(dl)
 103             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
 104             ret.append(r)
 105         return ret
 106     def select_by_chtxt_chdata(self,chtxt):
 107         db = self.connect_db()
 108         dbexe=db[1].execute("\
 109         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 110         FROM chdata \
 111         WHERE chtxt = %s",\
 112         (chtxt,))
 113         ret=[]
 114         dls=[]
 115         if dbexe>0:
 116             dls=db[1].fetchall()
 117         self.close_db(db)
 118         for dl in dls:
 119             r=list(dl)
 120             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
 121             ret.append(r)
 122         return ret
 123     def select_by_bctype_chdata(self,bctype):
 124         db = self.connect_db()
 125         dbexe=db[1].execute("\
 126         SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
 127         FROM chdata \
 128         WHERE bctype = %s",\
 129         (bctype,))
 130         ret=[]
 131         dls=[]
 132         if dbexe>0:
 133             dls=db[1].fetchall()
 134         self.close_db(db)
 135         for dl in dls:
 136             #print dl
 137             r=list(dl)
 138             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
 139             r[6]=str(r[6])
 140             ret.append(r)
 141         return ret
 142     def select_by_ch_chdata(self,ch):
 143         db = self.connect_db()
 144         dbexe=db[1].execute("\
 145         SELECT \
 146         bctype,ontv,chtxt,ch,csch,updatetime \
 147         FROM chdata \
 148         WHERE ch = %s",\
 149         (ch,))
 150         ret=[]
 151         dls=[]
 152         if dbexe>0:
 153             dls=db[1].fetchall()
 154         self.close_db(db)
 155         for dl in dls:
 156             r=list(dl)
 157             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
 158             ret.append(r)
 159         return ret
 160     def select_all_chdata(self):
 161         db = self.connect_db()
 162         dbexe=db[1].execute("\
 163         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 164         FROM chdata \
 165         ")
 166         ret=[]
 167         dls=[]
 168         if dbexe>0:
 169             dls=db[1].fetchall()
 170         self.close_db(db)
 171         for dl in dls:
 172             r=list(dl)
 173             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
 174             ret.append(r)
 175         return ret
 176     def select_get_update_chdata(self,dhour):
 177         db = self.connect_db()
 178         dbexe=db[1].execute("SELECT bctype,chtxt,status FROM chdata \
 179         WHERE \
 180         ( \
 181         updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR) \
 182         AND \
 183         status = 1 \
 184         ) \
 185         OR \
 186         status > 1 \
 187         ORDER BY status DESC")
 188         ret=[]
 189         #print dbexe
 190         if dbexe>0:
 191             ret=db[1].fetchall()
 192         self.close_db(db)
 193         return ret
 194     def update_by_bctype_chdata(self,bctype):
 195         db = self.connect_db()
 196         db[1].execute("\
 197         UPDATE chdata \
 198         SET \
 199         updatetime=now() , \
 200         status = 1 \
 201         WHERE bctype = %s",(bctype,))
 202         self.close_db(db)
 203     def update_by_bctype_and_chtxt_chdata(self,bctype,chtxt):
 204         db = self.connect_db()
 205         db[1].execute("\
 206         UPDATE chdata \
 207         SET \
 208         updatetime=now() , \
 209         status = 1\
 210         WHERE bctype = %s AND chtxt = %s",(bctype,chtxt))
 211         self.close_db(db)
 212     def update_status_by_bctype_chdata(self,bctype,status):
 213         db = self.connect_db()
 214         db[1].execute("\
 215         UPDATE chdata \
 216         SET \
 217         status=%s \
 218         WHERE bctype = %s",\
 219         (status,bctype)\
 220         )
 221         self.close_db(db)
 222     def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 223         db = self.connect_db()
 224         db[1].execute('\
 225         INSERT IGNORE into rectime \
 226         (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
 227         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 228         (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 229         ##db.commit()
 230         self.close_db(db)
 231     def del_rectime(self,type="", title="", chtxt="", btime=""):
 232         """
 233
 234         """
 235         db=self.connect_db()
 236         db[1].execute("\
 237         DELETE FROM rectime \
 238         WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
 239         (type, title, chtxt, btime))
 240         #db.commit()
 241         self.close_db(db)
 242     def select_all_rectime(self):
 243         db=self.connect_db()
 244         recdata=[]
 245         dbr=db[1].execute("\
 246         SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 247         FROM rectime")
 248         dbl=db[1].fetchall()
 249         self.close_db(db)
 250         if dbr>0:
 251             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 252                 ret={}
 253                 ret['type']=typet
 254                 ret['chtxt']=chtxt
 255                 ret['title']=title.encode('utf-8')
 256                 btime=btime.strftime("%Y-%m-%d %H:%M:%S")
 257                 etime=etime.strftime("%Y-%m-%d %H:%M:%S")
 258                 ret['btime']=btime
 259                 ret['etime']=etime
 260                 ret['opt']=opt
 261                 ret['deltatime']=""
 262                 ret['deltaday']=""
 263                 if deltatime == None:
 264                     deltatime="3"
 265                 if deltaday == None:
 266                     deltaday="7"
 267                 if typet == 'key':
 268                     ret['deltatime']=deltatime
 269                 elif typet == 'keyevery':
 270                     ret['deltatime']=deltatime
 271                     ret['deltaday']=deltaday
 272                 recdata.append(ret)
 273         self.close_db(db)
 274         return recdata
 275     def select_bytime_rectime(self,dminutes):
 276         db=self.connect_db()
 277         recdatum=[]
 278         #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 )")
 279         dbr=db[1].execute("SELECT \
 280         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 281         FROM rectime \
 282         WHERE btime BETWEEN DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND \
 283         DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
 284         dbl=db[1].fetchall()
 285         self.close_db(db)
 286         #print dbl
 287         if dbr > 0:
 288             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 289                 ret={}
 290                 ret['type']=typet
 291                 ret['chtxt']=chtxt
 292                 ret['title']=title.encode('utf-8')
 293                 btime=btime.strftime("%Y-%m-%d %H:%M:%S")
 294                 etime=etime.strftime("%Y-%m-%d %H:%M:%S")
 295                 ret['btime']=btime
 296                 ret['etime']=etime
 297                 ret['opt']=opt
 298                 if deltatime == None or deltatime == "":
 299                     deltatime="3"
 300                 if deltaday == None or deltaday == "":
 301                     deltaday="7"
 302                 if typet == 'key':
 303                     ret['deltatime']=deltatime
 304                 elif typet == 'keyevery':
 305                     ret['deltatime']=deltatime
 306                     ret['deltaday']=deltaday
 307                 recdatum.append(ret)
 308         return recdatum
 309     def delete_old_rectime(self,dhour):
 310         db=self.connect_db()
 311         db[1].execute("\
 312         DELETE FROM rectime \
 313         WHERE \
 314         NOT ( type = %s OR type = %s ) \
 315         AND \
 316         btime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR )", \
 317         (recdb.REC_MISS_ENCODE, recdb.REC_KEYWORD_EVERY_SOME_DAYS))
 318         #db.commit()
 319         self.close_db(db)
 320     def new_tv(self,bctype):
 321         db=self.connect_db()
 322         try:
 323             db[1].execute("\
 324             DELETE FROM tv \
 325             WHERE bctype = %s", \
 326             (bctype,))
 327         except:
 328             ""
 329         try:
 330             db[1].execute("\
 331             CREATE TABLE tv \
 332             (\
 333             bctype VARCHAR(20),\
 334             channel VARCHAR(100) NOT NULL,\
 335             start VARCHAR(30),\
 336             stop  VARCHAR(30),\
 337             title VARCHAR(100),\
 338             exp VARCHAR(200),\
 339             UNIQUE unitv(bctype,channel,start,stop,title)\
 340             )")
 341         except:
 342             ""
 343         #db.commit()
 344         self.close_db(db)
 345     def add_tv(self,bctype, channel, start, stop, title, desc):
 346         db=self.connect_db()
 347         db[1].execute('\
 348         INSERT IGNORE INTO tv \
 349         VALUES (%s,%s,%s,%s,%s,%s)', \
 350         (bctype, channel, start, stop, title, desc))
 351         #db.commit()
 352         self.close_db(db)
 353     def add_multi_tv(self,tvlists):
 354         """
 355         tvlists is (bctype,channel,start,stop,title,desc) lists.
 356         """
 357         db=self.connect_db()
 358         db[1].executemany('\
 359         INSERT IGNORE INTO tv \
 360         (bctype,channel,start,stop,title,exp) \
 361         values(%s,%s,%s,%s,%s,%s)', \
 362         tvlists)
 363         #db.commit()
 364         self.close_db(db)
 365
 366
 367     def select_by_time_ngram_tv(self,btime,etime,chtxt):
 368         db=self.connect_db()
 369         dbexe="\
 370         SELECT \
 371         chdata.chtxt,title,start,stop \
 372         FROM tv \
 373         INNER JOIN chdata \
 374         WHERE chdata.ontv=tv.channel \
 375         AND \
 376         start >= %s \
 377         AND \
 378         start <= %s \
 379         AND \
 380         chdata.chtxt=%s"
 381         dbcmd=db[1].execute(dbexe,(btime,etime,chtxt))
 382         retall=[]
 383         if dbcmd>0:
 384             retall=db[1].fetchall()
 385         self.close_db(db)
 386         return retall
 387     def new_ch(self,bctype):
 388         db=self.connect_db()
 389         try:
 390             db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,))
 391         except:
 392             ""
 393         try:
 394             db[1].execute('\
 395             CREATE TABLE ch \
 396             (\
 397             bctype VARCHAR(20),\
 398             channel VARCHAR(20) NOT NULL,\
 399             display VARCHAR(100),\
 400             UNIQUE unich(bctype,channel)\
 401             )')
 402         except:
 403             ""
 404         #db.commit()
 405         self.close_db(db)
 406     def add_ch(self,bctype, channel, display):
 407         db=self.connect_db()
 408         db[1].execute('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', (bctype, channel, display))
 409         #db.commit()
 410         self.close_db(db)
 411     def add_multi_ch(self,chlists):
 412         """
 413         chlists is (bctype,channel,display) lists
 414         """
 415         db=self.connect_db()
 416         db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
 417         self.close_db(db)
 418     def import_from_SQLite(self,dbpath):
 419         dbb=dbSQLite.DB_SQLite(dbpath)
 420         dbl=dbb.select_all_rectime()
 421         for db in dbl:
 422             self.add_rectime(db['type'],db['chtxt'],db['title'],db['btime'],db['etime'],db['deltatime'],db['deltaday'],db['opt'])
 423