# coding: UTF-8
# Rec10 TS Recording Tools
# Copyright (C) 2009 Yukikaze
-#import sys
-#import os
+
import sqlite3
import recdb
+import os
+import time
#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))')
-path=str(os.path.dirname(os.path.abspath(__file__)))+"/"
-dbpath=path+"ch.db"
-def __inif__(self,value):
- dbpath=value
- db=sqlite3.connect(dbpath)
- try:
- 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))')
- except:
- ""
-def connect_db(tout=10):
- global dbpath
- """
- dbへの接続(timeoutは秒)
+path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
+dbpath = path + "ch.db"
+class DB_SQLite():
+ dbpath=""
+ def __init__(self,dbpath):
+ self.dbpath = dbpath
+ db = self.connect_db(60)
+ try:
+ 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))')
+ except:
+ ""
+ db.commit
+ self.close_db(db)
+ def connect_db(self,tout=10):
+ global dbpath
+ """
+ dbへの接続(timeoutは秒)
+
+ """
+ tout=tout*1000
+ return sqlite3.connect(self.dbpath, timeout=tout)
+ def close_db(self,db):
+ db.close()
+ def new_chdata(self):
+ db = self.connect_db(60)
+ try:
+ db.execute('drop table chdata')
+ db.commit()
+ except:
+ ""
+ try:
+ db.execute('create table chdata (bctype TEXT,ontv TEXT,chtxt TEXT,ch TEXT,csch TEXT,station TEXT,station_name TEXT,updatetime TEXT)')
+ except:
+ ""
+ db.commit()
+ self.close_db(db)
+ def add_chdata(self,bctype, ontv, chtxt, ch, csch, station, station_name,updatetime):
+ db = self.connect_db(60)
+ db.execute('insert into chdata values (?,?,?,?,?,?,?,datetime(?))',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime))
+ db.commit()
+ self.close_db(db)
+ def select_by_ontv_chdata(self,ontv):
+ db = self.connect_db(60)
+ dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ontv = ?",(ontv,))
+ ret=dbexe.fetchall()
+ self.close_db(db)
+ return ret
+ def select_by_chtxt_chdata(self,chtxt):
+ db = self.connect_db(60)
+ dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt = ?",(chtxt,))
+ ret=dbexe.fetchall()
+ self.close_db(db)
+ return ret
+ def select_by_bctype_chdata(self,bctype):
+ db = self.connect_db(60)
+ dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE bctype = ?",(bctype,))
+ ret=dbexe.fetchall()
+ self.close_db(db)
+ return ret
+ def select_by_ch_chdata(self,ch):
+ db = self.connect_db(60)
+ dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = ?",(ch,))
+ ret=dbexe.fetchall()
+ self.close_db(db)
+ return ret
+ def select_all_chdata(self):
+ db = self.connect_db(60)
+ dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ")
+ ret=dbexe.fetchall()
+ self.close_db(db)
+ return ret
+ def select_get_update_chdata(self,dhour):
+ db = self.connect_db(60)
+ dbexe=db.execute("SELECT bctype,chtxt FROM chdata WHERE updatetime < datetime(\'now\',\'localtime\',\'-"+dhour+" hours\')")
+ ret=dbexe.fetchall()
+ self.close_db(db)
+ return ret
+ def update_by_bctype_chdata(self,bctype):
+ db = self.connect_db(60)
+ db.execute("UPDATE chdata SET updatetime=datetime(\'now\',\'localtime\') WHERE bctype = ?",(bctype,))
+ db.commit()
+ self.close_db(db)
+ def update_by_bctype_and_chtxt_chdata(self,bctype,chtxt):
+ db = self.connect_db(60)
+ db.execute("UPDATE chdata SET updatetime=datetime(\'now\',\'localtime\') WHERE bctype = ? AND chtxt = ?",(bctype,chtxt))
+ db.commit()
+ self.close_db(db)
+ def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
- """
- return sqlite3.connect(dbpath,timeout=tout*1000)
-def close_db(db):
- db.close()
-def add_rectime(type="",chtxt="",title="",btime="",etime="",deltatime="",deltaday="",opt=""):
- db=connect_db(60)
- t=0
- while t<10
+ db = self.connect_db(60)
+ t = 0
+ #while t < 10:
+ # try:
+ db.execute('insert into rectime (type,chtxt,title,btime,etime,deltatime,deltaday,opt) values (?,?,?,datetime(?),datetime(?),?,?,?)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
+ # break
+ # except sqlite3.OperationalError:
+ # t=t + 1
+ # time.sleep(1)
+ db.commit()
+ self.close_db(db)
+ def del_rectime(self,type="", title="", chtxt="", btime=""):
+ """
+
+ """
+ db=self.connect_db(60)
+ t=0
+ while t < 10 :
+ try:
+ db.execute("delete from rectime where type = ? AND title = ? AND chtxt = ? AND btime = datetime(?)", (type, title, chtxt, btime))
+ break
+ except sqlite3.OperationalError:
+ t=t + 1
+ time.sleep(1)
+ db.commit()
+ self.close_db(db)
+ def select_all_rectime(self):
+ db=self.connect_db(60)
+ recdata=[]
+ for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime"):
+ ret={}
+ ret['type']=typet
+ ret['chtxt']=chtxt
+ ret['title']=title.encode('utf-8')
+ ret['btime']=btime
+ ret['etime']=etime
+ ret['opt']=opt
+ if deltatime == None:
+ deltatime="3"
+ if deltaday == None:
+ deltaday="7"
+ if typet == 'key':
+ ret['deltatime']=deltatime
+ elif typet == 'keyevery':
+ ret['deltatime']=deltatime
+ ret['deltaday']=deltaday
+ recdata.append(ret)
+ self.close_db(db)
+ return recdata
+ def select_bytime_rectime(self,dhour):
+ db=self.connect_db(60)
+ recdatum=[]
+ 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\')"):
+ ret={}
+ ret['type']=typet
+ ret['chtxt']=chtxt
+ ret['title']=title.encode('utf-8')
+ ret['btime']=btime
+ ret['etime']=etime
+ ret['opt']=opt
+ if deltatime == None or deltatime == "":
+ deltatime="3"
+ if deltaday == None or deltaday == "":
+ deltaday="7"
+ if typet == 'key':
+ ret['deltatime']=deltatime
+ elif typet == 'keyevery':
+ ret['deltatime']=deltatime
+ ret['deltaday']=deltaday
+ recdatum.append(ret)
+ self.close_db(db)
+ return recdatum
+ def delete_old_rectime(self,dhour):
+ db=self.connect_db(60)
+ 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))
+ db.commit()
+ self.close_db(db)
+ def new_tv(self,bctype):
+ db=self.connect_db(60)
try:
- db.execute('insert into rectime values (?,?,?,datetime(?),datetime(?),?,?,?)',(type,chtxt,title,btime,etime,deltatime,deltaday,opt)
- break
- except sqlite3.OperationalError:
- t=t+1
- time.sleep(1)
- db.commit()
- close_db(db)
-def del_rectime(type="",title="",chtxt="",btime=""):
- db=connect_db(60)
- t=0
- while t<10
+ db.execute("delete from tv where bctype = ?", (bctype,))
+ except:
+ ""
try:
- db.execute("delete from rectime where type = ? AND title = ? AND chtxt = ? AND btime = datetime(?)",(type,title,chtxt,btime))
- break
- except sqlite3.OperationalError:
- t=t+1
- time.sleep(1)
- db.commit()
- close_db(db)
-def select_all_rectime():
- db=connect_db(60)
- recdata=[]
- for typet, chtxt, title, btime, etime, deltatime ,deltaday ,opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime"):
- ret={}
- ret['type']=typet
- ret['chtxt']=chtxt
- ret['title']=title.encode('utf-8')
- ret['btime']=btime
- ret['etime']=etime
- ret['opt']=opt
- if deltatime==None:
- deltatime="3"
- if deltaday==None:
- deltaday="7"
- if typet=='key':
- ret['deltatime']=deltatime
- elif typet=='keyevery':
- ret['deltatime']=deltatime
- ret['deltaday']=deltaday
- recdata.append(ret)
- close_db(db)
- return recdata
-def select_bytime_rectime(dhour):
- db=connect_db(60)
- recdatum=[]
- 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\')"):
- ret={}
- ret['type']=typet
- ret['chtxt']=chtxt
- ret['title']=title.encode('utf-8')
- ret['btime']=btime
- ret['etime']=etime
- ret['opt']=opt
- if deltatime==None or deltatime=="":
- deltatime="3"
- if deltaday==None or deltaday=="":
- deltaday="7"
- if typet=='key':
- ret['deltatime']=deltatime
- elif typet=='keyevery':
- ret['deltatime']=deltatime
- ret['deltaday']=deltaday
- recdatum.append(ret)
- close_db(db)
- return recdatum
-def delete_old_rectime(dhour):
- db=connect_db(60)
- 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))
- db.commit()
- close_db(db)
-def new_tv(bctype):
- db=connect_db(60)
- try:
- db.execute("delete from tv where bctype = ?",bctype)
- except:
- ""
- try:
- db.execute('create table tv (bctype TEXT,channel TEXT NOT NULL,start TEXT,stop TEXT,title TEXT,desc TEXT)')
- except:
- ""
- db.commit()
- close_db(db)
-def add_tv(bctype,channel,start,stop,title,desc):
- db=connect_db(60)
- t=0
- while t<10
+ db.execute('create table tv (bctype TEXT,channel TEXT NOT NULL,start TEXT,stop TEXT,title TEXT,desc TEXT)')
+ except:
+ ""
+ db.commit()
+ self.close_db(db)
+ def add_tv(self,bctype, channel, start, stop, title, desc):
+ db=self.connect_db(60)
+ t=0
+ while t < 10 :
+ try:
+ db.execute('insert into tv values (?,?,?,?,?,?)', (bctype, channel, start, stop, title, desc))
+ break
+ except sqlite3.OperationalError:
+ t=t + 1
+ time.sleep(1)
+ db.commit()
+ self.close_db(db)
+ def select_by_title_tv(self,title):
+ db=self.connect_db(60)
+ ret=[]
+ for ch, title, start, stop in db.execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\""):
+ btime=start.replace(" +0900","")
+ btime=btime[0:4]+"-"+btime[4:6]+"-"+btime[6:8]+" "+btime[8:10]+":"+btime[10:12]+":00"
+ etime=stop.replace(" +0900","")
+ etime=etime[0:4]+"-"+etime[4:6]+"-"+etime[6:8]+" "+etime[8:10]+":"+etime[10:12]+":00"
+ try:
+ chtxt=chdb.ontvsearch(ch)['chtxt']
+ except:
+ chtxt="Unknown"
+ ret.append(chtxt+","+title.encode('utf-8')+","+btime+","+etime)
+ self.close_db(db)
+ return ret
+ def select_by_time_ngram_tv(self,btime,etime,chtxt):
+ db=self.connect_db(60)
+ dbexe="SELECT chdata.chtxt,title,start,stop FROM tv INNER JOIN chdata WHERE chdata.ontv=tv.channel AND start >= ? AND start <= ? AND chdata.chtxt=?"
+ dbcmd=db.execute(dbexe,(btime,etime,chtxt))
+ retall=dbcmd.fetchall()
+ self.close_db(db)
+ return retall
+ def new_ch(self,bctype):
+ db=self.connect_db(60)
try:
- db.execute('insert into tv values (?,?,?,?,?,?)',(bctype,channel,start,stop,title,desc))
- break
- except sqlite3.OperationalError:
- t=t+1
- time.sleep(1)
- db.commit()
- close_db(db)
-def new_ch(bctype):
- db=connect_db(60)
- try:
- db.execute("delete from ch where bctype = ?",bctype)
- except:
- ""
- try:
- db.execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT)')
- except:
- ""
- db.commit()
- close_db(db)
-def add_ch(bctype,channel,display):
- db=connect_db(60)
- t=0
- while t<10
+ db.execute("delete from ch where bctype = ?", (bctype,))
+ except:
+ ""
try:
- db.execute('insert into tv values (?,?,?)',(bctype,channel,display))
- break
- except sqlite3.OperationalError:
- t=t+1
- time.sleep(1)
- db.commit()
- close_db(db)
\ No newline at end of file
+ db.execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT)')
+ except:
+ ""
+ db.commit()
+ self.close_db(db)
+ def add_ch(self,bctype, channel, display):
+ db=self.connect_db(60)
+ t=0
+ while t < 10:
+ try:
+ db.execute('insert into ch values (?,?,?)', (bctype, channel, display))
+ break
+ except sqlite3.OperationalError:
+ t=t+1
+ time.sleep(1)
+ db.commit()
+ self.close_db(db)
OSDN Git Service
