def update(self, id, item): update_sql = "update hupu_post set view_count=%s,reply_count=%s where id=%s" % ( item.get("view_count"), item.get("reply_count"), id) with pool.get_db_connect() as db: db.cursor.execute(update_sql) db.conn.commit()
def update_reply(self, id, like_count): update_sql = "update hupu_post_reply set like_count=%s where id=%s" % ( like_count, id) with pool.get_db_connect() as db: db.cursor.execute(update_sql) db.conn.commit()
def getByPostId(self, post_id): select_sql = "select id,view_count,reply_count,content_is_set from hupu_post where hupu_post_id=%s" % post_id with pool.get_db_connect() as db: db.cursor.execute(select_sql) results = db.cursor.fetchone() return results
def get_reply_by_id(self, reply_id): select_sql = "select id,like_count from hupu_post_reply where hupu_reply_id=%s" % reply_id with pool.get_db_connect() as db: db.cursor.execute(select_sql) results = db.cursor.fetchone() return results
def insertOrUpdate(self, item): try: post_time_str = item.get("post_time") post_time_sec = time.mktime( time.strptime(post_time_str, "%Y-%m-%d")) post_time_ms = int(post_time_sec) * 1000 except: raise DropItem("error date") post_id = item.get("id") old_old = self.getByPostId(post_id) if old_old is None: insert_sql = "insert into hupu_post(hupu_post_id,title, author, url, post_time, view_count, reply_count, gmt_created)" \ " values (%s,'%s','%s','%s',%s,%s,%s,%s)" \ % (item.get("id"), item.get("title"), item.get("author"), item.get("url"), post_time_ms, item.get("view_count"), item.get("reply_count"), int(time.time()) * 1000) with pool.get_db_connect() as db: db.cursor.execute(insert_sql) db.conn.commit() elif str(old_old[1]) == item.get("view_count") and str( old_old[2]) == item.get("reply_count"): print("hupuid:%s post nochange..." % old_old[0]) else: print("update hupu......old viewcount=%s,now:%s" % (old_old[1], item.get("view_count"))) self.update(old_old[0], item)
def update_content(self, hupu_post_id, content, post_time_str): post_time_sec = time.mktime(time.strptime(post_time_str, "%Y-%m-%d %H:%M")) post_time_ms = int(post_time_sec) * 1000 update_sql = "update hupu_post set post_time=%s,content='%s',content_is_set=1 where hupu_post_id=%s" % ( post_time_ms, pymysql.escape_string(content), hupu_post_id) try: with pool.get_db_connect() as db: db.cursor.execute(update_sql) db.conn.commit() except: print("error content:" + content)
def getByPostId(id): select_sql = "select id from hupu_post where hupu_post_id=%s" % id with pool.get_db_connect() as db: try: db.cursor.execute(select_sql) results = db.cursor.fetchone() return results except: print("Error: unable to fecth data") return None
def update_content(self, hupu_post_id, content, post_time_str): post_time_sec = time.mktime( time.strptime(post_time_str, "%Y-%m-%d %H:%M")) post_time_ms = int(post_time_sec) * 1000 update_sql = "update hupu_post set post_time=%s,content='%s',content_is_set=1 where hupu_post_id=%s" % ( post_time_ms, pymysql.escape_string(content), hupu_post_id) try: with pool.get_db_connect() as db: db.cursor.execute(update_sql) db.conn.commit() except: print("error content:" + content)
def insert_reply(self, item): reply_time_str = item.get("reply_time") reply_time_sec = time.mktime(time.strptime(reply_time_str, "%Y-%m-%d %H:%M")) reply_time_ms = int(reply_time_sec) * 1000 insert_sql = "insert into hupu_post_reply(hupu_reply_id,author,hupu_post_id,reply_time,like_count,content,floor_num, gmt_created)" \ " values (%s,'%s',%s,%s,%s,'%s',%s,%s)" \ % (item.get("hupu_reply_id"), item.get("author"), item.get("hupu_post_id"), reply_time_ms, item.get("like_count"), pymysql.escape_string(item.get("content")), item.get("floor_num"), int(time.time()) * 1000) try: with pool.get_db_connect() as db: db.cursor.execute(insert_sql) db.conn.commit() except: print("error reply content:" + pymysql.escape_string(item.get("content")))
def insert_reply(self, item): reply_time_str = item.get("reply_time") reply_time_sec = time.mktime( time.strptime(reply_time_str, "%Y-%m-%d %H:%M")) reply_time_ms = int(reply_time_sec) * 1000 insert_sql = "insert into hupu_post_reply(hupu_reply_id,author,hupu_post_id,reply_time,like_count,content,floor_num, gmt_created)" \ " values (%s,'%s',%s,%s,%s,'%s',%s,%s)" \ % (item.get("hupu_reply_id"), item.get("author"), item.get("hupu_post_id"), reply_time_ms, item.get("like_count"), pymysql.escape_string(item.get("content")), item.get("floor_num"), int(time.time()) * 1000) try: with pool.get_db_connect() as db: db.cursor.execute(insert_sql) db.conn.commit() except: print("error reply content:" + pymysql.escape_string(item.get("content")))
def insertOrUpdate(self, item): try: post_time_str = item.get("post_time") post_time_sec = time.mktime(time.strptime(post_time_str, "%Y-%m-%d")) post_time_ms = int(post_time_sec) * 1000 except: raise DropItem("error date") post_id = item.get("id") old_old = self.getByPostId(post_id) if old_old is None: insert_sql = "insert into hupu_post(hupu_post_id,title, author, url, post_time, view_count, reply_count, gmt_created)" \ " values (%s,'%s','%s','%s',%s,%s,%s,%s)" \ % (item.get("id"), pymysql.escape_string(item.get("title")), item.get("author"), item.get("url"), post_time_ms, item.get("view_count"), item.get("reply_count"), int(time.time()) * 1000) with pool.get_db_connect() as db: db.cursor.execute(insert_sql) db.conn.commit() elif str(old_old[1]) == item.get("view_count") and str(old_old[2]) == item.get("reply_count"): print("hupuid:%s post nochange..." % old_old[0]) else: print("update hupu......old viewcount=%s,now:%s" % (old_old[1], item.get("view_count"))) self.update(old_old[0], item)