CODEWITHSUNDEEP
pythonmysqlpython-3.xarduino
Saul Salcedo
Saul Salcedo

Reputation: 79

Send data to MySQL received from serial port

I have this code in python, what it does is read a data that comes from the serial port; The data is sent from one XBee and received by another XBee, the second XBee is connected to a computer port, and the data it receives is what I read, then I try to send this data to a mysql database, but I send the following error:

"MySQLdb._exceptions.ProgrammingError: %b requires a bytes-like object, or an object that implements bytes, not 'dict'"

CODE:

import serial
from xbee import XBee
import MySQLdb

serial_port = serial.Serial('COM5', 9600)
xbee = XBee(serial_port)

while True:
    try:
        value = xbee.wait_read_frame()
        sql_conn = MySQLdb.connect('localhost', 'root', 'pass', 'XBee')
        cursor = sql_conn.cursor()
        cursor.execute("INSERT INTO xbeedata (value) VALUES (%s)", (value))
        data = cursor.fetchall()
        cursor.close()
        print (xbee.wait_read_frame())


    except KeyboardInterrupt:
        break

serial_port.close()

Regards.

Upvotes: 0

Views: 1437

Answers (1)

Masoud Rahimi
Masoud Rahimi

Reputation: 6031

There are some problems with your code:

  1. You don't need to connect to your MySQL DB each time in a loop.

  2. Accordign to docs:

    Information returned from this library is a dictionary in the following format:

    {'id':str,
 'param':binary data,
 ...}

    The id field is always a human-readable name of the packet type received. All following fields, shown above with the key ‘param’, map binary data to each of the possible fields contained within the received data frame.

So the value is a dictionary type and you need to use the param key which is your valid data. I'm not sure what is the param in your example but you can simply put a print(value) and see the results. If you still insist on adding value to your table, you need to serialize it like a JSON string.

import serial
from xbee import XBee
import MySQLdb
import json
from pprint import pprint


serial_port = serial.Serial('COM5', 9600)
xbee = XBee(serial_port)

sql_conn = MySQLdb.connect('localhost', 'root', 'pass', 'XBee')
cursor = sql_conn.cursor()

while True:
    try:
        value = xbee.wait_read_frame()
        if value:
            pprint(value)
            # Assumed results in 'rf_data' ('param') key
            # the 'rf_data' is byte string so it is better to decode it first
            cursor.execute(
                """INSERT INTO xbeedata (value) VALUES ('%s')""" % (value['rf_data'].decode()))
            # or if you want to insert whole value
            # cursor.execute(
            #     """INSERT INTO xbeedata (value) VALUES ('%s')""" % (json.dumps(value)))
            sql_conn.commit()

    except KeyboardInterrupt:
        break

sql_conn.close()
serial_port.close()

Upvotes: 1

Related Questions