# coding: UTF-8
# Rec10 TS Recording Tools
# Copyright (C) 2009 Yukikaze
-import recdb
-import os
import MySQLdb
-import dbSQLite
-path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
-
-class DB_MySQL():
- dbname=""
- dbhost=""
- dbusr=""
- dbpasswd=""
- dbport=0
- def __init__(self,dbname,user,passwd,host="localhost",port=3306):
+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
+ 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)
+ 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[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))')
+ db[1].execute('\
+ CREATE TABLE main_timetable \
+ (\
+ 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)\
+ )')
except:
""
self.close_db(db)
+ self.new_epg_timeline("")
+ self.new_epg_ch("")
+ self.new_in_timeline_log()
+ self.new_in_auto_bayes_key()
+ self.new_in_auto_jbk_key()
+ self.new_auto_proc()
def connect_db(self):
"""
dbへの接続
"""
- con=MySQLdb.connect(db=self.dbname,host=self.dbhost,port=self.dbport,user=self.dbusr,passwd=self.dbpasswd,charset="utf8")
- cur=con.cursor()
+ 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):
+ return [con, cur]
+ def close_db(self, db):
db[1].close()
db[0].close()
- def new_chdata(self):
+ def new_epg_ch(self):
db = self.connect_db()
try:
- db[1].execute('drop table chdata')
+ db[1].execute('drop table epg_ch')
except:
""
try:
- db[1].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(15),\
+ ch VARCHAR(20),\
+ csch VARCHAR(20),\
+ chname VARCHAR(40),\
+ updatetime DATETIME,\
+ status TINYINT\
+ )')
except:
""
self.close_db(db)
- def add_chdata(self,bctype, ontv, chtxt, ch, csch, station, station_name,updatetime):
+ def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
db = self.connect_db()
- #bctype=bctype.encode('utf-8')
- #ontv=ontv.encode('utf-8')
- #chtxt=chtxt.encode('utf-8')
- #ch=ch.encode('utf-8')
- #csch=csch.encode('utf-8')
- #station=station.encode('utf-8')
- #station_name=station_name.encode('utf-8')
- #updatetime=updatetime.encode('utf-8')
- db[1].execute('insert into chdata values (%s,%s,%s,%s,%s,%s,%s,%s)',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime))
+ 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):
+ def select_by_ontv_epg_ch(self, ontv):
db = self.connect_db()
- dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ontv = %s",(ontv,))
- ret=dbexe.fetchall()
+ 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):
+ def select_by_chtxt_epg_ch(self, chtxt):
db = self.connect_db()
- dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt = %s",(chtxt,))
- ret=dbexe.fetchall()
+ 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):
+ def select_by_bctype_epg_ch(self, bctype):
db = self.connect_db()
- dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE bctype = %s",(bctype,))
- ret=dbexe.fetchall()
+ 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:
+ #print 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):
+ def select_by_ch_epg_ch(self, ch):
db = self.connect_db()
- dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = %s",(ch,))
- ret=dbexe.fetchall()
+ 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):
+ def select_all_epg_ch(self):
db = self.connect_db()
- dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ")
- ret=dbexe.fetchall()
+ 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):
+ def select_get_update_epg_ch(self, dhour):
db = self.connect_db()
- dbexe=db[1].execute("SELECT bctype,chtxt FROM chdata WHERE updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR)")
- ret=dbexe.fetchall()
+ 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 = []
+ #print dbexe
+ if dbexe > 0:
+ ret = db[1].fetchall()
self.close_db(db)
return ret
- def update_by_bctype_chdata(self,bctype):
+ 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 chdata SET updatetime=now() WHERE bctype = %s",(bctype,))
+ 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 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_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_in_timeline_log(self, title="", chtxt="", btime=""):
+ """
+
+ """
+ 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 update_by_bctype_and_chtxt_chdata(self,bctype,chtxt):
+ def new_in_timeline_log(self):
db = self.connect_db()
- db[1].execute("UPDATE chdata SET updatetime=now() WHERE bctype = %s AND chtxt = %s",(bctype,chtxt))
+ 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_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
+ def add_main_timetable(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
db = self.connect_db()
- 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))
+ db[1].execute('\
+ INSERT IGNORE into main_timetable \
+ (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_rectime(self,type="", title="", chtxt="", btime=""):
+ def del_main_timetable(self, type="", title="", chtxt="", btime=""):
"""
"""
- db=self.connect_db()
- db[1].execute("delete from rectime where type = %s AND title = %s AND chtxt = %s AND btime = %s", (type, title, chtxt, btime))
+ db = self.connect_db()
+ db[1].execute("\
+ DELETE FROM main_timetable \
+ 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_rectime(self):
- db=self.connect_db()
- recdata=[]
- for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db[1].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)
+ def select_all_main_timetable(self):
+ db = self.connect_db()
+ recdata = []
+ dbr = db[1].execute("\
+ SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
+ FROM main_timetable")
+ 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.encode('utf-8')
+ 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,dminutes):
- db=self.connect_db()
- recdatum=[]
- 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 )"):
- 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_main_timetable(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 main_timetable \
+ WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
+ DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
+ dbl = db[1].fetchall()
self.close_db(db)
+ #print 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.encode('utf-8')
+ 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 == 'key':
+ ret['deltatime'] = deltatime
+ elif typet == 'keyevery':
+ ret['deltatime'] = deltatime
+ ret['deltaday'] = deltaday
+ recdatum.append(ret)
return recdatum
- def delete_old_rectime(self,dhour):
- db=self.connect_db()
- 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))
+ def delete_old_main_timetable(self, dhour):
+ db = self.connect_db()
+ db[1].execute("\
+ DELETE FROM main_timetable \
+ WHERE \
+ btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
#db.commit()
self.close_db(db)
- def new_tv(self,bctype):
- db=self.connect_db()
+ def new_in_auto_jbk_key(self):
+ db = self.connect_db()
try:
- db[1].execute("delete from tv where bctype = %s", (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[1].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 status VALUE (0,0,0,0)")
+ except:
+ ""
+
+ 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
+ self.close_db(db)
+ 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)
+ 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[1].execute("\
+ DELETE FROM epg_timeline \
+ WHERE bctype = %s", \
+ (bctype,))
+ except:
+ ""
+ try:
+ 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_tv(self,bctype, channel, start, stop, title, desc):
- db=self.connect_db()
- db[1].execute('insert into tv values (%s,%s,%s,%s,%s,%s)', (bctype, channel, start, stop, title, desc))
+ 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_tv(self,tvlists):
+ def add_multi_epg_timeline(self, tvlists):
"""
- tvlists is (bctype,channel,start,stop,title,desc) lists.
+ tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
"""
- db=self.connect_db()
- db[1].executemany('insert into tv values (%s,%s,%s,%s,%s,%s)', tvlists)
+ 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)
#db.commit()
self.close_db(db)
- def select_by_title_tv(self,title):
- db=self.connect_db()
- ret=[]
- for ch, title, start, stop in db[1].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)
+ def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
+ db = self.connect_db()
+ dbexe = "\
+ SELECT \
+ chdata.chtxt,title,start,stop,exp,longexp,category \
+ FROM epg_timeline \
+ INNER JOIN chdata \
+ WHERE chdata.ontv=epg_timeline.channel \
+ AND \
+ start >= %s \
+ AND \
+ start <= %s \
+ AND \
+ chdata.chtxt=%s"
+ dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
+ retall = []
+ if dbcmd > 0:
+ retall = db[1].fetchall()
self.close_db(db)
- return ret
- def select_by_time_ngram_tv(self,btime,etime,chtxt):
- db=self.connect_db()
- 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"
- dbcmd=db[1].execute(dbexe,(btime,etime,chtxt))
- retall=dbcmd.fetchall()
+ return retall
+ def select_by_time_auto_suggest_epg_timeline(self,keyword,btime,etime):
+ db = self.connect_db()
+ dbexe = "\
+ SELECT \
+ chdata.chtxt,title,start,stop,exp,longexp,category \
+ FROM epg_timeline \
+ INNER JOIN chdata \
+ WHERE chdata.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_ch(self,bctype):
- db=self.connect_db()
+ def new_epg_ch(self, bctype):
+ db = self.connect_db()
try:
- db[1].execute("delete from ch where bctype = %s", (bctype,))
+ db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,))
except:
""
try:
- db[1].execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT)')
+ 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_ch(self,bctype, channel, display):
- db=self.connect_db()
- db[1].execute('insert into ch values (%s,%s,%s)', (bctype, channel, display))
+ #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:
+ ""
self.close_db(db)
- def add_multi_ch(self,chlists):
+
+ 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_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()
- db[1].executemany('insert into ch values (%s,%s,%s)', 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
\ No newline at end of file
OSDN Git Service
