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