import datetime from flatten_json import flatten import json import requests import sqlite_utils import logging from requests.models import HTTPError # TODO : # 1. Run in Load apps only mode. # 2. Run Daily Batch # 3. Update Deleted apps into table. # 4. --- Publish to heroku datasatte --- # 5. Write Queries0 # 6. Parse Reviews and store # 7. SQLite Maintenance # 8. Support URL_BASE='https://gplayapi.herokuapp.com/api/apps/' sqlite_db = sqlite_utils.Database("loanapps.db") if (sqlite_db['loanapp_playdata'] is None): sqlite_db['loanapp_playdata'].create({"id":str,}) if (sqlite_db['loanapp_permissions'] is None): sqlite_db['loanapp_permissions'].create({"id":str,}) if (sqlite_db['loanapp_deletedapps'] is None): sqlite_db['loanapp_deletedapps'].create({"id":str, "appId":str, "date":datetime}) otherApps = [] def getRelatedApps(devUrl,APP_NAME): relappdata = requests.get(devUrl).json() apps = [] logging.info(relappdata) if relappdata is not None and ('message' not in relappdata or relappdata['message'] != 'App not found (404)'): logging.info(relappdata['apps']) for app in relappdata['apps']: apps.append(app['appId'].strip()) apps.remove(APP_NAME) return apps def processApp(appName, checkDeveloper=False): logging.info('processApp for ' + appName) #if sqlite_db['loanapp_playdata'].search() json_data = requests.get(URL_BASE+appName).json() if ('message' in json_data and json_data['message'] == 'App not found (404)'): logging.info('App not found ' + appName) sqlite_db.table('loanapp_deletedapps').upsert({"appId":appName, "date": datetime.date.today()}, hash_id="id") logging.info('Inserted to deleted apps ' + appName) return #continue relatedApps = [] if checkDeveloper: relatedApps = getRelatedApps(json_data['developer']['url'].strip(),appName) data=flatten(json_data,'|') sqlite_db.table('loanapp_playdata3', alter=True).upsert(data, hash_id="id") permissions_json_data = requests.get(URL_BASE+appName+'/permissions').json() for perm in permissions_json_data['results']: perm['appId'] = appName sqlite_db.table('loanapp_permissions', alter=True).upsert(perm, hash_id="id") return relatedApps reviews_json_data = requests.get(URL_BASE+appName+'/reviews').json() def searchPlay(): #['cash loan', 'paisa loan', 'rupee loan', 'kredit loan', 'kash loan'] search_terms = ['instant loan', 'instant cash', 'instant ruppee', 'instant rupee' 'cash loan', 'paisa loan', 'rupee loan', 'kredit loan', 'kash loan', 'cash loan', 'paisa loan', 'rupee loan', 'kredit loan', 'kash loan'] appsearchdata = [] for term in search_terms: API_URL = URL_BASE + '?q=' + term + '&country=in&num=250' for result in requests.get(API_URL).json()['results']: appsearchdata.append(result['appId']) appsearchdata = list(set(appsearchdata)) file1 = open('applist.txt', 'r') apps = list(set(file1.readlines())) apps1 = [] for app in apps: apps1.append(app.strip().split("\n")[0]) appsearchdata = list(set(appsearchdata) - set(apps1)) print(appsearchdata) return appsearchdata def main(): logging.info('==== Start of Run ====') # file1 = open('RzpPayments.txt', 'r') # apps = list(set(file1.readlines())) # for app in apps: # app = app.strip() # json_data = requests.get(URL_BASE+app).json() # if ('message' in json_data and json_data['message'] == 'App not found (404)'): # logging.info('App not found ' + app) # else: # print(json_data['playstoreUrl'] + " | " + json_data['title'] + " | " + str(json_data['maxInstalls'])) # return file1 = open('NewApps_Feb21.txt', 'r') apps = list(set(file1.readlines())) for app in apps: app = app.strip() relatedApps = processApp(app,False) if relatedApps is not None: for app in relatedApps: processApp(app,False) logging.info('==== End of Run ====') if __name__ == "__main__": logging.basicConfig(filename='LoanApp.log', format='%(asctime)s %(message)s', level=logging.INFO) #main() searchPlay() #select appId, title, installs, minInstalls, maxInstalls, score, scoreText, ratings, [histogram|1], [histogram|2], [histogram|3], [histogram|4], [histogram|5], size, androidVersion, androidVersionText, [developer|devId], [developer|url], developerId, developerEmail, developerWebsite, privacyPolicy, released, date(updated/1000, 'unixepoch', 'localtime') as lastUpdated, version, playstoreUrl, permissions, similar developerAddress from loanapp_playdata order by appId limit 101 #SELECT appId, scoreText, maxInstalls, CAST(ratings AS REAL)/CAST(maxInstalls as REAL) rateRatio, CAST([histogram|5] AS REAL)/CAST(ratings AS REAL) five_star_ratio, CAST([histogram|1] AS REAL)/CAST(ratings AS REAL) as one_star_ratio from loanapp_playdata order by maxInstalls desc; # select * from loanapp_deletedapps where appId = 'com.financebuddha.financebuddha' # select count(*) from loanapp_deletedapps; # select * from loanapp_permissions; # select appId, count(appId) c from loanapp_deletedapps group by appId; # select appId, privacyPolicy from loanapp_playdata where appId in (select appId from loanapp_playdata where count(privacyPolicy) > 1 group by privacyPolicy); # select count(appId) coun, privacyPolicy from loanapp_playdata group by privacyPolicy order by coun desc # SELECT appId from loanapp_playdata where privacyPolicy='https://cash.in-mi.in:8100/cashbook/policy/index.html' # select * from loanapp_playdata where privacyPolicy='http://www.cloudloan.xyz/privacyPolicy.html' # delete from loanapp_playdata_archive # where rowid not in # ( # select min(rowid) # from loanapp_playdata_archive # group by # appId # ) ############################################# ### Remove Duplicates in deleted apps # delete from loanapp_deletedapps # where rowid not in # ( # select min(ROWID) # from loanapp_deletedapps # group by # appId # ) ############################################# # select DISTINCT appId from loanapp_permissions; # select DISTINCT appId from loanapp_playdata; # for row in sqlite_db["loanapp_playdata_archive"].rows: # sqlite_db.table('loanapp_playdata_archive_1', alter=True).upsert(row, hash_id="id")