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 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 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 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_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 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 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)