# -*- coding:utf-8 -*- # @Time : 2018/5/22 1:25 PM # @Author : Swing import pymongo import time import re import email_util # MONGO_HOST = 'mongodb://logdb:logdb@dds-uf6da0fedc9881d41450-pub.mongodb.rds.aliyuncs.com:3717,dds-uf6da0fedc9881d42459-pub.mongodb.rds.aliyuncs.com:3717/logdb?replicaSet=mgset-12835903' MONGO_HOST = 'mongodb://logdb:logdb@dds-uf6da0fedc9881d41154-pub.mongodb.rds.aliyuncs.com:3717,dds-uf6da0fedc9881d42456-pub.mongodb.rds.aliyuncs.com:3717/logdb?replicaSet=mgset-12835903' def daily_statistic(): now_date: time = time.localtime(time.time()) year = now_date.tm_year month = now_date.tm_mon day = now_date.tm_mday month_str = str(year) + '-' if month < 10: month_str += '0' month_str += str(month) day_str = month_str + '-' if day < 10: day_str += '0' day_str += str(day) date_reg = re.compile(day_str) area_reg = re.compile(r'(^[5][5-9][^\d])|(^[6][0-5][^\d])') city_residential_query_args = {'date': date_reg, 'area': area_reg, 'property_type': '普通住宅', 'decoration': {'$in': ['精装修', '豪华装修']}} city_department_query_args = {'date': date_reg, 'area': area_reg, 'property_type': '公寓', 'decoration': {'$in': ['精装修', '豪华装修']}} part_residential_query_args = {'date': date_reg, 'area': area_reg, 'property_type': '普通住宅', 'decoration': {'$in': ['精装修', '豪华装修']}} part_department_query_args = {'date': date_reg, 'area': area_reg, 'property_type': '公寓', 'decoration': {'$in': ['精装修', '豪华装修']}} city_residential_query_args_sold = {'date': date_reg, 'property_type': '普通住宅'} city_department_query_args_sold = {'date': date_reg, 'property_type': '公寓'} part_residential_query_args_sold = {'date': date_reg, 'property_type': '普通住宅'} part_department_query_args_sold = {'date': date_reg, 'property_type': '公寓'} task_args = [ city_residential_query_args, city_department_query_args, part_residential_query_args, part_department_query_args, city_residential_query_args_sold, city_department_query_args_sold, part_residential_query_args_sold, part_department_query_args_sold ] city_residential_average_rental = 0 city_department_average_rental = 0 part_residential_average_rental = 0 part_department_average_rental = 0 city_residential_average_sold = 0 city_department_average_sold = 0 part_residential_average_sold = 0 part_department_average_sold = 0 db = __open_db() for i in range(len(task_args)): col_name = '' if i in [0, 1]: col_name = 'rental_house' elif i in [2, 3]: col_name = 'sjk_rental_house' elif i in [4, 5]: col_name = 'nb_resold_house' elif i in [6, 7]: col_name = 'sjk_resold_house' data_list = __query_data(col_name, task_args[i], db) if data_list.count() == 0: continue average = __calculate_average_price(data_list) if i == 0: city_residential_average_rental = average elif i == 1: city_department_average_rental = average elif i == 2: part_residential_average_rental = average elif i == 3: part_department_average_rental = average elif i == 4: city_residential_average_sold = average elif i == 5: city_department_average_sold = average elif i == 6: part_residential_average_sold = average elif i == 7: part_department_average_sold = average # 住宅出租均价 rental_residential_filter_args = { 'day': day_str, 'property_type': '1' } rental_residential_update_args = { 'city_price': city_residential_average_rental if city_residential_average_rental > 0 else 2290, 'part_price': part_residential_average_rental if part_residential_average_rental > 0 else 3100, 'month': month_str, 'update_date': time.strftime('%Y-%m-%d %H:%M:%S'), 'house_id': '109' } __update_data(db, 'rental_statistics', rental_residential_filter_args, rental_residential_update_args) # 公寓出租均价 rental_department_filter_args = { 'day': day_str, 'property_type': '2' } rental_department_update_args = { 'city_price': city_department_average_rental if city_department_average_rental > 0 else 2330, 'part_price': part_department_average_rental if part_department_average_rental > 0 else 2850, 'month': month_str, 'update_date': time.strftime('%Y-%m-%d %H:%M:%S'), 'house_id': '109' } __update_data(db, 'rental_statistics', rental_department_filter_args, rental_department_update_args) # 住宅出售均价 sold_residential_filter_args = { 'day': day_str, 'property_type': '1' } sold_residential_update_args = { 'city_price': city_residential_average_sold if city_residential_average_sold > 0 else 19920, 'part_price': part_residential_average_sold if part_residential_average_sold > 0 else 24450, 'month': month_str, 'update_date': time.strftime('%Y-%m-%d %H:%M:%S'), 'house_id': '109' } __update_data(db, 'sold_statistics', sold_residential_filter_args, sold_residential_update_args) # 公寓出售均价 sold_department_filter_args = { 'day': day_str, 'property_type': '2' } sold_department_update_args = { 'city_price': city_department_average_sold if city_department_average_sold > 0 else 15650, 'part_price': part_department_average_sold if part_department_average_sold > 0 else 21560, 'month': month_str, 'update_date': time.strftime('%Y-%m-%d %H:%M:%S'), 'house_id': '109' } __update_data(db, 'sold_statistics', sold_department_filter_args, sold_department_update_args) # 关闭数据库连接 db.client.close() # 每日统计数据 通知谷晓晨 statistic_data = '宁波公寓租金: ' + str(city_department_average_rental) + '\n' statistic_data += ('宁波住宅租金: ' + str(city_residential_average_rental) + '\n') statistic_data += ('三江口公寓租金: ' + str(part_department_average_rental) + '\n') statistic_data += ('三江口住宅租金: ' + str(part_residential_average_rental) + '\n') statistic_data += ('宁波公寓售价: ' + str(city_department_average_sold) + '\n') statistic_data += ('宁波住宅售价: ' + str(city_residential_average_sold) + '\n') statistic_data += ('三江口公寓售价: ' + str(part_department_average_sold) + '\n') statistic_data += ('三江口住宅售价: ' + str(part_residential_average_sold) + '\n') email_util.send_email('每日统计数据', statistic_data, ['guxc@elab-plus.com', 'liuhx@elab-plus.com', 'zhaojh@elab-plus.com']) # 计算平均值 def __calculate_average_price(data_list): price_list = [] for data in data_list: try: date_str = re.search(r'[0-9]{4,}', data['price']).group() if date_str: price_list.append(float(date_str)) except Exception as err: pass sum = 0 for price in price_list: sum += price if len(price_list) == 0: return 0 average = int(round(sum / len(price_list))) return average # db 操作 def __query_data(collection, query_args, db): coll = db[collection] data_list = coll.find(query_args).sort('data', -1) return data_list # 更新数据 def __update_data(db, collection, filter, update): coll = db[collection] coll.update_one(filter, {'$set': update}, upsert=True) # 打开数据库 def __open_db(): # client = pymongo.MongoClient('101.132.106.154', authSource='logdb', authMechanism='SCRAM-SHA-1') client = pymongo.MongoClient(MONGO_HOST, authSource='logdb') db = client['logdb'] return db # list = [ # '2018-05-01', # '2018-05-02', # '2018-05-03', # '2018-05-04', # '2018-05-05', # '2018-05-06', # '2018-05-07', # '2018-05-08', # '2018-05-09', # '2018-05-10', # '2018-05-11', # '2018-05-12', # '2018-05-13', # '2018-05-14', # '2018-05-15', # '2018-05-16', # '2018-05-17', # '2018-05-18', # '2018-05-19', # '2018-05-20', # '2018-05-21', # '2018-05-22' # ] # # for i in list: # daily_statistic()