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")