rec10/trunk/src/dbSQLite.py

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