CODEWITHSUNDEEP
pythonmysqldjangopandasgraphql
ShashankAC
ShashankAC

Reputation: 1078

Unable to load a pandas dataframe having a JSON column into mysql database

I have a pandas dataframe that looks like this when I print it out in the terminal in pycharm. This is inside a django project

`     exception              recommendation    time_dimension_id
0  {'exception': []}               0                217
1  {'exception': []}               0                218
2  {'exception': []}               0                219
3  {'exception': []}             546                220
4  {'exception': []}            2876                221
5  {'exception': []}            7855                222
6  {'exception': [{'error...  , 5041                223
7  {'exception': []}              57                224
8  {'exception': []}               0                225
9  {'exception': []}               0                226
10 {'exception': []}               0                227
11 {'exception': []}             108                228
12 {'exception': []}               0                229
13 {'exception': []}              12                230
14 {'exception': []}               0                231
15 {'exception': []}               0                232
16 {'exception': []}               0                233
17 {'exception': []}               0                234
18 {'exception': []}               0                235
19 {'exception': []}               0                236
20 {'exception': []}               0                237
21 {'exception': []}               0                238
22 {'exception': []}               0                239
23 {'exception': []}               0                240
`

I tried to insert this dataframe into a table using the below code.

connection = engine.connect()
    df.to_sql('table_name', con=connection, if_exists='append', index=False)

Then, I got the below error

graphql.error.located_error.GraphQLLocatedError: (MySQLdb._exceptions.OperationalError) (3140, 'Invalid JSON text: "Missing a name for object member." at position 1 in value for column \'fact_exception.exception\'.') [SQL: 'INSERT INTO fact_exception (exception, recommendation, time_dimension_id) VALUES (%s, %s, %s)'] [parameters: (({'exception': []}, 0, 217), ({'exception': []}, 0, 218), ({'exception': []}, 0, 219), ({'exception': []}, 546, 220), ({'exception': []}, 2876, 221), ({'exception': []}, 7855, 222), ({'exception': [{'error': '', 'fatal': 'com.materiall.recommender.cache.MetaLU:58 - Cannot Load metaLU for express_com-u1456154309768com.materiall.conn ... (6923 characters truncated) ... "resource.type":"index_or_alias","resource.id":"null","index_uuid":"na","index":"null"},"status":404}\n', 'time_stamp': '2020-02-11T06:26:23,694'}]}, 5041, 223), ({'exception': []}, 57, 224) ... displaying 10 of 24 total bound parameter sets ... ({'exception': []}, 0, 239), ({'exception': []}, 0, 240))] (Background on this error at: http://sqlalche.me/e/e3q8)

Below the relevant code used to create the dataframe column-wise

        fact_excep["exception"] = excep_df_column #this is a list of dictionaries
        fact_excep["recommendation"] = recommendation_col #this is a list integers
        fact_excep["time_dimension_id"] = time_dimension_id_col #this is a list integers
        # print(fact_excep)
    connection = engine.connect()
    fact_excep.to_sql("fact_exception", con=connection, if_exists="append", index=False)
    response = "fact_exception data created"
    return response

Below is the model

class FactException (models.Model):    #this is the model
fact_exception_id = models.AutoField(primary_key=True)
time_dimension_id = models.ForeignKey(
    TimeDimension, null=False, blank=True, db_column="time_dimension_id", on_delete=models.CASCADE)
recommendation = models.IntegerField()
exception = JSONField(null=True, blank=True)

objects = models.Manager()

class Meta:
    db_table = 'fact_exception'

def __int__(self):
    return self.fact_exception_id

Any help will be appreciated.

Upvotes: 0

Views: 1979

Answers (1)

dirkgroten
dirkgroten

Reputation: 20692

Your column does not contain valid JSON:

{'exception': [{'error': '', 'fatal': 'com.materiall.recommender.cache.MetaLU:58 - Cannot Load metaLU for express_com-u1456154309768com.materiall.conn...'}]}
# and
{'exception': []}

is not valid because the keys and strings have single quotes, which isn't valid in JSON. You should use double quotes and the whole column should be strings:

'{"exception": [{"error": "", "fatal": "com.materiall.recommender.cache.MetaLU:58 - Cannot Load metaLU for express_com-u1456154309768com.materiall.conn..."}]}'
# and
'{"exception": []}'

You're setting the column using a list of python dicts, but since you use df.to_sql() to save, this requires your data frame to have the exact data required by the SQL query. If you were using your model, you could just assign my_factexception.exception = some_dict and it would save as JSON. But you're essentially bypassing the Django ORM that knows your model and knows how to map a dictionary to a jsonb field so you have to do it yourself.

So when you set the values for your exception column, use json.dumps(some_dict) to create json strings.

Upvotes: 2

Related Questions