0

I reading data from a serial port and trying to print the data onto the database, whenever I try and print the data to my DB it is only printing the data to the first column instead of being spread out.

READ_DATA

    def read_data(self):
        if self.serial is not None and self.serial.is_open:
            while self.serial.in_waiting:
                raw_data = self.serial.readline()
                data_str = raw_data.decode(errors='ignore').strip()
                self.desc.append(data_str)

                lines = data_str.split(',')
                print("Data to be uploaded:", lines)
                print("Raw Data:", raw_data)
                self.upload_callback(lines)

UPLOAD_DATA_TO_DATABASE

def upload_data_to_database(data):
    try:
        # Establish a connection to the database
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="Codyb12345",
            database="dongle"
        )

        # Create a cursor object to execute SQL statements
        cursor = connection.cursor()

        # Define the SQL statement to insert data into the table
        insert_query = """
            INSERT INTO data (emptySet, manfName, modelNum, serialNum, revNum, device_type, whats_taken, body, 
            temperature, unit, emptySet2, additionalInt1, additionalInt2)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        # Process and insert each line of data
        for line in data:
            # Skip empty lines
            if line:
                # Extract the values from the line
                values = line.split(",")

                # If the number of values doesn't match the number of placeholders, insert None for missing values
                if len(values) < 13:
                    values += [None] * (13 - len(values))

                # Execute the SQL statement with the extracted values
                cursor.execute(insert_query, values)

        # Commit the changes to the database
        connection.commit()

        print("Data inserted successfully")

    except mysql.connector.Error as error:
        print("Error inserting data:", error)

    finally:
        # Close the cursor and connection
        if cursor:
            cursor.close()
        if connection:
            connection.close()

mySQL Output: It is only printing the 3 one characters because it is set to VARCHAR(1)

Terminal output:

Data to be uploaded: ['']
Raw Data: b'\n'
Data inserted successfully
Data to be uploaded: ['Trimedika']
Raw Data: b'Trimedika\n'
Error inserting data: 1406 (22001): Data too long for column 'emptySet' at row 1
Data to be uploaded: ['TTC']
Raw Data: b'TTC\n'
Error inserting data: 1406 (22001): Data too long for column 'emptySet' at row 1
Data to be uploaded: ['MJ200540011']
Raw Data: b'MJ200540011\n'
Error inserting data: 1406 (22001): Data too long for column 'emptySet' at row 1
Data to be uploaded: ['1.0.12']
Raw Data: b'1.0.12\n'
Error inserting data: 1406 (22001): Data too long for column 'emptySet' at row 1
Data to be uploaded: ['Health Thermometer']
Raw Data: b'Health Thermometer\n'
Error inserting data: 1406 (22001): Data too long for column 'emptySet' at row 1
Data to be uploaded: ['Temperature']
Raw Data: b'Temperature\n'
Error inserting data: 1406 (22001): Data too long for column 'emptySet' at row 1
Data to be uploaded: ['Body']
Raw Data: b'Body\n'
Error inserting data: 1406 (22001): Data too long for column 'emptySet' at row 1
Data to be uploaded: ['31.5']
Raw Data: b'31.5\n'
Error inserting data: 1406 (22001): Data too long for column 'emptySet' at row 1
Data to be uploaded: ['C']
Raw Data: b'C\n'
Data inserted successfully
Data to be uploaded: ['']
Raw Data: b'\n'
Data inserted successfully
Data to be uploaded: ['1']
Raw Data: b'1\n'
Data inserted successfully
Data to be uploaded: ['0']
Raw Data: b'0\n'
Data inserted successfully
2Thingz
  • 1
  • 2

0 Answers0