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 os
   7 import MySQLdb
   8 import dbSQLite
   9 path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
  10
  11 class DB_MySQL():
  12     dbname=""
  13     dbhost=""
  14     dbusr=""
  15     dbpasswd=""
  16     dbport=0
  17     def __init__(self,dbname,user,passwd,host="localhost",port=3306):
  18         self.dbname = dbname
  19         self.dbhost=host
  20         self.dbusr=user
  21         self.dbpasswd=passwd
  22         self.dbport=port
  23         try:
  24             con = MySQLdb.connect(user= user, passwd = passwd)
  25             cur=con.cursor()
  26             cur.execute('CREATE DATABASE '+dbname+" DEFAULT CHARACTER SET utf8")
  27             cur.close()
  28             con.close()
  29         except:
  30             ""
  31         db = self.connect_db()
  32         try:
  33             db[1].execute('CREATE TABLE rectime (id INTEGER PRIMARY KEY,type VARCHAR(10),chtxt VARCHAR(20),title VARCHAR(100),btime DATETIME,etime DATETIME,deltatime VARCHAR(5),deltaday VARCHAR(5),opt VARCHAR(20),UNIQUE uni (type,chtxt,title,btime,deltaday))')
  34         except:
  35             ""
  36         self.new_tv("")
  37         self.new_ch("")
  38         self.close_db(db)
  39     def connect_db(self):
  40         """
  41         dbへの接続
  42         """
  43         con=MySQLdb.connect(db=self.dbname,host=self.dbhost,port=self.dbport,user=self.dbusr,passwd=self.dbpasswd,charset="utf8")
  44         cur=con.cursor()
  45         cur.execute('set names utf8;')
  46         return [con,cur]
  47     def close_db(self,db):
  48         db[1].close()
  49         db[0].close()
  50     def new_chdata(self):
  51         db = self.connect_db()
  52         try:
  53             db[1].execute('drop table chdata')
  54         except:
  55             ""
  56         try:
  57             db[1].execute('create table chdata (bctype VARCHAR(15),ontv VARCHAR(30),chtxt VARCHAR(15),ch VARCHAR(10),csch VARCHAR(10),station VARCHAR(20),station_name VARCHAR(30),updatetime DATETIME)')
  58         except:
  59             ""
  60         self.close_db(db)
  61     def add_chdata(self,bctype, ontv, chtxt, ch, csch, station, station_name,updatetime):
  62         db = self.connect_db()
  63         db[1].execute('insert into chdata values (%s,%s,%s,%s,%s,%s,%s,%s)',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime))
  64         self.close_db(db)
  65     def select_by_ontv_chdata(self,ontv):
  66         db = self.connect_db()
  67         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ontv = %s",(ontv,))
  68         ret=[]
  69         if dbexe>0:
  70             ret=db[1].fetchall()
  71         self.close_db(db)
  72         return ret
  73     def select_by_chtxt_chdata(self,chtxt):
  74         db = self.connect_db()
  75         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt = %s",(chtxt,))
  76         ret=[]
  77         if dbexe>0:
  78             ret=db[1].fetchall()
  79         self.close_db(db)
  80         return ret
  81     def select_by_bctype_chdata(self,bctype):
  82         db = self.connect_db()
  83         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE bctype = %s",(bctype,))
  84         ret=[]
  85         if dbexe>0:
  86             ret=db[1].fetchall()
  87         self.close_db(db)
  88         return ret
  89     def select_by_ch_chdata(self,ch):
  90         db = self.connect_db()
  91         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = %s",(ch,))
  92         ret=[]
  93         if dbexe>0:
  94             ret=db[1].fetchall()
  95         self.close_db(db)
  96         return ret
  97     def select_all_chdata(self):
  98         db = self.connect_db()
  99         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ")
 100         ret=[]
 101         if dbexe>0:
 102             ret=db[1].fetchall()
 103         self.close_db(db)
 104         return ret
 105     def select_get_update_chdata(self,dhour):
 106         db = self.connect_db()
 107         dbexe=db[1].execute("SELECT bctype,chtxt FROM chdata WHERE updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR)")
 108         ret=[]
 109         print dbexe
 110         if dbexe>0:
 111             ret=db[1].fetchall()
 112         self.close_db(db)
 113         return ret
 114     def update_by_bctype_chdata(self,bctype):
 115         db = self.connect_db()
 116         db[1].execute("UPDATE chdata SET updatetime=now() WHERE bctype = %s",(bctype,))
 117         self.close_db(db)
 118     def update_by_bctype_and_chtxt_chdata(self,bctype,chtxt):
 119         db = self.connect_db()
 120         db[1].execute("UPDATE chdata SET updatetime=now() WHERE bctype = %s AND chtxt = %s",(bctype,chtxt))
 121         self.close_db(db)
 122     def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 123         db = self.connect_db()
 124         db[1].execute('insert into rectime (type,chtxt,title,btime,etime,deltatime,deltaday,opt) values (%s,%s,%s,%s,%s,%s,%s,%s)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 125         ##db.commit()
 126         self.close_db(db)
 127     def del_rectime(self,type="", title="", chtxt="", btime=""):
 128         """
 129
 130         """
 131         db=self.connect_db()
 132         db[1].execute("delete from rectime where type = %s AND title = %s AND chtxt = %s AND btime = %s", (type, title, chtxt, btime))
 133         #db.commit()
 134         self.close_db(db)
 135     def select_all_rectime(self):
 136         db=self.connect_db()
 137         recdata=[]
 138         dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime")
 139         if dbr>0:
 140             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db[1].fetchone():
 141                 ret={}
 142                 ret['type']=typet
 143                 ret['chtxt']=chtxt
 144                 ret['title']=title.encode('utf-8')
 145                 ret['btime']=btime
 146                 ret['etime']=etime
 147                 ret['opt']=opt
 148                 if deltatime == None:
 149                     deltatime="3"
 150                 if deltaday == None:
 151                     deltaday="7"
 152                 if typet == 'key':
 153                     ret['deltatime']=deltatime
 154                 elif typet == 'keyevery':
 155                     ret['deltatime']=deltatime
 156                     ret['deltaday']=deltaday
 157                 recdata.append(ret)
 158         self.close_db(db)
 159         return recdata
 160     def select_bytime_rectime(self,dminutes):
 161         db=self.connect_db()
 162         recdatum=[]
 163         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 )")
 164         if dbr > 0:
 165             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db[1].fetchone():
 166                 ret={}
 167                 ret['type']=typet
 168                 ret['chtxt']=chtxt
 169                 ret['title']=title.encode('utf-8')
 170                 ret['btime']=btime
 171                 ret['etime']=etime
 172                 ret['opt']=opt
 173                 if deltatime == None or deltatime == "":
 174                     deltatime="3"
 175                 if deltaday == None or deltaday == "":
 176                     deltaday="7"
 177                 if typet == 'key':
 178                     ret['deltatime']=deltatime
 179                 elif typet == 'keyevery':
 180                     ret['deltatime']=deltatime
 181                     ret['deltaday']=deltaday
 182                 recdatum.append(ret)
 183         self.close_db(db)
 184         return recdatum
 185     def delete_old_rectime(self,dhour):
 186         db=self.connect_db()
 187         db[1].execute("DELETE FROM rectime WHERE NOT ( type = %s OR type = %s ) AND btime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR )", (recdb.REC_MISS_ENCODE, recdb.REC_KEYWORD_EVERY_SOME_DAYS))
 188         #db.commit()
 189         self.close_db(db)
 190     def new_tv(self,bctype):
 191         db=self.connect_db()
 192         try:
 193             db[1].execute("delete from tv where bctype = %s", (bctype,))
 194         except:
 195             ""
 196         try:
 197             db[1].execute("create table tv (bctype VARCHAR(20),channel VARCHAR(20) NOT NULL,start VARCHAR(20),stop  VARCHAR(20),title VARCHAR(100),exp VARCHAR(200),UNIQUE uni(bctype,channel,start,stop,title))")
 198         except:
 199             ""
 200         #db.commit()
 201         self.close_db(db)
 202     def add_tv(self,bctype, channel, start, stop, title, desc):
 203         db=self.connect_db()
 204         db[1].execute('INSERT IGNORE INTO tv VALUES (%s,%s,%s,%s,%s,%s)', (bctype, channel, start, stop, title, desc))
 205         #db.commit()
 206         self.close_db(db)
 207     def add_multi_tv(self,tvlists):
 208         """
 209         tvlists is (bctype,channel,start,stop,title,desc) lists.
 210         """
 211         db=self.connect_db()
 212         db[1].executemany('INSERT IGNORE INTO tv (bctype,channel,start,stop,title,exp) values(%s,%s,%s,%s,%s,%s)', tvlists)
 213         #db.commit()
 214         self.close_db(db)
 215     def select_by_title_tv(self,title):
 216         db=self.connect_db()
 217         ret=[]
 218         dbr=db[1].execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\"")
 219         if dbr>0:
 220             for ch, title, start, stop in db[1].fetchone():
 221                 btime=start.replace(" +0900","")
 222                 btime=btime[0:4]+"-"+btime[4:6]+"-"+btime[6:8]+" "+btime[8:10]+":"+btime[10:12]+":00"
 223                 etime=stop.replace(" +0900","")
 224                 etime=etime[0:4]+"-"+etime[4:6]+"-"+etime[6:8]+" "+etime[8:10]+":"+etime[10:12]+":00"
 225                 try:
 226                     chtxt=chdb.ontvsearch(ch)['chtxt']
 227                 except:
 228                     chtxt="Unknown"
 229                 ret.append(chtxt+","+title.encode('utf-8')+","+btime+","+etime)
 230         self.close_db(db)
 231         return ret
 232     def select_by_time_ngram_tv(self,btime,etime,chtxt):
 233         db=self.connect_db()
 234         dbexe="SELECT chdata.chtxt,title,start,stop FROM tv INNER JOIN chdata WHERE chdata.ontv=tv.channel AND start >= %s AND start <= %s AND chdata.chtxt=%s"
 235         dbcmd=db[1].execute(dbexe,(btime,etime,chtxt))
 236         retall=[]
 237         if dbcmd>0:
 238             retall=db[1].fetchall()
 239         self.close_db(db)
 240         return retall
 241     def new_ch(self,bctype):
 242         db=self.connect_db()
 243         try:
 244             db[1].execute("delete from ch where bctype = %s", (bctype,))
 245         except:
 246             ""
 247         try:
 248             db[1].execute('create table ch (bctype VARCHAR(20),channel VARCHAR(20) NOT NULL,display VARCHAR(100),UNIQUE uni(bctype,channel))')
 249         except:
 250             ""
 251         #db.commit()
 252         self.close_db(db)
 253     def add_ch(self,bctype, channel, display):
 254         db=self.connect_db()
 255         db[1].execute('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', (bctype, channel, display))
 256         #db.commit()
 257         self.close_db(db)
 258     def add_multi_ch(self,chlists):
 259         """
 260         chlists is (bctype,channel,display) lists
 261         """
 262         db=self.connect_db()
 263         db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
 264         self.close_db(db)