forked from sailik1991/PlanningAssistance
/
dbHandler.py
88 lines (72 loc) · 3.44 KB
/
dbHandler.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
import MySQLdb
import os
from flask import jsonify
class dbHandler:
def __init__(self):
self.db = MySQLdb.connect( 'localhost','root','yochan','radar' )
self.db.autocommit(True)
self.cursor = self.db.cursor()
self.tableKeys = {'fire_stations_actual': 'fire_station', 'hospitals': 'hospital', 'police_stations': 'police_station'}
def initializeDatabase(self):
try:
cmd = 'mysql -u root -pyochan radar < radar.sql'
os.system(cmd)
except:
print "[ERROR] Initializing RADAR Database"
def getObjects(self):
self.cursor.execute( 'select * from object_type' )
return self.cursor.fetchall()
def getTasks(self):
self.cursor.execute( 'select * from tasks' )
return self.cursor.fetchall()
def getFireStationsData(self):
self.cursor.execute( 'select * from fire_stations_actual' )
s = self.cursor.fetchall()
return s
def getFireStationPredicates(self):
self.cursor.execute( 'select * from predicates_for_fireStation' )
s = self.cursor.fetchall()
return s
def getHospitalData(self):
self.cursor.execute( 'select * from hospitals' )
return self.cursor.fetchall()
def getHospitalPredicates(self):
self.cursor.execute( 'select * from predicates_for_hospital' )
return self.cursor.fetchall()
def getPoliceStationData(self):
self.cursor.execute( 'select * from police_stations' )
return self.cursor.fetchall()
def getPoliceStationPredicates(self):
self.cursor.execute( 'select * from predicates_for_policeStation' )
return self.cursor.fetchall()
def getActionDurations(self):
self.cursor.execute( 'select * from durations' )
return self.cursor.fetchall()
def getSubGoalPredicates(self):
self.cursor.execute( 'select * from subgoals' )
return self.cursor.fetchall()
def getCustomCursor(self, rowsToGet, tableName, conditions='1=1' ):
self.cursor.execute( 'select {0} from {1} where {2}'.format(rowsToGet, tableName, conditions) )
return self.cursor.fetchall()
def getUIReadyData(self, data, tableName):
self.cursor.execute( 'describe {0}'.format(tableName) )
tableDesc = self.cursor.fetchall()
mutable_data = [ [] for i in range( len(data) ) ]
for i in range(len(data)):
mutable_data[i].append( data[i][0] )
for j in range(1, len(data[i] )):
s = '<div class="glyphicon glyphicon-{0}" style="color:{1};cursor:pointer" onclick="updateResource(\'{2}\', \'{3}\', \'{4}\', {5})"></div>'
if data[i][j] == 1:
mutable_data[i].append( s.format(
'ok', '#00C851', tableName, data[i][0], tableDesc[j][0], 1) )
else:
mutable_data[i].append( s.format(
'remove', '#ff4444', tableName, data[i][0], tableDesc[j][0], 0) )
return mutable_data
def updateResourcesInTable(self, resourceName, tableName, rowId, presentState):
updatedState = 1
if presentState == '1':
updatedState = 0
sql_cmd = 'update {0} set {1} = {2} where {3} like "{4}"'.format(tableName, resourceName, updatedState, self.tableKeys[ tableName ], rowId)
print(sql_cmd)
self.cursor.execute(sql_cmd)