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 main(): conn = get_connection(db_config) cursor = conn.cursor() f = open("data.csv", "rb") ff = open("data_out.csv", "wb") i = 0 for line in f: if i == 0: ff.write(line) i += 1 continue line = line.replace("\n", "") line = line.decode("gbk").encode("utf8") #print line datas = line.split(",") if len(datas) < 5: continue pid, partner_user_id, amount = datas[0], datas[6], datas[9] amount = float(amount) * 100 pid = int(pid) if pid == 1: continue if pid > 263: break sql = "select * from payment_order where partner_id = 1003 and partner_user_id = '%s' and amount = %s order by created_time desc limit 1" % ( partner_user_id, amount) info = cursor.fetchone(sql) if not info: print sql break server_id = info["server_id"] server_db_config = server_business.get_server_db_connect( server_id=server_id) server_conn = get_connection(server_db_config) server_cursor = server_conn.cursor() user_info = server_cursor.fetchone( "select * from user where user_id = (select user_id from user_mapper where partner_user_id = '%s' and partner_id = 1003)" % partner_user_id) print server_id, user_info["lodo_id"] datas[7] = server_id datas[8] = "%s" % user_info["lodo_id"] ff.write("%s\n" % ",".join(datas))
def get_payment_order_list(ucenter, name, server_id, page=1, pagesize=20): start = (page - 1) * pagesize limit = pagesize ucenter_conn = get_connection(ldsg_center_db_configs[ucenter]) ucenter_cursor = ucenter_conn.cursor() db_config = server_business.get_server_db_connect(server_id=server_id) game_conn = get_connection(db_config) game_cursor = game_conn.cursor() total, infos = 0, [] try: user_info = game_cursor.fetchone( "select * from user where lodo_id='%s'" % name) if not user_info: user_info = game_cursor.fetchone( "select * from user where username='******'" % name) if user_info: user_mapper_info = game_cursor.fetchone( "select * from user_mapper where user_id = '%s'" % user_info["user_id"]) partner_user_id = user_mapper_info["partner_user_id"] where = " server_id = '%s' and partner_user_id = '%s'" % ( server_id, partner_user_id) total = ucenter_cursor.fetchone( "select count(1) as total from payment_order where %s order by created_time desc " % where)["total"] if total > 0: sql = "select * from payment_order where %s order by created_time desc limit %s, %s" % ( where, start, limit) infos = ucenter_cursor.fetchall(sql) infos = infos.to_list() for info in infos: if not info["partner_order_id"]: info["partner_order_id"] = "" info["username"] = user_info["username"] finally: ucenter_cursor.close() game_cursor.close() return total, infos
def main(): conn = get_connection(db_config) cursor = conn.cursor() f = open("data.csv", "rb") ff = open("data_out.csv", "wb") i = 0 for line in f: if i == 0: ff.write(line) i += 1 continue line = line.replace("\n", "") line = line.decode("gbk").encode("utf8") #print line datas = line.split(",") if len(datas) < 5: continue pid, partner_user_id, amount = datas[0], datas[6], datas[9] amount = float(amount) * 100 pid = int(pid) if pid == 1: continue if pid > 263: break sql = "select * from payment_order where partner_id = 1003 and partner_user_id = '%s' and amount = %s order by created_time desc limit 1" % (partner_user_id, amount) info = cursor.fetchone(sql) if not info: print sql break server_id = info["server_id"] server_db_config = server_business.get_server_db_connect(server_id=server_id) server_conn = get_connection(server_db_config) server_cursor = server_conn.cursor() user_info = server_cursor.fetchone("select * from user where user_id = (select user_id from user_mapper where partner_user_id = '%s' and partner_id = 1003)" % partner_user_id) print server_id, user_info["lodo_id"] datas[7] = server_id datas[8] = "%s" % user_info["lodo_id"] ff.write("%s\n" % ",".join(datas))
def get_user_purchase_info(server_id, name, start_date, end_date): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: user_info = get_user_info(server_id, name) user_purchase_compact_info = [] if user_info: user_id = user_info['user_id'] user_purchase_info = cursor.fetchall( "SELECT * FROM user_mall_log WHERE user_id='%s' AND created_time BETWEEN '%s' AND '%s'" % (user_id, start_date, end_date)) for info in user_purchase_info: mall_id = info['mall_id'] system_mall = cursor.fetchone( "SELECT * FROM system_mall WHERE mall_id='%s'" % mall_id) if system_mall: num = info['num'] created_time = info['created_time'] name = system_mall['name'] data = { 'name': name, 'num': num, 'created_time': created_time } user_purchase_compact_info.append(data) finally: cursor.close() return user_purchase_compact_info
def get_user_package_info(server_id, name): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: user_info = get_user_info(server_id, name) user_package_compact_info = [] if user_info: user_id = user_info['user_id'] user_package_info = cursor.fetchall( "SELECT * FROM user_tool WHERE user_id='%s'" % user_id) for info in user_package_info: tool_id = info['tool_id'] system_tool = cursor.fetchone( "SELECT * FROM system_tool WHERE tool_id='%s'" % tool_id) if system_tool: tool_num = info['tool_num'] tool_name = system_tool['name'] data = {'name': tool_name, 'tool_num': tool_num} user_package_compact_info.append(data) finally: cursor.close() return user_package_compact_info
def get_user_hero_info(server_id, name): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: user_info = get_user_info(server_id, name) user_hero_compact_info = [] if user_info: user_id = user_info['user_id'] user_hero_info = cursor.fetchall( "SELECT * FROM user_hero WHERE user_id='%s' order by hero_level DESC" % user_id) for info in user_hero_info: system_hero_id = info['system_hero_id'] system_hero_info = cursor.fetchone( "SELECT * FROM system_hero WHERE system_hero_id='%s'" % system_hero_id) if system_hero_info: hero_name = system_hero_info['hero_name'] data = { 'hero_name': hero_name, 'hero_exp': info['hero_exp'], 'hero_level': info['hero_level'], 'system_hero': system_hero_info } user_hero_compact_info.append(data) finally: cursor.close() return user_hero_compact_info
def send(server_id): date = datetime.now() - timedelta(days=1) date_str = date.strftime("%Y-%m-%d") date_s = datetime.now().strftime("%Y-%m-%d") file_name = "%s/reward_payment_double_%s_%s.sql" % (PathSettings.SCRIPT, date_s, server) logging.debug(file_name) f = open(file_name, "wb") db_config = server_business.get_server_db_connect(server_id=server_id) connection = get_connection(db_config) cursor = connection.cursor() try: sql = "SELECT sum(gold) as gold, user_id FROM payment_log where DATE_FORMAT(created_time,'%%Y-%%m-%%d') = '%s' group by user_id" % date_str logging.debug(sql) infos = cursor.fetchall(sql) for info in infos: gold = info["gold"] user_id = info["user_id"] sql1 = "UPDATE user SET gold_num = gold_num + %s WHERE user_id ='%s' LIMIT 1;\n" % (gold, user_id) sql2 = "INSERT INTO user_gold_use_log(user_id, use_type, amount, flag, created_time) VALUES('%s', 10004, %s, 1, now());\n" % (user_id, gold) f.write(sql1) f.write(sql2) finally: f.close() cursor.close()
def get_user_info(server_id, name): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() user_extinfo = None try: user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name) if not user_info: user_info = cursor.fetchone("select * from user where username='******'" % name) if user_info: user_extinfo = cursor.fetchone("select * from user_extinfo where user_id='%s'" % user_info["user_id"]) finally: cursor.close() if user_info and user_extinfo: user_info = user_info.to_dict() reward_vip_level = user_extinfo["reward_vip_level"] vip_level = user_info["vip_level"] if vip_level < reward_vip_level: user_info["vip_level"] = reward_vip_level if user_info["is_banned"] and user_info["due_time"] > datetime.now(): user_info["is_banned"] = 1 else: user_info["is_banned"] = 0 return user_info
def get_tool_name(server_id, tool_type, tool_id): if tool_type == ToolType.TOOL_TYPE_HERO: return u"武将卡牌" elif tool_type == ToolType.TOOL_TYPE_EQUIP: return u"装备" elif tool_type == ToolType.TOOL_TYPE_EXP: return u"经验" elif tool_type == ToolType.TOOL_TYPE_POWER: return u"体力" else: sql = "select name from system_tool where tool_id = %s" % tool_id key = "name" db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: info = cursor.fetchone(sql) if info: return info[key] else: return "[%s][%s]" % (tool_type, tool_id) finally: cursor.close
def update_user_level(server_id, user_id, level): level = int(level) db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: info = cursor.fetchone("select exp from system_user_level where user_level = %s" % (level + 1)) if info: exp = info["exp"] - 10 else: exp = 10000 * 10000 server = server_business.get_server(server_id) host = server.server_host path = "/gameApi/updateUserLevel.do" logging.debug("host:[%s], path[%s]" % (host, path)) data = {"userId": user_id, "level": level, "exp": exp} post = Post(data, host, path, port=8088) content = post.submit(result_keyword=None, get_content=True, debug=True) try: info = json.loads(content) return info.get("rc", 3000) == 1000 except: logging.debug("bad json format:[%s]" % content) finally: cursor.close()
def get_user_payment_log(server_id, name, start_time, end_time, page=1, pagesize=20): """获取用户充值日志""" start = (page - 1) * pagesize limit = pagesize db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() total, infos = 0, [] try: user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name) if not user_info: user_info = cursor.fetchone("select * from user where username='******'" % name) if user_info: where = " user_id = '%s' " % user_info['user_id'] if start_time: where += " and created_time >= '%s' " % start_time if end_time: where += " and created_time <= '%s' " % end_time total = cursor.fetchone("select count(1) as total from payment_log where %s " % where)["total"] if total > 0: sql = "select * from payment_log where %s order by payment_log_id desc limit %s, %s" % (where, start, limit) infos = cursor.fetchall(sql) finally: cursor.close() return total, infos
def get_user_level_up_log(server_id, name, page=1, pagesize=20): """获取用户升级日志""" start = (page - 1) * pagesize limit = pagesize db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() total, infos = 0, [] try: user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name) if not user_info: user_info = cursor.fetchone("select * from user where username='******'" % name) if user_info: user_id = user_info['user_id'] table = "user_level_up_log" where = " user_id = '%s' " % user_id total = cursor.fetchone("select count(1) as total from %s where %s " % (table, where))["total"] if total > 0: sql = "select * from %s where %s order by log_id desc limit %s, %s" % (table, where, start, limit) infos = cursor.fetchall(sql) infos = infos.to_list() for info in infos: info["username"] = user_info["username"] finally: cursor.close() return total, infos
def get_user_purchase_info(server_id, name, start_date, end_date): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: user_info = get_user_info(server_id, name) user_purchase_compact_info = [] if user_info: user_id = user_info['user_id'] user_purchase_info = cursor.fetchall("SELECT * FROM user_mall_log WHERE user_id='%s' AND created_time BETWEEN '%s' AND '%s'" % (user_id, start_date, end_date)) for info in user_purchase_info: mall_id = info['mall_id'] system_mall = cursor.fetchone("SELECT * FROM system_mall WHERE mall_id='%s'" % mall_id) if system_mall: num = info['num'] created_time = info['created_time'] name = system_mall['name'] data = { 'name' : name, 'num' : num, 'created_time' : created_time } user_purchase_compact_info.append(data) finally: cursor.close() return user_purchase_compact_info
def update_user_level(server_id, user_id, level): level = int(level) db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: info = cursor.fetchone( "select exp from system_user_level where user_level = %s" % (level + 1)) if info: exp = info["exp"] - 10 else: exp = 10000 * 10000 server = server_business.get_server(server_id) host = server.server_host path = "/gameApi/updateUserLevel.do" logging.debug("host:[%s], path[%s]" % (host, path)) data = {"userId": user_id, "level": level, "exp": exp} post = Post(data, host, path, port=8088) content = post.submit(result_keyword=None, get_content=True, debug=True) try: info = json.loads(content) return info.get("rc", 3000) == 1000 except: logging.debug("bad json format:[%s]" % content) finally: cursor.close()
def get_user_register(server_id,datetime): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor_curr = connection.cursor() cursor = connect.cursor() data = {} try: info = cursor_curr.fetchone("select 1 from stat_server_data where stat_date = '%s' and server_id = '%s' " % (datetime, server_id)) total_user = cursor.fetchone("SELECT COUNT(distinct a.user_id) as total_user FROM `user` a , user_mapper b WHERE DATE(a.reg_time) <= '%s' AND a.`user_id` = b.`user_id`" % (datetime)) data['total_user'] = int(total_user['total_user']) if info: temp = cursor_curr.fetchone("select create_user AS new_register,date_active from stat_server_data where stat_date = '%s' and server_id = '%s'" % (datetime, server_id)) data['new_register'] = int(temp['new_register']) data['date_active'] = int(temp['date_active']) else: info = cursor.fetchone("SELECT COUNT(user_id) AS new_register FROM `user` WHERE DATE(reg_time) = '%s'" % (datetime)) data['new_register'] = int(info['new_register']) active_user = 0 for i in range(128): info = cursor.fetchone("select count(distinct user_id) as date_active from user_online_log_%d where date(login_time) = '%s';" % (i,datetime)) active_user = active_user+int(info['date_active']) data['date_active'] = active_user finally: cursor.close() cursor_curr.close() data['active'] = round(data['date_active']/float(data['total_user']),5)*100 return data
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 get_user_info(server_id, name): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() user_extinfo = None try: user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name) if not user_info: user_info = cursor.fetchone( "select * from user where username='******'" % name) if user_info: user_extinfo = cursor.fetchone( "select * from user_extinfo where user_id='%s'" % user_info["user_id"]) finally: cursor.close() if user_info and user_extinfo: user_info = user_info.to_dict() reward_vip_level = user_extinfo["reward_vip_level"] vip_level = user_info["vip_level"] if vip_level < reward_vip_level: user_info["vip_level"] = reward_vip_level if user_info["is_banned"] and user_info["due_time"] > datetime.now(): user_info["is_banned"] = 1 else: user_info["is_banned"] = 0 return user_info
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_user_hero_info(server_id, name): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: user_info = get_user_info(server_id, name) user_hero_compact_info = [] if user_info: user_id = user_info['user_id'] user_hero_info = cursor.fetchall("SELECT * FROM user_hero WHERE user_id='%s' order by hero_level DESC" % user_id) for info in user_hero_info: system_hero_id = info['system_hero_id'] system_hero_info = cursor.fetchone("SELECT * FROM system_hero WHERE system_hero_id='%s'" % system_hero_id) if system_hero_info: hero_name = system_hero_info['hero_name'] data = { 'hero_name' : hero_name, 'hero_exp' : info['hero_exp'], 'hero_level' : info['hero_level'], 'system_hero': system_hero_info } user_hero_compact_info.append(data) finally: cursor.close() return user_hero_compact_info
def add_giftcode(category, gift_bag_type, sub_type, amount): amount = int(amount) connection = get_connection(ldsg_common_db_configs[category]) cursor = connection.cursor() try: version = 1 info = cursor.fetchone("select version from gift_code where gift_bag_type=%s and type=%s order by version desc limit 1" % (gift_bag_type, sub_type)) if info: version = info["version"] + 1 add_count = 0 while True: values = [] for _ in range(100 if amount > 100 else amount): code = get_code() values.append([code, ]) effect_count = cursor.executemany("insert ignore into gift_code values(%%s, %s, 0, now(), null, %s, 1, 'all', %s)" % (sub_type, gift_bag_type, version), values) logging.debug("effect_count[%s]" % effect_count) add_count += effect_count if add_count > amount: break finally: cursor.close() return True
def send(server_id): date = datetime.now() - timedelta(days=1) date_str = date.strftime("%Y-%m-%d") date_s = datetime.now().strftime("%Y-%m-%d") file_name = "%s/reward_payment_double_%s_%s.sql" % (PathSettings.SCRIPT, date_s, server) logging.debug(file_name) f = open(file_name, "wb") db_config = server_business.get_server_db_connect(server_id=server_id) connection = get_connection(db_config) cursor = connection.cursor() try: sql = "SELECT sum(gold) as gold, user_id FROM payment_log where DATE_FORMAT(created_time,'%%Y-%%m-%%d') = '%s' group by user_id" % date_str logging.debug(sql) infos = cursor.fetchall(sql) for info in infos: gold = info["gold"] user_id = info["user_id"] sql1 = "UPDATE user SET gold_num = gold_num + %s WHERE user_id ='%s' LIMIT 1;\n" % ( gold, user_id) sql2 = "INSERT INTO user_gold_use_log(user_id, use_type, amount, flag, created_time) VALUES('%s', 10004, %s, 1, now());\n" % ( user_id, gold) f.write(sql1) f.write(sql2) finally: f.close() cursor.close()
def get_user_package_info(server_id, name): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: user_info = get_user_info(server_id, name) user_package_compact_info = [] if user_info: user_id = user_info['user_id'] user_package_info = cursor.fetchall("SELECT * FROM user_tool WHERE user_id='%s'" % user_id) for info in user_package_info: tool_id = info['tool_id'] system_tool = cursor.fetchone("SELECT * FROM system_tool WHERE tool_id='%s'" % tool_id) if system_tool: tool_num = info['tool_num'] tool_name = system_tool['name'] data = { 'name' : tool_name, 'tool_num' : tool_num } user_package_compact_info.append(data) finally: cursor.close() return user_package_compact_info
def get_user_mail_list(server_id, name, source_id=0, page=1, pagesize=20): db_config = server_business.get_server_db_connect(server_id=server_id) total, infos = 0, [] connect = get_connection(db_config) cursor = connect.cursor() try: user_info = cursor.fetchone("select user_id from user where username='******'" % name) if not user_info: user_info = cursor.fetchone("select user_id from user where lodo_id='%s'" % name) if user_info: user_id = user_info["user_id"] table_index = table_util.get_table_index(user_id) table = "user_mail_%s" % table_index where = "user_id = '%s' " % user_id if source_id: mail_info = cursor.fetchone("select * from system_mail where source_id = %s " % source_id) if mail_info: where += " and system_mail_id = '%s' " % mail_info["system_mail_id"] else: where += " and system_mail_id = '-1' " total = cursor.fetchone("select count(1) as total from %s where %s " % (table, where))["total"] if total > 0: start = (page - 1) * pagesize infos = cursor.fetchall("select * from %s where %s order by created_time desc limit %s, %s" % (table, where, start, pagesize)) infos = infos.to_list() for info in infos: info["mail_info"] = cursor.fetchone("select * from system_mail where system_mail_id ='%s'" % info["system_mail_id"]) finally: cursor.close() return total, infos
def sync_package(ucenter, pid): conn = get_connection(ldsg_center_db_configs[ucenter]) cursor = conn.cursor() cursor_local = connection.cursor() try: info = cursor.fetchone("select * from package_info where id=%s" % pid) if not info: return partner_id = info["partner_id"] package_partner_info = cursor_local.fetchone("select * from partner where partner_id = %s" % partner_id) partner_list = cursor_local.fetchall("select * from partner where server_zone=%s" % package_partner_info["server_zone"]) params = [] for partner in partner_list: if partner["partner_id"] == partner_id: continue cursor.execute("delete from package_info where partner_id=%s and version='%s' limit 1" % (partner["partner_id"], info["version"])) params.append([partner["partner_id"], ]) sql = "INSERT INTO package_info(partner_id, version, versions, full_url, upgrade_url, pkg_type, description, " \ " frs, is_test) VALUES(%%s, '%s', '%s', '%s', '%s', %s, '%s', '%s', %s) " % \ (info['version'], info['versions'], info['full_url'], info['upgrade_url'], info['pkg_type'], info['description'], info['frs'], info['is_test']) return cursor.executemany(sql, params) finally: cursor_local.close() cursor.close()
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 db_log(self, info): conn = get_connection(DB_Mysql) cursor = conn.cursor() try: cursor.insert(info, "script_execute_log") finally: cursor.close()
def get_user_payment_stat(server_id, start_date, end_date): start_date = "%s 00:00:00" % start_date end_date = "%s 23:59:59" % end_date db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() user_payment_stat = [] try: infos = cursor.fetchall( "SELECT partner_id, sum(amount) AS total FROM payment_log WHERE created_time BETWEEN '%s' AND '%s' GROUP BY partner_id ORDER BY total DESC" % (start_date, end_date)) #infos = infos.to_list() total = 0 for info in infos: total += info["total"] user_payment_stat.append({ "name": info["partner_id"], "total": info["total"] }) user_payment_stat.append({"name": u"总计", "total": total}) finally: cursor.close() return user_payment_stat
def get_user_reg_stat_by_pid(server_id, partner_id, start_date, end_date): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() admin_db_cursor = connection.cursor() try: data = {} partner = admin_db_cursor.fetchone( "SELECT * FROM partner WHERE partner_id='%s'" % partner_id) reg_num = cursor.fetchone( "SELECT COUNT(user_id) AS reg_num FROM user_mapper WHERE partner_id='%s' AND created_time BETWEEN '%s' AND '%s'" % (partner_id, start_date, end_date)) create_num = cursor.fetchone( "SELECT COUNT(u.user_id) AS create_num FROM `user` u LEFT JOIN user_mapper m ON u.`user_id` = m.`user_id` WHERE m.`partner_id` = '%s' AND reg_time BETWEEN '%s' AND '%s'" % (partner_id, start_date, end_date)) data['reg_num'] = int(reg_num['reg_num']) data['create_num'] = int(create_num['create_num']) data['partner_name'] = partner['name'] data['ratio'] = round(data['create_num'] / float(data['reg_num']), 4) * 100 finally: cursor.close() return data
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 del_black_imei(ucenter, id): conn = get_connection(ldsg_center_db_configs[ucenter]) cursor = conn.cursor() try: if id: cursor.execute("DELETE FROM black_imei WHERE id=" + id) finally: cursor.close()
def del_black_imei(ucenter,id): conn = get_connection(ldsg_center_db_configs[ucenter]) cursor = conn.cursor() try: if id: cursor.execute("DELETE FROM black_imei WHERE id="+id) finally: cursor.close()
def get_user_draw_pk_rank(server_id): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: sql = "SELECT u.`rank`,u.`score`,u.`username` FROM user_pk_info u ORDER BY u.`rank` LIMIT 30" return cursor.fetchall(sql) finally: cursor.close()
def get_user_draw_time_rank(server_id): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: sql = "select b.username, b.lodo_id, a.score from user_draw_time a left join user b on a.user_id = b.user_id order by a.score desc limit 50" return cursor.fetchall(sql) finally: cursor.close()
def add_black_imei(ucenter,imei,partner_id): conn = get_connection(ldsg_center_db_configs[ucenter]) cursor = conn.cursor() try: if imei != "" and partner_id != "": value=[imei,partner_id] cursor.execute("insert into black_imei values (null,%s,%s)",value) finally: cursor.close()
def get_user_draw_power_rank(server_id,date): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: sql = u"SELECT r.`rank_data` FROM rank_log r WHERE r.`rank_key` = 'ldsg_local_power_rank_key' AND r.`date` = '%s' " % (date) return cursor.fetchall(sql) finally: cursor.close()
def handle(self, file_name): if not file_name.endswith(".sql"): self.log("is not a sql file[%s]" % file_name) return if self.check(file_name) == False: self.log("file had execute[%s]" % file_name) return; info = {} info["file_name"] = file_name info["file_name_md5"] = md5mgr.mkmd5fromstr(file_name) info["start_time"] = datetime.now() status = 1 server = file_name.replace(".sql", "").split("_")[-1] self.log("server[%s]" % server) server_list = self.get_server_list(server); self.log("server list[%s]" % server_list) finish_count = 0 for server_id in server_list: conn = get_connection(db_configs[server_id]) cursor = conn.cursor() start = time.time() sql_list = self.get_sql_list(file_name) cursor.autocommit(True) try: sqls = "" for sql in sql_list: finish_count += 1 sql = sql.strip() if not sql: continue sql = sql.replace("\n", "").replace("\r", "") #self.log("sql[%s]" % sql) sqls += sql if finish_count % self.batch_count == 0: try: cursor.execute(sqls) except OperationalError, e: if e.args[0] not in (1050, 1060): raise sqls = "" self.log("execute[%s]" % finish_count) if sqls: try: cursor.execute(sqls) except OperationalError, e: if e.args[0] != 1006: raise
def add_black_imei(ucenter, imei, partner_id): conn = get_connection(ldsg_center_db_configs[ucenter]) cursor = conn.cursor() try: if imei != "" and partner_id != "": value = [imei, partner_id] cursor.execute("insert into black_imei values (null,%s,%s)", value) finally: cursor.close()
def handle(self, file_name): if not file_name.endswith(".sql"): self.log("is not a sql file[%s]" % file_name) return if self.check(file_name) == False: self.log("file had execute[%s]" % file_name) return info = {} info["file_name"] = file_name info["file_name_md5"] = md5mgr.mkmd5fromstr(file_name) info["start_time"] = datetime.now() status = 1 server = file_name.replace(".sql", "").split("_")[-1] self.log("server[%s]" % server) server_list = self.get_server_list(server) self.log("server list[%s]" % server_list) finish_count = 0 for server_id in server_list: conn = get_connection(db_configs[server_id]) cursor = conn.cursor() start = time.time() sql_list = self.get_sql_list(file_name) cursor.autocommit(True) try: sqls = "" for sql in sql_list: finish_count += 1 sql = sql.strip() if not sql: continue sql = sql.replace("\n", "").replace("\r", "") # self.log("sql[%s]" % sql) sqls += sql if finish_count % self.batch_count == 0: try: cursor.execute(sqls) except OperationalError, e: if e.args[0] not in (1050, 1060): raise sqls = "" self.log("execute[%s]" % finish_count) if sqls: try: cursor.execute(sqls) except OperationalError, e: if e.args[0] != 1006: raise
def get_user_draw_power_rank(server_id, date): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: sql = u"SELECT r.`rank_data` FROM rank_log r WHERE r.`rank_key` = 'ldsg_local_power_rank_key' AND r.`date` = '%s' " % ( date) return cursor.fetchall(sql) finally: cursor.close()
def get_payment_order_list(ucenter, name, server_id, page=1, pagesize=20): start = (page - 1) * pagesize limit = pagesize ucenter_conn = get_connection(ldsg_center_db_configs[ucenter]) ucenter_cursor = ucenter_conn.cursor() db_config = server_business.get_server_db_connect(server_id=server_id) game_conn = get_connection(db_config) game_cursor = game_conn.cursor() total, infos = 0, [] try: user_info = game_cursor.fetchone("select * from user where lodo_id='%s'" % name) if not user_info: user_info = game_cursor.fetchone("select * from user where username='******'" % name) if user_info: user_mapper_info = game_cursor.fetchone("select * from user_mapper where user_id = '%s'" % user_info["user_id"]) partner_user_id = user_mapper_info["partner_user_id"] where = " server_id = '%s' and partner_user_id = '%s'" % (server_id, partner_user_id) total = ucenter_cursor.fetchone("select count(1) as total from payment_order where %s order by created_time desc " % where)["total"] if total > 0: sql = "select * from payment_order where %s order by created_time desc limit %s, %s" % (where, start, limit) infos = ucenter_cursor.fetchall(sql) infos = infos.to_list() for info in infos: if not info["partner_order_id"]: info["partner_order_id"] = "" info["username"] = user_info["username"] finally: ucenter_cursor.close() game_cursor.close() return total, infos
def get_activity_list(server_id): ''' 跟住服务器ID拿活动列表''' db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: activity_list = cursor.fetchall("select * from system_activity") finally: cursor.close() return activity_list
def get_system_mall_list(server_id): ''' 跟住服务器ID拿商品列表''' db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: system_mall_list = cursor.fetchall("select * from system_mall where tag in (1, 2)") finally: cursor.close() return system_mall_list
def sync_tool_table(server_id): db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() cursor_loc = connection.cursor() tool_infos = [] sqls = [] try: colors = {0: u'白', 1: u'绿', 2: u'蓝', 3: u'紫', 4: u'橙', 5: u'鬼'} sqls.append(u"truncate table system_tool;") sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 1, '元宝');") sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 2, '银币');") sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 6, '武将背包');") sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 7, '装备背包');") sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 8, '国战声望');") infos = cursor.fetchall("select hero_name, hero_color, system_hero_id from system_hero") logging.debug("sync system hero") for info in infos: system_hero_id = info["system_hero_id"] hero_desc = '%s-%s' % (info["hero_name"], colors.get(info["hero_color"])) #sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(%s, 3001, '%s');" % (system_hero_id, hero_desc)) tool_infos.append({"tool_id": system_hero_id, "tool_type": 3001, "tool_name": hero_desc}) infos = cursor.fetchall("select equip_name, color, equip_id from system_equip") logging.debug("sync system equip") for info in infos: equip_id = info["equip_id"] equip_desc = '%s-%s' % (info["equip_name"], colors.get(info["color"])) #sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(%s, 2001, '%s');" % (equip_id, equip_desc)) tool_infos.append({"tool_id": equip_id, "tool_type": 2001, "tool_name": equip_desc}) infos = cursor.fetchall("select name, color, tool_id, type from system_tool") logging.debug("sync system tool") for info in infos: tool_id = info["tool_id"] tool_desc = '%s-%s' % (info["name"], colors.get(info["color"])) tool_type = info["type"] #sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(%s, %s, '%s');" % (tool_id, tool_type, tool_desc)) tool_infos.append({"tool_id": tool_id, "tool_type": tool_type, "tool_name": tool_desc}) for sql in sqls: cursor_loc.execute(sql) cursor_loc.insert(tool_infos, "system_tool") finally: cursor.close() cursor_loc.close()
def add_system_notice(ucenter, info): conn = get_connection(ldsg_center_db_configs[ucenter]) cursor = conn.cursor() total, infos = 0, [] try: sql = "INSERT INTO notice(server_id, title, content, is_enable, created_time, updated_time, partner_id) " \ "VALUES(%s, %s, %s, %s, now(), now(), %s) ON DUPLICATE KEY UPDATE title=VALUES(title), content=VALUES(content), is_enable=VALUES(is_enable), partner_id=VALUES(partner_id)" cursor.execute(sql, [info["server_id"], info["title"], info["content"], info["is_enable"], info["partner_id"]]) finally: cursor.close() return total, infos
def get_system_mall_list(server_id): ''' 跟住服务器ID拿商品列表''' db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: system_mall_list = cursor.fetchall( "select * from system_mall where tag in (1, 2)") finally: cursor.close() return system_mall_list
def get_forces_list(server_id): ''' 跟住服务器ID拿关卡''' db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() try: forces_list = cursor.fetchall( "select * from system_forces where forces_type = 3") finally: cursor.close() return forces_list
def get_user_hero_log(server_id, name, hero_name, start_time, end_time, use_type=0, flag=0, page=1, pagesize=20): """获取用户武将日志""" start = (page - 1) * pagesize limit = pagesize db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() total, infos = 0, [] try: user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name) if not user_info: user_info = cursor.fetchone("select * from user where username='******'" % name) if user_info: user_id = user_info['user_id'] table = "user_hero_log_%s" % table_util.get_table_index(user_id) where = " user_id = '%s' " % user_info['user_id'] if use_type: where += " and use_type = %s " % use_type if flag: where += " and flag = %s " % flag if start_time: where += " and created_time >= '%s' " % start_time if end_time: where += " and created_time <= '%s' " % end_time if hero_name: where += " and system_hero_id in (select system_hero_id from system_hero where hero_name like '%%%s%%') " % hero_name sql = "select count(1) as total from %s where %s " % (table, where) total = cursor.fetchone(sql)["total"] if total > 0: sql = "select * from %s where %s order by log_id desc limit %s, %s" % (table, where, start, limit) infos = cursor.fetchall(sql) hero_info_map = {} infos = infos.to_list() for info in infos: system_hero_id = info["system_hero_id"] if system_hero_id in hero_info_map: hero_info = hero_info_map[system_hero_id] else: hero_info = cursor.fetchone("select * from system_hero where system_hero_id = %s " % system_hero_id ) if hero_info: hero_info_map[system_hero_id] = hero_info info["hero_info"] = hero_info finally: cursor.close() return total, infos
def get_user_tool_use_log(server_id, name, start_time, end_time, use_type=0, flag=0, page=1, pagesize=20, tool_type=0, tool_id=0): """获取用户道具日志""" start = (page - 1) * pagesize limit = pagesize db_config = server_business.get_server_db_connect(server_id=server_id) connect = get_connection(db_config) cursor = connect.cursor() total, infos = 0, [] try: user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name) if not user_info: user_info = cursor.fetchone( "select * from user where username='******'" % name) if user_info: user_id = user_info['user_id'] table = "user_tool_use_log_%s" % table_util.get_table_index( user_id) where = " user_id = '%s' " % user_id if use_type: where += " and use_type = %s " % use_type if flag: where += " and flag = %s " % flag if tool_type: where += " and tool_type = %s " % tool_type if tool_id: where += " and tool_id = %s " % tool_id if start_time: where += " and created_time >= '%s' " % start_time if end_time: where += " and created_time <= '%s' " % end_time sql = "select count(1) as total from %s where %s " % (table, where) total = cursor.fetchone(sql)["total"] if total > 0: sql = "select * from %s where %s order by log_id desc limit %s, %s" % ( table, where, start, limit) infos = cursor.fetchall(sql) infos = infos.to_list() finally: cursor.close() return total, infos
def open_all_scene(server_id, user_id): db_config = server_business.get_server_db_connect(server_id=server_id) table = "user_forces_%s" % table_util.get_table_index(user_id) connect = get_connection(db_config) cursor = connect.cursor() try: sql = OPEN_FORCES_SQL % (table, user_id, table, user_id) cursor.execute(sql) finally: cursor.close() return True
def check(self, file_name): conn = get_connection(DB_Mysql) cursor = conn.cursor() file_name_md5 = md5mgr.mkmd5fromstr(file_name) try: info = cursor.fetchone("select 1 from script_execute_log where file_name_md5 = '%s'" % file_name_md5) if info : return False else: return True finally: cursor.close()