Insert nested list into mysql database using Python

Question

I want to insert a nested list to mysql database.

Normally, we insert a list data to mysql like this:

list_data = ['Kate', 'apple']
sql = 'INSERT INTO table VALUES ('name', 'likes')'
cursor.execute(sql, list_data)

But if

list_data = ['Kate', ['apple', 'orange', 'banana']]

or

list_data = ['Kate', {'food': 'apple', 'sports': 'running'}]

how can I do? (I mean to save the python list or dict as a string in mysql.)

Maybe you could think about flattenning your input to a list? (think CSV format). For example, your last example of list_data = ['Kate', {'food': 'apple', 'sports': 'running'}] could be transformed to input = ["Kate", "apple", "running"], where the column names are Name, Food, and Sports — rst-2cv
– rst-2cv, Commented Oct 9, 2019 at 12:44
For this example i would like to use only two columns 'name' and 'likes', and want the whole dict {'food': 'apple', 'sports': 'running'} to be in the 'likes' column. — Bruce Feng
– Bruce Feng, Commented Oct 9, 2019 at 12:54
What happens if you pass the dictionary as a string; i.e., list_data = ['Kate', "{'food': 'apple', 'sports': 'running'}"] (note the quotes around the dictionary) — rst-2cv
– rst-2cv, Commented Oct 9, 2019 at 12:56
Yes, that's how I'm doing it now and it works. Actually I have a lot of this kind of data to insert, so I was trying to find a more straightforward way instead of transfering them to string format every time. — Bruce Feng
– Bruce Feng, Commented Oct 10, 2019 at 1:42

Bharat Gera · Accepted Answer · 2019-10-09 12:53:09Z

2

You can use the json.dumps function in Python, Try below code:

import json
list_data = ['Kate', json.dumps(['apple', 'orange', 'banana'])]
OR, list_data = ['Kate', json.dumps({'food': 'apple', 'sports': 'running'})]
sql = 'INSERT INTO table VALUES ('name', 'likes')'
cursor.execute(sql, list_data)

Flattening your data is also a great idea but that totally depends on your DB design and requirement.

Hope this helps!

edited Oct 9, 2019 at 12:53

answered Oct 9, 2019 at 12:47

Bharat Gera

8301 gold badge5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bruce Feng Over a year ago

Yes, this works. It seems we have to transfer the list or dict type to string type before inserting them to mysql rather than insert directly.

Bharat Gera Over a year ago

When you extract the data from DB then you have to use json.loads() to convert it into Python data structure.

Bharat Gera Over a year ago

Please upvote it, if it helps in your case so that other would also get the benefit. Thanks!

Collectives™ on Stack Overflow

Insert nested list into mysql database using Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related