+#!/usr/bin/python
+# coding: UTF-8
+# Rec10 TS Recording Tools
+# Copyright (C) 2009 Yukikaze
+import sqlite3
+import os
+path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
+dbpath = path + "ch.db"
+class DB_SQLite():
+ dbpath=""
+ def __init__(self,dbpath):
+ self.dbpath = dbpath
+ 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 select_by_ontv_ch(self,ontv):
+ db=self.connect_db(120)
+ ret=db.execute("SELECT bctype,channel,display FROM ch WHERE channel= ? ",(ontv,))
+ ret=ret.fetchall()
+ self.close_db(db)
+ return ret
+ def select_all_rectime(self):
+ db=self.connect_db(120)
+ recdata=[]
+ for id,typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt FROM rectime"):
+ ret={}
+ ret['id']=id
+ ret['type']=typet
+ ret['chtxt']=chtxt
+ ret['title']=title.encode('utf-8')
+ ret['btime']=btime
+ ret['etime']=etime
+ ret['opt']=opt
+ ret['deltatime']=""
+ ret['deltaday']=""
+ 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_by_chtxt_rectime(self,chtxt):
+ db=self.connect_db(120)
+ recdata=[]
+ for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE chtxt = ? ",(chtxt,)):
+ 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_by_id_rectime(self,id):
+ db=self.connect_db(120)
+ recdata=[]
+ for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE id = ? ",(id,)):
+ 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_all_chdata(self):
+ db=self.connect_db(120)
+ ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata")
+ ret=ret.fetchall()
+ self.close_db(db)
+ return ret
+ def select_by_chtxt_chdata(self,chtxt):
+ db=self.connect_db(120)
+ ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt=?",(chtxt,))
+ ret=ret.fetchall()
+ self.close_db(db)
+ return ret
+ def select_by_station_chdata(self,station):
+ db=self.connect_db(120)
+ ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station=?",(station,))
+ ret=ret.fetchall()
+ self.close_db(db)
+ return ret
+ def select_by_station_name_chdata(self,station_name):
+ db=self.connect_db(120)
+ ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station_name=?",(station_name,))
+ ret=ret.fetchall()
+ self.close_db(db)
+ return ret
+ def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
+ 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 count_schedule_rectime(self,btime,etime):
+ """
+ count rectasknum
+ return [te num,bs/cs num]
+ """
+ db=self.connect_db(120)
+ dbexe="SELECT type,chdata.bctype,rectime.chtxt,rectime.title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt"
+ dbexe=dbexe+" WHERE NOT( ( rectime.etime <= ? ) OR ( rectime.btime >= ? ) )"
+ Srec=0
+ Trec=0
+ for typet,bctypet, chtxtt, titlet in db.execute(dbexe,(btime,etime)):
+ if (typet=="rec") or (typet=="res") or (typet=="key") or (typet=="keyevery"):
+ if bctypet.find("cs")>-1:
+ Srec=Srec+1
+ elif bctypet.find("bs")>-1:
+ Srec=Srec+1
+ elif bctypet.find("te")>-1:
+ Trec=Trec+1
+ self.close_db(db)
+ return [Trec,Srec]
+ def del_by_id_rectime(self,id):
+ db=self.connect_db(120)
+ db.execute('DELETE FROM rectime WHERE id='+id)
+ self.close_db(db)
\ No newline at end of file
OSDN Git Service
