def client(): client = Client() try: client.connect('127.0.0.1', 10801) yield client finally: client.close()
def client(connection_param): client = Client(partition_aware=True, timeout=CLIENT_SOCKET_TIMEOUT) try: client.connect(connection_param) yield client finally: client.close()
def client_routed(): client = Client(partition_aware=True) try: client.connect(client_routed_connection_string) yield client finally: client.close()
def client( ignite_host, ignite_port, timeout, use_ssl, ssl_keyfile, ssl_certfile, ssl_ca_certfile, ssl_cert_reqs, ssl_ciphers, ssl_version, username, password, ): client = Client( timeout=timeout, use_ssl=use_ssl, ssl_keyfile=ssl_keyfile, ssl_certfile=ssl_certfile, ssl_ca_certfile=ssl_ca_certfile, ssl_cert_reqs=ssl_cert_reqs, ssl_ciphers=ssl_ciphers, ssl_version=ssl_version, username=username, password=password, ) client.connect(ignite_host, ignite_port) yield client for cache_name in cache_get_names(client).value: cache_destroy(client, cache_name) client.close()
def export_users_csv(request): print("Hello") nodes = [ ('127.0.0.1', 10800), ('127.0.0.1', 10801), ] client = Client() client.connect(nodes) yesterday = date.today() - timedelta(days=1) datecsv = yesterday.strftime('%m/%d/%Y') print(datecsv) QUERY = ''' SELECT ID, APPID, EVENTS, DEVICE_ID, "TIMESTAMP", MTIMESTAMP, UPTIMESTAMP, IPADDRESS, CITY, COUNTRY, EVENTNAME, CREATED_DATE, CREATED_TIME FROM PUBLIC.EVENTSDATA WHERE APPID != '5def994ce96b09565e1f1ddd' AND CREATED_DATE = {} AND EVENTNAME!='_app_crash' AND ID!=855263 ORDER BY ID; '''.format("'"+datecsv+"'") print(QUERY) result = client.sql( QUERY, include_field_names=True, ) print(next(result)) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename="{}.csv"'.format(datecsv+"_IgniteEvents") writer = csv.writer(response) writer.writerow(['appid','deviceid','devicemodel','platform','apppackage','keyname','mobileoperator','app','song','album','pstate','source','ipaddress','city','station','duration','timestamp','to_char','created_time']) i = 0 for row in result: if i == 0: print(row) x = row[2] y = json.loads(x) if y.get("segmentation") is None: writer.writerow([row[1],row[3],y.get("d"),y.get("p"),y.get("ap"),row[10],y.get("network").get("ope"),"","","","","",row[7],row[8],"","",row[4],row[11],row[12]]) else: writer.writerow([row[1],row[3],y.get("d"),y.get("p"),y.get("ap"),row[10],y.get("network").get("ope"),y.get("segmentation").get("App"),y.get("segmentation").get("Song"),y.get("segmentation").get("Album"),y.get("segmentation").get("PState"),y.get("segmentation").get("Source"),row[7],row[8],y.get("segmentation").get("Station"),y.get("segmentation").get("Duration"),row[4],row[11],row[12]]) i = 1 else: print(row) x = row[5] try: y = json.loads(x) except: pass if y.get("segmentation") is None: writer.writerow([row[1],row[6],y.get("d"),y.get("p"),y.get("ap"),row[2],y.get("network").get("ope"),"","","","","",row[10],row[11],"","",row[7],row[3],row[4]]) else: writer.writerow([row[1],row[6],y.get("d"),y.get("p"),y.get("ap"),row[2],y.get("network").get("ope"),y.get("segmentation").get("App"),y.get("segmentation").get("Song"),y.get("segmentation").get("Album"),y.get("segmentation").get("PState"),y.get("segmentation").get("Source"),row[10],row[11],y.get("segmentation").get("Station"),y.get("segmentation").get("Duration"),row[7],row[3],row[4]]) client.close() return response
def test_handshake( monkeypatch, ignite_host, ignite_port, use_ssl, ssl_keyfile, ssl_certfile, ssl_ca_certfile, ssl_cert_reqs, ssl_ciphers, ssl_version, username, password, ): client = Client( use_ssl=use_ssl, ssl_keyfile=ssl_keyfile, ssl_certfile=ssl_certfile, ssl_ca_certfile=ssl_ca_certfile, ssl_cert_reqs=ssl_cert_reqs, ssl_ciphers=ssl_ciphers, ssl_version=ssl_version, username=username, password=password, ) client._socket = client._wrap( socket.socket(socket.AF_INET, socket.SOCK_STREAM)) client.socket.connect((ignite_host, ignite_port)) hs_request = HandshakeRequest(username, password) client.send(hs_request) hs_response = read_response(client) assert hs_response['op_code'] != 0 client.close() # intentionally pass wrong protocol version from pyignite.connection import handshake monkeypatch.setattr(handshake, 'PROTOCOL_VERSION_MAJOR', 10) client._socket = client._wrap( socket.socket(socket.AF_INET, socket.SOCK_STREAM)) client.socket.connect((ignite_host, ignite_port)) hs_request = HandshakeRequest(username, password) client.send(hs_request) hs_response = read_response(client) assert hs_response['op_code'] == 0 client.close()
def test_connection_context(connection_param, partition_aware): is_partition_aware = partition_aware == 'with_partition_aware' client = Client(partition_aware=is_partition_aware) # Check context manager with client.connect(connection_param): __check_open(client, is_partition_aware) __check_closed(client) # Check standard way try: client.connect(connection_param) __check_open(client, is_partition_aware) finally: client.close() __check_closed(client)
def test_handshake( monkeypatch, ignite_host, ignite_port, use_ssl, ssl_keyfile, ssl_certfile, ssl_ca_certfile, ssl_cert_reqs, ssl_ciphers, ssl_version, username, password, ): client = Client( use_ssl=use_ssl, ssl_keyfile=ssl_keyfile, ssl_certfile=ssl_certfile, ssl_ca_certfile=ssl_ca_certfile, ssl_cert_reqs=ssl_cert_reqs, ssl_ciphers=ssl_ciphers, ssl_version=ssl_version, username=username, password=password, ) client._socket = client._wrap( socket.socket(socket.AF_INET, socket.SOCK_STREAM) ) client.socket.connect((ignite_host, ignite_port)) hs_request = HandshakeRequest(username, password) client.send(hs_request) hs_response = read_response(client) assert hs_response['op_code'] != 0 client.close() # intentionally pass wrong protocol version from pyignite.connection import handshake monkeypatch.setattr(handshake, 'PROTOCOL_VERSION_MAJOR', 10) client._socket = client._wrap( socket.socket(socket.AF_INET, socket.SOCK_STREAM) ) client.socket.connect((ignite_host, ignite_port)) hs_request = HandshakeRequest(username, password) client.send(hs_request) hs_response = read_response(client) assert hs_response['op_code'] == 0 client.close()
def export_users_csv(): print("Hello") nodes = [ ('35.154.247.92', 10800), ('35.154.247.92', 10801), ] conn = psycopg2.connect( dbname="VTIONData", host="vtionproddb.chgz4nqwpdta.ap-south-1.rds.amazonaws.com", user="******", password="******") cur = conn.cursor() client = Client() client.connect(nodes) yesterday = date.today() - timedelta(days=1) yesterdayMidnight = int(time.mktime(yesterday.timetuple())) - 19800 print("FROM : ", yesterdayMidnight) todayMidnight = yesterdayMidnight + 86400 print(todayMidnight) yesterdayMidnight = '1579199400' todayMidnight = '1579285800' QUERY = ''' SELECT ID, APPID, EVENTS, DEVICE_ID, "TIMESTAMP", MTIMESTAMP, UPTIMESTAMP, IPADDRESS, CITY, COUNTRY, EVENTNAME, CREATED_DATE, CREATED_TIME FROM PUBLIC.EVENTSDATA WHERE "TIMESTAMP" > '{}' AND "TIMESTAMP" < '{}' ;'''.format( yesterdayMidnight, todayMidnight) result = client.sql( QUERY, include_field_names=True, ) next(result) BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) print(BASE_DIR) namespac = BASE_DIR + str("/target/Data_" + yesterday.strftime('%m_%d_%Y') + '.csv') with open(namespac, 'w+') as writeFile: print("START : ", time.localtime(time.time())) writer = csv.writer(writeFile) writer.writerow([ 'appid', 'DeviceId', 'vtionid', 'deviceModel', 'platform', 'apppackage', 'keyname', 'mobileOperator', 'app', 'song', 'album', 'Pstate', 'Program', 'Episode', 'Source', 'ipaddress', 'city', 'station', 'duration', 'timestamp', 'created_date', 'created_time', 'stationName', 'artist_name', 'genre', 'education', 'ownership', 'nccs_code', 'age', 'gender', 'number', 'Uninstall', 'Reward_option' ]) i = 0 for row in result: nccs = age = gender = number = status = '' artist_name = '' genre = '' try: cur.execute( '''SELECT nccs_code FROM public.installdata WHERE deviceid = '{}';''' .format(row[6])) nccs_result = cur.fetchone() nccs_res = nccs_result[0] except Exception as e: nccs_res = '' pass if i == 0: x = row[2] i = 1 else: x = row[5] urlSafeEncodedBytes = base64.urlsafe_b64encode( row[6].encode("utf-8")) vtionid = str(urlSafeEncodedBytes, "utf-8") try: y = json.loads(x) if y.get("segmentation") is None: writer.writerow([ row[1], row[6], vtionid, y.get("d"), y.get("p"), y.get("ap"), row[2], y.get("network").get("ope"), "", "", "", "", "", "", "", row[10], row[11], "", "", row[7], row[3], row[4] ]) else: if y.get("ap") == "com.video.meter": song = y.get("segmentation").get("Song") app = y.get("segmentation").get("App") education = y.get("segmentation").get( "Highest Education") ownership = y.get("segmentation").get("Ownership") if row[2] == 'FM_Tuned': try: QUERYa = ''' SELECT STATION FROM MASTERTABLEDEMO WHERE CITY = '{}' AND FREQUENCY = '{}'; '''.format( row[11], y.get("segmentation").get("Station")) resulta = client.sql( QUERYa, include_field_names=True, ) next(resulta) stationName = [rowa for rowa in resulta] writer.writerow([ row[1], row[6], vtionid, y.get("d"), y.get("p"), y.get("ap"), row[2], y.get("network").get("ope"), y.get("segmentation").get("App"), y.get("segmentation").get("Song"), y.get("segmentation").get("Album"), y.get("segmentation").get("PState"), "", "", y.get("segmentation").get("Source"), row[10], row[11], y.get("segmentation").get("Station"), y.get("segmentation").get("Duration"), row[7], row[3], row[4], stationName[0][0], "", "", "", "", nccs_res ]) except Exception as e: writer.writerow([ row[1], row[6], vtionid, y.get("d"), y.get("p"), y.get("ap"), row[2], y.get("network").get("ope"), y.get("segmentation").get("App"), y.get("segmentation").get("Song"), y.get("segmentation").get("Album"), y.get("segmentation").get("PState"), "", "", y.get("segmentation").get("Source"), row[10], row[11], y.get("segmentation").get("Station"), y.get("segmentation").get("Duration"), row[7], row[3], row[4], "", "", "", "", "", nccs_res ]) elif row[2] == 'Video_Tuned' or row[ 2] == 'Video_off': try: writer.writerow([ row[1], row[6], vtionid, y.get("d"), y.get("p"), y.get("ap"), row[2], y.get("network").get("ope"), y.get("segmentation").get("App"), y.get("segmentation").get("Song"), y.get("segmentation").get("Album"), y.get("segmentation").get("PState"), y.get("segmentation").get("Program"), y.get("segmentation").get("Episode"), y.get("segmentation").get("Source"), row[10], row[11], "", y.get("segmentation").get("Duration"), row[7], row[3], row[4], "", "", "", "", "", nccs_res ]) except Exception as e: pass elif row[2] == 'Audio_Tuned': try: if song: if song.isdigit(): continue song = song.strip() filter_new = [ 'Download', 'download', 'AUD', 'Advertise', '%' ] status_filter = ( filter_new[0] in song or filter_new[1] in song or filter_new[2] in song or filter_new[3] in song or filter_new[4] in song) if str(status_filter) == 'False': album = y.get("segmentation").get( "Album") if album is None: album_filter = False else: a_filter = [ 'unknown', 'Advertise', 'Sponsored' ] album_filter = ( a_filter[0] in album or a_filter[1] in album or a_filter[2] in album) if str(album_filter) == 'False': QUERY = '''SELECT ARTISTNAME, GENRE FROM PUBLIC.SONG_DATA WHERE TRACKNAME = '{}';'''.format( song) details = client.sql( QUERY, include_field_names=True, ) next(details) for det in details: artist_name = det[0] genre = det[1] if artist_name: pass else: QUERY = '''SELECT ARTISTNAME, GENRE FROM PUBLIC.SONG_DATA_ADD WHERE TRACKNAME = '{}';'''.format( song) details = client.sql( QUERY, include_field_names= True, ) next(details) for det in details: artist_name = det[0] genre = det[1] if app == 'Amazon Music': pass if '%' in song or song.isdigit( ): continue else: if 'ñ' in song: song = song.replace( 'ñ', 'n') else: pass song = deEmojify( filter( remove_bdata(song). replace( ')', '').replace( '(', ''))) elif app == 'Spotify' or 'Spot' in app: if '%' in song: continue else: song = filter( deEmojify( remove_bdata( song))) elif app == 'Gaana': if 'Gaana' in song or song.isdigit( ) or song == ',': continue else: if 'ñ' in song or '├▒' in song: song = song.replace( 'ñ', 'n').replace( '├▒', 'n') else: pass song = select_before_list( remove_bdata( deEmojify(song)) ).lstrip('Ep 0123456789-.') elif app == 'Google Play Music': aa = song if song.isdigit( ) or 'high quality' in song or song == ',': continue else: song = domain( remove_bdata(song) ).lstrip( ' 0123456789.-' ).strip() if '128k' in song or '256k' in song or 'p3' in song: song = song.replace( '(128k)', '' ).replace( '(256k)', '').replace( '128k', '' ).replace( '256k', '' ).replace( '.mp3', '') song = select_before_list( filter( deEmojify( song))) elif app == 'Hungama': song = filter( remove_bdata( deEmojify(song))) elif app == 'i Music': song = song.replace( '-', ' ').replace( ':', ' ').replace( ' ', ' ').replace( ' ', ' ' ).replace( '.mp3', '') if 'Unknown artist' in song or 'i Music' in song or '//' in song: continue song = deEmojify( select_before_list( filter( domain( remove_bdata( song)) ).lstrip( ' 0123456789.-' ).strip())).strip( ) elif app == 'JioSaavn': song = filter( remove_bdata(song)) elif app == 'Saavn': #combine with jio savan song = filter( domain( remove_bdata( song))) app = 'JioSaavn' elif app == 'MX Player': if '--:--:--' in song or 'Insufficient balance' in song or 'Expiry Date' in song: continue else: song = deEmojify( filter( remove_bdata( song) ).replace( ' ', ' ')).strip() elif app == 'Music Player': song = domain( filter( remove_bdata(song)) ).replace( ' ', ' ').lstrip( ' 0123456789.-' ).strip() elif app == 'Wynk Music': song = remove_bdata( deEmojify(song) ).lstrip(' 0123456789.-' ).strip() if song.isdigit( ) or '%' in song: continue elif '(From' in song: song = song.replace( '(From', '') else: if '")' in song: song = remove_bdata( song.replace( '\")', '')) elif app == 'YouTube Music': song = filter( deEmojify( remove_bdata( song))) else: song = song app = app try: if song: if song.isdigit(): continue song = song.strip() writer.writerow([ row[1], row[6], vtionid, y.get("d"), y.get("p"), y.get("ap"), row[2], y.get("network" ).get("ope"), app, song, y.get( "segmentation" ).get("Album"), y.get( "segmentation" ).get("PState"), "", "", y.get( "segmentation" ).get("Source"), row[10], row[11], y.get( "segmentation" ).get("Station"), y.get( "segmentation" ).get("Duration"), row[7], row[3], row[4], "", artist_name, genre, '', '', nccs_res, '', '', '', '' ]) else: pass except Exception as e: pass else: pass else: pass except Exception as e: pass elif row[2] == 'Register' or row[2] == 'Profile': if ownership: try: own = ownership.split(',') if len(own) >= 9: num_own = 9 else: num_own = len(own) cur.execute( '''SELECT nccs_code FROM public.nccs_flat where education = '{}' and ownership = '{}';'''. format(education, num_own)) nccs = cur.fetchone() except Exception as e: pass try: try: cur.execute( '''SELECT payment_option FROM public.payment_option where deviceid = '{}'; '''.format(row[6])) payment_option = cur.fetchone() payment = payment_option[0] except: payment = 'Amazon voucher' pass age = y.get("segmentation").get("age") if age: pass else: age = y.get("segmentation").get("Age") gender = y.get("segmentation").get( "Gender") if gender: pass else: gender = y.get("segmentation").get( "gender") number = y.get("segmentation").get( "Mobile Number") num_status = "+" in number if str(num_status) == 'True': number = '' else: try: cur.execute( '''SELECT i_status FROM public.appsflyer where number = '{}';'''.format( number)) status_now = cur.fetchone() status = status_now[0] if status != 'True': status = '' except: pass try: writer.writerow([ row[1], row[6], vtionid, y.get("d"), y.get("p"), y.get("ap"), row[2], y.get("network").get("ope"), y.get("segmentation").get("App"), y.get("segmentation").get("Song"), y.get("segmentation").get("Album"), y.get("segmentation").get( "PState"), "", "", y.get("segmentation").get( "Source"), row[10], row[11], y.get("segmentation").get( "Station"), y.get("segmentation").get( "Duration"), row[7], row[3], row[4], "", artist_name, genre, education, ownership, nccs[0], age, gender, number, status, payment ]) except Exception as e: pass writer.writerow([ row[1], row[6], vtionid, y.get("d"), y.get("p"), y.get("ap"), row[2], y.get("network").get("ope"), y.get("segmentation").get("App"), y.get("segmentation").get("Song"), y.get("segmentation").get("Album"), y.get("segmentation").get( "PState"), "", "", y.get("segmentation").get( "Source"), row[10], row[11], y.get("segmentation").get( "Station"), y.get("segmentation").get( "Duration"), row[7], row[3], row[4], "", artist_name, genre, education, ownership, "", age, gender, number, status, payment ]) except Exception as e: pass else: writer.writerow([ row[1], row[6], vtionid, y.get("d"), y.get("p"), y.get("ap"), row[2], y.get("network").get("ope"), y.get("segmentation").get("App"), y.get("segmentation").get("Song"), y.get("segmentation").get("Album"), y.get("segmentation").get("PState"), "", "", y.get("segmentation").get("Source"), row[10], row[11], y.get("segmentation").get("Station"), y.get("segmentation").get("Duration"), row[7], row[3], row[4], "", artist_name, genre, education, ownership, "" ]) except Exception as e: pass client.close() writeFile.close()
my_cache = client.get_or_create_cache('my cache') my_cache.put('my key', 42) # value ‘42’ takes 9 bytes of memory as a LongObject my_cache.put('my key', 42, value_hint=ShortObject) # value ‘42’ takes only 3 bytes as a ShortObject my_cache.put('a', 1) # ‘a’ is a key of type String my_cache.put('a', 2, key_hint=CharObject) # another key ‘a’ of type CharObject was created value = my_cache.get('a') print(value) # 1 value = my_cache.get('a', key_hint=CharObject) print(value) # 2 # now let us delete both keys at once my_cache.remove_keys([ 'a', # a default type key ('a', CharObject), # a key of type CharObject ]) my_cache.destroy() client.close()
def pythonignite(request): # print("Connection Pending!") loginUser = request.user nodes = [ ('127.0.0.1', 10800), ('127.0.0.1', 10801), ] # nodes = [ # ('35.154.247.92', 10800), # ('35.154.247.92', 10801), # ] client = Client() client.connect(nodes) #QUERY = ''' SELECT * FROM PUBLIC.EVENTSDATA ORDER BY TIMESTAMP DESC LIMIT 2000; ''' #QUERY = ''' SELECT ID, APPID, EVENTS, DEVICE_ID, "TIMESTAMP", MTIMESTAMP, UPTIMESTAMP, IPADDRESS, CITY, COUNTRY, EVENTNAME, CREATED_DATE, CREATED_TIME FROM PUBLIC.EVENTSDATA ORDER BY TIMESTAMP DESC LIMIT 2000; ''' #QUERY = ''' SELECT ID, APPID, EVENTS, DEVICE_ID, "TIMESTAMP", MTIMESTAMP, UPTIMESTAMP, IPADDRESS, CITY, COUNTRY, EVENTNAME, CREATED_DATE, CREATED_TIME FROM EVENTSDATA WHERE ID > (SELECT MAX(ID)-2000 FROM EVENTSDATA) ORDER BY "TIMESTAMP" DESC LIMIT 0,2000; ''' QUERY = ''' SELECT ID, APPID, EVENTS, DEVICE_ID, "TIMESTAMP", MTIMESTAMP, UPTIMESTAMP, IPADDRESS, CITY, COUNTRY, EVENTNAME, CREATED_DATE, CREATED_TIME FROM PUBLIC.EVENTSDATA WHERE ID > (SELECT MAX(ID) FROM EVENTSDATA)-2000 ORDER BY ID DESC LIMIT 2000; ''' result = client.sql( QUERY, include_field_names=True, ) print(next(result)) eventsdata = [] i = 0 for row in result: # print(row) if i == 0: eventsdata.append({ "ID":row[0], "APPID":row[1], "EVENTS":row[2], "DEVICE_ID":row[3], "TIMESTAMP":row[4], "MTIMESTAMP":row[5], "UPTIMESTAMP":row[6], "IPADDRESS":row[7], "CITY":row[8], "COUNTRY":row[9], "EVENTNAME":row[10], "CREATED_DATE":row[11], "CREATED_TIME":row[12], }) i = 1 else: eventsdata.append({ "ID": row[0], "APPID": row[1], "EVENTNAME": row[2], "CREATED_DATE": row[3], "CREATED_TIME": row[4], "EVENTS": row[5], "DEVICE_ID": row[6], "TIMESTAMP": row[7], "MTIMESTAMP": row[8], "UPTIMESTAMP": row[9], "IPADDRESS": row[10], "CITY": row[11], "COUNTRY": row[12], }) # print("Connection Establish!") client.close() # Pagenation page = request.GET.get('page', 1) paginator = Paginator(eventsdata, 50) try: contacts = paginator.page(page) except PageNotAnInteger: contacts = paginator.page(1) except EmptyPage: contacts = paginator.page(paginator.num_pages) return render(request,"Pythonignite.html",{"contacts":contacts,"loginUser":loginUser})
def queryresultalter(request): conn = psycopg2.connect(dbname="VTIONData",host= "vtionproddb.chgz4nqwpdta.ap-south-1.rds.amazonaws.com", user="******", password="******") cur = conn.cursor() nodes = [ ('35.154.247.92', 10800), ('35.154.247.92', 10801), ] client = Client() client.connect(nodes) artist_name = '' genre = '' yesterday = "QueryRan" if request.method=='GET': '''squery = request.GET.get('squery')''' starttimestamp = request.GET.get('starttimestamp') endtimestamp = request.GET.get('endtimestamp') print("lol") QUERY = ''' SELECT ID, APPID, EVENTS, DEVICE_ID, "TIMESTAMP", MTIMESTAMP, UPTIMESTAMP, IPADDRESS, CITY, COUNTRY, EVENTNAME, CREATED_DATE, CREATED_TIME FROM PUBLIC.EVENTSDATA WHERE "TIMESTAMP" > '{}' AND "TIMESTAMP" < '{}' AND EVENTNAME!='_app_crash';'''.format(starttimestamp,endtimestamp) #QUERY = ''' SELECT ID, APPID, EVENTS, DEVICE_ID, "TIMESTAMP", MTIMESTAMP, UPTIMESTAMP, IPADDRESS, CITY, COUNTRY, EVENTNAME, CREATED_DATE, CREATED_TIME FROM PUBLIC.EVENTSDATA WHERE "TIMESTAMP" > '{}' AND "TIMESTAMP" < '{}' AND EVENTNAME='Register';'''.format(starttimestamp,endtimestamp) print(QUERY) result = client.sql( QUERY, include_field_names=True, ) next(result) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename="{}.csv"'.format(yesterday+"_IgniteEvents") writer = csv.writer(response) writer.writerow(['appid','deviceid' ,'vtionid','devicemodel','platform','apppackage','keyname','mobileoperator','app','song','album','pstate','program','episode','source','ipaddress','city','station','duration','timestamp','to_char','created_time','artists', 'genre','education','ownership','nccs_code','age','gender','number', 'uninstall']) i = 0 for row in result: nccs = age = gender = number = status = '' if i == 0: print(row) x = row[2] urlSafeEncodedBytes = base64.urlsafe_b64encode(row[3].encode("utf-8")) vtionid = str(urlSafeEncodedBytes, "utf-8") y = json.loads(x) if y.get("segmentation") is None: pass #writer.writerow([row[1],row[3],vtionid,y.get("d"),y.get("p"),y.get("ap"),row[10],y.get("network").get("ope"),"","","","","","","",row[7],row[8],"","",row[4],row[11],row[12],"","",""]) else: if row[10] == 'Video_Tuned' or row[10] == 'Video_off': song = y.get("segmentation").get("Song") try: if song: QUERY = '''SELECT ARTISTNAME, GENRE FROM PUBLIC.SONG_DATA WHERE TRACKNAME = '{}';'''.format(song) details = client.sql( QUERY, include_field_names=True, ) print(next(details)) for det in details: artist_name = det[0] genre = det[1] print("Testing with second table : ------------------------------------------------------//") if artist_name: pass else: QUERY = '''SELECT ARTISTNAME, GENRE FROM PUBLIC.SONG_DATA_ADD WHERE TRACKNAME = '{}';'''.format(song) details = client.sql( QUERY, include_field_names=True, ) print(next(details)) for det in details: artist_name = det[0] genre = det[1] writer.writerow([row[1],row[3],vtionid,y.get("d"),y.get("p"),y.get("ap"),row[10],y.get("network").get("ope"),y.get("segmentation").get("App"),y.get("segmentation").get("Song"),y.get("segmentation").get("Album"),y.get("segmentation").get("PState"),y.get("segmentation").get("Program"),y.get("segmentation").get("Episode"),y.get("segmentation").get("Source"),row[7],row[8],"",y.get("segmentation").get("Duration"),row[4],row[11],row[12], artist_name, genre,"","","","","","",""]) else: pass except Exception as e: raise e elif row[10] == 'Audio_Off': pass else: song = y.get("segmentation").get("Song") education = y.get("segmentation").get("Highest Education") ownership = y.get("segmentation").get("Ownership") if ownership: try: print(ownership) print(education) own = ownership.split(',') print(len(own)) if len(own) >= 9 : num_own = 9 else : num_own = len(own) cur.execute('''SELECT nccs_code FROM public.nccs_flat where education = '{}' and ownership = '{}';'''.format(education,num_own)) nccs = cur.fetchone() print("Found reply from postgres :", nccs[0]) except Exception as e: print("query error") if row[10] == 'Register' or row[10] =='Profile': try: # cur.execute('''SELECT age, gender, number FROM public.installdata where deviceid = '{}';'''.format(row[3])) # data = cur.fetchone() # age = data[0] # gender = data[1] # number = data[2] age = y.get("segmentation").get("age") if age: pass else: age = y.get("segmentation").get("Age") gender = y.get("segmentation").get("Gender") if gender: pass else: gender = y.get("segmentation").get("gender") number = y.get("segmentation").get("Mobile Number") try: cur.execute('''SELECT i_status FROM public.appsflyer where number = '{}';'''.format(number)) status_now = cur.fetchone() status = status_now[0] if status == 'True': status = 'True' else: status = '' print("Status : -------------> ", status) except: pass except Exception as e: print("First case error :",e) try: if song: QUERY = '''SELECT ARTISTNAME, GENRE FROM PUBLIC.SONG_DATA WHERE TRACKNAME = '{}';'''.format(song) details = client.sql( QUERY, include_field_names=True, ) next(details) # print(next(details)) for det in details: artist_name = det[0] genre = det[1] print("lol its here") try: writer.writerow([row[1],row[3],vtionid,y.get("d"),y.get("p"),y.get("ap"),row[10],y.get("network").get("ope"),y.get("segmentation").get("App"),y.get("segmentation").get("Song"),y.get("segmentation").get("Album"),y.get("segmentation").get("PState"),"","",y.get("segmentation").get("Source"),row[7],row[8],y.get("segmentation").get("Station"),y.get("segmentation").get("Duration"),row[4],row[11],row[12], artist_name, genre, education, ownership, nccs[0], age, gender, number, status]) except Exception as e: print("first case : ",e) writer.writerow([row[1],row[3],vtionid,y.get("d"),y.get("p"),y.get("ap"),row[10],y.get("network").get("ope"),y.get("segmentation").get("App"),y.get("segmentation").get("Song"),y.get("segmentation").get("Album"),y.get("segmentation").get("PState"),"","",y.get("segmentation").get("Source"),row[7],row[8],y.get("segmentation").get("Station"),y.get("segmentation").get("Duration"),row[4],row[11],row[12], artist_name, genre, education, ownership,'',age, gender, number,status]) except Exception as e: print(e) pass i = 1 else: print(row) x = row[5] #Encoder urlSafeEncodedBytes = base64.urlsafe_b64encode(row[6].encode("utf-8")) vtionid = str(urlSafeEncodedBytes, "utf-8") #Decoder # decodedBytes = base64.b64decode(urlSafeEncodedStr) # decodedStr = str(decodedBytes, "utf-8") # print("TYPEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEee : ", type(row[6])) try: y = json.loads(x) if y.get("segmentation") is None: writer.writerow([row[1],row[6],vtionid,y.get("d"),y.get("p"),y.get("ap"),row[2],y.get("network").get("ope"),"","","","","","","",row[10],row[11],"","",row[7],row[3],row[4],"",""]) else: if row[2] == 'Video_Tuned' or row[2] == 'Video_off': song = y.get("segmentation").get("Song") try: if song: QUERY = '''SELECT ARTISTNAME, GENRE FROM PUBLIC.SONG_DATA WHERE TRACKNAME = '{}';'''.format(song) details = client.sql( QUERY, include_field_names=True, ) next(details) # print(next(details)) for det in details: artist_name = det[0] genre = det[1] writer.writerow([row[1],row[6],vtionid,y.get("d"),y.get("p"),y.get("ap"),row[2],y.get("network").get("ope"),y.get("segmentation").get("App"),y.get("segmentation").get("Song"),y.get("segmentation").get("Album"),y.get("segmentation").get("PState"),y.get("segmentation").get("Program"),y.get("segmentation").get("Episode"),y.get("segmentation").get("Source"),row[10],row[11],"",y.get("segmentation").get("Duration"),row[7],row[3],row[4], artist_name, genre,"","",""]) else: pass except Exception as e: pass elif row[2] == 'Audio_Off': pass else: song = y.get("segmentation").get("Song") education = y.get("segmentation").get("Highest Education") ownership = y.get("segmentation").get("Ownership") if ownership: try: own = ownership.split(',') if len(own) >= 9 : num_own = 9 else : num_own = len(own) cur.execute('''SELECT nccs_code FROM public.nccs_flat where education = '{}' and ownership = '{}';'''.format(education,num_own)) nccs = cur.fetchone() print("Found reply from postgres :", nccs[0]) except Exception as e: print("query error") if row[2] == 'Register' or row[2] =='Profile': try: # cur.execute('''SELECT age, gender, number FROM public.installdata where deviceid = '{}';'''.format(row[6])) # data = cur.fetchone() # age = data[0] # gender = data[1] # number = data[2] age = y.get("segmentation").get("Age") if age : pass else: age = y.get("segmentation").get("age") gender = y.get("segmentation").get("Gender") if gender: pass else: gender = y.get("segmentation").get("gender") number = y.get("segmentation").get("Mobile Number") try: cur.execute('''SELECT i_status FROM public.appsflyer where number = '{}';'''.format(number)) status_now = cur.fetchone() status = status_now[0] except: pass except Exception as e: print(e) try: if song: QUERY = '''SELECT ARTISTNAME, GENRE FROM PUBLIC.SONG_DATA WHERE TRACKNAME = '{}';'''.format(song) details = client.sql( QUERY, include_field_names=True, ) next(details) # print(next(details)) for det in details: artist_name = det[0] genre = det[1] if artist_name: pass else: QUERY = '''SELECT ARTISTNAME, GENRE FROM PUBLIC.SONG_DATA_ADD WHERE TRACKNAME = '{}';'''.format(song) details = client.sql( QUERY, include_field_names=True, ) print(next(details)) for det in details: artist_name = det[0] genre = det[1] try: writer.writerow([row[1],row[6],vtionid,y.get("d"),y.get("p"),y.get("ap"),row[2],y.get("network").get("ope"),y.get("segmentation").get("App"),y.get("segmentation").get("Song"),y.get("segmentation").get("Album"),y.get("segmentation").get("PState"),"","",y.get("segmentation").get("Source"),row[10],row[11],y.get("segmentation").get("Station"),y.get("segmentation").get("Duration"),row[7],row[3],row[4], artist_name, genre, education, ownership, nccs[0], age, gender, number, status]) except Exception as e: print("Second case :",e) writer.writerow([row[1],row[6],vtionid,y.get("d"),y.get("p"),y.get("ap"),row[2],y.get("network").get("ope"),y.get("segmentation").get("App"),y.get("segmentation").get("Song"),y.get("segmentation").get("Album"),y.get("segmentation").get("PState"),"","",y.get("segmentation").get("Source"),row[10],row[11],y.get("segmentation").get("Station"),y.get("segmentation").get("Duration"),row[7],row[3],row[4], artist_name, genre, education, ownership, '', age, gender, number, status]) except Exception as e: print("passed without writing main : ",e) pass except Exception as e: pass client.close() return response
def search_deviceid_ignite(request): value = 1 loginUser = request.user if request.method=='GET': sdeviceid = request.GET.get('sdeviceid') # print(sdeviceid) nodes = [ ('127.0.0.1', 10800), ('127.0.0.1', 10801), ] # nodes = [ # ('35.154.247.92', 10800), # ('35.154.247.92', 10801), # ] client = Client() client.connect(nodes) QUERY = '''SELECT * FROM (SELECT ID, APPID, EVENTS, DEVICE_ID, "TIMESTAMP", MTIMESTAMP, UPTIMESTAMP, IPADDRESS, CITY, COUNTRY, EVENTNAME, CREATED_DATE, CREATED_TIME FROM PUBLIC.EVENTSDATA WHERE DEVICE_ID='{}' AND "TIMESTAMP"> '1575138600' LIMIT 0,500) AS t ORDER BY t.ID desc; '''.format(sdeviceid) result = client.sql( QUERY, include_field_names=True, ) print(next(result)) eventsdata = [] i = 0 for row in result: # print(row) if i == 0: eventsdata.append({ "ID":row[0], "APPID":row[1], "EVENTS":row[2], "DEVICE_ID":row[3], "TIMESTAMP":row[4], "MTIMESTAMP":row[5], "UPTIMESTAMP":row[6], "IPADDRESS":row[7], "CITY":row[8], "COUNTRY":row[9], "EVENTNAME":row[10], "CREATED_DATE":row[11], "CREATED_TIME":row[12], }) i = 1 else: eventsdata.append({ "ID": row[0], "APPID": row[1], "EVENTNAME": row[2], "CREATED_DATE": row[3], "CREATED_TIME": row[4], "EVENTS": row[5], "DEVICE_ID": row[6], "TIMESTAMP": row[7], "MTIMESTAMP": row[8], "UPTIMESTAMP": row[9], "IPADDRESS": row[10], "CITY": row[11], "COUNTRY": row[12], }) # print("Connection Establish!") client.close() return render(request,"Pythonignite.html",{"contacts":eventsdata,"loginUser":loginUser,"value":value,"sdeviceid":sdeviceid})