SniperShooting/snipershooting/firebase_py_download/uploadDataToDB.py

# -*- 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()