X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;f=rec10%2Ftrunk%2Fsrc%2FdbMySQL.py;h=dce770f9e83c105cdeff07d9f90a38e1e292d3e0;hb=c6d09b8884d77c8eff6e9540478e7fc4fd05177d;hp=6924bc060a0f67c267bb8b5e469917cfb0350867;hpb=fc93091eb8e5df09cbd1af75c20401c905682253;p=rec10%2Frec10-git.git diff --git a/rec10/trunk/src/dbMySQL.py b/rec10/trunk/src/dbMySQL.py index 6924bc0..dce770f 100644 --- a/rec10/trunk/src/dbMySQL.py +++ b/rec10/trunk/src/dbMySQL.py @@ -1,238 +1,804 @@ - #!/usr/bin/python # coding: UTF-8 # Rec10 TS Recording Tools # Copyright (C) 2009 Yukikaze - -import recdb -import os -import time import MySQLdb -path = str(os.path.dirname(os.path.abspath(__file__))) + "/" -class DB_MySQL(): - dbname="" - dbhost="" - dbusr="" - dbpasswd="" - dbport=0 - def __init__(self,dbname,host="localhost",user,passwd,port=3306): - self.dbname = dbpath - self.dbhost=host - self.dbusr=usr - self.dbpasswd=passwd - self.dbport=port - db = self.connect_db(self.dbname,self.dbhost,self.dbport,self.dbusr,self.dbpasswd) +import recdblist +from decimal import Decimal +class DB_MySQL: + dbname = "" + dbhost = "" + dbusr = "" + dbpasswd = "" + dbport = 0 + def __init__(self, dbname, user, passwd, host="localhost", port=3306): + self.dbname = dbname + self.dbhost = host + self.dbusr = user + self.dbpasswd = passwd + self.dbport = port + try: + con = MySQLdb.connect(user=user, passwd=passwd) + cur = con.cursor() + cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8") + cur.close() + con.close() + except: + "" + db = self.connect_db() try: - db.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))') + db[1].execute('\ + CREATE TABLE timeline \ + (\ + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\ + type VARCHAR(40),\ + chtxt VARCHAR(40),\ + title VARCHAR(100),\ + btime DATETIME,\ + etime DATETIME,\ + deltatime VARCHAR(5),\ + deltaday VARCHAR(5),\ + opt VARCHAR(20),\ + UNIQUE uni (type,chtxt,title,btime,deltaday)\ + )') except: "" - db.commit self.close_db(db) - def connect_db(self,db,host,port,user,passwd): + self.new_epg_timeline("") + self.new_in_timeline_log() + self.new_in_auto_bayes_key() + self.new_in_auto_jbk_key() + self.new_auto_proc() + self.new_in_status() + self.new_auto_timeline_bayes() + self.new_auto_timeline_keyword() + def connect_db(self): """ dbへの接続 - """ - return MySQLdb.connect(db=self.dbname,host=dbhost,port=dbport,user=self.dbusr,passwd=self.dbpasswd) - def close_db(self,db): - db.close() - def new_chdata(self): - db = self.connect_db(60) + con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8") + cur = con.cursor() + cur.execute('set names utf8;') + return [con, cur] + def close_db(self, db): + db[1].close() + db[0].close() + def new_epg_ch(self): + db = self.connect_db() try: - db.execute('drop table chdata') - db.commit() + db[1].execute('drop table epg_ch') except: "" try: - db.execute('create table chdata (bctype TEXT,ontv TEXT,chtxt TEXT,ch TEXT,csch TEXT,station TEXT,station_name TEXT,updatetime DATETIME)') + db[1].execute('\ + CREATE TABLE epg_ch \ + (\ + bctype VARCHAR(15),\ + ontv VARCHAR(30) PRIMARY KEY,\ + chtxt VARCHAR(20),\ + ch VARCHAR(20),\ + csch VARCHAR(20),\ + chname VARCHAR(100),\ + updatetime DATETIME,\ + status TINYINT\ + )') except: "" - db.commit() self.close_db(db) - def add_chdata(self,bctype, ontv, chtxt, ch, csch, station, station_name,updatetime): - db = self.connect_db(60) - db.execute('insert into chdata values (?,?,?,?,?,?,?,?)',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime)) - db.commit() + def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime): + db = self.connect_db() + db[1].execute('\ + INSERT INTO epg_ch \ + VALUES (%s,%s,%s,%s,%s,"",%s,%s)', \ + (bctype, ontv, chtxt, ch, csch, updatetime, "1")) self.close_db(db) - def select_by_ontv_chdata(self,ontv): - db = self.connect_db(60) - dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ontv = ?",(ontv,)) - ret=dbexe.fetchall() + def select_by_ontv_epg_ch(self, ontv): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT bctype,ontv,chtxt,ch,csch,updatetime \ + FROM epg_ch \ + WHERE ontv = %s", \ + (ontv,)) + ret = [] + dls = [] + if dbexe > 0: + dls = db[1].fetchall() self.close_db(db) + for dl in dls: + r = list(dl) + r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") + ret.append(r) return ret - def select_by_chtxt_chdata(self,chtxt): - db = self.connect_db(60) - dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt = ?",(chtxt,)) - ret=dbexe.fetchall() + def select_by_chtxt_epg_ch(self, chtxt): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT bctype,ontv,chtxt,ch,csch,updatetime \ + FROM epg_ch \ + WHERE chtxt = %s", \ + (chtxt,)) + ret = [] + dls = [] + if dbexe > 0: + dls = db[1].fetchall() self.close_db(db) + for dl in dls: + r = list(dl) + r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") + ret.append(r) return ret - def select_by_bctype_chdata(self,bctype): - db = self.connect_db(60) - dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE bctype = ?",(bctype,)) - ret=dbexe.fetchall() + def select_by_bctype_epg_ch(self, bctype): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \ + FROM epg_ch \ + WHERE bctype = %s", \ + (bctype,)) + ret = [] + dls = [] + if dbexe > 0: + dls = db[1].fetchall() self.close_db(db) + for dl in dls: + #recdblist.printutf8(dl) + r = list(dl) + r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") + r[6] = str(r[6]) + ret.append(r) return ret - def select_by_ch_chdata(self,ch): - db = self.connect_db(60) - dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = ?",(ch,)) - ret=dbexe.fetchall() + def select_by_ch_epg_ch(self, ch): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT \ + bctype,ontv,chtxt,ch,csch,updatetime \ + FROM epg_ch \ + WHERE ch = %s", \ + (ch,)) + ret = [] + dls = [] + if dbexe > 0: + dls = db[1].fetchall() self.close_db(db) + for dl in dls: + r = list(dl) + r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") + ret.append(r) return ret - def select_all_chdata(self): - db = self.connect_db(60) - dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ") - ret=dbexe.fetchall() + def select_all_epg_ch(self): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT bctype,ontv,chtxt,ch,csch,updatetime \ + FROM epg_ch \ + ") + ret = [] + dls = [] + if dbexe > 0: + dls = db[1].fetchall() self.close_db(db) + for dl in dls: + r = list(dl) + r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") + ret.append(r) return ret - def select_get_update_chdata(self,dhour): - db = self.connect_db(60) - dbexe=db.execute("SELECT bctype,chtxt FROM chdata WHERE updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR)") - ret=dbexe.fetchall() + def select_get_update_epg_ch(self, dhour): + db = self.connect_db() + dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \ + WHERE \ + ( \ + updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \ + AND \ + status = 1 \ + ) \ + OR \ + status > 1 \ + ORDER BY status DESC") + ret = [] + #recdblist.printutf8(dbexe) + if dbexe > 0: + ret = db[1].fetchall() self.close_db(db) return ret - def update_by_bctype_chdata(self,bctype): - db = self.connect_db(60) - db.execute("UPDATE chdata SET updatetime=now() WHERE bctype = ?",(bctype,)) - db.commit() + def update_by_bctype_epg_ch(self, bctype): + db = self.connect_db() + db[1].execute("\ + UPDATE epg_ch \ + SET \ + updatetime=now() , \ + status = 1 \ + WHERE bctype = %s", (bctype,)) + self.close_db(db) + def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt): + db = self.connect_db() + db[1].execute("\ + UPDATE epg_ch \ + SET \ + updatetime=now() , \ + status = 1\ + WHERE bctype = %s AND chtxt = %s", (bctype, chtxt)) + self.close_db(db) + def update_status_by_bctype_epg_ch(self, bctype, status): + db = self.connect_db() + db[1].execute("\ + UPDATE epg_ch \ + SET \ + status=%s \ + WHERE bctype = %s", \ + (status, bctype)\ + ) + self.close_db(db) + def update_chname_by_ontv_epg_ch(self,ontv,chname): + db = self.connect_db() + db[1].execute("\ + UPDATE epg_ch \ + SET \ + chname=%s \ + WHERE ontv = %s", \ + (chname, ontv)\ + ) + self.close_db(db) + def add_auto_proc(self,type,title): + db = self.connect_db() + db[1].execute('\ + INSERT IGNORE into auto_proc \ + (type,title) \ + values (%s,%s)', \ + ( type, title)) + ##db.commit() self.close_db(db) - def update_by_bctype_and_chtxt_chdata(self,bctype,chtxt): - db = self.connect_db(60) - db.execute("UPDATE chdata SET updatetime=now() WHERE bctype = ? AND chtxt = ?",(bctype,chtxt)) - db.commit() + def new_auto_proc(self): + db = self.connect_db() + try: + db[1].execute('drop table auto_proc') + except: + "" + try: + db[1].execute('\ + CREATE TABLE auto_proc \ + (\ + type VARCHAR(20),\ + title VARCHAR(100) PRIMARY KEY,\ + UNIQUE unibayeskey(title)\ + )') + except: + "" self.close_db(db) - def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""): - db = self.connect_db(60) - db.execute('insert into rectime (type,chtxt,title,btime,etime,deltatime,deltaday,opt) values (?,?,?,?,?,?,?,?)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt)) - db.commit() + def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""): + db = self.connect_db() + db[1].execute('\ + INSERT IGNORE into in_timeline_log \ + (chtxt,title,btime,etime,opt,exp,longexp,category) \ + values (%s,%s,%s,%s,%s,%s,%s,%s)', \ + ( chtxt, title, btime, etime, opt,exp,longexp,category)) + ##db.commit() self.close_db(db) - def del_rectime(self,type="", title="", chtxt="", btime=""): + def del_in_timeline_log(self, title="", chtxt="", btime=""): """ """ - db=self.connect_db(60) - db.execute("delete from rectime where type = ? AND title = ? AND chtxt = ? AND btime = ?", (type, title, chtxt, btime)) - db.commit() - self.close_db(db) - def select_all_rectime(self): - db=self.connect_db(60) - recdata=[] - for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime"): - ret={} - ret['type']=typet - ret['chtxt']=chtxt - ret['title']=title.encode('utf-8') - ret['btime']=btime - ret['etime']=etime - ret['opt']=opt - if deltatime == None: - deltatime="3" - if deltaday == None: - deltaday="7" - if typet == 'key': - ret['deltatime']=deltatime - elif typet == 'keyevery': - ret['deltatime']=deltatime - ret['deltaday']=deltaday - recdata.append(ret) + db = self.connect_db() + db[1].execute("\ + DELETE FROM in_timeline_log \ + WHERE title = %s AND chtxt = %s AND btime = %s", \ + (title, chtxt, btime)) + #db.commit() + self.close_db(db) + def new_in_timeline_log(self): + db = self.connect_db() + try: + db[1].execute('\ + CREATE TABLE in_timeline_log \ + (\ + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\ + chtxt VARCHAR(20),\ + title VARCHAR(100),\ + btime DATETIME,\ + etime DATETIME,\ + opt VARCHAR(20),\ + exp VARCHAR(200),\ + longexp TEXT,\ + category VARCHAR(100),\ + UNIQUE uni (chtxt,title,btime,category)\ + )') + except: + "" + self.close_db(db) + def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""): + db = self.connect_db() + db[1].execute('\ + INSERT IGNORE into timeline \ + (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)) + ##db.commit() + self.close_db(db) + def del_timeline(self, type="", title="", chtxt="", btime=""): + """ + + """ + db = self.connect_db() + db[1].execute("\ + DELETE FROM timeline \ + WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \ + (type, title, chtxt, btime)) + #db.commit() + self.close_db(db) + def select_all_timeline(self): + db = self.connect_db() + recdata = [] + dbr = db[1].execute("\ + SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \ + FROM timeline") + dbl = db[1].fetchall() + self.close_db(db) + if dbr > 0: + for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl: + ret = {} + ret['type'] = typet + ret['chtxt'] = chtxt + ret['title'] = title + btime = btime.strftime("%Y-%m-%d %H:%M:%S") + etime = etime.strftime("%Y-%m-%d %H:%M:%S") + ret['btime'] = btime + ret['etime'] = etime + ret['opt'] = opt + ret['deltatime'] = "" + ret['deltaday'] = "" + if deltatime == None: + deltatime = "3" + if deltaday == None: + deltaday = "7" + if typet == 'key': + ret['deltatime'] = deltatime + elif typet == 'keyevery': + ret['deltatime'] = deltatime + ret['deltaday'] = deltaday + recdata.append(ret) self.close_db(db) return recdata - def select_bytime_rectime(self,dhour): - db=self.connect_db(60) - recdatum=[] - for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR ) AND btime > DATE_ADD(now(),INTERVAL "+dhour+" HOUR )"): - ret={} - ret['type']=typet - ret['chtxt']=chtxt - ret['title']=title.encode('utf-8') - ret['btime']=btime - ret['etime']=etime - ret['opt']=opt - if deltatime == None or deltatime == "": - deltatime="3" - if deltaday == None or deltaday == "": - deltaday="7" - if typet == 'key': - ret['deltatime']=deltatime - elif typet == 'keyevery': - ret['deltatime']=deltatime - ret['deltaday']=deltaday - recdatum.append(ret) + def select_bytime_timeline(self, dminutes): + db = self.connect_db() + recdatum = [] + #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 )") + dbr = db[1].execute("SELECT \ + type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \ + FROM timeline \ + WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \ + DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )") + dbl = db[1].fetchall() self.close_db(db) + #recdblist.printutf8(dbl) + if dbr > 0: + for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl: + ret = {} + ret['type'] = typet + ret['chtxt'] = chtxt + ret['title'] = title + btime = btime.strftime("%Y-%m-%d %H:%M:%S") + etime = etime.strftime("%Y-%m-%d %H:%M:%S") + ret['btime'] = btime + ret['etime'] = etime + ret['opt'] = opt + if deltatime == None or deltatime == "": + deltatime = "3" + if deltaday == None or deltaday == "": + deltaday = "7" + if typet == recdblist.REC_KEYWORD: + ret['deltatime'] = deltatime + elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS: + ret['deltatime'] = deltatime + ret['deltaday'] = deltaday + recdatum.append(ret) return recdatum - def delete_old_rectime(self,dhour): - db=self.connect_db(60) - db.execute("DELETE FROM rectime WHERE NOT ( type = ? OR type = ? ) AND btime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR )", (recdb.REC_MISS_ENCODE, recdb.REC_KEYWORD_EVERY_SOME_DAYS)) - db.commit() + def delete_old_timeline(self, dhour): + db = self.connect_db() + db[1].execute("\ + DELETE FROM timeline \ + WHERE \ + btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )") + #db.commit() self.close_db(db) - def new_tv(self,bctype): - db=self.connect_db(60) + def new_in_auto_jbk_key(self): + db = self.connect_db() try: - db.execute("delete from tv where bctype = ?", (bctype,)) + db[1].execute("\ + CREATE TABLE in_auto_jbk_key \ + (\ + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\ + keyword VARCHAR(200),\ + UNIQUE unijbk (keyword)\ + )") except: "" + self.close_db(db) + def add_in_auto_jbk_key(self,key): + db = self.connect_db() + db[1].execute('\ + INSERT IGNORE into in_auto_jbk_key \ + (keyword) \ + values (%s)', \ + (key,)) + ##db.commit() + self.close_db(db) + def select_all_in_auto_jbk_key(self): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT keyword \ + FROM in_auto_jbk_key \ + ") + ret = [] + if dbexe > 0: + ret = db[1].fetchall() + self.close_db(db) + return ret + def new_in_status(self): + db = self.connect_db() try: - db.execute('create table tv (bctype TEXT,channel TEXT NOT NULL,start TEXT,stop TEXT,title TEXT,desc TEXT)') + db[1].execute("\ + CREATE TABLE in_status \ + (\ + ts2avi TINYINT DEFAULT 0,\ + terec TINYINT DEFAULT 0,\ + bscsrec TINYINT DEFAULT 0,\ + b252ts TINYINT DEFAULT 0\ + )") + db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0)") except: "" - db.commit() - self.close_db(db) - def add_tv(self,bctype, channel, start, stop, title, desc): - db=self.connect_db(240) - db.execute('insert into tv values (?,?,?,?,?,?)', (bctype, channel, start, stop, title, desc)) - db.commit() - self.close_db(db) - def add_multi_tv(self,tvlists): - """ - tvlists is (bctype,channel,start,stop,title,desc) lists. - """ - db=self.connect_db(240) - db.executemany('insert into tv values (?,?,?,?,?,?)', tvlists) - db.commit() - self.close_db(db) - def select_by_title_tv(self,title): - db=self.connect_db(120) - ret=[] - for ch, title, start, stop in db.execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\""): - btime=start.replace(" +0900","") - btime=btime[0:4]+"-"+btime[4:6]+"-"+btime[6:8]+" "+btime[8:10]+":"+btime[10:12]+":00" - etime=stop.replace(" +0900","") - etime=etime[0:4]+"-"+etime[4:6]+"-"+etime[6:8]+" "+etime[8:10]+":"+etime[10:12]+":00" - try: - chtxt=chdb.ontvsearch(ch)['chtxt'] - except: - chtxt="Unknown" - ret.append(chtxt+","+title.encode('utf-8')+","+btime+","+etime) self.close_db(db) + def select_all_in_status(self): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT ts2avi,terec,bscsrec,b252ts \ + FROM in_status \ + ") + ret = [] + dls = [] + if dbexe > 0: + dls = db[1].fetchall() + self.close_db(db) + for dl in dls: + r = list(dl) + r[0]=str(r[0]) + r[1]=str(r[1]) + r[2]=str(r[2]) + r[3]=str(r[3]) + ret.append(r) return ret - def select_by_time_ngram_tv(self,btime,etime,chtxt): - db=self.connect_db(120) - dbexe="SELECT chdata.chtxt,title,start,stop FROM tv INNER JOIN chdata WHERE chdata.ontv=tv.channel AND start >= ? AND start <= ? AND chdata.chtxt=?" - dbcmd=db.execute(dbexe,(btime,etime,chtxt)) - retall=dbcmd.fetchall() + + def change_ts2avi_in_status(self,i): + """ + statuをiだけ増減する + iはint + """ + db = self.connect_db() + db[1].execute("\ + UPDATE in_status SET ts2avi=ts2avi+%s",i) self.close_db(db) - return retall - def new_ch(self,bctype): - db=self.connect_db(120) + def change_terec_in_status(self,i): + """ + statuをiだけ増減する + iはint + """ + db = self.connect_db() + db[1].execute("\ + UPDATE in_status SET terec=terec+%s",i) + self.close_db(db) + def change_bscsrec_in_status(self,i): + """ + statuをiだけ増減する + iはint + """ + db = self.connect_db() + db[1].execute("\ + UPDATE in_status SET bscsrec=bscsrec+%s",i) + self.close_db(db) + def change_b252ts_in_status(self,i): + """ + statuをiだけ増減する + iはint + """ + db = self.connect_db() + db[1].execute("\ + UPDATE in_status SET b252ts=b252ts+%s",i) + self.close_db(db) + def new_epg_timeline(self, bctype): + db = self.connect_db() try: - db.execute("delete from ch where bctype = ?", (bctype,)) + db[1].execute("\ + DELETE FROM epg_timeline \ + WHERE bctype = %s", \ + (bctype,)) except: "" try: - db.execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT)') + db[1].execute("\ + CREATE TABLE epg_timeline \ + (\ + bctype VARCHAR(20),\ + channel VARCHAR(100) NOT NULL,\ + start VARCHAR(30),\ + stop VARCHAR(30),\ + title VARCHAR(100),\ + exp VARCHAR(200),\ + longexp TEXT,\ + category VARCHAR(100),\ + UNIQUE unitv(bctype,channel,start,stop,title)\ + )") + except: + "" + #db.commit() + self.close_db(db) + def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category): + db = self.connect_db() + db[1].execute('\ + INSERT IGNORE INTO epg_timeline \ + VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \ + (bctype, channel, start, stop, title, desc,longdesc,category)) + #db.commit() + self.close_db(db) + def add_multi_epg_timeline(self, tvlists): + """ + tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists. + """ + db = self.connect_db() + db[1].executemany('\ + INSERT IGNORE INTO epg_timeline \ + (bctype,channel,start,stop,title,exp,longexp,category) \ + values(%s,%s,%s,%s,%s,%s,%s,%s)', \ + tvlists) + self.close_db(db) + def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt): + db = self.connect_db() + dbexe = "\ + SELECT \ + epg_ch.chtxt,title,start,stop,exp,longexp,category \ + FROM epg_timeline \ + INNER JOIN epg_ch \ + WHERE epg_ch.ontv=epg_timeline.channel \ + AND \ + start >= %s \ + AND \ + start <= %s \ + AND \ + epg_ch.chtxt=%s" + dbcmd = db[1].execute(dbexe, (btime, etime, chtxt)) + retall = [] + if dbcmd > 0: + retall = db[1].fetchall() + self.close_db(db) + return retall + def select_by_time_auto_suggest_epg_timeline(self,keyword,btime,etime): + db = self.connect_db() + dbexe = "\ + SELECT \ + epg_ch.chtxt,title,start,stop,exp,longexp,category \ + FROM epg_timeline \ + INNER JOIN epg_ch \ + WHERE epg_ch.ontv=epg_timeline.channel \ + AND \ + start >= %s \ + AND \ + start <= %s \ + AND \ + ( \ + ( title LIKE \'%%"+keyword+"%%\' ) \ + OR \ + ( exp LIKE \'%%"+keyword+"%%\' ) \ + OR \ + ( longexp LIKE \'%%"+keyword+"%%\' ) \ + )" + dbcmd = db[1].execute(dbexe,(btime, etime)) + retall = [] + if dbcmd > 0: + retall = db[1].fetchall() + self.close_db(db) + return retall + #def new_epg_ch(self, bctype): + # db = self.connect_db() + # try: + # db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,)) + # except: + # "" + # try: + # db[1].execute('\ + # CREATE TABLE epg_ch \ + # (\ + # bctype VARCHAR(20),\ + # channel VARCHAR(20) NOT NULL,\ + # display VARCHAR(100),\ + # UNIQUE unich(bctype,channel)\ + # )') + # except: + # "" + #db.commit() + # self.close_db(db) + #def add_epg_ch(self, bctype, channel, display): + # db = self.connect_db() + ## db[1].execute('INSERT IGNORE INTO epg_ch VALUES (%s,%s,%s)', (bctype, channel, display)) + #db.commit() + # self.close_db(db) + #def add_multi_ch(self, chlists): + # """ + # chlists is (bctype,channel,display) lists + ## """ + # db = self.connect_db() + # db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists) + # self.close_db(db) + def new_in_auto_bayes_key(self): + db = self.connect_db() + try: + db[1].execute('CREATE TABLE in_auto_bayes_key \ + (\ + keychar VARCHAR(10),\ + chtxt VARCHAR(20),\ + ratio_rec DECIMAL(32,14),\ + ratio_all DECIMAL(32,14),\ + UNIQUE unibayeskey(keychar,chtxt)\ + )') + db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)') except: "" - db.commit() self.close_db(db) - def add_ch(self,bctype, channel, display): - db=self.connect_db(240) - db.execute('insert into ch values (?,?,?)', (bctype, channel, display)) - db.commit() + + def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all): + db = self.connect_db() + ratio_rec=str(ratio_rec) + ratio_all=str(ratio_all) + db[1].execute('\ + INSERT IGNORE INTO in_auto_bayes_key \ + (keychar,chtxt,ratio_rec,ratio_all) \ + values (%s,%s,%s,%s)',\ + (key,chtxt,ratio_rec,ratio_all)) self.close_db(db) - def add_multi_ch(self,chlists): + def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num): + db = self.connect_db() + add_rec_num=str(add_rec_num) + add_all_num=str(add_all_num) + db[1].execute("\ + UPDATE in_auto_bayes_key SET ratio_rec=CONVERT(ratio_rec+%s,DECIMAL(32,14)),ratio_all=CONVERT(ratio_all+%s,DECIMAL(32,14)) WHERE keychar=\"NUM\" AND chtxt=%s",\ + (add_rec_num,add_all_num,chtxt)) + self.close_db(db) + def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all): """ - chlists is (bctype,channel,display) lists """ - db=self.connect_db(240) - db.executemany('insert into ch values (?,?,?)', chlists) - db.commit() + db = self.connect_db() + db[1].execute("\ + UPDATE in_auto_bayes_key SET ratio_rec=%s,ratio_all=%s WHERE keychar=%s AND chtxt=%s",(str(new_ratio_rec),str(new_ratio_all),key,chtxt)\ + ) + self.close_db(db) + def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum): + beforenum=str(beforenum) + newnum=str(newnum) + db = self.connect_db() + db[1].execute("\ + UPDATE in_auto_bayes_key SET ratio_all=CONVERT(ratio_all*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\ + ) + self.close_db(db) + def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum): + db = self.connect_db() + beforenumf=beforenum + beforenum=str(beforenum) + db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \ + VALUES (%s,%s,%s,%s)\ + ON DUPLICATE KEY UPDATE \ + ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key)) + self.close_db(db) + def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list): + """ + list={key:addnum}のリスト + """ + beforenumf=beforenum + beforenum=str(beforenum) + db = self.connect_db() + for i,j in list.items(): + retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum) + db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \ + VALUES (%s,%s,%s,%s)\ + ON DUPLICATE KEY UPDATE \ + ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl) + self.close_db(db) + def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum): + beforenum=str(beforenum) + newnum=str(newnum) + db = self.connect_db() + db[1].execute("\ + UPDATE in_auto_bayes_key SET ratio_rec=CONVERT(ratio_rec*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\ + ) + self.close_db(db) + def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum): + db = self.connect_db() + beforenumf=beforenum + beforenum=str(beforenum) + db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \ + VALUES (%s,%s,%s,%s)\ + ON DUPLICATE KEY UPDATE \ + ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key)) + self.close_db(db) + def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum): + beforenumf=beforenum + beforenum=str(beforenum) + db = self.connect_db() + for i,j in list.items(): + retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum) + db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \ + VALUES (%s,%s,%s,%s)\ + ON DUPLICATE KEY UPDATE \ + ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl) + self.close_db(db) + def select_by_key_in_auto_bayes_key(self,key,chtxt): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT keychar,chtxt,ratio_rec,ratio_all \ + FROM in_auto_bayes_key \ + WHERE keychar = %s AND chtxt = %s", \ + (key,chtxt)) + dls = [] + if dbexe > 0: + dls = db[1].fetchall() + self.close_db(db) + if len(dls)>0: + return dls[0] + else: + return dls + + def new_auto_timeline_keyword(self): + db = self.connect_db() + try: + db[1].execute('\ + CREATE TABLE auto_timeline_keyword \ + (\ + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\ + chtxt VARCHAR(40),\ + title VARCHAR(100),\ + btime DATETIME,\ + etime DATETIME,\ + UNIQUE uni (chtxt,title,btime,etime)\ + )') + except: + "" + def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""): + db = self.connect_db() + db[1].execute('\ + INSERT IGNORE into auto_timeline_keyword \ + (chtxt,title,btime,etime) \ + values (%s,%s,%s,%s)', \ + (chtxt, title, btime, etime)) + ##db.commit() + self.close_db(db) + def delete_old_auto_timeline_keyword(self, dhour): + db = self.connect_db() + db[1].execute("\ + DELETE FROM auto_timeline_keyword \ + WHERE \ + btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )") + self.close_db(db) + def new_auto_timeline_bayes(self): + db = self.connect_db() + try: + db[1].execute('\ + CREATE TABLE auto_timeline_bayes \ + (\ + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\ + chtxt VARCHAR(40),\ + title VARCHAR(100),\ + btime DATETIME,\ + etime DATETIME,\ + point INT,\ + UNIQUE uni (chtxt,title,btime,etime)\ + )') + except: + "" + def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""): + db = self.connect_db() + db[1].execute('\ + INSERT IGNORE into auto_timeline_bayes \ + (chtxt,title,btime,etime,point) \ + values (%s,%s,%s,%s,%s)', \ + (chtxt, title, btime, etime,point)) + self.close_db(db) + def delete_old_auto_timeline_bayes(self, dhour): + db = self.connect_db() + db[1].execute("\ + DELETE FROM auto_timeline_bayes \ + WHERE \ + btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )") self.close_db(db)