Converting from json to dataframe to sql

Question

I'm trying to save all the json data to the sql database and I'm using python so I decided to use pandas.
Part of the JSON:

{
"stores": [
    {
        "ID": "123456",
        "name": "Store 1",
        "status": "Active",
        "date": "2019-03-28T15:20:00Z",
        "tagIDs": null,
        "location": {
            "cityID": 2,
            "countryID": 4,
            "geoLocation": {
                "latitude": 1.13121,
                "longitude": 103.4324231
            },
            "postcode": "123456",
            "address": ""
        },
        "new": false
    },
    {
        "ID": "223456",
        "name": "Store 2",
        "status": "Active",
        "date": "2020-03-28T15:20:00Z",
        "tagIDs": [
            12,
            35
        ],
        "location": {
            "cityID": 21,
            "countryID": 5,
            "geoLocation": {
                "latitude": 1.12512,
                "longitude": 103.23342
            },
            "postcode": "223456",
            "address": ""
        },
        "new": true
    }
]

}

My Code:

response = requests.get(.....)
result = response.text
data = json.loads(result)
df = pd.json_normalize(data["store"])
.....

db_connection = sqlalchemy.create_engine(.....)
df.to_sql(con=db_connection, name="store", if_exists="append" )

Error: _mysql_connector.MySQLInterfaceError: Python type list cannot be converted
How I want the dataframe to actually look like:

     ID          tagIDs             date
0   123456        []         2020-04-23T09:32:26Z               
1   223456      [12,35]      2019-05-24T03:21:39Z                 
2   323456     [709,1493]    2019-03-28T15:38:39Z

I tried using different dataframes & json objects so far and they all work. So I discovered the issue is with the json object. Without the "tagIDs", everything else works fine.
I was thinking maybe if I converted the object to a string it can be parsed to sql but it didn't work either. How do I change the tagIDs such that I can parse everything to sql? Or is there another more efficient way to do this?

blondelg · Accepted Answer · 2020-08-21 13:58:59Z

2

I think the tagIDs field is a list and your database does not seem to be happy with it.

Not sure this is the best way but you can try to convert it from list to string

df['tagIDs'] = df['tagIDs'].apply(lambda x: str(x))

answered Aug 21, 2020 at 13:58

blondelg

9961 gold badge9 silver badges27 bronze badges

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

1 Comment

dsolate Over a year ago

yes thats what I was trying to do. thanks! just wondering do u know why does sql database not accept lists as a variable?

Collectives™ on Stack Overflow

Converting from json to dataframe to sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related