新浪sae搭建游戏分数排行榜

createtable.sql

CREATE TABLE `ScoreRank` (
  `Name` varbinary(64) NOT NULL,
  `Score` int(11) DEFAULT NULL,
  `Level` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`Name`,`Level`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

index.wsgi

# -*- coding: utf-8 -*-
import sae
import sae.const
import MySQLdb
import urllib

db = None
cursor = None

def connDB():
    global db
    global cursor
    sae.const.MYSQL_DB      # 数据库名
    sae.const.MYSQL_USER    # 用户名
    sae.const.MYSQL_PASS    # 密码
    sae.const.MYSQL_HOST    # 主库域名（可读写）
    sae.const.MYSQL_PORT    # 端口，类型为<type 'str'>，请根据框架要求自行转换为int
    sae.const.MYSQL_HOST_S  # 从库域名（只读）
    db=MySQLdb.connect(db=sae.const.MYSQL_DB,user=sae.const.MYSQL_USER,passwd=sae.const.MYSQL_PASS,host=sae.const.MYSQL_HOST,port=int(sae.const.MYSQL_PORT),charset="utf8")
    cursor=db.cursor()

def existName(name,level):
    cursor.execute("""SELECT count(Name) FROM ScoreRank WHERE Name=%s and Level=%s""",(name,level))
    tmp = cursor.fetchone()
    print("existName:",name,tmp)
    if tmp[0]>0:
        return True
    return False

def hellopage(args):
    return "hello world"

def gettoprank(args):
    result = "{}"
    if "level" in args:
        level = args['level']
        connDB()

        cursor.execute("""SELECT Name,Level,Score FROM ScoreRank  WHERE Level=%s ORDER BY Score DESC limit 10""",(level,))
        result = "return {"
        rank = 0
        for row in cursor.fetchall(): 
            rank += 1
            result += "{\""+str(rank)+"\","
            for r in row:
                result += "\""+str(r)+"\","
            result += "},"
        result += "}"

    return result

def addname(args):
    result = "error:"
    if "name" in args:
        name = args["name"]
        connDB()
        if existName(name,1):
            result = "error:1"
        else:
            n = cursor.executemany("""INSERT INTO ScoreRank (Name,Score,Level) Values(%s,%s,%s)""",[(name,0,1),(name,0,2),(name,0,3),(name,0,4),(name,0,5)])
            db.commit()
            result = "ok:"+str(n)
    return result

def updatescore(args):
    result = "error:"
    if "name" in args and "score" in args and "level" in args:
        name = args["name"]
        score = args["score"]
        level = args["level"]
        connDB()
        addname(args)
        n = cursor.execute("""UPDATE ScoreRank SET Score=%s WHERE Name=%s and Level=%s """,(score,name,level))
        db.commit()
        result = "ok" + str(n)
    return result

def getmyrank(args):
    result = "0"
    if "name" in args and "level" in args:
        name = args["name"]
        level = args['level']
        connDB()
        rank = 0
        if existName(name,level):
            cursor.execute("""SELECT count(Score) FROM ScoreRank WHERE Level=%s and Score>(select score from ScoreRank where Name=%s and Level=%s)""",(level,name,level,))
            tmp = cursor.fetchone()
            print("tmp",tmp[0])
            rank = tmp[0]+1
        result = str(rank)

    return result

def app(environ, start_response):
    result = ""
    if environ['REQUEST_METHOD'] == 'GET' :
        path = environ['PATH_INFO']
        args = {}
        if environ['QUERY_STRING']: #接收get参数
             for qp in environ['QUERY_STRING'].split('&'):
                 kv = qp.split('=')
                 args[kv[0]] = urllib.unquote(kv[1]).decode("utf-8", 'ignore')
        actions = {
            '/':hellopage,
            '/gettoprank':gettoprank,
            '/getmyrank':getmyrank,
            '/addname':addname,
            '/updatescore':updatescore,
        }
        if path in actions:
            result = actions[path](args)

    start_response('200 ok', [('content-type', 'text/plain')])
    print("result:",result)
    return [result]

application = sae.create_wsgi_app(app)