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
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) }
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
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
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
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
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)
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)
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
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)
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
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
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
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
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)
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
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]
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
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]
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
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
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)
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)
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
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
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)
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
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)
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
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)
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)
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
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
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)
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)
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
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
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)
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
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
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)
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
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
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
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
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
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
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
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