send temp_var_node value to Mysql database

Question

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 on the line

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

toggle to temp_var_node, press_var_node and altit_var_node where these are node values gives me this error:

############################# code to get values of Node variables

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

Answer 1

Let’s analyze the error outputs, by 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 bank, you are passing with the type node, in those duties:

    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 making the conversion to the type you want:

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

So you won’t trouble in converting to your table type.

Addendum: In the doc of Adafruit BMP085 you have a similar example, where to display with the print conversion is made.

Answer 2

I did the conversion of the variables temp_var_node, altit_var_node and press_var_node in this way:

       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 :

Is well converted?

Answer 3

Yes because in Uaexpert of Unified Automation also has these values as you can see in the image, IE, wanted to get the Nodesid Value

I’m just not getting past the Node Id and the data type, but Value does