def stat(self,server): self.log("LiuCunServerData start %s,server:[%s]" % (get_time(),server.server_name)) values = {} days = {'one_user':0,'two_user':1,'three_user':3,'four_user':4,'five_user':5,'six_user':6,'seven_user':7,'fivteen_user':15,'thirty_user':30} values['server_id'] = server.server_id values['server_name'] = server.server_name open_time = server.open_time con= get_conn(server_util.str_to_dbconfig(server.db_connect)) for k,v in days.iteritems(): temp_date = get_datetime_str_by_day(open_time,v,"%Y-%m-%d") values[k] = self.stat_login_user(temp_date,con,get_datetime_str_by_day(open_time,0,"%Y-%m-%d")) statLiucunData = StatLiucunData() one_user = values['one_user'] for k, v in values.iteritems(): if k == 'one_user' or k == 'server_id' or k == 'server_name': setattr(statLiucunData, k, v) else: value = 0 if one_user != 0 and v != 0: value = round(v/float(one_user),5)*100 setattr(statLiucunData, k, value) else: setattr(statLiucunData, k, value) statLiucunData.persist()
def stat(self, server, date): db_config = server_util.str_to_dbconfig(server.db_connect) connect = get_connection(db_config) page = 1 data_map = {} """ 统计金币消耗 """ self.log('server_name:[%s] stat gold start,date:[%s]' % (server.server_name, date)) while True: num = (page - 1) * 10000 sql = "SELECT SUM(amount) as total ,use_type,flag FROM (SELECT * FROM user_gold_use_log WHERE DATE(created_time) = '%s' LIMIT %d,10000) t GROUP BY use_type,flag" % ( date, num) cursor = None try: cursor = connect.cursor() datas = cursor.fetchall(sql) if not datas: break for data in datas: key = str(data['use_type']) + ',' + str(data['flag']) total = data_map.get(key, 0) total += data['total'] data_map[key] = total except Exception, ex: self.log(ex) self.log("server_name:[%s],page:[%s] stat fail " % (server.server_name, page)) finally:
def run(self): qa_db_config = server_business.get_server_db_connect(server_id="t2") qa_conn = get_connection(qa_db_config) qa_cursor = qa_conn.cursor() infos = qa_cursor.fetchall("show tables") tables = [] for info in infos: table = info["Tables_in_ldsg_t2"] tables.append(table) try: servers, _ = server_business.get_server_list(1, 30, 2) for server in servers: db_config = server_util.str_to_dbconfig(server.db_connect) conn = get_connection(db_config) cursor = conn.cursor() try: for table in tables: try: info = cursor.fetchone("select 1 from %s limit 1" % table) except: print "error:server:%s,table:%s" % (server.server_id, table) finally: cursor.close() finally: pass
def run(self): f_sql_1 = open("/data/game_fix.sql", "wb") f_sql_2 = open("/data/web_fix.sql", "wb") try: servers, _ = server_business.get_server_list(1, 30, 2) for server in servers: db_config = server_util.str_to_dbconfig(server.db_connect) conn = get_connection(db_config) cursor = conn.cursor() try: infos = cursor.fetchall("select partner_user_id, partner_id from user_mapper where partner_id in (1020, 1010) ") for info in infos: partner_user_id_old = info["partner_user_id"] partner_id = info["partner_id"] partner_user_id_new = self.get_partner_user_id(partner_user_id_old) if partner_user_id_new: self.log("old[%s], new[%s]" % (partner_user_id_old, partner_user_id_new)) f_sql_1.write("update user_mapper set partner_user_id='%s' where partner_user_id='%s' and partner_id=%s limit 1;\n" % (partner_user_id_new, partner_user_id_old, partner_id)) f_sql_1.write("update payment_log set partner_user_id='%s' where partner_user_id='%s' and partner_id=%s limit 1;\n" % (partner_user_id_new, partner_user_id_old, partner_id)) f_sql_2.write("update payment_order set partner_user_id='%s' where partner_user_id='%s' and partner_id=%s limit 1;\n" % (partner_user_id_new, partner_user_id_old, partner_id)) finally: cursor.close() finally: f_sql_1.close() f_sql_2.close()
def stat(self,server,open_time): self.log("LiuCunServerData start %s,server:[%s]" % (get_time(),server.server_name)) values = {} days = {'one_user':0,'two_user':1,'three_user':3,'four_user':4,'five_user':5,'six_user':6,'seven_user':7,'fivteen_user':15,'thirty_user':30} values['server_id'] = server.server_id values['server_name'] = server.server_name con= get_conn(server_util.str_to_dbconfig(server.db_connect)) for k,v in days.iteritems(): temp_date = get_datetime_str_by_day(open_time,v,"%Y-%m-%d") values[k] = self.stat_login_user(temp_date,con,get_datetime_str_by_day(open_time,0,"%Y-%m-%d")) statLiucunData = StatLiucunDataDate() one_user = values['one_user'] for k, v in values.iteritems(): if k == 'one_user' or k == 'server_id' or k == 'server_name': setattr(statLiucunData, k, v) else: value = 0 if one_user != 0 and v != 0: value = round(v/float(one_user),5)*100 setattr(statLiucunData, k, value) else: setattr(statLiucunData, k, value) statLiucunData.data_date=datetime_to_string(open_time) statLiucunData.persist()
def stat(self, server,date): db_config = server_util.str_to_dbconfig(server.db_connect) connect = get_connection(db_config) page = 1 data_map = {} """ 统计金币消耗 """ self.log('server_name:[%s] stat gold start,date:[%s]'% (server.server_name,date)) while True: num = (page - 1) * 10000 sql = "SELECT SUM(amount) as total ,use_type,flag FROM (SELECT * FROM user_gold_use_log WHERE DATE(created_time) = '%s' LIMIT %d,10000) t GROUP BY use_type,flag" %(date, num) cursor = None try: cursor = connect.cursor() datas = cursor.fetchall(sql) if not datas: break for data in datas: key = str(data['use_type']) + ',' + str(data['flag']) total = data_map.get(key,0) total += data['total'] data_map[key] = total except Exception,ex: self.log(ex) self.log("server_name:[%s],page:[%s] stat fail "%(server.server_name, page)) finally:
def total_server(self, server): """查询服务器""" db_config = server_util.str_to_dbconfig(server.db_connect) connect = get_connection(db_config) cursor = connect.cursor() level_map = {} try: start_time = None while True: where = "1=1" if start_time: where = "reg_time > '%s'" % start_time sql = "select reg_time, level from user where %s order by reg_time asc limit 10000" % where infos = cursor.fetchall(sql) if not infos: break self.log("size:[%s], start_time[%s]" % (len(infos), start_time)) start_time = infos[-1]["reg_time"] for info in infos: level = info["level"] total = level_map.get(level, 0) total += 1 level_map[level] = total finally: cursor.close() stat_user_level = StatUserLevel() stat_user_level.server_id = server.server_id stat_user_level.data = json.dumps(level_map) stat_user_level.date = datetime.now().strftime("%Y-%m-%d") stat_user_level.persist()
def get_server_db_connect(sid=None, server_id=None): if sid: server = Server.load(id=sid) else: server = Server.load(server_id=server_id) db_connect = server.db_connect return str_to_dbconfig(db_connect)
def getServerCountryCode(self, server): con = get_conn(server_util.str_to_dbconfig(server.db_connect)) ''' 新增用户 ''' sql = "select idfa from user_mapper group by idfa" cursor = None try: cursor = con.cursor() data = cursor.fetchall(sql) self.log("country codes:'%s'" % (data)) except Exception, ex: self.log(ex)
def getServerCountryCode(self,server): con= get_conn(server_util.str_to_dbconfig(server.db_connect)) ''' 新增用户 ''' sql = "select idfa from user_mapper group by idfa" cursor = None try: cursor = con.cursor() data = cursor.fetchall(sql) self.log("country codes:'%s'"%(data)) except Exception,ex: self.log(ex)
def fix(): server_list = get_all_server_list() for server in server_list: db_config = str_to_dbconfig(server.db_connect) connect = get_connection(db_config) cursor = connect.cursor() try: reg_time = cursor.fetchone("select reg_time from (select reg_time from user order by reg_time asc limit 500)d order by reg_time desc limit 1")["reg_time"] print server.server_id, reg_time server.open_time = reg_time server.persist() finally: cursor.close()
def fix(): server_list = get_all_server_list() for server in server_list: db_config = str_to_dbconfig(server.db_connect) connect = get_connection(db_config) cursor = connect.cursor() try: reg_time = cursor.fetchone( "select reg_time from (select reg_time from user order by reg_time asc limit 500)d order by reg_time desc limit 1" )["reg_time"] print server.server_id, reg_time server.open_time = reg_time server.persist() finally: cursor.close()
def stat(self ,server,date,country): while True: values = {} values['server_id'] = server.server_id values['server_name'] = server.server_name con= get_conn(server_util.str_to_dbconfig(server.db_connect)) ''' 新增用户 ''' sql = "SELECT COUNT(user_id) AS new_register FROM `user_mapper` WHERE DATE(created_time) = '%s' AND idfa='%s'" % (date,country) self.log(sql) cursor = None try: cursor = con.cursor() data = cursor.fetchone(sql) values['new_register'] = int(data['new_register']) except Exception,ex: self.log(ex) finally:
def stat(self, server, date): while True: values = {} values['server_id'] = server.server_id values['server_name'] = server.server_name con = get_conn(server_util.str_to_dbconfig(server.db_connect)) ''' 新增用户 ''' sql = "SELECT COUNT(distinct user_id) AS new_register FROM `user_mapper` WHERE DATE(created_time) = '%s'" % ( date) cursor = None try: cursor = con.cursor() data = cursor.fetchone(sql) values['new_register'] = int(data['new_register']) except Exception, ex: self.log(ex) finally:
def total_server(self, server): """查询服务器""" db_config = server_util.str_to_dbconfig(server.db_connect) connect = get_connection(db_config) cursor = connect.cursor() infos = None try: sql = "select lodo_id, username, level, exp from user order by level desc, exp desc limit 100" infos = cursor.fetchall(sql) infos = infos.to_list() finally: cursor.close() stat_user_level_rank = StatUserLevelRank() stat_user_level_rank.server_id = server.server_id stat_user_level_rank.data = json.dumps(infos) stat_user_level_rank.date = datetime.now().strftime("%Y-%m-%d") stat_user_level_rank.persist()
def stat(): server_list = get_server_list() file_name = "/data/apps/ldsg_admin/ldsg_admin/client/stat/stat/temp.csv" new_file = open(file_name,"wb") new_file.write("server_name,lodo_id\n") server_ids = ['h1','h2','h3','h4','h5','h6','h7','h8','h9','h10','h11','h12','h13','h14','h15','h16','h17','h18','h19','h20','h21','h22'] for server in server_list: if server.server_id not in server_ids: continue date_map_o = [] date_map_t = [] date_map_tt = [] try: connection = get_conn(server_util.str_to_dbconfig(server.db_connect)) cursor = connection.cursor() date_o = "2014-03-08" date_t = get_date_str_by_day(date_o,1,'%Y-%m-%d') date_tt = get_date_str_by_day(date_t,1,'%Y-%m-%d') sql_o = "SELECT DISTINCT user_id FROM payment_log WHERE DATE_FORMAT(created_time,'%%Y-%%m-%%d') = '%s' group by user_id" % date_o sql_t = "SELECT DISTINCT user_id FROM payment_log WHERE DATE_FORMAT(created_time,'%%Y-%%m-%%d') = '%s' group by user_id" % date_t sql_tt = "SELECT DISTINCT user_id FROM payment_log WHERE DATE_FORMAT(created_time,'%%Y-%%m-%%d') = '%s' group by user_id" % date_tt infos_o = cursor.fetchall(sql_o) infos_t = cursor.fetchall(sql_t) infos_tt = cursor.fetchall(sql_tt) for info in infos_o:date_map_o.append(info['user_id']) for info in infos_t:date_map_t.append(info['user_id']) for info in infos_tt:date_map_tt.append(info['user_id']) for k in date_map_o: if date_map_t.count(k) > 0 and date_map_tt.count(k) > 0: str_t = "%s,%s\n" % (server.server_name, cursor.fetchone("select lodo_id from user where user_id = '%s'" % k)["lodo_id"]) new_file.write(str_t.encode("gbk")) except: print traceback.format_exc() finally: cursor.close() new_file.close()
def run(self): f = open("/data/update.sql", "wb") try: servers, _ = server_business.get_server_list(1, 30, 2) for server in servers: db_config = server_util.str_to_dbconfig(server.db_connect) conn = get_connection(db_config) cursor = conn.cursor() try: sql ="""select * from user_mapper where user_id in (select user_id from user where level <=5 and user_id in (select user_id from user_mapper where partner_user_id in (select partner_user_id from (select count(1) as total, partner_user_id from user_mapper where partner_id = 1010 group by partner_user_id)t where total > 1))) ;""" infos = cursor.fetchall(sql) for info in infos: user_mapper_id = info["user_mapper_id"] print user_mapper_id f.write("update user_mapper set partner_user_id = CONCAT(user_id, '_', partner_user_id) where user_mapper_id = %s limit 1;\n" % user_mapper_id) finally: cursor.close() finally: f.close() pass
def run(self): f = open("/data/update.sql", "wb") try: servers, _ = server_business.get_server_list(1, 30, 2) for server in servers: db_config = server_util.str_to_dbconfig(server.db_connect) conn = get_connection(db_config) cursor = conn.cursor() try: sql = """select * from user_mapper where user_id in (select user_id from user where level <=5 and user_id in (select user_id from user_mapper where partner_user_id in (select partner_user_id from (select count(1) as total, partner_user_id from user_mapper where partner_id = 1010 group by partner_user_id)t where total > 1))) ;""" infos = cursor.fetchall(sql) for info in infos: user_mapper_id = info["user_mapper_id"] print user_mapper_id f.write( "update user_mapper set partner_user_id = CONCAT(user_id, '_', partner_user_id) where user_mapper_id = %s limit 1;\n" % user_mapper_id) finally: cursor.close() finally: f.close() pass