Pandas TypeError: sequence item 0: expected str instance, dict found when using to_sql

Question

I am trying to export a dataframe into a mysql database. I am getting the data via an Order and Inventory API call.

I have successfully been able to save the Order and Inventory API calls into dataframes and export Order dataframe into a MySQL table.

The Inventory dataframe however is throwing me the error:

TypeError: sequence item 0: expected str instance, dict found

I am not sure what I am doing wrong, I do suspect that the dataframe for inventory contains alot of nested json in many of the columns, but not sure what to do.

Here is my code so far for inventory:

import pandas as pd

#python libary to compare today date for birthday lists.
import numpy as np
import datetime as dt
import datetime
from pandas.io.json import json_normalize
from pandas.io import sql
import pymysql.cursors
import json
import pymysql
import pandas.io.sql
from sqlalchemy import create_engine

headers_inventory = {
    'Accept': '',
    'Content-Type': '',
    'x-api-key': '',
    'x-organization-id': '',
    'x-facility-id': '',
    'x-user-id': '',
}

r_inventory = requests.get(' URL', headers=headers_inventory, verify=False)

data = json.loads(r_inventory.text)
df_inventory = json_normalize(data)
print (df_inventory)

engine = create_engine('mysql+pymysql://USERNAME:PWD@HOST:3306/DB')
df_inventory.to_sql("inventory", engine, if_exists="replace", index = False)

Here is what the dataframe dtypes are:

int64 
object
float64

Answer 1

Had a similar problem running a pivot table with fiscal years. The issue is as above, Python 3 does not seem to recognize the numbers as a category. You have to turn that series into a string (ie change 2007, 2008 into FY2017, FY2018). Alternately just change that one column into a string, not the entire df ( df.column.astype(str) ).