OSDN Git Service

(root) / rec10 / rec10-git.git / blobdiff
? search:
summary | shortlog | log | commit | commitdiff | tree
raw | inline | side by side
implement mysql function.(RC1)
[rec10/rec10-git.git] / rec10 / trunk / src / dbMySQL.py
diff --git a/rec10/trunk/src/dbMySQL.py b/rec10/trunk/src/dbMySQL.py
index 3d859a9..4d3a450 100644 (file)
--- a/rec10/trunk/src/dbMySQL.py
+++ b/rec10/trunk/src/dbMySQL.py
@@ -6,7 +6,6 @@ import recdb
 import os
 import MySQLdb
 import dbSQLite
-path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
 
 class DB_MySQL():
     dbname=""
@@ -30,7 +29,7 @@ class DB_MySQL():
             ""
         db = self.connect_db()
         try:
-            db[1].execute('CREATE TABLE rectime (id INTEGER PRIMARY KEY,type VARCHAR(10),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))')
+            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("")
@@ -54,7 +53,7 @@ class DB_MySQL():
         except:
             ""
         try:
-            db[1].execute('create table chdata (bctype VARCHAR(15),ontv VARCHAR(30),chtxt VARCHAR(15),ch VARCHAR(10),csch VARCHAR(10),station VARCHAR(20),station_name VARCHAR(30),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)')
         except:
             ""
         self.close_db(db)
@@ -121,7 +120,7 @@ class DB_MySQL():
         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 (%s,%s,%s,%s,%s,%s,%s,%s)', (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=""):
@@ -136,15 +135,21 @@ class DB_MySQL():
         db=self.connect_db()
         recdata=[]
         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 db[1].fetchone():
+            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:
@@ -160,13 +165,19 @@ class DB_MySQL():
     def select_bytime_rectime(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 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 db[1].fetchone():
+            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
@@ -180,7 +191,6 @@ class DB_MySQL():
                     ret['deltatime']=deltatime
                     ret['deltaday']=deltaday
                 recdatum.append(ret)
-        self.close_db(db)
         return recdatum
     def delete_old_rectime(self,dhour):
         db=self.connect_db()
@@ -194,7 +204,7 @@ class DB_MySQL():
         except:
             ""
         try:
-            db[1].execute("create table tv (bctype VARCHAR(20),channel VARCHAR(20) NOT NULL,start VARCHAR(20),stop  VARCHAR(20),title VARCHAR(100),exp VARCHAR(200),UNIQUE uni(bctype,channel,start,stop,title))")
+            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()
@@ -216,8 +226,10 @@ class DB_MySQL():
         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 db[1].fetchone():
+            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","")
@@ -227,7 +239,6 @@ class DB_MySQL():
                 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()
@@ -245,7 +256,7 @@ class DB_MySQL():
         except:
             ""
         try:
-            db[1].execute('create table ch (bctype VARCHAR(20),channel VARCHAR(20) NOT NULL,display VARCHAR(100),UNIQUE uni(bctype,channel))')
+            db[1].execute('create table ch (bctype VARCHAR(20),channel VARCHAR(20) NOT NULL,display VARCHAR(100),UNIQUE unich(bctype,channel))')
         except:
             ""
         #db.commit()
@@ -262,3 +273,9 @@ class DB_MySQL():
         db=self.connect_db()
         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'])
+        
\ No newline at end of file
rec10 git
About OSDN
Find Software
Develop Software
Help