将嵌套数据框插入数据库

Question

我有一个数据框，其中嵌套了字典项，我无法将该数据框存储到我的数据库中。它给我一个TypeError: sequence item 0: expected str instance, dict found的错误。这个问题有解决办法吗

代码：

df = pd.DataFrame.from_dict(mprofile,orient='index')

我的字典mprofile的输出如下：

{'id': '10153836054533219', 'birthday': '04/17/1993', 'education': [{'school': {'id': '281278052799', 'name': 'Comsats Islamabad'}, 'type': 'High School', 'id': '10152109501113219'}, {'school': {'id': '114889221857515', 'name': 'IMCB G-10/4'}, 'type': 'High School', 'id': '10150528781298219'}, {'school': {'id': '281278052799', 'name': 'Comsats Islamabad'}, 'type': 'College', 'id': '10152183540228219'}], 'email': 'mj_90tr@hotmail.com', 'favorite_athletes': [{'id': '167866666571743', 'name': 'Luis Suarez'}, {'id': '169875889388', 'name': 'Philipp Lahm'}, {'id': '226625490712161', 'name': 'David Luiz'}, {'id': '79298470625', 'name': 'Andrés Iniesta'}, {'id': '511845538832537', 'name': 'Gareth Bale'}, {'id': '142523572511686', 'name': 'Ronaldinho Gaúcho'}, {'id': '176063032413299', 'name': 'Leo Messi'}, {'id': '249113725102662', 'name': 'Thomas Müller'}, {'id': '185923878109545', 'name': 'Mario Götze'}, {'id': '124037987606288', 'name': 'Mats Hummels'}, {'id': '7991759722', 'name': 'Bastian Schweinsteiger'}, {'id': '9752007795', 'name': 'Shawn Michaels'}, {'id': '148456285190063', 'name': 'Neymar Jr.'}, {'id': '552825254796051', 'name': 'Mesut Özil'}, {'id': '7550279682', 'name': 'Paul "Triple H" Levesque'}, {'id': '185950250927', 'name': 'Brock Lesnar'}, {'id': '14917350275', 'name': 'Jay Cutler'}, {'id': '169376299783862', 'name': 'Roger Federer Just peRFect'}, {'id': '417539191628925', 'name': 'Abdul Razzaq'}, {'id': '29756652821', 'name': 'Muhammad Ali'}, {'id': '606014986100673', 'name': 'Shoaib Akhtar'}, {'id': '145425262156250', 'name': 'Sheheryar-pro freestyler'}, {'id': '245323855526590', 'name': 'Wayne Rooney'}, {'id': '105587166141910', 'name': 'Shoaib Akhtar'}], 'favorite_teams': [{'id': '663371713769515', 'name': "Pakistan's Got Talent"}, {'id': '146078378744600', 'name': 'ICC - International Cricket Council'}, {'id': '599005550129062', 'name': 'Isb kpk undergrounds'}, {'id': '168868979824907', 'name': 'Drifting - Tuning'}, {'id': '322125507869686', 'name': 'KPK Underground'}, {'id': '269578509818086', 'name': 'The Bullz'}, {'id': '193575534016977', 'name': 'Twin City Freestyle Society'}, {'id': '365160980224559', 'name': 'Racing Community of Pakistan (RCOP)'}, {'id': '143806589018889', 'name': 'Pakistan Cricket Fans'}], 'first_name': 'Faizan', 'gender': 'male', 'hometown': {'id': '111501225536407', 'name': 'Islamabad, Pakistan'}, 'last_name': 'Ahmad', 'link': 'https://www.facebook.com/app_scoped_user_id/10153836054533219/', 'location': {'id': '111501225536407', 'name': 'Islamabad, Pakistan'}, 'locale': 'en_US', 'name': 'Faizan Ahmad', 'timezone': 5, 'updated_time': '2017-03-20T19:32:12+0000', 'verified': True}

mprofile是我的字典，我的数据帧的输出如下：

输出：

id                                                 10153836054533219
birthday                                                  04/17/1993
education          [{'school': {'id': '281278052799', 'name': 'Co...
email                                            mj_90tr@hotmail.com
favorite_athletes  [{'id': '167866666571743', 'name': 'Luis Suare...
favorite_teams     [{'id': '663371713769515', 'name': 'Pakistan's...
first_name                                                    Faizan
gender                                                          male
hometown           {'id': '111501225536407', 'name': 'Islamabad, ...
last_name                                                      Ahmad
link               https://www.facebook.com/app_scoped_user_id/10...
location           {'id': '111501225536407', 'name': 'Islamabad, ...
locale                                                         en_US
name                                                    Faizan Ahmad
timezone                                                           5
updated_time                                2017-03-20T19:32:12+0000
verified                                                        True 

Answer 1

如果你想用一种方法来表达这个，这样你就可以把它写进数据库。。。使用json

import json

df.applymap(json.dumps)

                                                                   0
id                                               "10153836054533219"
birthday                                                "04/17/1993"
education          [{"school": {"id": "281278052799", "name": "Co...
email                                          "mj_90tr@hotmail.com"
favorite_athletes  [{"id": "167866666571743", "name": "Luis Suare...
favorite_teams     [{"id": "663371713769515", "name": "Pakistan's...
first_name                                                  "Faizan"
gender                                                        "male"
hometown           {"id": "111501225536407", "name": "Islamabad, ...
last_name                                                    "Ahmad"
link               "https://www.facebook.com/app_scoped_user_id/1...
location           {"id": "111501225536407", "name": "Islamabad, ...
locale                                                       "en_US"
name                                                  "Faizan Ahmad"
timezone                                                           5
updated_time                              "2017-03-20T19:32:12+0000"
verified                                                        true

这些现在都是json字符串