#-*-coding:gbk-*-# pubwin member repare tools# change member level by score# code by spriteimport pymssqlimport _mssqlimport uuidimport decimalimport osfrom time import sleepdef getpath():    #获取当地程序所在目录    return os.path.abspath(‘.‘)    def dbtest(dbpasswd):    #检查数据库是否可以登录    cmd="osql -U netcafe -P "+dbpasswd+" -Q "+"\""+        ""+"\""    a=os.system(cmd)    return a    def databasecon(passwd=‘123456‘):    #返回数据库连接字串    try:        con=pymssql.connect(host=‘.‘,user=‘netcafe‘,        password=passwd,database=‘local‘)    except:        return 0    return con            def dbattach(opr=1,dbpasswd=‘123456‘):    #1 附加程序目录下的local_db.mdf文件数据库,0表示成功    #opr=0 表示分离souredb    path=getpath()    dbmdfpath=path+‘\\‘+‘local_Data.MDF‘    dbldfpath=path+‘\\‘+‘local_log.LDF‘    if opr==1:        cmd="osql -U netcafe -P "+dbpasswd+" -Q "+"\""+        "sp_attach_db @dbname=‘souredb‘,@filename1="+        "‘"+dbmdfpath+"‘,"+"@filename2="+"‘"+dbldfpath+"‘"+"\""    else:        cmd="osql -U netcafe -P "+dbpasswd+" -Q "+"\""+        "sp_detach_db @dbname=‘souredb‘"+"\""    a=os.popen(cmd)    aline=a.readlines()    if len(aline)==0:        return 0    else:        return 1    def creatmembertable(dbpassword):    #将soure库里的会员库提取到local库新建的member表里，soredb库必须附加成功才能合并    #isplus为Y时合并相同会员金额,为N时直接删除相同卡号会员    con=databasecon(dbpassword)    if con==0:return 0    try:        cur=con.cursor()        cur.execute("select * into local.dbo.member         from souredb.dbo.mem_localmemberbaseinfo")        con.commit()    except:        con.close()        return 0    con.close()    return 1    def getmaxidinlocalmember(password):    #获得local表最大id,后面的加id可以精准添加,以免报id唯一性错误    con=databasecon(password)    if con==0:return 0    cur=con.cursor()    cur.execute(‘select max(id) from mem_localmemberbaseinfo‘)    a=cur.fetchall()    con.close()    if a[0][0]>0:        return a[0][0]    return 0def memberblanceplus(dbpassword):    con=databasecon(dbpassword)    plussql=‘update mem_localmemberbaseinfo set     balance=mem_localmemberbaseinfo.balance+a.balance,    score=mem_localmemberbaseinfo.score+a.score     from member as a     where mem_localmemberbaseinfo.uniqueid=a.uniqueid‘    if con==0:return 0    try:        cur=con.cursor()        cur.execute(plussql)        con.commit()    except:        con.close()        return 0    con.close()    return 1    def memberjoin(dbpassword):    con=databasecon(dbpassword)    if con==0:return 0    maxid=(getmaxidinlocalmember(dbpassword))    delsql=‘delete member where uniqueid in    (select uniqueid from mem_localmemberbaseinfo)‘    idplussql=‘update member set id=id+‘+str(maxid)    insql=‘insert into  mem_localmemberbaseinfo     (level,levelid,realname,balance,score,updatetm,location,    certificateid,sex,birthday,country,password,validtm,opentm,certificatetype,id,uniqueid)     select level,levelid,realname,balance,score,updatetm,location,    certificateid,sex,birthday,country,password,validtm,opentm,certificatetype,id,uniqueid from member‘    try:        cur=con.cursor()        cur.execute(delsql)        if maxid != 0:cur.execute(idplussql)        cur.execute(insql)        con.commit()    except:        con.close()        return 0    con.close()    return 1    def dropmembertable(dbpassword):    con=databasecon(dbpassword)    if con==0:return 0    dropsql=‘drop table member‘    try:        cur=con.cursor()        cur.execute(dropsql)        con.commit()    except:        con.close()        return 0    con.close()    return 1    def initpro():    os.system("cls")    print ‘‘    print "*"*55    print "            pubwin会员合并程序         "    print "*"*55    print ""    print "   合并前请先停止pubwin服务，启动数据库服务"    print "请将要合并数据文件local_Data.MDF,local_log.LDF放在程序目录下"    print "   合并前请备份数据库,合并不成功请换回原来库"    print ‘‘    print ‘*‘*55    print "\n"    print "请输入数据库密码"while 1:    initpro()    dbpasswd=raw_input()    if dbtest(dbpasswd)==0:        print "数据库连接成功"        break    else:        print "数据库连接失败"        sleep(2)attach=dbattach(1,dbpasswd)if attach!=0:    #返回值有问题，不管成功不成功都会返回0，用别的方法判断    print ‘x‘*30    print "要合并的数据库无法附加\n请将数据文件local_Data.MDF,    local_log.LDF放在程序目录下\n程序将自动退出"    print ‘x‘*30    sleep(3)    exit()ismembertables=creatmembertable(dbpasswd)if ismembertables==0:    print "源库会员表无法提取或是网吧数据库错误\n程序将自动退出"    attach=dbattach(0,dbpasswd)    sleep(3)    exit()print "源库会员库数据提取成功"attach=dbattach(0,dbpasswd)while 1:    print "*"*30    print "同卡号会员金额是否相加,(Y/N)"    isplus=raw_input()    if isplus ==‘Y‘ or isplus ==‘N‘:break    if isplus==‘Y‘:    isplussuccess=memberblanceplus(dbpasswd)    if isplussuccess==0:        print ‘会员金额相加失败，程序将自动退出‘        sleep(3)        exit()    print ‘会员金额相加成功‘print ‘开始合并数据库‘if memberjoin(dbpasswd)==0:    dropmembertable(dbpasswd)    print ‘会员合并失败，程序将自动退出‘    sleep(3)    exit()dropmembertable(dbpasswd)print ‘合并成功,请勿重复操作‘print ‘‘print ‘press any key to exit‘os.system("pause>>null")