From: gn64_jp Date: Wed, 5 Aug 2009 07:08:47 +0000 (+0000) Subject: implement MySQL using function. X-Git-Url: http://git.osdn.net/view?p=rec10%2Frec10-git.git;a=commitdiff_plain;h=ab3270d5c84d2bd2e067092ed19169e6793e2584 implement MySQL using function. git-svn-id: svn+ssh://svn.sourceforge.jp/svnroot/rec10@89 4e526526-5e11-4fc0-8910-f8fd03428081 --- diff --git a/rec10/trunk/src/dbMySQL.py b/rec10/trunk/src/dbMySQL.py index 6924bc0..6ed3e98 100644 --- a/rec10/trunk/src/dbMySQL.py +++ b/rec10/trunk/src/dbMySQL.py @@ -1,122 +1,126 @@ - #!/usr/bin/python # coding: UTF-8 # Rec10 TS Recording Tools # 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(): dbname="" dbhost="" dbusr="" dbpasswd="" dbport=0 - def __init__(self,dbname,host="localhost",user,passwd,port=3306): - self.dbname = dbpath + def __init__(self,dbname,user,passwd,host="localhost",port=3306): + self.dbname = dbname self.dbhost=host - self.dbusr=usr + self.dbusr=user self.dbpasswd=passwd self.dbport=port - db = self.connect_db(self.dbname,self.dbhost,self.dbport,self.dbusr,self.dbpasswd) 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))') + con = MySQLdb.connect(user= user, passwd = passwd) + cur=con.cursor() + cur.execute('create database '+dbname) + 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))') except: "" - db.commit self.close_db(db) - def connect_db(self,db,host,port,user,passwd): + def connect_db(self): """ dbへの接続 - """ - return MySQLdb.connect(db=self.dbname,host=dbhost,port=dbport,user=self.dbusr,passwd=self.dbpasswd) + con=MySQLdb.connect(db=self.dbname,host=self.dbhost,port=self.dbport,user=self.dbusr,passwd=self.dbpasswd,charset="utf8") + cur=con.cursor() + return [con,cur] def close_db(self,db): - db.close() + db[1].close() + db[0].close() def new_chdata(self): - db = self.connect_db(60) + db = self.connect_db() try: - db.execute('drop table chdata') - db.commit() + db[1].execute('drop table chdata') 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 chdata (bctype TEXT,ontv TEXT,chtxt TEXT,ch TEXT,csch TEXT,station TEXT,station_name TEXT,updatetime DATETIME)') 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() + db = self.connect_db() + db[1].execute('insert into chdata values (?,?,?,?,?,?,?,?)',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime)) 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,)) + db = self.connect_db() + dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ontv = ?",(ontv,)) ret=dbexe.fetchall() self.close_db(db) 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,)) + db = self.connect_db() + dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt = ?",(chtxt,)) ret=dbexe.fetchall() self.close_db(db) 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,)) + db = self.connect_db() + dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE bctype = ?",(bctype,)) ret=dbexe.fetchall() self.close_db(db) 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,)) + db = self.connect_db() + dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = ?",(ch,)) ret=dbexe.fetchall() self.close_db(db) return ret def select_all_chdata(self): - db = self.connect_db(60) - dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ") + db = self.connect_db() + dbexe=db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ") ret=dbexe.fetchall() self.close_db(db) 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)") + db = self.connect_db() + dbexe=db[1].execute("SELECT bctype,chtxt FROM chdata WHERE updatetime < DATE_SUB(now(),INTERVAL "+dhour+" HOUR)") ret=dbexe.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() + db = self.connect_db() + db[1].execute("UPDATE chdata SET updatetime=now() WHERE bctype = ?",(bctype,)) 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() + db = self.connect_db() + db[1].execute("UPDATE chdata SET updatetime=now() WHERE bctype = ? AND chtxt = ?",(bctype,chtxt)) 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() + 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.commit() self.close_db(db) def del_rectime(self,type="", 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() + db=self.connect_db() + db[1].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) + db=self.connect_db() recdata=[] - for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime"): + 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 @@ -137,9 +141,9 @@ class DB_MySQL(): self.close_db(db) return recdata def select_bytime_rectime(self,dhour): - db=self.connect_db(60) + db=self.connect_db() 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 )"): + 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 @@ -160,39 +164,39 @@ class DB_MySQL(): self.close_db(db) 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() + 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.commit() self.close_db(db) def new_tv(self,bctype): - db=self.connect_db(60) + db=self.connect_db() try: - db.execute("delete from tv where bctype = ?", (bctype,)) + db[1].execute("delete from tv where bctype = ?", (bctype,)) except: "" 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 tv (bctype TEXT,channel TEXT NOT NULL,start TEXT,stop TEXT,title TEXT,desc TEXT)') except: "" - db.commit() + #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() + db=self.connect_db() + db[1].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() + db=self.connect_db() + db[1].executemany('insert into tv values (?,?,?,?,?,?)', tvlists) + #db.commit() self.close_db(db) def select_by_title_tv(self,title): - db=self.connect_db(120) + db=self.connect_db() ret=[] - for ch, title, start, stop in db.execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\""): + 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","") @@ -205,34 +209,34 @@ class DB_MySQL(): self.close_db(db) return ret def select_by_time_ngram_tv(self,btime,etime,chtxt): - db=self.connect_db(120) + 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=?" - dbcmd=db.execute(dbexe,(btime,etime,chtxt)) + dbcmd=db[1].execute(dbexe,(btime,etime,chtxt)) retall=dbcmd.fetchall() self.close_db(db) return retall def new_ch(self,bctype): - db=self.connect_db(120) + db=self.connect_db() try: - db.execute("delete from ch where bctype = ?", (bctype,)) + db[1].execute("delete from ch where bctype = ?", (bctype,)) except: "" try: - db.execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT)') + db[1].execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT)') except: "" - db.commit() + #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() + db=self.connect_db() + db[1].execute('insert into ch values (?,?,?)', (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(240) - db.executemany('insert into ch values (?,?,?)', chlists) - db.commit() + db=self.connect_db() + db[1].executemany('insert into ch values (?,?,?)', chlists) + #db.commit() self.close_db(db) diff --git a/rec10/trunk/src/dbSQLite.py b/rec10/trunk/src/dbSQLite.py index 0e8cb25..f8adc02 100644 --- a/rec10/trunk/src/dbSQLite.py +++ b/rec10/trunk/src/dbSQLite.py @@ -5,7 +5,6 @@ import sqlite3 import recdb import os -import time path = str(os.path.dirname(os.path.abspath(__file__))) + "/" dbpath = path + "ch.db" class DB_SQLite(): @@ -207,11 +206,11 @@ class DB_SQLite(): def new_ch(self,bctype): db=self.connect_db(120) try: - db.execute("delete from ch where bctype = ?", (bctype,)) + db.execute("delete from ch where bctype = ?",(bctype,)) except: "" try: - db.execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT)') + db.execute('create table ch (bctype TEXT,channel TEXT NOT NULL,display TEXT,UNIQUE (bctype,channel))') except: "" db.commit() diff --git a/rec10/trunk/src/rec10d.py b/rec10/trunk/src/rec10d.py index 058cbe8..16935c4 100644 --- a/rec10/trunk/src/rec10d.py +++ b/rec10/trunk/src/rec10d.py @@ -5,19 +5,20 @@ import os import timerec import dbSQLite -#import dbMySQL +import dbMySQL import configreader path=str(os.path.dirname(os.path.abspath(__file__)))+"/" global rec10db db=configreader.getpath("db") if db=="MySQL": dbn=configreader.getpath("mysql_dbname") - dbh=configreader.getpath("mysql_dbhost") - dbu=configreader.getpath("") - dbpwd=configreader.getpath("") - dbport=int(configreader.getpath("")) - #rec10db=dbMySQL.DB_MySQL(dbname,host="localhost",user,passwd,port=3306) -rec10db=dbSQLite.DB_SQLite(path+"ch.db") + dbh=configreader.getpath("mysql_host") + dbu=configreader.getpath("mysql_user") + dbpwd=configreader.getpath("mysql_passwd") + dbport=int(configreader.getpath("mysql_port")) + rec10db=dbMySQL.DB_MySQL(dbname=dbn,host=dbh,user=dbu,passwd=dbpwd,port=dbport) +else: + rec10db=dbSQLite.DB_SQLite(path+"ch.db") if __name__ == "__main__": timerec.task() diff --git a/rec10/trunk/src/recdb.py b/rec10/trunk/src/recdb.py index e8d4dd9..657498c 100644 --- a/rec10/trunk/src/recdb.py +++ b/rec10/trunk/src/recdb.py @@ -20,6 +20,7 @@ global REC_KEYWORD_EVERY_SOME_DAYS global REC_FIN_DECODE global REC_FIN_LOCAL global REC_MISS_DECODE +global REC_TS_DECODE_QUE global REC_TS_DECODING global REC_TS_RECORDING REC_RESERVE="res" @@ -35,6 +36,7 @@ REC_KEYWORD_EVERY_SOME_DAYS="keyevery" REC_FIN_DECODE="tsfin" REC_FIN_LOCAL="fin_local" REC_MISS_DECODE="tsmiss" +REC_TS_DECODE_QUE="b25tots" REC_TS_DECODING="tsdecoding" REC_TS_RECORDING="tsrecording" diff --git a/rec10/trunk/src/status.py b/rec10/trunk/src/status.py index 2aff23c..1685bb2 100755 --- a/rec10/trunk/src/status.py +++ b/rec10/trunk/src/status.py @@ -42,4 +42,7 @@ def setData(datastr,tasknum): f=open(path+'status','w') ini.write(f) f.close - +def getB25Decoding(): + return getStatusNum("b252ts") +def setB25Decoding(tasknum): + setData('b252ts',tasknum) diff --git a/rec10/trunk/src/tv2avi.py b/rec10/trunk/src/tv2avi.py index ea39b0a..6cab387 100644 --- a/rec10/trunk/src/tv2avi.py +++ b/rec10/trunk/src/tv2avi.py @@ -22,7 +22,61 @@ Bitrate_HD="2000" Bitrate_FHD="2500" Bitrate_SD="1250" Bitrate_WQVGA="200" - +def timetv2b25(pout, chtxt, btime, etime,opt): + bt = datetime.datetime.strptime(btime, "%Y-%m-%d %H:%M:%S") + et = datetime.datetime.strptime(etime, "%Y-%m-%d %H:%M:%S") + extt = os.path.splitext(pout) + tsout = extt[0] + #tsout=pout#### + #print "4"+tsout + tnow = datetime.datetime.now() + wt = bt-tnow + waitt = wt.seconds-5 + time.sleep(waitt) + tnow = datetime.datetime.now() + dt = et-tnow + rectime = dt.seconds-10 + rectime = str(rectime) + tv2ts.tv2b25ts(tsout+".ts.b25", chdb.chtxtsearch(chtxt)['ch'], rectime) +def b252ts(pout,chtxt,btime,etime,opt): + tv2ts.b252ts(pout+".ts", chdb.chtxtsearch(chtxt)['ch'], chdb.chtxtsearch(chtxt)['csch']) + aviin = tsout + ".ts" + dualaudio = 0 + pentaaudio = 0 + if re.search("5",opt): + pentaaudio = 1 + if re.search("d",opt): + dualaudio = 1 + if re.search("\[二\]", pout): + dualaudio = 1 + elif re.search('（二）', pout): + dualaudio = 1 + elif re.search('\(二\)', pout): + dualaudio = 1 + if opt=="": + opts="" + if chdb.chtxtsearch(chtxt)['bctype'] == 'cs': + opts=opts+"S2" + if chtxt == "disch": + "" + elif chtxt == "hisch": + "" + else: + opts=opts+"a" + else: + opts=opts+"Ha2" + else: + opts=opt + if re.search("x",opt): + makexvid = 0 + else: + makexvid = 1 + if dualaudio == 1: + dualaudiots2avi(aviin, tsout + "sa.avi") + aviin = tsout + "sa.avi" + if pentaaudio ==1: + dualaudiots2avi(aviin, tsout + "sa.avi") + aviin = tsout + "sa.avi" def timetv2avi(pout, chtxt, btime, etime,opt): bt = datetime.datetime.strptime(btime, "%Y-%m-%d %H:%M:%S") et = datetime.datetime.strptime(etime, "%Y-%m-%d %H:%M:%S") diff --git a/rec10/trunk/src/xml2db.py b/rec10/trunk/src/xml2db.py index 036234d..b1be2c1 100644 --- a/rec10/trunk/src/xml2db.py +++ b/rec10/trunk/src/xml2db.py @@ -104,7 +104,7 @@ def char_data(data): data=data.replace("\"","") data=data.replace("\'","") try: - datat=zenhan.toHankaku(data) + datat=zenhan.toHankaku_ABC123(data) except: datat=data data=datat.encode('utf-8') diff --git a/rec10/trunk/src/zenhan.py b/rec10/trunk/src/zenhan.py index 9c9c26e..3e7ef12 100644 --- a/rec10/trunk/src/zenhan.py +++ b/rec10/trunk/src/zenhan.py @@ -11,6 +11,8 @@ z_ascii=u"ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷ h_ascii=u"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz !\"#$%&'()*+,-./:;<=>?@[\]^_`{|}~ " z_number=u"０１２３４５６７８９" h_number=u"0123456789" +z_alphabet=u"ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ　．" +h_alphabet=u"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ." def toHankaku(str): retstr=u"" for s in str: @@ -22,5 +24,15 @@ def toHankaku(str): s=h_number[i] retstr=retstr+s return retstr - +def toHankaku_ABC123(str): + retstr=u"" + for s in str: + i=z_alphabet.find(s) + if (i != -1): + s=h_alphabet[i] + i=z_number.find(s) + if (i != -1): + s=h_number[i] + retstr=retstr+s + return retstr