123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134 |
- # -*- coding: future_fstrings -*-
- import os
- import sys
- import zipfile
- import gzip
- import pymysql
- import datetime
- import time
- import shutil
- downloadDir = "ss_upload"
- dbTableName = 'snipershooting'
- conn = pymysql.connect(
- host='localhost',
- port=6002,
- user='root',
- passwd='root',
- db='devrecord',
- charset='utf8'
- )
- def unzip_file(zip_src, dst_dir):
- r = zipfile.is_zipfile(zip_src)
- if r:
- fz = zipfile.ZipFile(zip_src)
- #print("zip file list=",len(fz.namelist()))
- for file in fz.namelist():
- #print("zip file name=",file)
- fz.extract(file, dst_dir)
- else:
- print('This is not zip')
- def list_all_files(rootdir):
- _files = []
- if os.path.exists(rootdir):
- fileList = os.listdir(rootdir)
- for f in fileList:
- if f=="$RECYCLE.BIN" or f=="System Volume Information":
- continue
- path = os.path.join(rootdir, f)
- if os.path.isdir(path):
- _files.extend(list_all_files(path))
- if os.path.isfile(path):
- _files.append(path)
- return _files
- def remove_empty_dir(rootdir):
- if os.path.exists(rootdir):
- fList = os.listdir(rootdir)
- parentdir = os.path.dirname(rootdir)
- #print("parentdir=",parent)
- if len(fList) == 0:
- os.rmdir(rootdir)
- #print("remove dir=", rootdir)
- remove_empty_dir(parentdir)
- else:
- for f in fList:
- fname = os.path.join(rootdir, f)
- #print("fname=",fname)
- if os.path.isdir(fname):
- remove_empty_dir(fname)
- def decodeFile(filename):
- f_gz = gzip.open(filename, 'rb')
- zipData = f_gz.read()
- #print(zipData)
- params = zipData.decode('UTF-8').split('#')
- sql = buildSql(params)
- #print("sql=",sql)
- f_gz.close()
- if insertDB(sql):
- os.remove(filename)
- def buildSql(sqlParams):
- updateTime = datetime.datetime.fromtimestamp(int(sqlParams[1]))
- firstLoginTime = datetime.datetime.fromtimestamp(int(sqlParams[9]))
- lastLoginTime = datetime.datetime.fromtimestamp(int(sqlParams[10]))
- paramDict = {'userid': sqlParams[0], 'upload_time': updateTime, 'app_version':sqlParams[2], 'groupid':sqlParams[3],
- 'max_level':int(sqlParams[4]), 'playerScore':int(sqlParams[5]), 'playerCrown':int(sqlParams[6]),
- 'playerDollar':int(sqlParams[7]), 'loginCount':int(sqlParams[8]), 'first_login_time':firstLoginTime,
- 'last_login_time':lastLoginTime,'online_time':sqlParams[11],'app_country':sqlParams[12],'app_language':sqlParams[13],
- 'mobile_name':sqlParams[14], 'mobile_type':sqlParams[15], 'mobile_battery':sqlParams[16],
- 'mobile_screen':sqlParams[17], 'mobile_mem':sqlParams[18],'mobile_graphic_mem':sqlParams[19],
- "mobile_graphic_type":sqlParams[20],'network':sqlParams[21],'androidSys':sqlParams[22],
- 'performance':sqlParams[23],'levelResult':sqlParams[24]}
- sql = f"""insert into {dbTableName}
- (userid,client_userid,upload_time,app_version,groupid,max_level,first_login_time,last_login_time,
- online_time,app_country,app_language,mobile_name,mobile_type,mobile_battery,mobile_screen,mobile_mem,
- mobile_graphic_mem,mobile_graphic_type,network,androidSys,performance,levelResult,
- playerScore,playerDollar,playerCrown,loginCount)
- values ('{paramDict['userid']}','none','{paramDict['upload_time']}','{paramDict['app_version']}',
- '{paramDict['groupid']}',{paramDict['max_level']},'{paramDict['first_login_time']}','{paramDict['last_login_time']}',
- {paramDict['online_time']},'{paramDict['app_country']}','{paramDict['app_language']}','{paramDict['mobile_name']}',
- '{paramDict['mobile_type']}',{paramDict['mobile_battery']},'{paramDict['mobile_screen']}',
- '{paramDict['mobile_mem']}','{paramDict['mobile_graphic_mem']}','{paramDict['mobile_graphic_type']}',
- '{paramDict['network']}','{paramDict['androidSys']}','{paramDict['performance']}','{paramDict['levelResult']}',
- {paramDict['playerScore']},{paramDict['playerDollar']},{paramDict['playerCrown']},
- {paramDict['loginCount']})"""
- return sql
- def insertDB(sql):
- success = False
- cursor = conn.cursor()
- try:
- cursor.execute(sql)
- conn.commit()
- success = True
- except Exception as e:
- conn.rollback()
- print("insertDB except",str(e))
- finally:
- cursor.close()
- return success
- def queryDB():
- cursor = conn.cursor()
- sql = 'select * from snipershooting'
- rows = cursor.execute(sql)
- print(cursor.fetchmany(3))
- cursor.close()
-
- def main():
- files = list_all_files(downloadDir)
- for f in files:
- print("filename=",f)
- decodeFile(f)
- conn.close()
- #remove_empty_dir(downloadDir)
- if __name__ == '__main__':
- main()
|