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