def save_system_mall_discount_detail(did, infos):
cursor = connection.cursor()
try:
cursor.execute("delete from system_mall_discount_activity_detail where system_mall_discount_activity_id = %s" % did)
cursor.insert(infos, "system_mall_discount_activity_detail")
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 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 persist(self):
'''persist object self'''
self._init_meta()
info('start to save object....[table:%s]' % self._table)
cache_key = self.build_cache_key(self)
if cache_key:
self._cache.delete(cache_key)
data = {}
update_skip_columns = ['id', 'created_time', 'updated_time']
meta = Persistable._meta_cache[self._table]
columns = meta.columns
for column in columns:
field_name = column.field
if hasattr(self, field_name):
data[field_name] = getattr(self, field_name)
elif field_name == 'created_time':
data['created_time'] = ReserveLiteral('now()')
cursor = connection.cursor()
try:
cursor.insert(data, self._table, True, update_skip_columns)
if not hasattr(self, 'id'):
data = cursor.fetchone('select last_insert_id() as id')
if data:
self.id = int(data['id'])
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('finish save object....[table:%s]' % self._table)
def load(cls, **keys):
'''load the object'''
cls._init_meta()
for _ in keys.values():
pass #检验参数
query_str = ','.join(['%s=%s' % (k, v) for k, v in keys.items()])
info('start load object with %s[table:%s]' % (query_str, cls._table))
#load from cache
obj = cls.load_from_cache(keys)
if obj:
return obj
condition = ' and '.join(["%s=%%s" % k for k in keys.keys()])
param = [keys[k] for k in keys.keys()]
sql = 'select * from %s where %s' % (cls._table, condition)
cursor = connection.cursor()
try:
data = cursor.fetchone(sql, param)
if data:
obj = cls._build_object(data)
cache_key = cls.build_cache_key(obj)
if cache_key:
cls._cache.set(cache_key, obj, 1800)
info('success load object[%s, %s]' % (cls._table, query_str))
return obj
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('load failure[table:%s]' % cls._table)
return None
def persist(self):
'''persist object self'''
self._init_meta()
info('start to save object....[table:%s]' % self._table)
cache_key = self.build_cache_key(self)
if cache_key:
self._cache.delete(cache_key)
data = {}
update_skip_columns = ['id', 'created_time', 'updated_time']
meta = Persistable._meta_cache[self._table]
columns = meta.columns
for column in columns:
field_name = column.field
if hasattr(self, field_name):
data[field_name] = getattr(self, field_name)
elif field_name == 'created_time':
data['created_time'] = ReserveLiteral('now()')
cursor = connection.cursor()
try:
cursor.insert(data, self._table, True, update_skip_columns)
if not hasattr(self, 'id'):
data = cursor.fetchone('select last_insert_id() as id')
if data:
self.id = int(data['id'])
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('finish save object....[table:%s]' % self._table)
def inserts(cls, objects):
'''insert objects'''
cls._init_meta()
info('start to save objects....[table:%s]' % cls._table)
update_skip_columns = ['id', 'created_time', 'updated_time']
meta = Persistable._meta_cache[cls._table]
columns = meta.columns
datas = []
for obj in objects:
data = {}
cache_key = obj.build_cache_key(obj)
if cache_key:
cls._cache.delete(cache_key)
for column in columns:
field_name = column.field
if field_name == 'updated_time':
continue
elif hasattr(obj, field_name):
data[field_name] = getattr(object, field_name)
elif field_name == 'created_time':
data['created_time'] = ReserveLiteral('now()')
datas.append(data)
cursor = connection.cursor()
try:
cursor.insert(datas, cls._table, True, update_skip_columns)
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('finish save objects....[table:%s]' % cls._table)
def set_server_partner_list(server_id, server_zone, partner_ids):
server_partner_list = get_server_partner_list(server_id)
delete_partner = []
partner_id_set = set()
partner_id_list = partner_ids.split(",")
#增加的parnter
params = []
for partner_id in partner_id_list:
partner_id_set.add(partner_id)
params.append([partner_id, ])
for server_partner in server_partner_list:
if server_partner.partner_id not in partner_id_set:
partner = Partner.load(partner_id=server_partner.partner_id)
if partner.server_zone != server_zone:
continue
delete_partner.append(server_partner.partner_id)
cursor = connection.cursor()
try:
if delete_partner:
cursor.execute("delete from server_partner_list where server_id = '%s' and partner_id in (%s)" % (server_id, ",".join(delete_partner)))
sql = "insert ignore into server_partner_list(server_id, partner_id, created_time) values('%s', %%s, now())" % server_id
cursor.executemany(sql, params)
finally:
cursor.close()
def inserts(cls, objects):
'''insert objects'''
cls._init_meta()
info('start to save objects....[table:%s]' % cls._table)
update_skip_columns = ['id', 'created_time', 'updated_time']
meta = Persistable._meta_cache[cls._table]
columns = meta.columns
datas = []
for obj in objects:
data = {}
cache_key = obj.build_cache_key(obj)
if cache_key:
cls._cache.delete(cache_key)
for column in columns:
field_name = column.field
if field_name == 'updated_time':
continue
elif hasattr(obj, field_name):
data[field_name] = getattr(object, field_name)
elif field_name == 'created_time':
data['created_time'] = ReserveLiteral('now()')
datas.append(data)
cursor = connection.cursor()
try:
cursor.insert(datas, cls._table, True, update_skip_columns)
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('finish save objects....[table:%s]' % cls._table)
def save_system_tool_exchange_detail(eid, infos):
cursor = connection.cursor()
try:
cursor.execute("delete from system_tool_exchange_detail where system_tool_exchange_id = %s" % eid)
cursor.insert(infos, "system_tool_exchange_detail")
finally:
cursor.close()
def set_server_partner_list(server_id, server_zone, partner_ids):
server_partner_list = get_server_partner_list(server_id)
delete_partner = []
partner_id_set = set()
partner_id_list = partner_ids.split(",")
#增加的parnter
params = []
for partner_id in partner_id_list:
partner_id_set.add(partner_id)
params.append([
partner_id,
])
for server_partner in server_partner_list:
if server_partner.partner_id not in partner_id_set:
partner = Partner.load(partner_id=server_partner.partner_id)
if partner.server_zone != server_zone:
continue
delete_partner.append(server_partner.partner_id)
cursor = connection.cursor()
try:
if delete_partner:
cursor.execute(
"delete from server_partner_list where server_id = '%s' and partner_id in (%s)"
% (server_id, ",".join(delete_partner)))
sql = "insert ignore into server_partner_list(server_id, partner_id, created_time) values('%s', %%s, now())" % server_id
cursor.executemany(sql, params)
finally:
cursor.close()
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 delete(self, key=None):
'''delete the object'''
self._init_meta()
if not key:
key = 'id'
if hasattr(self, key):
key_value = getattr(self, key)
else:
raise 'key value not exist'
info('start to delete[table:%s, %s=%s]' %
(self._table, key, key_value))
sql = 'delete from %s where %s = %s' % (self._table, key, key_value)
cache_key = self.build_cache_key(self)
if cache_key:
info('start to delete from cache:[%s]' % cache_key)
self._cache.delete(cache_key)
cursor = connection.cursor()
try:
cursor.execute(sql)
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('finish delete')
def save_system_pay_reward_detail(rid, infos):
cursor = connection.cursor()
try:
cursor.execute("delete from system_pay_reward_detail where system_pay_reward_id = %s" % rid)
cursor.insert(infos, "system_pay_reward_detail")
finally:
cursor.close()
def delete(self, key=None):
'''delete the object'''
self._init_meta()
if not key:
key = 'id'
if hasattr(self, key):
key_value = getattr(self, key)
else:
raise 'key value not exist'
info('start to delete[table:%s, %s=%s]' % (self._table, key, key_value))
sql = 'delete from %s where %s = %s' % (self._table, key, key_value)
cache_key = self.build_cache_key(self)
if cache_key:
info('start to delete from cache:[%s]' % cache_key)
self._cache.delete(cache_key)
cursor = connection.cursor()
try:
cursor.execute(sql)
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('finish delete')
def load(cls, **keys):
'''load the object'''
cls._init_meta()
for _ in keys.values():
pass #检验参数
query_str = ','.join(['%s=%s' % (k, v) for k, v in keys.items()])
info('start load object with %s[table:%s]' % (query_str, cls._table))
#load from cache
obj = cls.load_from_cache(keys)
if obj:
return obj
condition = ' and '.join(["%s=%%s" % k for k in keys.keys()])
param = [keys[k] for k in keys.keys()]
sql = 'select * from %s where %s' % (cls._table, condition)
cursor = connection.cursor()
try:
data = cursor.fetchone(sql, param)
if data:
obj = cls._build_object(data)
cache_key = cls.build_cache_key(obj)
if cache_key:
cls._cache.set(cache_key, obj, 1800)
info('success load object[%s, %s]' % (cls._table, query_str))
return obj
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('load failure[table:%s]' % cls._table)
return None
def save_system_tool_exchange_detail(eid, infos):
cursor = connection.cursor()
try:
cursor.execute(
"delete from system_tool_exchange_detail where system_tool_exchange_id = %s"
% eid)
cursor.insert(infos, "system_tool_exchange_detail")
finally:
cursor.close()
def save_system_pay_reward_detail(rid, infos):
cursor = connection.cursor()
try:
cursor.execute(
"delete from system_pay_reward_detail where system_pay_reward_id = %s"
% rid)
cursor.insert(infos, "system_pay_reward_detail")
finally:
cursor.close()
def save_system_mall_discount_detail(did, infos):
cursor = connection.cursor()
try:
cursor.execute(
"delete from system_mall_discount_activity_detail where system_mall_discount_activity_id = %s"
% did)
cursor.insert(infos, "system_mall_discount_activity_detail")
finally:
cursor.close()
def get_partner_server_list(partner_id):
sql = """select b.server_id, b.server_name, b.server_status, b.open_time, b.server_port from server_partner_list a
left join server b on a.server_id = b.server_id where a.partner_id = %s order by b.open_time asc """
cursor = connection.cursor()
try:
return cursor.fetchall(sql, [partner_id, ])
finally:
cursor.close()
def get_partner_server_list(partner_id):
sql = """select b.server_id, b.server_name, b.server_status, b.open_time, b.server_port from server_partner_list a
left join server b on a.server_id = b.server_id where a.partner_id = %s order by b.open_time asc """
cursor = connection.cursor()
try:
return cursor.fetchall(sql, [
partner_id,
])
finally:
cursor.close()
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 count(cls, condition='1=1'):
'''count the records'''
cls._init_meta()
cursor = connection.cursor()
sql = 'select count(*) as count from %s where %s' % (cls._table, condition)
try:
data = cursor.fetchone(sql)
return data['count']
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
def get_user_online_stat(server_id):
cursor = connection.cursor()
today = get_date() + ' 00:00:00'
tomorrow = get_tomorrow_str_by_str(today)
try:
user_online_stat = cursor.fetchall("SELECT * FROM stat_online WHERE server_id='%s' AND stat_time BETWEEN '%s' AND '%s'" % (server_id, today, tomorrow))
finally:
cursor.close()
return user_online_stat
def count(cls, condition='1=1'):
'''count the records'''
cls._init_meta()
cursor = connection.cursor()
sql = 'select count(*) as count from %s where %s' % (cls._table,
condition)
try:
data = cursor.fetchone(sql)
return data['count']
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
def get_user_online_stat(server_id):
cursor = connection.cursor()
today = get_date() + ' 00:00:00'
tomorrow = get_tomorrow_str_by_str(today)
try:
user_online_stat = cursor.fetchall(
"SELECT * FROM stat_online WHERE server_id='%s' AND stat_time BETWEEN '%s' AND '%s'"
% (server_id, today, tomorrow))
finally:
cursor.close()
return user_online_stat
def run(self):
if self.server_id:
statLiucunData = StatLiucunData.load(server_id = server_id)
if statLiucunData:
statLiucunData.is_hostory = 1
statLiucunData.persist()
else:
curr_cursor = None
try:
curr_cursor = connection.cursor()
sql = "update stat_liucun_data set is_hostory = 1"
curr_cursor.execute(sql)
except Exception,e:
self.log(e)
finally:
def run(self):
if self.server_id:
statLiucunData = StatLiucunData.load(server_id=server_id)
if statLiucunData:
statLiucunData.is_hostory = 1
statLiucunData.persist()
else:
curr_cursor = None
try:
curr_cursor = connection.cursor()
sql = "update stat_liucun_data set is_hostory = 1"
curr_cursor.execute(sql)
except Exception, e:
self.log(e)
finally:
def run(self):
if self.server_id:
#ServerPartnerList.query(condition="server_id='%s'" % server_id, order="partner_id desc")
statLiucunData = StatLiucunDataDate.load(server_id = server_id)
if statLiucunData:
statLiucunData.is_hostory = 1
statLiucunData.persist()
else:
curr_cursor = None
try:
curr_cursor = connection.cursor()
sql = "update stat_liucun_data set is_hostory = 1"
curr_cursor.execute(sql)
except Exception,e:
self.log(e)
finally:
def query(cls, **args):
'''query objects'''
cls._init_meta()
limit = ''
order = ''
where = '1=1'
if 'limit' in args:
limit = 'limit %s' % args['limit']
if 'order' in args:
order = 'order by %s' % args['order']
if 'condition' in args:
where = args['condition']
columns = args.get('columns', '*')
sql = 'select %s from %s where %s %s %s ' % (columns, cls._table,
where, order, limit)
if len(sql) < 2000:
info('start to load objects[%s] with sql:%s' % (cls._table, sql))
info('start to load objects')
cursor = connection.cursor()
try:
data = cursor.fetchall(sql)
if data:
objs = []
for row in data:
obj = cls._build_object(row)
objs.append(obj)
info('query success, return %s records...' % len(objs))
return objs
except:
logging.error(traceback.format_exc())
exception_mgr.on_except(sql)
raise PersistableException()
finally:
cursor.close()
info('not record found')
return []
def main():
"""main"""
servers = server_business.get_all_server_list()
date = get_time()
for server in servers:
if not server:
continue
redis_cfg = str_to_redisconfig(server.redis_connect)
logging.debug("host[%s], port[%s]" %
(redis_cfg['host'], redis_cfg['port']))
client = redis.Redis(host=redis_cfg['host'], port=redis_cfg['port'])
s = client.get("ldsg_local_system_status_key")
if s:
info = json.loads(s)
online = info["connCount"]
cursor = connection.cursor()
try:
cursor.execute(
"INSERT INTO stat_online(online, stat_time,server_id) VALUES(%d, '%s','%s')"
% (online, date, server.server_id))
finally:
connection.close()
def delete_mul(cls, **keys):
'''load the object'''
cls._init_meta()
for _ in keys.values():
pass #检验参数
query_str = ','.join(['%s=%s' % (k, v) for k, v in keys.items()])
info('start load object with %s[table:%s]' % (query_str, cls._table))
condition = ' and '.join(["%s=%%s" % k for k in keys.keys()])
param = [keys[k] for k in keys.keys()]
sql = 'delete from %s where %s' % (cls._table, condition)
cursor = connection.cursor()
try:
cursor.execute(sql, param)
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('finish delete')
def delete_mul(cls,**keys):
'''load the object'''
cls._init_meta()
for _ in keys.values():
pass #检验参数
query_str = ','.join(['%s=%s' % (k, v) for k, v in keys.items()])
info('start load object with %s[table:%s]' % (query_str, cls._table))
condition = ' and '.join(["%s=%%s" % k for k in keys.keys()])
param = [keys[k] for k in keys.keys()]
sql = 'delete from %s where %s' % (cls._table, condition)
cursor = connection.cursor()
try:
cursor.execute(sql,param)
except:
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
info('finish delete')
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 query(cls, **args):
'''query objects'''
cls._init_meta()
limit = ''
order = ''
where = '1=1'
if 'limit' in args:
limit = 'limit %s' % args['limit']
if 'order' in args:
order = 'order by %s' % args['order']
if 'condition' in args:
where = args['condition']
columns = args.get('columns', '*')
sql = 'select %s from %s where %s %s %s ' % (columns, cls._table, where, order, limit)
if len(sql) < 2000:
info('start to load objects[%s] with sql:%s' % (cls._table, sql))
info('start to load objects')
cursor = connection.cursor()
try:
data = cursor.fetchall(sql)
if data:
objs = []
for row in data:
obj = cls._build_object(row)
objs.append(obj)
info('query success, return %s records...' % len(objs))
return objs
except:
logging.error(traceback.format_exc())
exception_mgr.on_except(sql)
raise PersistableException()
finally:
cursor.close()
info('not record found')
return []
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 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()
date, date, date, country)
self.log(sql)
data = cursor.fetchone(sql)
values['new_payer'] = int(data['new_payer'])
except Exception, ex:
self.log(ex)
finally:
if cursor is not None:
cursor.close()
''' 老用户充值人�?'''
values['old_payer'] = int(values['pay_people']) - int(
values['new_payer'])
''' �?��在线 '''
cursor = None
try:
cursor = connection.cursor()
sql = "select COALESCE(max(online),0) as max_online from stat_online where date(stat_time) = '%s' and server_id = '%s'" % (
date, values['server_id'])
data = cursor.fetchone(sql)
if not data and data['max_online'] is None:
values['max_online'] = 0
else:
values['max_online'] = int(data['max_online'])
except Exception, ex:
self.log(ex)
finally:
if cursor is not None:
cursor.close()
statServerData = StatServerDataCountry()
statServerData.country_code = country
def transaction(cls):
if not cls._cursor:
cls._cursor = connection.cursor()
info('start transaction...')
cls._cursor.execute('start transaction;')
cls._transaction = True
def _init_meta(cls):
if hasattr(cls, 'inited') and getattr(cls, 'inited'):
return
info('-' * 100)
info('start to init meta[%s]...' % cls.__name__)
info('-' * 100)
if not hasattr(cls, '_table') or getattr(cls, '_table') is None:
cls._table = cls._parse_table()
meta = cls._cache.get(cls._table)
if meta:
#try get from cache
info('meta hit in cache, use it!!!')
Persistable._meta_cache[cls._table] = meta
cls.inited = True
return
if cls._table in Persistable._meta_cache:
return
sql = 'show columns from %s'
cursor = connection.cursor()
try:
try:
data = cursor.fetchall(sql % cls._table)
except ProgrammingError:
raise TableNotExistException(cls._table)
meta = Meta(cls._table)
column_infos = data.to_list()
for column_info in column_infos:
field = column_info['Field']
default = column_info['Default']
column_type = column_info['Type']
extra = column_info['Extra']
if column_type.startswith(Column.TYPE_INT):
temp_type = column_type[:len(Column.TYPE_INT)]
length = column_type[len(Column.TYPE_INT) + 1:-1]
column_type = temp_type
elif column_type.startswith(Column.TYPE_VARCHAR):
temp_type = column_type[:len(Column.TYPE_VARCHAR)]
length = column_type[len(Column.TYPE_VARCHAR) + 1:-1]
column_type = temp_type
else:
length = None
key = column_info['Key']
if key == 'UNI' or key == 'PRI':
is_unique = True
else:
is_unique = False
column = Column(field=field, column_type=column_type, length=length, \
is_unique=is_unique, default=default, extra=extra)
meta.add_column(column)
cls._cache.set(cls._table, meta)
Persistable._meta_cache[cls._table] = meta
except:
info(traceback.format_exc())
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
cls.inited = True
def get_stat_server(begindate, enddate=None):
statServerDatas = []
if begindate == date_utils.get_date():
statServerDate = StatServerDate(False)
statServerDate.start()
statServerDatas = statServerDate.statServerDatass
elif enddate is not None:
sql = "SELECT server_name,server_id, sum(new_register) as new_register, sum(create_user) as create_user, sum(date_active) as date_active, sum(pay_people) as pay_people, sum(pay_money) as pay_money, sum(max_online) as max_online, sum(new_user_pay) as new_user_pay\
, sum(old_user_pay) as old_user_pay, sum(new_payer) as new_payer, sum(old_payer) as old_payer FROM stat_server_data where stat_date>= '%s' and stat_date<='%s' group by server_id;" % (
begindate, enddate)
cursor = connection.cursor()
try:
datas = cursor.fetchall(sql)
for data in datas:
statServerDatas.append(data)
finally:
cursor.close()
else:
sql = "SELECT * from stat_server_data where stat_date = '%s'" % begindate
cursor = connection.cursor()
try:
datas = cursor.fetchall(sql)
for data in datas:
statServerDatas.append(data)
finally:
cursor.close()
#statServerDatas = StatServerData.query(condition="stat_date ='%s'" % begindate)
total = 0
statServerDatas_s = []
for s in statServerDatas:
if not s:
continue
try:
server_id = s["server_id"]
except:
server_id = s.server_id
server = Server.load(server_id=server_id)
try:
if server and server.is_test == 0:
statServerDatas_s.append(s)
total += s["pay_money"]
if s["pay_people"] != 0 and s["date_active"] != 0:
value = round(s["pay_people"] / (s["date_active"]), 5) * 100
s.temp = value
else:
s.temp = 0
except:
if server and server.is_test == 0:
total += s.pay_money
if s.pay_money != 0 and s.date_active != 0:
value = round(s.pay_money / (s.date_active), 5) * 100
s.temp = value
else:
s.temp = 0
stat_server_datas_map = {}
for s in statServerDatas_s:
if not s:
continue
try:
server_id = s["server_id"]
except:
server_id = s.server_id
server = Server.load(server_id=server_id)
server_zone = server.server_zone
server_zone_server_list = stat_server_datas_map.get(server_zone, [])
server_zone_server_list.append(s)
stat_server_datas_map[server_zone] = server_zone_server_list
items = stat_server_datas_map.items()
items.sort()
for k, values in stat_server_datas_map.iteritems():
try:
server_id = s["server_id"]
values = sorted(values,
cmp=lambda x, y: cmp(int(x["server_id"][1:]),
int(y["server_id"][1:])))
except:
values = sorted(values,
cmp=lambda x, y: cmp(int(x.server_id[1:]),
int(y.server_id[1:])))
stat_server_datas_map[k] = values
#statServerDatas_s.sort(cmp=lambda x,y:cmp(int(x.server_id[1:]), int(y.server_id[1:])))
return stat_server_datas_map, total, server_zone_map
self.log(sql)
data = cursor.fetchone(sql)
values['new_payer'] = int(data['new_payer'])
except Exception,ex:
self.log(ex)
finally:
if cursor is not None:
cursor.close()
''' 老用户充值人�?'''
values['old_payer'] = int(values['pay_people']) - int(values['new_payer'])
''' �?��在线 '''
cursor = None
try:
cursor = connection.cursor()
sql = "select COALESCE(max(online),0) as max_online from stat_online where date(stat_time) = '%s' and server_id = '%s'" % (date,values['server_id'])
data = cursor.fetchone(sql)
if not data and data['max_online'] is None:
values['max_online'] = 0
else:
values['max_online'] = int(data['max_online'])
except Exception,ex:
self.log(ex)
finally:
if cursor is not None:
cursor.close()
statServerData = StatServerDataCountry()
statServerData.country_code=country
for k, v in values.iteritems():
def transaction(cls):
if not cls._cursor:
cls._cursor = connection.cursor()
info('start transaction...')
cls._cursor.execute('start transaction;')
cls._transaction = True
def get_stat_server(begindate, enddate=None):
statServerDatas = []
if begindate == date_utils.get_date():
statServerDate = StatServerDate(False)
statServerDate.start()
statServerDatas = statServerDate.statServerDatass
elif enddate is not None:
sql = (
"SELECT server_name,server_id, sum(new_register) as new_register, sum(create_user) as create_user, sum(date_active) as date_active, sum(pay_people) as pay_people, sum(pay_money) as pay_money, sum(max_online) as max_online, sum(new_user_pay) as new_user_pay\
, sum(old_user_pay) as old_user_pay, sum(new_payer) as new_payer, sum(old_payer) as old_payer FROM stat_server_data where stat_date>= '%s' and stat_date<='%s' group by server_id;"
% (begindate, enddate)
)
cursor = connection.cursor()
try:
datas = cursor.fetchall(sql)
for data in datas:
statServerDatas.append(data)
finally:
cursor.close()
else:
sql = "SELECT * from stat_server_data where stat_date = '%s'" % begindate
cursor = connection.cursor()
try:
datas = cursor.fetchall(sql)
for data in datas:
statServerDatas.append(data)
finally:
cursor.close()
# statServerDatas = StatServerData.query(condition="stat_date ='%s'" % begindate)
total = 0
statServerDatas_s = []
for s in statServerDatas:
if not s:
continue
try:
server_id = s["server_id"]
except:
server_id = s.server_id
server = Server.load(server_id=server_id)
try:
if server and server.is_test == 0:
statServerDatas_s.append(s)
total += s["pay_money"]
if s["pay_people"] != 0 and s["date_active"] != 0:
value = round(s["pay_people"] / (s["date_active"]), 5) * 100
s.temp = value
else:
s.temp = 0
except:
if server and server.is_test == 0:
total += s.pay_money
if s.pay_money != 0 and s.date_active != 0:
value = round(s.pay_money / (s.date_active), 5) * 100
s.temp = value
else:
s.temp = 0
stat_server_datas_map = {}
for s in statServerDatas_s:
if not s:
continue
try:
server_id = s["server_id"]
except:
server_id = s.server_id
server = Server.load(server_id=server_id)
server_zone = server.server_zone
server_zone_server_list = stat_server_datas_map.get(server_zone, [])
server_zone_server_list.append(s)
stat_server_datas_map[server_zone] = server_zone_server_list
items = stat_server_datas_map.items()
items.sort()
for k, values in stat_server_datas_map.iteritems():
try:
server_id = s["server_id"]
values = sorted(values, cmp=lambda x, y: cmp(int(x["server_id"][1:]), int(y["server_id"][1:])))
except:
values = sorted(values, cmp=lambda x, y: cmp(int(x.server_id[1:]), int(y.server_id[1:])))
stat_server_datas_map[k] = values
# statServerDatas_s.sort(cmp=lambda x,y:cmp(int(x.server_id[1:]), int(y.server_id[1:])))
return stat_server_datas_map, total, server_zone_map
def _init_meta(cls):
if hasattr(cls, 'inited') and getattr(cls, 'inited'):
return
info('-' * 100)
info('start to init meta[%s]...' % cls.__name__)
info('-' * 100)
if not hasattr(cls, '_table') or getattr(cls, '_table') is None:
cls._table = cls._parse_table()
meta = cls._cache.get(cls._table)
if meta:
#try get from cache
info('meta hit in cache, use it!!!')
Persistable._meta_cache[cls._table] = meta
cls.inited = True
return
if cls._table in Persistable._meta_cache:
return
sql = 'show columns from %s'
cursor = connection.cursor()
try:
try:
data = cursor.fetchall(sql % cls._table)
except ProgrammingError:
raise TableNotExistException(cls._table)
meta = Meta(cls._table)
column_infos = data.to_list()
for column_info in column_infos:
field = column_info['Field']
default = column_info['Default']
column_type = column_info['Type']
extra = column_info['Extra']
if column_type.startswith(Column.TYPE_INT):
temp_type = column_type[:len(Column.TYPE_INT)]
length = column_type[len(Column.TYPE_INT) + 1:-1]
column_type = temp_type
elif column_type.startswith(Column.TYPE_VARCHAR):
temp_type = column_type[:len(Column.TYPE_VARCHAR)]
length = column_type[len(Column.TYPE_VARCHAR) + 1:-1]
column_type = temp_type
else:
length = None
key = column_info['Key']
if key == 'UNI' or key == 'PRI':
is_unique = True
else:
is_unique = False
column = Column(field=field, column_type=column_type, length=length, \
is_unique=is_unique, default=default, extra=extra)
meta.add_column(column)
cls._cache.set(cls._table, meta)
Persistable._meta_cache[cls._table] = meta
except:
info(traceback.format_exc())
exception_mgr.on_except()
raise PersistableException()
finally:
cursor.close()
cls.inited = True