dimanche 10 juillet 2016

Python request from MySQL JSON column returns a string instead of a float

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