-
#!/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
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
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","")
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)
OSDN Git Service
