def add_story(self, story): con, cur = dbpool.get_connection() result = None try: insert_sql = "insert into story (source_id, source_name, source_url, path, times_updated, s_id, s_date, s_title, s_summary, s_author, s_link, s_tags, s_media_content, created, updated, fuzzy_summary) " \ "values (%(_source_id)s, %(_source_name)s, %(_source_url)s, %(_path)s, 0, %(_id)s, %(_published_parsed)s, %(title)s, %(_summary)s, %(_author)s, %(link)s, %(_tags)s, %(_media_content)s, now(), now(), %(_fuzzy_summary)s) " \ "returning id" check_sql = "select count(*) num_stories from story where s_id=%(_id)s or (s_title=%(title)s and s_title<>'')" cur.execute(check_sql, story) if cur.rowcount > 0: match = True if cur.fetchone()['num_stories'] > 0 else False if not match: cur.execute(insert_sql, story) if cur.rowcount > 0: result = cur.fetchone()['id'] else: pass #print 'id:%s, title:%s, source:%s'%(story['_id'], story['title'], story['_source_name']) else: pass # bummer #print 'id:%s, title:%s, source:%s'%(story['_id'], story['title'], story['_source_name']) except Exception, e: logging.error('add_story: ' + e.message) pass
def get_active_sources(self): con, cur = dbpool.get_connection() result = None try: sql = "select id, name, url, type from source where active=true order by id" cur.execute(sql, {}) result = cur.fetchall() except Exception, e: logging.error('get_active_sources: ' + e.message)
def get_topics(self): con, cur = dbpool.get_connection() result = None try: sql = "select id from topic where created > now() - '24 hours'::interval order by id desc" cur.execute(sql, {}) result = cur.fetchall() except Exception, e: logging.error('get_topics: ' + e.message)
def get_topic_story_by_topic(self, id): con, cur = dbpool.get_connection() result = None try: sql = "select distinct story_id from topic_story where topic_id = %(id)s order by story_id desc" cur.execute(sql, {'id': id}) result = cur.fetchall() except Exception, e: logging.error('get_active_sources: ' + e.message)
def add_topic_story(self, topic_id, id, score): con, cur = dbpool.get_connection() result = None try: sql = "insert into topic_story (topic_id, story_id, created, score) values (%(topic_id)s, %(story_id)s, now(), %(score)s) returning id" cur.execute(sql, {'topic_id': topic_id, 'story_id': id, 'score': score}) if cur.rowcount > 0: result = cur.fetchone()['id'] except Exception, e: logging.error('get_active_sources: ' + e.message)
def add_topic(self, name): con, cur = dbpool.get_connection() result = None try: sql = "insert into topic (name, created, updated) values (%(name)s, now(), now()) returning id" cur.execute(sql, {'name': name}) if cur.rowcount > 0: result = cur.fetchone()['id'] except Exception, e: logging.error('get_active_sources: ' + e.message)
def get_topic_story(self, id): con, cur = dbpool.get_connection() result = None try: sql = "select topic_id from topic_story where story_id = %(id)s" cur.execute(sql, {'id': id}) if cur.rowcount > 0: result = cur.fetchone()['topic_id'] except Exception, e: logging.error('get_active_sources: ' + e.message)
def get_story_by_path(self, path): con, cur = dbpool.get_connection() result = None try: sql = "select id, source_name source, s_link, s_date date, s_title title, s_summary summary, s_author author, " \ "'/story/'||path link, s_media_content media_content " \ "from story where path = %(path)s" cur.execute(sql, {'path':path}) result = cur.fetchone() except Exception, e: logging.error('get_stories: ' + e.message)
def get_stories(self): con, cur = dbpool.get_connection() result = None try: sql = "select id, source_name source, s_date date, s_title title, s_summary summary, s_author author, " \ "'/story/'||path link, s_media_content media_content " \ "from story order by id desc limit 50;" cur.execute(sql, {}) result = cur.fetchall() except Exception, e: logging.error('get_stories: ' + e.message)
def get_story_by_path(self, path): con, cur = dbpool.get_connection() result = None try: sql = "select id, source_name source, s_link, s_date date, s_title title, s_summary summary, s_author author, " \ "'/story/'||path link, s_media_content media_content " \ "from story where path = %(path)s" cur.execute(sql, {'path': path}) result = cur.fetchone() except Exception, e: logging.error('get_stories: ' + e.message)
def get_fuzzy_summaries(self, except_id=None): con, cur = dbpool.get_connection() result = None try: sql_id = "select id, s_title, fuzzy_summary from story where s_date > now() - '24 hours'::interval and id != %(id)s" sql_no_id = "select id, s_title, fuzzy_summary from story where s_date > now() - '24 hours'::interval " if except_id is not None: cur.execute(sql_id, {'id':except_id}) else: cur.execute(sql_no_id, {}) result = cur.fetchall() except Exception, e: logging.error('get_fuzzy_summaries: ' + e.message)
def get_fuzzy_summaries(self, except_id=None): con, cur = dbpool.get_connection() result = None try: sql_id = "select id, s_title, fuzzy_summary from story where s_date > now() - '24 hours'::interval and id != %(id)s" sql_no_id = "select id, s_title, fuzzy_summary from story where s_date > now() - '24 hours'::interval " if except_id is not None: cur.execute(sql_id, {'id': except_id}) else: cur.execute(sql_no_id, {}) result = cur.fetchall() except Exception, e: logging.error('get_fuzzy_summaries: ' + e.message)
def add_topic_story(self, topic_id, id, score): con, cur = dbpool.get_connection() result = None try: sql = "insert into topic_story (topic_id, story_id, created, score) values (%(topic_id)s, %(story_id)s, now(), %(score)s) returning id" cur.execute(sql, { 'topic_id': topic_id, 'story_id': id, 'score': score }) if cur.rowcount > 0: result = cur.fetchone()['id'] except Exception, e: logging.error('get_active_sources: ' + e.message)