# Copyright (C) 2009 Yukikaze
import recdb
import os
-import time
import MySQLdb
import dbSQLite
-path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
-class DB_MySQL():
+class DB_MySQL:
dbname=""
dbhost=""
dbusr=""
try:
con = MySQLdb.connect(user= user, passwd = passwd)
cur=con.cursor()
- cur.execute('create database '+dbname)
+ 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 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))')
except:
""
+ self.new_tv("")
+ self.new_ch("")
self.close_db(db)
def connect_db(self):
"""
"""
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()
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 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)')
except:
""
self.close_db(db)
def add_chdata(self,bctype, ontv, chtxt, ch, csch, station, station_name,updatetime):
db = self.connect_db()
- db[1].execute('insert into chdata values (?,?,?,?,?,?,?,?)',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime))
+ 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"))
self.close_db(db)
def select_by_ontv_chdata(self,ontv):
db = self.connect_db()
- dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ontv = ?",(ontv,))
- ret=dbexe.fetchall()
+ dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata 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()
- dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt = ?",(chtxt,))
- ret=dbexe.fetchall()
+ dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata 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()
- dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE bctype = ?",(bctype,))
- ret=dbexe.fetchall()
+ dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime,status FROM chdata 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")
+ ret.append(r)
return ret
def select_by_ch_chdata(self,ch):
db = self.connect_db()
- dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = ?",(ch,))
- ret=dbexe.fetchall()
+ dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata 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()
dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ")
- ret=dbexe.fetchall()
+ 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()
- 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 chdata WHERE ( updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR) AND status = 0 ) 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):
db = self.connect_db()
- db[1].execute("UPDATE chdata SET updatetime=now() WHERE bctype = ?",(bctype,))
+ db[1].execute("UPDATE chdata SET updatetime=now() , status = 1 WHERE bctype = %s",(bctype,))
self.close_db(db)
def update_by_bctype_and_chtxt_chdata(self,bctype,chtxt):
db = self.connect_db()
- db[1].execute("UPDATE chdata SET updatetime=now() WHERE bctype = ? AND chtxt = ?",(bctype,chtxt))
+ db[1].execute("UPDATE chdata SET updatetime=now() , status = 1WHERE bctype = %s AND chtxt = %s",(bctype,chtxt))
+ self.close_db(db)
+ def update_status_by_bctype_chdata(self,bctype,status):
+ db = self.connect_db()
+ db[1].execute("UPDATE chdata SET status=%s WHERE bctype = %s",(status,bctype))
self.close_db(db)
def add_rectime(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 (?,?,?,?,?,?,?,?)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
+ 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))
##db.commit()
self.close_db(db)
def del_rectime(self,type="", title="", chtxt="", btime=""):
"""
db=self.connect_db()
- db[1].execute("delete from rectime where type = ? AND title = ? AND chtxt = ? AND btime = ?", (type, title, chtxt, btime))
+ db[1].execute("delete from rectime 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)
+ dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime")
+ 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,dhour):
+ 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 "+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)
+ #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 rectime 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 = ? OR type = ? ) AND btime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR )", (recdb.REC_MISS_ENCODE, recdb.REC_KEYWORD_EVERY_SOME_DAYS))
+ 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))
#db.commit()
self.close_db(db)
def new_tv(self,bctype):
db=self.connect_db()
try:
- db[1].execute("delete from tv where bctype = ?", (bctype,))
+ db[1].execute("delete from tv where bctype = %s", (bctype,))
except:
""
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 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))")
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 (?,?,?,?,?,?)', (bctype, channel, start, stop, title, desc))
+ db[1].execute('INSERT IGNORE INTO tv VALUES (%s,%s,%s,%s,%s,%s)', (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()
- db[1].executemany('insert into tv values (?,?,?,?,?,?)', tvlists)
+ db[1].executemany('INSERT IGNORE INTO tv (bctype,channel,start,stop,title,exp) values(%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)
- 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 >= ? AND start <= ? AND chdata.chtxt=?"
+ 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()
+ retall=[]
+ if dbcmd>0:
+ retall=db[1].fetchall()
self.close_db(db)
return retall
def new_ch(self,bctype):
db=self.connect_db()
try:
- db[1].execute("delete from ch where bctype = ?", (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 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 (?,?,?)', (bctype, channel, display))
+ db[1].execute('INSERT IGNORE INTO 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 into ch values (?,?,?)', chlists)
- #db.commit()
+ db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
+ self.close_db(db)
+ def import_from_SQLite(self,dbpath):
+ dbb=dbSQLite.DB_SQLite(dbpath)
+ dbl=dbb.select_all_rectime()
+ for db in dbl:
+ self.add_rectime(db['type'],db['chtxt'],db['title'],db['btime'],db['etime'],db['deltatime'],db['deltaday'],db['opt'])
+
+""" def select_by_title_tv(self,title):
+ db=self.connect_db()
+ ret=[]
+ dbr=db[1].execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\"")
+ dbl=db[1].fetchall()
self.close_db(db)
+ if dbr>0:
+ for ch, title, start, stop in dbl:
+ 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)
+ return ret"""
\ No newline at end of file
OSDN Git Service
