#!/usr/bin/env python # coding: utf-8 # In[ ]: #Hannes Krueger, 30.Apr.2018 #This script manages 'Vehicles' mySQL table, by adding new arriving vehicles ('newVehicles' table), removing leaving vehicles #(orderly via 'leavingVehicles' table or disorderly by failing communication) and regularly updating info on each known #vehicle already in the database. # In[ ]: print('Preprocessing...') #Import required libraries print('Importing libraries...') import pymysql #For mySQL connection import requests #For REST calls to Vehicles import time #Allows measuring time and delaying loops if needed import datetime #Allows use of standardised timestamps # In[ ]: #Connect to V2G Database print('Connecting to V2G Database...') mySQLconnection = pymysql.connect(host='######', user='######', password='######', db='######') mySQLconnection.autocommit(True) #removes need to commit to database changes after each change is made database = mySQLconnection.cursor() # In[ ]: print('Defining callable functions...') #Make MySQL query def mysqlquery(sQl): database.execute(sQl) #Make new database entry, Add_Vehicle def Add_Vehicle(ID, Name, Capacity, MaxChargingRate, IPaddress, SOC, ChargeValue, Mode, TargetSOC, TargetDateTime, Location): query = "INSERT INTO Vehicles (ID, Name, Capacity, MaxChargingRate, IPaddress, SOC, ChargeValue, Mode, TargetSOC, TargetDateTime, Location) VALUES (" + str(ID) + ",'" + Name + "'," + str(Capacity) + "," + str(MaxChargingRate) + ",'" + IPaddress + "'," + str(SOC) + "," + str(ChargeValue) + ",'" + Mode + "'," + str(TargetSOC) + ",'" + TargetDateTime + "','" + Location + "')" mysqlquery(query) #Remove existing database entry, Remove_Vehicle def Remove_Vehicle(ID): query = "DELETE FROM Vehicles WHERE ID=" + str(ID) mysqlquery(query) #Update existing database entry, Update_Vehicle def Update_VehicleSOC(ID, SOC): query = "UPDATE Vehicles SET SOC=" + str(SOC) + " WHERE ID=" + str(ID) mysqlquery(query) def Update_Vehicle(ID, SOC, ChargeValue, Mode, TargetSOC, TargetDateTime, Location, MaxChargingRate): query = "UPDATE Vehicles SET SOC=" + str(SOC) + " WHERE ID=" + str(ID) mysqlquery(query) query = "UPDATE Vehicles SET ChargeValue=" + str(ChargeValue) + " WHERE ID=" + str(ID) mysqlquery(query) query = "UPDATE Vehicles SET Mode=" + str(Mode) + " WHERE ID=" + str(ID) mysqlquery(query) query = "UPDATE Vehicles SET TargetSOC=" + str(TargetSOC) + " WHERE ID=" + str(ID) mysqlquery(query) query = "UPDATE Vehicles SET TargetDateTime='" + TargetDateTime + "' WHERE ID=" + str(ID) mysqlquery(query) query = "UPDATE Vehicles SET Location='" + Location + "' WHERE ID=" + str(ID) mysqlquery(query) query = "UPDATE Vehicles SET MaxChargingRate='" + str(MaxChargingRate) + "' WHERE ID=" + str(ID) mysqlquery(query) #Check for any new Vehicles on the car park def New_Vehicle(): query = 'SELECT * FROM newVehicles LIMIT 1' newVehicles=database.execute(query) if newVehicles > 0: #If any entries in new Vehicle table... global freeVehicleID first_IP=database.fetchone() #...identify first entry (an IP address) try: #...make contact Name, Capacity, MaxChargingRate, SOC, ChargeValue, Mode, TargetSOC, TargetDateTime, Location = Full_Info_Rest_Call(first_IP[0]) #...add vehicle to Car Park Table Add_Vehicle(freeVehicleID, Name, Capacity, MaxChargingRate, first_IP[0], SOC, ChargeValue, Mode, TargetSOC, TargetDateTime, Location) global VehicleNumber VehicleNumber += 1 tempVehicle = freeVehicleID, first_IP[0] #Create temporaty tuple Vehicles.append(tempVehicle) #Add new entry to vehicle list print('New vehicle added to car park, ID = ' + str(freeVehicleID) + ', now ' + str(VehicleNumber) + " vehicles") freeVehicleID = freeVehicleID + 1 #count up vehicle ID for next entry #Append log file with EV connection log logfile = open("EVarrivallog.txt","a+") logfile.write(str(datetime.datetime.now()) + "," + str(freeVehicleID) + "," + str(Name) + "," + str(Capacity) + "," + str(MaxChargingRate) + "," + str(first_IP[0]) + "," + str(SOC) + "," + str(Mode) + "," + str(TargetSOC) + "," + str(TargetDateTime) + "\n") logfile.close() except TypeError: #Raised if rest call fails for any reason: print("New vehicle could not be added due to connection failure") finally: query = "DELETE FROM newVehicles WHERE IPaddress='" + first_IP[0] + "'" mysqlquery(query) #...and remove new vehicle entry - added successfully or not CancelOrders(first_IP[0]) #...and cancel any outstanding orders for this EV New_Vehicle() #Check for any Vehicles orderly leaving the car park def Leaving_Vehicle(): query = 'SELECT * FROM leavingVehicles LIMIT 1' leavingVehicles=database.execute(query) if leavingVehicles > 0: #If any entries in leaving vehicle table... first_IP=database.fetchone() #...identify first entry (an IP address) query = 'SELECT * FROM leavingVehicles LIMIT 1' leavingVehicles=database.execute(query) #Update total car park power flow query = "SELECT ChargeValue FROM Vehicles WHERE IPaddress='" + first_IP[0] + "'" database.execute(query) flow = database.fetchone()#Latest power flow query = "UPDATE PowerFlowNow SET Total = Total + " + str(-flow[0]) mysqlquery(query) query = "DELETE FROM Vehicles WHERE IPaddress='" + first_IP[0] + "'" mysqlquery(query) #...remove vehicle from carpark global VehicleNumber #global nextVehicle VehicleNumber -= 1 query = "DELETE FROM leavingVehicles WHERE IPaddress='" + first_IP[0] + "'" mysqlquery(query) #...and remove leaving vehicle entry CancelOrders(first_IP[0]) #...and cancel any outstanding orders for this EV print("Vehicle with IP " + first_IP[0] + " left by choice, now " + str(VehicleNumber) + " vehicles") Kill_Rest_Call(first_IP[0]) #Send request to terminate EV simulation mystring=str(first_IP[0]) #Create IP string to find this vehicle entry in local vehicles list mystring.replace("(", "") mystring.replace(")", "") #Append log file with EV connection log logfile = open("EVdeparturelog.txt","a+") logfile.write(str(datetime.datetime.now()) + "," + str(first_IP[0]) + ", orderly" + "\n") logfile.close() for i, elem in enumerate(Vehicles): #Where is this IP in local vehicles list? if mystring in str(elem): del Vehicles[i] #Remove vehicle from local list of IDs and IPs break #Request all availe info from a known vehicle IP address def Full_Info_Rest_Call(IP): RESTcall = "http://" + IP + "/info/full" #print(RESTcall) try: RESTresponse = requests.get(RESTcall) Response=str(RESTresponse.text) #Create Response String for parsing Response=Response.replace('\n', '') #Remove newline from response string Response=Response.replace('"', '') #Remove quotation marks from response string #print("Full_Info_Rest_Call on " + IP + " response: " + Response) #Parse response assume that: #- Values seperated by ';' #- Values in Order: Name; Capacity; MaxChargingRate; SOC; ChargeVAL; Mode; Target SOC; Target Date/Time ('YYYY-MM-DD HH:MM:SS'); Location #- Values in the appropriate format Name, Capacity, MaxChargingRate, SOC, ChargeValue, Mode, TargetSOC, TargetDateTime, Location = Response.split(";") #Split into substrings return Name, float(Capacity), float(MaxChargingRate), float(SOC), float(ChargeValue), Mode, float(TargetSOC), TargetDateTime, Location except: #Any problems at this point? print("Could not connect to: " + IP) #Request all availe info from a known vehicle IP address def Short_Info_Rest_Call(IP): try: RESTcall = "http://" + IP + "/info/short" #print(RESTcall) RESTresponse = requests.get(RESTcall) Response=str(RESTresponse.text) #Create Response String for parsing Response=Response.replace('\n', '') #Remove newline from response string Response=Response.replace('"', '') #Remove quotation marks from response string #print("Short_Info_Rest_Call on " + IP + " response: " + Response) NewTarget, SOC = Response.split(";") #Split into substrings return NewTarget, float(SOC) except: #Any problems at this point? print("Could not connect to: " + IP) #Terminate simulated vehicle via REST call def Kill_Rest_Call(IP): RESTcall = "http://" + IP + "/kill" try: RESTresponse = requests.get(RESTcall) except: #No response expected so connection error means successful termination print("Vehicle removed") #Cancel all outstanding charging/discharging orders for particular vehicle def CancelOrders(IP): query="UPDATE Schedule SET Status = 'cancelled' WHERE IPaddress = '" + str(IP) + "'" database.execute(query) # In[ ]: #Assign Global variables print('Assigning global variables...') global freeVehicleID #The next vehicle ID to be assigned query="SELECT MAX(ID)FROM Vehicles" #What is the highest vehicle ID already assigned? database.execute(query) data = database.fetchone() if data[0] == None: #If vehicle database is currently empty freeVehicleID = 0 else: freeVehicleID = data[0] + 1 #The next free ID print('Next free vehicle ID: ' + str(freeVehicleID)) query="SELECT count(*) FROM Vehicles" #How many vehicles exist before program execution? database.execute(query) data = database.fetchone() VehicleNumber = data[0] print(str(VehicleNumber) + ' vehicles in car park') #Make local multi-dimensional list with all vehicle ID's and IP's already available Vehicles = [] #Declare empty list, fill with [ID, IP] lists query="SELECT ID, IPaddress FROM Vehicles" database.execute(query) data = database.fetchall() for x in range(0, VehicleNumber): Vehicles.append(data[x]) data = None #Free memory space global nextVehicle nextVehicle = 0 #Int value to loop through all known vehicles CycleTime = time.time() #Variable to measure time per 'management cycle' for benchmarking # In[ ]: print('Preprocessing complete, starting vehicle management') #Start program loop try: while True: #Infinite loop #What is the next known vehicle to contact? #global nextVehicle #Is a new Vehicle detected? New_Vehicle() #Is a Vehicle leaving? (orderly car park exit) Leaving_Vehicle() if (VehicleNumber) > 0: #Only do it if there are Vehicles try: #Contact next known vehicles tempVehicle=Vehicles[nextVehicle] #Select next Vehicle entry tempID, tempIP = tempVehicle #Select ID and IP address NewTarget, SOC = Short_Info_Rest_Call(tempIP) #Update SOC at every loop Update_VehicleSOC(tempID, SOC) #Update full vehicle info only if changes occured if NewTarget == "True": a,b,c,d,e,f,g,h,i = Full_Info_Rest_Call(tempIP) #Save full vehicle info in placeholder variables Update_Vehicle(tempID,d,e,f,g,h,i,c) #Pass on full vehicle info to update database print("Updated info on vehicle " + str(tempID)) except TypeError: #Raised if rest calls do not return adequate values (probably due to earlier ConnectionError) Remove_Vehicle(tempID) #Remove Vehicle from SQL database due to failed connection (disorderly car park exit) del Vehicles[nextVehicle] #Remove vehicle from local list of IDs and IPs VehicleNumber -= 1 #Decrease number of known vehicles print("Vehicle " + str(tempID) + " removed due to connection failure, now " + str(VehicleNumber) + " vehicles") CancelOrders(tempIP) #...and cancel any outstanding orders for this EV nextVehicle -= 1 #Decrement to avoid skipping next vehicle in line logfile = open("EVdeparturelog.txt","a+") logfile.write(str(datetime.datetime.now()) + "," + str(tempIP) + ", disorderly" + "\n") logfile.close() except IndexError: nextVehicle = -1 #Update next vehicle in line if nextVehicle < (VehicleNumber-1): nextVehicle += 1 #Increment by 1 else: nextVehicle = 0 #Reset timer LastCycleTime = CycleTime CycleTime = time.time() #Append log file with data collection benchmark logfile = open("datacollectionlog.txt","a+") logfile.write(str(datetime.datetime.now()) + "," + str(VehicleNumber) + "," + str(CycleTime - LastCycleTime) + "\n") logfile.close() if (VehicleNumber) < 10: time.sleep(0.3) #Delay for 1 second to avoid excessive communication with few vehicles elif (VehicleNumber) < 30: time.sleep(0.1) #Delay for 0.5 seconds to avoid excessive communication with few vehicles except KeyboardInterrupt: print("Ending vehicle management") quit()