import getopt
import os.path
+class StoryDB(object):
+ "Story database"
+ DB_FILE = "database/story_dat"
+ def __init__(self):
+ self._con = None
+ self._cur = None
+
+ def find_author(self, sid, writers):
+ cur = self._con.cursor()
+ author = ""
+ cur.execute("""select topic from topics where sid=?""", (sid,))
+ for row in cur:
+ if row[0] in writers:
+ author = row[0]
+ cur.close()
+ return author
+
+ def query(self, topic1, topic2, period_begin, period_end):
+ if topic2:
+ cmd = """select sid, title, date from stories
+ where date >= ? and date < ? and sid in (
+ select sid from topics where topic == ? and sid in (
+ select sid from topics where topic == ?
+ )) order by date"""
+ param = (period_begin, period_end, topic1, topic2)
+ elif topic1:
+ cmd = """select sid, title, date from stories
+ where date >= ? and date < ? and sid in (
+ select sid from topics where topic == ?
+ ) order by date"""
+ param = (period_begin, period_end, topic1)
+ else:
+ cmd = """select sid, title, date from stories
+ where date >= ? and date < ? and sid in (
+ select sid from topics) order by date"""
+ param = (period_begin, period_end)
+
+ self._cur.execute(cmd, param)
+ return self._cur
+
+ def result(self):
+ return self._cur
+
+ def __enter__(self):
+ self._con = sqlite3.connect(self.DB_FILE)
+ self._cur = self._con.cursor()
+
+ def __exit__(self, exc_type, exc_value, traceback):
+ if exc_type:
+ self._con = None
+ self._cur = None
+ return False
+ self._cur.close()
+ self._con.close()
+ self._con = None
+ self._cur = None
+ return True
+
+
+class GALogDB(object):
+ "Google Analytics logdata database"
+ DB_FILE = "database/analytics_dat"
+
+ def get_PV(self, sid):
+ con = sqlite3.connect(self.DB_FILE)
+ cur = con.cursor()
+ param = "/magazine/" + sid + "%"
+ cur.execute("""select * from logdata where url like ?""", (param,))
+ sum = 0
+ for row in cur:
+ sum = sum + row[2]
+ cur.close()
+ con.close()
+ return sum
+
+
class HatebuDB(object):
"Hatena Bookmark Counts Database"
DB_FILE = "database/hatebu_dat"
con.close()
return count
+# for database initialize
def main():
usage = "%s [-c]" % sys.argv[0]
(optlist, args) = getopt.getopt(sys.argv[1:], "c")
import databases
-LOG_DB = "database/analytics_dat"
+#LOG_DB = "database/analytics_dat"
WRITERS = (u"松島浩道", u"末岡洋子", u"二瓶亮史", u"大津真", u"林利明", u"Infostand")
class StatApp(BigBlack):
self.debugger = Debugger(self)
def root(self):
+ "/ handler"
topic1 = self.cgi.getfirst("topic1", "")
topic2 = self.cgi.getfirst("topic2", "")
self.view.render("root.html", dict(title="newsclip login",
topic2=topic2,
op=""))
- def get_pvs_info(self, sid):
- con = sqlite3.connect(LOG_DB)
- cur = con.cursor()
- param = "/magazine/" + sid + "%"
- cur.execute("""select * from logdata where url like ?""", (param,))
- sum = 0
- for row in cur:
- sum = sum + row[2]
- cur.close()
- con.close()
- return sum
-
def h_query(self):
- topic1 = self.cgi.getfirst("topic1", "")
- topic2 = self.cgi.getfirst("topic2", "")
+ "/query/ handler"
+ # get parameters
+ topic1 = self.cgi.getfirst("topic1", "").decode("utf-8")
+ topic2 = self.cgi.getfirst("topic2", "").decode("utf-8")
year = self.cgi.getfirst("start_y", "")
month = self.cgi.getfirst("start_m", "")
+ # set time period
start_year = int(year) if year else 2010
- start_month = int(month) if year else 1
+ start_month = int(month) if month else 1
if start_month == 1:
start_month = 12
start_year -= 1
if end_month > 12:
end_year += 1
end_month = 1
-
- DATABASE = "database/story_dat"
-
range_begin = datetime.datetime(start_year, start_month, 26)
- range_end = datetime.datetime(end_year, end_month, 26)
-
begin_t = time.mktime(range_begin.timetuple())
+ range_end = datetime.datetime(end_year, end_month, 26)
end_t = time.mktime(range_end.timetuple())
- t1 = topic1.decode("utf-8")
- t2 = topic2.decode("utf-8")
-
- if topic2:
- cmd = """select sid, title, date from stories where date >= ? and date < ? and sid in (
- select sid from topics where topic == ? and sid in (
- select sid from topics where topic == ? )) order by date
- """
- con = sqlite3.connect(DATABASE)
- cur = con.cursor()
- cur.execute(cmd, (begin_t, end_t, t1, t2))
- elif topic1:
- cmd = """select sid, title, date from stories where date >= ? and date < ? and sid in (
- select sid from topics where topic == ? ) order by date
- """
- con = sqlite3.connect(DATABASE)
- cur = con.cursor()
- cur.execute(cmd, (begin_t, end_t, t1))
- else:
- cmd = """select sid, title, date from stories where date >= ? and date < ? and sid in (
- select sid from topics) order by date
- """
- con = sqlite3.connect(DATABASE)
- cur = con.cursor()
- cur.execute(cmd, (begin_t, end_t))
+ # initiate DBs
hatebu_db = databases.HatebuDB()
-
+ ga_db = databases.GALogDB()
+ s_db = databases.StoryDB()
index = 1
result = []
- for row in cur:
- sid = row[0]
- title = row[1]
- tm = row[2]
- dt = datetime.datetime.fromtimestamp(tm)
- localdt = dt + datetime.timedelta(hours=9)
-
- # get topics
- cur2 = con.cursor()
- author = ""
- cur2.execute("""select topic from topics where sid=?""", (sid,))
- for row2 in cur2:
- if row2[0] in WRITERS:
- author = row2[0]
-
- pv = self.get_pvs_info(sid)
- hatebu_count = hatebu_db.get_count(sid)
- d = dict(index=index,
- date=localdt.strftime("%y-%m-%d"),
- time=localdt.strftime("%H:%M:%S"),
- url="http://sourceforge.jp/magazine/" + sid,
- pageviews=pv,
- author=author,
- title=title,
- hatebu_count=hatebu_count)
- result.append(d)
- index += 1
-
- cur.close()
- con.close()
-
+
+ # scan DB
+ with s_db:
+ for row in s_db.query(topic1, topic2, begin_t, end_t):
+ sid = row[0]
+ title = row[1]
+ tm = row[2]
+ dt = datetime.datetime.fromtimestamp(tm)
+ localdt = dt + datetime.timedelta(hours=9)
+
+ author = s_db.find_author(sid, WRITERS)
+ pv = ga_db.get_PV(sid)
+ hatebu_count = hatebu_db.get_count(sid)
+
+ d = dict(index=index,
+ date=localdt.strftime("%y-%m-%d"),
+ time=localdt.strftime("%H:%M:%S"),
+ url="http://sourceforge.jp/magazine/" + sid,
+ pageviews=pv,
+ author=author,
+ title=title,
+ hatebu_count=hatebu_count)
+ result.append(d)
+ index += 1
+
+ # render view
self.view.render("root.html", dict(title="newsclip login",
topic1=topic1,
topic2=topic2,
op="query",
result=result))
-
if __name__ == '__main__':
app = StatApp()
app.run()
OSDN Git Service
