Python script to communicate with serial gateway



  • Hi Guys,

    This is more of missing piece of puzzle, i have googled a lot to find some simple way to communicate with mysensors gateway but google this time failed me hence i decided to do this myself.

    i have this script running over one year, yes i have made some improvements over the year and here is final version. its self explanatory, feel free to use it as you wish and modify and if you think some improvements can be made let me know my ears are all yours... 👂

    i have this raspberry pi base heating control that i built but i m not sure if i can share it here and what are the implications etc.... until then have fun and enjoy creating things (IOT)....

    #!/usr/bin/python
    print " "
    print "  _____    _   _    _                            "
    print " |  __ \  (_) | |  | |                           "
    print " | |__) |  _  | |__| |   ___    _ __ ___     ___ "
    print " |  ___/  | | |  __  |  / _ \  | |_  \_ \   / _ \ "
    print " | |      | | | |  | | | (_) | | | | | | | |  __/"
    print " |_|      |_| |_|  |_|  \___/  |_| |_| |_|  \___|"
    print " "
    print "    S M A R T   H E A T I N G   C O N T R O L "
    print "*******************************************************"
    print "*  Serial Gateway Build for Wireless sensors using    *"
    print "*  MySeonsors serial API Build Date: 18/09/2017       *"
    print "*                                Have Fun - PiHome.eu *"
    print "*******************************************************"
    print " "
    print " "
    
    import MySQLdb as mdb
    import sys
    import serial
    import time
    # ps. you can troubleshoot with "screen" 
    #screen /dev/ttyAMA0 115200
    ser = serial.Serial('/dev/ttyAMA0', 115200, timeout=0)
    in_str = ser.readline()
    print in_str
    while 1:
    	try:
    		con = mdb.connect('localhost', 'root', 'passw0rd', 'pihome') # MySQL Database Connection Settings
    		cur = con.cursor()  Cursor object to Current Connection
    		cur.execute('SELECT COUNT(*) FROM `messages_out` where sent = 0') # MySQL query statement
    		count = cur.fetchone() # Grab all messages from database for Outgoing. 
    		count = count[0] # Parse first and the only one part of data table named "count" - there is number of records grabbed in SELECT above
    		if count > 0: #If greater then 0 then we have something to send out. 
    			print "Total Messages to Sent : ",count # Print how many Messages we have to send out.
    			cur.execute('SELECT * FROM `messages_out` where sent = 0') #grab all messages that where not send yet (sent ==0)
    			msg = cur.fetchone(); 	#Grab first record and build a message: if you change table fields order you need to change following lines as well. 
    			out_id = int(msg[0]) 	#Record ID - only DB info,
    			out_node_id = msg[1] 	#Node ID 
    			out_child_id = msg[2] 	#Child ID of the node where sensor/relay is attached.
    			out_sub_type = msg[3] 	#Command Type  
    			out_ack = msg[4] 		#Ack req/resp
    			out_type = msg[5]  		#Type  
    			out_payload = msg[6] 	#Payload to send out. 
    			sent = msg[7] 			#Status of message either its sent or not. (1 for sent, 0 for not sent yet)
    			print "Date & Time:            ",time.ctime()
    			print "Message From Database:  ",out_id, out_node_id, out_child_id, out_sub_type, out_ack, out_type, out_payload, sent #Print what will be sent including record id and sent status.
    			msg = str(out_node_id) 	#Node ID
    			msg += ';' 				#Separator
    			msg += str(out_child_id) #Child ID of the Node.
    			msg += ';' 				#Separator
    			msg += str(out_sub_type) 
    			msg += ';' 				#Separator
    			msg += str(out_ack) 
    			msg += ';' 				#Separator
    			msg += str(out_type)
    			msg += ';' 				#Separator
    			msg += str(out_payload) #Payload from DB
    			msg += ' \n'			#New line 		
    			print "Full Message to Send:   ",msg #Print Full Message
    			print "Node ID:                 ",out_node_id
    			print "Child Sensor ID:         ",out_child_id
    			print "Command Type:            ",out_sub_type			
    			print "Ack Req/Resp:            ",out_ack			
    			print "Type:                    ",out_type			
    			print "Pay Load:                ",out_payload
    			print " \n"
    			# node-id ; child-sensor-id ; command ; ack ; type ; payload \n
    			ser.write(msg) # !!!! send it to serial (arduino attached to rPI by USB port)
    			# help http://stackoverflow.com/questions/21740359/python-mysqldb-typeerror-not-all-arguments-converted-during-string-formatting
    			cur.execute('UPDATE `messages_out` set sent=1 where id=%s', [out_id]) #update DB so this message will not be processed in next loop
    			con.commit() #commit above
    	except mdb.Error, e:
    		print "Error %d: %s" % (e.args[0], e.args[1])
    		sys.exit(1)
    	finally:
    		if con:
    			con.close()
    	in_str = ser.readline() #Here is receiving part of the code
    	# ..:: Un-comments Following two lines to see what you are receing and size of string ::..
    	# print "Size of String:          ", sys.getsizeof(in_str)," \n"
    	# print "String as Received:      ",in_str," \n"
    	if not sys.getsizeof(in_str) <= 22 : # and in_str[:1] == '0': #here is the line where sensor IDs over 100 are processed
    		print "Date & Time:                 ",time.ctime()
    		print "Size of the String:      ", sys.getsizeof(in_str)
    		print "Full String Received:        ",in_str
    		statement = in_str.split(";")
    		print "Full Statement Received:     ",statement
    		node_id = int(statement[0])
    		print "Node ID:                     ",node_id
    		child_sensor_id = int(statement[1])
    		print "Child Sensor ID:             ",child_sensor_id
    		message_type = int(statement[2])
    		print "Message Type:                ",message_type
    		ack = int(statement[3])
    		print "Acknowledge:                 ",ack
    		sub_type = int(statement[4])
    		print "Sub Type:                    ",sub_type
    		payload = statement[5]
    		print "Pay Load:                    ",payload
    		try:
    			con = mdb.connect('localhost', 'root', 'passw0rd', 'pihome')#Database Connection Settings 
    			cur = con.cursor()
    
    			# ..::Step One::..
    			# First time sensor comes online and add node to nodes table, 
    			if (child_sensor_id != 255 and message_type == 0):
    				cur.execute('SELECT COUNT(*) FROM `nodes` where node_id = (%s)', (node_id)) 
    				row = cur.fetchone()  
    				row = int(row[0])
    				if (row == 0):
    					print "1 Add Node: ", node_id, " Child Sensor ID:", child_sensor_id
    					cur.execute('INSERT INTO nodes(node_id, child_id_1) VALUES(%s,%s)', (node_id,child_sensor_id))
    					con.commit()
    					
    			# ..::Step Two ::..
    			# Add Nodes name ie. relay, temperature sensor etc... to Database
    			if (child_sensor_id == 255 and message_type == 3 and sub_type == 11):
    				payload = payload[:-1] # remove \n from payload otherwise you will endup two lines sensors name in database. 
    				print "2 Update NodeID:", node_id, "Child Sensor ID:", child_sensor_id, " Sensor Type:", payload
    				cur.execute('UPDATE nodes SET name = %s where node_id = %s', (payload, node_id))
    				con.commit()
    
    			# ..::Step Three ::..
    			# Add Nodes MySensors Version to database 
    			if (node_id != 0 and child_sensor_id == 255 and message_type == 0 and sub_type == 17):
    				payload = payload[:-1] # remove \n from payload otherwise you will endup two lines sensors name in database. 
    				print "3 Update NodeID:", node_id, "Child Sensor ID:", child_sensor_id, " Sensor Type:", payload
    				cur.execute('UPDATE nodes SET ms_version = %s where node_id = %s', (payload, node_id))
    				con.commit()
    
    			# ..::Step Four ::..
    			# Add Nodes MySensors Version to database 
    			if (node_id != 0 and child_sensor_id == 255 and message_type == 3 and sub_type == 12):
    				payload = payload[:-1] # remove \n from payload otherwise you will endup two lines sensors name in database. 
    				print "4 Update NodeID:", node_id, "Child Sensor ID:", child_sensor_id, " Sensor Type:", payload
    				cur.execute('UPDATE nodes SET sketch_version = %s where node_id = %s', (payload, node_id))
    				con.commit()
    				
    			# ..::Step Five::..
    			# Add temperature Reading to database 
    			if (node_id != 0 and child_sensor_id != 255 and message_type == 1 and sub_type == 0):
    				print "5. Adding Database Record: Node ID:", node_id, " Child Sensor ID:", child_sensor_id, " PayLoad:", payload, "\n"
    				cur.execute('INSERT INTO messages_in(node_id, child_id, sub_type, payload) VALUES(%s,%s,%s,%s)', (node_id,child_sensor_id,sub_type,payload))
    				con.commit()
    				cur.execute('UPDATE `nodes` SET `last_seen`=now() WHERE node_id = %s', [node_id])
    				con.commit()
    
    			# ..::Step Six::..
    			# Add Battery Level to Database
    			# 20;255;3;0;0;102
    			if (child_sensor_id == 255 and message_type == 3 and sub_type == 0):  #BATTERY Level
    				print "6. Adding Database Record: Node ID:", node_id, " Battery Level:", payload, "Battery Voltage ", b_volt, "\n"
    				cur.execute('INSERT INTO nodes_battery(node_id, bat_level, bat_voltage) VALUES(%s,%s,%s)', (node_id,payload,b_volt))
    				cur.execute('UPDATE `nodes` SET `last_seen`=now() WHERE node_id = %s', [node_id])
    				con.commit()
    
    			# ..::Step Seven::..
    			# Add Battery Voltage to Database
    			# 20;1;1;0;38;3.78
    			# node-id ; child-sensor-id ; command ; ack ; type ; payload \n
    			if (child_sensor_id != 255 and message_type == 1 and sub_type == 38):  #BATTERY VOLTAGE
    				b_volt = payload
    
    				# ..::Step Eight::..
    			# Add Bost Status Level to Database 
    			if (node_id != 0 and child_sensor_id != 255 and message_type == 1 and sub_type == 2):
    			# print "2 insert: ", node_id, " , ", child_sensor_id, "payload", payload
    				print "8. Adding Database Record: Node ID:", node_id, " Child Sensor ID:", child_sensor_id, " PayLoad:", payload, "\n"
    				xboost = "UPDATE boost SET status=%s WHERE boost_button_id=%s AND boost_button_child_id = %s"
    				cur.execute(xboost, (payload, node_id,child_sensor_id,))
    				con.commit()
    				cur.execute('UPDATE `nodes` SET `last_seen`=now() WHERE node_id = %s', [node_id])
    				con.commit()				
    		except mdb.Error, e:
    				print "Error %d: %s" % (e.args[0], e.args[1])
    				sys.exit(1)
    		finally:
    			if con:
    				con.close()
    	time.sleep(1)	```
    
    
    Table for all in-coming message in mysql database:
    
    

    CREATE TABLE messages_in (
    id INT(11) NOT NULL AUTO_INCREMENT,
    node_id TINYINT(4) NULL DEFAULT NULL,
    child_id TINYINT(4) NULL DEFAULT NULL,
    sub_type INT(11) NULL DEFAULT NULL,
    payload DECIMAL(10,2) NULL DEFAULT NULL,
    datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
    )
    COLLATE='utf16_bin'
    ENGINE=InnoDB
    AUTO_INCREMENT=1;

    
    Table for all outgoing message in mysql database:
    
    

    CREATE TABLE messages_out (
    id INT(11) NOT NULL AUTO_INCREMENT,
    node_id INT(11) NOT NULL COMMENT 'Node ID',
    child_id INT(11) NOT NULL COMMENT 'Child Sensor',
    sub_type INT(11) NOT NULL COMMENT 'Command Type',
    ack INT(11) NOT NULL COMMENT 'Ack Req/Resp',
    type INT(11) NOT NULL COMMENT 'Type',
    payload VARCHAR(100) NOT NULL COMMENT 'Payload' COLLATE 'utf8_general_ci',
    sent TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Sent Status 0 No - 1 Yes',
    datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Current datetime',
    zone_id INT(11) NOT NULL COMMENT 'Zone ID related to this entery',
    PRIMARY KEY (id)
    )
    COLLATE='utf32_bin'
    ENGINE=InnoDB
    AUTO_INCREMENT=1;```


Log in to reply
 

278
Online

6.7k
Users

7.6k
Topics

80.4k
Posts

Looks like your connection to MySensors Forum was lost, please wait while we try to reconnect.