def __init__(self, parent):
tk.Frame.__init__(self, parent)
self.canvas_frame = tk.Frame(self)
self.canvas_frame.parent = self
self.alarms_canvas = tk.Canvas(self.canvas_frame)
self.alarms_canvas.parent = self.canvas_frame
self.alarms = tk.Frame(self.alarms_canvas)
self.alarms.parent = self.alarms_canvas
self.add_alarm_button = tk.Button(self,
text="Add Alarm",
command=self.add_alarm)
self.scrollbar = tk.Scrollbar(self.canvas_frame,
orient=tk.VERTICAL,
command=self.alarms_canvas.yview)
self.add_alarm_button.pack(side=tk.TOP, fill=tk.X, expand=0)
self.canvas_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.alarms_canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.scrollbar.pack(side=tk.RIGHT, fill=tk.Y, expand=0)
# Creating long list of alarm frames. Values populated from database
self.db = SqlConnector()
self.alarm_frames = []
self.add_alarms()
self.thread = Thread(target=self.check_time, daemon=True)
self.thread.start()
self.alarms_canvas.bind("<Configure>", self.on_canvas_resize)
def __init__(self):
super(StudentForm, self).__init__()
uic.loadUi('liststudent.ui', self)
from sql_connector import SqlConnector
self.sql = SqlConnector()
self.setupParams()
self.setupEvents()
def __init__(self, parent, id, time, repeat, sound, message, active):
self.db = SqlConnector() # Allows for access to database
self.id = id # Allows for database identification
self.time = time # Time for alarm to go off
self.repeat = repeat # Days of the week that alarm will go off, else One
self.sound = sound # Path to sound that will play when alarm goes off
self.active = active # Boolean value whether alarm will go off at time
self.message = message # Message that shows when alarm goes off
self.parent = parent
self.alarm_sound = None
tk.Frame.__init__(self, parent)
self.add_widgets()
self.configure(borderwidth=1, relief=tk.RAISED, background="black")
def query_daily_average_visitors():
'''Count the number of devices (non-randomized) detected within last 15
minutes'''
cmd = '''
SELECT AVG(unique_visitors) AS average_visitor_count
FROM
(
SELECT DATE(ts) day,
COUNT(DISTINCT mac_id) AS unique_visitors
FROM events
WHERE ts BETWEEN NOW() - INTERVAL 8 DAY AND NOW() - INTERVAL 1 DAY
GROUP BY day
) t1
;
'''
sql = SqlConnector()
sql.cursor.execute(cmd)
x = sql.cursor.fetchall()[0]['average_visitor_count']
if x is None:
value = 0
else:
value = x
sql.cursor.close()
return value
def plot1(dbname, condition):
#providers of beacons - count of beacons
logname = open(u'plot1' + dbname + '.csv', 'w')
db = SqlConnector(dbname)
output = db.execute(
"SELECT image_domains.domain, count(images.id) \
as imgcount FROM images INNER JOIN image_domains ON \
image_domains.id = images.id_image_domains WHERE " +
condition + " group by image_domains.domain order by imgcount DESC;")
for item in output:
logname.write("{0};{1}\n".format(item[0], item[1]))
print(item)
logname.close()
def query_mac_search(mac):
'''Query the Events table for unique mac addresses'''
cmd1 = '''
SELECT id mac_id
FROM mac_addresses
WHERE mac = %s;
'''
cmd2 = '''
SELECT COUNT(1) mac_count
FROM events
WHERE mac_id = %s;
'''
sql = SqlConnector()
sql.cursor.execute(cmd1, (mac,))
x = sql.cursor.fetchall()
if len(x) == 0:
return '0 entries'
else:
pass
mac_id = x[0]['mac_id']
sql.cursor.execute(cmd2, (mac_id,))
x = sql.cursor.fetchall()
mac_count = x[0]['mac_count']
sql.cursor.close()
mac_count = x[0]['mac_count']
if mac_count == 1:
mac_count = str(format(mac_count, ",d")) + ' entry'
else:
mac_count = str(format(mac_count, ",d")) + ' entries'
return mac_count
def extract_twitter_pixels(dbname):
logp1 = open(u'twitter_pixels.txt', 'w')
db = SqlConnector(dbname)
output = db.execute(
"SELECT images.url, pages.url, images.width, images.height \
FROM images INNER JOIN pages ON \
pages.id = images.id_pages INNER JOIN image_domains ON \
images.id_image_domains = image_domains.id WHERE width<=1 \
and height<=1 and image_domains.domain = 'twitter.com';")
for item in output:
logp1.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
logp1.close()
class StudentForm(QtWidgets.QWidget):
def __init__(self):
super(StudentForm, self).__init__()
uic.loadUi('newstudent.ui', self)
from sql_connector import SqlConnector
self.sql = SqlConnector()
self.setupParams()
self.setupEvents()
def setupParams(self):
for dept in self.sql.get_dept_names():
self.deptBox.addItem(dept)
def setupEvents(self):
self.saveButton.clicked.connect(self.on_save)
def on_save(self):
self.sql.add_student({
'name': self.nameLine.text(),
'dept': self.deptBox.currentText()
})
def get_connection(self, engine):
""" Get a specific engine object.
Args:
engine (str): Engine name.
Returns:
obj: Engine object.
"""
configuration = self._get_configuration(engine)
if engine == 'SQL':
return SqlConnector(configuration)
else:
raise ValueError(configuration)
def plot3(dbname, condition):
#users of beacons - % pages with at least 1 beacon
logname = open(u'plot3' + dbname + '.csv', 'w')
db = SqlConnector(dbname)
output = db.execute("SELECT domains.domain, count(distinct pages.id) \
as pagescount FROM images INNER JOIN pages ON \
pages.id = images.id_pages INNER JOIN domains ON \
pages.id_domains = domains.id group by domains.domain order by pagescount DESC;"
)
for item in output:
pages_count = item[1]
output = db.execute("SELECT count(distinct pages.id) \
as pagescount FROM images INNER JOIN pages ON \
pages.id = images.id_pages INNER JOIN domains ON \
pages.id_domains = domains.id WHERE domains.domain = '" +
item[0] + "' and " + condition + ";")
percentage = output[0][0] / item[1] * 100
print("{0} {1} {2} : {3}%\n".format(item[0], item[1], output[0][0],
percentage))
logname.write("{0};{1}\n".format(item[0], percentage))
logname.close()
def query_current_devices(period=15):
'''Count the number of devices (non-randomized) detected within last 15
minutes'''
cmd = '''
SELECT COUNT(DISTINCT mac_id) current_device_count
FROM events
WHERE ts BETWEEN NOW() - INTERVAL %s MINUTE AND NOW();
'''
sql = SqlConnector()
sql.cursor.execute(cmd, (period,))
x = sql.cursor.fetchall()[0]['current_device_count']
sql.cursor.close()
return x
def query_unique_visitors(start_time='1999-12-31 23:59:59', end_time='2020-01-01 00:00:00'):
'''Query the Events table for unique mac addresses'''
cmd = '''
SELECT COUNT(DISTINCT mac_id) unique_visitors
FROM events
WHERE ts BETWEEN %s AND %s;
'''
sql = SqlConnector()
sql.cursor.execute(cmd, (start_time, end_time))
x = sql.cursor.fetchall()
sql.cursor.close()
unique_visitors = str(format(x[0]['unique_visitors'], ",d"))
return unique_visitors
def query_average_dwell(start_time, end_time):
'''Query the Average Dwell Time for all events within the time range'''
cmd = '''
SELECT AVG(dwell) dwell_time
FROM
(select count(1), mac_id, MIN(ts), TIMESTAMPDIFF(MINUTE, MIN(ts), MAX(TS)) dwell
FROM events
GROUP BY 2
#HAVING COUNT(1) > 1
HAVING dwell >= 1
LIMIT 10) t;
'''
sql = SqlConnector()
sql.cursor.execute(cmd)
x = sql.cursor.fetchall()
sql.cursor.close()
dwell_time = str(round(float(x[0]['dwell_time']), 1))
return dwell_time
def query_repeat_devices(period=15):
'''Count the number of devices (non-randomized) detected within last 15
minutes'''
cmd = '''
SELECT COUNT(1) repeat_device_count
FROM
(SELECT DISTINCT(mac_id)
FROM events
WHERE ts BETWEEN NOW() - INTERVAL %s MINUTE AND NOW()) t1
JOIN
(SELECT DISTINCT(mac_id)
FROM events
WHERE ts BETWEEN DATE(NOW()) - INTERVAL 31 DAY
AND DATE(NOW())) t2
ON t1.mac_id = t2.mac_id
;
'''
sql = SqlConnector()
sql.cursor.execute(cmd, (period,))
x = sql.cursor.fetchall()[0]['repeat_device_count']
sql.cursor.close()
return x
def parse(text, f):
'''
Given text, parse the timestamp, MAC address, and signal strength.
Return as a list of entry objects.
'''
sql = SqlConnector()
file_date = re.search(r'data\_(\d{4}\-\d{2}-\d{2})', f).group(1)
lines = text.split('\n')
for line in lines:
if line == '':
continue
ts = ts_parser(line, file_date)
mac = mac_parser(line)
rssi = rssi_parser(line)
event = Event(ts, mac, rssi)
if event.is_random:
write_to_random_table(sql, event)
else:
write_to_events_table(sql, event)
sql.cnx.commit()
sql.cnx.close()
move_processed_file(f)
return None
class StudentForm(QtWidgets.QWidget):
def __init__(self):
super(StudentForm, self).__init__()
uic.loadUi('liststudent.ui', self)
from sql_connector import SqlConnector
self.sql = SqlConnector()
self.setupParams()
self.setupEvents()
def setupParams(self):
pass
def setupEvents(self):
self.refreshButton.clicked.connect(self.on_refresh)
def on_refresh(self):
self.studentView.setColumnCount(3)
self.studentView.setRowCount(20)
result = self.sql.get_students()
print(result)
for row, student in enumerate(result):
for col, (key, value) in enumerate(student.items()):
self.studentView.setItem(row, col, QTableWidgetItem(value))
class Alarm(tk.Frame):
def __init__(self, parent, id, time, repeat, sound, message, active):
self.db = SqlConnector() # Allows for access to database
self.id = id # Allows for database identification
self.time = time # Time for alarm to go off
self.repeat = repeat # Days of the week that alarm will go off, else One
self.sound = sound # Path to sound that will play when alarm goes off
self.active = active # Boolean value whether alarm will go off at time
self.message = message # Message that shows when alarm goes off
self.parent = parent
self.alarm_sound = None
tk.Frame.__init__(self, parent)
self.add_widgets()
self.configure(borderwidth=1, relief=tk.RAISED, background="black")
def add_widgets(self):
days = ("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
self.active_var = tk.StringVar()
self.active_var.set(1 if self.active == "True" else 0)
self.repeat_var = tk.StringVar(value=days)
self.message_var = tk.StringVar(value=self.message)
self.label_frame = tk.Frame(self)
self.alarm_label = tk.Label(self.label_frame,
text=self.time,
relief=tk.RAISED,
font=Font(family='Helvetica',
size=36,
weight='bold'))
self.message_frame = tk.LabelFrame(self.label_frame,
text="Alarm Message:")
self.message_box = tk.Text(self.message_frame,
wrap="word",
height=3,
width=15,
relief=tk.RAISED)
self.active_frame = tk.Frame(self)
self.alarm_repeat = tk.Listbox(self.active_frame,
height=0,
listvariable=self.repeat_var,
selectmode="extended",
relief=tk.RAISED)
self.active_button = tk.Checkbutton(self.active_frame,
text="Active?",
variable=self.active_var,
relief=tk.RAISED)
self.edit_frame = tk.Frame(self)
self.edit_button = tk.Button(self.edit_frame,
text="Edit",
command=self.edit_alarm)
self.delete_button = tk.Button(self.edit_frame,
text="Delete",
command=self.delete)
self.test_button = tk.Button(self.edit_frame,
text="Test",
command=self.play_sound)
self.label_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.alarm_label.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.message_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.message_box.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.edit_frame.pack(side=tk.RIGHT, fill=tk.BOTH, expand=1)
self.edit_button.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.delete_button.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.test_button.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.active_frame.pack(side=tk.RIGHT, fill=tk.BOTH, expand=1)
self.active_button.pack(side=tk.BOTTOM, fill=tk.BOTH, expand=1)
self.alarm_repeat.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
def delete(self):
self.db.delete(self.id)
if __name__ != "__main__":
self.parent.parent.parent.parent.add_alarms()
self.destroy()
def play_sound(self):
self.alarm_sound = AlarmSound(self, set_window=True)
def edit_alarm(self):
self.edit_window = tk.Toplevel(self)
self.new_hr = tk.StringVar()
self.new_min = tk.StringVar()
self.am_pm = tk.StringVar()
self.new_active = tk.StringVar()
self.am_pm.set(0)
self.edit_frame = tk.Frame(self.edit_window)
self.spinbox_frame = tk.Frame(self.edit_frame)
self.hours_frame = tk.LabelFrame(self.spinbox_frame, text="Set hour:")
self.hours_select = tk.Spinbox(self.hours_frame,
from_=1,
to=12,
textvariable=self.new_hr,
width=5,
font=Font(family='Helvetica',
size=36,
weight='bold'))
self.minutes_frame = tk.LabelFrame(self.spinbox_frame,
text="Set minute:")
self.minutes_select = tk.Spinbox(self.minutes_frame,
from_=0,
to=59,
textvariable=self.new_min,
width=5,
font=Font(family='Helvetica',
size=36,
weight='bold'))
self.am_pm_frame = tk.LabelFrame(self.spinbox_frame, text="Set Am/PM:")
self.set_am = tk.Radiobutton(self.am_pm_frame,
text="AM",
variable=self.am_pm,
value=0)
self.set_pm = tk.Radiobutton(self.am_pm_frame,
text="PM",
variable=self.am_pm,
value=1)
self.active_button = tk.Checkbutton(self.edit_frame,
text="Set Active:",
variable=self.new_active)
self.save_button = tk.Button(self.edit_window,
text="Save",
command=self.confirm_edit)
self.cancel_button = tk.Button(self.edit_window,
text="Cancel",
command=self.edit_window.destroy)
self.spinbox_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.hours_select.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.minutes_select.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.set_am.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.set_pm.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.hours_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.minutes_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.am_pm_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.active_button.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.edit_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.save_button.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.cancel_button.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
def confirm_edit(self):
self.confirm = messagebox.askyesno(
message="Are you sure you want to complete this action?",
icon="question",
title="Confirm Action",
default="yes")
if self.confirm == "yes":
am_pm = "AM" if self.am_pm.get() == 0 else "PM"
time = str(self.new_hr.get()) + ":" + str(
self.new_min.get()) + " " + am_pm
active = self.new_active.get()
if bool(active) is True:
active = "True"
elif bool(active) is False:
active = "False"
else:
print("There was an error with the active variable:", active)
return
if __name__ != "__main__":
self.db.edit(self.time, time, active)
self.time = time
self.active = active
else:
self.edit_window.destroy()
class Alarms(tk.Frame):
def __init__(self, parent):
tk.Frame.__init__(self, parent)
self.canvas_frame = tk.Frame(self)
self.canvas_frame.parent = self
self.alarms_canvas = tk.Canvas(self.canvas_frame)
self.alarms_canvas.parent = self.canvas_frame
self.alarms = tk.Frame(self.alarms_canvas)
self.alarms.parent = self.alarms_canvas
self.add_alarm_button = tk.Button(self,
text="Add Alarm",
command=self.add_alarm)
self.scrollbar = tk.Scrollbar(self.canvas_frame,
orient=tk.VERTICAL,
command=self.alarms_canvas.yview)
self.add_alarm_button.pack(side=tk.TOP, fill=tk.X, expand=0)
self.canvas_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.alarms_canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.scrollbar.pack(side=tk.RIGHT, fill=tk.Y, expand=0)
# Creating long list of alarm frames. Values populated from database
self.db = SqlConnector()
self.alarm_frames = []
self.add_alarms()
self.thread = Thread(target=self.check_time, daemon=True)
self.thread.start()
self.alarms_canvas.bind("<Configure>", self.on_canvas_resize)
def check_time(self):
while True:
if int(strftime("%S")) in [0, 1, 2]:
time = strftime("%I:%M %p")
for alarm in self.alarms_data:
if alarm[1] == time and alarm[2] == "True":
for alarm_obj in self.alarm_frames:
if alarm[0] == alarm_obj.id:
if alarm_obj.alarm_sound is None:
alarm_obj.play_sound()
break
sleep(1)
def add_alarms(self):
self.alarms_data = self.db.collect()
for alarm_frame in self.alarm_frames:
alarm_frame.pack_forget()
self.alarm_frames = []
for row in range(len(self.alarms_data)):
self.alarm_frames.append(
Alarm(self.alarms, self.alarms_data[row][0],
self.alarms_data[row][1], "SunMonTueWedThuFriSat", None,
"", self.alarms_data[row][2]))
for alarm_frame in self.alarm_frames:
alarm_frame.pack(side=tk.TOP, fill=tk.X, expand=1)
self.alarms_canvas.create_window(0,
0,
anchor='nw',
window=self.alarms,
tags="alarms")
self.alarms_canvas.update_idletasks()
self.alarms_canvas.configure(
scrollregion=self.alarms_canvas.bbox('all'),
yscrollcommand=self.scrollbar.set)
self.on_canvas_resize()
def remove_alpha(self, var, index, mode):
if str(var) == "PY_VAR0": # new_hr StringVar is edited
current = self.new_hr.get()
self.new_hr.set("".join(x for x in current if x.isdigit()))
current = str(self.new_hr.get())
if str(current) == "":
self.new_hr.set(1)
elif int(current) > 12:
self.new_hr.set(12)
if str(var) == "PY_VAR1": # new_min StringVar is edited
current = self.new_min.get()
self.new_min.set("".join(x for x in current if x.isdigit()))
current = str(self.new_min.get())
if str(current) == "":
self.new_min.set(0)
elif int(current) > 59:
self.new_min.set(59)
def add_alarm(self):
self.add_window = tk.Toplevel(self)
self.new_hr = tk.StringVar()
self.new_min = tk.StringVar()
self.am_pm = tk.StringVar()
self.new_active = tk.StringVar()
self.new_hr.trace_add("write", self.remove_alpha)
self.new_min.trace_add("write", self.remove_alpha)
self.am_pm.set(0)
self.new_active.set(1)
self.add_frame = tk.Frame(self.add_window)
self.spinbox_frame = tk.Frame(self.add_frame)
self.hours_frame = tk.LabelFrame(self.spinbox_frame, text="Set hour:")
self.hours_select = tk.Spinbox(self.hours_frame,
from_=1,
to=12,
textvariable=self.new_hr,
width=5,
font=Font(family='Helvetica',
size=36,
weight='bold'))
self.minutes_frame = tk.LabelFrame(self.spinbox_frame,
text="Set minute:")
self.minutes_select = tk.Spinbox(self.minutes_frame,
from_=0,
to=59,
textvariable=self.new_min,
width=5,
font=Font(family='Helvetica',
size=36,
weight='bold'))
self.am_pm_frame = tk.LabelFrame(self.spinbox_frame, text="Set Am/PM:")
self.set_am = tk.Radiobutton(self.am_pm_frame,
text="AM",
variable=self.am_pm,
value=0)
self.set_pm = tk.Radiobutton(self.am_pm_frame,
text="PM",
variable=self.am_pm,
value=1)
self.active_button = tk.Checkbutton(self.add_frame,
text="Set Active:",
variable=self.new_active)
self.save_button = tk.Button(self.add_window,
text="Save",
command=self.confirm_add_alarm)
self.cancel_button = tk.Button(self.add_window,
text="Cancel",
command=self.add_window.destroy)
self.spinbox_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.hours_select.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.minutes_select.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.set_am.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.set_pm.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.hours_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.minutes_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.am_pm_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.active_button.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.add_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.save_button.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.cancel_button.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
def confirm_add_alarm(self):
self.confirm = messagebox.askyesno(
message="Are you sure you want to complete this action?",
icon="question",
title="Confirm Action",
default="yes")
if self.confirm is True:
am_pm = int(self.am_pm.get())
if am_pm == 0:
am_pm = "AM"
elif am_pm == 1:
am_pm = "PM"
else:
print("There was an error with the am_pm value:", am_pm)
raise ValueError
new_hr = str(self.new_hr.get())
new_min = str(self.new_min.get())
if len(new_hr) == 1:
new_hr = "0" + new_hr
if len(new_min) == 1:
new_min = "0" + new_min
time = new_hr + ":" + new_min + " " + am_pm
active = int(self.new_active.get())
if active == 0:
active = "False"
elif active == 1:
active = "True"
else:
print("There was an error with the active value:", active)
raise ValueError
self.db.insert(time, active)
self.alarms_canvas.delete("all")
self.add_alarms()
self.add_window.destroy()
elif self.confirm is False:
self.add_window.destroy()
else:
print("There was an error with the self.confirm value:",
self.confirm)
self.confirm_add_alarm()
def on_canvas_resize(self, *args):
self.alarms.width = self.alarms_canvas.winfo_width()
self.alarms_canvas.itemconfig('alarms',
width=self.alarms_canvas.winfo_width())
self.alarms_canvas.update_idletasks()
def save_in_database(data):
db = SqlConnector(g_db1_name)
db2 = SqlConnector(g_db2_name)
total = len(data)
count = 0
for site in data:
count += 1
print("\033[92mSave in database: {0}/{1}\033[0m {2}".format(
count, total, site))
domain_id = db.load_domain_into_db(site)
domain_id2 = db2.load_domain_into_db(site)
for page in data[site]:
urlList = data[site][page]
page_id = db.load_page_into_db(page, domain_id)
page_id2 = db2.load_page_into_db(page, domain_id2)
for img in urlList:
if img.accessible == 0:
db2.load_method2_data(img.url, img.accessible,
img.content_length, img.domain,
page_id2, img.cookie)
db.load_method1_data(img.url, img.accessible, img.size,
img.domain, page_id, img.cookie)
else:
if img.content_type != None and img.content_type.find(
'image') != -1:
#store image URLs with any content_length
db2.load_method2_data(img.url, img.accessible,
img.content_length, img.domain,
page_id2, img.cookie)
#store image URLs with any size
db.load_method1_data(img.url, img.accessible, img.size,
img.domain, page_id, img.cookie)
from config import SQLConfig
from sqlalchemy import (Column, Integer, String, ForeignKey, Text, DateTime,
Time, Boolean)
from sqlalchemy.ext.declarative import declarative_base
from sql_connector import SqlConnector
Base = declarative_base()
#connector = SqlConnector('root', '10.132.0.3', 'clouddr', password='******')
connector = SqlConnector(SQLConfig.USER_ID,
SQLConfig.IP,
SQLConfig.DB,
password=SQLConfig.PASSWORD)
class Guests(Base):
__tablename__ = "Guests"
id = Column(String(50), primary_key=True)
first = Column(String(20), nullable=False)
last = Column(String(20), nullable=False)
def AllGuests():
session = connector.get_sql_session()
result = session.query(Guests).all()
session.close()
return result
def UpdateGuest(id, first, last):
session = connector.get_sql_session()
def extract_facebook_pixels(dbname):
logp1 = open(u'fb_pixel_pattern1.txt', 'w')
logp2 = open(u'fb_pixel_pattern2.txt', 'w')
logp3 = open(u'fb_pixel_pattern3.txt', 'w')
logp4 = open(u'fb_pixel_pattern4.txt', 'w')
logp5 = open(u'fb_pixel_pattern5.txt', 'w')
db = SqlConnector(dbname)
output = db.execute(
"SELECT images.url, pages.url, images.width, images.height \
FROM images INNER JOIN pages ON \
pages.id = images.id_pages INNER JOIN image_domains ON \
images.id_image_domains = image_domains.id WHERE width<=1 \
and height<=1 and image_domains.domain = 'facebook.com';")
fb_unique_pixels1 = []
fb_unique_pixels2 = []
fb_unique_pixels3 = []
fb_unique_pixels4 = []
fb_unique_pixels5 = []
fb_unique_pixels3 = []
count_pages_pattern1 = 0
count_pages_pattern2 = 0
count_pages_pattern3 = 0
count_pages_pattern4 = 0
count_pages_pattern5 = 0
for item in output:
if item[0].find("/tr/?") != -1:
count_pages_pattern1 += 1
fb_pixel = extract_pixel(item[0])
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels1:
fb_unique_pixels1.append(fb_pixel)
logp1.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
elif item[0].find("/tr?") != -1:
count_pages_pattern2 += 1
fb_pixel = extract_pixel(item[0])
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels2:
fb_unique_pixels2.append(fb_pixel)
logp2.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
elif item[0].find("brandlift.php?") != -1:
count_pages_pattern3 += 1
fb_pixel = extract_pixel(item[0])
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels3:
fb_unique_pixels3.append(fb_pixel)
logp3.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
elif item[0].find("offsite_event.php?") != -1:
count_pages_pattern4 += 1
fb_pixel = extract_pixel(item[0])
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels4:
fb_unique_pixels4.append(fb_pixel)
logp4.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
else:
count_pages_pattern5 += 1
fb_pixel = "null"
b = item[0].find("spacer.gif?")
if b != -1:
fb_pixel = item[0][b + 11:len(item[0])]
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels5:
fb_unique_pixels5.append(fb_pixel)
logp5.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
p1 = len(fb_unique_pixels1)
p2 = len(fb_unique_pixels2)
p3 = len(fb_unique_pixels3)
p4 = len(fb_unique_pixels4)
p5 = len(fb_unique_pixels5)
print('Count of pages with fb pixels [pattern1]: {0}'.format(
count_pages_pattern1))
logp1.write('Count of pages with fb pixels [pattern1]: {0}\n'.format(
count_pages_pattern1))
print('Count of pages with fb pixels [pattern2]: {0}'.format(
count_pages_pattern2))
logp2.write('Count of pages with fb pixels [pattern2]: {0}\n'.format(
count_pages_pattern2))
print('Count of pages with fb pixels [pattern3]: {0}'.format(
count_pages_pattern3))
logp3.write('Count of pages with fb pixels [pattern3]: {0}\n'.format(
count_pages_pattern3))
print('Count of pages with fb pixels [pattern4]: {0}'.format(
count_pages_pattern4))
logp4.write('Count of pages with fb pixels [pattern4]: {0}\n'.format(
count_pages_pattern4))
print('Count of pages with fb pixels [pattern5]: {0}'.format(
count_pages_pattern5))
logp5.write('Count of pages with fb pixels [pattern5]: {0}\n'.format(
count_pages_pattern5))
print('Count of unique fb pixels [pattern1]: {0}'.format(p1))
logp1.write('Count of unique fb pixels [pattern1]: {0}\n'.format(p1))
print('Count of unique fb pixels [pattern2]: {0}'.format(p2))
logp2.write('Count of unique fb pixels [pattern2]: {0}\n'.format(p2))
print('Count of unique fb pixels [pattern3]: {0}'.format(p3))
logp3.write('Count of unique fb pixels [pattern3]: {0}\n'.format(p3))
print('Count of unique fb pixels [pattern4]: {0}'.format(p4))
logp4.write('Count of unique fb pixels [pattern4]: {0}\n'.format(p4))
print('Count of unique fb pixels [pattern5]: {0}'.format(p5))
logp5.write('Count of unique fb pixels [pattern5]: {0}\n'.format(p5))
print('Total count of uniqie fb pixels: {0}'.format(p1 + p2 + p3 + p4 +
p5))
print('Total count of pages with fb pixels: {0}'.format(
count_pages_pattern1 + count_pages_pattern2 + count_pages_pattern3 +
count_pages_pattern4 + count_pages_pattern5))
logp1.close()
logp2.close()
logp3.close()
logp4.close()
logp5.close()
