Problems entering data into a table

Question

I have a Python script that reads messages coming from the serial port. When reading messages, record them in a mailing list and send them to a SQLITE3 database. However, when executing, it presents the following error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 5, and there are 1 supplied`

  import sqlite3

    conn = sqlite3.connect(database='dados.db')``

conn = sqlite3.connect(database='dados.db')
cursor = conn.cursor()
cursor.execute("""
                  CREATE TABLE dados (
                  titutlo TEXT NOT NULL,
                  command INTEGER NOT NULL,
                  tipo_dispositivo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                  id_dispositivo TEXT NOT NULL,
                  distancia INTEGER,
                  contador     VARCHAR(11) NOT NULL,
                  data  INTEGER NOT NULL
                  );
                  """)

while True: 
        command = [ser.readline().decode('utf-8').split('|')] # O decode() aqui é para converter de bytes para string         
        if  command!= '': 
         #   print(command)

            cursor.executemany("""
            INSERT INTO dados(tipo_dispositivo, id_dispositivo, distancia,contador,data)
            VALUES(?,?,?,?,?)""", command)
            conn.commit()





            #print(lista)

conn.close()`

Answer 1

First, your command of INSERT on the table dados will fail because violates the fields titulo and command who possess CONSTRAINTS of NOT NULL, that is, these fields cannot contain null values and need to be specified in the record insertion.

The error you pointed out happens because the variable command does not have 5 elements to satisfy the fields of its INSERT, the variable command is actually a list containing another list as a single element:

command = [ser.readline().decode('utf-8').split('|')]

The right thing would be:

command = ser.readline().decode('utf-8').split('|')

Data communication through the serial port is subject to interference by several factors that may affect the integrity of the received and transmitted data. There is almost always the need to create protocols and cyclic redundancy check to ensure the integrity of data trafficked.

Your main loop needs to identify and tolerate crashes without stopping to receive the data that is arriving at the port, that is, even if a corrupted line is received, the program will know to identify it as a malformed record and discard it.

Assuming the data read from the serial port is something like:

foo|X|10|20|30|40|50
bar|Y|11|22|33|44|55
baz|Z|15|25|35|45|55

Follow a program capable of tolerating data transmission and recording failures without leaving the main loop:

import sqlite3
import serial

conn = sqlite3.connect(database='dados.db')
cur = conn.cursor()

cur.execute("""
    CREATE TABLE dados
    (
        titulo TEXT NOT NULL,
        command INTEGER NOT NULL,
        tipo_dispositivo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        id_dispositivo TEXT NOT NULL,
        distancia INTEGER,
        contador VARCHAR(11) NOT NULL,
        data INTEGER NOT NULL
    );""")

# Abre porta serial
ser = serial.Serial( ... )

while True:
    print("Aguardando linha...")

    # Lê uma linha a partir da porta serial
    linha = ser.readline()

    print("Linha recebida.")

    # Codifica dados lidos para uma string UTF-8
    linha = linha.decode('utf-8','ignore').strip()

    # Recupera os campos da linha recebida
    values = linha.split('|')

    # Valida o registro recebido (7 campos)
    if len(values) != 7:
        print("Registro Malformado: Quantidade invalida de campos")
        continue

    print("Gravando registro...")

    try:
        # Insere registro na tabela
        cur.execute("""
            INSERT INTO dados (titulo, command, tipo_dispositivo, id_dispositivo, distancia, contador, data)
            VALUES(?,?,?,?,?,?,?)""", values)
        conn.commit()
    except Exception as e:
        print("O registro nao pode ser gravado: {}".format(e))
        continue

    print("Registro gravado com sucesso")

cur.close()
conn.close()