# coding: UTF-8
# Rec10 TS Recording Tools
# Copyright (C) 2009 Yukikaze
-
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"
class DB_SQLite():
dbpath=""
def __init__(self,dbpath):
self.dbpath = dbpath
- db = self.connect_db(60)
+ db = self.connect_db(480)
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))')
+ db.execute('CREATE TABLW 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
def close_db(self,db):
db.close()
def new_chdata(self):
- db = self.connect_db(60)
+ db = self.connect_db(480)
try:
- db.execute('drop table chdata')
+ 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)')
+ 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 = self.connect_db(480)
+ db.execute('INSERT OR IGNORE 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)
+ db = self.connect_db(480)
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)
+ db = self.connect_db(480)
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)
+ db = self.connect_db(480)
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)
+ db = self.connect_db(480)
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)
+ db = self.connect_db(480)
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)
+ db = self.connect_db(480)
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 = self.connect_db(480)
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 = self.connect_db(480)
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=""):
-
- 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 = self.connect_db(480)
+ db.execute('INSERT OR IGNORE INTO rectime (type,chtxt,title,btime,etime,deltatime,deltaday,opt) values (?,?,?,datetime(?),datetime(?),?,?,?)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
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=self.connect_db(480)
+ db.execute("DELETE FROM rectime where type = ? AND title = ? AND chtxt = ? AND btime = datetime(?)", (type, title, chtxt, btime))
db.commit()
self.close_db(db)
def select_all_rectime(self):
- db=self.connect_db(60)
+ db=self.connect_db(480)
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['btime']=btime
ret['etime']=etime
ret['opt']=opt
+ ret['deltatime']=""
+ ret['deltaday']=""
if deltatime == None:
deltatime="3"
if deltaday == None:
recdata.append(ret)
self.close_db(db)
return recdata
- def select_bytime_rectime(self,dhour):
- db=self.connect_db(60)
+ def select_bytime_rectime(self,dminutes):
+ db=self.connect_db(480)
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\')"):
+ 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\',\'+" + dminutes + " minutes\') AND btime >datetime(\'now\',\'localtime\',\'-" + dminutes + " minutes\')"):
ret={}
ret['type']=typet
ret['chtxt']=chtxt
self.close_db(db)
return recdatum
def delete_old_rectime(self,dhour):
- db=self.connect_db(60)
+ db=self.connect_db(480)
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)
+ db=self.connect_db(480)
try:
- db.execute("delete from tv where bctype = ?", (bctype,))
+ 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)')
+ db.execute('CREATE TABLE tv (bctype TEXT,channel TEXT NOT NULL,start TEXT,stop TEXT,title TEXT,exp TEXT, UNIQUE (bctype,channel,start,stop,title,exp))')
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)
+ def add_tv(self,bctype, channel, start, stop, title, exp):
+ db=self.connect_db(480)
+ db.execute('INSERT OR IGNORE INTO tv (bctype,channel,start,stop,title,exp) values (?,?,?,?,?,?)', (bctype, channel, start, stop, title, exp))
+ db.commit()
+ self.close_db(db)
+ def add_multi_tv(self,tvlists):
+ """
+ tvlists is (bctype,channel,start,stop,title,exp) lists.
+ """
+ db=self.connect_db(480)
+ db.executemany('INSERT OR IGNORE INTO tv (bctype,channel,start,stop,title,exp) values (?,?,?,?,?,?)', tvlists)
db.commit()
self.close_db(db)
def select_by_title_tv(self,title):
- db=self.connect_db(60)
+ db=self.connect_db(480)
ret=[]
for ch, title, start, stop in db.execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\""):
btime=start.replace(" +0900","")
self.close_db(db)
return ret
def select_by_time_ngram_tv(self,btime,etime,chtxt):
- db=self.connect_db(60)
+ db=self.connect_db(480)
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)
+ db=self.connect_db(480)
try:
- db.execute("delete from ch where bctype = ?", (bctype,))
+ db.execute("DELETE FROM ch WHERE bctype = ?",(bctype,))
except:
""
try:
- db.execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT)')
+ db.execute('CREATE TABLE ch (bctype TEXT,channel TEXT NOT NULL,display TEXT,UNIQUE (bctype,channel))')
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=self.connect_db(480)
+ db.execute('INSERT OR IGNORE INTO ch values (?,?,?)', (bctype, channel, display))
+ db.commit()
+ self.close_db(db)
+ def add_multi_ch(self,chlists):
+ """
+ chlists is (bctype,channel,display) lists
+ """
+ db=self.connect_db(480)
+ db.executemany('INSERT OR IGNORE INTO ch values (?,?,?)', chlists)
db.commit()
self.close_db(db)