rec10/trunk/src/dbMySQL.py

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