def sensor_traffic_Handler_from_Client1(Topic, jsonData):
try:
json_Dict = json.loads(jsonData)
traffic = json_Dict['traffic']
directions = json_Dict['directions']
select_minortraffic ="select direction from project_minortraffic"
cursor.execute(select_minortraffic)
trafficinfo = cursor.fetchall()
if(trafficinfo[0][0] == directions) :
postgres_insert_queryupdate="""update project_minortraffic set traffic= %s where direction =%s"""
cursor.execute(postgres_insert_queryupdate, (traffic,trafficinfo[0][0]))
elif(trafficinfo[2][0] == directions):
postgres_insert_queryupdate2 = """update project_minortraffic set traffic= %s where direction =%s"""
cursor.execute(postgres_insert_queryupdate2, (traffic, trafficinfo[2][0]))
elif(trafficinfo[3][0] == directions):
postgres_insert_queryupdate3 = """update project_minortraffic set traffic= %s where direction =%s"""
cursor.execute(postgres_insert_queryupdate3, (traffic, trafficinfo[3][0]))
else :
postgres_insert_queryupdate3 = """update project_minortraffic set traffic= %s where direction =%s"""
cursor.execute(postgres_insert_queryupdate3, (traffic, trafficinfo[1][0]))
#postgres_insert_query1 = """ INSERT INTO project_minortraffic (direction, traffic) VALUES (%s,%s)"""
#record1_to_insert = (directions, traffic)
# cursor.execute("Rollback")
#cursor.execute(postgres_insert_query1, record1_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into minortraffic table")
return(directions)
finally:
print("Inserted successfully")
def weatherinfo(weatherdetails):
Temperaturedetails = (weatherdetails[0].split(':'))[1]
Windspeed = (weatherdetails[1].split(':'))[1]
Description = (weatherdetails[2].split(':'))[1]
Feelslike = (weatherdetails[3].split(':'))[1]
Humidity = (weatherdetails[4].split(':'))[1]
Pressure = (weatherdetails[5].split(':'))[1]
Visibility =(weatherdetails[6].split(':'))[1]
if(weatherdetails[7]=="Brampton"):
direction="South to North"
elif(weatherdetails[7]=="Ottawa"):
direction = "North to South"
elif(weatherdetails[7]=="Montreal"):
direction = "East to West"
else:
direction="West to East"
#return(Temperaturedetails)
#print(Description)
#print(Feelslike)
#print(Humidity)
#print(Pressure)
#print(Visibility)
Temperature=findtemperature(Temperaturedetails)
now = datetime.now()
created_at = findcreated_at(now)
#postgres_insert_queryweather = """ INSERT INTO public.project_weather(temperature, humidity, created_at, created_time, windspeed, pressure, "Visibility", feels_like, description,direction) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
postgres_insert_queryweather= """update public.project_weather set temperature= %s,humidity =%s,created_at=%s,created_time=%s, windspeed=%s,pressure=%s,"Visibility"=%s,feels_like=%s,description=%s where direction=%s"""
record1_to_insert = (Temperature[0],Humidity,created_at, now, Windspeed, Pressure, Visibility, Feelslike, Description,direction)
# cursor.execute("Rollback")
cursor.execute(postgres_insert_queryweather, record1_to_insert)
connection.commit()
count = cursor.rowcount
def sensor_accident_Handler_from_Client1(Topic, jsonData):
try:
json_Dict = json.loads(jsonData)
accident = json_Dict['accident']
Direction = json_Dict['direction']
select_minoraccident = "select direction from project_minoraccident"
cursor.execute(select_minoraccident)
accidentinfo = cursor.fetchall()
if (accidentinfo[0][0] == Direction):
postgres_queryupdate_accident = """update project_minoraccident set minor_accident= %s where direction =%s"""
cursor.execute(postgres_queryupdate_accident,
(accident, accidentinfo[0][0]))
elif (accidentinfo[2][0] == Direction):
postgres_queryupdate2_accident = """update project_minoraccident set minor_accident= %s where direction =%s"""
cursor.execute(postgres_queryupdate2_accident,
(accident, accidentinfo[2][0]))
elif (accidentinfo[3][0] == Direction):
postgres_queryupdate3_accident = """update project_minoraccident set minor_accident= %s where direction =%s"""
cursor.execute(postgres_queryupdate3_accident,
(accident, accidentinfo[3][0]))
else:
postgres_queryupdate4_accident = """update project_minoraccident set minor_accident= %s where direction =%s"""
cursor.execute(postgres_queryupdate4_accident,
(accident, accidentinfo[1][0]))
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully in table")
return (Direction)
finally:
print("Insert into minor accident table")
def sensor_clientlatlong_from_Client1(Topic, client_latlong):
try:
now = datetime.now()
client_latlong = client_latlong.split(':')
clientinfo = "select client_name from client_latlong"
cursor.execute(clientinfo)
client_nameinfo = cursor.fetchall()
client_namelist = [item for t in client_nameinfo for item in t]
if (client_latlong[0] in client_namelist):
postgres_update_client_latlong = """update public.client_latlong set latitude= %s, longitude =%s ,created_time= %s where client_name=%s"""
cursor.execute(
postgres_update_client_latlong,
(client_latlong[1], client_latlong[2], now, client_latlong[0]))
else:
insert_client_latlong = """ INSERT INTO public.client_latlong (client_name,latitude,longitude,created_time) VALUES (%s,%s,%s,%s)"""
recordclientlatlong_to_insert = (client_latlong[0],
client_latlong[1],
client_latlong[2], now)
cursor.execute(insert_client_latlong,
recordclientlatlong_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into client_latlong table")
finally:
print("Inserted successfully")
def add_new_friends(cursor, args):
try:
friend1_id = args[0]
friend2_id = args[1]
query = f"INSERT into friends_connection VALUES ('{friend1_id}', '{friend2_id}');"
cursor.execute(query)
connection.commit()
except Exception as e:
return {'error': 500, 'details': 'adding friends' + str(e)}
def change_status(cursor, args):
try:
_id = args[0]
result = args[1]
print("CHANGE", _id, result)
query = f"UPDATE user SET status = {result} WHERE id = '{_id}';"
cursor.execute(query)
connection.commit()
except Exception as e:
return {'error': 500, 'details': 'getting on status friends' + str(e)}
def send_request(cursor, args):
try:
sender_id = args[0]
receiver_id = args[1]
message = args[2]
query = f"INSERT into requests VALUES ('{sender_id}', '{receiver_id}', '{message}');"
cursor.execute(query)
connection.commit()
except Exception as e:
return {'error': 500, 'details': 'adding friends' + str(e)}
def sensor_accident_Handler_from_Client1(Topic, jsonData):
try:
json_Dict = json.loads(jsonData)
accident = json_Dict['accident']
Direction = json_Dict['direction']
select_minoraccident = "select direction from project_minoraccident"
cursor.execute(select_minoraccident)
accidentinfo = cursor.fetchall()
#print(accidentinfo[0])
if (accidentinfo[0][0] == Direction):
postgres_queryupdate_accident = """update project_minoraccident set minor_accident= %s where direction =%s"""
cursor.execute(postgres_queryupdate_accident, (accident, accidentinfo[0][0]))
elif(accidentinfo[2][0] == Direction):
postgres_queryupdate2_accident = """update project_minoraccident set minor_accident= %s where direction =%s"""
cursor.execute(postgres_queryupdate2_accident, (accident, accidentinfo[2][0]))
elif (accidentinfo[3][0] == Direction):
#print(accidentinfo[3][0])
postgres_queryupdate3_accident = """update project_minoraccident set minor_accident= %s where direction =%s"""
cursor.execute(postgres_queryupdate3_accident, (accident, accidentinfo[3][0]))
else:
postgres_queryupdate4_accident = """update project_minoraccident set minor_accident= %s where direction =%s"""
cursor.execute(postgres_queryupdate4_accident, (accident, accidentinfo[1][0]))
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully in table")
return(Direction)
# traffic = json_Dict['traffic']
# if (Topic=='client1/accident'):
# accident = json_Dict['accident']
# print(accident)
# print("hello" + jsonData)
# subscribed_message=['jsonData']
# print(subscribed_message)
# for i in range(5, 100):
# print(i)
# directions="East to West"
# directions = get_loc1()
# msg = [("accident on lane 5 : Switch to lane 3"), ("accident on lane 5 : Switch to lane 10"),("accident on lane 5 : Switch to lane 15")]
# total_message_client1 = len(msg)
# total_message_client1 = len(jsonData)
# for j in range(1, total_message_client1):
# for subscribed_message in jsonData:
# directions = "East to West"
# postgres_insert_query = """ INSERT INTO project_minoraccident (direction, minor_accident) VALUES (%s,%s)"""
# record_to_insert = (Direction, accident)
# cursor.execute(postgres_insert_query, record_to_insert)
finally:
print("Insert into minor accident table")
def add_new_dog(cursor, args):
dog = args[0]
table_name = 'dog'
temp_dog = {}
attributes = {'id', 'photo_url', 'dog_name', 'description', 'owner_id'}
for attribute in attributes:
temp_dog[attribute] = dog.get(attribute, None)
try:
columns = ', '.join("`" + str(x).replace('/', '_') + "`"
for x in temp_dog.keys())
values = ', '.join("'" + str(x).replace('/', '_') + "'"
for x in temp_dog.values())
query = "INSERT into %s (%s) VALUES (%s);" % (table_name, columns,
values)
cursor.execute(query)
connection.commit()
except Exception as e:
return {'error': 500, 'details': 'adding dog ' + str(e)}
def sensor_clientlocation_from_Client1(Topic, client_loc):
try:
client_info = client_loc.split(':')
select_clientdirectioninfo = 'select "Name" from client_directions'
cursor.execute(select_clientdirectioninfo)
directioninfo = cursor.fetchall()
if (client_info[0] == directioninfo[0][0]):
print(client_info[0] + "Row already present")
elif (client_info[0] == directioninfo[1][0]):
print(client_info[0] + "Row already present")
else:
postgres_insert_query1 = """ INSERT INTO client_directions ("Name" , client_direction) VALUES (%s,%s)"""
record1_to_insert = (client_info[0], client_info[1])
cursor.execute(postgres_insert_query1, record1_to_insert)
connection.commit()
count = cursor.rowcount
print(count,
"Record inserted successfully into client_directions table")
finally:
print("Inserted successfully")
return (directioninfo[0][0])
def add_new_user(cursor, args):
user = args[0]
table_name = 'user'
temp_user = {}
attributes = {
'id', 'first_name', 'last_name', 'user_name', 'password', 'age',
'gender', 'city', 'phone_number', 'status', 'image_url'
}
for attribute in attributes:
temp_user[attribute] = user.get(attribute, None)
try:
columns = ', '.join("`" + str(x).replace('/', '_') + "`"
for x in temp_user.keys())
values = ', '.join("'" + str(x).replace('/', '_') + "'"
for x in temp_user.values())
query = "INSERT into %s (%s) VALUES (%s);" % (table_name, columns,
values)
cursor.execute(query)
connection.commit()
except Exception as e:
return {'error': 500, 'details': 'adding user ' + str(e)}
def update_request(cursor, args):
try:
logged_in_id = args[0]
id2 = args[1]
respond = args[2]
print(logged_in_id, id2, respond)
if respond == 'accept':
query = f"INSERT into friends_connection VALUES ('{logged_in_id}', '{id2}');"
cursor.execute(query)
connection.commit()
query = f"DELETE FROM requests where receiver_id = '{logged_in_id}' and sender_id = '{id2}';"
cursor.execute(query)
connection.commit()
else:
query = f"DELETE FROM requests where receiver_id = '{logged_in_id}' and sender_id = '{id2}';"
cursor.execute(query)
connection.commit()
print("FINISHED")
except Exception as err:
print("500 - Internal error", err)
for i, row in df.iterrows():
sc = df['SURFACE_COND_NO']
st = df['SURFACE_TEMP']
rcd = fis.road_condition(sc[i], st[i])
mpl.rc('figure', max_open_warning=0)
road_cond.append(rcd)
coefficient = round(road_cond[i], 2)
if (coefficient < 0.3):
postgreSQL_insert_RC = 'UPDATE public."RoadConditions" SET "µ(slipperycoefficient)"=%s where "Road_Condition"=%s'
cursor.execute(postgreSQL_insert_RC, (coefficient, "Very Slippery"))
elif (0.3 <= coefficient < 0.5):
postgreSQL_insert_RC = 'UPDATE public."RoadConditions" SET "µ(slipperycoefficient)"=%s where "Road_Condition"=%s'
cursor.execute(postgreSQL_insert_RC, (coefficient, "Slippery"))
elif (coefficient >= 0.5):
postgreSQL_insert_RC = 'UPDATE public."RoadConditions" SET "µ(slipperycoefficient)"=%s where "Road_Condition"=%s'
cursor.execute(postgreSQL_insert_RC, (coefficient, "Normal"))
connection.commit()
time.sleep(5)
#print(sc[i], st[i], round(road_cond[i], 2))
fig = plt.figure()
ax = fig.add_subplot(111, projection='3d')
ax.scatter(sc, st, road_cond, c='r', marker='o')
ax.set_xlabel('Surface condition')
ax.set_ylabel('Surface temp')
ax.set_zlabel('Slipperiness')
plt.show()
