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)')
  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)',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime))
  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 FROM chdata WHERE updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR)")
 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() 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() WHERE bctype = %s AND chtxt = %s",(bctype,chtxt))
 120         self.close_db(db)
 121     def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 122         db = self.connect_db()
 123         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))
 124         ##db.commit()
 125         self.close_db(db)
 126     def del_rectime(self,type="", title="", chtxt="", btime=""):
 127         """
 128
 129         """
 130         db=self.connect_db()
 131         db[1].execute("delete from rectime where type = %s AND title = %s AND chtxt = %s AND btime = %s", (type, title, chtxt, btime))
 132         #db.commit()
 133         self.close_db(db)
 134     def select_all_rectime(self):
 135         db=self.connect_db()
 136         recdata=[]
 137         dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime")
 138         dbl=db[1].fetchall()
 139         self.close_db(db)
 140         if dbr>0:
 141             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 142                 ret={}
 143                 ret['type']=typet
 144                 ret['chtxt']=chtxt
 145                 ret['title']=title.encode('utf-8')
 146                 btime=btime.strftime("%Y-%m-%d %H:%M:%S")
 147                 etime=etime.strftime("%Y-%m-%d %H:%M:%S")
 148                 ret['btime']=btime
 149                 ret['etime']=etime
 150                 ret['opt']=opt
 151                 ret['deltatime']=""
 152                 ret['deltaday']=""
 153                 if deltatime == None:
 154                     deltatime="3"
 155                 if deltaday == None:
 156                     deltaday="7"
 157                 if typet == 'key':
 158                     ret['deltatime']=deltatime
 159                 elif typet == 'keyevery':
 160                     ret['deltatime']=deltatime
 161                     ret['deltaday']=deltaday
 162                 recdata.append(ret)
 163         self.close_db(db)
 164         return recdata
 165     def select_bytime_rectime(self,dminutes):
 166         db=self.connect_db()
 167         recdatum=[]
 168         #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 )")
 169         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 )")
 170         dbl=db[1].fetchall()
 171         self.close_db(db)
 172         #print dbl
 173         if dbr > 0:
 174             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 175                 ret={}
 176                 ret['type']=typet
 177                 ret['chtxt']=chtxt
 178                 ret['title']=title.encode('utf-8')
 179                 btime=btime.strftime("%Y-%m-%d %H:%M:%S")
 180                 etime=etime.strftime("%Y-%m-%d %H:%M:%S")
 181                 ret['btime']=btime
 182                 ret['etime']=etime
 183                 ret['opt']=opt
 184                 if deltatime == None or deltatime == "":
 185                     deltatime="3"
 186                 if deltaday == None or deltaday == "":
 187                     deltaday="7"
 188                 if typet == 'key':
 189                     ret['deltatime']=deltatime
 190                 elif typet == 'keyevery':
 191                     ret['deltatime']=deltatime
 192                     ret['deltaday']=deltaday
 193                 recdatum.append(ret)
 194         return recdatum
 195     def delete_old_rectime(self,dhour):
 196         db=self.connect_db()
 197         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))
 198         #db.commit()
 199         self.close_db(db)
 200     def new_tv(self,bctype):
 201         db=self.connect_db()
 202         try:
 203             db[1].execute("delete from tv where bctype = %s", (bctype,))
 204         except:
 205             ""
 206         try:
 207             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))")
 208         except:
 209             ""
 210         #db.commit()
 211         self.close_db(db)
 212     def add_tv(self,bctype, channel, start, stop, title, desc):
 213         db=self.connect_db()
 214         db[1].execute('INSERT IGNORE INTO tv VALUES (%s,%s,%s,%s,%s,%s)', (bctype, channel, start, stop, title, desc))
 215         #db.commit()
 216         self.close_db(db)
 217     def add_multi_tv(self,tvlists):
 218         """
 219         tvlists is (bctype,channel,start,stop,title,desc) lists.
 220         """
 221         db=self.connect_db()
 222         db[1].executemany('INSERT IGNORE INTO tv (bctype,channel,start,stop,title,exp) values(%s,%s,%s,%s,%s,%s)', tvlists)
 223         #db.commit()
 224         self.close_db(db)
 225     def select_by_title_tv(self,title):
 226         db=self.connect_db()
 227         ret=[]
 228         dbr=db[1].execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\"")
 229         dbl=db[1].fetchall()
 230         self.close_db(db)
 231         if dbr>0:
 232             for ch, title, start, stop in dbl:
 233                 btime=start.replace(" +0900","")
 234                 btime=btime[0:4]+"-"+btime[4:6]+"-"+btime[6:8]+" "+btime[8:10]+":"+btime[10:12]+":00"
 235                 etime=stop.replace(" +0900","")
 236                 etime=etime[0:4]+"-"+etime[4:6]+"-"+etime[6:8]+" "+etime[8:10]+":"+etime[10:12]+":00"
 237                 try:
 238                     chtxt=chdb.ontvsearch(ch)['chtxt']
 239                 except:
 240                     chtxt="Unknown"
 241                 ret.append(chtxt+","+title.encode('utf-8')+","+btime+","+etime)
 242         return ret
 243     def select_by_time_ngram_tv(self,btime,etime,chtxt):
 244         db=self.connect_db()
 245         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"
 246         dbcmd=db[1].execute(dbexe,(btime,etime,chtxt))
 247         retall=[]
 248         if dbcmd>0:
 249             retall=db[1].fetchall()
 250         self.close_db(db)
 251         return retall
 252     def new_ch(self,bctype):
 253         db=self.connect_db()
 254         try:
 255             db[1].execute("delete from ch where bctype = %s", (bctype,))
 256         except:
 257             ""
 258         try:
 259             db[1].execute('create table ch (bctype VARCHAR(20),channel VARCHAR(20) NOT NULL,display VARCHAR(100),UNIQUE unich(bctype,channel))')
 260         except:
 261             ""
 262         #db.commit()
 263         self.close_db(db)
 264     def add_ch(self,bctype, channel, display):
 265         db=self.connect_db()
 266         db[1].execute('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', (bctype, channel, display))
 267         #db.commit()
 268         self.close_db(db)
 269     def add_multi_ch(self,chlists):
 270         """
 271         chlists is (bctype,channel,display) lists
 272         """
 273         db=self.connect_db()
 274         db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
 275         self.close_db(db)
 276     def import_from_SQLite(self,dbpath):
 277         dbb=dbSQLite.DB_SQLite(dbpath)
 278         dbl=dbb.select_all_rectime()
 279         for db in dbl:
 280             self.add_rectime(db['type'],db['chtxt'],db['title'],db['btime'],db['etime'],db['deltatime'],db['deltaday'],db['opt'])
 281