# Problem with inserting data into MySQL

**URL:** <https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506>\
**Category:** Python Help\
**Tags:** help\
**Created:** [October 29, 2022, 5:41am UTC](https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506 "2022-10-29T05:41:12Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![MohammadMAhdi](https://avatars.discourse-cdn.com/v4/letter/m/bbce88/32.png) [@MohammadMAhdi](https://discuss.python.org/u/MohammadMAhdi)\
**Post date:** [October 29, 2022, 5:41am UTC](https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506/1 "2022-10-29T05:41:12Z")

</div>

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:

```auto
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:

```auto
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

---

<div class="post-metadata">

**Author:** ![vbrozik](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/vbrozik/32/7423_2.png) [@vbrozik](https://discuss.python.org/u/vbrozik)\
**Post date:** [October 29, 2022, 9:36am UTC](https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506/2 "2022-10-29T09:36:54Z")

</div>

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

```python
val = list(master_data, )

```

By this:

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

```

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

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

---

<div class="post-metadata">

**Author:** ![MohammadMAhdi](https://avatars.discourse-cdn.com/v4/letter/m/bbce88/32.png) [@MohammadMAhdi](https://discuss.python.org/u/MohammadMAhdi)\
**Post date:** [October 30, 2022, 6:19am UTC](https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506/3 "2022-10-30T06:19:59Z")

</div>

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)](https://us1.discourse-cdn.com/flex002/uploads/python1/original/2X/5/58befaf260cea769ea940cd5146e5e66d1d79fce.png)  
 ![Screenshot (241)](https://us1.discourse-cdn.com/flex002/uploads/python1/original/2X/3/3f5ce5537a1c1a2bbd9be2d4e3f5f077cf683360.png)

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

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

```

 ![Screenshot (242)](https://us1.discourse-cdn.com/flex002/uploads/python1/original/2X/0/052f3563390f3c5b3de619909e080c26f10d4560.png)

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

---

<div class="post-metadata">

**Author:** ![vbrozik](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/vbrozik/32/7423_2.png) [@vbrozik](https://discuss.python.org/u/vbrozik)\
**Post date:** [October 30, 2022, 11:56am UTC](https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506/4 "2022-10-30T11:56:35Z")

</div>

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:

```python
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?

```sql
SHOW COLUMNS FROM vib_data2;

```

---

<div class="post-metadata">

**Author:** ![MohammadMAhdi](https://avatars.discourse-cdn.com/v4/letter/m/bbce88/32.png) [@MohammadMAhdi](https://discuss.python.org/u/MohammadMAhdi)\
**Post date:** [November 2, 2022, 8:54am UTC](https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506/5 "2022-11-02T08:54:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![vbrozik](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/vbrozik/32/7423_2.png) [@vbrozik](https://discuss.python.org/u/vbrozik)\
**Post date:** [November 2, 2022, 11:01am UTC](https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506/6 "2022-11-02T11:01:15Z")

</div>

> [@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?

---

<div class="post-metadata">

**Author:** ![MohammadMAhdi](https://avatars.discourse-cdn.com/v4/letter/m/bbce88/32.png) [@MohammadMAhdi](https://discuss.python.org/u/MohammadMAhdi)\
**Post date:** [November 6, 2022, 5:10am UTC](https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506/7 "2022-11-06T05:10:10Z")

</div>

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:
> 
> ```auto
> val = list(master_data, )
> 
> ```
> 
> By this:
> 
> ```auto
> 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 🌹

---

<div class="post-metadata">

**Author:** ![Kamal4C](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/kamal4c/32/10396_2.png) [@Kamal4C](https://discuss.python.org/u/Kamal4C)\
**Post date:** [December 31, 2022, 1:20am UTC](https://discuss.python.org/t/problem-with-inserting-data-into-mysql/20506/8 "2022-12-31T01:20:31Z")

</div>

thank you  
it works