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 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():
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})
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()
