Rec10WEB/trunk/src/rec10web_dbMySQL.py

   1 #!/usr/bin/python
   2 # coding: UTF-8
   3 # Rec10 TS Recording Tools
   4 # Copyright (C) 2009 Yukikaze
   5 import MySQLdb
   6 import os
   7 path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
   8 dbpath = path + "ch.db"
   9 class DB_MySQL:
  10     dbname=""
  11     dbhost=""
  12     dbusr=""
  13     dbpasswd=""
  14     dbport=0
  15     def __init__(self,dbname,user,passwd,host="localhost",port=3306):
  16         self.dbname = dbname
  17         self.dbhost=host
  18         self.dbusr=user
  19         self.dbpasswd=passwd
  20         self.dbport=port
  21     def connect_db(self):
  22         """
  23         dbへの接続
  24         """
  25         con=MySQLdb.connect(db=self.dbname,host=self.dbhost,port=self.dbport,user=self.dbusr,passwd=self.dbpasswd,charset="utf8")
  26         cur=con.cursor()
  27         cur.execute('set names utf8;')
  28         return [con,cur]
  29     def close_db(self,db):
  30         db[1].close()
  31         db[0].close()
  32     def select_by_ontv_ch(self,ontv):
  33         db=self.connect_db()
  34         ret=db[1].execute("SELECT bctype,channel,display FROM ch WHERE channel= %s ",(ontv,))
  35         ret=db[1].fetchall()
  36         self.close_db(db)
  37         return ret
  38     def select_all_rectime(self):
  39         db=self.connect_db()
  40         recdata=[]
  41         dl=db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt FROM rectime")
  42         dls=db[1].fetchall()
  43         for id,typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dls:
  44             ret={}
  45             ret['id']=id
  46             ret['type']=typet
  47             ret['chtxt']=chtxt
  48             ret['title']=title.encode('utf-8')
  49             btime=btime.strftime("%Y-%m-%d %H:%M:%S")
  50             etime=etime.strftime("%Y-%m-%d %H:%M:%S")
  51             ret['btime']=btime
  52             ret['etime']=etime
  53             ret['opt']=opt
  54             ret['deltatime']=""
  55             ret['deltaday']=""
  56             if deltatime == None:
  57                 deltatime="3"
  58             if deltaday == None:
  59                 deltaday="7"
  60             if typet == 'key':
  61                 ret['deltatime']=deltatime
  62             elif typet == 'keyevery':
  63                 ret['deltatime']=deltatime
  64                 ret['deltaday']=deltaday
  65             recdata.append(ret)
  66         self.close_db(db)
  67         return recdata
  68     def select_by_chtxt_rectime(self,chtxt):
  69         db=self.connect_db()
  70         recdata=[]
  71         dl=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE chtxt = %s ",(chtxt,))
  72         d=db[1].fetchall()
  73         for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in d:
  74             ret={}
  75             ret['type']=typet
  76             ret['chtxt']=chtxt
  77             ret['title']=title.encode('utf-8')
  78             btime=btime.strftime("%Y-%m-%d %H:%M:%S")
  79             etime=etime.strftime("%Y-%m-%d %H:%M:%S")
  80             ret['btime']=btime
  81             ret['etime']=etime
  82             ret['opt']=opt
  83             if deltatime == None:
  84                 deltatime="3"
  85             if deltaday == None:
  86                 deltaday="7"
  87             if typet == 'key':
  88                 ret['deltatime']=deltatime
  89             elif typet == 'keyevery':
  90                 ret['deltatime']=deltatime
  91                 ret['deltaday']=deltaday
  92             recdata.append(ret)
  93         self.close_db(db)
  94         return recdata
  95     def select_by_id_rectime(self,id):
  96         db=self.connect_db()
  97         recdata=[]
  98         dl=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE id = %s ",(id,))
  99         d=db[1].fetchall()
 100         for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in d:
 101             ret={}
 102             ret['type']=typet
 103             ret['chtxt']=chtxt
 104             ret['title']=title.encode('utf-8')
 105             btime=btime.strftime("%Y-%m-%d %H:%M:%S")
 106             etime=etime.strftime("%Y-%m-%d %H:%M:%S")
 107             ret['btime']=btime
 108             ret['etime']=etime
 109             ret['opt']=opt
 110             if deltatime == None:
 111                 deltatime="3"
 112             if deltaday == None:
 113                 deltaday="7"
 114             if typet == 'key':
 115                 ret['deltatime']=deltatime
 116             elif typet == 'keyevery':
 117                 ret['deltatime']=deltatime
 118                 ret['deltaday']=deltaday
 119             recdata.append(ret)
 120         self.close_db(db)
 121         return recdata
 122     def select_all_chdata(self):
 123         db=self.connect_db()
 124         ret=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata")
 125         ret=db[1].fetchall()
 126         self.close_db(db)
 127         return ret
 128     def select_by_chtxt_chdata(self,chtxt):
 129         db=self.connect_db()
 130         ret=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt=%s",(chtxt,))
 131         ret=db[1].fetchall()
 132         self.close_db(db)
 133         return ret
 134     def select_by_station_chdata(self,station):
 135         db=self.connect_db()
 136         ret=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station=%s",(station,))
 137         ret=db[1].fetchall()
 138         self.close_db(db)
 139         return ret
 140     def select_by_station_name_chdata(self,station_name):
 141         db=self.connect_db()
 142         db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station_name=%s",(station_name,))
 143         ret=db[1].fetchall()
 144         self.close_db(db)
 145         return ret
 146     def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 147         db = self.connect_db()
 148         db[1].execute('INSERT IGNORE INTO rectime (type,chtxt,title,btime,etime,deltatime,deltaday,opt) values (%s,%s,%s,%s,%s,%s,%s,%s)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 149         #db.commit()
 150         self.close_db(db)
 151     def count_schedule_rectime(self,btime,etime):
 152         """
 153         count rectasknum
 154         return [te num,bs/cs num]
 155         """
 156         db=self.connect_db()
 157         dbexe="SELECT type,chdata.bctype,rectime.chtxt,rectime.title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt"
 158         dbexe=dbexe+" WHERE NOT( ( rectime.etime <= %s ) OR ( rectime.btime >= %s ) )"
 159         Srec=0
 160         Trec=0
 161         db[1].execute(dbexe,(btime,etime))
 162         for typet,bctypet, chtxtt, titlet in db[1].fetchall():
 163             if (typet=="rec") or (typet=="res") or (typet=="key") or (typet=="keyevery"):
 164                 if bctypet.find("cs")>-1:
 165                     Srec=Srec+1
 166                 elif bctypet.find("bs")>-1:
 167                     Srec=Srec+1
 168                 elif bctypet.find("te")>-1:
 169                     Trec=Trec+1
 170         self.close_db(db)
 171         return [Trec,Srec]
 172     def del_by_id_rectime(self,id):
 173         db=self.connect_db()
 174         db[1].execute('DELETE FROM rectime WHERE id='+id)
 175         self.close_db(db)