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         dls=[]
  69         if dbexe>0:
  70             dls=db[1].fetchall()
  71         self.close_db(db)
  72         for dl in dls:
  73             r=list(dl)
  74             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
  75             ret.append(r)
  76         return ret
  77     def select_by_chtxt_chdata(self,chtxt):
  78         db = self.connect_db()
  79         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt = %s",(chtxt,))
  80         ret=[]
  81         dls=[]
  82         if dbexe>0:
  83             dls=db[1].fetchall()
  84         self.close_db(db)
  85         for dl in dls:
  86             r=list(dl)
  87             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
  88             ret.append(r)
  89         return ret
  90     def select_by_bctype_chdata(self,bctype):
  91         db = self.connect_db()
  92         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime,status FROM chdata WHERE bctype = %s",(bctype,))
  93         ret=[]
  94         dls=[]
  95         if dbexe>0:
  96             dls=db[1].fetchall()
  97         self.close_db(db)
  98         for dl in dls:
  99             #print dl
 100             r=list(dl)
 101             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
 102             r[6]=str(r[6])
 103             ret.append(r)
 104         return ret
 105     def select_by_ch_chdata(self,ch):
 106         db = self.connect_db()
 107         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = %s",(ch,))
 108         ret=[]
 109         dls=[]
 110         if dbexe>0:
 111             dls=db[1].fetchall()
 112         self.close_db(db)
 113         for dl in dls:
 114             r=list(dl)
 115             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
 116             ret.append(r)
 117         return ret
 118     def select_all_chdata(self):
 119         db = self.connect_db()
 120         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ")
 121         ret=[]
 122         dls=[]
 123         if dbexe>0:
 124             dls=db[1].fetchall()
 125         self.close_db(db)
 126         for dl in dls:
 127             r=list(dl)
 128             r[5]=r[5].strftime("%Y-%m-%d %H:%M:%S")
 129             ret.append(r)
 130         return ret
 131     def select_get_update_chdata(self,dhour):
 132         db = self.connect_db()
 133         dbexe=db[1].execute("SELECT bctype,chtxt,status FROM chdata WHERE ( updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR) AND status = 1 ) OR status > 1 ORDER BY status DESC")
 134         ret=[]
 135         #print dbexe
 136         if dbexe>0:
 137             ret=db[1].fetchall()
 138         self.close_db(db)
 139         return ret
 140     def update_by_bctype_chdata(self,bctype):
 141         db = self.connect_db()
 142         db[1].execute("UPDATE chdata SET updatetime=now() , status = 1 WHERE bctype = %s",(bctype,))
 143         self.close_db(db)
 144     def update_by_bctype_and_chtxt_chdata(self,bctype,chtxt):
 145         db = self.connect_db()
 146         db[1].execute("UPDATE chdata SET updatetime=now() , status = 1WHERE bctype = %s AND chtxt = %s",(bctype,chtxt))
 147         self.close_db(db)
 148     def update_status_by_bctype_chdata(self,bctype,status):
 149         db = self.connect_db()
 150         db[1].execute("UPDATE chdata SET status=%s WHERE bctype = %s",(status,bctype))
 151         self.close_db(db)
 152     def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 153         db = self.connect_db()
 154         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))
 155         ##db.commit()
 156         self.close_db(db)
 157     def del_rectime(self,type="", title="", chtxt="", btime=""):
 158         """
 159
 160         """
 161         db=self.connect_db()
 162         db[1].execute("delete from rectime where type = %s AND title = %s AND chtxt = %s AND btime = %s", (type, title, chtxt, btime))
 163         #db.commit()
 164         self.close_db(db)
 165     def select_all_rectime(self):
 166         db=self.connect_db()
 167         recdata=[]
 168         dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime")
 169         dbl=db[1].fetchall()
 170         self.close_db(db)
 171         if dbr>0:
 172             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 173                 ret={}
 174                 ret['type']=typet
 175                 ret['chtxt']=chtxt
 176                 ret['title']=title.encode('utf-8')
 177                 btime=btime.strftime("%Y-%m-%d %H:%M:%S")
 178                 etime=etime.strftime("%Y-%m-%d %H:%M:%S")
 179                 ret['btime']=btime
 180                 ret['etime']=etime
 181                 ret['opt']=opt
 182                 ret['deltatime']=""
 183                 ret['deltaday']=""
 184                 if deltatime == None:
 185                     deltatime="3"
 186                 if deltaday == None:
 187                     deltaday="7"
 188                 if typet == 'key':
 189                     ret['deltatime']=deltatime
 190                 elif typet == 'keyevery':
 191                     ret['deltatime']=deltatime
 192                     ret['deltaday']=deltaday
 193                 recdata.append(ret)
 194         self.close_db(db)
 195         return recdata
 196     def select_bytime_rectime(self,dminutes):
 197         db=self.connect_db()
 198         recdatum=[]
 199         #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 )")
 200         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 )")
 201         dbl=db[1].fetchall()
 202         self.close_db(db)
 203         #print dbl
 204         if dbr > 0:
 205             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 206                 ret={}
 207                 ret['type']=typet
 208                 ret['chtxt']=chtxt
 209                 ret['title']=title.encode('utf-8')
 210                 btime=btime.strftime("%Y-%m-%d %H:%M:%S")
 211                 etime=etime.strftime("%Y-%m-%d %H:%M:%S")
 212                 ret['btime']=btime
 213                 ret['etime']=etime
 214                 ret['opt']=opt
 215                 if deltatime == None or deltatime == "":
 216                     deltatime="3"
 217                 if deltaday == None or deltaday == "":
 218                     deltaday="7"
 219                 if typet == 'key':
 220                     ret['deltatime']=deltatime
 221                 elif typet == 'keyevery':
 222                     ret['deltatime']=deltatime
 223                     ret['deltaday']=deltaday
 224                 recdatum.append(ret)
 225         return recdatum
 226     def delete_old_rectime(self,dhour):
 227         db=self.connect_db()
 228         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))
 229         #db.commit()
 230         self.close_db(db)
 231     def new_tv(self,bctype):
 232         db=self.connect_db()
 233         try:
 234             db[1].execute("delete from tv where bctype = %s", (bctype,))
 235         except:
 236             ""
 237         try:
 238             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))")
 239         except:
 240             ""
 241         #db.commit()
 242         self.close_db(db)
 243     def add_tv(self,bctype, channel, start, stop, title, desc):
 244         db=self.connect_db()
 245         db[1].execute('INSERT IGNORE INTO tv VALUES (%s,%s,%s,%s,%s,%s)', (bctype, channel, start, stop, title, desc))
 246         #db.commit()
 247         self.close_db(db)
 248     def add_multi_tv(self,tvlists):
 249         """
 250         tvlists is (bctype,channel,start,stop,title,desc) lists.
 251         """
 252         db=self.connect_db()
 253         db[1].executemany('INSERT IGNORE INTO tv (bctype,channel,start,stop,title,exp) values(%s,%s,%s,%s,%s,%s)', tvlists)
 254         #db.commit()
 255         self.close_db(db)
 256
 257
 258     def select_by_time_ngram_tv(self,btime,etime,chtxt):
 259         db=self.connect_db()
 260         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"
 261         dbcmd=db[1].execute(dbexe,(btime,etime,chtxt))
 262         retall=[]
 263         if dbcmd>0:
 264             retall=db[1].fetchall()
 265         self.close_db(db)
 266         return retall
 267     def new_ch(self,bctype):
 268         db=self.connect_db()
 269         try:
 270             db[1].execute("delete from ch where bctype = %s", (bctype,))
 271         except:
 272             ""
 273         try:
 274             db[1].execute('create table ch (bctype VARCHAR(20),channel VARCHAR(20) NOT NULL,display VARCHAR(100),UNIQUE unich(bctype,channel))')
 275         except:
 276             ""
 277         #db.commit()
 278         self.close_db(db)
 279     def add_ch(self,bctype, channel, display):
 280         db=self.connect_db()
 281         db[1].execute('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', (bctype, channel, display))
 282         #db.commit()
 283         self.close_db(db)
 284     def add_multi_ch(self,chlists):
 285         """
 286         chlists is (bctype,channel,display) lists
 287         """
 288         db=self.connect_db()
 289         db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
 290         self.close_db(db)
 291     def import_from_SQLite(self,dbpath):
 292         dbb=dbSQLite.DB_SQLite(dbpath)
 293         dbl=dbb.select_all_rectime()
 294         for db in dbl:
 295             self.add_rectime(db['type'],db['chtxt'],db['title'],db['btime'],db['etime'],db['deltatime'],db['deltaday'],db['opt'])
 296
 297 """    def select_by_title_tv(self,title):
 298         db=self.connect_db()
 299         ret=[]
 300         dbr=db[1].execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\"")
 301         dbl=db[1].fetchall()
 302         self.close_db(db)
 303         if dbr>0:
 304             for ch, title, start, stop in dbl:
 305                 btime=start.replace(" +0900","")
 306                 btime=btime[0:4]+"-"+btime[4:6]+"-"+btime[6:8]+" "+btime[8:10]+":"+btime[10:12]+":00"
 307                 etime=stop.replace(" +0900","")
 308                 etime=etime[0:4]+"-"+etime[4:6]+"-"+etime[6:8]+" "+etime[8:10]+":"+etime[10:12]+":00"
 309                 try:
 310                     chtxt=chdb.ontvsearch(ch)['chtxt']
 311                 except:
 312                     chtxt="Unknown"
 313                 ret.append(chtxt+","+title.encode('utf-8')+","+btime+","+etime)
 314         return ret"""