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 path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
  10
  11 class DB_MySQL():
  12     dbname=""
  13     dbhost=""
  14     dbusr=""
  15     dbpasswd=""
  16     dbport=0
  17     def __init__(self,dbname,user,passwd,host="localhost",port=3306):
  18         self.dbname = dbname
  19         self.dbhost=host
  20         self.dbusr=user
  21         self.dbpasswd=passwd
  22         self.dbport=port
  23         try:
  24             con = MySQLdb.connect(user= user, passwd = passwd)
  25             cur=con.cursor()
  26             cur.execute('create database '+dbname)
  27             cur.close()
  28             con.close()
  29         except:
  30             ""
  31         db = self.connect_db()
  32         try:
  33             db[1].execute('create table rectime (type TEXT,chtxt TEXT,title TEXT,btime DATETIME,etime DATETIME,deltatime TEXT,deltaday TEXT,opt TEXT,id INTEGER PRIMARY KEY,UNIQUE unique (type,chtxt,title,btime,deltaday))')
  34         except:
  35             ""
  36         self.close_db(db)
  37     def connect_db(self):
  38         """
  39         dbへの接続
  40         """
  41         con=MySQLdb.connect(db=self.dbname,host=self.dbhost,port=self.dbport,user=self.dbusr,passwd=self.dbpasswd,charset="utf8")
  42         cur=con.cursor()
  43         cur.execute('set names utf8;')
  44         return [con,cur]
  45     def close_db(self,db):
  46         db[1].close()
  47         db[0].close()
  48     def new_chdata(self):
  49         db = self.connect_db()
  50         try:
  51             db[1].execute('drop table chdata')
  52         except:
  53             ""
  54         try:
  55             db[1].execute('create table chdata (bctype TEXT,ontv TEXT,chtxt TEXT,ch TEXT,csch TEXT,station TEXT,station_name TEXT,updatetime DATETIME)')
  56         except:
  57             ""
  58         self.close_db(db)
  59     def add_chdata(self,bctype, ontv, chtxt, ch, csch, station, station_name,updatetime):
  60         db = self.connect_db()
  61         #bctype=bctype.encode('utf-8')
  62         #ontv=ontv.encode('utf-8')
  63         #chtxt=chtxt.encode('utf-8')
  64         #ch=ch.encode('utf-8')
  65         #csch=csch.encode('utf-8')
  66         #station=station.encode('utf-8')
  67         #station_name=station_name.encode('utf-8')
  68         #updatetime=updatetime.encode('utf-8')
  69         db[1].execute('insert into chdata values (%s,%s,%s,%s,%s,%s,%s,%s)',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime))
  70         self.close_db(db)
  71     def select_by_ontv_chdata(self,ontv):
  72         db = self.connect_db()
  73         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ontv = %s",(ontv,))
  74         ret=dbexe.fetchall()
  75         self.close_db(db)
  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=dbexe.fetchall()
  81         self.close_db(db)
  82         return ret
  83     def select_by_bctype_chdata(self,bctype):
  84         db = self.connect_db()
  85         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE bctype = %s",(bctype,))
  86         ret=dbexe.fetchall()
  87         self.close_db(db)
  88         return ret
  89     def select_by_ch_chdata(self,ch):
  90         db = self.connect_db()
  91         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = %s",(ch,))
  92         ret=dbexe.fetchall()
  93         self.close_db(db)
  94         return ret
  95     def select_all_chdata(self):
  96         db = self.connect_db()
  97         dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ")
  98         ret=dbexe.fetchall()
  99         self.close_db(db)
 100         return ret
 101     def select_get_update_chdata(self,dhour):
 102         db = self.connect_db()
 103         dbexe=db[1].execute("SELECT bctype,chtxt FROM chdata WHERE updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR)")
 104         ret=dbexe.fetchall()
 105         self.close_db(db)
 106         return ret
 107     def update_by_bctype_chdata(self,bctype):
 108         db = self.connect_db()
 109         db[1].execute("UPDATE chdata SET updatetime=now() WHERE bctype = %s",(bctype,))
 110         self.close_db(db)
 111     def update_by_bctype_and_chtxt_chdata(self,bctype,chtxt):
 112         db = self.connect_db()
 113         db[1].execute("UPDATE chdata SET updatetime=now() WHERE bctype = %s AND chtxt = %s",(bctype,chtxt))
 114         self.close_db(db)
 115     def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 116         db = self.connect_db()
 117         db[1].execute('insert 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))
 118         ##db.commit()
 119         self.close_db(db)
 120     def del_rectime(self,type="", title="", chtxt="", btime=""):
 121         """
 122
 123         """
 124         db=self.connect_db()
 125         db[1].execute("delete from rectime where type = %s AND title = %s AND chtxt = %s AND btime = %s", (type, title, chtxt, btime))
 126         #db.commit()
 127         self.close_db(db)
 128     def select_all_rectime(self):
 129         db=self.connect_db()
 130         recdata=[]
 131         for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime"):
 132             ret={}
 133             ret['type']=typet
 134             ret['chtxt']=chtxt
 135             ret['title']=title.encode('utf-8')
 136             ret['btime']=btime
 137             ret['etime']=etime
 138             ret['opt']=opt
 139             if deltatime == None:
 140                 deltatime="3"
 141             if deltaday == None:
 142                 deltaday="7"
 143             if typet == 'key':
 144                 ret['deltatime']=deltatime
 145             elif typet == 'keyevery':
 146                 ret['deltatime']=deltatime
 147                 ret['deltaday']=deltaday
 148             recdata.append(ret)
 149         self.close_db(db)
 150         return recdata
 151     def select_bytime_rectime(self,dminutes):
 152         db=self.connect_db()
 153         recdatum=[]
 154         for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in 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 )"):
 155             ret={}
 156             ret['type']=typet
 157             ret['chtxt']=chtxt
 158             ret['title']=title.encode('utf-8')
 159             ret['btime']=btime
 160             ret['etime']=etime
 161             ret['opt']=opt
 162             if deltatime == None or deltatime == "":
 163                 deltatime="3"
 164             if deltaday == None or deltaday == "":
 165                 deltaday="7"
 166             if typet == 'key':
 167                 ret['deltatime']=deltatime
 168             elif typet == 'keyevery':
 169                 ret['deltatime']=deltatime
 170                 ret['deltaday']=deltaday
 171             recdatum.append(ret)
 172         self.close_db(db)
 173         return recdatum
 174     def delete_old_rectime(self,dhour):
 175         db=self.connect_db()
 176         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))
 177         #db.commit()
 178         self.close_db(db)
 179     def new_tv(self,bctype):
 180         db=self.connect_db()
 181         try:
 182             db[1].execute("delete from tv where bctype = %s", (bctype,))
 183         except:
 184             ""
 185         try:
 186             db[1].execute('create table tv (bctype TEXT,channel TEXT NOT NULL,start TEXT,stop  TEXT,title TEXT,desc  TEXT)')
 187         except:
 188             ""
 189         #db.commit()
 190         self.close_db(db)
 191     def add_tv(self,bctype, channel, start, stop, title, desc):
 192         db=self.connect_db()
 193         db[1].execute('insert into tv values (%s,%s,%s,%s,%s,%s)', (bctype, channel, start, stop, title, desc))
 194         #db.commit()
 195         self.close_db(db)
 196     def add_multi_tv(self,tvlists):
 197         """
 198         tvlists is (bctype,channel,start,stop,title,desc) lists.
 199         """
 200         db=self.connect_db()
 201         db[1].executemany('insert into tv values (%s,%s,%s,%s,%s,%s)', tvlists)
 202         #db.commit()
 203         self.close_db(db)
 204     def select_by_title_tv(self,title):
 205         db=self.connect_db()
 206         ret=[]
 207         for ch, title, start, stop in db[1].execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\""):
 208             btime=start.replace(" +0900","")
 209             btime=btime[0:4]+"-"+btime[4:6]+"-"+btime[6:8]+" "+btime[8:10]+":"+btime[10:12]+":00"
 210             etime=stop.replace(" +0900","")
 211             etime=etime[0:4]+"-"+etime[4:6]+"-"+etime[6:8]+" "+etime[8:10]+":"+etime[10:12]+":00"
 212             try:
 213                 chtxt=chdb.ontvsearch(ch)['chtxt']
 214             except:
 215                 chtxt="Unknown"
 216             ret.append(chtxt+","+title.encode('utf-8')+","+btime+","+etime)
 217         self.close_db(db)
 218         return ret
 219     def select_by_time_ngram_tv(self,btime,etime,chtxt):
 220         db=self.connect_db()
 221         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"
 222         dbcmd=db[1].execute(dbexe,(btime,etime,chtxt))
 223         retall=dbcmd.fetchall()
 224         self.close_db(db)
 225         return retall
 226     def new_ch(self,bctype):
 227         db=self.connect_db()
 228         try:
 229             db[1].execute("delete from ch where bctype = %s", (bctype,))
 230         except:
 231             ""
 232         try:
 233             db[1].execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT)')
 234         except:
 235             ""
 236         #db.commit()
 237         self.close_db(db)
 238     def add_ch(self,bctype, channel, display):
 239         db=self.connect_db()
 240         db[1].execute('insert into ch values (%s,%s,%s)', (bctype, channel, display))
 241         #db.commit()
 242         self.close_db(db)
 243     def add_multi_ch(self,chlists):
 244         """
 245         chlists is (bctype,channel,display) lists
 246         """
 247         db=self.connect_db()
 248         db[1].executemany('insert into ch values (%s,%s,%s)', chlists)
 249         #db.commit()
 250         self.close_db(db)