Пример #1
0
def creatNewCharacter(nickname ,profession ,userId,sex=1):
    """创建新的角色
    @param nickname: str 角色的昵称
    @param profession: int 角色的职业编号
    @param userId: int 用户的id
    @param fieldname: str 用户角色关系表中的字段名,表示用户的第几个角色
    """
    nowdatetime = str(datetime.datetime.today())
    sql = "insert into `tb_character`(nickName,profession,sex,createtime) \
    values('%s',%d,%d,'%s')"%(nickname ,profession,sex,nowdatetime)
    sql2 = "SELECT @@IDENTITY"
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = cursor.execute(sql)
    conn.commit()
    cursor.execute(sql2)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    if result and count:
        characterId = result[0]
        InsertUserCharacter(userId,characterId)
        return characterId
    else:
        return 0
Пример #2
0
def getStatistics():
    """获取单服总数据
    """
    sql1 = "SELECT COUNT(id) FROM tb_register;"  #总注册数
    sql2 = "SELECT COUNT(id) FROM tb_character;"  #总创建数
    sql3 = "SELECT COUNT(DISTINCT uid) FROM tb_recharge WHERE boo=1;"  #付费人数
    sql4 = "SELECT SUM(rbm) FROM tb_recharge WHERE boo=1;"  #总付费人数
    conn = dbpool.connection()
    cursor = conn.cursor()
    cursor.execute(sql1)
    result1 = cursor.fetchone()[0]
    cursor.execute(sql2)
    result2 = cursor.fetchone()[0]
    cursor.execute(sql3)
    result3 = cursor.fetchone()[0]
    cursor.execute(sql4)
    result4 = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    return {
        'reg_cnt': 0 if not result1 else result1,
        'role_cnt': 0 if not result2 else result2,
        'fu_cnt': 0 if not result3 else result3,
        'income': 0.0 if not result4 else float(result4)
    }
Пример #3
0
def getDayRecordList(index, limit=10):
    """获取每日的记录
    """
    sql = "SELECT * FROM tb_statistics ORDER BY recorddate DESC LIMIT %s,%s;" % (
        (index - 1) * limit, index * limit)
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    recordlist = []
    for daterecord in result:
        recorddate = daterecord['recorddate']
        IncomeInfo = getIncomeByDate(recorddate)
        info = daterecord
        info['f_arpu'] = 0 if not IncomeInfo[
            'cnt'] else IncomeInfo['goal'] / IncomeInfo['cnt']
        info['z_arpu'] = 0 if not info[
            'createrole'] else IncomeInfo['goal'] / info['createrole']
        info['pay_rate'] = 0 if not info[
            'loginuser'] else IncomeInfo['cnt'] * 100 / info['loginuser']
        info['r_rate'] = 0 if not info[
            'createrole'] else info['loginuser'] * 100 / info['createrole']
        info['recorddate'] = str(info['recorddate'])
        info['pay_cnt'] = IncomeInfo['cnt']
        info['pay_goal'] = IncomeInfo['goal']
        info.update(getDayConsume(recorddate))
        recordlist.append(info)
    return recordlist
Пример #4
0
def getDayRecordList(index,limit = 10):
    """获取每日的记录
    """
    sql ="SELECT * FROM tb_statistics ORDER BY recorddate DESC LIMIT %s,%s;"%((index-1)*limit,index*limit)
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    recordlist = []
    for daterecord in result:
        recorddate = daterecord['recorddate']
        IncomeInfo = getIncomeByDate(recorddate)
        info = daterecord
        info['f_arpu'] = 0 if not IncomeInfo['cnt'] else IncomeInfo['goal']/IncomeInfo['cnt']
        info['z_arpu'] = 0 if not info['createrole'] else IncomeInfo['goal']/info['createrole']
        info['pay_rate'] = 0 if not info['loginuser'] else IncomeInfo['cnt']*100/info['loginuser']
        info['r_rate'] = 0 if not info['createrole'] else info['loginuser']*100/info['createrole']
        info['recorddate'] = str(info['recorddate'])
        info['pay_cnt'] = IncomeInfo['cnt']
        info['pay_goal'] = IncomeInfo['goal']
        info.update(getDayConsume(recorddate))
        recordlist.append(info)
    return recordlist
Пример #5
0
def DBTest():
    sql = """SELECT * FROM tb_item WHERE characterId=1000001;"""
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass = DictCursor)
    cursor.execute(sql)
    result=cursor.fetchall()
    cursor.close()
    conn.close()
    return result
Пример #6
0
def DBTest():
    sql = """SELECT * FROM tb_item WHERE characterId=1000001;"""
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result
Пример #7
0
def excuteSQL(tablename,sql):
    conn = dbpool.connection(write=True,tablename=tablename)
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception,e:
        log.err(e,traceback.format_exc())
        log.err(sql)
Пример #8
0
def excuteSQL(tablename, sql):
    conn = dbpool.connection(write=True, tablename=tablename)
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception, e:
        log.err(e, traceback.format_exc())
        log.err(sql)
Пример #9
0
def CheckUserInfo(Uid):
    """检测用户信息"""
    sql = "SELECT * from tb_register where username = '******'"%Uid
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result
Пример #10
0
def InsertIntoDB(tablename,data):
    """写入数据库
    """
    sql = forEachPlusInsertProps(tablename,data)
    conn = dbpool.connection(write=True,tablename=tablename)
    cursor = conn.cursor()
    count = cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()
    return bool(count)
Пример #11
0
def getALlCharacterBaseInfo():
    """获取所有的角色的基础信息
    """
    sql = "SELECT `id`,`level`,`profession`,`nickname` FROM tb_character;"
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result
Пример #12
0
def ReadDataFromDB(tablename):
    """
    """
    sql = """select * from %s"""%tablename
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass = DictCursor)
    cursor.execute(sql)
    result=cursor.fetchall()
    cursor.close()
    conn.close()
    return result
Пример #13
0
def InsertIntoDB(tablename,data):
    """写入数据库
    """
    sql = forEachPlusInsertProps(tablename,data)
    conn = dbpool.connection(write=True,tablename=tablename)
    cursor = conn.cursor()
    count = cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()
    return bool(count)
Пример #14
0
def getALlCharacterBaseInfo():
    """获取所有的角色的基础信息
    """
    sql = "SELECT `id`,`level`,`profession`,`nickname` FROM tb_character;"
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result
Пример #15
0
def ReadDataFromDB(tablename):
    """
    """
    sql = """select * from %s""" % tablename
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result
Пример #16
0
def GetOneRecordInfo(tablename, props):
    '''获取单条数据的信息
    '''
    props = FormatCondition(props)
    sql = """Select * from `%s` where %s""" % (tablename, props)
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result
Пример #17
0
def execsql(sql):
	"""
	"""
	conn = dbpool.connection()
	cursor = conn.cursor(cursorclass = DictCursor)
	result = None
	try:
		count = cursor.execute(sql)
		result=cursor.fetchall()
	except Exception,e:
		logger.error(e)
		logger.error(sql)
Пример #18
0
def getCharacterIdByNickName(nickname):
    """根据昵称获取角色的id
    @param nickname: string 角色的昵称
    """
    sql = "select id from `tb_character` where nickname ='%s'" % nickname
    conn = dbpool.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result
Пример #19
0
def getAllPkByFkInDB(tablename, pkname, props):
    """根据所有的外键获取主键ID
    """
    props = FormatCondition(props)
    sql = """Select `%s` from `%s` where %s""" % (pkname, tablename, props)
    conn = dbpool.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return [key[0] for key in result]
Пример #20
0
def GetOneRecordInfo(tablename,props):
    """获取单条数据的信息
    """
    props = FormatCondition(props)
    sql = """Select * from `%s` where %s"""%(tablename,props)
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass = DictCursor)
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result
Пример #21
0
def getAllPkByFkInDB(tablename,pkname,props):
    """根据所有的外键获取主键ID
    """
    props = FormatCondition(props)
    sql = """Select `%s` from `%s` where %s"""%(pkname,tablename,props)
    conn = dbpool.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return [key[0] for key in result]
Пример #22
0
def getUserCharacterInfo(characterId):
    """获取用户角色列表的所需信息
    @param id: int 用户的id
    """
    sql = "select town from tb_character where id = %d"%(characterId)
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result
Пример #23
0
def getCharacterIdByNickName(nickname):
    """根据昵称获取角色的id
    @param nickname: string 角色的昵称
    """
    sql = "select id from `tb_character` where nickname ='%s'"%nickname
    conn = dbpool.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result
Пример #24
0
def InsertIntoDB(tablename,data):
    """写入数据库
    """
    sql = forEachPlusInsertProps(tablename,data)
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception,e:
        log.err(e)
        log.err(sql)
Пример #25
0
def ReadDataFromDB(tablename):
	"""
	"""
	sql = """select * from %s"""%tablename
	conn = dbpool.connection()
	cursor = conn.cursor(cursorclass = DictCursor)
	result = None
	try:
		count = cursor.execute(sql)
		result=cursor.fetchall()
	except Exception,e:
		logger.error(e)
		logger.error(sql)
Пример #26
0
def InsertIntoDBAndReturnID(tablename,data):
    """写入数据库,并返回ID
    """
    sql = forEachPlusInsertProps(tablename,data)
    conn = dbpool.connection(write=True,tablename=tablename)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    cursor.execute("SELECT LAST_INSERT_ID();")
    result=cursor.fetchall()[0]
    cursor.close()
    conn.close()
    return result
Пример #27
0
def InsertUserCharacter(userId,characterId):
    """加入角色用户关系"""
    sql = "update tb_register set characterId = %d where `id` = %d"%( characterId ,userId)
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()
    if count >= 1:
        return True
    else:
        return False
Пример #28
0
def UpdateWithDict(tablename,props,prere):
    """更新记录
    """
    sql = forEachUpdateProps(tablename, props, prere)
    conn = dbpool.connection(write=True,tablename=tablename)
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception,e:
        log.err(e,traceback.format_exc())
        log.err(sql)
Пример #29
0
def creatUserInfo(username,password):
    """创建
    """
    sql = "insert into tb_register(username,`password`) values ('%s','%s')"%(username,password)
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()
    if(count >= 1):
        return True
    return False
Пример #30
0
def UpdateWithDict(tablename,props,prere):
    """更新记录
    """
    sql = forEachUpdateProps(tablename, props, prere)
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception,e:
        log.err(e)
        log.err(sql)
Пример #31
0
def getIncomeByDate(onedate):
    """获取某天的缴费情况
    """
    sql = "SELECT SUM(rbm) AS goal,COUNT(DISTINCT uid) AS cnt\
    FROM tb_recharge WHERE DATE(rtime)=DATE('%s') and boo=1;"%onedate
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    result['goal'] = float(result.get('goal')) if result.get('goal') else 0
    return result
Пример #32
0
def UpdateWithDict(tablename, props, prere):
    """更新记录
    """
    sql = forEachUpdateProps(tablename, props, prere)
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception, e:
        log.err(e)
        log.err(sql)
Пример #33
0
def UpdateWithDict(tablename,props,prere):
    """更新记录
    """
    sql = forEachUpdateProps(tablename, props, prere)
    conn = dbpool.connection(write=True,tablename=tablename)
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception,e:
        log.err(e,traceback.format_exc())
        log.err(sql)
Пример #34
0
def InsertIntoDB(tablename, data):
    """写入数据库
    """
    sql = forEachPlusInsertProps(tablename, data)
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception, e:
        log.err(e)
        log.err(sql)
Пример #35
0
def getIncomeByDate(onedate):
    """获取某天的缴费情况
    """
    sql = "SELECT SUM(rbm) AS goal,COUNT(DISTINCT uid) AS cnt\
    FROM tb_recharge WHERE DATE(rtime)=DATE('%s') and boo=1;" % onedate
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    result['goal'] = float(result.get('goal')) if result.get('goal') else 0
    return result
Пример #36
0
def InsertIntoDBAndReturnID(tablename,data):
    """写入数据库,并返回ID
    """
    sql = forEachPlusInsertProps(tablename,data)
    conn = dbpool.connection(write=True,tablename=tablename)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    cursor.execute("SELECT LAST_INSERT_ID();")
    result=cursor.fetchone()[0]
    cursor.close()
    conn.close()
    return result
Пример #37
0
def DeleteFromDB(tablename, props):
    '''从数据库中删除
    '''
    prers = FormatCondition(props)
    sql = """DELETE FROM %s WHERE %s ;""" % (tablename, prers)
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception, e:
        log.err(e)
        log.err(sql)
Пример #38
0
def DeleteFromDB(tablename,props):
    '''从数据库中删除
    '''
    prers = FormatCondition(props)
    sql = """DELETE FROM %s WHERE %s ;"""%(tablename,prers)
    conn = dbpool.connection(write=True,tablename=tablename)
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception,e:
        log.err(e,traceback.format_exc())
        log.err(sql)
Пример #39
0
def getDayConsume(onedate):
    """获取某天的消费情况
    """
    sql = "SELECT * FROM tb_bill WHERE DATE(recordDate)=DATE('%s');"%onedate
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    info = {}
    info['cons_goal'] = sum([record['spendGold'] for record in result])
    info['user_cnt'] = len(set([record['characterId'] for record in result]))
    return info
Пример #40
0
def checkCharacterName(nickname):
    """检测角色名是否可用
    @param nickname: str 角色的名称
    """
    sql = "SELECT `id` from tb_character where nickname = '%s'"%nickname
    conn = dbpool.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    if result:
        return False
    return True
Пример #41
0
def executemany(sql, data):
	"""
	"""
	conn = dbpool.connection()
	cursor = conn.cursor()
	result = None
	count = 0
	try:
		count = cursor.executemany(sql, data)
		print('executemany', sql, data)
		conn.commit()
	except Exception,e:
		logger.error(e)
		logger.error(sql)
Пример #42
0
def getUserInfoByUsername(username,password):
    """检测用户名户密码
    @param username: str 用户的用户名
    @param password: str 用户密码
    """
    sql = "select * from `tb_register` where username = '******'\
     and password = '******'" %( username, password)
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result
Пример #43
0
def getDayConsume(onedate):
    """获取某天的消费情况
    """
    sql = "SELECT * FROM tb_bill WHERE DATE(recordDate)=DATE('%s');" % onedate
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass=DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    info = {}
    info['cons_goal'] = sum([record['spendGold'] for record in result])
    info['user_cnt'] = len(set([record['characterId'] for record in result]))
    return info
Пример #44
0
def DeleteFromDB(tablename,props):
    """从数据库中删除
    """
    prers = FormatCondition(props)
    sql = """DELETE FROM %s WHERE %s ;"""%(tablename,prers)
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = 0
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception,e:
        log.err(e)
        log.err(sql)
Пример #45
0
def GetTableIncrValue(tablename):
    """
    """
    database = dbpool.config.get('db')
    sql = """SELECT AUTO_INCREMENT FROM information_schema.`TABLES` \
    WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s';"""%(database,tablename)
    conn = dbpool.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    if result:
        return result[0]
    return result
Пример #46
0
def creatUserCharacter(uid):
    """为新用户建立空的用户角色关系记录
    @param id: int 用户id
    """
    sql = "insert into `tb_user_character` (`id`) values(%d)" %uid
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()
    if count >= 1:
        return True
    else:
        return False
Пример #47
0
def GetTableIncrValue(tablename):
    """
    """
    database = dbpool.config.get('db')
    sql = """SELECT AUTO_INCREMENT FROM information_schema.`TABLES` \
    WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s';""" % (database, tablename)
    conn = dbpool.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    if result:
        return result[0]
    return result
Пример #48
0
def GetRecordList(tablename,pkname,pklist):
    """
    """
    pkliststr = ""
    for pkid in pklist:
        pkliststr+="%s,"%pkid
    pkliststr = "(%s)"%pkliststr[:-1]
    sql = """SELECT * FROM `%s` WHERE `%s` IN %s;"""%(tablename,pkname,pkliststr)
    conn = dbpool.connection(write=False,tablename=tablename)
    cursor = conn.cursor(cursorclass = DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result
Пример #49
0
def GetRecordList(tablename,pkname,pklist):
    """
    """
    pkliststr = ""
    for pkid in pklist:
        pkliststr+="%s,"%pkid
    pkliststr = "(%s)"%pkliststr[:-1]
    sql = """SELECT * FROM `%s` WHERE `%s` IN %s;"""%(tablename,pkname,pkliststr)
    conn = dbpool.connection()
    cursor = conn.cursor(cursorclass = DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result
Пример #50
0
def checkUserPassword(username,password):
    """检测用户名户密码
    @param username: str 用户的用户名
    @param password: str 用户密码
    """
    sql = "select id from `tb_register` where username = '******' and password = '******'" %( username, password)
    conn = dbpool.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    pid = 0
    if result:
        pid = result[0]
    return pid
Пример #51
0
def updatePlayerDB(player):
    """更新角色的数据库信息"""
    characterId = player.baseInfo.id
    props = {'level':player.level.getLevel(),'coin':player.finance.getCoin(),
             'exp':player.level.getExp(),'hp':player.attribute.getHp()}
    sqlstr = util.forEachUpdateProps('tb_character',props, {'id':characterId})
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = cursor.execute(sqlstr)
    conn.commit()
    cursor.close()
    conn.close()
    if count >= 1:
        return True
    else:
        log.err(sqlstr)
        return False
Пример #52
0
def updateUserCharacter(userId ,fieldname ,characterId):
    """更新用户角色关系表
    @param userId: 用户的id
    @param fieldname: str 用户角色关系表中的字段名,表示用户的第几个角色
    @param characterId: int 角色的id
    """
    sql = "update `tb_user_character` set %s = %d where id = %d"%(fieldname ,characterId ,userId)
    conn = dbpool.connection()
    cursor = conn.cursor()
    count = cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()
    if count >= 1:
        return True
    else:
        return False