def updateAlluser(self, userid, nickname):
try:
sql = "insert alluser values(%d, '%s') ON DUPLICATE KEY UPDATE nickname = '%s';" % (int(userid), nickname, nickname)
print(sql)
self.cursor.execute(sql);
self.connect.commit();
except Exception:
errormode.errorWriting(__file__ + " updateAlluser");
def getAlluser(self):
allids = [];
try:
self.cursor.execute("select id from %s;" % (self.url_alluser));
ret = self.cursor.fetchall();
if len(ret) > 0 :
for element in ret:
allids.append(element[0]);
except Exception:
errormode.errorWriting(__file__ + " getAlluser");
return allids;
def insertUnvisit(self, unList):
'''
将未访问的用户列表放到数据库中
:param unList: 为访问的数据库列表
:return:
'''
try:
for element in unList:
#print("insert %s valuse(%s);" % (self.url_unvisituser, element));
self.cursor.execute("insert %s values(%s, 0) ON DUPLICATE KEY UPDATE nums=nums+1;" % (self.url_unvisituser, element));
self.connect.commit();
except Exception:
errormode.errorWriting(__file__ + " insertUnvisit");
def updateFirstuser(self, userinfo):
try:
id = userinfo["id"];
nickname = userinfo["nickname"];
sex = userinfo["sex"];
address = userinfo["address"];
birthday = userinfo["birthday"];
weibos = userinfo["weibos"];
follow = userinfo["follow"];
interes = userinfo["interes"];
sql = 'insert firstuser values(%s,"%s",%s,"%s",%s,%s,%s,%s) ON DUPLICATE KEY UPDATE nickname="%s",sex=%s,address="%s",birthday=%s,weibos=%s,follow=%s,interes=%s;' % (id,nickname,sex,address,birthday,weibos,follow,interes, nickname,sex,address,birthday,weibos,follow,interes);
print(sql);
self.cursor.execute(sql);
self.connect.commit();
except Exception:
errormode.errorWriting(__file__ + " updateFirstuser");
def __init__(self):
self.connect = None
self.cursor =None
self.url_alluser = "******"
self.url_firstuser = "******"
self.url_unvisituser = "******"
try:
self.connect = pymysql.connect(host='localhost',user='******',passwd='east',db='spider',port=3306, charset="gbk")
self.cursor = self.connect.cursor()
self.cursor.execute("show tables;")
tables = self.cursor.fetchall()
ut_firstuser = False
ut_alluser = False
ut_unvisituser = False
for element in tables:
if element[0] == self.url_firstuser:
ut_firstuser = True
elif element[0] == self.url_alluser:
ut_alluser = True
elif element[0] == self.url_unvisituser:
ut_unvisituser = True
if False == ut_firstuser:
print("create firstuser table...")
self.cursor.execute("create table %s(id bigint(10) not null auto_increment, nickname char(32), sex tinyint(2), address char(10), birthday int(8), weibos int(6), follow int(9), interes int(5), PRIMARY KEY(id))" % self.url_firstuser);
self.cursor.execute("alter table %s convert to charset gbk;" % (self.url_firstuser));
print("create firstuser table success...")
if False == ut_alluser:
print("create alluser table...")
self.cursor.execute("create table %s(id bigint(10) not null auto_increment, nickname char(32) not null, PRIMARY KEY(id))" % self.url_alluser);
self.cursor.execute("alter table %s convert to charset gbk;" % (self.url_alluser));
print("create alluser table success...")
if False == ut_unvisituser:
print("create unvisituser table...")
self.cursor.execute("create table %s(id bigint(10) not null auto_increment, nums int, PRIMARY KEY(id))" % self.url_unvisituser);
self.cursor.execute("alter table %s convert to charset gbk;" % (self.url_unvisituser));
print("create unvisituser table success...")
except Exception:
errormode.errorWriting(__file__ + " sqlOperate")
def getUnvisit(self, inum):
'''
从数据表中取出未访问的num个用户, 病从数据表中删除
:param num: 取出未访问的用户个数
:return: 返回未访问用户的表
'''
userids = []
try:
self.cursor.execute("select * from %s limit %d;" % (self.url_unvisituser, inum));
ret = self.cursor.fetchall()
if len(ret) > 0 :
userids = ret[0]
self.cursor.execute("delete from %s limit %d;" % (self.url_unvisituser, inum));
self.connect.commit();
except Exception:
errormode.errorWriting(__file__ + " getUnvisit")
return userids;
