rec10/trunk/src/dbSQLite.py

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