rec10/trunk/src/dbMySQL.py

   1 #!/usr/bin/python
   2 # coding: UTF-8
   3 # Rec10 TS Recording Tools
   4 # Copyright (C) 2009 Yukikaze
   5 import recdb
   6 import os
   7 import MySQLdb
   8 import dbSQLite
   9
  10 class DB_MySQL:
  11     dbname=""
  12     dbhost=""
  13     dbusr=""
  14     dbpasswd=""
  15     dbport=0
  16     def __init__(self,dbname,user,passwd,host="localhost",port=3306):
  17         self.dbname = dbname
  18         self.dbhost=host
  19         self.dbusr=user
  20         self.dbpasswd=passwd
  21         self.dbport=port
  22         try:
  23             con = MySQLdb.connect(user= user, passwd = passwd)
  24             cur=con.cursor()
  25             cur.execute('CREATE DATABASE '+dbname+" DEFAULT CHARACTER SET utf8")
  26             cur.close()
  27             con.close()
  28         except:
  29             ""
  30         db = self.connect_db()
  31         try:
  32             db[1].execute('CREATE TABLE rectime (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,type VARCHAR(20),chtxt VARCHAR(20),title VARCHAR(100),btime DATETIME,etime DATETIME,deltatime VARCHAR(5),deltaday VARCHAR(5),opt VARCHAR(20),UNIQUE uni (type,chtxt,title,btime,deltaday))')
  33         except:
  34             ""
  35         self.new_tv("")
  36         self.new_ch("")
  37         self.close_db(db)
  38     def connect_db(self):
  39         """
  40         dbへの接続
  41         """
  42         con=MySQLdb.connect(db=self.dbname,host=self.dbhost,port=self.dbport,user=self.dbusr,passwd=self.dbpasswd,charset="utf8")
  43         cur=con.cursor()
  44         cur.execute('set names utf8;')
  45         return [con,cur]
  46     def close_db(self,db):
  47         db[1].close()
  48         db[0].close()
  49     def new_chdata(self):
  50         db = self.connect_db()
  51         try:
  52             db[1].execute('drop table chdata')
  53         except:
  54             ""
  55         try:
  56             db[1].execute('create table chdata (bctype VARCHAR(15),ontv VARCHAR(30) PRIMARY KEY,chtxt VARCHAR(15),ch VARCHAR(20),csch VARCHAR(20),station VARCHAR(20),station_name VARCHAR(30),updatetime DATETIME,status TINYINT)')
  57         except:
  58             ""
  59         self.close_db(db)
  60     def add_chdata(self,bctype, ontv, chtxt, ch, csch, station, station_name,updatetime):
  61         db = self.connect_db()
  62         db[1].execute('insert into chdata values (%s,%s,%s,%s,%s,%s,%s,%s,%s)',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime,"1"))
  63         self.close_db(db)
  64     def select_by_ontv_chdata(self,ontv):
  65         db = self.connect_db()
  66         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ontv = %s",(ontv,))
  67         ret=[]
  68         if dbexe>0:
  69             ret=db[1].fetchall()
  70         self.close_db(db)
  71         return ret
  72     def select_by_chtxt_chdata(self,chtxt):
  73         db = self.connect_db()
  74         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt = %s",(chtxt,))
  75         ret=[]
  76         if dbexe>0:
  77             ret=db[1].fetchall()
  78         self.close_db(db)
  79         return ret
  80     def select_by_bctype_chdata(self,bctype):
  81         db = self.connect_db()
  82         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE bctype = %s",(bctype,))
  83         ret=[]
  84         if dbexe>0:
  85             ret=db[1].fetchall()
  86         self.close_db(db)
  87         return ret
  88     def select_by_ch_chdata(self,ch):
  89         db = self.connect_db()
  90         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = %s",(ch,))
  91         ret=[]
  92         if dbexe>0:
  93             ret=db[1].fetchall()
  94         self.close_db(db)
  95         return ret
  96     def select_all_chdata(self):
  97         db = self.connect_db()
  98         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ")
  99         ret=[]
 100         if dbexe>0:
 101             ret=db[1].fetchall()
 102         self.close_db(db)
 103         return ret
 104     def select_get_update_chdata(self,dhour):
 105         db = self.connect_db()
 106         dbexe=db[1].execute("SELECT bctype,chtxt,status FROM chdata WHERE updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR) AND status > 0 ORDER BY status DESC")
 107         ret=[]
 108         print dbexe
 109         if dbexe>0:
 110             ret=db[1].fetchall()
 111         self.close_db(db)
 112         return ret
 113     def update_by_bctype_chdata(self,bctype):
 114         db = self.connect_db()
 115         db[1].execute("UPDATE chdata SET updatetime=now() , status = 1 WHERE bctype = %s",(bctype,))
 116         self.close_db(db)
 117     def update_by_bctype_and_chtxt_chdata(self,bctype,chtxt):
 118         db = self.connect_db()
 119         db[1].execute("UPDATE chdata SET updatetime=now() , status = 1WHERE bctype = %s AND chtxt = %s",(bctype,chtxt))
 120         self.close_db(db)
 121     def update_status_by_bctype_chdata(self,bctype,status):
 122         db = self.connect_db()
 123         db[1].execute("UPDATE chdata SET status=%s WHERE bctype = %s",(status,bctype))
 124         self.close_db(db)
 125     def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 126         db = self.connect_db()
 127         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))
 128         ##db.commit()
 129         self.close_db(db)
 130     def del_rectime(self,type="", title="", chtxt="", btime=""):
 131         """
 132
 133         """
 134         db=self.connect_db()
 135         db[1].execute("delete from rectime where type = %s AND title = %s AND chtxt = %s AND btime = %s", (type, title, chtxt, btime))
 136         #db.commit()
 137         self.close_db(db)
 138     def select_all_rectime(self):
 139         db=self.connect_db()
 140         recdata=[]
 141         dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime")
 142         dbl=db[1].fetchall()
 143         self.close_db(db)
 144         if dbr>0:
 145             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 146                 ret={}
 147                 ret['type']=typet
 148                 ret['chtxt']=chtxt
 149                 ret['title']=title.encode('utf-8')
 150                 btime=btime.strftime("%Y-%m-%d %H:%M:%S")
 151                 etime=etime.strftime("%Y-%m-%d %H:%M:%S")
 152                 ret['btime']=btime
 153                 ret['etime']=etime
 154                 ret['opt']=opt
 155                 ret['deltatime']=""
 156                 ret['deltaday']=""
 157                 if deltatime == None:
 158                     deltatime="3"
 159                 if deltaday == None:
 160                     deltaday="7"
 161                 if typet == 'key':
 162                     ret['deltatime']=deltatime
 163                 elif typet == 'keyevery':
 164                     ret['deltatime']=deltatime
 165                     ret['deltaday']=deltaday
 166                 recdata.append(ret)
 167         self.close_db(db)
 168         return recdata
 169     def select_bytime_rectime(self,dminutes):
 170         db=self.connect_db()
 171         recdatum=[]
 172         #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
 173         dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime BETWEEN DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
 174         dbl=db[1].fetchall()
 175         self.close_db(db)
 176         #print dbl
 177         if dbr > 0:
 178             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 179                 ret={}
 180                 ret['type']=typet
 181                 ret['chtxt']=chtxt
 182                 ret['title']=title.encode('utf-8')
 183                 btime=btime.strftime("%Y-%m-%d %H:%M:%S")
 184                 etime=etime.strftime("%Y-%m-%d %H:%M:%S")
 185                 ret['btime']=btime
 186                 ret['etime']=etime
 187                 ret['opt']=opt
 188                 if deltatime == None or deltatime == "":
 189                     deltatime="3"
 190                 if deltaday == None or deltaday == "":
 191                     deltaday="7"
 192                 if typet == 'key':
 193                     ret['deltatime']=deltatime
 194                 elif typet == 'keyevery':
 195                     ret['deltatime']=deltatime
 196                     ret['deltaday']=deltaday
 197                 recdatum.append(ret)
 198         return recdatum
 199     def delete_old_rectime(self,dhour):
 200         db=self.connect_db()
 201         db[1].execute("DELETE FROM rectime WHERE NOT ( type = %s OR type = %s ) AND btime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR )", (recdb.REC_MISS_ENCODE, recdb.REC_KEYWORD_EVERY_SOME_DAYS))
 202         #db.commit()
 203         self.close_db(db)
 204     def new_tv(self,bctype):
 205         db=self.connect_db()
 206         try:
 207             db[1].execute("delete from tv where bctype = %s", (bctype,))
 208         except:
 209             ""
 210         try:
 211             db[1].execute("create table tv (bctype VARCHAR(20),channel VARCHAR(100) NOT NULL,start VARCHAR(30),stop  VARCHAR(30),title VARCHAR(100),exp VARCHAR(200),UNIQUE unitv(bctype,channel,start,stop,title))")
 212         except:
 213             ""
 214         #db.commit()
 215         self.close_db(db)
 216     def add_tv(self,bctype, channel, start, stop, title, desc):
 217         db=self.connect_db()
 218         db[1].execute('INSERT IGNORE INTO tv VALUES (%s,%s,%s,%s,%s,%s)', (bctype, channel, start, stop, title, desc))
 219         #db.commit()
 220         self.close_db(db)
 221     def add_multi_tv(self,tvlists):
 222         """
 223         tvlists is (bctype,channel,start,stop,title,desc) lists.
 224         """
 225         db=self.connect_db()
 226         db[1].executemany('INSERT IGNORE INTO tv (bctype,channel,start,stop,title,exp) values(%s,%s,%s,%s,%s,%s)', tvlists)
 227         #db.commit()
 228         self.close_db(db)
 229
 230
 231     def select_by_time_ngram_tv(self,btime,etime,chtxt):
 232         db=self.connect_db()
 233         dbexe="SELECT chdata.chtxt,title,start,stop FROM tv INNER JOIN chdata WHERE chdata.ontv=tv.channel AND start >= %s AND start <= %s AND chdata.chtxt=%s"
 234         dbcmd=db[1].execute(dbexe,(btime,etime,chtxt))
 235         retall=[]
 236         if dbcmd>0:
 237             retall=db[1].fetchall()
 238         self.close_db(db)
 239         return retall
 240     def new_ch(self,bctype):
 241         db=self.connect_db()
 242         try:
 243             db[1].execute("delete from ch where bctype = %s", (bctype,))
 244         except:
 245             ""
 246         try:
 247             db[1].execute('create table ch (bctype VARCHAR(20),channel VARCHAR(20) NOT NULL,display VARCHAR(100),UNIQUE unich(bctype,channel))')
 248         except:
 249             ""
 250         #db.commit()
 251         self.close_db(db)
 252     def add_ch(self,bctype, channel, display):
 253         db=self.connect_db()
 254         db[1].execute('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', (bctype, channel, display))
 255         #db.commit()
 256         self.close_db(db)
 257     def add_multi_ch(self,chlists):
 258         """
 259         chlists is (bctype,channel,display) lists
 260         """
 261         db=self.connect_db()
 262         db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
 263         self.close_db(db)
 264     def import_from_SQLite(self,dbpath):
 265         dbb=dbSQLite.DB_SQLite(dbpath)
 266         dbl=dbb.select_all_rectime()
 267         for db in dbl:
 268             self.add_rectime(db['type'],db['chtxt'],db['title'],db['btime'],db['etime'],db['deltatime'],db['deltaday'],db['opt'])
 269
 270 """    def select_by_title_tv(self,title):
 271         db=self.connect_db()
 272         ret=[]
 273         dbr=db[1].execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\"")
 274         dbl=db[1].fetchall()
 275         self.close_db(db)
 276         if dbr>0:
 277             for ch, title, start, stop in dbl:
 278                 btime=start.replace(" +0900","")
 279                 btime=btime[0:4]+"-"+btime[4:6]+"-"+btime[6:8]+" "+btime[8:10]+":"+btime[10:12]+":00"
 280                 etime=stop.replace(" +0900","")
 281                 etime=etime[0:4]+"-"+etime[4:6]+"-"+etime[6:8]+" "+etime[8:10]+":"+etime[10:12]+":00"
 282                 try:
 283                     chtxt=chdb.ontvsearch(ch)['chtxt']
 284                 except:
 285                     chtxt="Unknown"
 286                 ret.append(chtxt+","+title.encode('utf-8')+","+btime+","+etime)
 287         return ret"""