def test_SearchRecord(self):
RecordID = 94
Response = ""
try:
c, conn = function.connection()
sql = "select * from recordtbl where ID=%s"
c.execute(sql, RecordID)
if (c.rowcount >= 1):
rows = c.fetchall()
Response = "Ok"
else:
Response = "Not"
except Exception as e:
return (str(e))
self.assertEqual("Ok", Response)
def edit(id):
"""Renders the edit page."""
try:
c, conn = function.connection()
sql = "select * from recordtbl where ID=%s"
c.execute(sql, id)
rows = c.fetchall()
except Exception as e:
return (str(e))
conn.commit()
conn.close()
return render_template('Edit.html',
title='Edit Data',
year=datetime.now().year,
message='Modify Data',
result=rows,
content_type='application/json')
def view():
"""Renders the about page."""
try:
c, conn = function.connection()
sql = "select * from recordtbl order by ID desc limit 20"
c.execute(sql)
rows = c.fetchall()
except Exception as e:
return (str(e))
conn.commit()
conn.close()
return render_template('view.html',
title='Inserted Data View',
year=datetime.now().year,
message='Your application description page.',
result=rows,
content_type='application/json')
def test_DeleteRecord(self):
RecordID = 1
Response = ""
try:
tabYear = "2018"
tabCountry = "Australia"
tabDGUID = "2016A000011124"
tabFood_categories = "Food available"
tabCommodity = "Wheat flour"
tabUOM = "Kilograms per person, per year"
tabUOM_ID = "195"
tabSCALAR_FACTOR = "units"
tabSCALAR_FACTORID = "0"
tabVECTOR = "v108209"
tabCOORDINATE = "1.1.1"
tabVALUE = "59.19"
tabSTATUS = ""
tabSYMBOL = ""
tabTERMINATED = ""
tabDECIMALS = "2"
c, conn = function.connection()
sql = "update recordtbl set REF_DATE=%s,GEO=%s,DGUID=%s,Food_categories=%s,Commodity=%s,UOM=%s,UOM_ID=%s,SCALAR_FACTOR=%s,SCALAR_ID=%s,VECTOR=%s,COORDINATE=%s,ColumnVALUE=%s,ColumnSTATUS=%s,SYMBOL=%s,ColumnTERMINATED=%s,DECIMALS=%s where ID=%s"
c.execute(
sql, (tabYear, tabCountry, tabDGUID, tabFood_categories,
tabCommodity, tabUOM, tabUOM_ID, tabSCALAR_FACTOR,
tabSCALAR_FACTORID, tabVECTOR, tabCOORDINATE, tabVALUE,
tabSTATUS, tabSYMBOL, tabTERMINATED, tabDECIMALS, recID))
if (c.rowcount >= 1):
rows = c.fetchall()
Response = "Ok"
else:
Response = "Not"
except Exception as e:
return (str(e))
self.assertEqual("Ok", Response)
import traces
from function import connection, read_all
import pandas.io.sql as sqlio
import matplotlib.pyplot as plt
import pandas as pd
start_time = pd.to_datetime('2012-06-15 00:00:00.0000')
end_time = pd.to_datetime('2012-06-16 23:59:59.9999')
conn = connection(1)
sqlCon = "select name, id from activity.sensor where house = 'HH113' and name like 'L0__'"
dfC = sqlio.read_sql_query(sqlCon, conn)
for i in range(len(dfC)):
sql = "select date, state from activity.event where sensor = '" \
+ str(dfC.loc[i, "id"]) + "' order by(date)"
dataframe = sqlio.read_sql_query(sql, conn)
dataframe.to_csv("HH113-" + str(dfC.loc[i, "name"]) + ".csv", index=False)
ts_list = read_all('HH113-L0**.csv')
count = traces.TimeSeries.merge(ts_list, operation=sum)
start = pd.to_datetime("2011-06-15 07:30:00")
end = pd.to_datetime("2011-06-15 08:00:00")
histogram = count.distribution(start, end)
print(histogram)
dfTime = pd.DataFrame(count)
dfTime.columns = ['Date', 'Value']
dfTime.to_csv('HH113-AllLight.csv', index=False)
df = dfTime.loc[(dfTime['Date'] >= start_time) & (dfTime['Date'] < end_time)]
import csv
import pandas as pd
import numpy
from function import connection, exe, rem
from time import strftime, gmtime
cursor = connection()
with open('houseActivityStats.csv', 'w', newline='') as file:
writer = csv.writer(file)
writer.writerow([
"Houses", "Activity", "Occurred", "Average Duration",
"Avg Duration Dev Std", "Avg Duration Dev Std (min)",
"Average occurred (days)"
])
df = pd.read_csv("houseActivityStats.csv")
df1 = pd.read_csv("houseStats.csv")
queryAB = 'select distinct(house, name) from activity.activity order by(house, name)'
queryCount = "select count (*) from activity.activity group by(house, name) order by(house, name)"
colAB = exe(cursor, queryAB)
colCount = exe(cursor, queryCount)
for i in range(len(colAB)):
houseAc = str(colAB[i]).split(",")
df.loc[i, 'Houses'] = rem(houseAc[0])
df.loc[i, 'Activity'] = rem(houseAc[1])
df.loc[i, 'Occurred'] = rem(colCount[i])
queryDur = "select duration from activity.activity where house ='" + rem(houseAc[0]) + "' and name = '" \
def updateFooddata():
RecID = ""
tabYear = ""
tabCountry = ""
tabDGUID = ""
tabFood_categories = ""
tabCommodity = ""
tabUOM = ""
tabUOM_ID = ""
tabSCALAR_FACTOR = ""
tabSCALAR_FACTORID = ""
tabVECTOR = ""
tabCOORDINATE = ""
tabVALUE = ""
tabSTATUS = ""
tabSYMBOL = ""
tabTERMINATED = ""
tabDECIMALS = ""
RecID = request.form['ID']
tabYear = request.form['Year']
tabCountry = request.form['Country']
tabDGUID = request.form['DGUID']
tabFood_categories = request.form['FoodCategory']
tabCommodity = request.form['Commodity']
tabUOM = request.form['UOM']
tabUOM_ID = request.form['UOM_ID']
tabSCALAR_FACTOR = request.form['SCALAR_FACTOR']
tabSCALAR_FACTORID = request.form['SCALAR_FACTORID']
tabVECTOR = request.form['VECTOR']
tabCOORDINATE = request.form['COORDINATE']
tabVALUE = request.form['VALUE']
tabSTATUS = request.form['STATUS']
tabSYMBOL = request.form['SYMBOL']
tabTERMINATED = request.form['TERMINATED']
tabDECIMALS = request.form['Decimal']
try:
c, conn = function.connection()
sql = "update recordtbl set REF_DATE=%s,GEO=%s,DGUID=%s,Food_categories=%s,Commodity=%s,UOM=%s,UOM_ID=%s,SCALAR_FACTOR=%s,SCALAR_ID=%s,VECTOR=%s,COORDINATE=%s,ColumnVALUE=%s,ColumnSTATUS=%s,SYMBOL=%s,ColumnTERMINATED=%s,DECIMALS=%s where ID=%s"
"""arg=(tabYear,tabCountry,tabDGUID,tabFood_categories,tabCommodity,tabUOM,tabUOM_ID,tabSCALAR_FACTOR,tabSCALAR_FACTORID,tabVECTOR,tabCOORDINATE,tabVALUE,tabSTATUS,tabSYMBOL,tabTERMINATED,tabDECIMALS)"""
c.execute(sql,
(tabYear, tabCountry, tabDGUID, tabFood_categories,
tabCommodity, tabUOM, tabUOM_ID, tabSCALAR_FACTOR,
tabSCALAR_FACTORID, tabVECTOR, tabCOORDINATE, tabVALUE,
tabSTATUS, tabSYMBOL, tabTERMINATED, tabDECIMALS, RecID))
conn.commit()
conn.close()
except Exception as e:
return (str(e))
return render_template(
'usuccess.html',
title='Login Page',
year=datetime.now().year,
)
def ReadWrite():
firstline = True
fileupload = ""
tabYear = ""
tabCountry = ""
tabDGUID = ""
tabFood_categories = ""
tabCommodity = ""
tabUOM = ""
tabUOM_ID = ""
tabSCALAR_FACTOR = ""
tabSCALAR_FACTORID = ""
tabVECTOR = ""
tabCOORDINATE = ""
tabVALUE = ""
tabSTATUS = ""
tabSYMBOL = ""
tabTERMINATED = ""
tabDECIMALS = ""
fileupload = request.files['DataFileUpload']
f = os.path.join(app.config['UPLOAD_FOLDER'], fileupload.filename)
fileupload.save(f)
#csvReader = csv.reader(codecs.open('file.csv', 'rU', 'utf-16'))
Start = 1
firstline = True
with open('Uploads/' + fileupload.filename, 'r') as csvfile:
csvReader = csv.reader(
codecs.open('Uploads/' + fileupload.filename, 'rU', 'utf-16'))
for ReadLine1 in csvReader:
if firstline:
firstline = False
continue
Record = ReadLine1
for i in Record:
p = i
words = [p.replace('\t', '#') for word in p]
m1 = str(words[0])
r1 = m1.split('#')
if (m1.count("#") == 5):
tabYear = r1[0]
tabCountry = r1[1]
tabDGUID = r1[2]
tabFood_categories = r1[3]
tabCommodity = r1[4]
tabUOM = r1[5]
if (m1.count("#") == 4):
tabYear = r1[0]
tabCountry = r1[1]
tabDGUID = r1[2]
tabFood_categories = r1[3]
tabCommodity = r1[4]
#tabUOM=r1[5]
if (m1.count("#") == 0):
tabCommodity = tabCommodity + "," + r1[0]
if (m1.count("#") == 1):
tabCommodity = tabCommodity + "," + r1[0]
tabUOM = r1[1]
if (m1.count("#") == 2):
tabCommodity = tabCommodity + "," + r1[0] + "," + r1[1]
tabUOM = r1[2]
if (m1.count("#") == 3):
tabCommodity = tabCommodity + "," + r1[0] + "," + r1[
1] + "," + r1[2]
tabUOM = r1[3]
if (m1.count("#") == 10):
tabUOM = tabUOM + "," + r1[0]
tabUOM_ID = r1[1]
tabSCALAR_FACTOR = r1[2]
tabSCALAR_FACTORID = r1[3]
tabVECTOR = r1[4]
tabCOORDINATE = r1[5]
tabVALUE = r1[6]
tabSTATUS = r1[7]
tabSYMBOL = r1[8]
tabTERMINATED = r1[9]
tabDECIMALS = r1[10]
if (m1.count("#") == 10):
tabUOM = tabUOM + "," + r1[0]
tabUOM_ID = r1[1]
tabSCALAR_FACTOR = r1[2]
tabSCALAR_FACTORID = r1[3]
tabVECTOR = r1[4]
tabCOORDINATE = r1[5]
tabVALUE = r1[6]
tabSTATUS = r1[7]
tabSYMBOL = r1[8]
tabTERMINATED = r1[9]
tabDECIMALS = r1[10]
try:
c, conn = function.connection()
sql = "insert into recordtbl(REF_DATE,GEO,DGUID,Food_categories,Commodity,UOM,UOM_ID,SCALAR_FACTOR,SCALAR_ID,VECTOR,COORDINATE,ColumnVALUE,ColumnSTATUS,SYMBOL,ColumnTERMINATED,DECIMALS) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#sql = "insert into recordtbl(REF_DATE,GEO,DGUID,Food_categories,Commodity,UOM,UOM_ID,SCALAR_FACTOR,SCALAR_ID,VECTOR,COORDINATE,ColumnVALUE,ColumnSTATUS,SYMBOL,ColumnTERMINATED,DECIMALS) values(tabYear,tabCountry,tabDGUID,tabFood_categories,tabCommodity,tabUOM,tabUOM_ID,tabSCALAR_FACTOR,tabSCALAR_FACTORID,tabVECTOR,tabCOORDINATE,tabVALUE,tabSTATUS,tabSYMBOL,tabTERMINATED,tabDECIMALS)"
c.execute(
sql,
(tabYear, tabCountry, tabDGUID, tabFood_categories,
tabCommodity, tabUOM, tabUOM_ID, tabSCALAR_FACTOR,
tabSCALAR_FACTORID, tabVECTOR, tabCOORDINATE,
tabVALUE, tabSTATUS, tabSYMBOL, tabTERMINATED,
tabDECIMALS))
conn.commit()
conn.close()
except Exception as e:
return ()
return redirect("view")
