Python MySQL select JSON value as actual data type

Question

I'm using MySQL connector in Python to store and get some JSON data. I'm having problem with getting the stored JSON value as actual Python data type. Hope someone could point me to the right direction.

Let's say I have the following table, TestTable;

id   data
1    {"val": 100.5}
2    {"val": true}

And I have the following SQL query:

SELECT id, data->"$.val" FROM TestTable;

When I run the query int the mysql client's terminal, the result looks good
When I run the query in a Python script, the results for "val" are of type string for the float value (ie. '100.5') and boolean for the bool value (ie. true)

This is the Python code

cmd = "SELECT id, data->"$.val" FROM TestTable;"
mysql_cur.execute(cmd)
res = mysql_cur.fetchall()
mysql_cur.close()
# res has id and val. id is of type int, val is of type string for float value and type bool for boolean value

How do I get the result into Python's data type, ie. float and bool?

Thanks,

You can't. In the resultst, all values in this column must have the same datatype. — GMB
– GMB, Commented Sep 10, 2020 at 15:07
@GMB Even if all the "val" are float values, the result set still has val as string. — trungdok
– trungdok, Commented Sep 10, 2020 at 15:39
@nbk That's what I am doing right now. I am hoping for a better way to do that, such as a setting I don't know about within the mysql connector to automatically convert them just as how the boolean values are converted. — trungdok
– trungdok, Commented Sep 10, 2020 at 17:31

nbk · Accepted Answer · 2020-09-10 17:56:29Z

1

Json is basically a string, so the result is also always string.

And that is what you are receiving, and no one can convert it automatically, because only, you decide what the bytes are meaning.

Save your data properly in rows and columns and not as json or any other delimited string

answered Sep 10, 2020 at 17:56

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

2 Comments

trungdok Over a year ago

Actually, the boolean type is auto-converted in the result set so MySQL connector already has a way to deal with it. When I do JSON_TYPE(data->"$.val"), it shows the actual expected type (ie. "DOUBLE" and "BOOLEAN"). I'm just not sure why the double values were not automatically converted like the boolean values.

nbk Over a year ago

as GMb already said, you can't ,ix types,. so stay with your cobnversion in your code

Collectives™ on Stack Overflow

Python MySQL select JSON value as actual data type

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related