My MySQL table:
CREATE TABLE ref_data (
id BIGINT AUTO_INCREMENT NOT NULL,
symbol VARCHAR(64) NOT NULL,
metadata JSON NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO ref_data (symbol, metadata)
VALUES ('XYZ', '{"currency": "USD", "tick_size": 0.01}');
My Python script:
import mysql.connector
con = mysql.connector.connect(**config)
cur = con.cursor()
cur.execute(
"""
SELECT JSON_EXTRACT(metadata, '$.tick_size')
FROM ref_data
WHERE symbol = 'CL';
""")
And the result is a unicode:
cur.fetchall()[0][0]
>> u'0.01'
When I run the same query within MySQL Workbench I get a double. I know I could convert the string to a float, but the point of using a JSON was flexibility and not having to specify what each column is, etc.
Thanks!
Aucun commentaire:
Enregistrer un commentaire