def insert_to_mysql_default(self, columns_data): field_exclude = ["uid", "video_id", "is56", "dtime", "play_plat"] datalist = [] cmc = ContantsMappingColumn() contants = cmc.get_unique_value() table_field = [k for k in contants] table_field.append("dtime") table_field.append("play_plat") table_field_len = len(table_field) set_diff = contants.difference(set(field_exclude)) update_field = [] map(lambda s: update_field.append("%s=values(%s)" % (s, s)), set_diff) for report in columns_data.values(): report.setdefault("dtime", self.mydate.get_now()) report.setdefault("play_plat", 1) table_value = [] for v in table_field: table_value.append(report.get(v, cmc.get_default_value(v))) datalist.append(tuple(table_value)) sql_head = ' insert into ' + self.table_name sql_body = ' ( ' + ','.join(table_field) + ' ) ' sql_blank = ' , %s ' * (table_field_len - 1) sql_tail = ' values ( %s ' + sql_blank + ' ) ' sql_duplicate = ' ON DUPLICATE KEY UPDATE ' + ','.join(update_field) sql = sql_head + sql_body + sql_tail + sql_duplicate Util.printf(sql) Util.printf("will update rows_num:%d" % datalist.__len__()) self.mysql_executemany(sql, datalist)
def insert_to_mysql_on_duplicate(self, columns_data): field_exclude = ["uid", "video_id", "is56", "dtime", "play_plat"] cmc = ContantsMappingColumn() table_field_len = 0 update_field = [] table_field = [] datalist = [] for report in columns_data.values(): report.setdefault("dtime", self.mydate.get_now()) report.setdefault("play_plat", 1) if table_field_len == 0: for k in report.keys(): table_field.append(k) if k not in field_exclude: update_field.append("%s=values(%s)" % (k, k)) table_field_len = len(table_field) table_value = [] for v in table_field: table_value.append(report.get(v, cmc.get_default_value(v))) datalist.append(tuple(table_value)) sql_head = ' insert into ' + self.table_name sql_body = ' ( ' + ','.join(table_field) + ' ) ' sql_blank = ' , %s ' * (table_field_len - 1) sql_tail = ' values ( %s ' + sql_blank + ' ) ' sql_duplicate = ' ON DUPLICATE KEY UPDATE ' + ','.join(update_field) sql = sql_head + sql_body + sql_tail + sql_duplicate Util.printf(sql) Util.printf("will update rows_num:%d" % datalist.__len__()) self.mysql_executemany(sql, datalist)