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