Problem with inserting data into MySQL

[MohammadMAhdi]

Hi
I want to read the data of a sensor and transfer it to MySQL local host with mysql.connector. The problem is that it reads the data as a list containing 500 data (that is, it reads 500 to 500, not one by one). This is my code:

master_data = master_task.read(number_of_samples_per_channel=500)
sql = "INSERT INTO vib_data2 (data) VALUES (%s)"
val = list(master_data, )
mycursor.executemany(sql, val)
mydb.commit()

I got this error:
Could not process parameters: float(-0.15846696725827258), it must be of type list, tuple or dict

I can fix the problem with this code:

for i in master_data:
   sql = "INSERT INTO vib_data2 (data) VALUES (%s)"
   val = (str(I),)
   mycursor.execute(sql, val)
   mydb.commit()

But the code execution time is very long (more than one second) and thus some of the data is lost.
Thank you for helping to correct the code

[vbrozik (Václav Brožík)]

• Please show the full traceback (not just the error). It can provide useful information about the problem.
• What does master_data contain? What does print(repr(master_data)) and print(type(master_data)) show?
• val = list(master_data, ) — Why is there the comma? It should not change the result but it is very confusing.
• val = (str(I),) — Where does I come from? Did you replace i by I by mistake?
• mydb.commit() — Do you need to commit in every iteration of the loop? Why not just after the loop?

Anyway - guessing what could be the problem

It looks like that in your failing code you are providing list of floats instead of iterable of lists of floats. Try to replace this:

val = list(master_data, )

By this:

val = ((item,) for item in master_data)

I.e. create a generator providing tuples. If conversion to string is needed:

val = ((str(item),) for item in master_data)

Note: better name would be suitable because val suggests “single value” while it looks like it contains multiple values.

[MohammadMAhdi]

Dear Václav Brožík, thank you for your quick response.
master_data is the data read from the vibration sensor every second and contains a list of 500 float numbers.

I want to send all 500 data to the host simultaneously, not one by one.

sql = "INSERT INTO vib_data2 (data) VALUES (%s)"
val = list(master_data, )
mycursor.executemany(sql, val)
mydb.commit()

I can transfer the data to the host one by one but it takes a lot of time.
(Sorry for the typos. In the second code, val = (str(i),) is correct.)

[vbrozik (Václav Brožík)]

Please next time do not send text in pictures. Send it as text — the same way you show your Python code. You can shorten your 500 item list

---

Your line:

val = list(master_data, )

is redundant it just creates a new list with the same content as the list master_data.

So it looks like my suggestion was right. Did you test it? Please let us know about the result.

---

Note — for the case type conversion is needed: What type is your data column in the vib_data2 table?

SHOW COLUMNS FROM vib_data2;

[MohammadMAhdi]

@vbrozik

You can shorten your 500 item list

I can’t shorten the 500 item list. I need the whole data. I can’t use val = ((str(item),) for item in master_data) because the code execution time is more than 1 second and again some data is lost.

[vbrozik (Václav Brožík)]

@MohammadMAhdi

I can’t shorten the 500 item list.

I meant shorten the list just for showing it here

We are awaiting the result - did my suggestion help? Did you encounter another problem while applying the suggestion?

[MohammadMAhdi]

Dear Václav Brožík
I used your suggestion and my problem was solved.

@vbrozik

It looks like that in your failing code you are providing list of floats instead of iterable of lists of floats. Try to replace this:

val = list(master_data, )

By this:

val = ((item,) for item in master_data)

I.e. create a generator providing tuples. If conversion to string is needed:

val = ((str(item),) for item in master_data)

I was wrong in calculating the time
Thank you for your guidance

[Kamal4C]

thank you
it works