from excel_util import ExcelUtil import os class JianYeDataInsert(object): brand_id = 13 file_path = r'E:\elab\建业小程序升级\0330\建业项目卡片信息合档3.29 补2.xlsx' sql_1 = """update t_city_management set `order` = {}, coordinate_x = {}, coordinate_y = {} where name = '{}';""" sql_1_2 = "insert into t_city_management (brand_id, name, `order`, coordinate_x, coordinate_y, is_recommend, default_city, is_application, status, creator, created) values ({}, '{}', {}, {}, {}, {}, {}, 1, 1, 'binren', now());" sql_2 = """insert into t_house_management(is_recommend, brand_id, house_id, `order`, sale_status, description, is_new_open, is_hide, is_application, status, creator, created) values({}, {}, {}, {}, {}, "{}", {}, -1, 1, 1, 'binren', now());""" sql_3 = """insert into t_house_image (brand_id, house_id, image_url, image_type, status, creator, created) values({}, {}, '{}', '{}', 1, 'binren', now());""" sql_4 = """insert into t_house_parameters (brand_id, house_id, area_min, area_max, open_time, coordinate_x, coordinate_y, status, creator, created) values ({}, {}, {}, {}, {}, {}, {}, 1, 'binren', now());""" sql_5 = """insert into t_house_paraments_price_data(brand_id, house_id, value_min, value_max, value_type, house_type, status, creator, created) values ({}, {}, {}, {}, {}, '{}', 1,'binren', now());""" sql_6 = """update t_house_management set is_recommend = 1 where brand_id = 13;""" def __init__(self): self.excel_util = ExcelUtil(file_name=self.file_path) # 项目信息 def house_data(self): work_sheet = self.excel_util.read_excel_by_ox_name('项目信息补充') rows = [row for row in work_sheet.rows][1:] house_data = [] params_data = [] for row in rows: # 0:项目id 1:省份 2:城市 3:项目 4:项目地址 5:排序 6:最新开盘（1：是，-1：不是） 7:项目描述description # 8:销售状态（1：在售，2：待售，3：售完） 9:列表标签 10:推荐 11:状态（是否隐藏） 12:面积低值 13:面积高值 # 14开盘时间 15:经度 16:纬度 house_id = row[0].value order = row[5].value is_new_open = row[6].value description = row[7].value sale_status = row[8].value area_min = row[12].value area_max = row[13].value coordinate_x = row[15].value coordinate_y = row[16].value recommend = 'null' if row[10].value is None else row[10].value if order is None: order = 'null' if area_min == '待定' or area_min is None: area_min = 'null' if area_max == '待定' or area_max is None: area_max = 'null' if coordinate_x is None or coordinate_x == '待定': coordinate_x = 'null' if coordinate_y is None or coordinate_y == '待定': coordinate_y = 'null' if house_id: # house_id, `order`, sale_status, description, is_new_open house_data.append(self.sql_2.format(recommend, self.brand_id, house_id, order, sale_status, description, is_new_open)) # house_id, area_min, area_max, open_time, coordinate_x, coordinate_y params_data.append(self.sql_4.format(self.brand_id, house_id, area_min, area_max, 'null', coordinate_x, coordinate_y)) return house_data, params_data def params_prices(self): work_sheet = self.excel_util.read_excel_by_ox_name('项目参数单价') price_data = [] rows = [row for row in work_sheet.rows][1:] for row in rows: house_id = row[0].value price_min = row[4].value if price_min == '待定': price_min = 'null' price_max = row[5].value if price_max == '待定': price_max = 'null' house_type = row[6].value if house_type is None: house_type = 'null' if house_id: price_data.append(self.sql_5.format(self.brand_id, house_id, price_min, price_max, 1, house_type)) return price_data def total_price_data(self): work_sheet = self.excel_util.read_excel_by_ox_name('项目参数-总价') total_price_data = [] rows = [row for row in work_sheet.rows][1:] for row in rows: house_id = row[0].value price_min = row[5].value if price_min == '待定' or price_min is None: price_min = 'null' price_max = row[6].value if price_max == '待定' or price_max is None: price_max = 'null' house_type = row[4].value if house_type is None: house_type = 'null' if house_id: total_price_data.append(self.sql_5.format(self.brand_id, house_id, price_min, price_max, 2, house_type)) return total_price_data def get_city_data(self): work_sheet = self.excel_util.read_excel_by_ox_name('城市信息') city_data = [] rows = [row for row in work_sheet.rows][1:] for row in rows: name = row[0].value order = row[1].value x = 'null' if row[4].value is None else row[4].value y = 'null' if row[5].value is None else row[5].value default = 'null' if row[7].value is None else row[7].value recommend = 'null' if row[2].value is None else row[2].value # brand_id, name, order, coordinate_x, coordinate_y, city_data.append(self.sql_1_2.format(self.brand_id, name, order, x, y, recommend, default)) return city_data def search_image(self): work_sheet = self.excel_util.read_excel_by_ox_name('搜索主图') rows = [row for row in work_sheet.rows][1:] search_data = [] for row in rows: search_data.append(self.sql_3.format(row[0].value, row[1].value, '3')) return search_data def recommend(self): work_sheet = self.excel_util.read_excel_by_ox_name('推荐主图') rows = [row for row in work_sheet.rows][1:] recommend_data = [] for row in rows: recommend_data.append(self.sql_3.format(row[0].value, row[1].value, '2')) return recommend_data def list_image(self): work_sheet = self.excel_util.read_excel_by_ox_name('列表主图') rows = [row for row in work_sheet.rows][1:] list_data = [] for row in rows: list_data.append(self.sql_3.format(row[0].value, row[1].value, '1')) return list_data def house_cards(self): work_sheet = self.excel_util.read_excel_by_ox_name('项目卡片') rows = [row for row in work_sheet.rows][1:] insert_data = [] for row in rows: insert_data.append(self.sql_3.format(row[0].value, row[1].value, '3')) return insert_data def house_card_image(self): card_image = self.excel_util.read_excel_by_ox_name('项目卡片') card_rows = [row[0].value for row in card_image.rows] house_ids = self.excel_util.read_excel_by_ox_name('项目信息补充') ids = [row for row in house_ids] result = [] for cr in card_rows: path, file_name = os.path.split(cr) house_name = file_name.split('.')[0] house_id_end = 0 for row in ids: house_name_1 = row[3].value house_id = row[0].value if house_name == house_name_1: house_id_end = house_id else: pass result.append((house_id_end, cr)) return result def update_recommend(self): work_sheet = self.excel_util.read_excel_by_ox_name('建业推荐参数3.14') rows = [row for row in work_sheet.rows][1:] list_data = [] for row in rows: list_data.append(self.sql_6.format(row[1].value, row[0].value)) return list_data if __name__ == '__main__': jy = JianYeDataInsert() house_data, params_data = jy.house_data() # price_data = jy.params_prices() # total_price_data = jy.total_price_data() # city_data = jy.get_city_data() # search_data = jy.search_image() # recommend_data = jy.recommend() # card_datas = jy.house_cards() # list_data = jy.list_image() # print(len(house_data), len(params_data), len(price_data), len(total_price_data), len(city_data)) for sql in params_data: print(sql)