def db_add_data(table_name, data_list, sw_list=set()):
command = {
'switches': 'insert into switches (hostname, location) values (?, ?)',
'dhcp': 'replace into dhcp values (?, ?, ?, ?, ?, ?, ?)'
}
con = sq.connect('dhcp_snooping.db')
print('Добавляю данные в таблицу {}...'.format(table_name))
with con:
if sw_list != set():
for sw in sw_list:
con.execute(
'update {} set active = 0 where switch = \'{}\''.format(
table_name, sw))
week_ago = da.today().replace(microsecond=0) - ti(days=7)
for l in con.execute('select * from dhcp where active=0'):
mac = l[0]
if da.strptime(l[6], '%Y-%m-%d %H:%M:%S') < week_ago:
con.execute(
'delete from dhcp where mac=\'{}\''.format(mac))
for r in data_list:
try:
if table_name == 'dhcp':
con.execute(
command[table_name],
tuple(
list(r) + [
1, '{0:%Y}-{0:%m}-{0:%d} {0:%H}:{0:%M}:{0:%S}'.
format(da.now())
]))
else:
con.execute(command[table_name], r)
except sq.IntegrityError as e:
print('При добавлении данных: {} Возникла ошибка: {}'.format(
r, e))
class CrimeReport(ServiceBase):
@srpc(String, String, String, _returns=Iterable(String))
def checkcrime(lat, lon, radius):
res = urllib.urlopen('https://api.spotcrime.com/crimes.json?lat=' +
lat + '&lon=' + lon + '&radius=' + radius +
'&key=.')
#res = urlib.urlopen('https://api.spotcrime.com/crimes.json?lat=37.334164&lon=-121.884301&radius=0.02&key=.')
crimedata = json.loads(res.read())
address_dict = {}
dict = {
"total_crime": 0,
"the_most_dangerous_streets": 0,
"crime_type_count": {
"Assault": 0,
"Arrest": 0,
"Burglary": 0,
"Robbery": 0,
"Theft": 0,
"Other": 0
},
"event_time_count": {
"12:01am-3am": 0,
"3:01am-6am": 0,
"6:01am-9am": 0,
"9:01am-12noon": 0,
"12:01pm-3pm": 0,
"3:01pm-6pm": 0,
"6:01pm-9pm": 0,
"9:01pm-12midnight": 0
}
}
dict["total_crime"] = len(crimedata['crimes'])
for each in crimedata['crimes']:
if each.get('type') == "Assault":
dict["crime_type_count"]["Assault"] += 1
elif each.get('type') == "Arrest":
dict["crime_type_count"]["Arrest"] += 1
elif each.get('type') == "Burglary":
dict["crime_type_count"]["Burglary"] += 1
elif each.get('type') == "Robbery":
dict["crime_type_count"]["Robbery"] += 1
elif each.get('type') == "Theft":
dict["crime_type_count"]["Theft"] += 1
elif each.get('type') == "Other":
dict["crime_type_count"]["Other"] += 1
timedate = each.get('date')[9:]
t1 = datetime.strptime(timedate, "%I:%M %p")
crimet = t1.time()
if crimet >= ti(00, 01, 00) and crimet <= ti(03, 00, 00):
dict["event_time_count"]["12:01am-3am"] += 1
elif crimet >= ti(03, 01, 00) and crimet <= ti(06, 00, 00):
dict["event_time_count"]["3:01am-6am"] += 1
elif crimet >= ti(06, 01, 00) and crimet <= ti(9, 00, 00):
dict["event_time_count"]["6:01am-9am"] += 1
if begin_time < end_time:
return check_time >= begin_time and check_time <= end_time
else: # crosses midnight
return check_time >= begin_time or check_time <= end_time
location = "Barron.xlsx" #location of excel file
wb = xlrd.open_workbook(location) #opens the excel file
no_of_sheets = 23
notify = Notify() #used for notification in phones
checkDict = defaultdict(int)
toRepeatList = []
count = 0 # send 40 words only
while (1):
if (is_time_between(ti(10, 00),
ti(20, 30))): #runs only between 10am to 8:30 pm
randSheet = random.randint(0, 21)
print('sheet', randSheet) #gets a random sheet number
sheet = wb.sheet_by_index(randSheet)
randRow = random.randint(2, sheet.nrows -
1) #gets a random row number (random word)
print('row', randRow)
randText = str(randSheet) + str(randRow)
if (count == 40
): #done with words for the day, just print again from list
print("Time for revision now")
notify.send(
"Time to revise those 40 words now~~. Will begin in 5 minutes")
time.sleep(300)
for i in range(len(toRepeatList)):
# def __cmp__(self, other):
# return cmp(self.id, self.id)
def __repr__(self):
return self.fact.activity
def get_text(self, fact):
text = ""
if fact.description:
text += ": %s" % (fact.description)
if fact.tags:
text += " ("+", ".join(fact.tags)+")"
return text
d = date(2005, 7, 14)
t = ti(12, 30)
facts = list([\
Fact(activity = "#123: 1", category = "category", description = "description", id=1), \
Fact(activity = "#123: 2", category = "category", description = "description", id=2), \
Fact(activity = "#222: 3", category = "category", description = "description", id=6), \
Fact(activity = "#123: 4", category = "category", description = "description", id=3), \
Fact(activity = "#222: 5", category = "category", description = "description", id=7), \
Fact(activity = "#123: 6", category = "category", description = "description", id=4), \
Fact(activity = "#123: 7", category = "category", description = "description", id=5), \
])
rows = [ExportRow(fact) for fact in facts]
tickets = {}
for ticket, group in groupby(rows, lambda export_row: export_row.id):
def actogram(template_filename, n_pir, bin_display):
from datetime import time as ti
import pandas as pd
import matplotlib.pyplot as plt
import math
template_filename = template_filename + "_parsed.txt"
nlin = round(math.sqrt(n_pir))
ncol = math.ceil(math.sqrt(n_pir))
fig, ax = plt.subplots(nlin,ncol,sharex='all',sharey='all')
for n in range(n_pir):
ls_ts=list()
ls_stat = list()
PIR_dataframe = None
try:
with open(template_filename%(n+1),'r') as f:
f.readline()
while True:
tmp=f.readline()
if tmp=='':
break
tmp=[float(x) for x in tmp.strip().split(',')]
ls_ts.append(datetime.fromtimestamp(int(tmp[0])))
ls_stat.append(tmp[1])
PIR_dataframe = pd.DataFrame({'Status':ls_stat}, index=ls_ts)
except ValueError:
# The date is already converted
PIR_dataframe = pd.read_csv(template_filename%(n+1),index_col='time', parse_dates=True)
binsize = (PIR_dataframe.index[1]-PIR_dataframe.index[0]).seconds/3600
# resample data in x-minute bins to avoid heavy figures
if bin_display:
print('binning data in %i-minute bins'%bin_display)
PIR_dataframe = PIR_dataframe.resample('%iT'%bin_display).mean()
binsize = (PIR_dataframe.index[1]-PIR_dataframe.index[0]).seconds/3600
days_array = pd.date_range(PIR_dataframe.index[0].date(), PIR_dataframe.index[-1].date()+timedelta(days=1))
n_days=len(days_array)-1
k=n_days
lin = math.floor((n)/ncol)
col = (n)%ncol
for i in range(n_days):
idx = numpy.logical_and(PIR_dataframe.index < datetime.combine(days_array[i+1], ti(0,0,0)),
PIR_dataframe.index >= datetime.combine(days_array[i], ti(0,0,0)))
x = PIR_dataframe.index[idx]
x = x.hour+x.minute/60+x.second/3600
y = PIR_dataframe.Status.iloc[idx] * 0.9
# y = numpy.ones((len(x),)) * (k-1)
# u = numpy.zeros((len(x),))
# v = PIR_dataframe.Status.iloc[idx]
if nlin>1:
# ax[lin,col].quiver(x, y, u, v,
# headwidth=0, angles='xy', scale_units='xy', scale=1)
# ax[lin,col].quiver(x+24, y+1, u, v,
# headwidth=0, angles='xy', scale_units='xy', scale=1)
# ax[lin,col].plot([0,48], [k-1,k-1], color='black')
ax[lin,col].fill_between(x, k-1, y+k-1, where=y+k>k, color='black', edgecolor='none')
ax[lin,col].fill_between(x+24, k, y+k, where=y+k>k, color='black', edgecolor='none')
ax[lin,col].plot([0,48], [k-1,k-1], color='black')
else:
# ax[lin].quiver(x, y, u, v,
# headwidth=0, angles='xy', scale_units='xy', scale=1)
# ax[lin].quiver(x+24, y+1, u, v,
# headwidth=0, angles='xy', scale_units='xy', scale=1)
# ax[lin].plot([0,48], [k-1,k-1], color='black')
ax[col].fill_between(x, k-1, y+k-1, where=y+k>k, color='black', edgecolor='none')
ax[col].fill_between(x+24, k, y+k, where=y+k>k, color='black', edgecolor='none')
ax[col].plot([0,48], [k-1,k-1], color='black')
k-=1
if nlin>1:
ax[lin,col].set_title(template_filename%(n+1))
else:
ax[col].set_title(template_filename%(n+1))
plt.xticks(range(0, 48, 6),
['00:00', '06:00', '12:00', '18:00', '00:00', '06:00', '12:00', '18:00'])
plt.yticks(numpy.arange(0.5,n_days,1), reversed(days_array[0:-1].strftime("%a %d-%m")))
plt.ylim(ymax=n_days)
plt.xlim(xmin=0,xmax=48)
plt.show()
plt.savefig(template_filename + '.png')
dict["crime_type_count"]["Robbery"] += 1
elif each.get('type') == "Theft":
dict["crime_type_count"]["Theft"] += 1
elif each.get('type') == "Other":
dict["crime_type_count"]["Other"] += 1
timedate = each.get('date')[9:]
t1 = datetime.strptime(timedate, "%I:%M %p")
crimet = t1.time()
if crimet >= ti(00, 01, 00) and crimet <= ti(03, 00, 00):
dict["event_time_count"]["12:01am-3am"] += 1
elif crimet >= ti(03, 01, 00) and crimet <= ti(06, 00, 00):
dict["event_time_count"]["3:01am-6am"] += 1
elif crimet >= ti(06, 01, 00) and crimet <= ti(9, 00, 00):
dict["event_time_count"]["6:01am-9am"] += 1
elif crimet >= ti(9, 01, 00) and crimet <= ti(12, 00, 00):
dict["event_time_count"]["9:01am-12noon"] += 1
elif crimet >= ti(12, 01, 00) and crimet <= ti(15, 00, 00):
dict["event_time_count"]["12:01pm-3pm"] += 1
elif crimet >= ti(15, 01, 00) and crimet <= ti(18, 00, 00):
dict["event_time_count"]["3:01pm-6pm"] += 1
elif crimet >= ti(18, 01, 00) and crimet <= ti(21, 00, 00):
dict["event_time_count"]["6:01pm-9pm"] += 1
elif crimet >= ti(21, 01, 00) and crimet < ti(
00, 00, 00) or crimet == ti(00, 00, 00):
dict["event_time_count"]["9:01pm-12midnight"] += 1
address = each.get('address')
ad = ''
if 'OF' in address:
ad = address.split('OF')