1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
| def after_login():
print("您已登录")
# 连接数据库
db = pymysql.connect(host='localhost',
user='root',
password='lonbon',
db='wechat',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = db.cursor() # 使用cursor()方法获取操作游标
friends = itchat.get_friends(update=True)[0:] # 获取好友信息
print("好友:" + str(friends))
for friend in friends:
#判断好友是否已经存在在数据库
sql_select = "SELECT nickname,remarkname FROM contacts WHERE nickname =%s"
try:
cursor.execute(sql_select, (friend['NickName'])) # 查询好友昵称在数据库是否存在
result = cursor.fetchall()
db.commit()
if (len(result) > 0):
sql = "UPDATE contacts SET username =%s,time=%s WHERE nickname = %s"
try:
cursor.execute(sql, (friend['UserName'], time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
friend['NickName'])) # 更新好友id
db.commit() # 提交到数据库执行
except Exception as e:
# db.rollback()# 发生错误时回滚
print("sql_select_Error", e)
else:
sql_insert = """INSERT INTO contacts (nickname,username, remarkname
, signature, province,city,membercount,sex
,attrstatus,snsflag,contactflag,headimgurl,time) VALUES ('%s','%s'
,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (
str(friend['NickName']), str(friend['UserName']), str(friend['RemarkName'])
, str(friend['Signature']), str(friend['Province']), str(friend['City']), str(friend['MemberCount'])
, str(friend['Sex']), str(friend['AttrStatus']), str(friend['SnsFlag']), str(friend['ContactFlag'])
, str(friend['HeadImgUrl']), time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
cursor.execute(sql_insert)
db.commit()
except Exception as e:
# db.rollback() # 发生错误时回滚
print("sql_insert_Error", e)
# finally:
# 关闭数据库连接
# cursor.close()
# db.close()
# 获得完整的群聊列表
groups = itchat.get_chatrooms()
print("获得完整的群聊列表:" + str(groups))
for item in groups:
sql_select_group = "SELECT nickname,remarkname FROM groups WHERE nickname =%s"
try:
cursor.execute(sql_select_group, (item['NickName'])) # 查询群昵称在数据库是否存在
result = cursor.fetchall()
db.commit()
if (len(result) > 0):
print("已存在群：" + item['NickName'])
sql_update_group = "UPDATE groups SET username =%s,time=%s WHERE nickname = %s" # 更新群的id
try:
cursor.execute(sql_update_group, (
item['UserName'], time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), item['NickName']))
db.commit()
except Exception as e:
# 发生错误时回滚
# db.rollback()
print("发生了错误")
print("Error", e)
else:
sql_insert_group = """INSERT INTO groups (nickname,username, remarkname
, signature, province,city,membercount,sex
,attrstatus,snsflag,contactflag,headimgurl,time) VALUES ('%s','%s'
,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (
str(item['NickName']), str(item['UserName']), str(item['RemarkName'])
, str(item['Signature']), str(item['Province']), str(item['City']), str(item['MemberCount'])
, str(item['Sex']), str(item['AttrStatus']), str(item['SnsFlag']), str(item['ContactFlag'])
, str(item['HeadImgUrl']), time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
cursor.execute(sql_insert_group)
db.commit()
except Exception as e:
# db.rollback() # 发生错误时回滚
print("sql_select_group_Error", e)
# finally:
# 关闭数据库连接
cursor.close()
db.close()
|
