Problem with inserting data into MySQL - Python Help - Discussions on Python.org

相关文章推荐

暴走的树叶 · [RabbitMQ] 한 Queue에서 ...· 1 月前 ·

爱喝酒的荔枝 · 基于Docker部署Wizard文档管理系统 ...· 3 月前 ·

想发财的滑板 · 深圳铁路暑运到发旅客超2700万人次 ...· 3 月前 ·

乐观的甘蔗 · 苹果教育优惠便宜多少钱？3分钟看懂苹果教育优 ...· 4 月前 ·

孤独的火龙果 · 通过Blob流在浏览器中打开pdf文件_bl ...· 4 月前 ·

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
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.
              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.

Screenshot (240)1920×952 131 KB
Screenshot (241)1920×359 31.7 KB
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()
Screenshot (242)1255×375 23.6 KB
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.)
              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;
 Václav Brožík:
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