Rec10WEB/trunk/src/rec10web_dbSQLite.py

   1 #!/usr/bin/python
   2 # coding: UTF-8
   3 # Rec10 TS Recording Tools
   4 # Copyright (C) 2009 Yukikaze
   5 import sqlite
   6 #import sqlite3
   7 import os
   8 path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
   9 dbpath = path + "ch.db"
  10 class DB_SQLite:
  11     dbpath=""
  12     def __init__(self,dbpath):
  13         self.dbpath = dbpath
  14     def connect_db(self,tout=10):
  15         global dbpath
  16         """
  17         dbへの接続(timeoutは秒)
  18
  19         """
  20         tout=tout*1000
  21         return sqlite3.connect(self.dbpath, timeout=tout)
  22     def close_db(self,db):
  23         db.close()
  24     def select_by_ontv_ch(self,ontv):
  25         db=self.connect_db(120)
  26         ret=db.execute("SELECT bctype,channel,display FROM ch WHERE channel= ? ",(ontv,))
  27         ret=ret.fetchall()
  28         self.close_db(db)
  29         return ret
  30     def select_all_rectime(self):
  31         db=self.connect_db(120)
  32         recdata=[]
  33         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"):
  34             ret={}
  35             ret['id']=id
  36             ret['type']=typet
  37             ret['chtxt']=chtxt
  38             ret['title']=title.encode('utf-8')
  39             ret['btime']=btime
  40             ret['etime']=etime
  41             ret['opt']=opt
  42             ret['deltatime']=""
  43             ret['deltaday']=""
  44             if deltatime == None:
  45                 deltatime="3"
  46             if deltaday == None:
  47                 deltaday="7"
  48             if typet == 'key':
  49                 ret['deltatime']=deltatime
  50             elif typet == 'keyevery':
  51                 ret['deltatime']=deltatime
  52                 ret['deltaday']=deltaday
  53             recdata.append(ret)
  54         self.close_db(db)
  55         return recdata
  56     def select_by_chtxt_rectime(self,chtxt):
  57         db=self.connect_db(120)
  58         recdata=[]
  59         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,)):
  60             ret={}
  61             ret['type']=typet
  62             ret['chtxt']=chtxt
  63             ret['title']=title.encode('utf-8')
  64             ret['btime']=btime
  65             ret['etime']=etime
  66             ret['opt']=opt
  67             if deltatime == None:
  68                 deltatime="3"
  69             if deltaday == None:
  70                 deltaday="7"
  71             if typet == 'key':
  72                 ret['deltatime']=deltatime
  73             elif typet == 'keyevery':
  74                 ret['deltatime']=deltatime
  75                 ret['deltaday']=deltaday
  76             recdata.append(ret)
  77         self.close_db(db)
  78         return recdata
  79     def select_by_id_rectime(self,id):
  80         db=self.connect_db(120)
  81         recdata=[]
  82         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,)):
  83             ret={}
  84             ret['type']=typet
  85             ret['chtxt']=chtxt
  86             ret['title']=title.encode('utf-8')
  87             ret['btime']=btime
  88             ret['etime']=etime
  89             ret['opt']=opt
  90             if deltatime == None:
  91                 deltatime="3"
  92             if deltaday == None:
  93                 deltaday="7"
  94             if typet == 'key':
  95                 ret['deltatime']=deltatime
  96             elif typet == 'keyevery':
  97                 ret['deltatime']=deltatime
  98                 ret['deltaday']=deltaday
  99             recdata.append(ret)
 100         self.close_db(db)
 101         return recdata
 102     def select_all_chdata(self):
 103         db=self.connect_db(120)
 104         ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata")
 105         ret=ret.fetchall()
 106         self.close_db(db)
 107         return ret
 108     def select_by_chtxt_chdata(self,chtxt):
 109         db=self.connect_db(120)
 110         ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt=?",(chtxt,))
 111         ret=ret.fetchall()
 112         self.close_db(db)
 113         return ret
 114     def select_by_station_chdata(self,station):
 115         db=self.connect_db(120)
 116         ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station=?",(station,))
 117         ret=ret.fetchall()
 118         self.close_db(db)
 119         return ret
 120     def select_by_station_name_chdata(self,station_name):
 121         db=self.connect_db(120)
 122         ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station_name=?",(station_name,))
 123         ret=ret.fetchall()
 124         self.close_db(db)
 125         return ret
 126     def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 127         db = self.connect_db(480)
 128         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))
 129         db.commit()
 130         self.close_db(db)
 131     def count_schedule_rectime(self,btime,etime):
 132         """
 133         count rectasknum
 134         return [te num,bs/cs num]
 135         """
 136         db=self.connect_db(120)
 137         dbexe="SELECT type,chdata.bctype,rectime.chtxt,rectime.title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt"
 138         dbexe=dbexe+" WHERE NOT( ( rectime.etime <= ? ) OR ( rectime.btime >= ? ) )"
 139         Srec=0
 140         Trec=0
 141         for typet,bctypet, chtxtt, titlet in db.execute(dbexe,(btime,etime)):
 142             if (typet=="rec") or (typet=="res") or (typet=="key") or (typet=="keyevery"):
 143                 if bctypet.find("cs")>-1:
 144                     Srec=Srec+1
 145                 elif bctypet.find("bs")>-1:
 146                     Srec=Srec+1
 147                 elif bctypet.find("te")>-1:
 148                     Trec=Trec+1
 149         self.close_db(db)
 150         return [Trec,Srec]
 151     def del_by_id_rectime(self,id):
 152         db=self.connect_db(120)
 153         db.execute('DELETE FROM rectime WHERE id='+id)
 154         self.close_db(db)