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

• rst-2cv: 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
• Bruce Feng: 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.
• rst-2cv: 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)
• Bruce Feng: 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.

Answer 1

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!

• Bruce Feng: 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: When you extract the data from DB then you have to use json.loads() to convert it into Python data structure.
• Bharat Gera: Please upvote it, if it helps in your case so that other would also get the benefit. Thanks!