send temp_var_node value to the MySQL database

Navigation
0

I want to send the temperature value in OPCUA to the MySQL database, in this case the value of temp_var_node, my code is as follows: 

import sys
sys.path.insert(0, "..")
import time
import datetime
import subprocess
import logging

from opcua import Server
import BMP085
import mysql.connector as mysql

##### Sensor Data of BMP180 ######################
bmp = BMP085.BMP085()
temperature =bmp.read_temperature()
altitude = bmp.read_altitude()
pressure = bmp.read_pressure()
################################################

if __name__ == "__main__":
    logging.basicConfig(level = logging.WARNING)

# get Objects node, this is where we should put our nodes
# setup our server
server = Server()
server.set_endpoint("opc.tcp://192.168.1.10:4840/freeopcua/server/")

# setup our own namespace, not really necessary but should as spec
uri = "http://192.168.1.10:4840/freeopcua/server/"
idx = server.register_namespace(uri)

# get Objects node, this is where we should put our nodes
objects = server.get_objects_node()

# populating our address space
################################### Object ####################################################
sensordata = objects.add_object(idx, "Sensor One BMP180")
############################# Variables of Object #############################################
temp_var_node= sensordata.add_variable(idx, "Temperature Sensor One", temperature)
print("the temp_var_node is:", temp_var_node)
altit_var_node = sensordata.add_variable(idx, "Altitude Sensor One", altitude)
print("the altit_var_node is:", altit_var_node)
press_var_node = sensordata.add_variable(idx, "Pressure Sensor One", pressure)
print("the pressure_var_node is:", press_var_node)
#################### Set MyVariable to be writable by clients #################################

temp_var_node.set_writable()# Set MyVariable to be writable by clients
altit_var_node.set_writable()
press_var_node.set_writable()

######################### Sending data values of variables to the database ####################################

db = mysql.connect(host = '192.168.1.15',port = 3306,user = 'root',password = 'admin',database = 'OPCUA')
cursor = db.cursor()
delete = "DROP TABLE sensorBMP180_raspberrypi2"
cursor.execute(delete)

sql = """CREATE TABLE sensorBMP180_raspberrypi2 (
         temperature DOUBLE,
         altitude DOUBLE,
         pressure INT)"""
cursor.execute(sql)

# starting de server OPCUA!
server.start()
try:

    while True:
 ############## Values on UaExpert ###########################################     
        temp_var_node.set_value(bmp.read_temperature())
        time.sleep(2)   
        altit_var_node.set_value(bmp.read_altitude())
        time.sleep(2)
        press_var_node.set_value(bmp.read_pressure())
        time.sleep(2)
################## Values to DataBase #########################################
        temperature = bmp.read_temperature()
        pressure = bmp.read_pressure()
        altitude = bmp.read_altitude()
        time.sleep(2)
        cursor.execute("""INSERT INTO sensorBMP180_raspberrypi2 VALUES (%s, %s, %s)""",(temperature,pressure,altitude))
        db.commit()

finally:

    #close connection, remove subcsriptions, etc
    server.stop()
    db.close()
#################################################################################

When in line 

cursor.execute("""INSERT INTO sensorBMP180_raspberrypi2 VALUES (%s, %s, %s)""",(temperature,pressure,altitude))

change to temp_var_node, press_var_node and altit_var_node where these are the values of the nodes gives me this error:

#############################codetoobtainthevaluesofthevariablesofthenode
temp=temp_var_node.get_value()print("the temp_var_node is:", temp)
       time.sleep(1)
       altit = altit_var_node.get_value()
       print("the altit_var_node is:", altit)
       time.sleep(1)
       press = press_var_node.get_value()
       print("the pressure_var_node is:", press)
       time.sleep(1)

       cursor.execute("""INSERT INTO sensorBMP180_raspberrypi1 VALUES (%s, %s, %s)""",(temp, altit, press))
       db.commit()

    
asked by anonymous 17.08.2018 / 00:54

3 answers

2

Let's look at the error outputs, in parts.

The first error output says:

  

AttributeError: 'MySQLConverter' object has no attribute '_node_to_mysql'

And the second:

  

TypeError: Python 'node' cannot be converted to a MySQL type

This suggests that instead of passing the variables as they are in your table in the database, you are passing with type node , in these assignments: 

    temperature = bmp.read_temperature() -> está indo node e não double
    pressure = bmp.read_pressure() --> está indo node e não int
    altitude = bmp.read_altitude() --> está indo node e não double

What could be easily fixed by converting to the type you want: 

        temperature = float(bmp.read_temperature()) 
        pressure = int(bmp.read_pressure()) 
        altitude = float(bmp.read_altitude())

So you will not have issues converting to the type of your table.

Addendum: Na #

You have a similar example where to display with print conversion is done.

    
17.08.2018 / 04:02
0

I've done the temp_var_node, altit_var_node and press_var_node variables conversion like this: 

       temp = temp_var_node.get_value()
       print("the temp_var_node is:", temp)
       time.sleep(1)
       altit = altit_var_node.get_value()
       print("the altit_var_node is:", altit)
       time.sleep(1)
       press = press_var_node.get_value()
       print("the pressure_var_node is:", press)
       time.sleep(1)

       cursor.execute("""INSERT INTO sensorBMP180_raspberrypi1 VALUES (%s, %s, %s)""",(float(temp), float(altit), int(press)))
       db.commit()

and give this:

Are you well converted?

    
17.08.2018 / 04:43
0

Yes, because the Unified Automation UaExpert also has these values as you can see in the image, that is, you wanted to get the Value of NodesID

I'm just not able to pass the Node Id and the data type to the database, but the Value passes

    
17.08.2018 / 15:25
How to pass a parameter to DataPrpetyName (in DataGrid) C # Two on-change in a select