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
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from datetime import datetime
from django.conf import settings
from django.core.management import BaseCommand
from django.db import connection, connections
from django.db.models import Count
from qa.models import Answer, AnswerVote
class Command(BaseCommand):
""" python django_manage.py answer_like_num_update --year= --month= --day="""
def add_arguments(self, parser):
parser.add_argument(
'--year',
help=u'指定日期%Y-%m-%d'
)
parser.add_argument(
'--month',
help=u'指定日期%Y-%m-%d'
)
parser.add_argument(
'--day',
help=u'指定日期%Y-%m-%d'
)
def handle(self, *args, **options):
# 获取指定日期点赞灌水数据
year = int(options['year'])
month = int(options['month'])
day = int(options['day'])
if not all([year, month, day]):
return
cursor = connection.cursor()
connections.close_all()
start_time = datetime.today().replace(year=year, month=month, day=day, hour=0)
end_time = datetime.today().replace(year=year, month=month, day=day, hour=23)
# 统计指定日期灌水点赞个数
answer_map_vote_num = list(AnswerVote.objects.using(settings.HERA_READ_DB).filter(
create_time__lte=end_time, create_time__gte=start_time, is_fake=True,
).values('answer_id').annotate(count=Count('id')).values_list('answer_id', 'count'))
answer_ids = [item[0] for item in answer_map_vote_num]
# 回答原有的点赞数
answer_like_num = Answer.objects.using(settings.SLAVE_DB_NAME).filter(
id__in=answer_ids,
).values_list('id', 'like_num')
answer_like_num_dict = {item[0]: item[1] for item in answer_like_num}
sql = """UPDATE api_answer SET like_num = CASE id {} END WHERE id IN {}"""
sub_sql = """WHEN {} THEN {} """
sqls = []
len_answer = len(answer_map_vote_num)
ids = []
sub_sql_list = []
for index, (answer_id, like_num) in enumerate(answer_map_vote_num):
# WHEN 25 THEN 25 WHEN 14 THEN 245
count = answer_like_num_dict.get(answer_id, 0) + like_num
sub_sql_list.append(
sub_sql.format(answer_id, count)
)
ids.append(answer_id)
index += 1
if index % 500 == 0 or index == len_answer:
sqls.append(
sql.format("".join(sub_sql_list), tuple(ids))
)
ids = []
sub_sql_list = []
for sql in sqls:
cursor.execute(sql)
cursor.fetchall()
print("Done")